create_tables.sql

cms是开源的框架

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(440) NOT NULL,
	STRUCTURE_STATE INT NOT NULL,
	DATE_RELEASED BIGINT NOT NULL,
	DATE_EXPIRED BIGINT NOT NULL,
	PRIMARY KEY (STRUCTURE_ID)
);

CREATE TABLE CMS_BACKUP_STRUCTURE (
	BACKUP_ID NVARCHAR(36)  NOT NULL,
	PUBLISH_TAG INT NOT NULL,
	VERSION_ID INT NOT NULL,
	STRUCTURE_ID NVARCHAR(36)  NOT NULL,
	RESOURCE_ID NVARCHAR(36)  NOT NULL,
	RESOURCE_PATH NVARCHAR(440) NOT NULL,
	STRUCTURE_STATE INT NOT NULL,
	DATE_RELEASED BIGINT NOT NULL,
	DATE_EXPIRED BIGINT NOT NULL,
	PRIMARY KEY (BACKUP_ID)
);

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,                                         
	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 INT NOT NULL,          
	PRIMARY KEY(RESOURCE_ID)
);

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,
	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 INT NOT NULL,
	PRIMARY KEY(RESOURCE_ID)
);
                                         
CREATE TABLE CMS_BACKUP_RESOURCES (
	BACKUP_ID NVARCHAR(36)  NOT NULL,
	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,
	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 INT NOT NULL,
	PUBLISH_TAG INT NOT NULL,
	VERSION_ID INT NOT NULL,
	USER_CREATED_NAME NVARCHAR(64) NOT NULL,
	USER_LASTMODIFIED_NAME NVARCHAR(64) NOT NULL,
	PRIMARY KEY(BACKUP_ID)
);

CREATE TABLE CMS_OFFLINE_CONTENTS (
	CONTENT_ID NVARCHAR(36)  NOT NULL,
	RESOURCE_ID NVARCHAR(36)  NOT NULL,
	FILE_CONTENT IMAGE NOT NULL,
	UNIQUE (RESOURCE_ID),
	PRIMARY KEY(CONTENT_ID)
);

CREATE TABLE CMS_ONLINE_CONTENTS (
	CONTENT_ID NVARCHAR(36)  NOT NULL,
	RESOURCE_ID NVARCHAR(36)  NOT NULL,
	FILE_CONTENT IMAGE NOT NULL,
	UNIQUE (RESOURCE_ID),
	PRIMARY KEY(CONTENT_ID)
);

CREATE TABLE CMS_BACKUP_CONTENTS (
	BACKUP_ID NVARCHAR(36)  NOT NULL,
	CONTENT_ID NVARCHAR(36)  NOT NULL,
	RESOURCE_ID NVARCHAR(36)  NOT NULL,
	FILE_CONTENT IMAGE NOT NULL,
	PUBLISH_TAG INT,
	VERSION_ID INT NOT NULL,
	PRIMARY KEY(BACKUP_ID)
);

CREATE NONCLUSTERED INDEX IX_CMS_OFFLINE_PROPERTIES ON CMS_OFFLINE_PROPERTIES
( PROPERTYDEF_ID );

CREATE NONCLUSTERED INDEX IX_CMS_OFFLINE_PROPERTIES_1 ON CMS_OFFLINE_PROPERTIES
(PROPERTY_MAPPING_ID);

CREATE NONCLUSTERED INDEX IX_CMS_OFFLINE_PROPERTIES_2 ON CMS_OFFLINE_PROPERTIES
(PROPERTYDEF_ID,PROPERTY_MAPPING_ID);


CREATE NONCLUSTERED INDEX IX_CMS_ONLINE_PROPERTIES ON CMS_ONLINE_PROPERTIES
( PROPERTYDEF_ID );

CREATE NONCLUSTERED INDEX IX_CMS_ONLINE_PROPERTIES_1 ON CMS_ONLINE_PROPERTIES
(PROPERTY_MAPPING_ID);

CREATE NONCLUSTERED INDEX IX_CMS_ONLINE_PROPERTIES_2 ON CMS_ONLINE_PROPERTIES
(PROPERTYDEF_ID,PROPERTY_MAPPING_ID);


CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_PROPERTIES ON CMS_BACKUP_PROPERTIES
( PROPERTYDEF_ID );

CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_PROPERTIES_1 ON CMS_BACKUP_PROPERTIES
(PROPERTY_MAPPING_ID);

CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_PROPERTIES_2 ON CMS_BACKUP_PROPERTIES
(PROPERTYDEF_ID,PROPERTY_MAPPING_ID);



CREATE NONCLUSTERED INDEX IX_CMS_ONLINE_ACCESSCONTROL ON CMS_ONLINE_ACCESSCONTROL
(PRINCIPAL_ID);

CREATE NONCLUSTERED INDEX IX_CMS_OFFLINE_ACCESSCONTROL ON CMS_OFFLINE_ACCESSCONTROL
(PRINCIPAL_ID);

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

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

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

CREATE NONCLUSTERED INDEX IX_CMS_OFFLINE_STRUCTURE_3 ON CMS_OFFLINE_STRUCTURE
(STRUCTURE_STATE);

CREATE NONCLUSTERED INDEX IX_CMS_OFFLINE_STRUCTURE_4 ON CMS_OFFLINE_STRUCTURE
(PARENT_ID);

CREATE NONCLUSTERED INDEX IX_CMS_OFFLINE_STRUCTURE_5 ON CMS_OFFLINE_STRUCTURE
(RESOURCE_ID);

CREATE NONCLUSTERED INDEX IX_CMS_OFFLINE_STRUCTURE_6 ON CMS_OFFLINE_STRUCTURE
(RESOURCE_PATH);


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

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

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

CREATE NONCLUSTERED INDEX IX_CMS_ONLINE_STRUCTURE_3 ON CMS_ONLINE_STRUCTURE
(STRUCTURE_STATE);

CREATE NONCLUSTERED INDEX IX_CMS_ONLINE_STRUCTURE_4 ON CMS_ONLINE_STRUCTURE
(PARENT_ID);

CREATE NONCLUSTERED INDEX IX_CMS_ONLINE_STRUCTURE_5 ON CMS_ONLINE_STRUCTURE
(RESOURCE_ID);

CREATE NONCLUSTERED INDEX IX_CMS_ONLINE_STRUCTURE_6 ON CMS_ONLINE_STRUCTURE
(RESOURCE_PATH);


CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_STRUCTURE ON CMS_BACKUP_STRUCTURE
(STRUCTURE_ID, RESOURCE_PATH);

CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_STRUCTURE_1 ON CMS_BACKUP_STRUCTURE
(RESOURCE_PATH, RESOURCE_ID);

CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_STRUCTURE_2 ON CMS_BACKUP_STRUCTURE
(STRUCTURE_ID, RESOURCE_ID);

CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_STRUCTURE_3 ON CMS_BACKUP_STRUCTURE
(STRUCTURE_STATE);

CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_STRUCTURE_4 ON CMS_BACKUP_STRUCTURE
(PUBLISH_TAG);

CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_STRUCTURE_5 ON CMS_BACKUP_STRUCTURE
(RESOURCE_ID);

CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_STRUCTURE_6 ON CMS_BACKUP_STRUCTURE
(RESOURCE_PATH);

CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_STRUCTURE_7 ON CMS_BACKUP_STRUCTURE
(VERSION_ID);


CREATE NONCLUSTERED INDEX IX_CMS_OFFLINE_RESOURCES ON CMS_OFFLINE_RESOURCES
(PROJECT_LASTMODIFIED);

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

CREATE NONCLUSTERED INDEX IX_CMS_OFFLINE_RESOURCES_2 ON CMS_OFFLINE_RESOURCES
(RESOURCE_SIZE);

CREATE NONCLUSTERED INDEX IX_CMS_OFFLINE_RESOURCES_3 ON CMS_OFFLINE_RESOURCES
(DATE_LASTMODIFIED);

CREATE NONCLUSTERED INDEX IX_CMS_OFFLINE_RESOURCES_4 ON CMS_OFFLINE_RESOURCES
(RESOURCE_TYPE);

CREATE NONCLUSTERED INDEX IX_CMS_ONLINE_RESOURCES ON CMS_ONLINE_RESOURCES
(PROJECT_LASTMODIFIED);

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

CREATE NONCLUSTERED INDEX IX_CMS_ONLINE_RESOURCES_2 ON CMS_ONLINE_RESOURCES
(RESOURCE_SIZE);

CREATE NONCLUSTERED INDEX IX_CMS_ONLINE_RESOURCES_3 ON CMS_ONLINE_RESOURCES
(DATE_LASTMODIFIED);

CREATE NONCLUSTERED INDEX IX_CMS_ONLINE_RESOURCES_4 ON CMS_ONLINE_RESOURCES
(RESOURCE_TYPE);

CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_RESOURCES ON CMS_BACKUP_RESOURCES
(PROJECT_LASTMODIFIED);

CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_RESOURCES_1 ON CMS_BACKUP_RESOURCES
(PROJECT_LASTMODIFIED,RESOURCE_SIZE);

CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_RESOURCES_2 ON CMS_BACKUP_RESOURCES
(RESOURCE_SIZE);

CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_RESOURCES_3 ON CMS_BACKUP_RESOURCES
(DATE_LASTMODIFIED);

CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_RESOURCES_4 ON CMS_BACKUP_RESOURCES
(RESOURCE_TYPE);



CREATE NONCLUSTERED INDEX IX_CMS_BACKUP_CONTENTS ON CMS_BACKUP_CONTENTS
(RESOURCE_ID);