refactions3.sql

derby database source code.good for you.

    CONSTRAINT FK320E FOREIGN KEY ( SHADOW_OID ) REFERENCES COMP_INST ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK320F FOREIGN KEY ( THEME_DESC_OID) REFERENCES THEME_DESC ( OID ) ON DELETE SET NULL,
    CONSTRAINT FK320G FOREIGN KEY ( SKIN_DESC_OID) REFERENCES SKIN_DESC ( OID ) ON DELETE SET NULL,
    CONSTRAINT FK320H FOREIGN KEY ( COMPOS_REF ) REFERENCES PAGE_INST ( OID ) ON DELETE CASCADE
);

CREATE INDEX IX320A ON COMP_INST ( PAGE_INST_OID );
CREATE INDEX IX320B ON COMP_INST ( PORT_INST_OID );

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

    CONSTRAINT PK331 PRIMARY KEY ( COMP_INST_OID , LOCALE ),
    CONSTRAINT FK331 FOREIGN KEY ( COMP_INST_OID ) REFERENCES COMP_INST ( OID ) ON DELETE CASCADE
);

CREATE TABLE COMP_INST_DD (
    COMP_INST_OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    VALUE VARCHAR(255) NOT NULL,

    CONSTRAINT PK330 PRIMARY KEY ( COMP_INST_OID , NAME ),
    CONSTRAINT FK330 FOREIGN KEY ( COMP_INST_OID ) REFERENCES COMP_INST ( OID ) ON DELETE CASCADE
);

CREATE TABLE COMP_INST_MAD (
    COMP_INST_OID INTEGER NOT NULL,
    MARKUP VARCHAR(128) NOT NULL,
    URL VARCHAR(255),

    CONSTRAINT PK333 PRIMARY KEY ( COMP_INST_OID , MARKUP ),
    CONSTRAINT FK333 FOREIGN KEY ( COMP_INST_OID ) REFERENCES COMP_INST ( OID ) ON DELETE CASCADE
);

CREATE TABLE COMP_INST_ALIAS (
    COMP_INST_OID INTEGER NOT NULL,
    ALIAS VARCHAR(255) NOT NULL,

    CONSTRAINT PK332 PRIMARY KEY ( COMP_INST_OID , ALIAS ),
    CONSTRAINT FK332 FOREIGN KEY ( COMP_INST_OID ) REFERENCES COMP_INST ( OID ) ON DELETE CASCADE,
    CONSTRAINT UN332 UNIQUE ( ALIAS )
);



CREATE TABLE USER_DESC (
    OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    TYPE INTEGER NOT NULL,
    LAST_LOGIN BIGINT,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK340 PRIMARY KEY ( OID ),
    CONSTRAINT UN340 UNIQUE ( NAME, TYPE )
);

CREATE INDEX IX340 ON USER_DESC ( OID, NAME );

CREATE TABLE USER_DESC_DD (
    USER_DESC_OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    VALUE VARCHAR(255) NOT NULL,

    CONSTRAINT PK350 PRIMARY KEY ( USER_DESC_OID, NAME ),
    CONSTRAINT FK350 FOREIGN KEY ( USER_DESC_OID ) REFERENCES USER_DESC ( OID ) ON DELETE CASCADE
);


CREATE TABLE USER_SESSION (
    OID INTEGER NOT NULL,
    USER_DESC_OID INTEGER NOT NULL,
    MARKUP VARCHAR(128) NOT NULL,
    USER_LOGGED_OUT CHAR(1) DEFAULT 'Y' NOT NULL,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK360 PRIMARY KEY ( OID ),
    CONSTRAINT UN360 UNIQUE ( USER_DESC_OID, MARKUP ),
    CONSTRAINT FK360 FOREIGN KEY ( USER_DESC_OID ) REFERENCES USER_DESC ( OID ) ON DELETE CASCADE
);

CREATE TABLE SELECTION_STATE (
    USER_SESSION_OID INTEGER NOT NULL,
    ROOT_OID INTEGER NOT NULL,
    SELECTION_OID INTEGER NOT NULL,

    CONSTRAINT PK370 PRIMARY KEY ( USER_SESSION_OID , ROOT_OID ),
    CONSTRAINT FK370A FOREIGN KEY ( USER_SESSION_OID ) REFERENCES USER_SESSION ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK370B FOREIGN KEY ( ROOT_OID ) REFERENCES COMP_INST ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK370C FOREIGN KEY ( SELECTION_OID ) REFERENCES COMP_INST ( OID ) ON DELETE CASCADE
);

CREATE TABLE PORT_INST_STATE (
    USER_SESSION_OID INTEGER NOT NULL,
    PORT_INST_OID INTEGER NOT NULL,
    MODES INTEGER NOT NULL,

    CONSTRAINT PK380 PRIMARY KEY ( USER_SESSION_OID , PORT_INST_OID ),
    CONSTRAINT FK380A FOREIGN KEY ( USER_SESSION_OID ) REFERENCES USER_SESSION ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK380B FOREIGN KEY ( PORT_INST_OID ) REFERENCES PORT_INST ( OID ) ON DELETE CASCADE
);

CREATE TABLE COMP_INST_STATE (
    USER_SESSION_OID INTEGER NOT NULL,
    COMP_INST_OID INTEGER NOT NULL,
    STATE INTEGER NOT NULL,

    CONSTRAINT PK390 PRIMARY KEY ( USER_SESSION_OID , COMP_INST_OID ),
    CONSTRAINT FK390A FOREIGN KEY ( USER_SESSION_OID ) REFERENCES USER_SESSION ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK390B FOREIGN KEY ( COMP_INST_OID ) REFERENCES COMP_INST ( OID ) ON DELETE CASCADE
);


CREATE TABLE CRED_SEGMENT (
    OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    DESCRIPTION VARCHAR(255),
    USER_MAPPED CHAR(1) NOT NULL,
    ADAPTER_TYPE VARCHAR(255),
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK400 PRIMARY KEY ( OID ),
    CONSTRAINT UN400 UNIQUE ( NAME )
);

CREATE INDEX IX400 ON CRED_SEGMENT ( OID, NAME );


CREATE TABLE CRED_SLOT (
    OID INTEGER NOT NULL,
    SLOT_KEY VARCHAR(255) NOT NULL,
    IS_ACTIVE CHAR(1) NOT NULL,
    IS_SYSTEM CHAR(1) NOT NULL,
    SECRET_TYPE INTEGER NOT NULL,
    RESOURCE_NAME VARCHAR(255),
    SEGMENT_OID INTEGER NOT NULL,
    USER_DESC_OID INTEGER,
    PORT_INST_OID INTEGER,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK410 PRIMARY KEY ( OID ),
    CONSTRAINT UN410 UNIQUE ( SLOT_KEY ),
    CONSTRAINT FK410A FOREIGN KEY ( SEGMENT_OID ) REFERENCES CRED_SEGMENT ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK410B FOREIGN KEY ( USER_DESC_OID ) REFERENCES USER_DESC ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK410C FOREIGN KEY ( PORT_INST_OID ) REFERENCES PORT_INST ( OID ) ON DELETE CASCADE
);

CREATE INDEX IX410A ON CRED_SLOT ( USER_DESC_OID, PORT_INST_OID );
CREATE INDEX IX410B ON CRED_SLOT ( RESOURCE_NAME );
CREATE INDEX IX410C ON CRED_SLOT ( SEGMENT_OID );

CREATE TABLE CRED_SLOT_LOD (
    CRED_SLOT_OID INTEGER NOT NULL,
    LOCALE VARCHAR(64) NOT NULL,
    DESCRIPTION VARCHAR(255),
    KEYWORDS VARCHAR(1024),

    CONSTRAINT PK420 PRIMARY KEY ( CRED_SLOT_OID , LOCALE ),
    CONSTRAINT FK420 FOREIGN KEY ( CRED_SLOT_OID ) REFERENCES CRED_SLOT ( OID ) ON DELETE CASCADE
);


CREATE TABLE LNK_PAGE_PORT (
    PAGE_INST_OID INTEGER NOT NULL,
    PORT_DESC_OID INTEGER NOT NULL,

    CONSTRAINT PK430 PRIMARY KEY ( PAGE_INST_OID, PORT_DESC_OID ),
    CONSTRAINT FK430A FOREIGN KEY ( PAGE_INST_OID ) REFERENCES PAGE_INST ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK430B FOREIGN KEY ( PORT_DESC_OID ) REFERENCES PORT_DESC ( OID ) ON DELETE CASCADE
);

CREATE TABLE LNK_THEME_SKIN (
    THEME_DESC_OID INTEGER NOT NULL,
    SKIN_DESC_OID INTEGER NOT NULL,

    CONSTRAINT PK450 PRIMARY KEY ( THEME_DESC_OID, SKIN_DESC_OID ),
    CONSTRAINT FK450A FOREIGN KEY ( THEME_DESC_OID ) REFERENCES THEME_DESC ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK450B FOREIGN KEY ( SKIN_DESC_OID ) REFERENCES SKIN_DESC ( OID ) ON DELETE CASCADE
);


CREATE TABLE MARKUP_DESC (
    OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    IS_ACTIVE CHAR(1) DEFAULT 'Y' NOT NULL,
    MIMETYPE VARCHAR(255) NOT NULL,
    DEFAULT_CHARSET VARCHAR(64) DEFAULT 'UTF-8' NOT NULL,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK470 PRIMARY KEY ( OID ),
    CONSTRAINT UN470 UNIQUE ( NAME )
);

CREATE TABLE MARKUP_DESC_LOD (
    MARKUP_DESC_OID INTEGER NOT NULL,
    LOCALE VARCHAR(64) NOT NULL,
    TITLE VARCHAR(255),
    CHARSET VARCHAR(64),

    CONSTRAINT PK480 PRIMARY KEY ( MARKUP_DESC_OID , LOCALE ),
    CONSTRAINT FK480 FOREIGN KEY ( MARKUP_DESC_OID ) REFERENCES MARKUP_DESC ( OID ) ON DELETE CASCADE
);


CREATE TABLE CLIENT_DESC (
    OID INTEGER NOT NULL,
    ORDINAL INTEGER NOT NULL,
    MANUFACTURER VARCHAR(64),
    MODEL VARCHAR(255),
    VERSION VARCHAR(16),
    USERAGENT_PATTERN VARCHAR(255) NOT NULL,
    MARKUP_NAME VARCHAR(255) NOT NULL,
    MARKUP_VERSION VARCHAR(16),
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK490 PRIMARY KEY (OID)
);

CREATE INDEX IX490 ON CLIENT_DESC ( ORDINAL );

CREATE TABLE CLIENT_DESC_CAPS (
    CLIENT_DESC_OID INTEGER NOT NULL,
    CAPABILITY VARCHAR(255) NOT NULL,

    CONSTRAINT PK500 PRIMARY KEY ( CLIENT_DESC_OID , CAPABILITY ),
    CONSTRAINT FK500 FOREIGN KEY ( CLIENT_DESC_OID ) REFERENCES CLIENT_DESC ( OID ) ON DELETE CASCADE
);


CREATE TABLE UDDI_REG_DESC (
    OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    PUBLISH_URL VARCHAR(255),
    INQUIRY_URL VARCHAR(255) NOT NULL,
    PORTLET_TMODEL VARCHAR(255),
    URL_TMODEL VARCHAR(255),
    CRED_SLOT_ID VARCHAR(255),
    IS_DELETED CHAR(1) DEFAULT 'N' NOT NULL,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK510 PRIMARY KEY ( OID )
);

CREATE INDEX IX510 ON UDDI_REG_DESC ( IS_DELETED );

CREATE TABLE UDDI_REG_DESC_PD (
    UDDI_REG_DESC_OID INTEGER NOT NULL,
    REMOTE_ID VARCHAR(255) NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    PORT_DESC_OID INTEGER,

    CONSTRAINT PK520 PRIMARY KEY ( UDDI_REG_DESC_OID , REMOTE_ID ),
    CONSTRAINT FK520A FOREIGN KEY ( UDDI_REG_DESC_OID ) REFERENCES UDDI_REG_DESC ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK520B FOREIGN KEY ( PORT_DESC_OID ) REFERENCES PORT_DESC ( OID ) ON DELETE SET NULL,
    CONSTRAINT UN520 UNIQUE ( REMOTE_ID )
);

CREATE INDEX IX520 ON UDDI_REG_DESC_PD ( REMOTE_ID, PORT_DESC_OID );


CREATE TABLE BIND_CTX (
    OID INTEGER NOT NULL,
    HANDLE_ID VARCHAR(255) NOT NULL,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK530 PRIMARY KEY ( OID )
);

CREATE INDEX IX530 ON BIND_CTX ( HANDLE_ID );

CREATE TABLE BIND_CTX_DD (
    BIND_CTX_OID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    VALUE VARCHAR(255) NOT NULL,

    CONSTRAINT PK540 PRIMARY KEY ( BIND_CTX_OID , NAME ),
    CONSTRAINT FK540 FOREIGN KEY ( BIND_CTX_OID ) REFERENCES BIND_CTX ( OID ) ON DELETE CASCADE
);

CREATE TABLE BIND_CTX_PI (
    BIND_CTX_OID INTEGER NOT NULL,
    PORT_INST_OID INTEGER NOT NULL,
    LAST_USED BIGINT NOT NULL,

    CONSTRAINT PK550 PRIMARY KEY ( BIND_CTX_OID , PORT_INST_OID ),
    CONSTRAINT FK550A FOREIGN KEY ( BIND_CTX_OID ) REFERENCES BIND_CTX ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK550B FOREIGN KEY ( PORT_INST_OID ) REFERENCES PORT_INST ( OID ) ON DELETE CASCADE
);

CREATE TABLE CLI_BIND (
    OID INTEGER NOT NULL,
    ACCESS_POINT_URL VARCHAR(255) NOT NULL,
    HANDLE_ID VARCHAR(255) NOT NULL,
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK560 PRIMARY KEY ( OID ),
    CONSTRAINT UN560 UNIQUE ( ACCESS_POINT_URL )
);

CREATE TABLE CLI_BIND_HANDLES (
    CLI_BIND_OID INTEGER NOT NULL,
    HANDLE_ID VARCHAR(255) NOT NULL,
    PORT_INST_OID INTEGER,

    CONSTRAINT PK570 PRIMARY KEY ( CLI_BIND_OID , HANDLE_ID ),
    CONSTRAINT FK570A FOREIGN KEY ( CLI_BIND_OID ) REFERENCES CLI_BIND ( OID ) ON DELETE CASCADE,
    CONSTRAINT FK570B FOREIGN KEY ( PORT_INST_OID ) REFERENCES PORT_INST ( OID ) ON DELETE SET NULL
);


CREATE TABLE MAG_DATA (
    OID INTEGER NOT NULL,
    USERID VARCHAR(255) NOT NULL,
    SUBNO VARCHAR(255),
    CLID VARCHAR(255),
    FORCE_PROMPT CHAR(1) DEFAULT 'N' NOT NULL,

    CONSTRAINT PK580 PRIMARY KEY ( USERID )
);

CREATE INDEX IX580A ON MAG_DATA ( SUBNO );
CREATE INDEX IX580B ON MAG_DATA ( CLID );


CREATE TABLE PUB_CLIP_DEF (
    OID INTEGER NOT NULL,
    UDDI_REG_DESC_OID INTEGER NOT NULL,
    REMOTE_ID VARCHAR(255) NOT NULL,
    CLIP_DEF VARCHAR(1024) NOT NULL,
    SERVICE_KEY VARCHAR(255),
    CREATED BIGINT NOT NULL,
    MODIFIED BIGINT NOT NULL,

    CONSTRAINT PK650 PRIMARY KEY (OID),
    CONSTRAINT FK650 FOREIGN KEY ( UDDI_REG_DESC_OID) REFERENCES UDDI_REG_DESC ( OID ) ON DELETE CASCADE,
    CONSTRAINT UN650 UNIQUE ( REMOTE_ID )
);


CREATE TABLE ACL (
    SUBJECTTYPE INTEGER NOT NULL,