openidm.sql revision ece129ea346a2c66eb6241f1c39735a582698cda
CONNECT TO DOPENIDM;
CONNECT RESET;
-- DROP STOGROUP GOPENIDM;
-- COMMIT;
-- CREATE STOGROUP GOPENIDM
-- VOLUMES ('*')
-- VCAT VSDB2T
--;
-- STOGROUP GOPENIDM
-- BUFFERPOOL BP2
-- Increase default page size for Activiti
;
CONNECT TO DOPENIDM;
-- http://db2-vignettes.blogspot.com/2013/07/a-temporary-table-could-not-be-created.html
-- -----------------------------------------------------
-- Table openidm.objecttypes
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.OBJECTTYPES (
OBJECTTYPE VARCHAR(255) NOT NULL,
-- -----------------------------------------------------
-- Table openidm.genericobjects
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.GENERICOBJECTS (
objecttypes_id INTEGER NOT NULL,
objectid VARCHAR(255) NOT NULL,
rev VARCHAR(38) NOT NULL,
CONSTRAINT FK_GENERICOBJECTS_OBJECTTYPES
FOREIGN KEY (OBJECTTYPES_ID ) REFERENCES SOPENIDM.OBJECTTYPES (ID ) ON DELETE CASCADE) IN DOPENIDM.SOIDM01;
CREATE INDEX SOPENIDM.FK_GENERICOBJECTS_OBJECTTYPES ON SOPENIDM.GENERICOBJECTS (OBJECTTYPES_ID ASC);
CREATE UNIQUE INDEX SOPENIDM.IDX_GENERICOBJECTS_OBJECT ON SOPENIDM.GENERICOBJECTS (OBJECTID ASC, OBJECTTYPES_ID ASC);
-- -----------------------------------------------------
-- Table openidm.genericobjectproperties
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.GENERICOBJECTPROPERTIES (
genericobjects_id INTEGER NOT NULL,
propkey VARCHAR(255) NOT NULL,
proptype VARCHAR(255),
propvalue VARCHAR(2000),
CONSTRAINT FK_GENERICOBJECTPROPERTIES_GENERICOBJECTS
ON DELETE CASCADE
CREATE INDEX SOPENIDM.IDX_GENERICOBJECTPROPERTIES_GENERICOBJECTS ON SOPENIDM.GENERICOBJECTPROPERTIES (GENERICOBJECTS_ID ASC);
CREATE INDEX SOPENIDM.IDX_GENERICOBJECTPROPERTIES_PROPKEY ON SOPENIDM.GENERICOBJECTPROPERTIES (PROPKEY ASC);
CREATE INDEX SOPENIDM.IDX_GENERICOBJECTPROPERTIES_PROPVALUE ON SOPENIDM.GENERICOBJECTPROPERTIES (PROPVALUE ASC);
-- -----------------------------------------------------
-- Table openidm.managedobjects
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.MANAGEDOBJECTS (
objecttypes_id INTEGER NOT NULL,
objectid VARCHAR(255) NOT NULL,
rev VARCHAR(38) NOT NULL,
CONSTRAINT FK_MANAGEDOBJECTS_OBJECTTYPES
ON DELETE CASCADE
CREATE INDEX SOPENIDM.FK_MANAGEDOBJECTS_OBJECTTYPES ON SOPENIDM.MANAGEDOBJECTS (OBJECTTYPES_ID ASC);
-- -----------------------------------------------------
-- Table openidm.managedobjectproperties
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.MANAGEDOBJECTPROPERTIES (
MANAGEDOBJECTS_ID INTEGER NOT NULL,
PROPKEY VARCHAR(255) NOT NULL,
PROPTYPE VARCHAR(255),
PROPVALUE VARCHAR(2000),
CONSTRAINT FK_MANAGEDOBJECTPROPERTIES_MANAGEDOBJECTS
FOREIGN KEY (MANAGEDOBJECTS_ID )
ON DELETE CASCADE
CREATE INDEX SOPENIDM.IDX_MANAGEDOBJECTPROPERTIES_MANAGEDOBJECTS ON SOPENIDM.MANAGEDOBJECTPROPERTIES (MANAGEDOBJECTS_ID ASC);
CREATE INDEX SOPENIDM.IDX_MANAGEDOBJECTPROPERTIES_PROPKEY ON SOPENIDM.MANAGEDOBJECTPROPERTIES (PROPKEY ASC);
CREATE INDEX SOPENIDM.IDX_MANAGEDOBJECTPROPERTIES_PROPVALUE ON SOPENIDM.MANAGEDOBJECTPROPERTIES (PROPVALUE ASC);
-- -----------------------------------------------------
-- Table openidm.configobjects
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.CONFIGOBJECTS (
objecttypes_id INTEGER NOT NULL,
objectid VARCHAR(255) NOT NULL,
rev VARCHAR(38) NOT NULL,
CONSTRAINT FK_CONFIGOBJECTS_OBJECTTYPES
FOREIGN KEY (OBJECTTYPES_ID )
ON DELETE CASCADE
CREATE UNIQUE INDEX SOPENIDM.IDX_CONFIGOBJECTS_OBJECT ON SOPENIDM.CONFIGOBJECTS (OBJECTID ASC, OBJECTTYPES_ID ASC);
-- -----------------------------------------------------
-- Table openidm.configobjectproperties
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.CONFIGOBJECTPROPERTIES (
CONFIGOBJECTS_ID INTEGER NOT NULL,
PROPKEY VARCHAR(255) NOT NULL,
PROPTYPE VARCHAR(255),
PROPVALUE VARCHAR(2000),
CONSTRAINT FK_CONFIGOBJECTPROPERTIES_CONFIGOBJECTS
FOREIGN KEY (CONFIGOBJECTS_ID )
ON DELETE CASCADE
CREATE INDEX SOPENIDM.IDX_CONFIGOBJECTPROPERTIES_CONFIGOBJECTS ON SOPENIDM.CONFIGOBJECTPROPERTIES (CONFIGOBJECTS_ID ASC);
CREATE INDEX SOPENIDM.IDX_CONFIGOBJECTPROPERTIES_PROPKEY ON SOPENIDM.CONFIGOBJECTPROPERTIES (PROPKEY ASC);
CREATE INDEX SOPENIDM.IDX_CONFIGOBJECTPROPERTIES_PROPVALUE ON SOPENIDM.CONFIGOBJECTPROPERTIES (PROPVALUE ASC);
-- -----------------------------------------------------
-- Table openidm.relationships
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.RELATIONSHIPS (
objecttypes_id INTEGER NOT NULL,
objectid VARCHAR(255) NOT NULL,
rev VARCHAR(38) NOT NULL,
CONSTRAINT fk_relationships_objecttypes
FOREIGN KEY (objecttypes_id)
ON DELETE CASCADE
CREATE UNIQUE INDEX sopenidm.idx_relaitonships_object ON sopenidm.relationships (objectid ASC, objecttypes_id ASC);
-- -----------------------------------------------------
-- Table openidm.relationshiproperties
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.RELATIONSHIPPROPERTIES (
relationships_id INTEGER NOT NULL,
propkey VARCHAR(255) NOT NULL,
proptype VARCHAR(255),
propvalue VARCHAR(2000),
CONSTRAINT fk_relationshipproperties_relationships
FOREIGN KEY (relationships_id)
ON DELETE CASCADE
CREATE INDEX SOPENIDM.IDX_RELATIONSHIPPROPERTIES_RELATIONSHIPS ON SOPENIDM.RELATIONSHIPPROPERTIES (RELATIONSHIPS_ID ASC);
CREATE INDEX SOPENIDM.IDX_RELATIONSHIPPROPERTIES_PROPKEY ON SOPENIDM.RELATIONSHIPPROPERTIES (PROPKEY ASC);
CREATE INDEX SOPENIDM.IDX_RELATIONSHIPPROPERTIES_PROPVALUE ON SOPENIDM.RELATIONSHIPPROPERTIES (PROPVALUE ASC);
-- -----------------------------------------------------
-- Table openidm.links
-- -----------------------------------------------------
objectid VARCHAR(38) NOT NULL,
rev VARCHAR(38) NOT NULL,
linktype VARCHAR(255) NOT NULL,
linkqualifier VARCHAR(255) NOT NULL,
firstid VARCHAR(255) NOT NULL,
secondid VARCHAR(255) NOT NULL,
CREATE UNIQUE INDEX SOPENIDM.IDX_LINKS_FIRST ON SOPENIDM.LINKS (LINKTYPE ASC, LINKQUALIFIER ASC, FIRSTID ASC);
CREATE UNIQUE INDEX SOPENIDM.IDX_LINKS_SECOND ON SOPENIDM.LINKS (LINKTYPE ASC, LINKQUALIFIER ASC, SECONDID ASC);
-- -----------------------------------------------------
-- Table openidm.security
-- -----------------------------------------------------
objectid VARCHAR(38) NOT NULL,
rev VARCHAR(38) NOT NULL,
-- -----------------------------------------------------
-- Table openidm.securitykeys
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.SECURITYKEYS (
objectid VARCHAR(38) NOT NULL,
rev VARCHAR(38) NOT NULL,
-- -----------------------------------------------------
-- Table `openidm`.`auditauthentication`
-- -----------------------------------------------------
CREATE TABLE sopenidm.auditauthentication (
objectid VARCHAR(56) NOT NULL,
transactionid VARCHAR(255) NOT NULL,
activitydate VARCHAR(29) NOT NULL,
userid VARCHAR(255) NULL,
eventname VARCHAR(50) NULL,
result VARCHAR(255) NULL,
-- -----------------------------------------------------
-- Table openidm.auditrecon
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.AUDITRECON (
objectid VARCHAR(56) NOT NULL ,
transactionid VARCHAR(255) NOT NULL ,
activitydate VARCHAR(29) NOT NULL ,
eventname VARCHAR(50) NULL ,
userid VARCHAR(255) NULL ,
activity VARCHAR(24) NULL ,
linkqualifier VARCHAR(255) NULL ,
mapping VARCHAR(511) NULL ,
situation VARCHAR(24) NULL ,
sourceobjectid VARCHAR(511) NULL ,
status VARCHAR(20) NULL ,
targetobjectid VARCHAR(511) NULL ,
reconciling VARCHAR(12) NULL ,
reconaction VARCHAR(36) NULL ,
entrytype VARCHAR(7) NULL ,
reconid VARCHAR(56) NULL ,
--CREATE INDEX sopenidm.idx_auditrecon_targetobjectid ON sopenidm.auditrecon (targetobjectid(28) ASC);
--CREATE INDEX sopenidm.idx_auditrecon_sourceobjectid ON sopenidm.auditrecon (sourceobjectid(28) ASC);
--CREATE INDEX sopenidm.idx_auditrecon_mapping ON sopenidm.auditrecon (mapping(255) ASC);
-- -----------------------------------------------------
-- Table openidm.auditsync
-- -----------------------------------------------------
objectid VARCHAR(56) NOT NULL ,
transactionid VARCHAR(255) NOT NULL ,
activitydate VARCHAR(29) NOT NULL ,
eventname VARCHAR(50) NULL ,
userid VARCHAR(255) NULL ,
activity VARCHAR(24) NULL ,
linkqualifier VARCHAR(255) NULL ,
mapping VARCHAR(511) NULL ,
situation VARCHAR(24) NULL ,
sourceobjectid VARCHAR(511) NULL ,
status VARCHAR(20) NULL ,
targetobjectid VARCHAR(511) NULL ,
-- -----------------------------------------------------
-- Table `openidm`.`auditconfig`
-- -----------------------------------------------------
CREATE TABLE sopenidm.auditconfig (
objectid VARCHAR(56) NOT NULL ,
activitydate VARCHAR(29) NOT NULL,
eventname VARCHAR(255) NULL ,
transactionid VARCHAR(255) NOT NULL ,
userid VARCHAR(255) NULL ,
runas VARCHAR(255) NULL ,
configobjectid VARCHAR(255) NULL ,
operation VARCHAR(255) NULL ,
changedfields VARCHAR(255) NULL ,
rev VARCHAR(255) NULL,
-- -----------------------------------------------------
-- Table openidm.auditactivity
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.AUDITACTIVITY (
objectid VARCHAR(56) NOT NULL ,
activitydate VARCHAR(29) NOT NULL,
eventname VARCHAR(255) NULL ,
transactionid VARCHAR(255) NOT NULL ,
userid VARCHAR(255) NULL ,
runas VARCHAR(255) NULL ,
activityobjectid VARCHAR(255) NULL ,
operation VARCHAR(255) NULL ,
changedfields VARCHAR(255) NULL ,
subjectrev VARCHAR(255) NULL ,
passwordchanged VARCHAR(5) NULL ,
status VARCHAR(20) ,
CREATE INDEX SOPENIDM.idx_auditactivity_transactionid ON SOPENIDM.AUDITACTIVITY (transactionid ASC);
-- -----------------------------------------------------
-- Table openidm.auditaccess
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.AUDITACCESS (
objectid VARCHAR(56) NOT NULL ,
activitydate VARCHAR(29) NOT NULL,
eventname VARCHAR(255) ,
transactionid VARCHAR(255) NOT NULL ,
userid VARCHAR(255) ,
server_ip VARCHAR(40) ,
server_port VARCHAR(5) ,
client_host VARCHAR(255) ,
client_ip VARCHAR(40) ,
client_port VARCHAR(5) ,
request_protocol VARCHAR(255) NULL ,
request_operation VARCHAR(255) NULL ,
http_request_secure VARCHAR(255) NULL ,
http_request_method VARCHAR(255) NULL ,
http_request_path VARCHAR(255) NULL ,
response_status VARCHAR(255) NULL ,
response_statuscode VARCHAR(255) NULL ,
response_elapsedtime VARCHAR(255) NULL ,
response_elapsedtimeunits VARCHAR(255) NULL ,
CREATE INDEX SOPENIDM.idx_auditaccess_response_status ON SOPENIDM.AUDITACCESS (response_status ASC);
--CREATE INDEX SOPENIDM.idx_auditaccess_principal ON SOPENIDM.AUDITACCESS (principal(28) ASC);
-- -----------------------------------------------------
-- Table openidm.internaluser
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.INTERNALUSER (
objectid VARCHAR(254) NOT NULL,
rev VARCHAR(38),
pwd VARCHAR(510),
roles VARCHAR(1024),
-- -----------------------------------------------------
-- Table openidm.internaluser
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.INTERNALROLE (
objectid VARCHAR(254) NOT NULL,
rev VARCHAR(38),
description VARCHAR(1024),
-- -----------------------------------------------------
-- Table openidm.schedulerobjects
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.SCHEDULEROBJECTS (
OBJECTTYPES_ID INTEGER NOT NULL,
OBJECTID VARCHAR(255) NOT NULL,
REV VARCHAR(38) NOT NULL,
CONSTRAINT FK_SCHEDULEROBJECTS_OBJECTTYPES
FOREIGN KEY (OBJECTTYPES_ID )
ON DELETE CASCADE
CREATE INDEX SOPENIDM.FK_SCHEDULEROBJECTS_OBJECTTYPES ON SOPENIDM.SCHEDULEROBJECTS (OBJECTTYPES_ID ASC) ;
CREATE UNIQUE INDEX SOPENIDM.IDX_SCHEDULEROBJECTS_OBJECT ON SOPENIDM.SCHEDULEROBJECTS (OBJECTID ASC, OBJECTTYPES_ID ASC);
-- -----------------------------------------------------
-- Table openidm.schedulerobjectproperties
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.SCHEDULEROBJECTPROPERTIES (
SCHEDULEROBJECTS_ID INTEGER NOT NULL,
PROPKEY VARCHAR(255) NOT NULL,
PROPTYPE VARCHAR(255),
PROPVALUE VARCHAR(2000),
FOREIGN KEY (SCHEDULEROBJECTS_ID )
ON DELETE CASCADE
CREATE INDEX SOPENIDM.IDX_SCHEDULEROBJECTPROPERTIES_SCHEDULEROBJECTS ON SOPENIDM.SCHEDULEROBJECTPROPERTIES (SCHEDULEROBJECTS_ID ASC) ;
CREATE INDEX SOPENIDM.IDX_SCHEDULEROBJECTPROPERTIES_PROPKEY ON SOPENIDM.SCHEDULEROBJECTPROPERTIES (PROPKEY ASC) ;
CREATE INDEX SOPENIDM.IDX_SCHEDULEROBJECTPROPERTIES_PROPVALUE ON SOPENIDM.SCHEDULEROBJECTPROPERTIES (PROPVALUE ASC) ;
-- -----------------------------------------------------
-- Table openidm.uinotification
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.UINOTIFICATION (
OBJECTID VARCHAR(38) NOT NULL,
REV VARCHAR(38) NOT NULL,
NOTIFICATIONTYPE VARCHAR(255) NOT NULL,
CREATEDATE VARCHAR(255) NOT NULL,
REQUESTER VARCHAR(255) NULL,
RECEIVERID VARCHAR(38) NOT NULL,
REQUESTERID VARCHAR(38) NULL,
NOTIFICATIONSUBTYPE VARCHAR(255) NULL,
-- -----------------------------------------------------
-- Table openidm.clusterobjects
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.CLUSTEROBJECTS (
OBJECTTYPES_ID INTEGER NOT NULL,
OBJECTID VARCHAR(255) NOT NULL,
REV VARCHAR(38) NOT NULL,
CONSTRAINT FK_CLUSTEROBJECTS_OBJECTTYPES
FOREIGN KEY (OBJECTTYPES_ID )
ON DELETE CASCADE
CREATE INDEX SOPENIDM.FK_CLUSTEROBJECTS_OBJECTTYPES ON SOPENIDM.CLUSTEROBJECTS (OBJECTTYPES_ID ASC);
CREATE UNIQUE INDEX SOPENIDM.IDX_CLUSTEROBJECTS_OBJECT ON SOPENIDM.CLUSTEROBJECTS (OBJECTID ASC, OBJECTTYPES_ID ASC);
-- -----------------------------------------------------
-- Table openidm.clusterobjectproperties
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.CLUSTEROBJECTPROPERTIES (
CLUSTEROBJECTS_ID INTEGER NOT NULL,
PROPKEY VARCHAR(255) NOT NULL,
PROPTYPE VARCHAR(255),
PROPVALUE VARCHAR(2000),
CONSTRAINT FK_CLUSTEROBJECTPROPERTIES_CLUSTEROBJECTS
FOREIGN KEY (CLUSTEROBJECTS_ID )
ON DELETE CASCADE
CREATE INDEX SOPENIDM.IDX_CLUSTEROBJECTPROPERTIES_CLUSTEROBJECTS ON SOPENIDM.CLUSTEROBJECTPROPERTIES (CLUSTEROBJECTS_ID ASC);
CREATE INDEX SOPENIDM.IDX_CLUSTEROBJECTPROPERTIES_PROPKEY ON SOPENIDM.CLUSTEROBJECTPROPERTIES (PROPKEY ASC);
CREATE INDEX SOPENIDM.IDX_CLUSTEROBJECTPROPERTIES_PROPVALUE ON SOPENIDM.CLUSTEROBJECTPROPERTIES (PROPVALUE ASC);
-- -----------------------------------------------------
-- Table `openidm`.`updateobjects`
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.UPDATEOBJECTS (
OBJECTTYPES_ID INTEGER NOT NULL,
OBJECTID VARCHAR(255) NOT NULL,
REV VARCHAR(38) NOT NULL,
CONSTRAINT FK_UPDATEOBJECTS_OBJECTTYPES
FOREIGN KEY (OBJECTTYPES_ID )
ON DELETE CASCADE
-- -----------------------------------------------------
-- Table `openidm`.`updateobjectproperties`
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.UPDATEOBJECTPROPERTIES (
UPDATEOBJECTS_ID INTEGER NOT NULL,
PROPKEY VARCHAR(255) NOT NULL,
PROPTYPE VARCHAR(255),
PROPVALUE VARCHAR(2000),
CONSTRAINT FK_UPDATEOBJECTPROPERTIES_UPDATEOBJECTS
FOREIGN KEY (UPDATEOBJECTS_ID )
ON DELETE CASCADE
CREATE INDEX SOPENIDM.IDX_UPDATEOBJECTPROPERTIES_UPDATEOBJECTS ON SOPENIDM.UPDATEOBJECTPROPERTIES (UPDATEOBJECTS_ID ASC);
CREATE INDEX SOPENIDM.IDX_UPDATEOBJECTPROPERTIES_PROPKEY ON SOPENIDM.UPDATEOBJECTPROPERTIES (PROPKEY ASC);
CREATE INDEX SOPENIDM.IDX_UPDATEOBJECTPROPERTIES_PROPVALUE ON SOPENIDM.UPDATEOBJECTPROPERTIES (PROPVALUE ASC);
-- -----------------------------------------------------
-- Data for table openidm.internaluser
-- -----------------------------------------------------
INSERT INTO sopenidm.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 sopenidm.internaluser (objectid, rev, pwd, roles) VALUES ('anonymous', '0', 'anonymous', '[ { "_ref" : "repo/internal/role/openidm-reg" } ]');
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;