in perfkitbenchmarker/data/sysbench/default_tpcc_common.lua [124:357]
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="smallint"
local datetime_type="timestamp"
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
print(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 char(2),
w_zip char(9),
w_tax decimal(4,2),
w_ytd decimal(12,2),
primary key (w_id)
) %s %s]],
table_num, engine_def, extra_table_options)
con:query(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 char(2),
d_zip char(9),
d_tax decimal(4,2),
d_ytd decimal(12,2),
d_next_o_id int,
primary key (d_w_id, d_id)
) %s %s]],
table_num, engine_def, extra_table_options)
con:query(query)
query = string.format([[
create table IF NOT EXISTS customer%d (
c_id int not null,
c_d_id ]] .. tinyint_type .. [[ not null,
c_w_id int not null,
c_first varchar(16),
c_middle char(2),
c_last varchar(16),
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 ]] .. datetime_type .. [[,
c_credit char(2),
c_credit_lim bigint,
c_discount decimal(4,2),
c_balance decimal(12,2),
c_ytd_payment decimal(12,2),
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)
con:query(query)
local hist_auto_inc=""
local hist_pk=""
if sysbench.opt.force_pk == 1 then
hist_auto_inc="id int NOT NULL AUTO_INCREMENT,"
hist_pk=",PRIMARY KEY(id)"
end
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(6,2),
h_data varchar(24) %s
) %s %s]],
table_num, hist_auto_inc, hist_pk, engine_def, extra_table_options)
con:query(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)
con:query(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)
con:query(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(6,2),
ol_dist_info char(24),
PRIMARY KEY(ol_w_id, ol_d_id, ol_o_id, ol_number)
) %s %s]],
table_num, engine_def, extra_table_options)
con:query(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 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),
s_ytd decimal(8,0),
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)
con:query(query)
local i = table_num
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(5,2),
i_data varchar(50),
PRIMARY KEY(i_id)
) %s %s]],
i, engine_def, extra_table_options)
con:query(query)
con:bulk_insert_init("INSERT INTO item" .. i .." (i_id, i_im_id, i_name, i_price, i_data) values")
for j = 1 , MAXITEMS do
local i_im_id = sysbench.rand.uniform(1,10000)
local i_price = sysbench.rand.uniform_double()*100+1
local i_name = string.format("item-%d-%f-%s", i_im_id, i_price, sysbench.rand.string("@@@@@"))
local i_data = string.format("data-%s-%s", i_name, sysbench.rand.string("@@@@@"))
query = string.format([[(%d,%d,'%s',%f,'%s')]],
j, i_im_id, i_name:sub(1,24), i_price, i_data:sub(1,50))
con:bulk_insert_next(query)
end
con:bulk_insert_done()
print(string.format("Adding indexes %d ... \n", i))
con:query("CREATE INDEX idx_customer"..i.." ON customer"..i.." (c_w_id,c_d_id,c_last,c_first)")
con:query("CREATE INDEX idx_orders"..i.." ON orders"..i.." (o_w_id,o_d_id,o_c_id,o_id)")
con:query("CREATE INDEX fkey_stock_2"..i.." ON stock"..i.." (s_i_id)")
con:query("CREATE INDEX fkey_order_line_2"..i.." ON order_line"..i.." (ol_supply_w_id,ol_i_id)")
con:query("CREATE INDEX fkey_history_1"..i.." ON history"..i.." (h_c_w_id,h_c_d_id,h_c_id)")
con:query("CREATE INDEX fkey_history_2"..i.." ON history"..i.." (h_w_id,h_d_id )")
if sysbench.opt.use_fk == 1 then
print(string.format("Adding FK %d ... \n", i))
con:query("ALTER TABLE new_orders"..i.." ADD CONSTRAINT fkey_new_orders_1_"..table_num.." FOREIGN KEY(no_w_id,no_d_id,no_o_id) REFERENCES orders"..i.."(o_w_id,o_d_id,o_id)")
con:query("ALTER TABLE orders"..i.." ADD CONSTRAINT fkey_orders_1_"..table_num.." FOREIGN KEY(o_w_id,o_d_id,o_c_id) REFERENCES customer"..i.."(c_w_id,c_d_id,c_id)")
con:query("ALTER TABLE customer"..i.." ADD CONSTRAINT fkey_customer_1_"..table_num.." FOREIGN KEY(c_w_id,c_d_id) REFERENCES district"..i.."(d_w_id,d_id)")
con:query("ALTER TABLE history"..i.." ADD CONSTRAINT fkey_history_1_"..table_num.." FOREIGN KEY(h_c_w_id,h_c_d_id,h_c_id) REFERENCES customer"..i.."(c_w_id,c_d_id,c_id)")
con:query("ALTER TABLE history"..i.." ADD CONSTRAINT fkey_history_2_"..table_num.." FOREIGN KEY(h_w_id,h_d_id) REFERENCES district"..i.."(d_w_id,d_id)")
con:query("ALTER TABLE district"..i.." ADD CONSTRAINT fkey_district_1_"..table_num.." FOREIGN KEY(d_w_id) REFERENCES warehouse"..i.."(w_id)")
con:query("ALTER TABLE order_line"..i.." ADD CONSTRAINT fkey_order_line_1_"..table_num.." FOREIGN KEY(ol_w_id,ol_d_id,ol_o_id) REFERENCES orders"..i.."(o_w_id,o_d_id,o_id)")
con:query("ALTER TABLE order_line"..i.." ADD CONSTRAINT fkey_order_line_2_"..table_num.." FOREIGN KEY(ol_supply_w_id,ol_i_id) REFERENCES stock"..i.."(s_w_id,s_i_id)")
con:query("ALTER TABLE stock"..i.." ADD CONSTRAINT fkey_stock_1_"..table_num.." FOREIGN KEY(s_w_id) REFERENCES warehouse"..i.."(w_id)")
con:query("ALTER TABLE stock"..i.." ADD CONSTRAINT fkey_stock_2_"..table_num.." FOREIGN KEY(s_i_id) REFERENCES item"..i.."(i_id)")
end
end