openidm.sql revision bf6542c5aa722eb97a0df17e6db46cc884f203bf
-- -----------------------------------------------------
-- Table `openidm`.`objecttypes`
-- -----------------------------------------------------
`objecttype` VARCHAR(255) NULL ,
-- -----------------------------------------------------
-- Table `openidm`.`genericobjects`
-- -----------------------------------------------------
`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)
-- -----------------------------------------------------
-- Table `openidm`.`genericobjectproperties`
-- -----------------------------------------------------
`genericobjects_id` BIGINT UNSIGNED NOT NULL ,
`propkey` VARCHAR(255) NOT NULL ,
`proptype` VARCHAR(32) NULL ,
`propvalue` VARCHAR(2000) NULL ,
CONSTRAINT `fk_genericobjectproperties_genericobjects`
FOREIGN KEY (`genericobjects_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION)
-- -----------------------------------------------------
-- Table `openidm`.`managedobjects`
-- -----------------------------------------------------
`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)
-- -----------------------------------------------------
-- Table `openidm`.`managedobjectproperties`
-- -----------------------------------------------------
`managedobjects_id` BIGINT UNSIGNED NOT NULL ,
`propkey` VARCHAR(255) NOT NULL ,
`proptype` VARCHAR(32) NULL ,
`propvalue` VARCHAR(2000) NULL ,
CONSTRAINT `fk_managedobjectproperties_managedobjects`
FOREIGN KEY (`managedobjects_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION)
-- -----------------------------------------------------
-- Table `openidm`.`configobjects`
-- -----------------------------------------------------
`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)
-- -----------------------------------------------------
-- Table `openidm`.`configobjectproperties`
-- -----------------------------------------------------
`configobjects_id` BIGINT UNSIGNED NOT NULL ,
`propkey` VARCHAR(255) NOT NULL ,
`proptype` VARCHAR(255) NULL ,
`propvalue` VARCHAR(2000) NULL ,
CONSTRAINT `fk_configobjectproperties_configobjects`
FOREIGN KEY (`configobjects_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION)
-- -----------------------------------------------------
-- 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 ,
-- -----------------------------------------------------
-- Table `openidm`.`security`
-- -----------------------------------------------------
`objectid` VARCHAR(38) NOT NULL ,
`rev` VARCHAR(38) NOT NULL ,
`storestring` LONGTEXT NOT NULL ,
-- -----------------------------------------------------
-- Table `openidm`.`securitykeys`
-- -----------------------------------------------------
`objectid` VARCHAR(38) NOT NULL ,
`rev` VARCHAR(38) NOT NULL ,
-- -----------------------------------------------------
-- Table `openidm`.`auditauthentication`
-- -----------------------------------------------------
`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) ,
)
-- -----------------------------------------------------
-- Table `openidm`.`auditrecon`
-- -----------------------------------------------------
`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 ,
-- -----------------------------------------------------
-- Table `openidm`.`auditsync`
-- -----------------------------------------------------
`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 ,
)
-- -----------------------------------------------------
-- Table `openidm`.`auditconfig`
-- -----------------------------------------------------
`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,
)
-- -----------------------------------------------------
-- Table `openidm`.`auditactivity`
-- -----------------------------------------------------
`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) ,
)
-- -----------------------------------------------------
-- Table `openidm`.`internaluser`
-- -----------------------------------------------------
`objectid` VARCHAR(254) NOT NULL ,
`rev` VARCHAR(38) NOT NULL ,
`pwd` VARCHAR(510) NULL ,
`roles` VARCHAR(1024) NULL ,
-- -----------------------------------------------------
-- Table `openidm`.`auditaccess`
-- -----------------------------------------------------
`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(10) NULL ,
`resource_detail` VARCHAR(255) NULL ,
`http_method` VARCHAR(10) NULL ,
`http_path` VARCHAR(255) NULL ,
`http_querystring` VARCHAR(255) NULL ,
`http_headers` TEXT ,
`status` VARCHAR(20) NULL ,
`elapsedtime` VARCHAR(13) NULL ,
-- -----------------------------------------------------
-- Table `openidm`.`schedulerobjects`
-- -----------------------------------------------------
`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)
-- -----------------------------------------------------
-- Table `openidm`.`schedulerobjectproperties`
-- -----------------------------------------------------
`schedulerobjects_id` BIGINT UNSIGNED NOT NULL ,
`propkey` VARCHAR(255) NOT NULL ,
`proptype` VARCHAR(32) NULL ,
`propvalue` VARCHAR(2000) NULL ,
CONSTRAINT `fk_schedulerobjectproperties_schedulerobjects`
FOREIGN KEY (`schedulerobjects_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION)
-- -----------------------------------------------------
-- Table `openidm`.`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`
-- -----------------------------------------------------
`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)
-- -----------------------------------------------------
-- Table `openidm`.`clusterobjectproperties`
-- -----------------------------------------------------
`clusterobjects_id` BIGINT UNSIGNED NOT NULL ,
`propkey` VARCHAR(255) NOT NULL ,
`proptype` VARCHAR(32) NULL ,
`propvalue` VARCHAR(2000) NULL ,
CONSTRAINT `fk_clusterobjectproperties_clusterobjects`
FOREIGN KEY (`clusterobjects_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION)
delimiter //
create procedure `openidm`.`getAllFromTable` (t_schema varchar(255), t_name varchar(255), order_by varchar(255), order_dir varchar(255), num_rows bigint, skip bigint, acceptable_order_by varchar(512))
begin
set @query = concat('select * from ', t_schema, '.', t_name ,' order by ', order_by ,' ', order_dir ,' limit ? offset ?');
END IF;
end //
SET SQL_MODE=@OLD_SQL_MODE;
-- -----------------------------------------------------
-- 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;
-- -------------------------------------------
-- openidm database user
-- ------------------------------------------