SQLize | PHPize | SQLtest

create table CCM_ATTACHMENTS ( ATTACHMENT_ID VARCHAR2(50 char) not null, MESSAGE_ID VARCHAR2(50 char) not null, CUSTOMER_ID VARCHAR2(50 char) not null, FILENAME VARCHAR2(255 char), MIME_TYPE VARCHAR2(255 char), FILE_SIZE NUMBER(7,0), FILE_DATA BLOB, CREATION_TIME DATE default CURRENT_TIMESTAMP, DELETION_TIME DATE ); CREATE UNIQUE INDEX CCM_ATTACHMENTS_UQ_ID ON CCM_ATTACHMENTS (ATTACHMENT_ID); ALTER TABLE CCM_ATTACHMENTS ADD CONSTRAINT CHECK_FILE_SIZE CHECK (FILE_SIZE BETWEEN 0 AND 3145729) ENABLE; ALTER TABLE CCM_ATTACHMENTS ADD CONSTRAINT CCM_ATTACHMENTS_UQ_ID UNIQUE (ATTACHMENT_ID) USING INDEX CCM_ATTACHMENTS_UQ_ID ENABLE; create table CCM_ATTACHMENTS_UPLOADED ( ATTACHMENT_ID VARCHAR2(50 char) not null, MESSAGE_ID VARCHAR2(50 char), CUSTOMER_ID VARCHAR2(50 char) not null, FILENAME VARCHAR2(255 char), MIME_TYPE VARCHAR2(255 char), FILE_SIZE NUMBER(7,0), FILE_DATA BLOB, CREATION_TIME DATE default CURRENT_TIMESTAMP, DELETION_TIME DATE ); CREATE UNIQUE INDEX CCM_ATTACHMENTS_UPLOADED_UQ_ID ON CCM_ATTACHMENTS_UPLOADED (ATTACHMENT_ID); ALTER TABLE CCM_ATTACHMENTS_UPLOADED ADD CONSTRAINT CHECK_FILE_SIZE CHECK (FILE_SIZE BETWEEN 0 AND 3145729) ENABLE; ALTER TABLE CCM_ATTACHMENTS_UPLOADED ADD CONSTRAINT CCM_ATTACHMENTS_UPLOADED_UQ_ID UNIQUE (ATTACHMENT_ID) USING INDEX CCM_ATTACHMENTS_UPLOADED_UQ_ID ENABLE; create table CCM_CATEGORIES ( CATEGORY_ID VARCHAR2(20) not null, CATEGORY_PERMISSION VARCHAR2(20) not null, CATEGORY_NAME VARCHAR2(50) not null, CATEGORY_ORDER NUMBER(3,0) ); ALTER TABLE CCM_CATEGORIES ADD CONSTRAINT CCM_CATEGORIES_PK PRIMARY KEY (CATEGORY_ID); -- alter table tab_name add constraint tab_name_pk primary key (pk_column1[, pk_column2,...]); create table CCM_MESSAGES ( MESSAGE_ID VARCHAR2(50 char) not null, CUSTOMER_ID VARCHAR2(50 char) not null, THREAD_ID VARCHAR2(50 char) not null, SUBJECT VARCHAR2(100 char), CONTENT VARCHAR2(4000 char) not null, HELPDESK_USERID VARCHAR2(300 char), HELPDESK_SENDER_NAME VARCHAR2(300 char), NOTES VARCHAR2(1000 char), MESSAGE_STATUS VARCHAR2(20 char), ALLOW_REPLY NUMBER(1,0), SENDER VARCHAR2(50 char), CREATION_TIME DATE default CURRENT_TIMESTAMP not null, UNCOMMITTED_CONTENT VARCHAR2(4000 char), READ_STATUS NUMBER(1) default 0, MESSAGE_THREAD_HEAD NUMBER(1) default 0, CUSTOMER_BROWSER VARCHAR2(500 char), TIME_READ TIMESTAMP(6), DELETED NUMBER(1) default 0, DELETED_TIME DATE, HELPDESK_READ_STATUS NUMBER(1) default 0, CATEGORY VARCHAR2(20), OFFER_APPROVED_TIME DATE ); CREATE INDEX CCM_MESSAGES_STATUS_IDX ON CCM_MESSAGES (MESSAGE_STATUS); CREATE INDEX CCM_MESSAGES_SSA_IDX ON CCM_MESSAGES (MESSAGE_STATUS, SENDER); CREATE UNIQUE INDEX CCM_MESSAGES_MESS_IDX ON CCM_MESSAGES (MESSAGE_ID, CREATION_TIME); CREATE INDEX CREATION_TIME_IDX ON CCM_MESSAGES (CREATION_TIME); CREATE INDEX CCM_MESSAGES_USER_KEY_IDX ON CCM_MESSAGES (CUSTOMER_ID, SENDER); CREATE INDEX CCM_MESSAGES_USER_IDX ON CCM_MESSAGES (CUSTOMER_ID); CREATE UNIQUE INDEX CCM_MESSAGES_PK ON CCM_MESSAGES (MESSAGE_ID); ALTER TABLE CCM_MESSAGES ADD CONSTRAINT CHECK_ALLOW_REPLY CHECK (ALLOW_REPLY in(0,1)) ENABLE; ALTER TABLE CCM_MESSAGES ADD CONSTRAINT CCM_MESSAGES_PK PRIMARY KEY (MESSAGE_ID) USING INDEX CCM_MESSAGES_PK ENABLE; create table CCM_MESSAGE_THREADS ( THREAD_ID VARCHAR2(50 char) not null, CUSTOMER_ID VARCHAR2(50 char) not null, MESSAGE_ID VARCHAR2(50 char) not null ); CREATE UNIQUE INDEX CCM_MESSAGE_THREADS_PK ON CCM_MESSAGE_THREADS (THREAD_ID, CUSTOMER_ID, MESSAGE_ID); ALTER TABLE CCM_MESSAGE_THREADS ADD CONSTRAINT CCM_MESSAGE_THREADS_PK PRIMARY KEY (THREAD_ID, CUSTOMER_ID, MESSAGE_ID) USING INDEX CCM_MESSAGE_THREADS_PK ENABLE;