--[[------------------------------------------------------------------------

multi-table sysbench workload with UPDATE_NO_KEY and POINT_SELECT_ALL_COLUMNS

prepare:

sysbench-0.5.0  --test=multi_table_rw.lua --oltp-table-size=4000000 --mysql-table-engine=innodb \
                --db-ps-mode=disable --mysql-engine-trx=yes --rand-type=uniform  --tables=8 \
                --mysql-host=<host> --mysql-user=<user> --mysql-port=<port> prepare

run:

sysbench-0.5.0  --test=multi_table_rw.lua --oltp-table-size=4000000 --mysql-table-engine=innodb \
                --db-ps-mode=disable --mysql-engine-trx=yes --rand-type=uniform  --tables=8 \
                --mysql-host=<host> --mysql-user=<user> --mysql-port=<port> \
                --max-requests=0 --max-time=600 --num-threads=<threads> run 

--]]------------------------------------------------------------------------

function prepare()
   local query
   local i

   set_vars()

   db_connect()

   for table=1,tables do

   print([[Creating table 'sbtest]]..table..[['...]])

   if (db_driver == "mysql") then
      query = [[
	    CREATE TABLE sbtest]]..table..[[ (
	      id INTEGER UNSIGNED NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
	      k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
	      c CHAR(120) DEFAULT '' NOT NULL,
              pad CHAR(60) DEFAULT '' NOT NULL,
              PRIMARY KEY (id)
	    ) /*! ENGINE = ]] .. mysql_table_engine .. " MAX_ROWS = " .. myisam_max_rows .. " */"
   else
      print("Unknown database driver: " .. db_driver)
      return 1
   end

   db_query(query)

   db_query("CREATE INDEX k on sbtest"..table.."(k)")

   print("Inserting " .. oltp_table_size .. " records into 'sbtest"..table.."'")
   
   if (oltp_auto_inc) then
      db_bulk_insert_init("INSERT INTO sbtest"..table.."(k, c, pad) VALUES")
   else
      db_bulk_insert_init("INSERT INTO sbtest"..table.."(id, k, c, pad) VALUES")
   end

   for i = 1,oltp_table_size do
      if (oltp_auto_inc) then
	 db_bulk_insert_next("(0, ' ', 'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
      else
	 db_bulk_insert_next("("..i..",0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
      end
   end

   db_bulk_insert_done()
   print ("Done table sbtest"..table)
   end

   return 0
end

function cleanup()
   for table=1,tables do
         
   print("Dropping table 'sbtest"..table.."'...")
   db_query("DROP TABLE sbtest"..table)
   end
end

function thread_init(thread_id)
   set_vars()

   idx=math.fmod(thread_id,tables)+1

   print ("Init thread "..thread_id.. " for table sbtest"..idx)

   point_stmt = db_prepare("SELECT id, k, c, pad from sbtest"..idx.." where id=?")
   point_params = {0}
   db_bind_param(point_stmt, point_params)

   update_nonidx_stmt = db_prepare("UPDATE sbtest"..idx.." SET c=? WHERE id=?")
   update_nonidx_params = {"", 0}
   db_bind_param(update_nonidx_stmt, update_nonidx_params)

end

function event(thread_id)
   local rs
   local i

   point_params[1] = sb_rand(1, oltp_table_size)
   rs = db_execute(point_stmt)
   db_store_results(rs)
   db_free_results(rs)
   
   update_nonidx_params[1] = sb_rand_str([[
###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
   update_nonidx_params[2] = sb_rand(1, oltp_table_size)
   rs = db_execute(update_nonidx_stmt)
   
end

function set_vars()
   oltp_table_size = oltp_table_size or 10000
   oltp_range_size = oltp_range_size or 100
   
   if (oltp_auto_inc == 'off') then
      oltp_auto_inc = false
   else
      oltp_auto_inc = true
   end
end