create_tables.sql

来自「找了很久才找到到源代码」· SQL 代码 · 共 564 行 · 第 1/2 页

USE ${database};

CREATE TABLE CMS_USERS (
    USER_ID NVARCHAR(36) NOT NULL,
    USER_NAME NVARCHAR(128) NOT NULL,
    USER_PASSWORD NVARCHAR(64) NOT NULL,
    USER_FIRSTNAME NVARCHAR(128) NOT NULL,
    USER_LASTNAME NVARCHAR(128) NOT NULL,
    USER_EMAIL NVARCHAR(128) NOT NULL,
    USER_LASTLOGIN BIGINT NOT NULL,
    USER_FLAGS INT NOT NULL,
    USER_OU NVARCHAR(128),
    USER_DATECREATED BIGINT NOT NULL,
    PRIMARY KEY (USER_ID), 
    UNIQUE (USER_OU, USER_NAME)
);

CREATE NONCLUSTERED INDEX CMS_USERS_01_IDX 
    ON CMS_USERS (USER_NAME);

CREATE NONCLUSTERED INDEX CMS_USERS_02_IDX 
    ON CMS_USERS (USER_OU);
        
CREATE TABLE CMS_USERDATA (
    USER_ID NVARCHAR(36) NOT NULL,
    DATA_KEY NVARCHAR(255) NOT NULL,
    DATA_VALUE IMAGE,
    DATA_TYPE NVARCHAR(128) NOT NULL,
    PRIMARY KEY (USER_ID, DATA_KEY)
);

CREATE NONCLUSTERED INDEX CMS_USERDATA_01_IDX 
    ON CMS_USERDATA (USER_ID);
    
CREATE NONCLUSTERED INDEX CMS_USERDATA_02_IDX 
    ON CMS_USERDATA (DATA_KEY);
    
CREATE TABLE CMS_HISTORY_PRINCIPALS (
    PRINCIPAL_ID NVARCHAR(36) NOT NULL,
    PRINCIPAL_NAME NVARCHAR(128) NOT NULL,
    PRINCIPAL_DESCRIPTION NVARCHAR(255) NOT NULL,
    PRINCIPAL_OU NVARCHAR(128),
    PRINCIPAL_EMAIL NVARCHAR(128) NOT NULL,
    PRINCIPAL_TYPE NVARCHAR(5) NOT NULL,
    PRINCIPAL_USERDELETED NVARCHAR(36) NOT NULL,
    PRINCIPAL_DATEDELETED BIGINT NOT NULL,
    PRIMARY KEY (PRINCIPAL_ID)
);

CREATE TABLE CMS_GROUPS (
    GROUP_ID NVARCHAR(36)  NOT NULL,
    PARENT_GROUP_ID NVARCHAR(36)  NOT NULL,
    GROUP_NAME NVARCHAR(128)  NOT NULL,
    GROUP_DESCRIPTION NVARCHAR(255) NOT NULL,
    GROUP_FLAGS INT NOT NULL,
    GROUP_OU NVARCHAR(128),
    PRIMARY KEY (GROUP_ID),
    UNIQUE (GROUP_OU, GROUP_NAME)
);

CREATE NONCLUSTERED INDEX CMS_GROUPS_01_IDX 
    ON CMS_GROUPS (GROUP_NAME);

CREATE NONCLUSTERED INDEX CMS_GROUPS_02_IDX 
    ON CMS_GROUPS (GROUP_OU);

CREATE NONCLUSTERED INDEX CMS_GROUPS_03_IDX 
    ON CMS_GROUPS (PARENT_GROUP_ID);
      
CREATE TABLE CMS_GROUPUSERS (
    GROUP_ID NVARCHAR(36)  NOT NULL,
    USER_ID NVARCHAR(36)  NOT NULL,
    GROUPUSER_FLAGS INT NOT NULL,
    PRIMARY KEY (GROUP_ID,USER_ID)
);

CREATE NONCLUSTERED INDEX CMS_GROUPUSERS_01_IDX 
    ON CMS_GROUPUSERS (GROUP_ID);
    
CREATE NONCLUSTERED INDEX CMS_GROUPUSERS_02_IDX 
    ON CMS_GROUPUSERS (USER_ID);

CREATE TABLE CMS_PROJECTS (
    PROJECT_ID NVARCHAR(36) NOT NULL,
    PROJECT_NAME NVARCHAR(200)  NOT NULL,
    PROJECT_DESCRIPTION NVARCHAR(255) NOT NULL,
    PROJECT_FLAGS INT NOT NULL,
    PROJECT_TYPE INT NOT NULL,
    USER_ID NVARCHAR(36)  NOT NULL,
    GROUP_ID NVARCHAR(36)  NOT NULL, 
    MANAGERGROUP_ID NVARCHAR(36)  NOT NULL,
    DATE_CREATED BIGINT NOT NULL,
    PROJECT_OU NVARCHAR(128) NOT NULL,
    PRIMARY KEY (PROJECT_ID), 
    UNIQUE (PROJECT_OU, PROJECT_NAME, DATE_CREATED)
);

CREATE NONCLUSTERED INDEX CMS_PROJECTS_01_IDX 
    ON CMS_PROJECTS (PROJECT_FLAGS);

CREATE NONCLUSTERED INDEX CMS_PROJECTS_02_IDX 
    ON CMS_PROJECTS (GROUP_ID);

CREATE NONCLUSTERED INDEX CMS_PROJECTS_03_IDX 
    ON CMS_PROJECTS (MANAGERGROUP_ID);
    
CREATE NONCLUSTERED INDEX CMS_PROJECTS_04_IDX 
    ON CMS_PROJECTS (PROJECT_OU, PROJECT_NAME);
    
CREATE NONCLUSTERED INDEX CMS_PROJECTS_05_IDX 
    ON CMS_PROJECTS (PROJECT_NAME);
    
CREATE NONCLUSTERED INDEX CMS_PROJECTS_06_IDX 
    ON CMS_PROJECTS (PROJECT_OU);

CREATE NONCLUSTERED INDEX CMS_PROJECTS_07_IDX 
    ON CMS_PROJECTS (USER_ID);
    
CREATE TABLE CMS_HISTORY_PROJECTS (
    PROJECT_ID NVARCHAR(36) NOT NULL,
    PROJECT_NAME NVARCHAR(255)  NOT NULL,
    PROJECT_DESCRIPTION NVARCHAR(255) NOT NULL,
    PROJECT_TYPE INT NOT NULL,
    USER_ID NVARCHAR(36)  NOT NULL,
    GROUP_ID NVARCHAR(36)  NOT NULL,
    MANAGERGROUP_ID NVARCHAR(36)  NOT NULL,
    DATE_CREATED BIGINT NOT NULL,    
    PUBLISH_TAG INT NOT NULL,
    PROJECT_PUBLISHDATE BIGINT,
    PROJECT_PUBLISHED_BY NVARCHAR(36) NOT NULL,
    PROJECT_OU NVARCHAR(128) NOT NULL,
    PRIMARY KEY (PUBLISH_TAG)
);

CREATE TABLE CMS_PROJECTRESOURCES (
    PROJECT_ID NVARCHAR(36) NOT NULL,
    RESOURCE_PATH NVARCHAR(511) NOT NULL,
    PRIMARY KEY (PROJECT_ID, RESOURCE_PATH)
);

CREATE NONCLUSTERED INDEX CMS_PROJECTRESOURCES_01_IDX 
    ON CMS_PROJECTRESOURCES (RESOURCE_PATH);

CREATE TABLE CMS_HISTORY_PROJECTRESOURCES (
    PUBLISH_TAG INT NOT NULL,
    PROJECT_ID NVARCHAR(36) NOT NULL,
    RESOURCE_PATH NVARCHAR(511) NOT NULL,
    PRIMARY KEY (PUBLISH_TAG, PROJECT_ID, RESOURCE_PATH)
);

CREATE TABLE CMS_OFFLINE_PROPERTYDEF (
    PROPERTYDEF_ID NVARCHAR(36)  NOT NULL, 
    PROPERTYDEF_NAME NVARCHAR(128)  NOT NULL,
    PROPERTYDEF_TYPE INT NOT NULL,
    PRIMARY KEY (PROPERTYDEF_ID), 
    UNIQUE (PROPERTYDEF_NAME)
);
                           
CREATE TABLE CMS_ONLINE_PROPERTYDEF (
    PROPERTYDEF_ID NVARCHAR(36)  NOT NULL, 
    PROPERTYDEF_NAME NVARCHAR(128)  NOT NULL,
    PROPERTYDEF_TYPE INT NOT NULL,
    PRIMARY KEY (PROPERTYDEF_ID), 
    UNIQUE (PROPERTYDEF_NAME)    
);
                                        
CREATE TABLE CMS_HISTORY_PROPERTYDEF (
    PROPERTYDEF_ID NVARCHAR(36)  NOT NULL, 
    PROPERTYDEF_NAME NVARCHAR(128)  NOT NULL,
    PROPERTYDEF_TYPE INT NOT NULL,
    PRIMARY KEY (PROPERTYDEF_ID), 
    UNIQUE (PROPERTYDEF_NAME)    
);

CREATE TABLE CMS_OFFLINE_PROPERTIES (
    PROPERTY_ID NVARCHAR(36)  NOT NULL,
    PROPERTYDEF_ID NVARCHAR(36)  NOT NULL,
    PROPERTY_MAPPING_ID NVARCHAR(36)  NOT NULL,
    PROPERTY_MAPPING_TYPE INT NOT NULL,
    PROPERTY_VALUE NTEXT NOT NULL,
    PRIMARY KEY (PROPERTY_ID),
    UNIQUE (PROPERTYDEF_ID, PROPERTY_MAPPING_ID)
);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_PROPERTIES_01_IDX 
    ON CMS_OFFLINE_PROPERTIES (PROPERTYDEF_ID);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_PROPERTIES_02_IDX 
    ON CMS_OFFLINE_PROPERTIES (PROPERTY_MAPPING_ID);
                                      
CREATE TABLE CMS_ONLINE_PROPERTIES (
    PROPERTY_ID NVARCHAR(36)  NOT NULL,
    PROPERTYDEF_ID NVARCHAR(36)  NOT NULL,
    PROPERTY_MAPPING_ID NVARCHAR(36)  NOT NULL,
    PROPERTY_MAPPING_TYPE INT NOT NULL,
    PROPERTY_VALUE NTEXT NOT NULL,
    PRIMARY KEY(PROPERTY_ID),
    UNIQUE (PROPERTYDEF_ID, PROPERTY_MAPPING_ID)
);

CREATE NONCLUSTERED INDEX CMS_ONLINE_PROPERTIES_01_IDX 
    ON CMS_ONLINE_PROPERTIES (PROPERTYDEF_ID);

CREATE NONCLUSTERED INDEX CMS_ONLINE_PROPERTIES_02_IDX 
    ON CMS_ONLINE_PROPERTIES (PROPERTY_MAPPING_ID);
                                                                         
CREATE TABLE CMS_HISTORY_PROPERTIES (
    STRUCTURE_ID NVARCHAR(36)  NOT NULL,
    PROPERTYDEF_ID NVARCHAR(36)  NOT NULL,
    PROPERTY_MAPPING_ID NVARCHAR(36)  NOT NULL,
    PROPERTY_MAPPING_TYPE INT NOT NULL,
    PROPERTY_VALUE NTEXT NOT NULL,
    PUBLISH_TAG INT,
    PRIMARY KEY(STRUCTURE_ID, PROPERTYDEF_ID, PROPERTY_MAPPING_TYPE, PUBLISH_TAG)
);

CREATE NONCLUSTERED INDEX CMS_HISTORY_PROPERTIES_01_IDX 
    ON CMS_HISTORY_PROPERTIES (PROPERTYDEF_ID);

CREATE NONCLUSTERED INDEX CMS_HISTORY_PROPERTIES_02_IDX 
    ON CMS_HISTORY_PROPERTIES (PROPERTY_MAPPING_ID);

CREATE NONCLUSTERED INDEX CMS_HISTORY_PROPERTIES_03_IDX 
    ON CMS_HISTORY_PROPERTIES (PROPERTYDEF_ID, PROPERTY_MAPPING_ID);
        
CREATE NONCLUSTERED INDEX CMS_HISTORY_PROPERTIES_04_IDX 
    ON CMS_HISTORY_PROPERTIES (STRUCTURE_ID,PUBLISH_TAG);
    
CREATE TABLE CMS_ONLINE_ACCESSCONTROL (
    RESOURCE_ID NVARCHAR(36)  NOT NULL,
    PRINCIPAL_ID NVARCHAR(36)  NOT NULL,
    ACCESS_ALLOWED INT,
    ACCESS_DENIED INT,
    ACCESS_FLAGS INT,
    PRIMARY KEY (RESOURCE_ID, PRINCIPAL_ID)
);

CREATE NONCLUSTERED INDEX CMS_ONLINE_ACCESSCONTROL_01_IDX 
    ON CMS_ONLINE_ACCESSCONTROL (PRINCIPAL_ID);
   
CREATE NONCLUSTERED INDEX CMS_ONLINE_ACCESSCONTROL_02_IDX 
    ON CMS_ONLINE_ACCESSCONTROL (RESOURCE_ID);
     
CREATE TABLE CMS_OFFLINE_ACCESSCONTROL (
    RESOURCE_ID NVARCHAR(36)  NOT NULL,
    PRINCIPAL_ID NVARCHAR(36)  NOT NULL,
    ACCESS_ALLOWED INT,
    ACCESS_DENIED INT,
    ACCESS_FLAGS INT,
    PRIMARY KEY (RESOURCE_ID, PRINCIPAL_ID)
);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_ACCESSCONTROL_01_IDX 
    ON CMS_OFFLINE_ACCESSCONTROL (PRINCIPAL_ID);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_ACCESSCONTROL_02_IDX 
    ON CMS_OFFLINE_ACCESSCONTROL (RESOURCE_ID);
        
CREATE TABLE CMS_PUBLISH_HISTORY (
    HISTORY_ID NVARCHAR(36)  NOT NULL,
    PUBLISH_TAG INT NOT NULL,
    STRUCTURE_ID NVARCHAR(36)  NOT NULL,
    RESOURCE_ID NVARCHAR(36)  NOT NULL,
    RESOURCE_PATH NVARCHAR(511) NOT NULL,
    RESOURCE_STATE INT NOT NULL,
    RESOURCE_TYPE INT NOT NULL,
    SIBLING_COUNT INT NOT NULL,
    UNIQUE (HISTORY_ID, PUBLISH_TAG, STRUCTURE_ID, RESOURCE_PATH)
);

CREATE NONCLUSTERED INDEX CMS_PUBLISH_HISTORY_01_IDX 
    ON CMS_PUBLISH_HISTORY (PUBLISH_TAG);

CREATE NONCLUSTERED INDEX CMS_PUBLISH_HISTORY_02_IDX 
    ON CMS_PUBLISH_HISTORY (HISTORY_ID);
        
CREATE TABLE CMS_PUBLISH_JOBS (
    HISTORY_ID NVARCHAR(36) NOT NULL,
    PROJECT_ID NVARCHAR(36) NOT NULL,
    PROJECT_NAME NVARCHAR(255) NOT NULL,
    USER_ID NVARCHAR(36) NOT NULL,
    PUBLISH_LOCALE NVARCHAR(16) NOT NULL,