openidm.sql revision 16b75fb7974653c48d7b2a3b1d6c53e13f4897f5
-- -----------------------------------------------------
-- Database OpenIDM - case-sensitive and accent-sensitive
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Login openidm
-- Login openidm_proxy
-- -----------------------------------------------------
IF (NOT EXISTS (select loginname from master.dbo.syslogins where name = N'openidm' and dbname = N'openidm'))
CREATE LOGIN [openidm] WITH PASSWORD=N'Passw0rd', DEFAULT_DATABASE=[openidm], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
IF (NOT EXISTS (select loginname from master.dbo.syslogins where name = N'openidm_proxy' and dbname = N'openidm'))
CREATE LOGIN [openidm_proxy] WITH PASSWORD=N'Passw0rd', DEFAULT_DATABASE=[openidm], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
-- -----------------------------------------------------
-- User openidm - Database owner user
-- User openidm_proxy - More restricted user to access the data only
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema openidm
-- -----------------------------------------------------
BEGIN TRANSACTION
-- -----------------------------------------------------
-- Table `openidm`.`objecttypes`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[objecttypes]
(
);
END
-- -----------------------------------------------------
-- Table `openidm`.`genericobjects`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[genericobjects]
(
fullobject NTEXT NULL ,
CONSTRAINT fk_genericobjects_objecttypes
FOREIGN KEY (objecttypes_id)
ON DELETE CASCADE
ON UPDATE NO ACTION,
);
CREATE UNIQUE INDEX idx_genericobjects_object ON [openidm].[genericobjects] (objecttypes_id ASC, objectid ASC);
END
-- -----------------------------------------------------
-- Table `openidm`.`genericobjectproperties`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[genericobjectproperties]
(
CONSTRAINT fk_genericobjectproperties_genericobjects
FOREIGN KEY (genericobjects_id)
ON DELETE CASCADE
ON UPDATE NO ACTION
);
ALTER TABLE [openidm].[genericobjectproperties] ADD partpropvalue AS CAST(propvalue AS VARCHAR(255)) persisted;
CREATE INDEX fk_genericobjectproperties_genericobjects ON [openidm].[genericobjectproperties] (genericobjects_id ASC);
CREATE INDEX idx_genericobjectproperties_prop ON [openidm].[genericobjectproperties] (propkey ASC, partpropvalue ASC);
END
-- -----------------------------------------------------
-- Table `openidm`.`managedobjects`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[managedobjects]
(
fullobject NTEXT NULL ,
CONSTRAINT fk_managedobjects_objectypes
FOREIGN KEY (objecttypes_id)
ON DELETE CASCADE
ON UPDATE NO ACTION,
);
CREATE UNIQUE INDEX idx_managedobjects_object ON [openidm].[managedobjects] (objecttypes_id ASC, objectid ASC);
END
-- -----------------------------------------------------
-- Table `openidm`.`managedobjectproperties`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[managedobjectproperties]
(
CONSTRAINT fk_managedobjectproperties_managedobjects
FOREIGN KEY (managedobjects_id)
ON DELETE CASCADE
ON UPDATE NO ACTION
);
ALTER TABLE [openidm].[managedobjectproperties] ADD partpropvalue AS CAST(propvalue AS VARCHAR(255)) persisted;
CREATE INDEX fk_managedobjectproperties_managedobjects ON [openidm].[managedobjectproperties] (managedobjects_id ASC);
CREATE INDEX idx_managedobjectproperties_prop ON [openidm].[managedobjectproperties] (propkey ASC, partpropvalue ASC);
END
-- -----------------------------------------------------
-- Table `openidm`.`configobjects`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[configobjects]
(
fullobject NTEXT NULL ,
CONSTRAINT fk_configobjects_objecttypes
FOREIGN KEY (objecttypes_id)
ON DELETE CASCADE
ON UPDATE NO ACTION,
);
CREATE UNIQUE INDEX idx_configobjects_object ON [openidm].[configobjects] (objecttypes_id ASC, objectid ASC);
END
-- -----------------------------------------------------
-- Table `openidm`.`configobjectproperties`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[configobjectproperties] (
CONSTRAINT fk_configobjectproperties_configobjects
FOREIGN KEY (configobjects_id)
ON DELETE CASCADE
ON UPDATE NO ACTION
);
ALTER TABLE [openidm].[configobjectproperties] ADD partpropvalue AS CAST(propvalue AS VARCHAR(255)) persisted;
CREATE INDEX fk_configobjectproperties_configobjects ON [openidm].[configobjectproperties] (configobjects_id ASC);
CREATE INDEX idx_configobjectproperties_prop ON [openidm].[configobjectproperties] (propkey ASC, partpropvalue ASC);
END
-- -----------------------------------------------------
-- Table `openidm`.`links`
-- -----------------------------------------------------
BEGIN
(
);
END
-- -----------------------------------------------------
-- Table `openidm`.`security`
-- -----------------------------------------------------
BEGIN
(
storestring NTEXT NOT NULL ,
);
END
-- -----------------------------------------------------
-- Table `openidm`.`securitykeys`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[securitykeys]
(
);
END
-- -----------------------------------------------------
-- Table `openidm`.`auditrecon`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[auditrecon]
(
ambiguoustargetobjectids NTEXT NULL ,
exceptiondetail NTEXT NULL ,
messagedetail NTEXT NULL ,
);
EXEC sp_addextendedproperty 'MS_Description', 'Date format: 2011-09-09T14:58:17.654+02:00', 'SCHEMA', openidm, 'TABLE', auditrecon, 'COLUMN', activitydate;
END
-- -----------------------------------------------------
-- Table `openidm`.`auditsync`
-- -----------------------------------------------------
BEGIN
(
exceptiondetail NTEXT NULL ,
messagedetail NTEXT NULL ,
);
EXEC sp_addextendedproperty 'MS_Description', 'Date format: 2011-09-09T14:58:17.654+02:00', 'SCHEMA', openidm, 'TABLE', auditsync, 'COLUMN', activitydate;
END
-- -----------------------------------------------------
-- Table `openidm`.`auditactivity`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[auditactivity]
(
subjectbefore NTEXT NULL ,
subjectafter NTEXT NULL ,
);
EXEC sp_addextendedproperty 'MS_Description', 'Date format: 2011-09-09T14:58:17.654+02:00', 'SCHEMA', openidm, 'TABLE', auditactivity, 'COLUMN', activitydate;
END
-- -----------------------------------------------------
-- Table `openidm`.`internaluser`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[internaluser]
(
);
END
-- -----------------------------------------------------
-- Table `openidm`.`auditaccess`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[auditaccess] (
);
EXEC sp_addextendedproperty 'MS_Description', 'Date format: 2011-09-09T14:58:17.654+02:00', 'SCHEMA', openidm, 'TABLE', auditaccess, 'COLUMN', activitydate;
END
-- -----------------------------------------------------
-- Table `openidm`.`schedulerobjects`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[schedulerobjects]
(
fullobject NTEXT NULL ,
CONSTRAINT fk_schedulerobjects_objecttypes
FOREIGN KEY (objecttypes_id)
ON DELETE CASCADE
ON UPDATE NO ACTION,
);
CREATE UNIQUE INDEX idx_schedulerobjects_object ON [openidm].[schedulerobjects] (objecttypes_id ASC, objectid ASC);
END
-- -----------------------------------------------------
-- Table `openidm`.`schedulerobjectproperties`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[schedulerobjectproperties] (
FOREIGN KEY (schedulerobjects_id)
ON DELETE CASCADE
ON UPDATE NO ACTION
);
ALTER TABLE [openidm].[schedulerobjectproperties] ADD partpropvalue AS CAST(propvalue AS VARCHAR(255)) persisted;
CREATE INDEX fk_schedulerobjectproperties_schedulerobjects ON [openidm].[schedulerobjectproperties] (schedulerobjects_id ASC);
CREATE INDEX idx_schedulerobjectproperties_prop ON [openidm].[schedulerobjectproperties] (propkey ASC, partpropvalue ASC);
END
-- -----------------------------------------------------
-- Table `openidm`.`clusterobjects`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[clusterobjects]
(
fullobject NTEXT NULL ,
CONSTRAINT fk_clusterobjects_objecttypes
FOREIGN KEY (objecttypes_id)
ON DELETE CASCADE
ON UPDATE NO ACTION,
);
CREATE UNIQUE INDEX idx_clusterobjects_object ON [openidm].[clusterobjects] (objecttypes_id ASC, objectid ASC);
END
-- -----------------------------------------------------
-- Table `openidm`.`clusterobjectproperties`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[clusterobjectproperties] (
CONSTRAINT fk_clusterobjectproperties_clusterobjects
FOREIGN KEY (clusterobjects_id)
ON DELETE CASCADE
ON UPDATE NO ACTION
);
ALTER TABLE [openidm].[clusterobjectproperties] ADD partpropvalue AS CAST(propvalue AS VARCHAR(255)) persisted;
CREATE INDEX fk_clusterobjectproperties_clusterobjects ON [openidm].[clusterobjectproperties] (clusterobjects_id ASC);
CREATE INDEX idx_clusterobjectproperties_prop ON [openidm].[clusterobjectproperties] (propkey ASC, partpropvalue ASC);
END
-- -----------------------------------------------------
-- Table `openidm`.`uinotification`
-- -----------------------------------------------------
BEGIN
CREATE TABLE [openidm].[uinotification] (
);
EXEC sp_addextendedproperty 'MS_Description', 'Date format: 2011-09-09T14:58:17.654+02:00', 'SCHEMA', openidm, 'TABLE', uinotification, 'COLUMN', createdate;
END
-- -----------------------------------------------------
-- Data for table `openidm`.`internaluser`
-- -----------------------------------------------------
INSERT INTO [openidm].[internaluser] (objectid, rev, pwd, roles) VALUES (N'openidm-admin', '0', 'openidm-admin', 'openidm-admin,openidm-authorized');
INSERT INTO [openidm].[internaluser] (objectid, rev, pwd, roles) VALUES ('anonymous', '0', 'anonymous', 'openidm-reg');
COMMIT