in perfkitbenchmarker/data/sysbench/spanner_pg_tpcc_common.lua [187:394]
function create_tables(drv, con, table_num)
local id_index_def, id_def
local engine_def = ""
local extra_table_options = ""
local query
local tinyint_type="int"
local datetime_type="timestamptz"
if drv:name() == "mysql" or drv:name() == "attachsql" or
drv:name() == "drizzle"
then
engine_def = "/*! ENGINE = " .. sysbench.opt.mysql_storage_engine .. " */"
extra_table_options = sysbench.opt.mysql_table_options or ""
tinyint_type="tinyint"
datetime_type="datetime"
end
log(string.format("Creating tables: %d\n", table_num))
query = string.format([[
CREATE TABLE IF NOT EXISTS warehouse%d (
w_id int not null,
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state varchar(2),
w_zip varchar(9),
w_tax decimal,
w_ytd decimal,
primary key (w_id)
) %s %s;]],
table_num, engine_def, extra_table_options)
query = query .. string.format([[
create table IF NOT EXISTS district%d (
d_id ]] .. tinyint_type .. [[ not null,
d_w_id int not null,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state varchar(2),
d_zip varchar(9),
d_tax decimal,
d_ytd decimal,
d_next_o_id int,
primary key (d_w_id, d_id)
) %s %s;]],
table_num, engine_def, extra_table_options)
query = query .. string.format([[
create table IF NOT EXISTS customer%d (
c_id int not null,
c_d_id int not null,
c_w_id int not null,
c_first varchar(16),
c_middle varchar(2),
c_last varchar(16),
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state varchar(2),
c_zip varchar(9),
c_phone varchar(16),
c_since ]] .. datetime_type .. [[,
c_credit varchar(2),
c_credit_lim bigint,
c_discount decimal,
c_balance decimal,
c_ytd_payment decimal,
c_payment_cnt int,
c_delivery_cnt int,
c_data text,
PRIMARY KEY(c_w_id, c_d_id, c_id)
) %s %s;]],
table_num, engine_def, extra_table_options)
local hist_auto_inc=""
local hist_pk=",PRIMARY KEY(h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date)"
if sysbench.opt.force_pk == 1 then
hist_auto_inc="id int NOT NULL AUTO_INCREMENT,"
hist_pk=",PRIMARY KEY(id)"
end
query = query .. string.format([[
create table IF NOT EXISTS history%d (
%s
h_c_id int,
h_c_d_id ]] .. tinyint_type .. [[,
h_c_w_id int,
h_d_id ]] .. tinyint_type .. [[,
h_w_id int,
h_date ]] .. datetime_type .. [[,
h_amount decimal,
h_data varchar(24) %s
) %s %s;]],
table_num, hist_auto_inc, hist_pk, engine_def, extra_table_options)
query = query .. string.format([[
create table IF NOT EXISTS orders%d (
o_id int not null,
o_d_id ]] .. tinyint_type .. [[ not null,
o_w_id int not null,
o_c_id int,
o_entry_d ]] .. datetime_type .. [[,
o_carrier_id ]] .. tinyint_type .. [[,
o_ol_cnt ]] .. tinyint_type .. [[,
o_all_local ]] .. tinyint_type .. [[,
PRIMARY KEY(o_w_id, o_d_id, o_id)
) %s %s;]],
table_num, engine_def, extra_table_options)
query = query .. string.format([[
create table IF NOT EXISTS new_orders%d (
no_o_id int not null,
no_d_id ]] .. tinyint_type .. [[ not null,
no_w_id int not null,
PRIMARY KEY(no_w_id, no_d_id, no_o_id)
) %s %s;]],
table_num, engine_def, extra_table_options)
query = query .. string.format([[
create table IF NOT EXISTS order_line%d (
ol_o_id int not null,
ol_d_id ]] .. tinyint_type .. [[ not null,
ol_w_id int not null,
ol_number ]] .. tinyint_type .. [[ not null,
ol_i_id int,
ol_supply_w_id int,
ol_delivery_d ]] .. datetime_type .. [[,
ol_quantity ]] .. tinyint_type .. [[,
ol_amount decimal,
ol_dist_info varchar(24),
PRIMARY KEY(ol_w_id, ol_d_id, ol_o_id, ol_number)
) %s %s;]],
table_num, engine_def, extra_table_options)
query = query .. string.format([[
create table IF NOT EXISTS stock%d (
s_i_id int not null,
s_w_id int not null,
s_quantity int,
s_dist_01 varchar(24),
s_dist_02 varchar(24),
s_dist_03 varchar(24),
s_dist_04 varchar(24),
s_dist_05 varchar(24),
s_dist_06 varchar(24),
s_dist_07 varchar(24),
s_dist_08 varchar(24),
s_dist_09 varchar(24),
s_dist_10 varchar(24),
s_ytd decimal,
s_order_cnt int,
s_remote_cnt int,
s_data varchar(50),
PRIMARY KEY(s_w_id, s_i_id)
) %s %s;]],
table_num, engine_def, extra_table_options)
local i = table_num
query = query .. string.format([[
create table IF NOT EXISTS item%d (
i_id int not null,
i_im_id int,
i_name varchar(24),
i_price decimal,
i_data varchar(50),
PRIMARY KEY(i_id)
) %s %s;]],
i, engine_def, extra_table_options)
log(string.format("Adding indexes %d ... \n", i))
query = query .. "CREATE INDEX idx_customer"..i.." ON customer"..i.." (c_w_id,c_d_id,c_last,c_first);"
query = query .. "CREATE INDEX idx_orders"..i.." ON orders"..i.." (o_w_id,o_d_id,o_c_id,o_id);"
query = query .. "CREATE INDEX fkey_stock_2"..i.." ON stock"..i.." (s_i_id);"
query = query .. "CREATE INDEX fkey_order_line_2"..i.." ON order_line"..i.." (ol_supply_w_id,ol_i_id);"
query = query .. "CREATE INDEX fkey_history_1"..i.." ON history"..i.." (h_c_w_id,h_c_d_id,h_c_id);"
query = query .. "CREATE INDEX fkey_history_2"..i.." ON history"..i.." (h_w_id,h_d_id );"
if sysbench.opt.use_fk == 1 then
log(string.format("Adding FK %d ... \n", i))
query = query .. "ALTER TABLE new_orders"..i.." ADD CONSTRAINT fkey_new_orders_1_"..i.." FOREIGN KEY(no_w_id,no_d_id,no_o_id) REFERENCES orders"..i.."(o_w_id,o_d_id,o_id);"
query = query .. "ALTER TABLE orders"..i.." ADD CONSTRAINT fkey_orders_1_"..i.." FOREIGN KEY(o_w_id,o_d_id,o_c_id) REFERENCES customer"..i.."(c_w_id,c_d_id,c_id);"
query = query .. "ALTER TABLE customer"..i.." ADD CONSTRAINT fkey_customer_1_"..i.." FOREIGN KEY(c_w_id,c_d_id) REFERENCES district"..i.."(d_w_id,d_id);"
query = query .. "ALTER TABLE history"..i.." ADD CONSTRAINT fkey_history_1_"..i.." FOREIGN KEY(h_c_w_id,h_c_d_id,h_c_id) REFERENCES customer"..i.."(c_w_id,c_d_id,c_id);"
query = query .. "ALTER TABLE history"..i.." ADD CONSTRAINT fkey_history_2_"..i.." FOREIGN KEY(h_w_id,h_d_id) REFERENCES district"..i.."(d_w_id,d_id);"
query = query .. "ALTER TABLE district"..i.." ADD CONSTRAINT fkey_district_1_"..i.." FOREIGN KEY(d_w_id) REFERENCES warehouse"..i.."(w_id);"
query = query .. "ALTER TABLE order_line"..i.." ADD CONSTRAINT fkey_order_line_1_"..i.." FOREIGN KEY(ol_w_id,ol_d_id,ol_o_id) REFERENCES orders"..i.."(o_w_id,o_d_id,o_id);"
query = query .. "ALTER TABLE order_line"..i.." ADD CONSTRAINT fkey_order_line_2_"..i.." FOREIGN KEY(ol_supply_w_id,ol_i_id) REFERENCES stock"..i.."(s_w_id,s_i_id);"
query = query .. "ALTER TABLE stock"..i.." ADD CONSTRAINT fkey_stock_1_"..i.." FOREIGN KEY(s_w_id) REFERENCES warehouse"..i.."(w_id);"
query = query .. "ALTER TABLE stock"..i.." ADD CONSTRAINT fkey_stock_2_"..i.." FOREIGN KEY(s_i_id) REFERENCES item"..i.."(i_id);"
end
log("Send all DDLs in a single query.\n")
con:query(query)
log('Finished creating tables\n')
end