x5QdXx7A

1SET @OLD_UNIQUE_CHECKS = @@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
2SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;
3
4DROP DATABASE IF EXISTS TPCCH;
5CREATE DATABASE TPCCH;
6
7CREATE TABLE TPCCH.WAREHOUSE (
8  W_ID       SMALLINT NOT NULL,
9  W_NAME     VARCHAR(10),
10  W_STREET_1 VARCHAR(20),
11  W_STREET_2 VARCHAR(20),
12  W_CITY     VARCHAR(20),
13  W_STATE    CHAR(2),
14  W_ZIP      CHAR(9),
15  W_TAX      DECIMAL(4, 2),
16  W_YTD      DECIMAL(12, 2),
17  PRIMARY KEY (W_ID)
18)
19  ENGINE = INNODB;
20
21CREATE TABLE TPCCH.DISTRICT (
22  D_ID        TINYINT  NOT NULL,
23  D_W_ID      SMALLINT NOT NULL,
24  D_NAME      VARCHAR(10),
25  D_STREET_1  VARCHAR(20),
26  D_STREET_2  VARCHAR(20),
27  D_CITY      VARCHAR(20),
28  D_STATE     CHAR(2),
29  D_ZIP       CHAR(9),
30  D_TAX       DECIMAL(4, 2),
31  D_YTD       DECIMAL(12, 2),
32  D_NEXT_O_ID INT,
33  PRIMARY KEY (D_W_ID, D_ID)
34)
35  ENGINE = INNODB;
36
37CREATE TABLE TPCCH.CUSTOMER (
38  C_ID           INT      NOT NULL,
39  C_D_ID         TINYINT  NOT NULL,
40  C_W_ID         SMALLINT NOT NULL,
41  C_FIRST        VARCHAR(16),
42  C_MIDDLE       CHAR(2),
43  C_LAST         VARCHAR(16),
44  C_STREET_1     VARCHAR(20),
45  C_STREET_2     VARCHAR(20),
46  C_CITY         VARCHAR(20),
47  C_STATE        CHAR(2),
48  C_ZIP          CHAR(9),
49  C_PHONE        CHAR(16),
50  C_SINCE        DATETIME,
51  C_CREDIT       CHAR(2),
52  C_CREDIT_LIM   BIGINT,
53  C_DISCOUNT     DECIMAL(4, 2),
54  C_BALANCE      DECIMAL(12, 2),
55  C_YTD_PAYMENT  DECIMAL(12, 2),
56  C_PAYMENT_CNT  SMALLINT,
57  C_DELIVERY_CNT SMALLINT,
58  C_DATA         TEXT,
59  PRIMARY KEY (C_W_ID, C_D_ID, C_ID)
60)
61  ENGINE = INNODB;
62
63CREATE TABLE TPCCH.HISTORY (
64  H_C_ID   INT,
65  H_C_D_ID TINYINT,
66  H_C_W_ID SMALLINT,
67  H_D_ID   TINYINT,
68  H_W_ID   SMALLINT,
69  H_DATE   DATETIME,
70  H_AMOUNT DECIMAL(6, 2),
71  H_DATA   VARCHAR(24)
72)
73  ENGINE = INNODB;
74
75CREATE TABLE TPCCH.NEW_ORDERS (
76  NO_O_ID INT      NOT NULL,
77  NO_D_ID TINYINT  NOT NULL,
78  NO_W_ID SMALLINT NOT NULL,
79  PRIMARY KEY (NO_W_ID, NO_D_ID, NO_O_ID)
80)
81  ENGINE = INNODB;
82
83CREATE TABLE TPCCH.ORDERS (
84  O_ID         INT      NOT NULL,
85  O_D_ID       TINYINT  NOT NULL,
86  O_W_ID       SMALLINT NOT NULL,
87  O_C_ID       INT,
88  O_ENTRY_D    DATETIME,
89  O_CARRIER_ID TINYINT,
90  O_OL_CNT     TINYINT,
91  O_ALL_LOCAL  TINYINT,
92  PRIMARY KEY (O_W_ID, O_D_ID, O_ID)
93)
94  ENGINE = INNODB;
95
96CREATE TABLE TPCCH.ORDER_LINE (
97  OL_O_ID        INT      NOT NULL,
98  OL_D_ID        TINYINT  NOT NULL,
99  OL_W_ID        SMALLINT NOT NULL,
100  OL_NUMBER      TINYINT  NOT NULL,
101  OL_I_ID        INT,
102  OL_SUPPLY_W_ID SMALLINT,
103  OL_DELIVERY_D  DATETIME,
104  OL_QUANTITY    TINYINT,
105  OL_AMOUNT      DECIMAL(6, 2),
106  OL_DIST_INFO   CHAR(24),
107  PRIMARY KEY (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)
108)
109  ENGINE = INNODB;
110
111CREATE TABLE TPCCH.ITEM (
112  I_ID    INT NOT NULL,
113  I_IM_ID INT,
114  I_NAME  VARCHAR(24),
115  I_PRICE DECIMAL(5, 2),
116  I_DATA  VARCHAR(50),
117  PRIMARY KEY (I_ID)
118)
119  ENGINE = INNODB;
120
121CREATE TABLE TPCCH.STOCK (
122  S_I_ID       INT      NOT NULL,
123  S_W_ID       SMALLINT NOT NULL,
124  S_QUANTITY   SMALLINT,
125  S_DIST_01    CHAR(24),
126  S_DIST_02    CHAR(24),
127  S_DIST_03    CHAR(24),
128  S_DIST_04    CHAR(24),
129  S_DIST_05    CHAR(24),
130  S_DIST_06    CHAR(24),
131  S_DIST_07    CHAR(24),
132  S_DIST_08    CHAR(24),
133  S_DIST_09    CHAR(24),
134  S_DIST_10    CHAR(24),
135  S_YTD        DECIMAL(8, 0),
136  S_ORDER_CNT  SMALLINT,
137  S_REMOTE_CNT SMALLINT,
138  S_DATA       VARCHAR(50),
139  PRIMARY KEY (S_W_ID, S_I_ID)
140)
141  ENGINE = INNODB;
142
143
144CREATE TABLE TPCCH.NATION (
145  N_NATIONKEY TINYINT      NOT NULL,
146  N_NAME      CHAR(25)     NOT NULL,
147  N_REGIONKEY TINYINT      NOT NULL,
148  N_COMMENT   VARCHAR(152) NOT NULL,
149  PRIMARY KEY (N_NATIONKEY)
150)
151  ENGINE = INNODB;
152
153CREATE TABLE TPCCH.SUPPLIER (
154  SU_SUPPKEY   SMALLINT     NOT NULL,
155  SU_NAME      CHAR(25)     NOT NULL,
156  SU_ADDRESS   VARCHAR(40)  NOT NULL,
157  SU_NATIONKEY TINYINT      NOT NULL,
158  SU_PHONE     CHAR(15)     NOT NULL,
159  SU_ACCTBAL   DECIMAL      NOT NULL,
160  SU_COMMENT   VARCHAR(101) NOT NULL,
161  PRIMARY KEY (SU_SUPPKEY)
162)
163  ENGINE = INNODB;
164
165CREATE TABLE TPCCH.REGION (
166  R_REGIONKEY TINYINT      NOT NULL,
167  R_NAME      CHAR(25)     NOT NULL,
168  R_COMMENT   VARCHAR(152) NOT NULL,
169  PRIMARY KEY (R_REGIONKEY)
170)
171  ENGINE = INNODB;
172
173SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;
174SET UNIQUE_CHECKS = @OLD_UNIQUE_CHECKS;