#######################################
# Author: JBM                         #
# Date: 2006-02-23                    #
# Purpose: See if replication between #
# NDB -> MyISAM and InnoDB works.     #
# and if                              #   
# MyISAM and InnoDB -> NDB works.     #
#######################################
# By JBM                              #
# Date 2006-02-28                     #
# Change: Implemented review comments #
#######################################

--echo --- Doing pre test cleanup --- 

connection master;
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_query_log

--echo
--echo --- Start test 1 Basic testing ---
--echo --- Create Table Section ---
--echo

#################################################
# Requirment: Create basic table, replicate     #
# basic operations such at insert, update       #
# delete between 2 different storage engines    #
# Alter table and ensure table is handled       #
# Correctly on the slave                        #
#################################################

CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255), 
                 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, 
                 f FLOAT DEFAULT 0, total BIGINT UNSIGNED, 
                 y YEAR, t DATE,PRIMARY KEY(id));

--echo
--echo --- Show table on master ---
--echo

SHOW CREATE TABLE t1;

--echo
--echo --- Show table on slave ---
--echo

sync_slave_with_master;
SHOW CREATE TABLE t1;

--echo
--echo --- Perform basic operation on master ---
--echo --- and ensure replicated correctly ---
--echo

--source include/rpl_multi_engine3.inc

--echo
--echo

# Okay lets see how it holds up to table changes
--echo --- Check that simple Alter statements are replicated correctly --
--echo

ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
ALTER TABLE t1 MODIFY vc TEXT;

--echo
--echo --- Show the new improved table on the master ---

SHOW CREATE TABLE t1;

--echo
--echo --- Make sure that our tables on slave are still same engine ---
--echo --- and that the alter statements replicated correctly ---
--echo

sync_slave_with_master;
SHOW CREATE TABLE t1;

--echo
--echo --- Perform basic operation on master ---
--echo --- and ensure replicated correctly --- 
--echo

--source include/rpl_multi_engine3.inc

--echo
--echo --- End test 1 Basic testing ---
--echo --- Do Cleanup --
--echo

DROP TABLE IF EXISTS t1;


########################################################
--echo
--echo --- Start test 2 partition by key testing ---
--echo --- Create Table Section ---
--echo
#################################################
# Requirment: Create table that is partitioned  #
# by key on id with 4 parts.    Then replicate  #
# basic operations such at insert, update       #
# delete between 2 different storage engines    #
# Alter table and ensure table is handled       #
# Correctly on the slave                        #
#################################################

CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255), 
                 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, 
                 f FLOAT DEFAULT 0, total BIGINT UNSIGNED, 
                 y YEAR, t DATE,PRIMARY KEY(id))
                 PARTITION BY KEY() 
                 PARTITIONS 4;

--echo
--echo --- Show that tables on master are ndbcluster tables ---
--echo

SHOW CREATE TABLE t1;

--echo
--echo --- Show that tables on slave ---
--echo

sync_slave_with_master;
SHOW CREATE TABLE t1;

--echo
--echo --- Perform basic operation on master ---
--echo --- and ensure replicated correctly ---
--echo

--source include/rpl_multi_engine3.inc

# Okay lets see how it holds up to table changes
--echo
--echo --- Check that simple Alter statements are replicated correctly ---
--echo

ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);

--echo
--echo --- Show the new improved table on the master ---
--echo

SHOW CREATE TABLE t1;

--echo
--echo --- Make sure that our tables on slave are still right type ---
--echo --- and that the alter statements replicated correctly ---
--echo

sync_slave_with_master;
SHOW CREATE TABLE t1;

--echo
--echo --- Perform basic operation on master ---
--echo --- and ensure replicated correctly ---
--echo

--source include/rpl_multi_engine3.inc

--echo
--echo --- Check that simple Alter statements are replicated correctly ---
--echo

ALTER TABLE t1 MODIFY vc TEXT;

--echo
--echo --- Show the new improved table on the master ---
--echo

SHOW CREATE TABLE t1;

--echo
--echo --- Make sure that our tables on slave are still same engine ---
--echo --- and that the alter statements replicated correctly ---
--echo

sync_slave_with_master;
SHOW CREATE TABLE t1;

--echo
--echo --- Perform basic operation on master ---
--echo --- and ensure replicated correctly ---
--echo

--source include/rpl_multi_engine3.inc

--echo
--echo --- End test 2 key partition testing ---
--echo --- Do Cleanup ---
--echo

DROP TABLE IF EXISTS t1;

########################################################
--echo
--echo --- Start test 3 Transaction testing ---
--echo --- Create Table Section ---
--echo
#################################################
# Requirment: Create stored procedures and run  #
# tranactions with roll backs                   #
#################################################
CREATE DATABASE tpcb;

--echo
CREATE TABLE tpcb.account (id INT, bid INT, balance DECIMAL(10,2),
                           filler CHAR(255), PRIMARY KEY(id));
--echo
CREATE TABLE tpcb.branch (bid INT, balance DECIMAL(10,2), filler VARCHAR(255),
                          PRIMARY KEY(bid));
--echo
CREATE TABLE tpcb.teller (tid INT, balance DECIMAL(10,2), filler VARCHAR(255),
                          PRIMARY KEY(tid));
--echo
CREATE TABLE tpcb.history (id MEDIUMINT NOT NULL AUTO_INCREMENT,aid INT,
                           tid INT, bid INT,  amount DECIMAL(10,2),
                           tdate DATETIME, teller CHAR(20), uuidf LONGBLOB,
                           filler CHAR(80),PRIMARY KEY (id));

--echo 
--echo --- Create stored procedures & functions ---
--echo --- Load DB & run transaction ---
--echo

--disable_query_log
delimiter |;
CREATE PROCEDURE tpcb.load()
BEGIN
  DECLARE acct INT DEFAULT 1000;
  DECLARE brch INT DEFAULT 10;
  DECLARE tell INT DEFAULT 1000;
  DECLARE tmp INT DEFAULT 100;
  WHILE brch > 0 DO
    SET tmp = 100;
    WHILE tmp > 0 DO
     INSERT INTO tpcb.account VALUES (acct, brch, 0.0, "FRESH ACCOUNT");
     SET acct = acct - 1;
     SET tmp = tmp -1;
    END WHILE;
    INSERT INTO tpcb.branch VALUES (brch, 0.0, "FRESH BRANCH");
    SET brch = brch - 1;
  END WHILE;
  WHILE tell > 0 DO
   INSERT INTO tpcb.teller VALUES (tell, 0.0, "FRESH TELLER");
   SET tell = tell - 1;
  END WHILE;
END|

CREATE FUNCTION tpcb.account_id () RETURNS INT
BEGIN
  DECLARE num INT;
  DECLARE ran INT;
  SELECT RAND() * 10 INTO ran;
  IF (ran < 5)
   THEN
     SELECT RAND() * 10 INTO num;
   ELSE
     SELECT RAND() * 100 INTO num;
   END IF;
   IF (num < 1)
    THEN
     RETURN 1;
   END IF;
  RETURN  num;
END|

CREATE FUNCTION tpcb.teller_id () RETURNS INT
BEGIN
  DECLARE num INT;
  DECLARE ran INT;
  SELECT RAND() * 10 INTO ran;
  IF (ran < 5)
   THEN
     SELECT RAND() * 10 INTO num;
   ELSE
     SELECT RAND() * 100 INTO num;
   END IF;
   IF (num < 1)
    THEN
      RETURN 1;
   END IF;
   RETURN  num;
END|

CREATE PROCEDURE tpcb.trans()
BEGIN
  DECLARE acct INT DEFAULT 0;
  DECLARE brch INT DEFAULT 0;
  DECLARE tell INT DEFAULT 0;
  DECLARE bal  DECIMAL(10,2) DEFAULT 0.0;
  DECLARE amount DECIMAL(10,2) DEFAULT 1.00;
  DECLARE test INT DEFAULT 0;
  DECLARE bbal DECIMAL(10,2) DEFAULT 0.0;
  DECLARE tbal DECIMAL(10,2) DEFAULT 0.0;
  DECLARE local_uuid VARCHAR(255);
  DECLARE local_user VARCHAR(255);
  DECLARE local_time TIMESTAMP;

  SELECT RAND() * 10 INTO test;
  SELECT tpcb.account_id() INTO acct;
  SELECT tpcb.teller_id() INTO tell;

  SELECT account.balance INTO bal FROM tpcb.account WHERE id = acct;
  SELECT account.bid INTO brch FROM tpcb.account WHERE id = acct;
  SELECT teller.balance INTO tbal FROM tpcb.teller WHERE tid = tell;
  SELECT branch.balance INTO bbal FROM tpcb.branch WHERE bid = brch;

  IF (test < 5)
   THEN
     SET bal = bal + amount;
     SET bbal = bbal + amount;
     SET tbal = tbal + amount;
     UPDATE tpcb.account SET balance = bal, filler = 'account updated'
     WHERE id = acct;
     UPDATE tpcb.branch SET balance = bbal, filler = 'branch updated'
     WHERE bid = brch;
     UPDATE tpcb.teller SET balance = tbal, filler = 'teller updated'
     WHERE tid = tell;
   ELSE
     SET bal = bal - amount;
     SET bbal = bbal - amount;
     SET tbal = tbal - amount;
     UPDATE tpcb.account SET balance = bal, filler = 'account updated'
     WHERE id = acct;
     UPDATE tpcb.branch SET balance = bbal, filler = 'branch updated'
     WHERE bid = brch;
     UPDATE tpcb.teller SET balance = tbal, filler = 'teller updated'
     WHERE tid = tell;
  END IF;

  IF (@RPL_SYS_MODE = 'SBR')
  THEN
    SET local_uuid=UUID();
    SET local_user=USER();
    SET local_time= NOW();
    INSERT INTO tpcb.history VALUES(NULL,acct,tell,brch,amount, local_time,local_user,
                             local_uuid,'completed trans');
  ELSE
    INSERT INTO tpcb.history VALUES(NULL,acct,tell,brch,amount, NOW(), USER(),
                             UUID(),'completed trans');
  END IF;

END|
delimiter ;|

CALL tpcb.load();
SET AUTOCOMMIT=0;
let $run= 50;
while ($run)
{
 START TRANSACTION;
 CALL tpcb.trans();
 eval SET @my_errno= $mysql_errno;
 let $run_good= `SELECT @my_errno = 0`;
 let $run_bad= `SELECT @my_errno <> 0`;
 if ($run_good)
 {
   COMMIT;
 }
 if ($run_bad)
 {
   ROLLBACK;
 }
 dec $run;
}
SET AUTOCOMMIT=1;
--enable_query_log

sync_slave_with_master;
connection master;

--echo
--echo --- Dump Master & Slave ---
--echo


--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info tpcb > $MYSQLTEST_VARDIR/tmp/master_tpcb.sql

--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info tpcb > $MYSQLTEST_VARDIR/tmp/slave_tpcb.sql

--echo
--echo --- Make sure Master and Slave data matches (DIFF) ---
--echo

--exec diff $MYSQLTEST_VARDIR/tmp/master_tpcb.sql $MYSQLTEST_VARDIR/tmp/slave_tpcb.sql

--echo
DROP DATABASE tpcb;

--echo
--echo --- End test 3 Transaction testing ---

# End of 5.1 test case