-- sysbench Lua script for Multi-Level Cascading FK Test
-- in mariadb galera cluster

sysbench.cmdline.options = {
   table_size = {"Number of rows in t1", 5000},
   child_ratio = {"Number of t2 rows per t1, and t3 per t2", 5}
}

function prepare()
   local drv = sysbench.sql.driver()
   local con = drv:connect()

   local t_size = sysbench.opt.table_size
   local c_ratio = sysbench.opt.child_ratio

   print("Cleaning up old tables...")
   con:query("DROP TABLE IF EXISTS t3")
   con:query("DROP TABLE IF EXISTS t2")
   con:query("DROP TABLE IF EXISTS t1")

   print("Creating hierarchy...")
   con:query("CREATE TABLE t1 (id INT NOT NULL, val CHAR(50), PRIMARY KEY (id)) ENGINE=InnoDB")
   con:query([[
      CREATE TABLE t2 (
          id INT NOT NULL AUTO_INCREMENT,
          t1_id INT NOT NULL,
          v_col INT AS (t1_id + 1) VIRTUAL,
          PRIMARY KEY (id),
          KEY (t1_id),
          KEY (v_col),
          CONSTRAINT fk_t1 FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
      ) ENGINE=InnoDB
   ]])
   con:query([[
      CREATE TABLE t3 (
          id INT NOT NULL AUTO_INCREMENT,
          t2_id INT NOT NULL,
          count INT NOT NULL DEFAULT 0,
          PRIMARY KEY (id),
          KEY (t2_id),
          CONSTRAINT fk_t2 FOREIGN KEY (t2_id) REFERENCES t2 (id) ON DELETE CASCADE
      ) ENGINE=InnoDB
   ]])

   print(string.format("Populating hierarchy (%d t1 rows)...", t_size))
   con:query("SET FOREIGN_KEY_CHECKS=0")
   
   for i = 1, t_size do
      con:query(string.format("INSERT INTO t1 (id, val) VALUES (%d, 'p')", i))
      for j = 1, c_ratio do
         con:query(string.format("INSERT INTO t2 (t1_id) VALUES (%d)", i))
         
         -- Correct way to get the ID in sysbench 1.0.x
         local rs = con:query("SELECT LAST_INSERT_ID()")
         local t2_id = rs:fetch_row()[1]
         
         for k = 1, c_ratio do
            con:query(string.format("INSERT INTO t3 (t2_id, count) VALUES (%s, 0)", t2_id))
         end
      end
   end
   
   con:query("SET FOREIGN_KEY_CHECKS=1")
   print("Prepare complete.")
end

function thread_init()
   drv = sysbench.sql.driver()
   con = drv:connect()
end

function event()
   local t_size = sysbench.opt.table_size
   local c_ratio = sysbench.opt.child_ratio
   local id = sysbench.rand.default(1, t_size)

   con:query("BEGIN")

   -- 1. Triple Cascade Trigger
   con:query(string.format("DELETE FROM t1 WHERE id = %d", id))

   -- 2. Restore Grandparent
   con:query(string.format("INSERT INTO t1 (id, val) VALUES (%d, 'p')", id))
   
   for j = 1, c_ratio do
      -- 3. Restore Parent
      con:query(string.format("INSERT INTO t2 (t1_id) VALUES (%d)", id))
      local rs = con:query("SELECT LAST_INSERT_ID()")
      local t2_id = rs:fetch_row()[1]
      
      for k = 1, c_ratio do
         -- 4. Restore Grandchild
         con:query(string.format("INSERT INTO t3 (t2_id, count) VALUES (%s, 0)", t2_id))
      end
   end

   con:query("COMMIT")
end

function cleanup()
    local drv = sysbench.sql.driver()
    local con = drv:connect()
    con:query("DROP TABLE IF EXISTS t3, t2, t1")
    print("Cleanup complete.")
end