in perfkitbenchmarker/data/sysbench/spanner_pg_tpcc_common.lua [480:691]
function load_tables(drv, con, tid, warehouse_num)
local id_index_def, id_def
local engine_def = ""
local extra_table_options = ""
local query
con:query("SET STATEMENT_TIMEOUT TO '1200s'")
for table_num = 1, sysbench.opt.tables do
log(string.format("loading tables: %d for warehouse: %d by thread %d\n", table_num, warehouse_num, tid))
con:bulk_insert_init("INSERT INTO warehouse" .. table_num ..
" (w_id, w_name, w_street_1, w_street_2, w_city, w_state, w_zip, w_tax, w_ytd) values")
query = string.format([[(%d, '%s','%s','%s', '%s', '%s', '%s', %f,300000)]],
warehouse_num, sysbench.rand.string("name-@@@@@"), sysbench.rand.string("street1-@@@@@@@@@@"),
sysbench.rand.string("street2-@@@@@@@@@@"), sysbench.rand.string("city-@@@@@@@@@@"),
sysbench.rand.string("@@"),sysbench.rand.string("zip-#####"),sysbench.rand.uniform_double()*0.2 )
con:bulk_insert_next(query)
con:bulk_insert_done()
con:bulk_insert_init("INSERT INTO district" .. table_num ..
" (d_id, d_w_id, d_name, d_street_1, d_street_2, d_city, d_state, d_zip, d_tax, d_ytd, d_next_o_id) values")
for d_id = 1 , DIST_PER_WARE do
query = string.format([[(%d, %d, '%s','%s','%s', '%s', '%s', '%s', %f,30000,3001)]],
d_id, warehouse_num, sysbench.rand.string("name-@@@@@"), sysbench.rand.string("street1-@@@@@@@@@@"),
sysbench.rand.string("street2-@@@@@@@@@@"), sysbench.rand.string("city-@@@@@@@@@@"),
sysbench.rand.string("@@"),sysbench.rand.string("zip-#####"),sysbench.rand.uniform_double()*0.2 )
con:bulk_insert_next(query)
end
con:bulk_insert_done()
assert(CUST_PER_DIST % CUST_PER_QUERY == 0)
num_queries = CUST_PER_DIST / CUST_PER_QUERY
for d_id = 1 , DIST_PER_WARE do
for c_iter = 0, num_queries - 1 do
local buf = sysbench.opt.enable_pg_compat_mode == 1 and {} or {"START BATCH DML;"}
for c_id = 1 + (c_iter * CUST_PER_QUERY), (c_iter + 1) * CUST_PER_QUERY do
local c_last
if c_id <= 1000 then
c_last = Lastname(c_id - 1)
else
c_last = Lastname(NURand(255, 0, 999))
end
query = string.format([[EXECUTE insert_customer]] .. table_num .. [[ (%d, %d, %d, '%s','OE','%s','%s', '%s', '%s', '%s', '%s','%s','%s','%s',50000,%f,-10.0,10.0,1,0,'%s' );]],
c_id,
d_id,
warehouse_num,
sysbench.rand.string("first-"..string.rep("@",sysbench.rand.uniform(2,10))),
c_last,
sysbench.rand.string("street1-@@@@@@@@@@"),
sysbench.rand.string("street2-@@@@@@@@@@"),
sysbench.rand.string("city-@@@@@@@@@@"),
sysbench.rand.string("@@"),
sysbench.rand.string("zip-#####"),
sysbench.rand.string(string.rep("#",16)),
now(),
(sysbench.rand.uniform(1,100) > 10) and "GC" or "BC",
sysbench.rand.uniform_double()*0.5,
string.rep(sysbench.rand.string("@"), sysbench.rand.uniform(300,500))
)
buf[#buf+1] = query
end
if sysbench.opt.enable_pg_compat_mode ~= 1 then
buf[#buf+1] = "RUN BATCH;"
end
con:query(table.concat( buf ))
end
end
assert(CUST_PER_DIST % HISTORY_PER_QUERY == 0)
num_queries = CUST_PER_DIST / HISTORY_PER_QUERY
for d_id = 1 , DIST_PER_WARE do
for c_iter = 0, num_queries - 1 do
local buf = sysbench.opt.enable_pg_compat_mode == 1 and {} or {"START BATCH DML;"}
for c_id = 1 + (c_iter * HISTORY_PER_QUERY), (c_iter + 1) * HISTORY_PER_QUERY do
query = string.format([[EXECUTE insert_history]] .. table_num .. [[ (%d, %d, %d, %d, %d, '%s' );]],
c_id,
d_id,
warehouse_num,
d_id,
warehouse_num,
string.rep(sysbench.rand.string("@"),sysbench.rand.uniform(12,24))
)
buf[#buf+1] = query
end
if sysbench.opt.enable_pg_compat_mode ~= 1 then
buf[#buf+1] = "RUN BATCH;"
end
local final_string = table.concat( buf )
con:query(final_string)
end
end
local tab = {}
for i = 1, 3000 do
tab[i] = i
end
for i = 1, 3000 do
local j = math.random(i, 3000)
tab[i], tab[j] = tab[j], tab[i]
end
assert(CUST_PER_DIST % ORDERS_PER_QUERY == 0)
num_queries = CUST_PER_DIST / ORDERS_PER_QUERY
for d_id = 1 , DIST_PER_WARE do
for o_incr = 0, num_queries - 1 do
local buf = sysbench.opt.enable_pg_compat_mode == 1 and {} or {"START BATCH DML;"}
for o_id = 1 + (o_incr * ORDERS_PER_QUERY) , (1 + o_incr) * ORDERS_PER_QUERY do
query = string.format([[EXECUTE insert_order]] .. table_num .. [[ (%d, %d, %d, %d, '%s', %s, %d, 1 );]],
o_id,
d_id,
warehouse_num,
tab[o_id],
now(),
o_id < 2101 and sysbench.rand.uniform(1,10) or "NULL",
hash(warehouse_num, d_id, o_id) % 11 + 5
)
buf[#buf+1] = query
end
if sysbench.opt.enable_pg_compat_mode ~= 1 then
buf[#buf+1] = "RUN BATCH;"
end
con:query(table.concat( buf ))
end
end
assert(MAXITEMS % STOCK_PER_QUERY == 0)
num_queries = MAXITEMS / STOCK_PER_QUERY
for s_incr = 0, num_queries - 1 do
local buf = sysbench.opt.enable_pg_compat_mode == 1 and {} or {"START BATCH DML;"}
for s_id = 1 + (s_incr * STOCK_PER_QUERY) , (1 + s_incr) * STOCK_PER_QUERY do
query = string.format([[EXECUTE insert_stock]] .. table_num .. [[ (%d, %d, %d,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',0,0,0,'%s');]],
s_id, warehouse_num, sysbench.rand.uniform(10,100),
string.rep(sysbench.rand.string("@"),24),
string.rep(sysbench.rand.string("@"),24),
string.rep(sysbench.rand.string("@"),24),
string.rep(sysbench.rand.string("@"),24),
string.rep(sysbench.rand.string("@"),24),
string.rep(sysbench.rand.string("@"),24),
string.rep(sysbench.rand.string("@"),24),
string.rep(sysbench.rand.string("@"),24),
string.rep(sysbench.rand.string("@"),24),
string.rep(sysbench.rand.string("@"),24),
string.rep(sysbench.rand.string("@"),sysbench.rand.uniform(26,50)))
buf[#buf+1] = query
end
if sysbench.opt.enable_pg_compat_mode ~= 1 then
buf[#buf+1] = "RUN BATCH;"
end
con:query(table.concat( buf ))
end
con:query(string.format("INSERT INTO new_orders%d (no_o_id, no_d_id, no_w_id) SELECT o_id, o_d_id, o_w_id FROM orders%d WHERE o_id>2100 and o_id<=2300 and o_w_id=%d", table_num, table_num, warehouse_num))
con:query(string.format("INSERT INTO new_orders%d (no_o_id, no_d_id, no_w_id) SELECT o_id, o_d_id, o_w_id FROM orders%d WHERE o_id>2300 and o_id<=2500 and o_w_id=%d", table_num, table_num, warehouse_num))
con:query(string.format("INSERT INTO new_orders%d (no_o_id, no_d_id, no_w_id) SELECT o_id, o_d_id, o_w_id FROM orders%d WHERE o_id>2500 and o_id<=2700 and o_w_id=%d", table_num, table_num, warehouse_num))
con:query(string.format("INSERT INTO new_orders%d (no_o_id, no_d_id, no_w_id) SELECT o_id, o_d_id, o_w_id FROM orders%d WHERE o_id>2700 and o_id<=3000 and o_w_id=%d", table_num, table_num, warehouse_num))
assert(CUST_PER_DIST % ORDER_LINE_PER_QUERY == 0)
num_queries = CUST_PER_DIST / ORDER_LINE_PER_QUERY
for d_id = 1 , DIST_PER_WARE do
for o_iter = 0, num_queries - 1 do
local buf = sysbench.opt.enable_pg_compat_mode == 1 and {} or {"START BATCH DML;"}
for o_id = 1 + (o_iter * ORDER_LINE_PER_QUERY), (o_iter + 1) * ORDER_LINE_PER_QUERY do
o_ol_cnt = hash(warehouse_num, d_id, o_id) % 11 + 5
for ol_id = 1, o_ol_cnt do
query = string.format([[EXECUTE insert_order_line]]..table_num..[[ (%d, %d, %d, %d, %d, %d, %s, 5, %f, '%s' );]],
o_id,
d_id,
warehouse_num,
ol_id,
sysbench.rand.uniform(1, MAXITEMS),
warehouse_num,
o_id < 2101 and string.format("'%s'", now()) or "NULL",
o_id < 2101 and 0 or sysbench.rand.uniform_double()*9999.99,
string.rep(sysbench.rand.string("@"),24)
)
buf[#buf+1] = query
end
end
if sysbench.opt.enable_pg_compat_mode ~= 1 then
buf[#buf+1] = "RUN BATCH;"
end
con:query(table.concat( buf ))
end
end
end
end