BenchmarkSQL测试脚本实现

sqlTableCreates

DROP SCHEMA IF EXISTS benchmarksql CASCADE;

CREATE SCHEMA benchmarksql;

create table benchmarksql.warehouse (
w_id integer not null,
w_ytd decimal(12,2),
w_tax decimal(4,4),
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9)
);

create table benchmarksql.district (
d_w_id integer not null,
d_id integer not null,
d_ytd decimal(12,2),
d_tax decimal(4,4),
d_next_o_id integer,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9)
);

create table benchmarksql.customer (
c_w_id integer not null,
c_d_id integer not null,
c_id integer not null,
c_discount decimal(4,4),
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_credit_lim decimal(12,2),
c_balance decimal(12,2),
c_ytd_payment float,
c_payment_cnt integer,
c_delivery_cnt integer,
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since timestamp,
c_middle char(2),
c_data varchar(500)
);

create sequence hist_id_seq;

create table benchmarksql.history (
hist_id integer not null default nextval('hist_id_seq') primary key,
h_c_id integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id integer,
h_w_id integer,
h_date timestamp,
h_amount decimal(6,2),
h_data varchar(24)
);

create table benchmarksql.oorder (
o_w_id integer not null,
o_d_id integer not null,
o_id integer not null,
o_c_id integer,
o_carrier_id integer,
o_ol_cnt decimal(2,0),
o_all_local decimal(1,0),
o_entry_d timestamp
);

create table benchmarksql.new_order (
no_w_id integer not null,
no_d_id integer not null,
no_o_id integer not null
);

create table benchmarksql.order_line (
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer not null,
ol_number integer not null,
ol_i_id integer not null,
ol_delivery_d timestamp,
ol_amount decimal(6,2),
ol_supply_w_id integer,
ol_quantity decimal(2,0),
ol_dist_info char(24)
);

create table benchmarksql.stock (
s_w_id integer not null,
s_i_id integer not null,
s_quantity decimal(4,0),
s_ytd decimal(8,2),
s_order_cnt integer,
s_remote_cnt integer,
s_data varchar(50),
s_dist_01 char(24),
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24),
s_dist_05 char(24),
s_dist_06 char(24),
s_dist_07 char(24),
s_dist_08 char(24),
s_dist_09 char(24),
s_dist_10 char(24)
);

create table benchmarksql.item (
i_id integer not null,
i_name varchar(24),
i_price decimal(5,2),
i_data varchar(50),
i_im_id integer
);

sqlTableDrops 

drop table benchmarksql.warehouse;

drop table benchmarksql.item;

drop table benchmarksql.stock;

drop table benchmarksql.district;

drop table benchmarksql.customer;

drop table benchmarksql.oorder;

drop table benchmarksql.order_line;

drop table benchmarksql.history;

drop table benchmarksql.new_order;

sqlIndexCreates

alter table benchmarksql.warehouse add constraint pk_warehouse
primary key (w_id);

alter table benchmarksql.district add constraint pk_district
primary key (d_w_id, d_id);

alter table benchmarksql.customer add constraint pk_customer
primary key (c_w_id, c_d_id, c_id);

create index ndx_customer_name
on benchmarksql.customer (c_w_id, c_d_id, c_last, c_first);

select setval('hist_id_seq', (select max(hist_id) + 1 from benchmarksql.history), false);

alter table benchmarksql.oorder add constraint pk_oorder
primary key (o_w_id, o_d_id, o_id);

create unique index ndx_oorder_carrier
on benchmarksql.oorder (o_w_id, o_d_id, o_carrier_id, o_id);

alter table benchmarksql.new_order add constraint pk_new_order
primary key (no_w_id, no_d_id, no_o_id);

alter table benchmarksql.order_line add constraint pk_order_line
primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);

alter table benchmarksql.stock add constraint pk_stock
primary key (s_w_id, s_i_id);

alter table benchmarksql.item add constraint pk_item
primary key (i_id);

vacuum analyze;

 sqlIndexDrops

alter table benchmarksql.warehouse drop constraint pk_warehouse;

alter table benchmarksql.district drop constraint pk_district;

alter table benchmarksql.customer drop constraint pk_customer;
drop index ndx_customer_name;

-- history table has no primary key
-- commit;

alter table benchmarksql.oorder drop constraint pk_oorder;
drop index ndx_oorder_carrier;

alter table benchmarksql.new_order drop constraint pk_new_order;

alter table benchmarksql.order_line drop constraint pk_order_line;

alter table benchmarksql.stock drop constraint pk_stock;

alter table benchmarksql.item drop constraint pk_item;

 sqlTableCopies

copy benchmarksql.warehouse
(w_id, w_ytd, w_tax, w_name, w_street_1, w_street_2, w_city, w_state, w_zip)
from '/tmp/csv/warehouse.csv' WITH CSV;

copy benchmarksql.item
(i_id, i_name, i_price, i_data, i_im_id)
from '/tmp/csv/item.csv' WITH CSV;

copy benchmarksql.stock
(s_i_id, s_w_id, s_quantity, s_ytd, s_order_cnt, s_remote_cnt, s_data,
s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05,
s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10)
from '/tmp/csv/stock.csv' WITH CSV;

copy benchmarksql.district
(d_id, d_w_id, d_ytd, d_tax, d_next_o_id, d_name, d_street_1,
d_street_2, d_city, d_state, d_zip)
from '/tmp/csv/district.csv' WITH CSV;

copy benchmarksql.customer
(c_id, c_d_id, c_w_id, c_discount, c_credit, c_last, c_first, c_credit_lim,
c_balance, c_ytd_payment, c_payment_cnt, c_delivery_cnt, c_street_1,
c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_middle, c_data)
from '/tmp/csv/customer.csv' WITH CSV;

copy benchmarksql.history
(hist_id, h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data)
from '/tmp/csv/cust-hist.csv' WITH CSV;

copy benchmarksql.oorder
(o_id, o_w_id, o_d_id, o_c_id, o_carrier_id, o_ol_cnt, o_all_local, o_entry_d)
from '/tmp/csv/order.csv' WITH CSV;

copy benchmarksql.order_line
(ol_w_id, ol_d_id, ol_o_id, ol_number, ol_i_id, ol_delivery_d,
ol_amount, ol_supply_w_id, ol_quantity, ol_dist_info)
from '/tmp/csv/order-line.csv' WITH CSV;

copy benchmarksql.new_order
(no_w_id, no_d_id, no_o_id)
from '/tmp/csv/new-order.csv' WITH CSV;

sqlTableTruncates

truncate table benchmarksql.warehouse;

truncate table benchmarksql.item;

truncate table benchmarksql.stock;

truncate table benchmarksql.district;

truncate table benchmarksql.customer;

truncate table benchmarksql.history;

truncate table benchmarksql.oorder;

truncate table benchmarksql.order_line;

truncate table benchmarksql.new_order;

clean.sh

rm -rf log/

mkdir -p log/archive