Wren:IDM 5.5 SQL Install Script for Postgres SQL

idm-5.5--01--run-as-root.sql

-- Run this script as the Postgres ROOT user
--
-- Replace the following variables before you do:
--   PASSWORD_YOU_WANT      - Should be the password you want to use for the
--                            Wren:IDM user. DO NOT USE "openidm" as a 
--                            password.
--   ROOT_USER_ACCOUNT_NAME - Should be the name of the root account you are
--                            logged-in as currently.
--

-- =====================================================
-- Source: createuser.pgsql
-- =====================================================

DROP USER IF EXISTS wrenidm;
DROP DATABASE IF EXISTS wrenidm;

CREATE USER wrenidm WITH PASSWORD 'PASSWORD_YOU_WANT';
GRANT wrenidm TO ROOT_USER_ACCOUNT_NAME;

CREATE DATABASE wrenidm ENCODING 'utf8' OWNER wrenidm;
GRANT ALL PRIVILEGES ON DATABASE wrenidm TO wrenidm;

idm-5.5--02--run-as-wrenidm.sql

-- Log-in to Postgres as the new "wrenidm" user
--

-- =====================================================
-- Source: openidm.pgsql
-- =====================================================

DROP SCHEMA IF EXISTS wrenidm CASCADE;
CREATE SCHEMA wrenidm AUTHORIZATION wrenidm;

-- -----------------------------------------------------
-- Table wrenidm.objecttpyes
-- -----------------------------------------------------

CREATE TABLE wrenidm.objecttypes (
  id BIGSERIAL NOT NULL,
  objecttype VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id),
  CONSTRAINT idx_objecttypes_objecttype UNIQUE (objecttype)
);



-- -----------------------------------------------------
-- Table wrenidm.genericobjects
-- -----------------------------------------------------

CREATE TABLE wrenidm.genericobjects (
  id BIGSERIAL NOT NULL,
  objecttypes_id BIGINT NOT NULL,
  objectid VARCHAR(255) NOT NULL,
  rev VARCHAR(38) NOT NULL,
  fullobject JSON,
  PRIMARY KEY (id),
  CONSTRAINT fk_genericobjects_objecttypes FOREIGN KEY (objecttypes_id) REFERENCES wrenidm.objecttypes (id) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT idx_genericobjects_object UNIQUE (objecttypes_id, objectid)
);



-- -----------------------------------------------------
-- Table wrenidm.genericobjectproperties
-- -----------------------------------------------------

CREATE TABLE wrenidm.genericobjectproperties (
  genericobjects_id BIGINT NOT NULL,
  propkey VARCHAR(255) NOT NULL,
  proptype VARCHAR(32) DEFAULT NULL,
  propvalue TEXT,
  CONSTRAINT fk_genericobjectproperties_genericobjects FOREIGN KEY (genericobjects_id) REFERENCES wrenidm.genericobjects (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE INDEX fk_genericobjectproperties_genericobjects ON wrenidm.genericobjectproperties (genericobjects_id);
CREATE INDEX idx_genericobjectproperties_prop ON wrenidm.genericobjectproperties (propkey,propvalue);


-- -----------------------------------------------------
-- Table wrenidm.managedobjects
-- -----------------------------------------------------

CREATE TABLE wrenidm.managedobjects (
  id BIGSERIAL NOT NULL,
  objecttypes_id BIGINT NOT NULL,
  objectid VARCHAR(255) NOT NULL,
  rev VARCHAR(38) NOT NULL,
  fullobject JSON,
  PRIMARY KEY (id),
  CONSTRAINT fk_managedobjects_objectypes FOREIGN KEY (objecttypes_id) REFERENCES wrenidm.objecttypes (id) ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE UNIQUE INDEX idx_managedobjects_object ON wrenidm.managedobjects (objecttypes_id,objectid);
-- Note that the next two indices apply only to role objects, as only role objects have a condition or temporalConstraints
CREATE INDEX idx_json_managedobjects_roleCondition ON wrenidm.managedobjects
    ( json_extract_path_text(fullobject, 'condition') );
CREATE INDEX idx_json_managedobjects_roleTemporalConstraints ON wrenidm.managedobjects
    ( json_extract_path_text(fullobject, 'temporalConstraints') );


-- -----------------------------------------------------
-- Table wrenidm.managedobjectproperties
-- -----------------------------------------------------

CREATE TABLE wrenidm.managedobjectproperties (
  managedobjects_id BIGINT NOT NULL,
  propkey VARCHAR(255) NOT NULL,
  proptype VARCHAR(32) DEFAULT NULL,
  propvalue TEXT,
  CONSTRAINT fk_managedobjectproperties_managedobjects FOREIGN KEY (managedobjects_id) REFERENCES wrenidm.managedobjects (id) ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE INDEX fk_managedobjectproperties_managedobjects ON wrenidm.managedobjectproperties (managedobjects_id);
CREATE INDEX idx_managedobjectproperties_prop ON wrenidm.managedobjectproperties (propkey,propvalue);



-- -----------------------------------------------------
-- Table wrenidm.configobjects
-- -----------------------------------------------------

CREATE TABLE wrenidm.configobjects (
  id BIGSERIAL NOT NULL,
  objecttypes_id BIGINT NOT NULL,
  objectid VARCHAR(255) NOT NULL,
  rev VARCHAR(38) NOT NULL,
  fullobject JSON,
  PRIMARY KEY (id),
  CONSTRAINT fk_configobjects_objecttypes FOREIGN KEY (objecttypes_id) REFERENCES wrenidm.objecttypes (id) ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE UNIQUE INDEX idx_configobjects_object ON wrenidm.configobjects (objecttypes_id,objectid);
CREATE INDEX fk_configobjects_objecttypes ON wrenidm.configobjects (objecttypes_id);


-- -----------------------------------------------------
-- Table wrenidm.configobjectproperties
-- -----------------------------------------------------

CREATE TABLE wrenidm.configobjectproperties (
  configobjects_id BIGINT NOT NULL,
  propkey VARCHAR(255) NOT NULL,
  proptype VARCHAR(255) DEFAULT NULL,
  propvalue TEXT,
  CONSTRAINT fk_configobjectproperties_configobjects FOREIGN KEY (configobjects_id) REFERENCES wrenidm.configobjects (id) ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE INDEX fk_configobjectproperties_configobjects ON wrenidm.configobjectproperties (configobjects_id);
CREATE INDEX idx_configobjectproperties_prop ON wrenidm.configobjectproperties (propkey,propvalue);

-- -----------------------------------------------------
-- Table wrenidm.relationships
-- -----------------------------------------------------

CREATE TABLE wrenidm.relationships (
  id BIGSERIAL NOT NULL,
  objecttypes_id BIGINT NOT NULL,
  objectid VARCHAR(255) NOT NULL,
  rev VARCHAR(38) NOT NULL,
  fullobject JSON,
  PRIMARY KEY (id),
  CONSTRAINT fk_relationships_objecttypes FOREIGN KEY (objecttypes_id) REFERENCES wrenidm.objecttypes (id) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT idx_relationships_object UNIQUE (objecttypes_id, objectid)
);

CREATE INDEX idx_json_relationships_first ON wrenidm.relationships ( json_extract_path_text(fullobject, 'firstId'), json_extract_path_text(fullobject, 'firstPropertyName') );
CREATE INDEX idx_json_relationships_second ON wrenidm.relationships ( json_extract_path_text(fullobject, 'secondId'), json_extract_path_text(fullobject, 'secondPropertyName') );
CREATE INDEX idx_json_relationships ON wrenidm.relationships ( json_extract_path_text(fullobject, 'firstId'), json_extract_path_text(fullobject, 'firstPropertyName'), json_extract_path_text(fullobject, 'secondId'), json_extract_path_text(fullobject, 'secondPropertyName') );

-- -----------------------------------------------------
-- Table wrenidm.relationshipproperties (not used in postgres)
-- -----------------------------------------------------

CREATE TABLE wrenidm.relationshipproperties (
  relationships_id BIGINT NOT NULL,
  propkey VARCHAR(255) NOT NULL,
  proptype VARCHAR(32) DEFAULT NULL,
  propvalue TEXT,
  CONSTRAINT fk_relationshipproperties_relationships FOREIGN KEY (relationships_id) REFERENCES wrenidm.relationships (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE INDEX fk_relationshipproperties_relationships ON wrenidm.relationshipproperties (relationships_id);
CREATE INDEX idx_relationshipproperties_prop ON wrenidm.relationshipproperties (propkey,propvalue);


-- -----------------------------------------------------
-- Table wrenidm.links
-- -----------------------------------------------------

CREATE TABLE wrenidm.links (
  objectid VARCHAR(38) NOT NULL,
  rev VARCHAR(38) NOT NULL,
  linktype VARCHAR(50) NOT NULL,
  linkqualifier VARCHAR(50) NOT NULL,
  firstid VARCHAR(255) NOT NULL,
  secondid VARCHAR(255) NOT NULL,
  PRIMARY KEY (objectid)
);

CREATE UNIQUE INDEX idx_links_first ON wrenidm.links (linktype, linkqualifier, firstid);
CREATE UNIQUE INDEX idx_links_second ON wrenidm.links (linktype, linkqualifier, secondid);

-- -----------------------------------------------------
-- Table wrenidm.securitykeys
-- -----------------------------------------------------

CREATE TABLE wrenidm.securitykeys (
  objectid VARCHAR(38) NOT NULL,
  rev VARCHAR(38) NOT NULL,
  keypair TEXT,
  PRIMARY KEY (objectid)
);

-- -----------------------------------------------------
-- Table wrenidm.auditauthentication
-- -----------------------------------------------------
CREATE TABLE wrenidm.auditauthentication (
  objectid VARCHAR(56) NOT NULL,
  transactionid VARCHAR(255) NOT NULL,
  activitydate VARCHAR(29) NOT NULL,
  userid VARCHAR(255) DEFAULT NULL,
  eventname VARCHAR(50) DEFAULT NULL,
  result VARCHAR(255) DEFAULT NULL,
  principals TEXT,
  context TEXT,
  entries TEXT,
  trackingids TEXT,
  PRIMARY KEY (objectid)
);

-- -----------------------------------------------------
-- Table wrenidm.auditaccess
-- -----------------------------------------------------

CREATE TABLE wrenidm.auditaccess (
  objectid VARCHAR(56) NOT NULL,
  activitydate VARCHAR(29) NOT NULL,
  eventname VARCHAR(255),
  transactionid VARCHAR(255) NOT NULL,
  userid VARCHAR(255) DEFAULT NULL,
  trackingids TEXT,
  server_ip VARCHAR(40),
  server_port VARCHAR(5),
  client_ip VARCHAR(40),
  client_port VARCHAR(5),
  request_protocol VARCHAR(255) NULL ,
  request_operation VARCHAR(255) NULL ,
  request_detail TEXT NULL ,
  http_request_secure VARCHAR(255) NULL ,
  http_request_method VARCHAR(255) NULL ,
  http_request_path VARCHAR(255) NULL ,
  http_request_queryparameters TEXT NULL ,
  http_request_headers TEXT NULL ,
  http_request_cookies TEXT NULL ,
  http_response_headers TEXT NULL ,
  response_status VARCHAR(255) NULL ,
  response_statuscode VARCHAR(255) NULL ,
  response_elapsedtime VARCHAR(255) NULL ,
  response_elapsedtimeunits VARCHAR(255) NULL ,
  response_detail TEXT NULL ,
  roles TEXT NULL ,
  PRIMARY KEY (objectid)
);

-- -----------------------------------------------------
-- Table wrenidm.auditconfig
-- -----------------------------------------------------

CREATE TABLE wrenidm.auditconfig (
  objectid VARCHAR(56) NOT NULL,
  activitydate VARCHAR(29) NOT NULL,
  eventname VARCHAR(255) DEFAULT NULL,
  transactionid VARCHAR(255) NOT NULL,
  userid VARCHAR(255) DEFAULT NULL,
  trackingids TEXT,
  runas VARCHAR(255) DEFAULT NULL,
  configobjectid VARCHAR(255) NULL ,
  operation VARCHAR(255) NULL ,
  beforeObject TEXT,
  afterObject TEXT,
  changedfields TEXT DEFAULT NULL,
  rev VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (objectid)
);

-- -----------------------------------------------------
-- Table wrenidm.auditactivity
-- -----------------------------------------------------

CREATE TABLE wrenidm.auditactivity (
  objectid VARCHAR(56) NOT NULL,
  activitydate VARCHAR(29) NOT NULL,
  eventname VARCHAR(255) DEFAULT NULL,
  transactionid VARCHAR(255) NOT NULL,
  userid VARCHAR(255) DEFAULT NULL,
  trackingids TEXT,
  runas VARCHAR(255) DEFAULT NULL,
  activityobjectid VARCHAR(255) NULL ,
  operation VARCHAR(255) NULL ,
  subjectbefore TEXT,
  subjectafter TEXT,
  changedfields TEXT DEFAULT NULL,
  subjectrev VARCHAR(255) DEFAULT NULL,
  passwordchanged VARCHAR(5) DEFAULT NULL,
  message TEXT,
  status VARCHAR(20),
  PRIMARY KEY (objectid)
);

-- -----------------------------------------------------
-- Table wrenidm.auditrecon
-- -----------------------------------------------------

CREATE TABLE wrenidm.auditrecon (
  objectid VARCHAR(56) NOT NULL,
  transactionid VARCHAR(255) NOT NULL,
  activitydate VARCHAR(29) NOT NULL,
  eventname VARCHAR(50) DEFAULT NULL,
  userid VARCHAR(255) DEFAULT NULL,
  trackingids TEXT,
  activity VARCHAR(24) DEFAULT NULL,
  exceptiondetail TEXT,
  linkqualifier VARCHAR(255) DEFAULT NULL,
  mapping VARCHAR(511) DEFAULT NULL,
  message TEXT,
  messagedetail TEXT,
  situation VARCHAR(24) DEFAULT NULL,
  sourceobjectid VARCHAR(511) DEFAULT NULL,
  status VARCHAR(20) DEFAULT NULL,
  targetobjectid VARCHAR(511) DEFAULT NULL,
  reconciling VARCHAR(12) DEFAULT NULL,
  ambiguoustargetobjectids TEXT,
  reconaction VARCHAR(36) DEFAULT NULL,
  entrytype VARCHAR(7) DEFAULT NULL,
  reconid VARCHAR(56) DEFAULT NULL,
  PRIMARY KEY (objectid)
);

CREATE INDEX idx_auditrecon_reconid ON wrenidm.auditrecon (reconid);
CREATE INDEX idx_auditrecon_entrytype ON wrenidm.auditrecon (entrytype);

-- -----------------------------------------------------
-- Table wrenidm.auditsync
-- -----------------------------------------------------

CREATE TABLE wrenidm.auditsync (
  objectid VARCHAR(56) NOT NULL,
  transactionid VARCHAR(255) NOT NULL,
  activitydate VARCHAR(29) NOT NULL,
  eventname VARCHAR(50) DEFAULT NULL,
  userid VARCHAR(255) DEFAULT NULL,
  trackingids TEXT,
  activity VARCHAR(24) DEFAULT NULL,
  exceptiondetail TEXT,
  linkqualifier VARCHAR(255) DEFAULT NULL,
  mapping VARCHAR(511) DEFAULT NULL,
  message TEXT,
  messagedetail TEXT,
  situation VARCHAR(24) DEFAULT NULL,
  sourceobjectid VARCHAR(511) DEFAULT NULL,
  status VARCHAR(20) DEFAULT NULL,
  targetobjectid VARCHAR(511) DEFAULT NULL,
  PRIMARY KEY (objectid)
);


-- -----------------------------------------------------
-- Table wrenidm.internaluser
-- -----------------------------------------------------

CREATE TABLE wrenidm.internaluser (
  objectid VARCHAR(255) NOT NULL,
  rev VARCHAR(38) NOT NULL,
  pwd VARCHAR(510) DEFAULT NULL,
  roles VARCHAR(1024) DEFAULT NULL,
  PRIMARY KEY (objectid)
);


-- -----------------------------------------------------
-- Table wrenidm.internalrole
-- -----------------------------------------------------

CREATE TABLE wrenidm.internalrole (
  objectid VARCHAR(255) NOT NULL,
  rev VARCHAR(38) NOT NULL,
  description VARCHAR(510) DEFAULT NULL,
  PRIMARY KEY (objectid)
);


-- -----------------------------------------------------
-- Table wrenidm.schedulerobjects
-- -----------------------------------------------------
CREATE TABLE wrenidm.schedulerobjects (
  id BIGSERIAL NOT NULL,
  objecttypes_id BIGINT NOT NULL,
  objectid VARCHAR(255) NOT NULL,
  rev VARCHAR(38) NOT NULL,
  fullobject JSON,
  PRIMARY KEY (id),
  CONSTRAINT fk_schedulerobjects_objectypes FOREIGN KEY (objecttypes_id) REFERENCES wrenidm.objecttypes (id) ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE UNIQUE INDEX idx_schedulerobjects_object ON wrenidm.schedulerobjects (objecttypes_id,objectid);
CREATE INDEX fk_schedulerobjects_objectypes ON wrenidm.schedulerobjects (objecttypes_id);


-- -----------------------------------------------------
-- Table wrenidm.schedulerobjectproperties
-- -----------------------------------------------------
CREATE TABLE wrenidm.schedulerobjectproperties (
  schedulerobjects_id BIGINT NOT NULL,
  propkey VARCHAR(255) NOT NULL,
  proptype VARCHAR(32) DEFAULT NULL,
  propvalue TEXT,
  CONSTRAINT fk_schedulerobjectproperties_schedulerobjects FOREIGN KEY (schedulerobjects_id) REFERENCES wrenidm.schedulerobjects (id) ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE INDEX fk_schedulerobjectproperties_schedulerobjects ON wrenidm.schedulerobjectproperties (schedulerobjects_id);
CREATE INDEX idx_schedulerobjectproperties_prop ON wrenidm.schedulerobjectproperties (propkey,propvalue);


-- -----------------------------------------------------
-- Table wrenidm.uinotification
-- -----------------------------------------------------
CREATE TABLE wrenidm.uinotification (
  objectid VARCHAR(38) NOT NULL,
  rev VARCHAR(38) NOT NULL,
  notificationType VARCHAR(255) NOT NULL,
  createDate VARCHAR(255) NOT NULL,
  message TEXT NOT NULL,
  requester VARCHAR(255) NULL,
  receiverId VARCHAR(38) NOT NULL,
  requesterId VARCHAR(38) NULL,
  notificationSubtype VARCHAR(255) NULL,
  PRIMARY KEY (objectid)
);


-- -----------------------------------------------------
-- Table wrenidm.clusterobjects
-- -----------------------------------------------------
CREATE TABLE wrenidm.clusterobjects (
  id BIGSERIAL NOT NULL,
  objecttypes_id BIGINT NOT NULL,
  objectid VARCHAR(255) NOT NULL,
  rev VARCHAR(38) NOT NULL,
  fullobject JSON,
  PRIMARY KEY (id),
  CONSTRAINT fk_clusterobjects_objectypes FOREIGN KEY (objecttypes_id) REFERENCES wrenidm.objecttypes (id) ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE UNIQUE INDEX idx_clusterobjects_object ON wrenidm.clusterobjects (objecttypes_id,objectid);
CREATE INDEX fk_clusterobjects_objectypes ON wrenidm.clusterobjects (objecttypes_id);


-- -----------------------------------------------------
-- Table wrenidm.clusterobjectproperties
-- -----------------------------------------------------
CREATE TABLE wrenidm.clusterobjectproperties (
  clusterobjects_id BIGINT NOT NULL,
  propkey VARCHAR(255) NOT NULL,
  proptype VARCHAR(32) DEFAULT NULL,
  propvalue TEXT,
  CONSTRAINT fk_clusterobjectproperties_clusterobjects FOREIGN KEY (clusterobjects_id) REFERENCES wrenidm.clusterobjects (id) ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE INDEX fk_clusterobjectproperties_clusterobjects ON wrenidm.clusterobjectproperties (clusterobjects_id);
CREATE INDEX idx_clusterobjectproperties_prop ON wrenidm.clusterobjectproperties (propkey,propvalue);


-- -----------------------------------------------------
-- Table wrenidm.updateobjects
-- -----------------------------------------------------

CREATE TABLE wrenidm.updateobjects (
  id BIGSERIAL NOT NULL,
  objecttypes_id BIGINT NOT NULL,
  objectid VARCHAR(255) NOT NULL,
  rev VARCHAR(38) NOT NULL,
  fullobject JSON,
  PRIMARY KEY (id),
  CONSTRAINT fk_updateobjects_objecttypes FOREIGN KEY (objecttypes_id) REFERENCES wrenidm.objecttypes (id) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT idx_updateobjects_object UNIQUE (objecttypes_id, objectid)
);



-- -----------------------------------------------------
-- Table wrenidm.updateobjectproperties
-- -----------------------------------------------------

CREATE TABLE wrenidm.updateobjectproperties (
  updateobjects_id BIGINT NOT NULL,
  propkey VARCHAR(255) NOT NULL,
  proptype VARCHAR(32) DEFAULT NULL,
  propvalue TEXT,
  CONSTRAINT fk_updateobjectproperties_updateobjects FOREIGN KEY (updateobjects_id) REFERENCES wrenidm.updateobjects (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE INDEX fk_updateobjectproperties_updateobjects ON wrenidm.updateobjectproperties (updateobjects_id);
CREATE INDEX idx_updateobjectproperties_prop ON wrenidm.updateobjectproperties (propkey,propvalue);


-- -----------------------------------------------------
-- Data for table wrenidm.internaluser
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO wrenidm.internaluser (objectid, rev, pwd, roles) VALUES ('openidm-admin', '0', 'openidm-admin', '[ { "_ref" : "repo/internal/role/openidm-admin" }, { "_ref" : "repo/internal/role/openidm-authorized" } ]');
INSERT INTO wrenidm.internaluser (objectid, rev, pwd, roles) VALUES ('anonymous', '0', 'anonymous', '[ { "_ref" : "repo/internal/role/openidm-reg" } ]');

INSERT INTO wrenidm.internalrole (objectid, rev, description)
VALUES
('openidm-authorized', '0', 'Basic minimum user'),
('openidm-admin', '0', 'Administrative access'),
('openidm-cert', '0', 'Authenticated via certificate'),
('openidm-tasks-manager', '0', 'Allowed to reassign workflow tasks'),
('openidm-reg', '0', 'Anonymous access');

COMMIT;

CREATE INDEX idx_json_clusterobjects_timestamp ON wrenidm.clusterobjects ( json_extract_path_text(fullobject, 'timestamp') );
CREATE INDEX idx_json_clusterobjects_state ON wrenidm.clusterobjects ( json_extract_path_text(fullobject, 'state') );

-- =====================================================
-- Source: activiti.postgres.create.engine.sql
-- =====================================================
CREATE TABLE ACT_GE_PROPERTY (
    NAME_ varchar(64),
    VALUE_ varchar(300),
    REV_ integer,
    primary key (NAME_)
);

INSERT INTO ACT_GE_PROPERTY
VALUES ('schema.version', '5.15', 1);

INSERT INTO ACT_GE_PROPERTY
VALUES ('schema.history', 'create(5.15)', 1);

INSERT INTO ACT_GE_PROPERTY
VALUES ('next.dbid', '1', 1);

CREATE TABLE ACT_GE_BYTEARRAY (
    ID_ varchar(64),
    REV_ integer,
    NAME_ varchar(255),
    DEPLOYMENT_ID_ varchar(64),
    BYTES_ bytea,
    GENERATED_ boolean,
    primary key (ID_)
);

CREATE TABLE ACT_RE_DEPLOYMENT (
    ID_ varchar(64),
    NAME_ varchar(255),
    CATEGORY_ varchar(255),
    TENANT_ID_ varchar(255) default '',
    DEPLOY_TIME_ timestamp,
    primary key (ID_)
);

CREATE TABLE ACT_RE_MODEL (
    ID_ varchar(64) not null,
    REV_ integer,
    NAME_ varchar(255),
    KEY_ varchar(255),
    CATEGORY_ varchar(255),
    CREATE_TIME_ timestamp,
    LAST_UPDATE_TIME_ timestamp,
    VERSION_ integer,
    META_INFO_ varchar(4000),
    DEPLOYMENT_ID_ varchar(64),
    EDITOR_SOURCE_VALUE_ID_ varchar(64),
    EDITOR_SOURCE_EXTRA_VALUE_ID_ varchar(64),
    TENANT_ID_ varchar(255) default '',
    primary key (ID_)
);

CREATE TABLE ACT_RU_EXECUTION (
    ID_ varchar(64),
    REV_ integer,
    PROC_INST_ID_ varchar(64),
    BUSINESS_KEY_ varchar(255),
    PARENT_ID_ varchar(64),
    PROC_DEF_ID_ varchar(64),
    SUPER_EXEC_ varchar(64),
    ACT_ID_ varchar(255),
    IS_ACTIVE_ boolean,
    IS_CONCURRENT_ boolean,
    IS_SCOPE_ boolean,
    IS_EVENT_SCOPE_ boolean,
    SUSPENSION_STATE_ integer,
    CACHED_ENT_STATE_ integer,
    TENANT_ID_ varchar(255) default '',
    primary key (ID_)
);

CREATE TABLE ACT_RU_JOB (
    ID_ varchar(64) NOT NULL,
    REV_ integer,
    TYPE_ varchar(255) NOT NULL,
    LOCK_EXP_TIME_ timestamp,
    LOCK_OWNER_ varchar(255),
    EXCLUSIVE_ boolean,
    EXECUTION_ID_ varchar(64),
    PROCESS_INSTANCE_ID_ varchar(64),
    PROC_DEF_ID_ varchar(64),
    RETRIES_ integer,
    EXCEPTION_STACK_ID_ varchar(64),
    EXCEPTION_MSG_ varchar(4000),
    DUEDATE_ timestamp,
    REPEAT_ varchar(255),
    HANDLER_TYPE_ varchar(255),
    HANDLER_CFG_ varchar(4000),
    TENANT_ID_ varchar(255) default '',
    primary key (ID_)
);

CREATE TABLE ACT_RE_PROCDEF (
    ID_ varchar(64) NOT NULL,
    REV_ integer,
    CATEGORY_ varchar(255),
    NAME_ varchar(255),
    KEY_ varchar(255) NOT NULL,
    VERSION_ integer NOT NULL,
    DEPLOYMENT_ID_ varchar(64),
    RESOURCE_NAME_ varchar(4000),
    DGRM_RESOURCE_NAME_ varchar(4000),
    DESCRIPTION_ varchar(4000),
    HAS_START_FORM_KEY_ boolean,
    SUSPENSION_STATE_ integer,
    TENANT_ID_ varchar(255) default '',
    primary key (ID_)
);

CREATE TABLE ACT_RU_TASK (
    ID_ varchar(64),
    REV_ integer,
    EXECUTION_ID_ varchar(64),
    PROC_INST_ID_ varchar(64),
    PROC_DEF_ID_ varchar(64),
    NAME_ varchar(255),
    PARENT_TASK_ID_ varchar(64),
    DESCRIPTION_ varchar(4000),
    TASK_DEF_KEY_ varchar(255),
    OWNER_ varchar(255),
    ASSIGNEE_ varchar(255),
    DELEGATION_ varchar(64),
    PRIORITY_ integer,
    CREATE_TIME_ timestamp,
    DUE_DATE_ timestamp,
    CATEGORY_ varchar(255),
    SUSPENSION_STATE_ integer,
    TENANT_ID_ varchar(255) default '',
    primary key (ID_)
);

CREATE TABLE ACT_RU_IDENTITYLINK (
    ID_ varchar(64),
    REV_ integer,
    GROUP_ID_ varchar(255),
    TYPE_ varchar(255),
    USER_ID_ varchar(255),
    TASK_ID_ varchar(64),
    PROC_INST_ID_ varchar(64),
    PROC_DEF_ID_ varchar (64),
    primary key (ID_)
);

CREATE TABLE ACT_RU_VARIABLE (
    ID_ varchar(64) not null,
    REV_ integer,
    TYPE_ varchar(255) not null,
    NAME_ varchar(255) not null,
    EXECUTION_ID_ varchar(64),
    PROC_INST_ID_ varchar(64),
    TASK_ID_ varchar(64),
    BYTEARRAY_ID_ varchar(64),
    DOUBLE_ double precision,
    LONG_ bigint,
    TEXT_ varchar(4000),
    TEXT2_ varchar(4000),
    primary key (ID_)
);

CREATE TABLE ACT_RU_EVENT_SUBSCR (
    ID_ varchar(64) not null,
    REV_ integer,
    EVENT_TYPE_ varchar(255) not null,
    EVENT_NAME_ varchar(255),
    EXECUTION_ID_ varchar(64),
    PROC_INST_ID_ varchar(64),
    ACTIVITY_ID_ varchar(64),
    CONFIGURATION_ varchar(255),
    CREATED_ timestamp not null,
    PROC_DEF_ID_ varchar(64),
    TENANT_ID_ varchar(255) default '',
    primary key (ID_)
);

CREATE INDEX ACT_IDX_EXEC_BUSKEY on ACT_RU_EXECUTION(BUSINESS_KEY_);
CREATE INDEX ACT_IDX_TASK_CREATE on ACT_RU_TASK(CREATE_TIME_);
CREATE INDEX ACT_IDX_IDENT_LNK_USER on ACT_RU_IDENTITYLINK(USER_ID_);
CREATE INDEX ACT_IDX_IDENT_LNK_GROUP on ACT_RU_IDENTITYLINK(GROUP_ID_);
CREATE INDEX ACT_IDX_EVENT_SUBSCR_CONFIG_ on ACT_RU_EVENT_SUBSCR(CONFIGURATION_);
CREATE INDEX ACT_IDX_VARIABLE_TASK_ID on ACT_RU_VARIABLE(TASK_ID_);

CREATE INDEX ACT_IDX_BYTEAR_DEPL on ACT_GE_BYTEARRAY(DEPLOYMENT_ID_);
ALTER TABLE ACT_GE_BYTEARRAY
    ADD CONSTRAINT ACT_FK_BYTEARR_DEPL
    FOREIGN KEY (DEPLOYMENT_ID_) 
    REFERENCES ACT_RE_DEPLOYMENT (ID_);

ALTER TABLE ACT_RE_PROCDEF
    ADD CONSTRAINT ACT_UNIQ_PROCDEF
    unique (KEY_,VERSION_, TENANT_ID_);
    
CREATE INDEX ACT_IDX_EXE_PROCINST on ACT_RU_EXECUTION(PROC_INST_ID_);
ALTER TABLE ACT_RU_EXECUTION
    ADD CONSTRAINT ACT_FK_EXE_PROCINST 
    FOREIGN KEY (PROC_INST_ID_) 
    REFERENCES ACT_RU_EXECUTION (ID_);

CREATE INDEX ACT_IDX_EXE_PARENT on ACT_RU_EXECUTION(PARENT_ID_);
ALTER TABLE ACT_RU_EXECUTION
    ADD CONSTRAINT ACT_FK_EXE_PARENT
    FOREIGN KEY (PARENT_ID_) 
    REFERENCES ACT_RU_EXECUTION (ID_);
    
CREATE INDEX ACT_IDX_EXE_SUPER on ACT_RU_EXECUTION(SUPER_EXEC_);
ALTER TABLE ACT_RU_EXECUTION
    ADD CONSTRAINT ACT_FK_EXE_SUPER
    FOREIGN KEY (SUPER_EXEC_) 
    REFERENCES ACT_RU_EXECUTION (ID_);

CREATE INDEX ACT_IDX_EXE_PROCDEF on ACT_RU_EXECUTION(PROC_DEF_ID_); 
ALTER TABLE ACT_RU_EXECUTION
    ADD CONSTRAINT ACT_FK_EXE_PROCDEF 
    FOREIGN KEY (PROC_DEF_ID_) 
    REFERENCES ACT_RE_PROCDEF (ID_);    
    

CREATE INDEX ACT_IDX_TSKASS_TASK on ACT_RU_IDENTITYLINK(TASK_ID_);
ALTER TABLE ACT_RU_IDENTITYLINK
    ADD CONSTRAINT ACT_FK_TSKASS_TASK
    FOREIGN KEY (TASK_ID_) 
    REFERENCES ACT_RU_TASK (ID_);
    
CREATE INDEX ACT_IDX_ATHRZ_PROCEDEF on ACT_RU_IDENTITYLINK(PROC_DEF_ID_);
ALTER TABLE ACT_RU_IDENTITYLINK
    ADD CONSTRAINT ACT_FK_ATHRZ_PROCEDEF
    FOREIGN KEY (PROC_DEF_ID_) 
    REFERENCES ACT_RE_PROCDEF (ID_);
    
CREATE INDEX ACT_IDX_IDL_PROCINST on ACT_RU_IDENTITYLINK(PROC_INST_ID_);
ALTER TABLE ACT_RU_IDENTITYLINK
    ADD CONSTRAINT ACT_FK_IDL_PROCINST
    FOREIGN KEY (PROC_INST_ID_) 
    REFERENCES ACT_RU_EXECUTION (ID_);    
    
CREATE INDEX ACT_IDX_TASK_EXEC on ACT_RU_TASK(EXECUTION_ID_);
ALTER TABLE ACT_RU_TASK
    ADD CONSTRAINT ACT_FK_TASK_EXE
    FOREIGN KEY (EXECUTION_ID_)
    REFERENCES ACT_RU_EXECUTION (ID_);
    
CREATE INDEX ACT_IDX_TASK_PROCINST on ACT_RU_TASK(PROC_INST_ID_);
ALTER TABLE ACT_RU_TASK
    ADD CONSTRAINT ACT_FK_TASK_PROCINST
    FOREIGN KEY (PROC_INST_ID_)
    REFERENCES ACT_RU_EXECUTION (ID_);
    
CREATE INDEX ACT_IDX_TASK_PROCDEF on ACT_RU_TASK(PROC_DEF_ID_);
ALTER TABLE ACT_RU_TASK
  ADD CONSTRAINT ACT_FK_TASK_PROCDEF
  FOREIGN KEY (PROC_DEF_ID_)
  REFERENCES ACT_RE_PROCDEF (ID_);
  
CREATE INDEX ACT_IDX_VAR_EXE on ACT_RU_VARIABLE(EXECUTION_ID_);
ALTER TABLE ACT_RU_VARIABLE 
    ADD CONSTRAINT ACT_FK_VAR_EXE
    FOREIGN KEY (EXECUTION_ID_) 
    REFERENCES ACT_RU_EXECUTION (ID_);

CREATE INDEX ACT_IDX_VAR_PROCINST on ACT_RU_VARIABLE(PROC_INST_ID_);
ALTER TABLE ACT_RU_VARIABLE
    ADD CONSTRAINT ACT_FK_VAR_PROCINST
    FOREIGN KEY (PROC_INST_ID_)
    REFERENCES ACT_RU_EXECUTION(ID_);

CREATE INDEX ACT_IDX_VAR_BYTEARRAY on ACT_RU_VARIABLE(BYTEARRAY_ID_);
ALTER TABLE ACT_RU_VARIABLE 
    ADD CONSTRAINT ACT_FK_VAR_BYTEARRAY 
    FOREIGN KEY (BYTEARRAY_ID_) 
    REFERENCES ACT_GE_BYTEARRAY (ID_);

CREATE INDEX ACT_IDX_JOB_EXCEPTION on ACT_RU_JOB(EXCEPTION_STACK_ID_);
ALTER TABLE ACT_RU_JOB 
    ADD CONSTRAINT ACT_FK_JOB_EXCEPTION
    FOREIGN KEY (EXCEPTION_STACK_ID_) 
    REFERENCES ACT_GE_BYTEARRAY (ID_);

CREATE INDEX ACT_IDX_EVENT_SUBSCR on ACT_RU_EVENT_SUBSCR(EXECUTION_ID_);
ALTER TABLE ACT_RU_EVENT_SUBSCR
    ADD CONSTRAINT ACT_FK_EVENT_EXEC
    FOREIGN KEY (EXECUTION_ID_)
    REFERENCES ACT_RU_EXECUTION(ID_);

CREATE INDEX ACT_IDX_MODEL_SOURCE on ACT_RE_MODEL(EDITOR_SOURCE_VALUE_ID_);
ALTER TABLE ACT_RE_MODEL 
    ADD CONSTRAINT ACT_FK_MODEL_SOURCE 
    FOREIGN KEY (EDITOR_SOURCE_VALUE_ID_) 
    REFERENCES ACT_GE_BYTEARRAY (ID_);

CREATE INDEX ACT_IDX_MODEL_SOURCE_EXTRA on ACT_RE_MODEL(EDITOR_SOURCE_EXTRA_VALUE_ID_);
ALTER TABLE ACT_RE_MODEL 
    ADD CONSTRAINT ACT_FK_MODEL_SOURCE_EXTRA 
    FOREIGN KEY (EDITOR_SOURCE_EXTRA_VALUE_ID_) 
    REFERENCES ACT_GE_BYTEARRAY (ID_);
    
CREATE INDEX ACT_IDX_MODEL_DEPLOYMENT on ACT_RE_MODEL(DEPLOYMENT_ID_);    
ALTER TABLE ACT_RE_MODEL 
    ADD CONSTRAINT ACT_FK_MODEL_DEPLOYMENT 
    FOREIGN KEY (DEPLOYMENT_ID_) 
    REFERENCES ACT_RE_DEPLOYMENT (ID_);

-- =====================================================
-- Source: activiti.postgres.create.history.sql
-- =====================================================
CREATE TABLE ACT_HI_PROCINST (
    ID_ varchar(64) not null,
    PROC_INST_ID_ varchar(64) not null,
    BUSINESS_KEY_ varchar(255),
    PROC_DEF_ID_ varchar(64) not null,
    START_TIME_ timestamp not null,
    END_TIME_ timestamp,
    DURATION_ bigint,
    START_USER_ID_ varchar(255),
    START_ACT_ID_ varchar(255),
    END_ACT_ID_ varchar(255),
    SUPER_PROCESS_INSTANCE_ID_ varchar(64),
    DELETE_REASON_ varchar(4000),
    TENANT_ID_ varchar(255) default '',
    primary key (ID_),
    unique (PROC_INST_ID_)
);

CREATE TABLE ACT_HI_ACTINST (
    ID_ varchar(64) not null,
    PROC_DEF_ID_ varchar(64) not null,
    PROC_INST_ID_ varchar(64) not null,
    EXECUTION_ID_ varchar(64) not null,
    ACT_ID_ varchar(255) not null,
    TASK_ID_ varchar(64),
    CALL_PROC_INST_ID_ varchar(64),
    ACT_NAME_ varchar(255),
    ACT_TYPE_ varchar(255) not null,
    ASSIGNEE_ varchar(255),
    START_TIME_ timestamp not null,
    END_TIME_ timestamp,
    DURATION_ bigint,
    TENANT_ID_ varchar(255) default '',
    primary key (ID_)
);

CREATE TABLE ACT_HI_TASKINST (
    ID_ varchar(64) not null,
    PROC_DEF_ID_ varchar(64),
    TASK_DEF_KEY_ varchar(255),
    PROC_INST_ID_ varchar(64),
    EXECUTION_ID_ varchar(64),
    NAME_ varchar(255),
    PARENT_TASK_ID_ varchar(64),
    DESCRIPTION_ varchar(4000),
    OWNER_ varchar(255),
    ASSIGNEE_ varchar(255),
    START_TIME_ timestamp not null,
    CLAIM_TIME_ timestamp,
    END_TIME_ timestamp,
    DURATION_ bigint,
    DELETE_REASON_ varchar(4000),
    PRIORITY_ integer,
    DUE_DATE_ timestamp,
    FORM_KEY_ varchar(255),
    CATEGORY_ varchar(255),
    TENANT_ID_ varchar(255) default '',
    primary key (ID_)
);

CREATE TABLE ACT_HI_VARINST (
    ID_ varchar(64) not null,
    PROC_INST_ID_ varchar(64),
    EXECUTION_ID_ varchar(64),
    TASK_ID_ varchar(64),
    NAME_ varchar(255) not null,
    VAR_TYPE_ varchar(100),
    REV_ integer,
    BYTEARRAY_ID_ varchar(64),
    DOUBLE_ double precision,
    LONG_ bigint,
    TEXT_ varchar(4000),
    TEXT2_ varchar(4000),
    CREATE_TIME_ timestamp,
    LAST_UPDATED_TIME_ timestamp,
    primary key (ID_)
);

CREATE TABLE ACT_HI_DETAIL (
    ID_ varchar(64) not null,
    TYPE_ varchar(255) not null,
    PROC_INST_ID_ varchar(64),
    EXECUTION_ID_ varchar(64),
    TASK_ID_ varchar(64),
    ACT_INST_ID_ varchar(64),
    NAME_ varchar(255) not null,
    VAR_TYPE_ varchar(64),
    REV_ integer,
    TIME_ timestamp not null,
    BYTEARRAY_ID_ varchar(64),
    DOUBLE_ double precision,
    LONG_ bigint,
    TEXT_ varchar(4000),
    TEXT2_ varchar(4000),
    primary key (ID_)
);

CREATE TABLE ACT_HI_COMMENT (
    ID_ varchar(64) not null,
    TYPE_ varchar(255),
    TIME_ timestamp not null,
    USER_ID_ varchar(255),
    TASK_ID_ varchar(64),
    PROC_INST_ID_ varchar(64),
    ACTION_ varchar(255),
    MESSAGE_ varchar(4000),
    FULL_MSG_ bytea,
    primary key (ID_)
);

CREATE TABLE ACT_HI_ATTACHMENT (
    ID_ varchar(64) not null,
    REV_ integer,
    USER_ID_ varchar(255),
    NAME_ varchar(255),
    DESCRIPTION_ varchar(4000),
    TYPE_ varchar(255),
    TASK_ID_ varchar(64),
    PROC_INST_ID_ varchar(64),
    URL_ varchar(4000),
    CONTENT_ID_ varchar(64),
    primary key (ID_)
);

CREATE TABLE ACT_HI_IDENTITYLINK (
    ID_ varchar(64),
    GROUP_ID_ varchar(255),
    TYPE_ varchar(255),
    USER_ID_ varchar(255),
    TASK_ID_ varchar(64),
    PROC_INST_ID_ varchar(64),
    primary key (ID_)
);


CREATE INDEX ACT_IDX_HI_PRO_INST_END on ACT_HI_PROCINST(END_TIME_);
CREATE INDEX ACT_IDX_HI_PRO_I_BUSKEY on ACT_HI_PROCINST(BUSINESS_KEY_);
CREATE INDEX ACT_IDX_HI_ACT_INST_START on ACT_HI_ACTINST(START_TIME_);
CREATE INDEX ACT_IDX_HI_ACT_INST_END on ACT_HI_ACTINST(END_TIME_);
CREATE INDEX ACT_IDX_HI_DETAIL_PROC_INST on ACT_HI_DETAIL(PROC_INST_ID_);
CREATE INDEX ACT_IDX_HI_DETAIL_ACT_INST on ACT_HI_DETAIL(ACT_INST_ID_);
CREATE INDEX ACT_IDX_HI_DETAIL_TIME on ACT_HI_DETAIL(TIME_);
CREATE INDEX ACT_IDX_HI_DETAIL_NAME on ACT_HI_DETAIL(NAME_);
CREATE INDEX ACT_IDX_HI_DETAIL_TASK_ID on ACT_HI_DETAIL(TASK_ID_);
CREATE INDEX ACT_IDX_HI_PROCVAR_PROC_INST on ACT_HI_VARINST(PROC_INST_ID_);
CREATE INDEX ACT_IDX_HI_PROCVAR_NAME_TYPE on ACT_HI_VARINST(NAME_, VAR_TYPE_);
CREATE INDEX ACT_IDX_HI_ACT_INST_PROCINST on ACT_HI_ACTINST(PROC_INST_ID_, ACT_ID_);
CREATE INDEX ACT_IDX_HI_ACT_INST_EXEC on ACT_HI_ACTINST(EXECUTION_ID_, ACT_ID_);
CREATE INDEX ACT_IDX_HI_IDENT_LNK_USER on ACT_HI_IDENTITYLINK(USER_ID_);
CREATE INDEX ACT_IDX_HI_IDENT_LNK_TASK on ACT_HI_IDENTITYLINK(TASK_ID_);
CREATE INDEX ACT_IDX_HI_IDENT_LNK_PROCINST on ACT_HI_IDENTITYLINK(PROC_INST_ID_);

-- =====================================================
-- Source: activiti.postgres.create.identity.sql
-- =====================================================
CREATE TABLE ACT_ID_GROUP (
    ID_ varchar(64),
    REV_ integer,
    NAME_ varchar(255),
    TYPE_ varchar(255),
    primary key (ID_)
);

CREATE TABLE ACT_ID_MEMBERSHIP (
    USER_ID_ varchar(64),
    GROUP_ID_ varchar(64),
    primary key (USER_ID_, GROUP_ID_)
);

CREATE TABLE ACT_ID_USER (
    ID_ varchar(64),
    REV_ integer,
    FIRST_ varchar(255),
    LAST_ varchar(255),
    EMAIL_ varchar(255),
    PWD_ varchar(255),
    PICTURE_ID_ varchar(64),
    primary key (ID_)
);

CREATE TABLE ACT_ID_INFO (
    ID_ varchar(64),
    REV_ integer,
    USER_ID_ varchar(64),
    TYPE_ varchar(64),
    KEY_ varchar(255),
    VALUE_ varchar(255),
    PASSWORD_ bytea,
    PARENT_ID_ varchar(255),
    primary key (ID_)
);

CREATE INDEX ACT_IDX_MEMB_GROUP on ACT_ID_MEMBERSHIP(GROUP_ID_);
ALTER TABLE ACT_ID_MEMBERSHIP 
    ADD CONSTRAINT ACT_FK_MEMB_GROUP
    FOREIGN KEY (GROUP_ID_) 
    REFERENCES ACT_ID_GROUP (ID_);

CREATE INDEX ACT_IDX_MEMB_USER on ACT_ID_MEMBERSHIP(USER_ID_);
ALTER TABLE ACT_ID_MEMBERSHIP 
    ADD CONSTRAINT ACT_FK_MEMB_USER
    FOREIGN KEY (USER_ID_) 
    REFERENCES ACT_ID_USER (ID_);

idm-5.5--03--run-as-root.sql

-- Customize this script to match the fields you are using for searchable user
-- properties.
--
-- Then log-in as the Postgres root user and run this script to create the 
-- indices and appropriate extensions.
--

-- =====================================================
-- Source: default_schema_optimization.pgsql
-- =====================================================

-- This script is optional. It is designed to optimize the performance of the
-- queries used in the default repo.jdbc.json file for PostgreSQL and the 
-- default schema, along with the default UI.

-- This file has to be executed by a user with SUPERUSER privileges, so that 
-- the extension can be created. By default this is the 'postgres' user. 
--
-- For example: 

-- psql -U postgres wrenidm < default_schema_optimization.sql


-- These btree indexes are great for sorting and exact matches.
CREATE UNIQUE INDEX idx_json_managedobjects_userName ON wrenidm.managedobjects 
    ( json_extract_path_text(fullobject, 'userName'), objecttypes_id );
CREATE INDEX idx_json_managedobjects_givenName ON wrenidm.managedobjects 
    ( json_extract_path_text(fullobject, 'givenName') );
CREATE INDEX idx_json_managedobjects_sn ON wrenidm.managedobjects 
    ( json_extract_path_text(fullobject, 'sn') );
CREATE INDEX idx_json_managedobjects_mail ON wrenidm.managedobjects 
    ( json_extract_path_text(fullobject, 'mail') );
CREATE INDEX idx_json_managedobjects_accountStatus ON wrenidm.managedobjects 
    ( json_extract_path_text(fullobject, 'accountStatus') );

-- The PosgreSQL contrib extension 'pg_trgm' is needed to perform fast LIKE queries. Be sure you have installed
-- the 'postgresql-contrib' packages necessary to support it.

-- More info here http://www.depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike/

CREATE EXTENSION pg_trgm;

-- These "gin" indexes are great for performing LIKE operations. Use if you plan on doing
-- a lot of these types of queries. Below are some examples you might create if you are
-- using the default project schema with the default Wren:IDM UI. Only enable these if you
-- need to perform the LIKE queries, otherwise you will incur a cost on creation with no
-- associated benefit.

CREATE INDEX idx_json_managedobjects_userName_gin ON wrenidm.managedobjects
  USING gin (json_extract_path_text(fullobject, 'userName') gin_trgm_ops);
CREATE INDEX idx_json_managedobjects_givenName_gin ON wrenidm.managedobjects 
  USING gin (json_extract_path_text(fullobject, 'givenName') gin_trgm_ops);
CREATE INDEX idx_json_managedobjects_sn_gin ON wrenidm.managedobjects 
  USING gin (json_extract_path_text(fullobject, 'sn') gin_trgm_ops);
CREATE INDEX idx_json_managedobjects_mail_gin ON wrenidm.managedobjects 
  USING gin (json_extract_path_text(fullobject, 'mail') gin_trgm_ops);
CREATE INDEX idx_json_managedobjects_accountStatus_gin ON wrenidm.managedobjects 
  USING gin (json_extract_path_text(fullobject, 'accountStatus') gin_trgm_ops);