~drizzle-trunk/drizzle/development

#########################################

# Author:  Serge Kozlov skozlov@mysql.com

# Date:    04/25/2007

# Purpose: Testing Invocation and Invoked

#          Features for Replication.

#########################################

--source include/master-slave.inc

--source include/have_innodb.inc

# --disable_warnings/--enable_warnings added before/after query 

# if one uses UUID() function because we need to avoid warnings

# for STATEMENT binlog format

# Non-transactional engine

--let $engine_type= myisam

# Transactional engine

--let $engine_type2= innodb

#

# Clean up

#

USE test;

--disable_warnings

DROP VIEW IF EXISTS v1,v11;

DROP TABLE IF EXISTS t1,t2,t3,t11,t12,t13;

DROP PROCEDURE IF EXISTS p1;

DROP PROCEDURE IF EXISTS p11;

DROP FUNCTION IF EXISTS f1;

DROP FUNCTION IF EXISTS f2;

DROP EVENT IF EXISTS e1;

DROP EVENT IF EXISTS e11;

--enable_warnings

#

# Prepare objects (tables etc)

#

# Create tables

--echo

eval CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=$engine_type;

INSERT INTO t1 VALUES (1,1,'1');

--disable_warnings

INSERT INTO t1 VALUES (2,2,UUID());

--enable_warnings

eval CREATE TABLE t2 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=$engine_type;

INSERT INTO t2 VALUES (1,1,'1');

--disable_warnings

INSERT INTO t2 VALUES (2,2,UUID());

--enable_warnings

eval CREATE TABLE t11 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=$engine_type2;

INSERT INTO t11 VALUES (1,1,'1');

--disable_warnings

INSERT INTO t11 VALUES (2,2,UUID());

--enable_warnings

eval CREATE TABLE t12 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=$engine_type2;

INSERT INTO t12 VALUES (1,1,'1');

--disable_warnings

INSERT INTO t12 VALUES (2,2,UUID());

--enable_warnings

# Create invoked features

--echo

# Create view for tables t1,t11

CREATE VIEW v1 AS SELECT * FROM t1;

CREATE VIEW v11 AS SELECT * FROM t11;

# Create triggers for t1,t11

DELIMITER |;

CREATE TRIGGER t1_tr1 BEFORE INSERT ON t1 FOR EACH ROW 

BEGIN

  INSERT INTO t2 VALUES (NEW.a, NEW.b, NEW.c);

  INSERT INTO t3 VALUES (NEW.a, NEW.b, NEW.c);

END|

CREATE TRIGGER t1_tr2 BEFORE UPDATE ON t1 FOR EACH ROW 

BEGIN

  UPDATE t2 SET c = '';

  UPDATE t3 SET c = '';

END|

CREATE TRIGGER t11_tr1 BEFORE INSERT ON t11 FOR EACH ROW 

BEGIN

  INSERT INTO t12 VALUES (NEW.a, NEW.b, NEW.c);

  INSERT INTO t13 VALUES (NEW.a, NEW.b, NEW.c);

END|

CREATE TRIGGER t11_tr2 BEFORE UPDATE ON t11 FOR EACH ROW 

BEGIN

  UPDATE t12 SET c = '';

  UPDATE t13 SET c = '';

END|

# Create events which will run every 1 sec

CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO

BEGIN

  ALTER EVENT e1 DISABLE;

  CALL p1(10, '');  

END|

CREATE EVENT e11 ON SCHEDULE EVERY 1 SECOND DISABLE DO

BEGIN

  ALTER EVENT e11 DISABLE;

  CALL p11(10, '');  

END|

# Create functions and procedures used for events

CREATE FUNCTION f1 (x INT) RETURNS VARCHAR(64)

BEGIN

  IF x > 5 THEN

    RETURN UUID();

  END IF;

  RETURN '';

END|

CREATE FUNCTION f2 (x INT) RETURNS VARCHAR(64)

BEGIN

  RETURN f1(x);

END|

CREATE PROCEDURE p1 (IN x INT, IN y VARCHAR(64))

BEGIN

  INSERT IGNORE INTO t1 VALUES (x,x,y);

END|

CREATE PROCEDURE p11 (IN x INT, IN y VARCHAR(64))

BEGIN

  INSERT IGNORE INTO t11 VALUES (x,x,y);

END|

DELIMITER ;|

#

# Start test case

#

# Do some actions for non-transactional tables

--echo

CREATE TABLE t3 SELECT * FROM v1;

INSERT INTO t1 VALUES (3,3,'');

UPDATE t1 SET c='2' WHERE a = 1;

--disable_warnings

INSERT INTO t1 VALUES(4,4,f1(4));

--enable_warnings

INSERT INTO t1 VALUES (100,100,'');

--disable_warnings

CALL p1(5, UUID());

--enable_warnings

INSERT INTO t1 VALUES (101,101,'');

--disable_warnings

INSERT INTO t1 VALUES(6,6,f1(6));

--enable_warnings

INSERT INTO t1 VALUES (102,102,'');

--disable_warnings

INSERT INTO t1 VALUES(7,7,f2(7));

--enable_warnings

INSERT INTO t1 VALUES (103,103,'');

# Do some actions for transactional tables

--echo

CREATE TABLE t13 SELECT * FROM v11;

INSERT INTO t11 VALUES (3,3,'');

UPDATE t11 SET c='2' WHERE a = 1;

--disable_warnings

INSERT INTO t11 VALUES(4,4,f1(4));

--enable_warnings

INSERT INTO t11 VALUES (100,100,'');

--disable_warnings

CALL p11(5, UUID());

--enable_warnings

INSERT INTO t11 VALUES (101,101,'');

--disable_warnings

INSERT INTO t11 VALUES(6,6,f1(6));

--enable_warnings

INSERT INTO t11 VALUES (102,102,'');

--disable_warnings

INSERT INTO t11 VALUES(7,7,f2(7));

--enable_warnings

INSERT INTO t11 VALUES (103,103,'');

# Scheduler is on

--echo

# Temporally events fire sequentally due Bug#29020.

SET GLOBAL EVENT_SCHEDULER = on;

# Wait while events will executed

ALTER EVENT e1 ENABLE;

let $wait_condition= SELECT COUNT(*) = 1 FROM t1 WHERE t1.a = 10;

--source include/wait_condition.inc

ALTER EVENT e11 ENABLE;

let $wait_condition= SELECT COUNT(*) = 1 FROM t11 WHERE t11.a = 10;

--source include/wait_condition.inc

SET GLOBAL EVENT_SCHEDULER = off;

# Check original objects

--echo

--sorted_result

SHOW TABLES LIKE 't%';

--sorted_result

SELECT table_name FROM information_schema.views WHERE table_schema='test';

--sorted_result

SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';

--sorted_result

SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';

--sorted_result

SELECT event_name, status FROM information_schema.events WHERE event_schema='test';

# Check original data

--echo

SELECT COUNT(*) FROM t1;

SELECT a,b FROM t1 ORDER BY a;

SELECT COUNT(*) FROM t2;

SELECT a,b FROM t2 ORDER BY a;

SELECT COUNT(*) FROM t3;

SELECT a,b FROM t3 ORDER BY a;

SELECT a,b FROM v1 ORDER BY a;

SELECT COUNT(*) FROM t11;

SELECT a,b FROM t11 ORDER BY a;

SELECT COUNT(*) FROM t12;

SELECT a,b FROM t12 ORDER BY a;

SELECT COUNT(*) FROM t13;

SELECT a,b FROM t13 ORDER BY a;

SELECT a,b FROM v11 ORDER BY a;

--sync_slave_with_master slave

# Check replicated objects

--echo

--sorted_result

SHOW TABLES LIKE 't%';

--sorted_result

SELECT table_name FROM information_schema.views WHERE table_schema='test';

--sorted_result

SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';

--sorted_result

SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';

--sorted_result

SELECT event_name, status FROM information_schema.events WHERE event_schema='test';

# Check replicated data

--echo

SELECT COUNT(*) FROM t1;

SELECT a,b FROM t1 ORDER BY a;

SELECT COUNT(*) FROM t2;

SELECT a,b FROM t2 ORDER BY a;

SELECT COUNT(*) FROM t3;

SELECT a,b FROM t3 ORDER BY a;

SELECT a,b FROM v1 ORDER BY a;

SELECT COUNT(*) FROM t11;

SELECT a,b FROM t11 ORDER BY a;

SELECT COUNT(*) FROM t12;

SELECT a,b FROM t12 ORDER BY a;

SELECT COUNT(*) FROM t13;

SELECT a,b FROM t13 ORDER BY a;

SELECT a,b FROM v11 ORDER BY a;

# Remove UUID() before comparing and sort tables

--connection master

--echo

UPDATE t1 SET c='';

UPDATE t2 SET c='';

UPDATE t3 SET c='';

UPDATE t11 SET c='';

UPDATE t12 SET c='';

UPDATE t13 SET c='';

ALTER TABLE t3 ORDER BY a;

ALTER TABLE t13 ORDER BY a;

--sync_slave_with_master slave

# Compare a data from master and slave

--echo

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

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

--diff_files $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_master.sql $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_slave.sql

#

# Clean up

#

# Remove dumps

--echo

--remove_file $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_master.sql

--remove_file $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_slave.sql

# Remove tables,views,procedures,functions

--connection master

--echo

DROP VIEW IF EXISTS v1,v11;

DROP TABLE IF EXISTS t1,t2,t3,t11,t12,t13;

DROP PROCEDURE IF EXISTS p1;

DROP PROCEDURE IF EXISTS p11;

DROP FUNCTION IF EXISTS f1;

DROP FUNCTION IF EXISTS f2;

DROP EVENT IF EXISTS e1;

DROP EVENT IF EXISTS e11;

--sync_slave_with_master slave

# End 5.1 test case