create_tables.sql

找了很久才找到到源代码

CREATE TABLE CMS_OFFLINE_STRUCTURE (
	STRUCTURE_ID VARCHAR(36) NOT NULL,
	RESOURCE_ID VARCHAR(36) NOT NULL,
	PARENT_ID VARCHAR(36) NOT NULL,
	RESOURCE_PATH VARCHAR(1024),
	STRUCTURE_STATE INT NOT NULL,	
	DATE_RELEASED BIGINT NOT NULL,
	DATE_EXPIRED BIGINT NOT NULL,	
	STRUCTURE_VERSION INT NOT NULL,
	CONSTRAINT PK_OFFLINE_STRUCTURE PRIMARY KEY (STRUCTURE_ID)
);

CREATE INDEX CMS_OFFLINE_STRUCTURE_01_IDX
	ON CMS_OFFLINE_STRUCTURE (STRUCTURE_ID, RESOURCE_PATH)
	;
	
CREATE INDEX CMS_OFFLINE_STRUCTURE_02_IDX
	ON CMS_OFFLINE_STRUCTURE (RESOURCE_PATH, RESOURCE_ID)
	;
	
CREATE INDEX CMS_OFFLINE_STRUCTURE_03_IDX
	ON CMS_OFFLINE_STRUCTURE (STRUCTURE_ID, RESOURCE_ID)
	;
	
CREATE INDEX CMS_OFFLINE_STRUCTURE_04_IDX
	ON CMS_OFFLINE_STRUCTURE (STRUCTURE_STATE)
	;
	
CREATE INDEX CMS_OFFLINE_STRUCTURE_05_IDX
	ON CMS_OFFLINE_STRUCTURE (PARENT_ID)
	;
	
CREATE INDEX CMS_OFFLINE_STRUCTURE_06_IDX
	ON CMS_OFFLINE_STRUCTURE (RESOURCE_ID)
	;
	
CREATE INDEX CMS_OFFLINE_STRUCTURE_07_IDX
	ON CMS_OFFLINE_STRUCTURE (RESOURCE_PATH)
	;

CREATE TABLE CMS_ONLINE_STRUCTURE (
	STRUCTURE_ID VARCHAR(36) NOT NULL,
	RESOURCE_ID VARCHAR(36) NOT NULL,
	PARENT_ID VARCHAR(36) NOT NULL,
	RESOURCE_PATH VARCHAR(1024),
	STRUCTURE_STATE INT NOT NULL,	
	DATE_RELEASED BIGINT NOT NULL,
	DATE_EXPIRED BIGINT NOT NULL,	
	STRUCTURE_VERSION INT NOT NULL,	
	CONSTRAINT PK_ONLINE_STRUCTURE PRIMARY KEY (STRUCTURE_ID)
);

CREATE INDEX CMS_ONLINE_STRUCTURE_01_IDX
	ON CMS_ONLINE_STRUCTURE (STRUCTURE_ID, RESOURCE_PATH)
	;
	
CREATE INDEX CMS_ONLINE_STRUCTURE_02_IDX
	ON CMS_ONLINE_STRUCTURE (RESOURCE_PATH, RESOURCE_ID)
	;
	
CREATE INDEX CMS_ONLINE_STRUCTURE_03_IDX
	ON CMS_ONLINE_STRUCTURE (STRUCTURE_ID, RESOURCE_ID)
	;
	
CREATE INDEX CMS_ONLINE_STRUCTURE_04_IDX
	ON CMS_ONLINE_STRUCTURE (STRUCTURE_STATE)
	;
	
CREATE INDEX CMS_ONLINE_STRUCTURE_05_IDX
	ON CMS_ONLINE_STRUCTURE (PARENT_ID)
	;
	
CREATE INDEX CMS_ONLINE_STRUCTURE_06_IDX
	ON CMS_ONLINE_STRUCTURE (RESOURCE_ID)
	;
	
CREATE INDEX CMS_ONLINE_STRUCTURE_07_IDX
	ON CMS_ONLINE_STRUCTURE (RESOURCE_PATH)
	;

CREATE TABLE CMS_HISTORY_STRUCTURE (
    PUBLISH_TAG INT NOT NULL,
    VERSION INT NOT NULL,
	STRUCTURE_ID VARCHAR(36) NOT NULL,
	RESOURCE_ID VARCHAR(36) NOT NULL,
	PARENT_ID VARCHAR(36) NOT NULL,
	RESOURCE_PATH VARCHAR(1024),
	STRUCTURE_STATE INT NOT NULL,
	DATE_RELEASED BIGINT NOT NULL,
	DATE_EXPIRED BIGINT NOT NULL,	
	STRUCTURE_VERSION INT NOT NULL,	
	CONSTRAINT PK_HISTORY_STRUCTURE PRIMARY KEY (STRUCTURE_ID,PUBLISH_TAG,VERSION)
); 

CREATE INDEX CMS_HISTORY_STRUCTURE_01_IDX
	ON CMS_HISTORY_STRUCTURE (STRUCTURE_ID, RESOURCE_PATH)
	;
	
CREATE INDEX CMS_HISTORY_STRUCTURE_02_IDX
	ON CMS_HISTORY_STRUCTURE (RESOURCE_PATH, RESOURCE_ID)
	;
	
CREATE INDEX CMS_HISTORY_STRUCTURE_03_IDX
	ON CMS_HISTORY_STRUCTURE (STRUCTURE_ID, RESOURCE_ID)
	;
	
CREATE INDEX CMS_HISTORY_STRUCTURE_04_IDX
	ON CMS_HISTORY_STRUCTURE (STRUCTURE_STATE)
	;
	
CREATE INDEX CMS_HISTORY_STRUCTURE_05_IDX
	ON CMS_HISTORY_STRUCTURE (RESOURCE_ID)
	;
	
CREATE INDEX CMS_HISTORY_STRUCTURE_06_IDX
	ON CMS_HISTORY_STRUCTURE (RESOURCE_PATH)
	;

CREATE INDEX CMS_HISTORY_STRUCTURE_07_IDX
	ON CMS_HISTORY_STRUCTURE (PUBLISH_TAG)
	;

CREATE INDEX CMS_HISTORY_STRUCTURE_08_IDX
	ON CMS_HISTORY_STRUCTURE (VERSION)
	;

CREATE TABLE CMS_OFFLINE_RESOURCES (
	RESOURCE_ID VARCHAR(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 VARCHAR(36) NOT NULL,
	USER_LASTMODIFIED VARCHAR(36) NOT NULL,
	PROJECT_LASTMODIFIED VARCHAR(36) NOT NULL,
	RESOURCE_VERSION INT NOT NULL,
	CONSTRAINT PK_OFFLINE_RESOURCES PRIMARY KEY(RESOURCE_ID)
);

CREATE INDEX CMS_OFFLINE_RESOURCES_01_IDX
	ON CMS_OFFLINE_RESOURCES (PROJECT_LASTMODIFIED)
	;

CREATE INDEX CMS_OFFLINE_RESOURCES_02_IDX
	ON CMS_OFFLINE_RESOURCES (PROJECT_LASTMODIFIED, RESOURCE_SIZE)
	;

CREATE INDEX CMS_OFFLINE_RESOURCES_03_IDX
	ON CMS_OFFLINE_RESOURCES (RESOURCE_SIZE)
	;
	
CREATE INDEX CMS_OFFLINE_RESOURCES_04_IDX
	ON CMS_OFFLINE_RESOURCES (DATE_LASTMODIFIED)
	;
	
CREATE INDEX CMS_OFFLINE_RESOURCES_05_IDX
	ON CMS_OFFLINE_RESOURCES (RESOURCE_TYPE)
	;

CREATE TABLE CMS_ONLINE_RESOURCES (
	RESOURCE_ID VARCHAR(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 VARCHAR(36) NOT NULL,
	USER_LASTMODIFIED VARCHAR(36) NOT NULL,
	PROJECT_LASTMODIFIED VARCHAR(36) NOT NULL,
	RESOURCE_VERSION INT NOT NULL,
	CONSTRAINT PK_ONLINE_RESOURCES PRIMARY KEY(RESOURCE_ID)
);

CREATE INDEX CMS_ONLINE_RESOURCES_01_IDX
	ON CMS_ONLINE_RESOURCES (PROJECT_LASTMODIFIED)
	;

CREATE INDEX CMS_ONLINE_RESOURCES_02_IDX
	ON CMS_ONLINE_RESOURCES (PROJECT_LASTMODIFIED, RESOURCE_SIZE)
	;

CREATE INDEX CMS_ONLINE_RESOURCES_03_IDX
	ON CMS_ONLINE_RESOURCES (RESOURCE_SIZE)
	;
	
CREATE INDEX CMS_ONLINE_RESOURCES_04_IDX
	ON CMS_ONLINE_RESOURCES (DATE_LASTMODIFIED)
	;
	
CREATE INDEX CMS_ONLINE_RESOURCES_05_IDX
	ON CMS_ONLINE_RESOURCES (RESOURCE_TYPE)
	;

CREATE TABLE CMS_HISTORY_RESOURCES (
	RESOURCE_ID VARCHAR(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 VARCHAR(36) NOT NULL,
	USER_LASTMODIFIED VARCHAR(36) NOT NULL,
	PROJECT_LASTMODIFIED VARCHAR(36) NOT NULL,
	PUBLISH_TAG INT NOT NULL,
	RESOURCE_VERSION INT NOT NULL,
	CONSTRAINT PK_HISTORY_RESOURCES PRIMARY KEY(RESOURCE_ID, PUBLISH_TAG)
);

CREATE INDEX CMS_HISTORY_RESOURCES_01_IDX
	ON CMS_HISTORY_RESOURCES (PROJECT_LASTMODIFIED)
	;
	
CREATE INDEX CMS_HISTORY_RESOURCES_02_IDX
	ON CMS_HISTORY_RESOURCES (PROJECT_LASTMODIFIED, RESOURCE_SIZE)
	;
	
CREATE INDEX CMS_HISTORY_RESOURCES_03_IDX
	ON CMS_HISTORY_RESOURCES (RESOURCE_SIZE)
	;
	
CREATE INDEX CMS_HISTORY_RESOURCES_04_IDX
	ON CMS_HISTORY_RESOURCES (DATE_LASTMODIFIED)
	;
	
CREATE INDEX CMS_HISTORY_RESOURCES_05_IDX
	ON CMS_HISTORY_RESOURCES (RESOURCE_TYPE)
	;

CREATE INDEX CMS_HISTORY_RESOURCES_06_IDX
	ON CMS_HISTORY_RESOURCES (PUBLISH_TAG)
	;

CREATE TABLE CMS_OFFLINE_CONTENTS (
	RESOURCE_ID VARCHAR(36) NOT NULL,
	FILE_CONTENT BYTEA NOT NULL,
	CONSTRAINT PK_OFFLINE_CONTENTS PRIMARY KEY(RESOURCE_ID)
);

CREATE TABLE CMS_CONTENTS (
	RESOURCE_ID VARCHAR(36) NOT NULL,
	FILE_CONTENT BYTEA NOT NULL,
	PUBLISH_TAG_FROM INT,
	PUBLISH_TAG_TO INT,
	ONLINE_FLAG INT,
	CONSTRAINT PK_CONTENTS PRIMARY KEY(RESOURCE_ID, PUBLISH_TAG_FROM)
);

CREATE INDEX CMS_CONTENTS_01_IDX
	ON CMS_CONTENTS (RESOURCE_ID, PUBLISH_TAG_TO)
	;
	
CREATE INDEX CMS_CONTENTS_02_IDX
	ON CMS_CONTENTS (RESOURCE_ID)
	;
	
CREATE INDEX CMS_CONTENTS_03_IDX
	ON CMS_CONTENTS (PUBLISH_TAG_FROM)
	;
	
CREATE INDEX CMS_CONTENTS_04_IDX
	ON CMS_CONTENTS (PUBLISH_TAG_TO)
	;
	
CREATE INDEX CMS_CONTENTS_05_IDX
	ON CMS_CONTENTS (RESOURCE_ID, ONLINE_FLAG)
	;

CREATE TABLE CMS_ONLINE_RESOURCE_RELATIONS (
	RELATION_SOURCE_ID VARCHAR(36) NOT NULL,
	RELATION_SOURCE_PATH VARCHAR(1024) NOT NULL,
	RELATION_TARGET_ID VARCHAR(36) NOT NULL,
	RELATION_TARGET_PATH VARCHAR(1024) NOT NULL,
	RELATION_TYPE INT NOT NULL
);

CREATE INDEX CMS_ONLINE_RELATIONS_01_IDX
	ON CMS_ONLINE_RESOURCE_RELATIONS (RELATION_SOURCE_ID)
	;
	
CREATE INDEX CMS_ONLINE_RELATIONS_02_IDX
	ON CMS_ONLINE_RESOURCE_RELATIONS (RELATION_TARGET_ID)
	;
	
CREATE INDEX CMS_ONLINE_RELATIONS_03_IDX
	ON CMS_ONLINE_RESOURCE_RELATIONS (RELATION_SOURCE_PATH)
	;
	
CREATE INDEX CMS_ONLINE_RELATIONS_04_IDX
	ON CMS_ONLINE_RESOURCE_RELATIONS (RELATION_TARGET_PATH)
	;

CREATE INDEX CMS_ONLINE_RELATIONS_05_IDX
	ON CMS_ONLINE_RESOURCE_RELATIONS (RELATION_TYPE)
	;

CREATE TABLE CMS_OFFLINE_RESOURCE_RELATIONS (
	RELATION_SOURCE_ID VARCHAR(36) NOT NULL,
	RELATION_SOURCE_PATH VARCHAR(1024) NOT NULL,
	RELATION_TARGET_ID VARCHAR(36) NOT NULL,
	RELATION_TARGET_PATH VARCHAR(1024) NOT NULL,
	RELATION_TYPE INT NOT NULL
);

CREATE INDEX CMS_OFFLINE_RELATIONS_01_IDX
	ON CMS_OFFLINE_RESOURCE_RELATIONS (RELATION_SOURCE_ID)
	;
	
CREATE INDEX CMS_OFFLINE_RELATIONS_02_IDX
	ON CMS_OFFLINE_RESOURCE_RELATIONS (RELATION_TARGET_ID)
	;
	
CREATE INDEX CMS_OFFLINE_RELATIONS_03_IDX
	ON CMS_OFFLINE_RESOURCE_RELATIONS (RELATION_SOURCE_PATH)
	;

CREATE INDEX CMS_OFFLINE_RELATIONS_04_IDX
	ON CMS_OFFLINE_RESOURCE_RELATIONS (RELATION_TARGET_PATH)
	;
	
CREATE INDEX CMS_OFFLINE_RELATIONS_05_IDX
	ON CMS_OFFLINE_RESOURCE_RELATIONS (RELATION_TYPE)
	;