refactions3.sql

derby database source code.good for you.

-- webSphere Portal Server Development test cases 

CREATE TABLE ID_TABLE (
    TABLE_NAME VARCHAR(128) NOT NULL,
    LAST_ID INTEGER NOT NULL,
    CONSTRAINT PK10 PRIMARY KEY (TABLE_NAME)
);


CREATE TABLE APP_DESC (
    OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    RESOURCE_ROOT VARCHAR(255),
    CONTEXT_ROOT VARCHAR(255),
    IS_ACTIVE CHAR(1) NOT NULL,
    IS_REMOVABLE CHAR(1) DEFAULT 'Y' NOT NULL,
    SYSTEM_ACCESS CHAR(1) NOT NULL,
    GUID VARCHAR(255),
    MAJOR_VERSION INTEGER,
    MINOR_VERSION INTEGER,
    PARENT_OID INTEGER,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK20 PRIMARY KEY ( OID ),
    CONSTRAINT FK20 FOREIGN KEY ( PARENT_OID ) REFERENCES APP_DESC ( OID ) ON DELETE CASCADE
);

CREATE INDEX IX20A ON APP_DESC ( OID, NAME );
CREATE INDEX IX20B ON APP_DESC ( GUID );
CREATE INDEX IX20C ON APP_DESC ( PARENT_OID );

CREATE TABLE APP_DESC_DD (
    APP_DESC_OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    IS_LARGE CHAR(1) DEFAULT 'N' NOT NULL,
    IS_STRING CHAR(1) DEFAULT 'Y' NOT NULL,
    VALUE VARCHAR(255) FOR BIT DATA,
    LARGE_VALUE LONG VARCHAR FOR BIT DATA,

    CONSTRAINT PK30 PRIMARY KEY ( APP_DESC_OID , NAME ),
    CONSTRAINT FK30 FOREIGN KEY ( APP_DESC_OID ) REFERENCES APP_DESC ( OID ) ON DELETE CASCADE
);


CREATE TABLE PORT_DESC (
    OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    SERVLET_MAPPING VARCHAR(255),
    IS_ACTIVE CHAR(1) NOT NULL,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,
    WINDOW_STATES INTEGER NOT NULL,
    LISTENERS INTEGER NOT NULL,
    SM_ICON_URL VARCHAR(255),
    LG_ICON_URL VARCHAR(255),
    EXPIRES INTEGER NOT NULL,
    IS_SHARED CHAR(1) NOT NULL,
    APP_DESC_OID INTEGER NOT NULL,
    DEFAULT_LOCALE VARCHAR(64),
    IS_REMOTE CHAR(1) DEFAULT 'N' NOT NULL,
    IS_PUBLISHED CHAR(1) DEFAULT 'N' NOT NULL,
    REFERENCE_ID VARCHAR(64),
    MAJOR_VERSION INTEGER,
    MINOR_VERSION INTEGER,
    PARENT_OID INTEGER,

    CONSTRAINT PK40 PRIMARY KEY (OID),
    CONSTRAINT FK40A FOREIGN KEY ( APP_DESC_OID ) REFERENCES APP_DESC (OID) ON DELETE CASCADE,
    CONSTRAINT FK40B FOREIGN KEY ( PARENT_OID ) REFERENCES PORT_DESC (OID) ON DELETE CASCADE
);

CREATE INDEX IX40A ON PORT_DESC ( OID, NAME );
CREATE INDEX IX40D ON PORT_DESC ( REFERENCE_ID, APP_DESC_OID );
CREATE INDEX IX40E ON PORT_DESC ( APP_DESC_OID );

CREATE TABLE PORT_DESC_LOD (
    PORT_DESC_OID INTEGER NOT NULL,
    LOCALE VARCHAR(64) NOT NULL,
    TITLE VARCHAR(255),
    TITLE_SHORT VARCHAR(128),
    DESCRIPTION VARCHAR(1024),
    KEYWORDS VARCHAR(1024),

    CONSTRAINT PK50 PRIMARY KEY ( PORT_DESC_OID, LOCALE ),
    CONSTRAINT FK50 FOREIGN KEY (PORT_DESC_OID) REFERENCES PORT_DESC (OID) ON DELETE CASCADE
);

CREATE TABLE PORT_DESC_MAD (
    PORT_DESC_OID INTEGER NOT NULL,
    MARKUP VARCHAR(128) NOT NULL,
    MODES INTEGER NOT NULL,

    CONSTRAINT PK60 PRIMARY KEY ( PORT_DESC_OID, MARKUP ),
    CONSTRAINT FK60 FOREIGN KEY (PORT_DESC_OID) REFERENCES PORT_DESC (OID) ON DELETE CASCADE
);

CREATE TABLE PORT_DESC_DD (
    PORT_DESC_OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    IS_LARGE CHAR(1) DEFAULT 'N' NOT NULL,
    IS_STRING CHAR(1) DEFAULT 'Y' NOT NULL,
    VALUE VARCHAR(255) FOR BIT DATA,
    LARGE_VALUE LONG VARCHAR FOR BIT DATA,

    CONSTRAINT PK70 PRIMARY KEY ( PORT_DESC_OID , NAME ),
    CONSTRAINT FK70 FOREIGN KEY ( PORT_DESC_OID ) REFERENCES PORT_DESC ( OID ) ON DELETE CASCADE
);


CREATE TABLE COMP_DESC (
    OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,
    CLASS_NAME VARCHAR(128) NOT NULL,
    IS_ACTIVE CHAR(1) NOT NULL,
    IS_HIDDEN CHAR(1) NOT NULL,
    SM_ICON_URL VARCHAR(255),
    LG_ICON_URL VARCHAR(255),

    CONSTRAINT PK100 PRIMARY KEY (OID)
);

CREATE TABLE COMP_DESC_LOD (
    COMP_DESC_OID INTEGER NOT NULL,
    LOCALE VARCHAR(64) NOT NULL,
    TITLE VARCHAR(255),

    CONSTRAINT PK110 PRIMARY KEY ( COMP_DESC_OID , LOCALE ),
    CONSTRAINT FK110 FOREIGN KEY ( COMP_DESC_OID ) REFERENCES COMP_DESC ( OID ) ON DELETE CASCADE
);

CREATE TABLE COMP_DESC_MAD (
    COMP_DESC_OID INTEGER NOT NULL,
    MARKUP VARCHAR(128) NOT NULL,
    MODES INTEGER NOT NULL,

    CONSTRAINT PK120 PRIMARY KEY ( COMP_DESC_OID , MARKUP ),
    CONSTRAINT FK120 FOREIGN KEY ( COMP_DESC_OID ) REFERENCES COMP_DESC ( OID ) ON DELETE CASCADE
);

CREATE TABLE COMP_DESC_DD (
    COMP_DESC_OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    VALUE VARCHAR(255) NOT NULL,

    CONSTRAINT PK130 PRIMARY KEY ( COMP_DESC_OID , NAME ),
    CONSTRAINT FK130 FOREIGN KEY ( COMP_DESC_OID ) REFERENCES COMP_DESC ( OID ) ON DELETE CASCADE
);


CREATE TABLE SKIN_DESC (
    OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    RESOURCE_ROOT VARCHAR(255) NOT NULL,
    IS_ACTIVE CHAR(1) NOT NULL,
    IS_SYSTEM CHAR(1) DEFAULT 'N' NOT NULL,
    IS_DEFAULT CHAR(1) DEFAULT 'N' NOT NULL,
    DEFAULT_LOCALE VARCHAR(64),
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK140 PRIMARY KEY (OID)
);

CREATE INDEX IX140 ON SKIN_DESC ( OID, NAME );

CREATE TABLE SKIN_DESC_LOD (
    SKIN_DESC_OID INTEGER NOT NULL,
    LOCALE VARCHAR(64) NOT NULL,
    TITLE VARCHAR(255) NOT NULL,
    DESCRIPTION VARCHAR(1024),

    CONSTRAINT PK150 PRIMARY KEY ( SKIN_DESC_OID, LOCALE ),
    CONSTRAINT FK150 FOREIGN KEY (SKIN_DESC_OID) REFERENCES SKIN_DESC (OID) ON DELETE CASCADE
);

CREATE TABLE SKIN_DESC_MAD (
    SKIN_DESC_OID INTEGER NOT NULL,
    MARKUP VARCHAR(128) NOT NULL,

    CONSTRAINT PK160 PRIMARY KEY ( SKIN_DESC_OID, MARKUP ),
    CONSTRAINT FK160 FOREIGN KEY (SKIN_DESC_OID) REFERENCES SKIN_DESC (OID) ON DELETE CASCADE
);


CREATE TABLE THEME_DESC (
    OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    RESOURCE_ROOT VARCHAR(255) NOT NULL,
    IS_ACTIVE CHAR(1) NOT NULL,
    IS_SYSTEM CHAR(1) DEFAULT 'N' NOT NULL,
    IS_DEFAULT CHAR(1) DEFAULT 'N' NOT NULL,
    DEFAULT_LOCALE VARCHAR(64),
    DEFAULT_SKIN INTEGER,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK170 PRIMARY KEY (OID),
    CONSTRAINT FK170 FOREIGN KEY (DEFAULT_SKIN) REFERENCES SKIN_DESC (OID) ON DELETE SET NULL
);

CREATE INDEX IX170 ON THEME_DESC ( OID, NAME );

CREATE TABLE THEME_DESC_LOD (
    THEME_DESC_OID INTEGER NOT NULL,
    LOCALE VARCHAR(64) NOT NULL,
    TITLE VARCHAR(255) NOT NULL,
    DESCRIPTION VARCHAR(1024),

    CONSTRAINT PK180 PRIMARY KEY ( THEME_DESC_OID, LOCALE ),
    CONSTRAINT FK180 FOREIGN KEY (THEME_DESC_OID) REFERENCES THEME_DESC (OID) ON DELETE CASCADE
);

CREATE TABLE THEME_DESC_MAD (
    THEME_DESC_OID INTEGER NOT NULL,
    MARKUP VARCHAR(128) NOT NULL,

    CONSTRAINT PK190 PRIMARY KEY ( THEME_DESC_OID, MARKUP ),
    CONSTRAINT FK190 FOREIGN KEY (THEME_DESC_OID) REFERENCES THEME_DESC (OID) ON DELETE CASCADE
);


CREATE TABLE PAGE_INST (
    OID INTEGER NOT NULL,
    OWNERID VARCHAR(255) NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    IS_ACTIVE CHAR(1) DEFAULT 'Y' NOT NULL,
    IS_SYSTEM CHAR(1) DEFAULT 'N' NOT NULL,
    ALL_PORT_ALLOWED CHAR(1) DEFAULT 'N' NOT NULL,
    PARENT_OID INTEGER,
    SKIN_DESC_OID INTEGER,
    THEME_DESC_OID INTEGER,
    CREATE_TYPE CHAR(1) DEFAULT 'E' NOT NULL,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK280 PRIMARY KEY (OID),
    CONSTRAINT FK280B FOREIGN KEY ( PARENT_OID ) REFERENCES PAGE_INST ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK280C FOREIGN KEY ( SKIN_DESC_OID) REFERENCES SKIN_DESC ( OID ) ON DELETE SET NULL,
    CONSTRAINT FK280D FOREIGN KEY ( THEME_DESC_OID) REFERENCES THEME_DESC ( OID ) ON DELETE SET NULL
);

CREATE INDEX IX280A ON PAGE_INST ( OID, NAME );

CREATE TABLE PAGE_INST_LOD (
    PAGE_INST_OID INTEGER NOT NULL,
    LOCALE VARCHAR(64) NOT NULL,
    TITLE VARCHAR(255) NOT NULL,
    DESCRIPTION VARCHAR(1024),

    CONSTRAINT PK281 PRIMARY KEY ( PAGE_INST_OID , LOCALE ),
    CONSTRAINT FK281 FOREIGN KEY ( PAGE_INST_OID ) REFERENCES PAGE_INST ( OID ) ON DELETE CASCADE
);

CREATE TABLE PAGE_INST_MAD (
    PAGE_INST_OID INTEGER NOT NULL,
    MARKUP VARCHAR(128) NOT NULL,

    CONSTRAINT PK260 PRIMARY KEY ( PAGE_INST_OID , MARKUP ),
    CONSTRAINT FK260 FOREIGN KEY ( PAGE_INST_OID ) REFERENCES PAGE_INST ( OID ) ON DELETE CASCADE
);

CREATE TABLE PAGE_INST_DD (
    PAGE_INST_OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    VALUE VARCHAR(255) NOT NULL,

    CONSTRAINT PK282 PRIMARY KEY ( PAGE_INST_OID , NAME ),
    CONSTRAINT FK282 FOREIGN KEY ( PAGE_INST_OID ) REFERENCES PAGE_INST ( OID ) ON DELETE CASCADE
);

CREATE TABLE PAGE_INST_ALIAS (
    PAGE_INST_OID INTEGER NOT NULL,
    ALIAS VARCHAR(255) NOT NULL,

    CONSTRAINT PK283 PRIMARY KEY ( PAGE_INST_OID , ALIAS ),
    CONSTRAINT FK283 FOREIGN KEY ( PAGE_INST_OID ) REFERENCES PAGE_INST ( OID ) ON DELETE CASCADE,
    CONSTRAINT UN283 UNIQUE ( ALIAS )
);



CREATE TABLE APP_INST (
    OID INTEGER NOT NULL,
    APP_DESC_OID INTEGER NOT NULL,
    NAME VARCHAR(255),
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK290 PRIMARY KEY (OID),
    CONSTRAINT FK290 FOREIGN KEY ( APP_DESC_OID) REFERENCES APP_DESC ( OID ) ON DELETE CASCADE
);


CREATE TABLE PORT_INST (
    OID INTEGER NOT NULL,
    PORT_DESC_OID INTEGER NOT NULL,
    APP_INST_OID INTEGER NOT NULL,
    NAME VARCHAR(255),
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK300 PRIMARY KEY (OID),
    CONSTRAINT FK300A FOREIGN KEY ( PORT_DESC_OID) REFERENCES PORT_DESC ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK300B FOREIGN KEY ( APP_INST_OID) REFERENCES APP_INST ( OID ) ON DELETE CASCADE
);

CREATE TABLE PORT_INST_DD (
    PORT_INST_OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    IS_LARGE CHAR(1) DEFAULT 'N' NOT NULL,
    IS_STRING CHAR(1) DEFAULT 'Y' NOT NULL,
    VALUE VARCHAR(255) FOR BIT DATA,
    LARGE_VALUE LONG VARCHAR FOR BIT DATA,

    CONSTRAINT PK310 PRIMARY KEY ( PORT_INST_OID , NAME ),
    CONSTRAINT FK310 FOREIGN KEY ( PORT_INST_OID ) REFERENCES PORT_INST ( OID ) ON DELETE CASCADE
);

CREATE TABLE COMP_INST (
    OID INTEGER NOT NULL,
    NAME VARCHAR(255),
    COMP_DESC_OID INTEGER NOT NULL,
    PORT_INST_OID INTEGER,
    PAGE_INST_OID INTEGER NOT NULL,
    COMPOS_REF INTEGER,
    PARENT_OID INTEGER,
    ORIENTATION CHAR(1),
    EXPAND_STATE CHAR(1),
    URL VARCHAR(255),
    ALL_MARK_ALLOWED CHAR(1) DEFAULT 'Y' NOT NULL,
    ORDINAL INTEGER,
    MAX_SIZE INTEGER,
    SHADOW_OID INTEGER,
    IS_EDITABLE CHAR(1),
    IS_DELETABLE CHAR(1),
    IS_MODIFIABLE CHAR(1),
    IS_NESTABLE CHAR(1),
    IS_MOVABLE CHAR(1),
    IS_ACTIVE CHAR(1) DEFAULT 'Y' NOT NULL,
    WIDTH VARCHAR(32),
    ICON_URL VARCHAR(255),
    THEME_DESC_OID INTEGER,
    SKIN_DESC_OID INTEGER,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK320 PRIMARY KEY (OID),
    CONSTRAINT FK320A FOREIGN KEY ( COMP_DESC_OID) REFERENCES COMP_DESC ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK320B FOREIGN KEY ( PAGE_INST_OID) REFERENCES PAGE_INST ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK320C FOREIGN KEY ( PORT_INST_OID) REFERENCES PORT_INST ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK320D FOREIGN KEY ( PARENT_OID ) REFERENCES COMP_INST ( OID ) ON DELETE CASCADE,