db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r467474 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema: ./ foreignkey.sql primarykey.sql schema.sql
Date Tue, 24 Oct 2006 20:45:07 GMT
Author: djd
Date: Tue Oct 24 13:45:07 2006
New Revision: 467474

URL: http://svn.apache.org/viewvc?view=rev&rev=467474
Log:
DERBY-1995 (partial) Add SQL scripts to create tables, primary key and foreign key constraints
for order entry testkit.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/foreignkey.sql
  (with props)
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/primarykey.sql
  (with props)
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/schema.sql 
 (with props)

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/foreignkey.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/foreignkey.sql?view=auto&rev=467474
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/foreignkey.sql
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/foreignkey.sql
Tue Oct 24 13:45:07 2006
@@ -0,0 +1,48 @@
+-- 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.
+;
+
+-- Foreign key constraint definitions from TPC-C specification, version 5.7.
+-- Section 1.3 Table Layout
+;
+
+ALTER TABLE DISTRICT ADD CONSTRAINT
+    D_W_FK FOREIGN KEY (D_W_ID) REFERENCES WAREHOUSE;
+
+ALTER TABLE CUSTOMER ADD CONSTRAINT
+    C_D_FK_DISTRICT FOREIGN KEY (C_W_ID,C_D_ID) REFERENCES DISTRICT;
+
+ALTER TABLE STOCK ADD CONSTRAINT
+    S_W_FK FOREIGN KEY (S_W_ID) REFERENCES WAREHOUSE;
+ALTER TABLE STOCK ADD CONSTRAINT
+    S_I_FK FOREIGN KEY (S_I_ID) REFERENCES ITEM;
+
+ALTER TABLE HISTORY ADD CONSTRAINT
+    H_C_FK FOREIGN KEY (H_C_W_ID, H_C_D_ID, H_C_ID) REFERENCES CUSTOMER;
+ALTER TABLE HISTORY ADD CONSTRAINT
+    H_D_FK FOREIGN KEY (H_W_ID, H_D_ID) REFERENCES DISTRICT;
+
+ALTER TABLE ORDERS ADD CONSTRAINT
+    O_C_FK FOREIGN KEY (O_W_ID, O_D_ID, O_C_ID) REFERENCES CUSTOMER;
+
+ALTER TABLE NEWORDERS ADD CONSTRAINT
+    NO_O_FK FOREIGN KEY (NO_W_ID, NO_D_ID, NO_O_ID) REFERENCES ORDERS;
+
+ALTER TABLE ORDERLINE ADD CONSTRAINT
+    OL_O_FK FOREIGN KEY (OL_W_ID, OL_D_ID, OL_O_ID) REFERENCES ORDERS;
+ALTER TABLE ORDERLINE ADD CONSTRAINT
+    OL_S_FK FOREIGN KEY (OL_SUPPLY_W_ID, OL_I_ID) REFERENCES STOCK;
+
+

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/foreignkey.sql
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/primarykey.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/primarykey.sql?view=auto&rev=467474
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/primarykey.sql
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/primarykey.sql
Tue Oct 24 13:45:07 2006
@@ -0,0 +1,43 @@
+-- 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.
+;
+
+-- Primary key constraint definitions from TPC-C specification, version 5.7.
+-- Section 1.3 Table Layout
+;
+ALTER TABLE WAREHOUSE ADD CONSTRAINT
+    WAREHOUSE_PK PRIMARY KEY (W_ID);
+
+ALTER TABLE DISTRICT ADD CONSTRAINT
+    DISTRICT_PK PRIMARY KEY (D_W_ID, D_ID);
+
+ALTER TABLE CUSTOMER ADD CONSTRAINT
+    CUSTOMER_PK PRIMARY KEY(C_W_ID, C_D_ID, C_ID);
+
+ALTER TABLE ITEM ADD CONSTRAINT
+    ITEM_PK PRIMARY KEY (I_ID);
+
+ALTER TABLE STOCK ADD CONSTRAINT
+    STOCK_PK PRIMARY KEY (S_W_ID, S_I_ID);
+
+ALTER TABLE ORDERS ADD CONSTRAINT
+    ORDERS_PK PRIMARY KEY(O_W_ID, O_D_ID, O_ID);
+
+ALTER TABLE NEWORDERS ADD CONSTRAINT
+    NEWORDERS_PK PRIMARY KEY(NO_W_ID, NO_D_ID, NO_O_ID);
+
+ALTER TABLE ORDERLINE ADD CONSTRAINT
+    ORDERLINE_PK PRIMARY KEY(OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER);
+    
\ No newline at end of file

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/primarykey.sql
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/schema.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/schema.sql?view=auto&rev=467474
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/schema.sql (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/schema.sql Tue
Oct 24 13:45:07 2006
@@ -0,0 +1,149 @@
+-- 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.
+;
+
+-- Table definitions from TPC-C specification, version 5.7.
+-- Section 1.3 Table Layout
+--
+-- Constraints are defined in a separate SQL scripts to
+-- allow intial data load with or without contstraints.
+;
+
+CREATE TABLE WAREHOUSE (
+ W_ID       SMALLINT       NOT NULL,
+
+ W_NAME     VARCHAR(10)    NOT NULL,
+ W_STREET_1 VARCHAR(20)    NOT NULL,
+ W_STREET_2 VARCHAR(20)    NOT NULL,
+ W_CITY     VARCHAR(20)    NOT NULL,
+ W_STATE    CHAR(2)        NOT NULL,
+ W_ZIP      CHAR(9)        NOT NULL,
+ W_TAX      DECIMAL (4,4)  NOT NULL,
+ W_YTD      DECIMAL (12,2) NOT NULL
+);
+
+CREATE TABLE DISTRICT (
+ D_ID        SMALLINT       NOT NULL,
+ D_W_ID      SMALLINT       NOT NULL,
+
+ D_NAME      VARCHAR(10)    NOT NULL,
+ D_STREET_1  VARCHAR(20)    NOT NULL,
+ D_STREET_2  VARCHAR(20)    NOT NULL,
+ D_CITY      VARCHAR(20)    NOT NULL,
+ D_STATE     CHAR(2)        NOT NULL,
+ D_ZIP       CHAR(9)        NOT NULL,
+ D_TAX       DECIMAL (4,4)  NOT NULL,
+ D_YTD       DECIMAL (12,2) NOT NULL,
+ D_NEXT_O_ID INTEGER        NOT NULL
+ );
+
+CREATE TABLE CUSTOMER (
+ C_ID           INTEGER       NOT NULL,
+ C_D_ID         SMALLINT      NOT NULL,
+ C_W_ID         SMALLINT      NOT NULL,
+
+ C_FIRST        VARCHAR(16)   NOT NULL,
+ C_MIDDLE       CHAR(2)       NOT NULL,
+ C_LAST         VARCHAR(16)   NOT NULL,
+ C_STREET_1     VARCHAR(20)   NOT NULL,
+ C_STREET_2     VARCHAR(20)   NOT NULL,
+ C_CITY         VARCHAR(20)   NOT NULL,
+ C_STATE        CHAR(2)       NOT NULL,
+ C_ZIP          CHAR(9)       NOT NULL,
+ C_PHONE        CHAR(16)      NOT NULL,
+ C_SINCE        TIMESTAMP     NOT NULL,
+ C_CREDIT       CHAR(2)       NOT NULL,
+ C_CREDIT_LIM   DECIMAL(12,2) NOT NULL,
+ C_DISCOUNT     DECIMAL(4,4)  NOT NULL,
+ C_BALANCE      DECIMAL(12,2) NOT NULL,
+ C_YTD_PAYMENT  DECIMAL(12,2) NOT NULL,
+ C_PAYMENT_CNT  INTEGER       NOT NULL,
+ C_DELIVERY_CNT INTEGER       NOT NULL,
+ C_DATA         VARCHAR(500)  NOT NULL
+);
+
+CREATE TABLE HISTORY (
+ H_C_ID   INTEGER      NOT NULL,
+ H_C_D_ID SMALLINT     NOT NULL,
+ H_C_W_ID SMALLINT     NOT NULL,
+ H_D_ID   SMALLINT     NOT NULL,
+ H_W_ID   SMALLINT     NOT NULL ,
+ H_DATE   TIMESTAMP    NOT NULL,
+ H_AMOUNT DECIMAL(6,2) NOT NULL,
+ H_DATA   VARCHAR(24)  NOT NULL
+);
+
+CREATE TABLE NEWORDERS (
+ NO_O_ID  INTEGER  NOT NULL,
+ NO_D_ID  SMALLINT NOT NULL,
+ NO_W_ID  SMALLINT NOT NULL
+);
+
+CREATE TABLE ORDERS (
+ O_ID         INTEGER NOT NULL,
+ O_D_ID       SMALLINT NOT NULL,
+ O_W_ID       SMALLINT NOT NULL,
+ O_C_ID       INTEGER NOT NULL,
+
+ O_ENTRY_D    TIMESTAMP NOT NULL,
+ O_CARRIER_ID SMALLINT,
+ O_OL_CNT     SMALLINT NOT NULL,
+ O_ALL_LOCAL  SMALLINT NOT NULL
+);
+
+CREATE TABLE ORDERLINE (
+ OL_O_ID        INTEGER      NOT NULL,
+ OL_D_ID        SMALLINT     NOT NULL,
+ OL_W_ID        SMALLINT     NOT NULL,
+ OL_NUMBER      SMALLINT     NOT NULL,
+
+ OL_I_ID        INTEGER      NOT NULL,
+ OL_SUPPLY_W_ID SMALLINT     NOT NULL,
+ OL_DELIVERY_D  TIMESTAMP,
+ OL_QUANTITY    SMALLINT     NOT NULL,
+ OL_AMOUNT      DECIMAL(6,2) NOT NULL,
+ OL_DIST_INFO   CHAR(24)     NOT NULL
+);
+
+CREATE TABLE ITEM (
+ I_ID     INTEGER      NOT NULL,
+
+ I_IM_ID  INTEGER      NOT NULL,
+ I_NAME   VARCHAR(24)  NOT NULL,
+ I_PRICE  DECIMAL(5,2) NOT NULL,
+ I_DATA   VARCHAR(50)  NOT NULL
+);
+
+CREATE TABLE STOCK (
+ S_I_ID       INTEGER     NOT NULL,
+ S_W_ID       SMALLINT    NOT NULL,
+ S_QUANTITY   INTEGER     NOT NULL,
+
+ S_DIST_01    CHAR(24)    NOT NULL,
+ S_DIST_02    CHAR(24)    NOT NULL,
+ S_DIST_03    CHAR(24)    NOT NULL,
+ S_DIST_04    CHAR(24)    NOT NULL,
+ S_DIST_05    CHAR(24)    NOT NULL,
+ S_DIST_06    CHAR(24)    NOT NULL,
+ S_DIST_07    CHAR(24)    NOT NULL,
+ S_DIST_08    CHAR(24)    NOT NULL,
+ S_DIST_09    CHAR(24)    NOT NULL,
+ S_DIST_10    CHAR(24)    NOT NULL,
+
+ S_YTD        DECIMAL(8)  NOT NULL,
+ S_ORDER_CNT  INTEGER     NOT NULL,
+ S_REMOTE_CNT INTEGER     NOT NULL,
+ S_DATA       VARCHAR(50) NOT NULL
+);
\ No newline at end of file

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/schema.sql
------------------------------------------------------------------------------
    svn:eol-style = native



Mime
View raw message