refactions3.sql

derby database source code.good for you.

    SUBJECTID   INTEGER NOT NULL,
    ACTIONS     INTEGER NOT NULL,
    OBJECTTYPE  INTEGER NOT NULL,
    OBJECTID    INTEGER NOT NULL
);

CREATE INDEX IX2000A ON ACL ( OBJECTTYPE, SUBJECTTYPE, SUBJECTID, OBJECTID, ACTIONS );
CREATE INDEX IX2000B ON ACL ( SUBJECTTYPE, OBJECTTYPE, OBJECTID );


CREATE TABLE VAULT_RESOURCES (
     RESOURCE_NAME VARCHAR(255) NOT NULL,

     CONSTRAINT PK2010 PRIMARY KEY (RESOURCE_NAME)
);

CREATE TABLE VAULT_DATA (
    RESOURCE_NAME VARCHAR(255) NOT NULL,
    USER_DN VARCHAR(255) NOT NULL,
    USERID VARCHAR(255),
    PWD VARCHAR(255),
    BINARY_DATA LONG VARCHAR FOR BIT DATA,

    CONSTRAINT PK2020 PRIMARY KEY (RESOURCE_NAME, USER_DN),
    CONSTRAINT FK2020 FOREIGN KEY (RESOURCE_NAME) REFERENCES VAULT_RESOURCES (RESOURCE_NAME) ON DELETE CASCADE
);


CREATE SCHEMA WPSPCO;

CREATE TABLE WPSPCO.FORMAT (
    OID INTEGER NOT NULL,
    NAME VARCHAR(100) NOT NULL,
    IS_INDEX_CONTENT CHAR(1) DEFAULT 'N' NOT NULL,
    USE_SMODE_PLUGIN CHAR(1) DEFAULT 'N' NOT NULL,
    MGR_CLASSNAME VARCHAR(255) NOT NULL,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK1000 PRIMARY KEY (OID),
    CONSTRAINT UN1000 UNIQUE (NAME)
);

CREATE TABLE WPSPCO.FORMAT_TAG (
    FORMAT_OID INTEGER NOT NULL,
    TAG_NAME VARCHAR(255) NOT NULL,
    COLUMN_NAME VARCHAR(255) NOT NULL,
    METHOD_NAME VARCHAR(255) NOT NULL,

    CONSTRAINT PK1010 PRIMARY KEY (FORMAT_OID, TAG_NAME),
    CONSTRAINT FK1010 FOREIGN KEY ( FORMAT_OID ) REFERENCES WPSPCO.FORMAT ( OID ) ON DELETE CASCADE
);

CREATE TABLE WPSPCO.FORMAT_ATTR (
    FORMAT_OID INTEGER NOT NULL,
    ATTRIBUTE_NAME VARCHAR(255) NOT NULL,
    IS_INITIAL CHAR(1) DEFAULT 'N' NOT NULL,
    VIEWABLE_SMODE CHAR(1) DEFAULT 'N' NOT NULL,
    VIEWABLE_TMODE CHAR(1) DEFAULT 'N' NOT NULL,
    IS_INDEXED CHAR(1) DEFAULT 'N' NOT NULL,

    CONSTRAINT PK1020 PRIMARY KEY (FORMAT_OID, ATTRIBUTE_NAME),
    CONSTRAINT FK1020 FOREIGN KEY ( FORMAT_OID ) REFERENCES WPSPCO.FORMAT ( OID ) ON DELETE CASCADE
);

CREATE TABLE WPSPCO.RES_COLLECTION (
    OID INTEGER NOT NULL,
    NAME VARCHAR(64) NOT NULL,
    STATE INTEGER NOT NULL,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK1030 PRIMARY KEY (OID)
);

CREATE INDEX WPSPCO.IX1030A ON WPSPCO.RES_COLLECTION ( OID, NAME );
CREATE INDEX WPSPCO.IX1030B ON WPSPCO.RES_COLLECTION ( NAME );

CREATE TABLE WPSPCO.WORKINGSET (
    URI VARCHAR(255) NOT NULL,
    RESOURCEID VARCHAR(155) NOT NULL,
    TITLE VARCHAR(100),
    DESCRIPTION VARCHAR(1000),
    CREATOR VARCHAR(100),
    CONTRIBUTOR VARCHAR(100),
    SOURCE VARCHAR(100),
    DCDATE DATE,
    COVERAGE VARCHAR(100),
    IDENTIFIER VARCHAR(255),
    LANG VARCHAR(100),
    PUBLISHER VARCHAR(100),
    RELATION VARCHAR(100),
    RIGHTS VARCHAR(100),
    SUBJECT VARCHAR(100),
    FORMAT VARCHAR(100),
    DCTYPE VARCHAR(100),
    CONTENTSIZE BIGINT,
    FULLCONTENT LONG VARCHAR FOR BIT DATA,
    ISINDEXED CHAR(1) DEFAULT '0' NOT NULL,
    MARKEDFORDEL CHAR(1) DEFAULT '0' NOT NULL,

    CONSTRAINT PK1040 PRIMARY KEY (URI)
);

CREATE TABLE WPSPCO.URI_LIST (
    URI VARCHAR(255) NOT NULL,
    CONTENTURI VARCHAR(255) NOT NULL,

    CONSTRAINT FK1050 FOREIGN KEY (URI) REFERENCES WPSPCO.WORKINGSET (URI) ON DELETE CASCADE

);

CREATE TABLE WPSPCO.RSS_ITEM (
    TITLE VARCHAR(155) NOT NULL,
    DESCRIPTION VARCHAR(1000),
    LINK VARCHAR(512),
    CHANNELTITLE VARCHAR(512),
    CONTENT VARCHAR(1000),
    ISINDEXED CHAR(1) DEFAULT '0',
    MARKEDFORDEL CHAR(1) DEFAULT '0',

    CONSTRAINT PK1060 PRIMARY KEY (TITLE)
);

CREATE TABLE WPSPCO.RES_UPDATES (
    RESCOLLNAME VARCHAR(100),
    RESOURCEID VARCHAR(155) NOT NULL,
    STATE INTEGER NOT NULL,
    CONTENTFORMAT VARCHAR(100) NOT NULL
);

CREATE TABLE WPSPCO.PATH (
    PATH VARCHAR(105) NOT NULL,
    RESCOLLNAME VARCHAR(100) NOT NULL
);

CREATE TABLE WPSPCO.RES_INFO (
    OID INTEGER NOT NULL,
    URI VARCHAR(255) NOT NULL,
    RESOURCE_ID VARCHAR(155) NOT NULL,
    CONTENT_FORMAT VARCHAR(100) NOT NULL,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK1090 PRIMARY KEY (OID),
    CONSTRAINT UN1090 UNIQUE (URI)
);

CREATE TABLE WPSPCO.RES_INFO_CAT (
    RESOURCE_INFO_OID INTEGER NOT NULL,
    CATEGORY_NUMBER INTEGER  NOT NULL,
    IS_MEMBER CHAR(1) DEFAULT 'N' NOT NULL,

    CONSTRAINT PK1100 PRIMARY KEY (RESOURCE_INFO_OID, CATEGORY_NUMBER),
    CONSTRAINT FK1100 FOREIGN KEY (RESOURCE_INFO_OID) REFERENCES WPSPCO.RES_INFO (OID) ON DELETE CASCADE
);

CREATE TABLE WPSPCO.CONTENT_CAT (
    OID INTEGER NOT NULL,
    CATEGORY_NUMBER INTEGER NOT NULL,
    CATEGORY_NAME VARCHAR(100) NOT NULL,
    IS_ACTIVE CHAR(1) DEFAULT 'Y' NOT NULL,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK1110 PRIMARY KEY (OID),
    CONSTRAINT UN1110 UNIQUE (CATEGORY_NUMBER)
);

CREATE TABLE WPSPCO.PUBLISH_STATUS (
    OID INTEGER NOT NULL,
    N_PUBLISHED INTEGER NOT NULL,
    N_DOCUMENTS INTEGER NOT NULL,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK1120 PRIMARY KEY (OID)
);


--END OF WEBSPERE PORTTAL CASES

---SOME TEST CASES GOT FROM DB2 TESTS.

--some test cases got from db2 tests.
CREATE SCHEMA refint;
set schema refint ;

CREATE TABLE refint.E010_T1 (C0 CHAR(3) NOT NULL,
                              C1 CHAR(3) NOT NULL,
                              C0b  CHAR(3)  ,
                              C2  CHAR(3)  ,
                              C3  INTEGER NOT NULL,
                              C4  INTEGER,
                              C5  DECIMAL(9,3) NOT NULL,
                              C6  FLOAT,
                              C7  VARCHAR(20) NOT NULL,
                              C8  LONG VARCHAR,
                              C9  DATE NOT NULL,
                              C10 TIME,
                              C11 TIMESTAMP,
                              PRIMARY KEY (C0, C1, C3, C9),
                              CONSTRAINT E010_T1_SELFREF 
                              FOREIGN KEY (C0b, C2, C4, C9)
                              REFERENCES refint.E010_T1 ON DELETE SET NULL);

CREATE VIEW refint.E010_V1 AS SELECT * FROM refint.E010_T1;

CREATE TABLE refint.E020_T1 (C0 CHAR(3) NOT NULL,
                              C1 CHAR(3) NOT NULL,
                              C0b  CHAR(3),
                              C2  CHAR(3),
                              C0c  CHAR(3),
                              C3 CHAR(3),
                              PRIMARY KEY (C0, C1),
                              CONSTRAINT E020_T1_SELFREF  FOREIGN KEY (C0b, C2)
                              REFERENCES refint.E020_T1 ON DELETE RESTRICT);


CREATE TABLE refint.E030_T1 (C0 CHAR(3) NOT NULL,
                              C1 CHAR(3) NOT NULL,
                              C0b  CHAR(3),
                              C2  CHAR(3),
                              PRIMARY KEY (C0, C1),
                              CONSTRAINT E030_T1_SELFREF FOREIGN KEY (C0b, C2)
                              REFERENCES refint.E030_T1 ON DELETE CASCADE);


CREATE TABLE refint.E110_T1 (C0 CHAR(3) NOT NULL,
                              C1 CHAR(3) NOT NULL,
                              PRIMARY KEY (C0, C1));

CREATE TABLE refint.E110_T2 (C0 CHAR(3),
                              C1 CHAR(3),
                              C0b CHAR(3),
                              C2  CHAR(3)  ,
                              CONSTRAINT E110_T1_T2 FOREIGN KEY (C0b, C2)
                              REFERENCES refint.E110_T1 ON DELETE CASCADE);

CREATE VIEW refint.E110_V1 AS SELECT * FROM refint.E110_T1;
CREATE VIEW refint.E110_V2 AS SELECT * FROM refint.E110_T2;

CREATE TABLE refint.E210_T1 (C0 CHAR(3) NOT NULL,
                              C1 CHAR(3) NOT NULL,
                              PRIMARY KEY (C0,C1));

CREATE TABLE refint.E210_T2 (C0 CHAR(3) NOT NULL,
                              C1 CHAR(3) NOT NULL,
                              C0b  CHAR(3)  ,
                              C2  CHAR(3)  ,
                              PRIMARY KEY (C0,C1),
                              CONSTRAINT E210_T1_T2  FOREIGN KEY (C0b,C2)
                              REFERENCES refint.E210_T1 ON DELETE CASCADE);

CREATE TABLE refint.E210_T3 (C0 CHAR(3),
                              C1 CHAR(3),
                              C0b  CHAR(3)  ,
                              C2  CHAR(3)  ,
                              CONSTRAINT FK12 FOREIGN KEY (C0b, C2)
                              REFERENCES refint.E210_T1 ON DELETE CASCADE);

CREATE VIEW refint.E210_V1 AS SELECT * FROM refint.E210_T1;
CREATE VIEW refint.E210_V2 (C1, C2) AS
    SELECT refint.E210_T2.C1, refint.E210_T3.C1
    FROM refint.E210_T2, refint.E210_T3
    WHERE refint.E210_T2.C1 = refint.E210_T3.C1;

CREATE TABLE refint.E120_T1 (C0 CHAR(3) NOT NULL,
                              C1 CHAR(3) NOT NULL,
                              C2 CHAR(3),
                              PRIMARY KEY (C0, C1));

CREATE TABLE refint.E120_T2 (C0 CHAR(3),
                              C1 CHAR(3),
                              C0b CHAR(3),
                              C2 CHAR(3),
                              CONSTRAINT E120_T1_T2  FOREIGN KEY (C0b, C2)
                              REFERENCES refint.E120_T1 ON DELETE RESTRICT);

CREATE VIEW refint.E120_V1 AS SELECT * FROM refint.E120_T1;
CREATE VIEW refint.E120_V2 AS SELECT * FROM refint.E120_V1;


CREATE TABLE refint.RJCE120_T1 (C0 INTEGER NOT NULL,
                                 C1 INTEGER NOT NULL,
                                 C2 INTEGER,
                                 PRIMARY KEY (C0, C1));

CREATE TABLE refint.RJCE120_T2 (C0 INTEGER NOT NULL,
                                 C1 INTEGER NOT NULL,
                                 C0b   INTEGER,
                                 C2   INTEGER,
                                 PRIMARY KEY (C0, C1),
                                 CONSTRAINT RJCE120_T1_T2 FOREIGN KEY (C0b, C2)
                                 REFERENCES refint.RJCE120_T1 ON DELETE RESTRICT);

CREATE VIEW refint.RJCE120_V1 AS SELECT * FROM refint.RJCE120_T1;
CREATE VIEW refint.RJCE120_V2 AS SELECT * FROM refint.RJCE120_V1;

CREATE TABLE refint.E130_T1 (C0 CHAR(3) NOT NULL,
                              C1 CHAR(3) NOT NULL,
                              PRIMARY KEY (C0, C1));

CREATE TABLE refint.E130_T2 (C0 CHAR(3),
                              C1 CHAR(3),
                              C0b  CHAR(3),
                              C2   CHAR(3),
                              CONSTRAINT E130_T1_T2 FOREIGN KEY (C0b,C2)
                              REFERENCES refint.E130_T1 ON DELETE SET NULL);

CREATE VIEW refint.E130_V1 AS SELECT * FROM refint.E130_T1;

CREATE TABLE refint.E140_T1 (C0 CHAR(3) NOT NULL,
                              C1 CHAR(3) NOT NULL,
                              PRIMARY KEY (C0,C1));

CREATE TABLE refint.E140_T2 (C0 CHAR(3),
                              C1 CHAR(3),
                              C0b CHAR(3),
                              C2  CHAR(3),
                              CONSTRAINT E140_T1_T2  FOREIGN KEY (C0b, C2)
                              REFERENCES refint.E140_T1 ON UPDATE RESTRICT);

CREATE VIEW refint.E140_V1 AS SELECT * FROM refint.E140_T1;

CREATE TABLE refint.E220_T1 (C0 CHAR(3) NOT NULL,
                              C1 CHAR(3) NOT NULL,
                              PRIMARY KEY (C0, C1));

CREATE TABLE refint.E220_T2 (C0 CHAR(3),
                              C1 CHAR(3),
                              C0b  CHAR(3),
                              C2  CHAR(3),
                              CONSTRAINT FK13 FOREIGN KEY (C0b, C2)
                              REFERENCES refint.E220_T1 ON DELETE RESTRICT);

CREATE TABLE refint.E220_T3 (C0 CHAR(3),
                              C1 CHAR(3),
                              C0b  CHAR(3),
                              C2  CHAR(3),
                              CONSTRAINT FK32 FOREIGN KEY (C0b,C2)
                              REFERENCES refint.E220_T1 ON DELETE CASCADE);


CREATE TABLE refint.E230_T1 (C0 CHAR(3) NOT NULL,
                              C1 CHAR(3) NOT NULL,
                              PRIMARY KEY (C0,C1));

CREATE TABLE refint.E230_T2 (C0 CHAR(3) NOT NULL,
                              C1 CHAR(3) NOT NULL,
                              C0b  CHAR(3)  ,
                              C2  CHAR(3)  ,
                              PRIMARY KEY (C0,C1),
                              CONSTRAINT FK14 FOREIGN KEY (C0b,C2)