create_tables.sql

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

    PUBLISH_FLAGS INT NOT NULL,
    PUBLISH_LIST IMAGE,
    PUBLISH_REPORT IMAGE,
    RESOURCE_COUNT INT NOT NULL,
    ENQUEUE_TIME BIGINT NOT NULL,
    START_TIME BIGINT NOT NULL,
    FINISH_TIME BIGINT NOT NULL,
    PRIMARY KEY(HISTORY_ID)
);

CREATE TABLE CMS_RESOURCE_LOCKS (
  RESOURCE_PATH TEXT NOT NULL,
  USER_ID NVARCHAR(36) NOT NULL,
  PROJECT_ID NVARCHAR(36) NOT NULL,
  LOCK_TYPE INT NOT NULL
);

CREATE TABLE CMS_STATICEXPORT_LINKS (
    LINK_ID NVARCHAR(36)  NOT NULL,
    LINK_RFS_PATH NVARCHAR(511) NOT NULL,
    LINK_TYPE INT NOT NULL,
    LINK_PARAMETER TEXT,
    LINK_TIMESTAMP BIGINT,	
    PRIMARY KEY (LINK_ID)    
);

CREATE NONCLUSTERED INDEX CMS_STATICEXPORT_LINKS_01_IDX 
    ON CMS_STATICEXPORT_LINKS (LINK_RFS_PATH);
    
CREATE TABLE CMS_OFFLINE_STRUCTURE (
    STRUCTURE_ID NVARCHAR(36)  NOT NULL,
    RESOURCE_ID NVARCHAR(36)  NOT NULL,
    PARENT_ID NVARCHAR(36)  NOT NULL,
    RESOURCE_PATH NVARCHAR(511)NOT NULL,
    STRUCTURE_STATE INT NOT NULL,
    DATE_RELEASED BIGINT NOT NULL,
    DATE_EXPIRED BIGINT NOT NULL,
    STRUCTURE_VERSION INT NOT NULL,
    PRIMARY KEY (STRUCTURE_ID)
);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_STRUCTURE_01_IDX 
    ON CMS_OFFLINE_STRUCTURE (STRUCTURE_ID, RESOURCE_PATH);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_STRUCTURE_02_IDX 
    ON CMS_OFFLINE_STRUCTURE (RESOURCE_PATH, RESOURCE_ID);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_STRUCTURE_03_IDX 
    ON CMS_OFFLINE_STRUCTURE (STRUCTURE_ID, RESOURCE_ID);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_STRUCTURE_04_IDX 
    ON CMS_OFFLINE_STRUCTURE (STRUCTURE_STATE);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_STRUCTURE_05_IDX 
    ON CMS_OFFLINE_STRUCTURE (PARENT_ID);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_STRUCTURE_06_IDX 
    ON CMS_OFFLINE_STRUCTURE (RESOURCE_PATH);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_STRUCTURE_07_IDX 
    ON CMS_OFFLINE_STRUCTURE (RESOURCE_ID);
                            
CREATE TABLE CMS_ONLINE_STRUCTURE (
    STRUCTURE_ID NVARCHAR(36)  NOT NULL,
    RESOURCE_ID NVARCHAR(36)  NOT NULL,
    PARENT_ID NVARCHAR(36)  NOT NULL,
    RESOURCE_PATH NVARCHAR(511) NOT NULL,
    STRUCTURE_STATE INT NOT NULL,
    DATE_RELEASED BIGINT NOT NULL,
    DATE_EXPIRED BIGINT NOT NULL,
    STRUCTURE_VERSION INT NOT NULL,
    PRIMARY KEY (STRUCTURE_ID)
);

CREATE NONCLUSTERED INDEX CMS_ONLINE_STRUCTURE_01_IDX 
    ON CMS_ONLINE_STRUCTURE (STRUCTURE_ID, RESOURCE_PATH);

CREATE NONCLUSTERED INDEX CMS_ONLINE_STRUCTURE_02_IDX 
    ON CMS_ONLINE_STRUCTURE (RESOURCE_PATH, RESOURCE_ID);

CREATE NONCLUSTERED INDEX CMS_ONLINE_STRUCTURE_03_IDX 
    ON CMS_ONLINE_STRUCTURE (STRUCTURE_ID, RESOURCE_ID);

CREATE NONCLUSTERED INDEX CMS_ONLINE_STRUCTURE_04_IDX 
    ON CMS_ONLINE_STRUCTURE (STRUCTURE_STATE);
    
CREATE NONCLUSTERED INDEX CMS_ONLINE_STRUCTURE_05_IDX 
    ON CMS_ONLINE_STRUCTURE (PARENT_ID);

CREATE NONCLUSTERED INDEX CMS_ONLINE_STRUCTURE_06_IDX 
    ON CMS_ONLINE_STRUCTURE (RESOURCE_PATH);

CREATE NONCLUSTERED INDEX CMS_ONLINE_STRUCTURE_07_IDX 
    ON CMS_ONLINE_STRUCTURE (RESOURCE_ID);
            
CREATE TABLE CMS_HISTORY_STRUCTURE (
    PUBLISH_TAG INT NOT NULL,
    VERSION INT NOT NULL,
    STRUCTURE_ID NVARCHAR(36)  NOT NULL,
    RESOURCE_ID NVARCHAR(36)  NOT NULL,
    PARENT_ID NVARCHAR(36) NOT NULL,
    RESOURCE_PATH NVARCHAR(511) NOT NULL,
    STRUCTURE_STATE INT NOT NULL,
    DATE_RELEASED BIGINT NOT NULL,
    DATE_EXPIRED BIGINT NOT NULL,
    STRUCTURE_VERSION INT NOT NULL,
    PRIMARY KEY (STRUCTURE_ID, PUBLISH_TAG, VERSION)
);

CREATE NONCLUSTERED INDEX CMS_HISTORY_STRUCTURE_01_IDX 
    ON CMS_HISTORY_STRUCTURE (STRUCTURE_ID);

CREATE NONCLUSTERED INDEX CMS_HISTORY_STRUCTURE_02_IDX 
    ON CMS_HISTORY_STRUCTURE (RESOURCE_PATH);

CREATE NONCLUSTERED INDEX CMS_HISTORY_STRUCTURE_03_IDX 
    ON CMS_HISTORY_STRUCTURE (PUBLISH_TAG);

CREATE NONCLUSTERED INDEX CMS_HISTORY_STRUCTURE_04_IDX 
    ON CMS_HISTORY_STRUCTURE (VERSION);
            
CREATE TABLE CMS_OFFLINE_RESOURCES (
    RESOURCE_ID NVARCHAR(36)  NOT NULL,
    RESOURCE_TYPE INT NOT NULL,
    RESOURCE_FLAGS INT NOT NULL,
    RESOURCE_STATE    INT NOT NULL,
    RESOURCE_SIZE INT NOT NULL,                                         
    DATE_CONTENT BIGINT NOT NULL,                                             
    SIBLING_COUNT INT NOT NULL,
    DATE_CREATED BIGINT NOT NULL,
    DATE_LASTMODIFIED BIGINT NOT NULL,
    USER_CREATED NVARCHAR(36)  NOT NULL,                                         
    USER_LASTMODIFIED NVARCHAR(36)  NOT NULL,
    PROJECT_LASTMODIFIED NVARCHAR(36) NULL,          
    RESOURCE_VERSION INT NOT NULL,
    PRIMARY KEY(RESOURCE_ID)
);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_RESOURCES_01_IDX 
    ON CMS_OFFLINE_RESOURCES (PROJECT_LASTMODIFIED);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_RESOURCES_02_IDX 
    ON CMS_OFFLINE_RESOURCES (PROJECT_LASTMODIFIED, RESOURCE_SIZE);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_RESOURCES_03_IDX 
    ON CMS_OFFLINE_RESOURCES (RESOURCE_SIZE);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_RESOURCES_04_IDX 
    ON CMS_OFFLINE_RESOURCES (DATE_LASTMODIFIED);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_RESOURCES_05_IDX 
    ON CMS_OFFLINE_RESOURCES (RESOURCE_TYPE);
                    
CREATE TABLE CMS_ONLINE_RESOURCES (
    RESOURCE_ID NVARCHAR(36)  NOT NULL,
    RESOURCE_TYPE INT NOT NULL,
    RESOURCE_FLAGS INT NOT NULL,
    RESOURCE_STATE    INT NOT NULL,
    RESOURCE_SIZE INT NOT NULL,
    DATE_CONTENT BIGINT NOT NULL,                                             
    SIBLING_COUNT INT NOT NULL,    
    DATE_CREATED BIGINT NOT NULL,
    DATE_LASTMODIFIED BIGINT NOT NULL,
    USER_CREATED NVARCHAR(36)  NOT NULL,                                         
    USER_LASTMODIFIED NVARCHAR(36)  NOT NULL,
    PROJECT_LASTMODIFIED NVARCHAR(36) NULL,
    RESOURCE_VERSION INT NOT NULL,
    PRIMARY KEY(RESOURCE_ID)
);

CREATE NONCLUSTERED INDEX CMS_ONLINE_RESOURCES_01_IDX 
    ON CMS_ONLINE_RESOURCES (PROJECT_LASTMODIFIED);

CREATE NONCLUSTERED INDEX CMS_ONLINE_RESOURCES_02_IDX 
    ON CMS_ONLINE_RESOURCES (PROJECT_LASTMODIFIED, RESOURCE_SIZE);

CREATE NONCLUSTERED INDEX CMS_ONLINE_RESOURCES_03_IDX 
    ON CMS_ONLINE_RESOURCES (RESOURCE_SIZE);

CREATE NONCLUSTERED INDEX CMS_ONLINE_RESOURCES_04_IDX 
    ON CMS_ONLINE_RESOURCES (DATE_LASTMODIFIED);

CREATE NONCLUSTERED INDEX CMS_ONLINE_RESOURCES_05_IDX 
    ON CMS_ONLINE_RESOURCES (RESOURCE_TYPE);
                                                           
CREATE TABLE CMS_HISTORY_RESOURCES (
    RESOURCE_ID NVARCHAR(36)  NOT NULL,
    RESOURCE_TYPE INT NOT NULL,
    RESOURCE_FLAGS INT NOT NULL,
    RESOURCE_STATE    INT NOT NULL,
    RESOURCE_SIZE INT NOT NULL,
    DATE_CONTENT BIGINT NOT NULL,                                             
    SIBLING_COUNT INT NOT NULL,    
    DATE_CREATED BIGINT NOT NULL,
    DATE_LASTMODIFIED BIGINT NOT NULL,
    USER_CREATED NVARCHAR(36)  NOT NULL,
    USER_LASTMODIFIED NVARCHAR(36)  NOT NULL,
    PROJECT_LASTMODIFIED NVARCHAR(36) NOT NULL,
    PUBLISH_TAG INT NOT NULL,
    RESOURCE_VERSION INT NOT NULL,
    PRIMARY KEY(RESOURCE_ID, PUBLISH_TAG)
);

CREATE NONCLUSTERED INDEX CMS_HISTORY_RESOURCES_01_IDX 
    ON CMS_HISTORY_RESOURCES (RESOURCE_ID);

CREATE NONCLUSTERED INDEX CMS_HISTORY_RESOURCES_02_IDX 
    ON CMS_HISTORY_RESOURCES (PUBLISH_TAG);
    
CREATE TABLE CMS_OFFLINE_CONTENTS (
    RESOURCE_ID NVARCHAR(36)  NOT NULL,
    FILE_CONTENT IMAGE NOT NULL,
    PRIMARY KEY(RESOURCE_ID)
);

CREATE TABLE CMS_CONTENTS (
    RESOURCE_ID NVARCHAR(36)  NOT NULL,
    FILE_CONTENT IMAGE NOT NULL,
    PUBLISH_TAG_FROM INT,
    PUBLISH_TAG_TO INT,
    ONLINE_FLAG INT,
    PRIMARY KEY(RESOURCE_ID, PUBLISH_TAG_FROM),
    UNIQUE (RESOURCE_ID, PUBLISH_TAG_TO)
);

CREATE NONCLUSTERED INDEX CMS_CONTENTS_01_IDX 
    ON CMS_CONTENTS (RESOURCE_ID);

CREATE NONCLUSTERED INDEX CMS_CONTENTS_02_IDX 
    ON CMS_CONTENTS (PUBLISH_TAG_FROM);

CREATE NONCLUSTERED INDEX CMS_CONTENTS_03_IDX 
    ON CMS_CONTENTS (PUBLISH_TAG_TO);

CREATE NONCLUSTERED INDEX CMS_CONTENTS_04_IDX 
    ON CMS_CONTENTS (RESOURCE_ID, ONLINE_FLAG);
                
CREATE TABLE CMS_ONLINE_RESOURCE_RELATIONS (
    RELATION_SOURCE_ID NVARCHAR(36) NOT NULL,
    RELATION_SOURCE_PATH NVARCHAR(511) NOT NULL,
    RELATION_TARGET_ID NVARCHAR(36) NOT NULL,
    RELATION_TARGET_PATH NVARCHAR(511) NOT NULL,
    RELATION_TYPE INT NOT NULL
);

CREATE NONCLUSTERED INDEX CMS_ONLINE_RESOURCE_RELATIONS_01_IDX 
    ON CMS_ONLINE_RESOURCE_RELATIONS (RELATION_SOURCE_ID);

CREATE NONCLUSTERED INDEX CMS_ONLINE_RESOURCE_RELATIONS_02_IDX 
    ON CMS_ONLINE_RESOURCE_RELATIONS (RELATION_SOURCE_PATH);

CREATE NONCLUSTERED INDEX CMS_ONLINE_RESOURCE_RELATIONS_03_IDX 
    ON CMS_ONLINE_RESOURCE_RELATIONS (RELATION_TARGET_ID);

CREATE NONCLUSTERED INDEX CMS_ONLINE_RESOURCE_RELATIONS_04_IDX 
    ON CMS_ONLINE_RESOURCE_RELATIONS (RELATION_TARGET_PATH);

CREATE NONCLUSTERED INDEX CMS_ONLINE_RESOURCE_RELATIONS_05_IDX 
    ON CMS_ONLINE_RESOURCE_RELATIONS (RELATION_TYPE);
                
CREATE TABLE CMS_OFFLINE_RESOURCE_RELATIONS (
    RELATION_SOURCE_ID NVARCHAR(36) NOT NULL,
    RELATION_SOURCE_PATH NVARCHAR(511) NOT NULL,
    RELATION_TARGET_ID NVARCHAR(36) NOT NULL,
    RELATION_TARGET_PATH NVARCHAR(511) NOT NULL,
    RELATION_TYPE INT NOT NULL
);

CREATE NONCLUSTERED INDEX CMS_OFFLINE_RESOURCE_RELATIONS_01_IDX 
    ON CMS_OFFLINE_RESOURCE_RELATIONS (RELATION_SOURCE_ID);
    
CREATE NONCLUSTERED INDEX CMS_OFFLINE_RESOURCE_RELATIONS_02_IDX 
    ON CMS_OFFLINE_RESOURCE_RELATIONS (RELATION_SOURCE_PATH);
    
CREATE NONCLUSTERED INDEX CMS_OFFLINE_RESOURCE_RELATIONS_03_IDX 
    ON CMS_OFFLINE_RESOURCE_RELATIONS (RELATION_TARGET_ID);
    
CREATE NONCLUSTERED INDEX CMS_OFFLINE_RESOURCE_RELATIONS_04_IDX 
    ON CMS_OFFLINE_RESOURCE_RELATIONS (RELATION_TARGET_PATH);
    
CREATE NONCLUSTERED INDEX CMS_OFFLINE_RESOURCE_RELATIONS_05_IDX 
    ON CMS_OFFLINE_RESOURCE_RELATIONS (RELATION_TYPE);