create_tables.sql

一个cms内容管理平台

	LINK_RFS_PATH VARCHAR2(1024),
	LINK_TYPE INT NOT NULL,
	LINK_PARAMETER VARCHAR2(1024),
	LINK_TIMESTAMP NUMBER NOT NULL,
	CONSTRAINT CMS_STATICEXPORT_LINKS PRIMARY KEY (LINK_ID) USING INDEX TABLESPACE ${indexTablespace}
);

CREATE INDEX CMS_STATICEXPORT_LINKS_01_IDX
	ON CMS_STATICEXPORT_LINKS (LINK_RFS_PATH)
	TABLESPACE ${indexTablespace};

CREATE TABLE CMS_OFFLINE_STRUCTURE (
	STRUCTURE_ID VARCHAR2(36) NOT NULL,
	RESOURCE_ID VARCHAR(36) NOT NULL,
	PARENT_ID VARCHAR2(36) NOT NULL,
	RESOURCE_PATH VARCHAR2(1024),
	STRUCTURE_STATE INT NOT NULL,	
	DATE_RELEASED NUMBER NOT NULL,
	DATE_EXPIRED NUMBER NOT NULL,	
	CONSTRAINT PK_OFFLINE_STRUCTURE PRIMARY KEY (STRUCTURE_ID) USING INDEX TABLESPACE ${indexTablespace}
	STORAGE (FREELISTS 10)
);

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

CREATE TABLE CMS_ONLINE_STRUCTURE (
	STRUCTURE_ID VARCHAR2(36) NOT NULL,
	RESOURCE_ID VARCHAR(36) NOT NULL,
	PARENT_ID VARCHAR2(36) NOT NULL,
	RESOURCE_PATH VARCHAR2(1024),
	STRUCTURE_STATE INT NOT NULL,	
	DATE_RELEASED NUMBER NOT NULL,
	DATE_EXPIRED NUMBER NOT NULL,		
	CONSTRAINT PK_ONLINE_STRUCTURE PRIMARY KEY (STRUCTURE_ID) USING INDEX TABLESPACE ${indexTablespace}
	STORAGE (FREELISTS 10)
);

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

CREATE TABLE CMS_BACKUP_STRUCTURE (
	BACKUP_ID VARCHAR2(36) NOT NULL,
    PUBLISH_TAG INT NOT NULL,
    VERSION_ID INT NOT NULL,
	STRUCTURE_ID VARCHAR2(36) NOT NULL,
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	RESOURCE_PATH VARCHAR2(1024),
	STRUCTURE_STATE INT NOT NULL,
	DATE_RELEASED NUMBER NOT NULL,
	DATE_EXPIRED NUMBER NOT NULL,		
	CONSTRAINT PK_BACKUP_STRUCTURE PRIMARY KEY (BACKUP_ID) USING INDEX TABLESPACE ${indexTablespace}
);     

CREATE INDEX CMS_BACKUP_STRUCTURE_01_IDX
	ON CMS_BACKUP_STRUCTURE (STRUCTURE_ID, RESOURCE_PATH)
	TABLESPACE ${indexTablespace};
	
CREATE INDEX CMS_BACKUP_STRUCTURE_02_IDX
	ON CMS_BACKUP_STRUCTURE (RESOURCE_PATH, RESOURCE_ID)
	TABLESPACE ${indexTablespace};
	
CREATE INDEX CMS_BACKUP_STRUCTURE_03_IDX
	ON CMS_BACKUP_STRUCTURE (STRUCTURE_ID, RESOURCE_ID)
	TABLESPACE ${indexTablespace};
	
CREATE INDEX CMS_BACKUP_STRUCTURE_04_IDX
	ON CMS_BACKUP_STRUCTURE (STRUCTURE_STATE)
	TABLESPACE ${indexTablespace};
	
CREATE INDEX CMS_BACKUP_STRUCTURE_05_IDX
	ON CMS_BACKUP_STRUCTURE (RESOURCE_ID)
	TABLESPACE ${indexTablespace};
	
CREATE INDEX CMS_BACKUP_STRUCTURE_06_IDX
	ON CMS_BACKUP_STRUCTURE (RESOURCE_PATH)
	TABLESPACE ${indexTablespace};

CREATE INDEX CMS_BACKUP_STRUCTURE_07_IDX
	ON CMS_BACKUP_STRUCTURE (PUBLISH_TAG)
	TABLESPACE ${indexTablespace};

CREATE INDEX CMS_BACKUP_STRUCTURE_08_IDX
	ON CMS_BACKUP_STRUCTURE (VERSION_ID)
	TABLESPACE ${indexTablespace};

CREATE TABLE CMS_OFFLINE_RESOURCES (
	RESOURCE_ID VARCHAR2(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 NUMBER NOT NULL,
	DATE_LASTMODIFIED NUMBER NOT NULL,
	USER_CREATED VARCHAR2(36) NOT NULL,
	USER_LASTMODIFIED VARCHAR2(36) NOT NULL,
	PROJECT_LASTMODIFIED INT NOT NULL,
	CONSTRAINT PK_OFFLINE_RESOURCES PRIMARY KEY(RESOURCE_ID) USING INDEX TABLESPACE ${indexTablespace}
	STORAGE (FREELISTS 10)
);

CREATE INDEX CMS_OFFLINE_RESOURCES_01_IDX
	ON CMS_OFFLINE_RESOURCES (PROJECT_LASTMODIFIED)
	TABLESPACE ${indexTablespace};

CREATE INDEX CMS_OFFLINE_RESOURCES_02_IDX
	ON CMS_OFFLINE_RESOURCES (PROJECT_LASTMODIFIED, RESOURCE_SIZE)
	TABLESPACE ${indexTablespace};

CREATE INDEX CMS_OFFLINE_RESOURCES_03_IDX
	ON CMS_OFFLINE_RESOURCES (RESOURCE_SIZE)
	TABLESPACE ${indexTablespace};
	
CREATE INDEX CMS_OFFLINE_RESOURCES_04_IDX
	ON CMS_OFFLINE_RESOURCES (DATE_LASTMODIFIED)
	TABLESPACE ${indexTablespace};
	
CREATE INDEX CMS_OFFLINE_RESOURCES_05_IDX
	ON CMS_OFFLINE_RESOURCES (RESOURCE_TYPE)
	TABLESPACE ${indexTablespace};

CREATE TABLE CMS_ONLINE_RESOURCES (
	RESOURCE_ID VARCHAR2(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 NUMBER NOT NULL,
	DATE_LASTMODIFIED NUMBER NOT NULL,
	USER_CREATED VARCHAR2(36) NOT NULL,
	USER_LASTMODIFIED VARCHAR2(36) NOT NULL,
	PROJECT_LASTMODIFIED INT NOT NULL,
	CONSTRAINT PK_ONLINE_RESOURCES PRIMARY KEY(RESOURCE_ID) USING INDEX TABLESPACE ${indexTablespace}
	STORAGE (FREELISTS 10)
);

CREATE INDEX CMS_ONLINE_RESOURCES_01_IDX
	ON CMS_ONLINE_RESOURCES (PROJECT_LASTMODIFIED)
	TABLESPACE ${indexTablespace};

CREATE INDEX CMS_ONLINE_RESOURCES_02_IDX
	ON CMS_ONLINE_RESOURCES (PROJECT_LASTMODIFIED, RESOURCE_SIZE)
	TABLESPACE ${indexTablespace};

CREATE INDEX CMS_ONLINE_RESOURCES_03_IDX
	ON CMS_ONLINE_RESOURCES (RESOURCE_SIZE)
	TABLESPACE ${indexTablespace};
	
CREATE INDEX CMS_ONLINE_RESOURCES_04_IDX
	ON CMS_ONLINE_RESOURCES (DATE_LASTMODIFIED)
	TABLESPACE ${indexTablespace};
	
CREATE INDEX CMS_ONLINE_RESOURCES_05_IDX
	ON CMS_ONLINE_RESOURCES (RESOURCE_TYPE)
	TABLESPACE ${indexTablespace};

CREATE TABLE CMS_BACKUP_RESOURCES (
	BACKUP_ID VARCHAR2(36) NOT NULL,
	RESOURCE_ID VARCHAR2(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 NUMBER NOT NULL,
	DATE_LASTMODIFIED NUMBER NOT NULL,
	USER_CREATED VARCHAR2(36) NOT NULL,
	USER_LASTMODIFIED VARCHAR2(36) NOT NULL,
	PROJECT_LASTMODIFIED INT NOT NULL,
	PUBLISH_TAG INT NOT NULL,
	VERSION_ID INT NOT NULL,
    USER_CREATED_NAME VARCHAR2(128) NOT NULL,
    USER_LASTMODIFIED_NAME VARCHAR2(128) NOT NULL,
	CONSTRAINT PK_BACKUP_RESOURCES PRIMARY KEY(BACKUP_ID) USING INDEX TABLESPACE ${indexTablespace},
	CONSTRAINT UK_BACKUP_RESOURCES UNIQUE(PUBLISH_TAG, RESOURCE_ID) USING INDEX TABLESPACE ${indexTablespace}
);

CREATE INDEX CMS_BACKUP_RESOURCES_01_IDX
	ON CMS_BACKUP_RESOURCES (PROJECT_LASTMODIFIED)
	TABLESPACE ${indexTablespace};
	
CREATE INDEX CMS_BACKUP_RESOURCES_02_IDX
	ON CMS_BACKUP_RESOURCES (PROJECT_LASTMODIFIED, RESOURCE_SIZE)
	TABLESPACE ${indexTablespace};
	
CREATE INDEX CMS_BACKUP_RESOURCES_03_IDX
	ON CMS_BACKUP_RESOURCES (RESOURCE_SIZE)
	TABLESPACE ${indexTablespace};
	
CREATE INDEX CMS_BACKUP_RESOURCES_04_IDX
	ON CMS_BACKUP_RESOURCES (DATE_LASTMODIFIED)
	TABLESPACE ${indexTablespace};
	
CREATE INDEX CMS_BACKUP_RESOURCES_05_IDX
	ON CMS_BACKUP_RESOURCES (RESOURCE_TYPE)
	TABLESPACE ${indexTablespace};

CREATE INDEX CMS_BACKUP_RESOURCES_06_IDX
	ON CMS_BACKUP_RESOURCES (RESOURCE_ID)
	TABLESPACE ${indexTablespace};

CREATE INDEX CMS_BACKUP_RESOURCES_07_IDX
	ON CMS_BACKUP_RESOURCES (PUBLISH_TAG)
	TABLESPACE ${indexTablespace};

CREATE TABLE CMS_OFFLINE_CONTENTS (
	CONTENT_ID VARCHAR2(36) NOT NULL,
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	FILE_CONTENT BLOB NOT NULL,
	CONSTRAINT PK_OFFLINE_CONTENTS PRIMARY KEY(CONTENT_ID) USING INDEX TABLESPACE ${indexTablespace},
	CONSTRAINT UK_OFFLINE_CONTENTS UNIQUE(RESOURCE_ID) USING INDEX TABLESPACE ${indexTablespace}
)
STORAGE (INITIAL 256K NEXT 1M PCTINCREASE 0)
	LOB(FILE_CONTENT) STORE AS (
		CHUNK 32K PCTVERSION 20  
	    CACHE
);

CREATE TABLE CMS_ONLINE_CONTENTS (
	CONTENT_ID VARCHAR2(36) NOT NULL,
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	FILE_CONTENT BLOB NOT NULL,
	CONSTRAINT PK_ONLINE_CONTENTS PRIMARY KEY(CONTENT_ID) USING INDEX TABLESPACE ${indexTablespace},
	CONSTRAINT UK_ONLINE_CONTENTS UNIQUE(RESOURCE_ID) USING INDEX TABLESPACE ${indexTablespace}
)
STORAGE (INITIAL 256K NEXT 1M PCTINCREASE 0)
	LOB(FILE_CONTENT) STORE AS (
        CHUNK 32K PCTVERSION 20  
        CACHE
);

CREATE TABLE CMS_BACKUP_CONTENTS (
	BACKUP_ID VARCHAR2(36) NOT NULL,
	CONTENT_ID VARCHAR2(36) NOT NULL,
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	FILE_CONTENT BLOB NOT NULL,
	PUBLISH_TAG INT,
	VERSION_ID INT NOT NULL,
	CONSTRAINT PK_BACKUP_CONTENTS PRIMARY KEY(BACKUP_ID) USING INDEX TABLESPACE ${indexTablespace}
)	
STORAGE (INITIAL 256K NEXT 1M PCTINCREASE 0)
	LOB(FILE_CONTENT) STORE AS (
        CHUNK 32K PCTVERSION 20  
        CACHE
);

CREATE INDEX CMS_BACKUP_CONTENTS_01_IDX
	ON CMS_BACKUP_CONTENTS (PUBLISH_TAG)
	TABLESPACE ${indexTablespace};

CREATE INDEX CMS_BACKUP_CONTENTS_02_IDX
	ON CMS_BACKUP_CONTENTS (RESOURCE_ID)
	TABLESPACE ${indexTablespace};
	
CREATE INDEX CMS_BACKUP_CONTENTS_03_IDX
	ON CMS_BACKUP_CONTENTS (CONTENT_ID)
	TABLESPACE ${indexTablespace};