openidm.sql revision 6275e78706a4a35b3c86b33be3d80f3539620923
`objecttype` VARCHAR(255) NULL ,
`objecttypes_id` BIGINT UNSIGNED NOT NULL ,
`objectid` VARCHAR(255) NOT NULL ,
`rev` VARCHAR(38) NOT NULL ,
`fullobject` MEDIUMTEXT NULL ,
CONSTRAINT `fk_genericobjects_objecttypes`
FOREIGN KEY (`objecttypes_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION) ;
`genericobjects_id` BIGINT UNSIGNED NOT NULL ,
`propkey` VARCHAR(255) NOT NULL ,
`proptype` VARCHAR(32) NULL ,
CONSTRAINT `fk_genericobjectproperties_genericobjects`
FOREIGN KEY (`genericobjects_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION);
CREATE INDEX IF NOT EXISTS `openidm`.`idx_genericobjectproperties_prop` ON `openidm`.`genericobjectproperties` (`propkey` ASC, `propvalue` ASC);
`objecttypes_id` BIGINT UNSIGNED NOT NULL ,
`objectid` VARCHAR(255) NOT NULL ,
`rev` VARCHAR(38) NOT NULL ,
`fullobject` MEDIUMTEXT NULL ,
CONSTRAINT `fk_managedobjects_objectypes`
FOREIGN KEY (`objecttypes_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION);
CREATE INDEX IF NOT EXISTS `openidm`.`fk_managedobjects_objectypes` ON `openidm`.`managedobjects` (`objecttypes_id` ASC) ;
`managedobjects_id` BIGINT UNSIGNED NOT NULL ,
`propkey` VARCHAR(255) NOT NULL ,
`proptype` VARCHAR(32) NULL ,
CONSTRAINT `fk_managedobjectproperties_managedobjects`
FOREIGN KEY (`managedobjects_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION);
CREATE INDEX IF NOT EXISTS `openidm`.`idx_managedobjectproperties_prop` ON `openidm`.`managedobjectproperties` (`propkey` ASC, `propvalue` ASC);
CREATE INDEX IF NOT EXISTS `openidm`.`fk_managedobjectproperties_managedobjects` ON `openidm`.`managedobjectproperties` (`managedobjects_id` ASC);
`objecttypes_id` BIGINT UNSIGNED NOT NULL ,
`objectid` VARCHAR(255) NOT NULL ,
`rev` VARCHAR(38) NOT NULL ,
`fullobject` MEDIUMTEXT NULL ,
CONSTRAINT `fk_configobjects_objecttypes`
FOREIGN KEY (`objecttypes_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION);
CREATE INDEX IF NOT EXISTS `openidm`.`fk_configobjects_objecttypes` ON `openidm`.`configobjects`(`objecttypes_id` ASC);
`configobjects_id` BIGINT UNSIGNED NOT NULL ,
`propkey` VARCHAR(255) NOT NULL ,
`proptype` VARCHAR(255) NULL ,
CONSTRAINT `fk_configobjectproperties_configobjects`
FOREIGN KEY (`configobjects_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION);
CREATE INDEX IF NOT EXISTS `openidm`.`fk_configobjectproperties_configobjects` ON `openidm`.`configobjectproperties`(`configobjects_id` ASC);
CREATE INDEX IF NOT EXISTS `openidm`.`idx_configobjectproperties_prop` ON `openidm`.`configobjectproperties`(`propkey` ASC, `propvalue` ASC);
`objecttypes_id` BIGINT UNSIGNED NOT NULL ,
`objectid` VARCHAR(255) NOT NULL ,
`rev` VARCHAR(38) NOT NULL ,
`fullobject` MEDIUMTEXT NULL ,
CONSTRAINT `fk_relationships_objecttypes`
FOREIGN KEY (`objecttypes_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION);
CREATE INDEX IF NOT EXISTS `openidm`.`fk_relationships_objecttypes` ON `openidm`.`relationships`(`objecttypes_id` ASC);
`relationships_id` BIGINT UNSIGNED NOT NULL ,
`propkey` VARCHAR(255) NOT NULL ,
`proptype` VARCHAR(255) NULL ,
CONSTRAINT `fk_relationshipproperties_relationships`
FOREIGN KEY (`relationships_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION);
CREATE INDEX IF NOT EXISTS `openidm`.`fk_relationshipproperties_relationships` ON `openidm`.`relationshipproperties`(`relationships_id` ASC);
CREATE INDEX IF NOT EXISTS `openidm`.`idx_relationshipproperties_prop` ON `openidm`.`relationshipproperties`(`propkey` ASC, `propvalue` ASC);
`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 ,
`objectid` VARCHAR(38) NOT NULL ,
`rev` VARCHAR(38) NOT NULL ,
`storestring` TEXT NULL ,
`objectid` VARCHAR(38) NOT NULL ,
`rev` VARCHAR(38) NOT NULL ,
`objectid` VARCHAR(38) NOT NULL ,
`transactionid` VARCHAR(56) NOT NULL ,
`userid` VARCHAR(255) NULL ,
`eventname` VARCHAR(50) NULL ,
`result` VARCHAR(255) NULL ,
`principals` TEXT ,
`sessionid` VARCHAR(255) ,
`objectid` VARCHAR(38) NOT NULL ,
`transactionid` VARCHAR(56) NOT NULL ,
`eventname` VARCHAR(50) NULL ,
`userid` VARCHAR(255) NULL ,
`activity` VARCHAR(24) NULL ,
`exceptiondetail` TEXT NULL ,
`linkqualifier` VARCHAR(255) NULL ,
`mapping` VARCHAR(511) NULL ,
`messagedetail` MEDIUMTEXT NULL ,
`situation` VARCHAR(24) NULL ,
`sourceobjectid` VARCHAR(511) NULL ,
`status` VARCHAR(20) NULL ,
`targetobjectid` VARCHAR(511) NULL ,
`reconciling` VARCHAR(12) NULL ,
`ambiguoustargetobjectids` MEDIUMTEXT NULL ,
`reconaction` VARCHAR(36) NULL ,
`entrytype` VARCHAR(7) NULL ,
`reconid` VARCHAR(56) NULL ,
CREATE INDEX IF NOT EXISTS `openidm`.`idx_auditrecon_transactionid` ON `openidm`.`auditrecon` (`transactionid` ASC);
CREATE INDEX IF NOT EXISTS `openidm`.`idx_auditrecon_targetobjectid` ON `openidm`.`auditrecon`(`targetobjectid` ASC);
CREATE INDEX IF NOT EXISTS `openidm`.`idx_auditrecon_sourceobjectid` ON `openidm`.`auditrecon`(`sourceobjectid` ASC);
CREATE INDEX IF NOT EXISTS `openidm`.`idx_auditrecon_activitydate` ON `openidm`.`auditrecon`(`activitydate` ASC);
CREATE INDEX IF NOT EXISTS `openidm`.`idx_auditrecon_entrytype` ON `openidm`.`auditrecon`(`entrytype` ASC);
CREATE INDEX IF NOT EXISTS `openidm`.`idx_auditrecon_situation` ON `openidm`.`auditrecon`(`situation` ASC);
CREATE INDEX IF NOT EXISTS `openidm`.`idx_auditrecon_status` ON `openidm`.`auditrecon`(`status` ASC);
`objectid` VARCHAR(38) NOT NULL ,
`transactionid` VARCHAR(56) NOT NULL ,
`eventname` VARCHAR(50) NULL ,
`userid` VARCHAR(255) NULL ,
`activity` VARCHAR(24) NULL ,
`exceptiondetail` TEXT NULL ,
`linkqualifier` VARCHAR(255) NULL ,
`mapping` VARCHAR(511) NULL ,
`messagedetail` MEDIUMTEXT NULL ,
`situation` VARCHAR(24) NULL ,
`sourceobjectid` VARCHAR(511) NULL ,
`status` VARCHAR(20) NULL ,
`targetobjectid` VARCHAR(511) NULL ,
`objectid` VARCHAR(38) NOT NULL ,
`transactionid` VARCHAR(56) NOT NULL ,
`eventname` VARCHAR(255) NULL ,
`userid` VARCHAR(255) NULL ,
`runas` VARCHAR(255) NULL ,
`resource_uri` VARCHAR(255) NULL ,
`resource_protocol` VARCHAR(10) NULL ,
`resource_method` VARCHAR(10) NULL ,
`resource_detail` VARCHAR(255) NULL ,
`before` MEDIUMTEXT NULL ,
`after` MEDIUMTEXT NULL ,
`changedfields` VARCHAR(255) NULL ,
`rev` VARCHAR(255) NULL ,
CREATE INDEX IF NOT EXISTS `openidm`.`idx_auditconfig_transactionid` ON `openidm`.`auditconfig`(`transactionid` ASC);
`objectid` VARCHAR(38) NOT NULL ,
`activity` VARCHAR(24) NULL ,
`transactionid` VARCHAR(56) NOT NULL ,
`eventname` VARCHAR(255) NULL ,
`userid` VARCHAR(255) NULL ,
`runas` VARCHAR(255) NULL ,
`resource_uri` VARCHAR(255) NULL ,
`resource_protocol` VARCHAR(10) NULL ,
`resource_method` VARCHAR(10) NULL ,
`resource_detail` VARCHAR(255) NULL ,
`subjectbefore` MEDIUMTEXT NULL ,
`subjectafter` MEDIUMTEXT NULL ,
`changedfields` VARCHAR(255) NULL ,
`passwordchanged` VARCHAR(5) NULL ,
`subjectrev` VARCHAR(255) NULL ,
`activityobjectid` VARCHAR(255) ,
`status` VARCHAR(20) ,
CREATE INDEX IF NOT EXISTS `openidm`.`idx_auditactivity_transactionid` ON `openidm`.`auditactivity`(`transactionid` ASC);
`objectid` VARCHAR(255) NOT NULL ,
`rev` VARCHAR(38) NOT NULL ,
`pwd` VARCHAR(510) NULL ,
`roles` VARCHAR(1024) NULL ,
`objectid` VARCHAR(255) NOT NULL ,
`rev` VARCHAR(38) NOT NULL ,
`description` VARCHAR(510) NULL,
`objectid` VARCHAR(38) NOT NULL ,
`activity` VARCHAR(24) 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) NULL ,
`roles` VARCHAR(1024) NULL ,
`auth_component` VARCHAR(255) NULL ,
`resource_uri` VARCHAR(255) NULL ,
`resource_protocol` VARCHAR(10) NULL ,
`resource_method` VARCHAR(14) NULL ,
`resource_detail` VARCHAR(255) NULL ,
`http_method` VARCHAR(10) NULL ,
`http_path` VARCHAR(255) NULL ,
`http_querystring` TEXT NULL ,
`http_headers` TEXT ,
`status` VARCHAR(20) NULL ,
`elapsedtime` VARCHAR(13) NULL ,
`objecttypes_id` BIGINT UNSIGNED NOT NULL ,
`objectid` VARCHAR(255) NOT NULL ,
`rev` VARCHAR(38) NOT NULL ,
`fullobject` MEDIUMTEXT NULL ,
CONSTRAINT `fk_schedulerobjects_objectypes`
FOREIGN KEY (`objecttypes_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION);
CREATE INDEX IF NOT EXISTS `openidm`.`fk_schedulerobjects_objectypes` ON `openidm`.`schedulerobjects` (`objecttypes_id` ASC);
`schedulerobjects_id` BIGINT UNSIGNED NOT NULL ,
`propkey` VARCHAR(255) NOT NULL ,
`proptype` VARCHAR(32) NULL ,
CONSTRAINT `fk_schedulerobjectproperties_schedulerobjects`
FOREIGN KEY (`schedulerobjects_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION);
CREATE INDEX IF NOT EXISTS `openidm`.`fk_schedulerobjectproperties_schedulerobjects` ON `openidm`.`schedulerobjectproperties`(`schedulerobjects_id` ASC);
CREATE INDEX IF NOT EXISTS `openidm`.`idx_schedulerobjectproperties_prop` ON `openidm`.`schedulerobjectproperties`(`propkey` ASC, `propvalue` ASC);
`objecttypes_id` BIGINT UNSIGNED NOT NULL ,
`objectid` VARCHAR(255) NOT NULL ,
`rev` VARCHAR(38) NOT NULL ,
`fullobject` MEDIUMTEXT NULL ,
CONSTRAINT `fk_clusterobjects_objectypes`
FOREIGN KEY (`objecttypes_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION);
CREATE INDEX IF NOT EXISTS `openidm`.`fk_clusterobjects_objectypes` ON `openidm`.`clusterobjects` (`objecttypes_id` ASC);
`clusterobjects_id` BIGINT UNSIGNED NOT NULL ,
`propkey` VARCHAR(255) NOT NULL ,
`proptype` VARCHAR(32) NULL ,
CONSTRAINT `fk_clusterobjectproperties_clusterobjects`
FOREIGN KEY (`clusterobjects_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION);
CREATE INDEX IF NOT EXISTS `fk_clusterobjectproperties_clusterobjects` ON `openidm`.`clusterobjectproperties` (`clusterobjects_id` ASC);
CREATE INDEX IF NOT EXiSTS `idx_clusterobjectproperties_prop` ON `openidm`.`clusterobjectproperties` (`propkey` ASC, `propvalue` ASC);
`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 ,
`status` VARCHAR(64) NOT NULL ,
`statusMessage` VARCHAR(256) NOT NULL ,
`completedTasks` INT DEFAULT 0,
`totalTasks` INT DEFAULT 0,
`startDate` VARCHAR(29) NOT NULL,
`endDate` VARCHAR(29) NULL,
`userName` VARCHAR(256) NOT NULL,
`nodeId` VARCHAR(64) NULL ,
`fileState` VARCHAR(64) NOT NULL ,
`backupFile` TEXT NULL ,
CONSTRAINT `fk_updatefileupdatesid_updatesid`
ON DELETE CASCADE
ON UPDATE NO ACTION);
SELECT 'openidm-admin', '0', 'openidm-admin', '["openidm-admin","openidm-authorized"]'
SELECT 'anonymous', '0', 'anonymous', '["openidm-reg"]'
SELECT 'openidm-authorized', '0', 'Basic minimum user'
UNION
SELECT 'openidm-admin', '0', 'Administrative access'
UNION
SELECT 'openidm-cert', '0', 'Authenticated via certificate'
UNION
SELECT 'openidm-tasks-manager', '0', 'Allowed to reassign workflow tasks'
WHERE NOT EXISTS (SELECT * FROM `openidm`.`internalrole` WHERE `objectid` = 'openidm-tasks-manager')
UNION
SELECT 'openidm-reg', '0', 'Anonymous access'