openidm.pgsql revision 2f04b65634c7ef5c92562ec6dacc802d9156ab74
--DROP SCHEMA IF EXISTS openidm CASCADE;
CREATE SCHEMA openidm AUTHORIZATION openidm;
-- -----------------------------------------------------
-- Table openidm.objecttpyes
-- -----------------------------------------------------
CREATE TABLE openidm.objecttypes (
id BIGSERIAL NOT NULL,
objecttype VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT idx_objecttypes_objecttype UNIQUE (objecttype)
);
-- -----------------------------------------------------
-- Table openidm.genericobjects
-- -----------------------------------------------------
CREATE TABLE openidm.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 openidm.objecttypes (id) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT idx_genericobjects_object UNIQUE (objecttypes_id, objectid)
);
-- -----------------------------------------------------
-- Table openidm.genericobjectproperties
-- -----------------------------------------------------
CREATE TABLE openidm.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 openidm.genericobjects (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE INDEX fk_genericobjectproperties_genericobjects ON openidm.genericobjectproperties (genericobjects_id);
CREATE INDEX idx_genericobjectproperties_prop ON openidm.genericobjectproperties (propkey,propvalue);
-- -----------------------------------------------------
-- Table openidm.managedobjects
-- -----------------------------------------------------
CREATE TABLE openidm.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 openidm.objecttypes (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE UNIQUE INDEX idx_managedobjects_object ON openidm.managedobjects (objecttypes_id,objectid);
CREATE INDEX fk_managedobjects_objectypes ON openidm.managedobjects (objecttypes_id);
-- -----------------------------------------------------
-- Table openidm.managedobjectproperties
-- -----------------------------------------------------
CREATE TABLE openidm.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 openidm.managedobjects (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE INDEX fk_managedobjectproperties_managedobjects ON openidm.managedobjectproperties (managedobjects_id);
CREATE INDEX idx_managedobjectproperties_prop ON openidm.managedobjectproperties (propkey,propvalue);
-- -----------------------------------------------------
-- Table openidm.configobjects
-- -----------------------------------------------------
CREATE TABLE openidm.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 openidm.objecttypes (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE UNIQUE INDEX idx_configobjects_object ON openidm.configobjects (objecttypes_id,objectid);
CREATE INDEX fk_configobjects_objecttypes ON openidm.configobjects (objecttypes_id);
-- -----------------------------------------------------
-- Table openidm.configobjectproperties
-- -----------------------------------------------------
CREATE TABLE openidm.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 openidm.configobjects (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE INDEX fk_configobjectproperties_configobjects ON openidm.configobjectproperties (configobjects_id);
CREATE INDEX idx_configobjectproperties_prop ON openidm.configobjectproperties (propkey,propvalue);
-- -----------------------------------------------------
-- Table openidm.links
-- -----------------------------------------------------
CREATE TABLE openidm.links (
objectid VARCHAR(38) NOT NULL,
rev VARCHAR(38) NOT NULL,
linktype VARCHAR(510) NOT NULL,
linkqualifier VARCHAR(255) NOT NULL,
firstid VARCHAR(255) NOT NULL,
secondid VARCHAR(255) NOT NULL,
PRIMARY KEY (objectid)
);
CREATE UNIQUE INDEX idx_links_first ON openidm.links (linktype, linkqualifier, firstid);
CREATE UNIQUE INDEX idx_links_second ON openidm.links (linktype, linkqualifier, secondid);
-- -----------------------------------------------------
-- Table openidm.security
-- -----------------------------------------------------
CREATE TABLE openidm.security (
objectid VARCHAR(38) NOT NULL,
rev VARCHAR(38) NOT NULL,
storestring TEXT,
PRIMARY KEY (objectid)
);
-- -----------------------------------------------------
-- Table openidm.securitykeys
-- -----------------------------------------------------
CREATE TABLE openidm.securitykeys (
objectid VARCHAR(38) NOT NULL,
rev VARCHAR(38) NOT NULL,
keypair TEXT,
PRIMARY KEY (objectid)
);
-- -----------------------------------------------------
-- Table openidm.auditauthentication
-- -----------------------------------------------------
CREATE TABLE openidm.auditauthentication (
objectid VARCHAR(38) NOT NULL,
transactionid VARCHAR(56) 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,
sessionid VARCHAR(255),
entries TEXT,
PRIMARY KEY (objectid)
);
-- -----------------------------------------------------
-- Table openidm.auditaccess
-- -----------------------------------------------------
CREATE TABLE openidm.auditaccess (
objectid VARCHAR(38) NOT NULL,
activity VARCHAR(24) DEFAULT NULL,
activitydate VARCHAR(29) NOT NULL,
transactionid VARCHAR(56) NOT NULL,
eventname VARCHAR(255),
server_ip VARCHAR(40),
server_port VARCHAR(5),
client_host VARCHAR(255),
client_ip VARCHAR(40),
client_port VARCHAR(5),
userid VARCHAR(255) DEFAULT NULL,
principal TEXT NULL,
roles VARCHAR(1024) DEFAULT NULL,
auth_component VARCHAR(255) DEFAULT NULL,
resource_uri VARCHAR(255) DEFAULT NULL,
resource_protocol VARCHAR(10) DEFAULT NULL,
resource_method VARCHAR(10) DEFAULT NULL,
resource_detail VARCHAR(255) DEFAULT NULL,
http_method VARCHAR(10) DEFAULT NULL,
http_path VARCHAR(255) DEFAULT NULL,
http_querystring VARCHAR(255) DEFAULT NULL,
http_headers TEXT,
status VARCHAR(20) DEFAULT NULL,
elapsedtime VARCHAR(13) DEFAULT NULL,
PRIMARY KEY (objectid)
);
-- -----------------------------------------------------
-- Table openidm.auditactivity
-- -----------------------------------------------------
CREATE TABLE openidm.auditactivity (
objectid VARCHAR(38) NOT NULL,
activity VARCHAR(24) DEFAULT NULL,
activitydate VARCHAR(29) NOT NULL,
transactionid VARCHAR(56) NOT NULL,
eventname VARCHAR(255) DEFAULT NULL,
userid VARCHAR(255) DEFAULT NULL,
runas VARCHAR(255) DEFAULT NULL,
resource_uri VARCHAR(255) DEFAULT NULL,
resource_protocol VARCHAR(10) DEFAULT NULL,
resource_method VARCHAR(10) DEFAULT NULL,
resource_detail VARCHAR(255) DEFAULT NULL,
subjectbefore TEXT,
subjectafter TEXT,
changedfields VARCHAR(255) DEFAULT NULL,
passwordchanged VARCHAR(5) DEFAULT NULL,
subjectrev VARCHAR(255) DEFAULT NULL,
message TEXT,
activityobjectid VARCHAR(255),
status VARCHAR(20),
PRIMARY KEY (objectid),
);
CREATE INDEX idx_auditactivity_transactionid ON openidm.auditactivity (transactionid);
-- -----------------------------------------------------
-- Table openidm.auditrecon
-- -----------------------------------------------------
CREATE TABLE openidm.auditrecon (
objectid VARCHAR(38) NOT NULL,
transactionid VARCHAR(56) NOT NULL,
activitydate VARCHAR(29) NOT NULL,
eventname VARCHAR(50) DEFAULT NULL,
userid VARCHAR(255) DEFAULT NULL,
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)
);
-- -----------------------------------------------------
-- Table openidm.auditsync
-- -----------------------------------------------------
CREATE TABLE openidm.auditsync (
objectid VARCHAR(38) NOT NULL,
transactionid VARCHAR(56) NOT NULL,
activitydate VARCHAR(29) NOT NULL,
eventname VARCHAR(50) DEFAULT NULL,
userid VARCHAR(255) DEFAULT NULL,
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 openidm.internaluser
-- -----------------------------------------------------
CREATE TABLE openidm.internaluser (
objectid VARCHAR(254) NOT NULL,
rev VARCHAR(38) NOT NULL,
pwd VARCHAR(510) DEFAULT NULL,
roles VARCHAR(1024) DEFAULT NULL,
PRIMARY KEY (objectid)
);
-- -----------------------------------------------------
-- Table openidm.schedulerobjects
-- -----------------------------------------------------
CREATE TABLE openidm.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 openidm.objecttypes (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE UNIQUE INDEX idx_schedulerobjects_object ON openidm.schedulerobjects (objecttypes_id,objectid);
CREATE INDEX fk_schedulerobjects_objectypes ON openidm.schedulerobjects (objecttypes_id);
-- -----------------------------------------------------
-- Table openidm.schedulerobjectproperties
-- -----------------------------------------------------
CREATE TABLE openidm.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 openidm.schedulerobjects (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE INDEX fk_schedulerobjectproperties_schedulerobjects ON openidm.schedulerobjectproperties (schedulerobjects_id);
CREATE INDEX idx_schedulerobjectproperties_prop ON openidm.schedulerobjectproperties (propkey,propvalue);
-- -----------------------------------------------------
-- Table openidm.uinotification
-- -----------------------------------------------------
CREATE TABLE openidm.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 openidm.clusterobjects
-- -----------------------------------------------------
CREATE TABLE openidm.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 openidm.objecttypes (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE UNIQUE INDEX idx_clusterobjects_object ON openidm.clusterobjects (objecttypes_id,objectid);
CREATE INDEX fk_clusterobjects_objectypes ON openidm.clusterobjects (objecttypes_id);
-- -----------------------------------------------------
-- Table openidm.clusterobjectproperties
-- -----------------------------------------------------
CREATE TABLE openidm.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 openidm.clusterobjects (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE INDEX fk_clusterobjectproperties_clusterobjects ON openidm.clusterobjectproperties (clusterobjects_id);
CREATE INDEX idx_clusterobjectproperties_prop ON openidm.clusterobjectproperties (propkey,propvalue);
-- -----------------------------------------------------
-- Data for table openidm.internaluser
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO openidm.internaluser (objectid, rev, pwd, roles) VALUES ('openidm-admin', '0', 'openidm-admin', '["openidm-admin","openidm-authorized"]');
INSERT INTO openidm.internaluser (objectid, rev, pwd, roles) VALUES ('anonymous', '0', 'anonymous', '["openidm-reg"]');
COMMIT;
CREATE INDEX idx_json_clusterobjects_timestamp ON openidm.clusterobjects ( json_extract_path_text(fullobject, 'timestamp') );
CREATE INDEX idx_json_clusterobjects_state ON openidm.clusterobjects ( json_extract_path_text(fullobject, 'state') );