tpch-q9-sqlite-prep.sql

CREATE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL,
                            N_NAME       CHAR(25) NOT NULL,
                            PRIMARY KEY (N_NATIONKEY));
CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL,
                          P_NAME        VARCHAR(55) NOT NULL,
                          PRIMARY KEY (P_PARTKEY));
CREATE TABLE SUPPLIER ( S_SUPPKEY     INTEGER NOT NULL,
                             S_NATIONKEY   INTEGER NOT NULL REFERENCES NATION (N_NATIONKEY),
                             PRIMARY KEY (S_SUPPKEY));
CREATE TABLE PARTSUPP ( PS_PARTKEY     INTEGER NOT NULL REFERENCES PART (P_PARTKEY),
                             PS_SUPPKEY     INTEGER NOT NULL REFERENCES SUPPLIER (S_SUPPKEY),
                             PS_SUPPLYCOST  DOUBLE  NOT NULL,
                             PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY));
CREATE TABLE ORDERS  ( O_ORDERKEY       INTEGER NOT NULL,
                           O_ORDERDATE      DATE NOT NULL,
                           PRIMARY KEY (O_ORDERKEY));
CREATE TABLE LINEITEM ( L_PARTKEY     INTEGER NOT NULL REFERENCES PART (P_PARTKEY),
                             L_SUPPKEY     INTEGER NOT NULL REFERENCES SUPPLIER (S_SUPPKEY),
                             L_ORDERKEY    INTEGER NOT NULL REFERENCES ORDERS (O_ORDERKEY),
                             L_LINENUMBER  INTEGER NOT NULL,
                             L_QUANTITY    DOUBLE NOT NULL,
                             L_EXTENDEDPRICE  DOUBLE NOT NULL,
                             L_DISCOUNT    DOUBLE NOT NULL,
                             PRIMARY KEY (L_ORDERKEY, L_LINENUMBER));

CREATE INDEX LINEITEM_idx_q9 ON LINEITEM(L_PARTKEY, L_SUPPKEY, L_ORDERKEY, L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT);
CREATE INDEX PART_idx_q9 ON PART(P_PARTKEY, P_NAME);
CREATE INDEX ORDERS_idx_q9 ON ORDERS(O_ORDERKEY, O_ORDERDATE);
CREATE INDEX SUPPLIER_idx_q9 ON SUPPLIER(S_SUPPKEY, S_NATIONKEY);
CREATE INDEX PARTSUPP_idx_q9 ON PARTSUPP(PS_PARTKEY, PS_SUPPKEY, PS_SUPPLYCOST);
CREATE INDEX NATION_idx_q9 ON NATION(N_NATIONKEY, N_NAME);

ATTACH DATABASE 'TPC-H.db' AS t;

INSERT INTO NATION
SELECT N_NATIONKEY, N_NAME
FROM t.NATION
ORDER BY 1;

INSERT INTO PART
SELECT P_PARTKEY, P_NAME
FROM t.PART
ORDER BY 1;

INSERT INTO SUPPLIER
SELECT S_SUPPKEY, S_NATIONKEY
FROM t.SUPPLIER
ORDER BY 1;

INSERT INTO PARTSUPP
SELECT PS_PARTKEY, PS_SUPPKEY, PS_SUPPLYCOST
FROM t.PARTSUPP
ORDER BY 1, 2;

INSERT INTO ORDERS
SELECT O_ORDERKEY, O_ORDERDATE
FROM t.ORDERS
ORDER BY 1;

INSERT INTO LINEITEM
SELECT L_PARTKEY, L_SUPPKEY, L_ORDERKEY, L_LINENUMBER, L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT
FROM t.LINEITEM
ORDER BY 1, 2, 3, 4, 5, 6, 7;

DETACH DATABASE t;

SELECT page_count * page_size as size
FROM pragma_page_count(), pragma_page_size();