| ij> -- |
| -- Licensed to the Apache Software Foundation (ASF) under one or more |
| -- contributor license agreements. See the NOTICE file distributed with |
| -- this work for additional information regarding copyright ownership. |
| -- The ASF licenses this file to You under the Apache License, Version 2.0 |
| -- (the "License"); you may not use this file except in compliance with |
| -- the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| -- |
| -- 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) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX20A ON APP_DESC ( OID, NAME ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX20B ON APP_DESC ( GUID ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX20C ON APP_DESC ( PARENT_OID ); |
| 0 rows inserted/updated/deleted |
| WARNING 01504: 'IX20C' index not created because it is a duplicate of an existing index: 'xxxxGENERATED-IDxxxx'. |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX40A ON PORT_DESC ( OID, NAME ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX40D ON PORT_DESC ( REFERENCE_ID, APP_DESC_OID ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX40E ON PORT_DESC ( APP_DESC_OID ); |
| 0 rows inserted/updated/deleted |
| WARNING 01504: 'IX40E' index not created because it is a duplicate of an existing index: 'xxxxGENERATED-IDxxxx'. |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX140 ON SKIN_DESC ( OID, NAME ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX170 ON THEME_DESC ( OID, NAME ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX280A ON PAGE_INST ( OID, NAME ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 ) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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, |
| 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX320A ON COMP_INST ( PAGE_INST_OID ); |
| 0 rows inserted/updated/deleted |
| WARNING 01504: 'IX320A' index not created because it is a duplicate of an existing index: 'xxxxGENERATED-IDxxxx'. |
| ij> CREATE INDEX IX320B ON COMP_INST ( PORT_INST_OID ); |
| 0 rows inserted/updated/deleted |
| WARNING 01504: 'IX320B' index not created because it is a duplicate of an existing index: 'xxxxGENERATED-IDxxxx'. |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 ) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 ) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX340 ON USER_DESC ( OID, NAME ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 ) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX400 ON CRED_SEGMENT ( OID, NAME ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX410A ON CRED_SLOT ( USER_DESC_OID, PORT_INST_OID ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX410B ON CRED_SLOT ( RESOURCE_NAME ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX410C ON CRED_SLOT ( SEGMENT_OID ); |
| 0 rows inserted/updated/deleted |
| WARNING 01504: 'IX410C' index not created because it is a duplicate of an existing index: 'xxxxGENERATED-IDxxxx'. |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 ) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX490 ON CLIENT_DESC ( ORDINAL ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 ) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX510 ON UDDI_REG_DESC ( IS_DELETED ); |
| 0 rows inserted/updated/deleted |
| ij> 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 ) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX520 ON UDDI_REG_DESC_PD ( REMOTE_ID, PORT_DESC_OID ); |
| 0 rows inserted/updated/deleted |
| ij> 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 ) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX530 ON BIND_CTX ( HANDLE_ID ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 ) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 ) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX580A ON MAG_DATA ( SUBNO ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX580B ON MAG_DATA ( CLID ); |
| 0 rows inserted/updated/deleted |
| ij> 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 ) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE ACL ( |
| SUBJECTTYPE INTEGER NOT NULL, |
| SUBJECTID INTEGER NOT NULL, |
| ACTIONS INTEGER NOT NULL, |
| OBJECTTYPE INTEGER NOT NULL, |
| OBJECTID INTEGER NOT NULL |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX2000A ON ACL ( OBJECTTYPE, SUBJECTTYPE, SUBJECTID, OBJECTID, ACTIONS ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX IX2000B ON ACL ( SUBJECTTYPE, OBJECTTYPE, OBJECTID ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE VAULT_RESOURCES ( |
| RESOURCE_NAME VARCHAR(255) NOT NULL, |
| CONSTRAINT PK2010 PRIMARY KEY (RESOURCE_NAME) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE SCHEMA WPSPCO; |
| 0 rows inserted/updated/deleted |
| ij> 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) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX WPSPCO.IX1030A ON WPSPCO.RES_COLLECTION ( OID, NAME ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE INDEX WPSPCO.IX1030B ON WPSPCO.RES_COLLECTION ( NAME ); |
| 0 rows inserted/updated/deleted |
| ij> 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) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE WPSPCO.RES_UPDATES ( |
| RESCOLLNAME VARCHAR(100), |
| RESOURCEID VARCHAR(155) NOT NULL, |
| STATE INTEGER NOT NULL, |
| CONTENTFORMAT VARCHAR(100) NOT NULL |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE WPSPCO.PATH ( |
| PATH VARCHAR(105) NOT NULL, |
| RESCOLLNAME VARCHAR(100) NOT NULL |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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 |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> 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) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> --END OF WEBSPERE PORTTAL CASES |
| ---SOME TEST CASES GOT FROM DB2 TESTS. |
| --some test cases got from db2 tests. |
| CREATE SCHEMA refint; |
| 0 rows inserted/updated/deleted |
| ij> set schema refint ; |
| 0 rows inserted/updated/deleted |
| ij> 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); |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E010_V1 AS SELECT * FROM refint.E010_T1; |
| 0 rows inserted/updated/deleted |
| ij> 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); |
| 0 rows inserted/updated/deleted |
| ij> 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); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E110_T1 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0, C1)); |
| 0 rows inserted/updated/deleted |
| ij> 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); |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E110_V1 AS SELECT * FROM refint.E110_T1; |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E110_V2 AS SELECT * FROM refint.E110_T2; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E210_T1 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> 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); |
| 0 rows inserted/updated/deleted |
| ij> 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); |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E210_V1 AS SELECT * FROM refint.E210_T1; |
| 0 rows inserted/updated/deleted |
| ij> 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; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E120_T1 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| C2 CHAR(3), |
| PRIMARY KEY (C0, C1)); |
| 0 rows inserted/updated/deleted |
| ij> 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); |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E120_V1 AS SELECT * FROM refint.E120_T1; |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E120_V2 AS SELECT * FROM refint.E120_V1; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.RJCE120_T1 (C0 INTEGER NOT NULL, |
| C1 INTEGER NOT NULL, |
| C2 INTEGER, |
| PRIMARY KEY (C0, C1)); |
| 0 rows inserted/updated/deleted |
| ij> 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); |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.RJCE120_V1 AS SELECT * FROM refint.RJCE120_T1; |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.RJCE120_V2 AS SELECT * FROM refint.RJCE120_V1; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E130_T1 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0, C1)); |
| 0 rows inserted/updated/deleted |
| ij> 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); |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E130_V1 AS SELECT * FROM refint.E130_T1; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E140_T1 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> 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); |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E140_V1 AS SELECT * FROM refint.E140_T1; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E220_T1 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0, C1)); |
| 0 rows inserted/updated/deleted |
| ij> 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); |
| 0 rows inserted/updated/deleted |
| ij> 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); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E230_T1 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> 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) |
| REFERENCES refint.E230_T1 ON DELETE SET NULL); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E230_T3 (C0 CHAR(3), |
| C1 CHAR(3), |
| C0b CHAR(3), |
| C2 CHAR(3), |
| CONSTRAINT FK33 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E230_T1 ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E230_V1 AS SELECT * FROM refint.E230_T1; |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E230_V2 (C1, C2) AS |
| SELECT refint.E230_T2.C1, refint.E230_T3.C2 |
| FROM refint.E230_T2, refint.E230_T3; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E240_T1 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3), |
| C2 CHAR(3) NOT NULL, |
| C3 SMALLINT NOT NULL, |
| C4 INTEGER, |
| C5 DECIMAL(9,3) NOT NULL, |
| C6 VARCHAR(20) NOT NULL, |
| C7 DATE NOT NULL, |
| PRIMARY KEY (C0, C2, C6, C7)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E240_T2 (C0 CHAR(3) NOT NULL, |
| C0a CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| C2 CHAR(3) , |
| C3 SMALLINT NOT NULL, |
| C4 DECIMAL(9,3) NOT NULL, |
| C5 VARCHAR(20), |
| C6 TIME, |
| C7 DATE NOT NULL, |
| PRIMARY KEY (C0,C1,C3), |
| CONSTRAINT E240_T1_T2_A FOREIGN KEY (C0a,C1,C5,C7) |
| REFERENCES refint.E240_T1 ON DELETE SET NULL); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E240_T3 (C0 CHAR(3), |
| C0a CHAR(3), |
| C1 CHAR(3), |
| C2 CHAR(3) , |
| C3 SMALLINT NOT NULL, |
| C4 DECIMAL(9,3) NOT NULL, |
| C5 VARCHAR(20), |
| C6 TIME, |
| C7 DATE NOT NULL, |
| CONSTRAINT E240_T1_T2_B |
| FOREIGN KEY (C0a, C1, C5, C7) |
| REFERENCES refint.E240_T1 ON DELETE SET NULL); |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E240_V1 AS SELECT * FROM refint.E240_T1; |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E240_V2 (C1,C2,C3,C4,C5,C6,C7) AS |
| SELECT refint.E240_T2.C1, refint.E240_T2.C2, refint.E240_T3.C3, |
| refint.E240_T3.C4, refint.E240_T3.C5, refint.E240_T2.C6, |
| refint.E240_T2.C7 |
| FROM refint.E240_T2,refint.E240_T3; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E250_T1 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E250_T2 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| C0b CHAR(3), |
| C2 CHAR(3), |
| PRIMARY KEY (C0,C1), |
| CONSTRAINT E250_T1_T2 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E250_T1 ON DELETE RESTRICT); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E250_T3 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| C2 CHAR(3), |
| C0b CHAR(3), |
| PRIMARY KEY (C0,C1), |
| CONSTRAINT E250_T1_T3 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E250_T1 ON DELETE RESTRICT); |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E250_V1 AS SELECT * FROM refint.E250_T1; |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E250_V2 (C1 ,C2) AS |
| SELECT refint.E250_T2.C1, refint.E250_T3.C2 |
| FROM refint.E250_T2, refint.E250_T3 |
| WHERE refint.E250_T2.C1 = refint.E250_T3.C1; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E260_T1 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E260_T2 (C0 CHAR(3), |
| C1 CHAR(3), |
| C0b CHAR(3), |
| C2 CHAR(3), |
| CONSTRAINT E260_T1_T2 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E260_T1); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E260_T3 (C0 CHAR(3), |
| C1 CHAR(3), |
| C0b CHAR(3), |
| C2 CHAR(3), |
| CONSTRAINT E260_T1_T3 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E260_T1); |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E260_V1 AS SELECT * FROM refint.E260_T1; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E310_T1 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E310_T2 (C0 INTEGER NOT NULL, |
| C1 INTEGER NOT NULL, |
| C2 CHAR(3) , |
| C0b CHAR(3) , |
| PRIMARY KEY (C0,C1), |
| CONSTRAINT FK15 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E310_T1 ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E310_T3 (C0 CHAR(3), |
| C1 CHAR(3), |
| C0b INTEGER, |
| C2 INTEGER, |
| CONSTRAINT FK34 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E310_T2 ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E310_V1 AS SELECT * FROM refint.E310_T1; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E360_T1 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E360_T2 (C0 INT NOT NULL, |
| C1 DECIMAL(9,3) NOT NULL, |
| C0b CHAR(3) , |
| C2 CHAR(3) , |
| PRIMARY KEY (C0,C1), |
| CONSTRAINT E360_T1_T2 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E360_T1 ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E360_T3 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| C0b INT, |
| C2 DECIMAL(9,3), |
| CONSTRAINT E360_T2_T3 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E360_T2 ON DELETE RESTRICT); |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E360_V1 AS SELECT * FROM refint.E360_T1; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E370_T1 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E370_T2 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C2 CHAR(3), |
| C0b CHAR(3), |
| PRIMARY KEY (C0,C1), |
| CONSTRAINT FK16 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E370_T1 ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E370_T3 (C1 CHAR(3), |
| C0 CHAR(3), |
| C2 CHAR(3), |
| C0b CHAR(3), |
| CONSTRAINT FK35 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E370_T2 ON DELETE SET NULL); |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E370_V1 AS SELECT * FROM refint.E370_T1; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E340_T1 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E340_T2 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C2 CHAR(3), |
| C0b CHAR(3), |
| PRIMARY KEY (C0,C1), |
| CONSTRAINT FK17 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E340_T1 ON DELETE SET NULL); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E340_T3 (C1 CHAR(3), |
| C0 CHAR(3), |
| C2 CHAR(3), |
| C0b CHAR(3), |
| CONSTRAINT FK36 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E340_T2 ON DELETE RESTRICT); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E410_T1 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E410_T2 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| C2 CHAR(3) NOT NULL, |
| C0b CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,C1,C2), |
| CONSTRAINT FK18 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E410_T1 ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E410_T3 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C0a CHAR(3), |
| C2 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,C1,C2), |
| CONSTRAINT FK37 FOREIGN KEY (C0a,C1) |
| REFERENCES refint.E410_T1 ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E410_T4 (C0a CHAR(3), |
| C1 CHAR(3), |
| C0 CHAR(3), |
| C0b CHAR(3), |
| C2 CHAR(3), |
| C3 CHAR(3), |
| CONSTRAINT FK7 FOREIGN KEY (C0b,C2,C3) |
| REFERENCES refint.E410_T2 ON DELETE CASCADE, |
| CONSTRAINT FK8 FOREIGN KEY (C0a,C1,C2) |
| REFERENCES refint.E410_T3 ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E450_T1 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E450_T2 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C2 CHAR(3) NOT NULL, |
| C0c CHAR(3), |
| C3 CHAR(3), |
| PRIMARY KEY (C0,C1,C2), |
| CONSTRAINT FK20 FOREIGN KEY (C0c,C3) |
| REFERENCES refint.E450_T1 ON DELETE SET NULL); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E450_T3 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| C2 CHAR(3) NOT NULL, |
| C0c CHAR(3), |
| C3 CHAR(3), |
| PRIMARY KEY (C0,C1,C2), |
| CONSTRAINT FK38 FOREIGN KEY (C0c,C3) |
| REFERENCES refint.E450_T1 ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E450_T4 (C1 CHAR(3), |
| C0 CHAR(3), |
| C0a CHAR(3), |
| C0b CHAR(3), |
| C2 CHAR(3), |
| C3 CHAR(3), |
| CONSTRAINT FK3 FOREIGN KEY (C0b,C2,C3) |
| REFERENCES refint.E450_T2 ON DELETE SET NULL, |
| CONSTRAINT FK4 FOREIGN KEY (C0a,C1,C2) |
| REFERENCES refint.E450_T3 ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E510_T1 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C0a CHAR(3), |
| C2 CHAR(3), |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E510_T2 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C0b CHAR(3), |
| C2 CHAR(3), |
| PRIMARY KEY (C0,C1), |
| CONSTRAINT FK21 FOREIGN KEY (c0b,C2) |
| REFERENCES refint.E510_T1 ON DELETE SET NULL); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E510_T3 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| C2 CHAR(3), |
| C0b CHAR(3), |
| PRIMARY KEY(C0,C1), |
| CONSTRAINT FK39 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E510_T2 ON DELETE SET NULL); |
| 0 rows inserted/updated/deleted |
| ij> alter table refint.E510_T1 add CONSTRAINT CYC FOREIGN KEY (C0a,C1) |
| REFERENCES refint.E510_T3 ON DELETE CASCADE; |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E510_V1 AS SELECT * FROM refint.E510_T3; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E540_T1 (C0 CHAR(3) NOT NULL, |
| C0a CHAR(3), |
| C1 CHAR(3) NOT NULL, |
| C2 CHAR(3), |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E540_T2 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C2 CHAR(3), |
| C0b CHAR(3), |
| PRIMARY KEY (C0,C1), |
| CONSTRAINT E540_T1_T2 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E540_T1 ON DELETE RESTRICT); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E540_T3 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C2 CHAR(3), |
| C0b CHAR(3), |
| PRIMARY KEY (C0,C1), |
| CONSTRAINT E540_T2_T3 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E540_T2 ON DELETE RESTRICT); |
| 0 rows inserted/updated/deleted |
| ij> alter table refint.E540_T1 add CONSTRAINT E540_T3_T1 FOREIGN KEY (C0a,C1) |
| REFERENCES refint.E540_T3 ON DELETE RESTRICT; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E560_T1 (C1 CHAR(3) NOT NULL, |
| C0a CHAR(3), |
| C0 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E560_T2 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C2 CHAR(3), |
| C0b CHAR(3), |
| PRIMARY KEY (C0,C1), |
| CONSTRAINT E560_T2_T1 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E560_T1 ON DELETE RESTRICT); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E560_T3 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C2 CHAR(3), |
| C0b CHAR(3), |
| PRIMARY KEY (C0,C1), |
| CONSTRAINT E560_T3_T2 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E560_T2 ON DELETE RESTRICT); |
| 0 rows inserted/updated/deleted |
| ij> alter table refint.E560_T1 add CONSTRAINT E560_T1_T3 FOREIGN KEY (C0a,C1) |
| REFERENCES refint.E560_T3 ON DELETE CASCADE; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E550_T1 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| C0b CHAR(3), |
| C2 CHAR(3), |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E550_T2 (C0 CHAR(3) NOT NULL, |
| C1 CHAR(3) NOT NULL, |
| C0b CHAR(3), |
| C2 CHAR(3), |
| PRIMARY KEY (C0,C1), |
| FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E550_T1 ON DELETE SET NULL); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E550_T3 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C2 CHAR(3), |
| C0b CHAR(3), |
| PRIMARY KEY (C0,C1), |
| FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E550_T2 ON DELETE SET NULL); |
| 0 rows inserted/updated/deleted |
| ij> alter table refint.E550_T1 add CONSTRAINT F550 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E550_T3 ON DELETE SET NULL; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E570_T1 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C0a CHAR(3), |
| C2 CHAR(3), |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E570_T2 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C2 CHAR(3), |
| C0b CHAR(3), |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E570_T3 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C2 CHAR(3), |
| C0b CHAR(3), |
| PRIMARY KEY (C0,C1)); |
| 0 rows inserted/updated/deleted |
| ij> alter table refint.E570_T1 add CONSTRAINT E570_T1_T3 FOREIGN KEY (C0a,C1) |
| REFERENCES refint.E570_T3 ON DELETE CASCADE; |
| 0 rows inserted/updated/deleted |
| ij> alter table refint.E570_T2 add CONSTRAINT E570_T2_T1 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E570_T1 ON DELETE RESTRICT; |
| 0 rows inserted/updated/deleted |
| ij> alter table refint.E570_T3 add CONSTRAINT E570_T3_T2 FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E570_T2 ON DELETE SET NULL; |
| 0 rows inserted/updated/deleted |
| ij> CREATE VIEW refint.E570_V1 AS SELECT * FROM refint.E570_T3; |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E710_T1 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C2 CHAR(3), |
| C0b CHAR(3), |
| C3 CHAR(3), |
| C0c CHAR(3), |
| PRIMARY KEY (C0,C1), |
| FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E710_T1 ON DELETE CASCADE, |
| FOREIGN KEY (C0c,C3) |
| REFERENCES refint.E710_T1 ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E720_T1 (C1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| C2 CHAR(3), |
| C0b CHAR(3), |
| PRIMARY KEY (C0,C1), |
| FOREIGN KEY (C0b,C2) |
| REFERENCES refint.E720_T1 ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E720_T2 (X1 CHAR(3) NOT NULL, |
| C0 CHAR(3) NOT NULL, |
| X3 CHAR(3), |
| C0d CHAR(3), |
| C3 CHAR(3), |
| C0c CHAR(3), |
| PRIMARY KEY (C0,X1), |
| FOREIGN KEY (C0d,X3) |
| REFERENCES refint.E720_T2 ON DELETE CASCADE, |
| FOREIGN KEY (C0c,C3) |
| REFERENCES refint.E720_T1 ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E610_T1 (C0 CHAR(3) NOT NULL, |
| P1 CHAR(3) NOT NULL, |
| P2 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,P1,P2)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E610_T2 (P1 CHAR(3), |
| C0 CHAR(3) NOT NULL, |
| P4 CHAR(3) NOT NULL, |
| P5 CHAR(3) NOT NULL, |
| PRIMARY KEY (C0,P4,P5)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE refint.E610_T3 (F1 CHAR(3), |
| C0 CHAR(3), |
| C0e CHAR(3), |
| F2 CHAR(3), |
| C0g CHAR(3), |
| F3 CHAR(3), |
| CONSTRAINT E610_T1_T3 FOREIGN KEY (C0e,F1,F2) |
| REFERENCES refint.E610_T1, |
| CONSTRAINT E610_T2_T3 FOREIGN KEY (C0g,F2,F3) |
| REFERENCES refint.E610_T2) |
| ; |
| 0 rows inserted/updated/deleted |
| ij> ---END OF TEST CASES GOT FROM DB2 Tests. |
| --START RANDOM COMPLEX LINKS |
| create table t1( a int not null primary key, b int); |
| 0 rows inserted/updated/deleted |
| ij> create table t2(x int, y int not null unique, z int); |
| 0 rows inserted/updated/deleted |
| ij> create table t3(l int, m int not null unique , k int ); |
| 0 rows inserted/updated/deleted |
| ij> create table t4(c1 int not null unique , c2 int); |
| 0 rows inserted/updated/deleted |
| ij> create table t5(c1 int not null unique , c2 int); |
| 0 rows inserted/updated/deleted |
| ij> create table t6(c1 int not null unique , c2 int); |
| 0 rows inserted/updated/deleted |
| ij> --cycle |
| alter table t2 add constraint c3 foreign key (z) |
| references t4(c1) on delete cascade; |
| 0 rows inserted/updated/deleted |
| ij> alter table t4 add constraint c4 foreign key (c2) |
| references t5(c1) on delete cascade; |
| 0 rows inserted/updated/deleted |
| ij> alter table t5 add constraint c5 foreign key (c2) |
| references t6(c1) on delete cascade; |
| 0 rows inserted/updated/deleted |
| ij> alter table t1 add constraint c1 foreign key (b) |
| references t3(m) on delete cascade; |
| 0 rows inserted/updated/deleted |
| ij> alter table t2 add constraint c2 foreign key (x) |
| references t1(a) on delete cascade; |
| 0 rows inserted/updated/deleted |
| ij> alter table t3 add constraint c6 foreign key (k) |
| references t2(y) on delete cascade; |
| 0 rows inserted/updated/deleted |
| ij> --link a self referencing table to above cycle with a SET NULL |
| create table t7( a int not null primary key, b int not null unique, |
| x int references t7(a) ON DELETE CASCADE, |
| z int references t7(b) ON DELETE CASCADE, |
| w int references t6(c1) ON DELETE SET NULL); |
| 0 rows inserted/updated/deleted |
| ij> --valide multiple paths |
| create table t8( a int not null primary key, b int); |
| 0 rows inserted/updated/deleted |
| ij> create table t9(x int, y int not null unique, z int); |
| 0 rows inserted/updated/deleted |
| ij> create table t10(l int, m int not null unique , k int ); |
| 0 rows inserted/updated/deleted |
| ij> create table t11(c1 int not null unique , c2 int); |
| 0 rows inserted/updated/deleted |
| ij> alter table t9 add constraint c7 foreign key (x) |
| references t8(a) on delete set null; |
| 0 rows inserted/updated/deleted |
| ij> alter table t9 add constraint c8 foreign key (z) |
| references t11(c1) on delete set null; |
| 0 rows inserted/updated/deleted |
| ij> alter table t10 add constraint c9 foreign key (l) |
| references t8(a) on delete set null; |
| 0 rows inserted/updated/deleted |
| ij> alter table t11 add constraint c10 foreign key (c1) |
| references t10(m) on delete cascade; |
| 0 rows inserted/updated/deleted |
| ij> --link this one first cycle case |
| alter table t9 add constraint c11 foreign key (z) |
| references t5(c1) on delete SET NULL; |
| 0 rows inserted/updated/deleted |
| ij> --valide multiple paths |
| create table t12( a int not null primary key, b int); |
| 0 rows inserted/updated/deleted |
| ij> create table t13(x int, y int not null unique, z int); |
| 0 rows inserted/updated/deleted |
| ij> create table t14(l int, m int not null unique , k int ); |
| 0 rows inserted/updated/deleted |
| ij> create table t15(c1 int not null unique , c2 int); |
| 0 rows inserted/updated/deleted |
| ij> alter table t13 add constraint c12 foreign key (x) |
| references t12(a) on delete SET NULL; |
| 0 rows inserted/updated/deleted |
| ij> alter table t13 add constraint c13 foreign key (z) |
| references t15(c1) on delete SET NULL; |
| 0 rows inserted/updated/deleted |
| ij> alter table t14 add constraint c14 foreign key (l) |
| references t12(a) on delete CASCADE; |
| 0 rows inserted/updated/deleted |
| ij> alter table t15 add constraint c15 foreign key (c2) |
| references t14(m) on delete SET NULL; |
| 0 rows inserted/updated/deleted |
| ij> --link this one to first cycle case |
| alter table t12 add constraint c16 foreign key (b) |
| references t2(y) on delete CASCADE; |
| 0 rows inserted/updated/deleted |
| ij> alter table t2 drop constraint c2; |
| 0 rows inserted/updated/deleted |
| ij> alter table t3 drop constraint c6; |
| 0 rows inserted/updated/deleted |
| ij> alter table t12 drop constraint c16; |
| 0 rows inserted/updated/deleted |
| ij> alter table t9 drop constraint c11; |
| 0 rows inserted/updated/deleted |
| ij> alter table t9 drop constraint c7; |
| 0 rows inserted/updated/deleted |
| ij> alter table t10 drop constraint c9; |
| 0 rows inserted/updated/deleted |
| ij> alter table t11 drop constraint c10; |
| 0 rows inserted/updated/deleted |
| ij> alter table t13 drop constraint c12; |
| 0 rows inserted/updated/deleted |
| ij> alter table t14 drop constraint c14; |
| 0 rows inserted/updated/deleted |
| ij> alter table t15 drop constraint c15; |
| 0 rows inserted/updated/deleted |
| ij> drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> drop table t2; |
| 0 rows inserted/updated/deleted |
| ij> drop table t3; |
| 0 rows inserted/updated/deleted |
| ij> drop table t4; |
| 0 rows inserted/updated/deleted |
| ij> drop table t5; |
| 0 rows inserted/updated/deleted |
| ij> drop table t7; |
| 0 rows inserted/updated/deleted |
| ij> drop table t6; |
| 0 rows inserted/updated/deleted |
| ij> drop table t8; |
| 0 rows inserted/updated/deleted |
| ij> drop table t9; |
| 0 rows inserted/updated/deleted |
| ij> drop table t10; |
| 0 rows inserted/updated/deleted |
| ij> drop table t11; |
| 0 rows inserted/updated/deleted |
| ij> drop table t12; |
| 0 rows inserted/updated/deleted |
| ij> drop table t13; |
| 0 rows inserted/updated/deleted |
| ij> drop table t14; |
| 0 rows inserted/updated/deleted |
| ij> drop table t15; |
| 0 rows inserted/updated/deleted |
| ij> --END OF RANDOM COMPLEX CASE |
| --FOLLOWING SQL SHOULD PASS |
| CREATE TABLE Employee ( |
| ssn INTEGER NOT NULL, |
| name VARCHAR(30), |
| salary INTEGER, |
| address VARCHAR(50), |
| constraint EmployeeKey PRIMARY KEY (ssn) |
| ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE Manages (manager_ssn INTEGER NOT NULL unique, |
| employee_ssn INTEGER NOT NULL, |
| constraint ManagesKey PRIMARY KEY (manager_ssn,employee_ssn), |
| FOREIGN KEY (employee_ssn) REFERENCES Employee(ssn) |
| ON DELETE CASCADE ON UPDATE NO ACTION, |
| FOREIGN KEY (manager_ssn) REFERENCES Employee(ssn) |
| ON DELETE CASCADE ON UPDATE NO ACTION); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE Shop( |
| shop_name VARCHAR(20)NOT NULL, |
| open_closed_times TIME, |
| department VARCHAR(20), |
| location INTEGER CHECK(location BETWEEN 1 AND 50), |
| floor INTEGER CHECK(floor BETWEEN 1 AND 4), |
| shift char(20), |
| rent INTEGER, |
| tel_no INTEGER, |
| income INTEGER, |
| expenditure INTEGER, |
| manager_ssn INTEGER, |
| FOREIGN KEY (manager_ssn) REFERENCES Manages(manager_ssn) |
| ON DELETE SET NULL ON UPDATE NO ACTION, |
| constraint ShopKey PRIMARY KEY (shop_name)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE Works_in ( |
| ssn INTEGER NOT NULL, |
| shop_name VARCHAR(20)NOT NULL, |
| since DATE, |
| task VARCHAR(20), |
| constraint WorksInKey PRIMARY KEY (ssn, shop_name), |
| constraint fkey1 FOREIGN KEY (ssn) REFERENCES Employee |
| ON DELETE CASCADE ON UPDATE RESTRICT, |
| constraint fkey2 FOREIGN KEY (shop_name) REFERENCES Shop |
| ON DELETE NO ACTION ON UPDATE NO ACTION); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE Owns(ssn INTEGER NOT NULL, |
| shop_name VARCHAR(20)NOT NULL, |
| date1 DATE, |
| constraint OwnsKey PRIMARY KEY (ssn, shop_name), |
| FOREIGN KEY (ssn) REFERENCES Employee |
| ON DELETE CASCADE ON UPDATE NO ACTION, |
| FOREIGN KEY (shop_name) REFERENCES Shop |
| ON DELETE CASCADE ON UPDATE NO ACTION); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE Item(item_id INTEGER NOT NULL, |
| Supplier VARCHAR(20), |
| price INTEGER, |
| department VARCHAR(20), |
| constraint ItemKey PRIMARY KEY (item_id)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE Producer(producer_name VARCHAR(20) NOT NULL, |
| city VARCHAR(20), |
| address VARCHAR(50), |
| department VARCHAR(20), |
| constraint ProducerKey PRIMARY KEY (producer_name)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE Supplies (producer_name VARCHAR(20) NOT NULL, |
| item_id INTEGER NOT NULL, |
| constraint SuppliesKey PRIMARY KEY (producer_name, item_id), |
| FOREIGN KEY (item_id) REFERENCES Item |
| ON DELETE CASCADE ON UPDATE NO ACTION, |
| FOREIGN KEY (producer_name) REFERENCES Producer |
| ON DELETE RESTRICT ON UPDATE NO ACTION); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE Sells( item_id INTEGER NOT NULL, |
| shop_name VARCHAR(20)NOT NULL, |
| constraint SellsKey PRIMARY KEY (item_id, shop_name), |
| FOREIGN KEY (item_id) REFERENCES Item |
| ON DELETE NO ACTION ON UPDATE NO ACTION, |
| FOREIGN KEY (shop_name) REFERENCES Shop |
| ON DELETE CASCADE ON UPDATE NO ACTION); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE Commerce(item_id INTEGER NOT NULL, |
| shop_name VARCHAR(20)NOT NULL, |
| cost INTEGER, |
| date1 DATE, |
| constraint CommerceKey PRIMARY KEY (item_id, shop_name), |
| FOREIGN KEY (item_id) REFERENCES Item |
| ON DELETE CASCADE ON UPDATE NO ACTION, |
| FOREIGN KEY (shop_name) REFERENCES Shop |
| ON DELETE RESTRICT ON UPDATE NO ACTION); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE Stocks( item_id INTEGER NOT NULL, |
| shop_name VARCHAR(20) NOT NULL, |
| available INTEGER, |
| purchased_date DATE, |
| ordered INTEGER, |
| constraint StocksKey PRIMARY KEY (item_id, shop_name), |
| FOREIGN KEY (item_id) REFERENCES Item |
| ON DELETE CASCADE ON UPDATE NO ACTION, |
| FOREIGN KEY (shop_name) REFERENCES Shop |
| ON DELETE RESTRICT ON UPDATE NO ACTION); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE Orders( |
| producer_name VARCHAR(20) NOT NULL, |
| shop_name VARCHAR(20)NOT NULL, |
| item_id INTEGER NOT NULL, |
| receival_date DATE, |
| order_date DATE, |
| item_amount INTEGER, |
| cost INTEGER, |
| constraint OrdersKey PRIMARY KEY (item_id, shop_name, producer_name), |
| FOREIGN KEY (shop_name) REFERENCES Shop |
| ON DELETE RESTRICT ON UPDATE NO ACTION, |
| FOREIGN KEY (item_id) REFERENCES Item |
| ON DELETE CASCADE ON UPDATE NO ACTION ); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE Food( |
| item_id INTEGER NOT NULL, |
| type VARCHAR(20), |
| expiration_date DATE, |
| constraint FoodKey PRIMARY KEY (item_id), |
| FOREIGN KEY (item_id) REFERENCES Item |
| ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE Media( |
| item_id INTEGER NOT NULL, |
| type VARCHAR(20), |
| author VARCHAR(50), |
| publisher VARCHAR(50), |
| title VARCHAR(20), |
| published_date DATE, |
| constraint MediaKey PRIMARY KEY (item_id), |
| FOREIGN KEY (item_id) REFERENCES Item |
| ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE Clothing( |
| item_id INTEGER NOT NULL, |
| type VARCHAR(20), |
| color VARCHAR(20), |
| cloth_size char(2), |
| brand VARCHAR(30), |
| constraint ClothingKey PRIMARY KEY (item_id), |
| FOREIGN KEY (item_id) REFERENCES Item |
| ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE Accessories( |
| item_id INTEGER NOT NULL, |
| type VARCHAR(20), |
| constraint AccessoriesKey PRIMARY KEY (item_id), |
| FOREIGN KEY (item_id) REFERENCES Item |
| ON DELETE CASCADE); |
| 0 rows inserted/updated/deleted |
| ij> ---END |
| ; |
| ij> |
