generic.sql

cms是开源的框架

create table CMS_MODULE_ONLINE_MASTER   
(MASTER_ID VARCHAR2(36) not null,
SUB_ID NUMBER not null,
USER_ID VARCHAR2(36) not null,
GROUP_ID VARCHAR2(36) not null,
PROJECT_ID NUMBER not null,
ACCESS_FLAGS NUMBER not null,
STATE NUMBER not null,
LOCKED_BY VARCHAR2(36) not null,
LASTMODIFIED_BY VARCHAR2(36) not null,
DATE_CREATED DATE not null,
DATE_LASTMODIFIED DATE not null,
PUBLICATION_DATE DATE,
PURGE_DATE DATE,
FLAGS NUMBER,
FEED_ID NUMBER,
FEED_REFERENCE NUMBER,
FEED_FILENAME VARCHAR2(255),
TITLE VARCHAR2(255),
DATA_BIG_0 VARCHAR2(4000),
DATA_BIG_1 VARCHAR2(4000),
DATA_BIG_2 VARCHAR2(4000),
DATA_BIG_3 VARCHAR2(4000),
DATA_BIG_4 VARCHAR2(4000),
DATA_BIG_5 VARCHAR2(4000),
DATA_BIG_6 VARCHAR2(4000),
DATA_BIG_7 VARCHAR2(4000),
DATA_BIG_8 VARCHAR2(4000),
DATA_BIG_9 VARCHAR2(4000),
DATA_MEDIUM_0 VARCHAR2(2000),
DATA_MEDIUM_1 VARCHAR2(2000),
DATA_MEDIUM_2 VARCHAR2(2000),
DATA_MEDIUM_3 VARCHAR2(2000),
DATA_MEDIUM_4 VARCHAR2(2000),
DATA_MEDIUM_5 VARCHAR2(2000),
DATA_MEDIUM_6 VARCHAR2(2000),
DATA_MEDIUM_7 VARCHAR2(2000),
DATA_MEDIUM_8 VARCHAR2(2000),
DATA_MEDIUM_9 VARCHAR2(2000),
DATA_SMALL_0 VARCHAR2(500),
DATA_SMALL_1 VARCHAR2(500),
DATA_SMALL_2 VARCHAR2(500),
DATA_SMALL_3 VARCHAR2(500),
DATA_SMALL_4 VARCHAR2(500),
DATA_SMALL_5 VARCHAR2(500),
DATA_SMALL_6 VARCHAR2(500),
DATA_SMALL_7 VARCHAR2(500),
DATA_SMALL_8 VARCHAR2(500),
DATA_SMALL_9 VARCHAR2(500),
DATA_SMALL_10 VARCHAR2(500),
DATA_SMALL_11 VARCHAR2(500),
DATA_SMALL_12 VARCHAR2(500),
DATA_SMALL_13 VARCHAR2(500),
DATA_SMALL_14 VARCHAR2(500),
DATA_SMALL_15 VARCHAR2(500),
DATA_SMALL_16 VARCHAR2(500),
DATA_SMALL_17 VARCHAR2(500),
DATA_SMALL_18 VARCHAR2(500),
DATA_SMALL_19 VARCHAR2(500),
DATA_SMALL_20 VARCHAR2(500),
DATA_SMALL_21 VARCHAR2(500),
DATA_SMALL_22 VARCHAR2(500),
DATA_SMALL_23 VARCHAR2(500),
DATA_SMALL_24 VARCHAR2(500),
DATA_SMALL_25 VARCHAR2(500),
DATA_SMALL_26 VARCHAR2(500),
DATA_SMALL_27 VARCHAR2(500),
DATA_SMALL_28 VARCHAR2(500),
DATA_SMALL_29 VARCHAR2(500),
DATA_SMALL_30 VARCHAR2(500),
DATA_SMALL_31 VARCHAR2(500),
DATA_SMALL_32 VARCHAR2(500),
DATA_SMALL_33 VARCHAR2(500),
DATA_SMALL_34 VARCHAR2(500),
DATA_SMALL_35 VARCHAR2(500),
DATA_SMALL_36 VARCHAR2(500),
DATA_SMALL_37 VARCHAR2(500),
DATA_SMALL_38 VARCHAR2(500),
DATA_SMALL_39 VARCHAR2(500),
DATA_INT_0 NUMBER,
DATA_INT_1 NUMBER,
DATA_INT_2 NUMBER,
DATA_INT_3 NUMBER,
DATA_INT_4 NUMBER,
DATA_INT_5 NUMBER,
DATA_INT_6 NUMBER,
DATA_INT_7 NUMBER,
DATA_INT_8 NUMBER,
DATA_INT_9 NUMBER,
DATA_REFERENCE_0 NUMBER,
DATA_REFERENCE_1 NUMBER,
DATA_REFERENCE_2 NUMBER,
DATA_REFERENCE_3 NUMBER,
DATA_REFERENCE_4 NUMBER,
DATA_REFERENCE_5 NUMBER,
DATA_REFERENCE_6 NUMBER,
DATA_REFERENCE_7 NUMBER,
DATA_REFERENCE_8 NUMBER,
DATA_REFERENCE_9 NUMBER,
DATA_DATE_0 DATE,
DATA_DATE_1 DATE,
DATA_DATE_2 DATE,
DATA_DATE_3 DATE,
DATA_DATE_4 DATE,
primary key(MASTER_ID));

create table CMS_MODULE_ONLINE_CHANNEL_REL
(MASTER_ID VARCHAR2(36) not null,
CHANNEL_ID NUMBER not null,
primary key(CHANNEL_ID, MASTER_ID));

create table CMS_MODULE_ONLINE_MEDIA    
(MEDIA_ID NUMBER not null,
MASTER_ID VARCHAR2(36) not null,
MEDIA_POSITION NUMBER not null,
MEDIA_WIDTH NUMBER not null,
MEDIA_HEIGHT NUMBER not null,
MEDIA_SIZE NUMBER not null,
MEDIA_MIMETYPE VARCHAR2(255),
MEDIA_TYPE NUMBER not null,
MEDIA_TITLE VARCHAR2(255),
MEDIA_NAME VARCHAR2(255),
MEDIA_DESCRIPTION VARCHAR2(500) not null,
MEDIA LONG RAW not null,
primary key(MEDIA_ID));

create table CMS_MODULE_MASTER 
(MASTER_ID VARCHAR2(36) not null,
SUB_ID NUMBER not null,
USER_ID VARCHAR2(36) not null,
GROUP_ID VARCHAR2(36) not null,
PROJECT_ID NUMBER not null,
ACCESS_FLAGS NUMBER not null,
STATE NUMBER not null,
LOCKED_BY VARCHAR2(36) not null,
LASTMODIFIED_BY VARCHAR2(36) not null,
DATE_CREATED DATE not null,
DATE_LASTMODIFIED DATE not null,
PUBLICATION_DATE DATE,
PURGE_DATE DATE,
FLAGS NUMBER,
FEED_ID NUMBER,
FEED_REFERENCE NUMBER,
FEED_FILENAME VARCHAR2(255),
TITLE VARCHAR2(255),
DATA_BIG_0 VARCHAR2(4000),
DATA_BIG_1 VARCHAR2(4000),
DATA_BIG_2 VARCHAR2(4000),
DATA_BIG_3 VARCHAR2(4000),
DATA_BIG_4 VARCHAR2(4000),
DATA_BIG_5 VARCHAR2(4000),
DATA_BIG_6 VARCHAR2(4000),
DATA_BIG_7 VARCHAR2(4000),
DATA_BIG_8 VARCHAR2(4000),
DATA_BIG_9 VARCHAR2(4000),
DATA_MEDIUM_0 VARCHAR2(2000),
DATA_MEDIUM_1 VARCHAR2(2000),
DATA_MEDIUM_2 VARCHAR2(2000),
DATA_MEDIUM_3 VARCHAR2(2000),
DATA_MEDIUM_4 VARCHAR2(2000),
DATA_MEDIUM_5 VARCHAR2(2000),
DATA_MEDIUM_6 VARCHAR2(2000),
DATA_MEDIUM_7 VARCHAR2(2000),
DATA_MEDIUM_8 VARCHAR2(2000),
DATA_MEDIUM_9 VARCHAR2(2000),
DATA_SMALL_0 VARCHAR2(500),
DATA_SMALL_1 VARCHAR2(500),
DATA_SMALL_2 VARCHAR2(500),
DATA_SMALL_3 VARCHAR2(500),
DATA_SMALL_4 VARCHAR2(500),
DATA_SMALL_5 VARCHAR2(500),
DATA_SMALL_6 VARCHAR2(500),
DATA_SMALL_7 VARCHAR2(500),
DATA_SMALL_8 VARCHAR2(500),
DATA_SMALL_9 VARCHAR2(500),
DATA_SMALL_10 VARCHAR2(500),
DATA_SMALL_11 VARCHAR2(500),
DATA_SMALL_12 VARCHAR2(500),
DATA_SMALL_13 VARCHAR2(500),
DATA_SMALL_14 VARCHAR2(500),
DATA_SMALL_15 VARCHAR2(500),
DATA_SMALL_16 VARCHAR2(500),
DATA_SMALL_17 VARCHAR2(500),
DATA_SMALL_18 VARCHAR2(500),
DATA_SMALL_19 VARCHAR2(500),
DATA_SMALL_20 VARCHAR2(500),
DATA_SMALL_21 VARCHAR2(500),
DATA_SMALL_22 VARCHAR2(500),
DATA_SMALL_23 VARCHAR2(500),
DATA_SMALL_24 VARCHAR2(500),
DATA_SMALL_25 VARCHAR2(500),
DATA_SMALL_26 VARCHAR2(500),
DATA_SMALL_27 VARCHAR2(500),
DATA_SMALL_28 VARCHAR2(500),
DATA_SMALL_29 VARCHAR2(500),
DATA_SMALL_30 VARCHAR2(500),
DATA_SMALL_31 VARCHAR2(500),
DATA_SMALL_32 VARCHAR2(500),
DATA_SMALL_33 VARCHAR2(500),
DATA_SMALL_34 VARCHAR2(500),
DATA_SMALL_35 VARCHAR2(500),
DATA_SMALL_36 VARCHAR2(500),
DATA_SMALL_37 VARCHAR2(500),
DATA_SMALL_38 VARCHAR2(500),
DATA_SMALL_39 VARCHAR2(500),
DATA_INT_0 NUMBER,
DATA_INT_1 NUMBER,
DATA_INT_2 NUMBER,
DATA_INT_3 NUMBER,
DATA_INT_4 NUMBER,
DATA_INT_5 NUMBER,
DATA_INT_6 NUMBER,
DATA_INT_7 NUMBER,
DATA_INT_8 NUMBER,
DATA_INT_9 NUMBER,
DATA_REFERENCE_0 NUMBER,
DATA_REFERENCE_1 NUMBER,
DATA_REFERENCE_2 NUMBER,
DATA_REFERENCE_3 NUMBER,
DATA_REFERENCE_4 NUMBER,
DATA_REFERENCE_5 NUMBER,
DATA_REFERENCE_6 NUMBER,
DATA_REFERENCE_7 NUMBER,
DATA_REFERENCE_8 NUMBER,
DATA_REFERENCE_9 NUMBER,
DATA_DATE_0 DATE,
DATA_DATE_1 DATE,
DATA_DATE_2 DATE,
DATA_DATE_3 DATE,
DATA_DATE_4 DATE,
primary key(MASTER_ID));

create table CMS_MODULE_CHANNEL_REL
(MASTER_ID VARCHAR2(36) not null,
CHANNEL_ID NUMBER not null,
primary key(CHANNEL_ID, MASTER_ID));

create table CMS_MODULE_MEDIA
(MEDIA_ID NUMBER not null,
MASTER_ID VARCHAR2(36) not null,
MEDIA_POSITION NUMBER not null,
MEDIA_WIDTH NUMBER not null,
MEDIA_HEIGHT NUMBER not null,
MEDIA_SIZE NUMBER not null,
MEDIA_MIMETYPE VARCHAR2(255),
MEDIA_TYPE NUMBER not null,
MEDIA_TITLE VARCHAR2(255),
MEDIA_NAME VARCHAR2(255),
MEDIA_DESCRIPTION VARCHAR2(500) not null,
MEDIA LONG RAW not null,
primary key(MEDIA_ID));

create table CMS_MODULE_BACKUP_MASTER
(MASTER_ID VARCHAR2(36) not null,
SUB_ID NUMBER not null,
USER_ID VARCHAR2(36) not null,
GROUP_ID VARCHAR2(36) not null,
PROJECT_ID NUMBER not null,
ACCESS_FLAGS NUMBER not null,
STATE NUMBER not null,
LOCKED_BY VARCHAR2(36) not null,
LASTMODIFIED_BY VARCHAR2(36) not null,
DATE_CREATED DATE not null,
DATE_LASTMODIFIED DATE not null,
PUBLICATION_DATE DATE,
PURGE_DATE DATE,
FLAGS NUMBER,
FEED_ID NUMBER,
FEED_REFERENCE NUMBER,
FEED_FILENAME VARCHAR2(255),
TITLE VARCHAR2(255),
DATA_BIG_0 VARCHAR2(4000),
DATA_BIG_1 VARCHAR2(4000),
DATA_BIG_2 VARCHAR2(4000),
DATA_BIG_3 VARCHAR2(4000),
DATA_BIG_4 VARCHAR2(4000),
DATA_BIG_5 VARCHAR2(4000),
DATA_BIG_6 VARCHAR2(4000),
DATA_BIG_7 VARCHAR2(4000),
DATA_BIG_8 VARCHAR2(4000),
DATA_BIG_9 VARCHAR2(4000),
DATA_MEDIUM_0 VARCHAR2(2000),
DATA_MEDIUM_1 VARCHAR2(2000),
DATA_MEDIUM_2 VARCHAR2(2000),
DATA_MEDIUM_3 VARCHAR2(2000),
DATA_MEDIUM_4 VARCHAR2(2000),
DATA_MEDIUM_5 VARCHAR2(2000),
DATA_MEDIUM_6 VARCHAR2(2000),
DATA_MEDIUM_7 VARCHAR2(2000),
DATA_MEDIUM_8 VARCHAR2(2000),
DATA_MEDIUM_9 VARCHAR2(2000),
DATA_SMALL_0 VARCHAR2(500),
DATA_SMALL_1 VARCHAR2(500),
DATA_SMALL_2 VARCHAR2(500),
DATA_SMALL_3 VARCHAR2(500),
DATA_SMALL_4 VARCHAR2(500),
DATA_SMALL_5 VARCHAR2(500),
DATA_SMALL_6 VARCHAR2(500),
DATA_SMALL_7 VARCHAR2(500),
DATA_SMALL_8 VARCHAR2(500),
DATA_SMALL_9 VARCHAR2(500),
DATA_SMALL_10 VARCHAR2(500),
DATA_SMALL_11 VARCHAR2(500),
DATA_SMALL_12 VARCHAR2(500),
DATA_SMALL_13 VARCHAR2(500),
DATA_SMALL_14 VARCHAR2(500),
DATA_SMALL_15 VARCHAR2(500),
DATA_SMALL_16 VARCHAR2(500),
DATA_SMALL_17 VARCHAR2(500),
DATA_SMALL_18 VARCHAR2(500),
DATA_SMALL_19 VARCHAR2(500),
DATA_SMALL_20 VARCHAR2(500),
DATA_SMALL_21 VARCHAR2(500),
DATA_SMALL_22 VARCHAR2(500),
DATA_SMALL_23 VARCHAR2(500),
DATA_SMALL_24 VARCHAR2(500),
DATA_SMALL_25 VARCHAR2(500),
DATA_SMALL_26 VARCHAR2(500),
DATA_SMALL_27 VARCHAR2(500),
DATA_SMALL_28 VARCHAR2(500),
DATA_SMALL_29 VARCHAR2(500),
DATA_SMALL_30 VARCHAR2(500),
DATA_SMALL_31 VARCHAR2(500),
DATA_SMALL_32 VARCHAR2(500),
DATA_SMALL_33 VARCHAR2(500),
DATA_SMALL_34 VARCHAR2(500),
DATA_SMALL_35 VARCHAR2(500),
DATA_SMALL_36 VARCHAR2(500),
DATA_SMALL_37 VARCHAR2(500),
DATA_SMALL_38 VARCHAR2(500),
DATA_SMALL_39 VARCHAR2(500),
DATA_INT_0 NUMBER,
DATA_INT_1 NUMBER,
DATA_INT_2 NUMBER,
DATA_INT_3 NUMBER,
DATA_INT_4 NUMBER,
DATA_INT_5 NUMBER,
DATA_INT_6 NUMBER,
DATA_INT_7 NUMBER,
DATA_INT_8 NUMBER,
DATA_INT_9 NUMBER,
DATA_REFERENCE_0 NUMBER,
DATA_REFERENCE_1 NUMBER,
DATA_REFERENCE_2 NUMBER,
DATA_REFERENCE_3 NUMBER,
DATA_REFERENCE_4 NUMBER,
DATA_REFERENCE_5 NUMBER,
DATA_REFERENCE_6 NUMBER,
DATA_REFERENCE_7 NUMBER,
DATA_REFERENCE_8 NUMBER,
DATA_REFERENCE_9 NUMBER,
DATA_DATE_0 DATE,
DATA_DATE_1 DATE,
DATA_DATE_2 DATE,
DATA_DATE_3 DATE,
DATA_DATE_4 DATE,
VERSION_ID NUMBER not null,
USER_NAME VARCHAR(167),
GROUP_NAME VARCHAR(64),
LASTMODIFIED_BY_NAME VARCHAR(167),
primary key(MASTER_ID, VERSION_ID));

create table CMS_MODULE_BACKUP_MEDIA    
(MEDIA_ID NUMBER not null,
MASTER_ID VARCHAR2(36) not null,
MEDIA_POSITION NUMBER not null,
MEDIA_WIDTH NUMBER not null,
MEDIA_HEIGHT NUMBER not null,
MEDIA_SIZE NUMBER not null,
MEDIA_MIMETYPE VARCHAR2(255),
MEDIA_TYPE NUMBER not null,
MEDIA_TITLE VARCHAR2(255),
MEDIA_NAME VARCHAR2(255),
MEDIA_DESCRIPTION VARCHAR2(500) not null,
MEDIA LONG RAW not null,
VERSION_ID NUMBER not null,
primary key(MEDIA_ID, VERSION_ID));

CREATE INDEX MASTER_SUBID ON CMS_MODULE_MASTER(SUB_ID);
  
CREATE INDEX ONLINEMASTER_SUBID ON CMS_MODULE_ONLINE_MASTER(SUB_ID);
  
CREATE INDEX ONLINEMEDIA_MASTERID ON CMS_MODULE_ONLINE_MEDIA(MASTER_ID);
  
CREATE INDEX MEDIA_MASTERID ON CMS_MODULE_MEDIA(MASTER_ID);

CREATE TABLE CMS_WEBUSERS (
USER_ID VARCHAR(36) BINARY NOT NULL,
USER_MEMBER_ID VARCHAR(255),
USER_SALUTATION VARCHAR(255),
USER_TITLE VARCHAR(255),
USER_PWD VARCHAR(255),   
USER_PWD_QUESTION VARCHAR(255),   
USER_PWD_ANSWER VARCHAR(255),  
USER_CITY VARCHAR(255),
USER_POSTCODE VARCHAR(255),
USER_STATE VARCHAR(255),
USER_COUNTRY VARCHAR(255),
USER_ADDRESS_TYPE INT,
USER_BIRTHDAY DATETIME,
USER_PHONE VARCHAR(255),
USER_FAX VARCHAR(255),
USER_MOBILE VARCHAR(255),
USER_ACCEPT INT,   
USER_RECOMMENDED_BY VARCHAR(255), 
USER_PROFESSION VARCHAR(255),
USER_COMPANY VARCHAR(255),
USER_DEPARTMENT VARCHAR(255),
USER_POSITION VARCHAR(255),   
USER_ACCOUNT_NUMBER VARCHAR(255),
USER_BANK_NUMBER VARCHAR(255),
USER_BANK VARCHAR(255),
USER_NEWSLETTER VARCHAR(255),   
USER_EXTRAINFO_1 VARCHAR(255),
USER_EXTRAINFO_2 VARCHAR(255),
USER_EXTRAINFO_3 VARCHAR(255),
USER_EXTRAINFO_4 VARCHAR(255),
USER_EXTRAINFO_5 VARCHAR(255),
USER_EXTRAINFO_6 VARCHAR(255),
USER_EXTRAINFO_7 VARCHAR(255),
USER_EXTRAINFO_8 VARCHAR(255),
USER_EXTRAINFO_9 VARCHAR(255),
USER_EXTRAINFO_10 VARCHAR(255),
USER_EXTRAINFO_11 VARCHAR(255),
USER_EXTRAINFO_12 VARCHAR(255),
USER_EXTRAINFO_13 VARCHAR(255),
USER_EXTRAINFO_14 VARCHAR(255),
USER_EXTRAINFO_15 VARCHAR(255),
USER_EXTRAINFO_16 VARCHAR(255),
USER_EXTRAINFO_17 VARCHAR(255),
USER_EXTRAINFO_18 VARCHAR(255),
USER_EXTRAINFO_19 TEXT,
USER_EXTRAINFO_20 TEXT,
USER_PICTURE MEDIUMBLOB,
USER_PICTURE_NAME VARCHAR(255),   
USER_CREATE_DATE DATETIME,
USER_LASTCHANGE_BY VARCHAR(255),
USER_LASTCHANGE_DATE DATETIME,
LOCKSTATE INT,
PRIMARY KEY (USER_ID)
);