#----------------------------------------------------------
# Concurrency  check for instrumentation of stored programs 
#----------------------------------------------------------


--source include/big_test.inc
--source include/not_embedded.inc
--source include/have_perfschema.inc
--source include/no_protocol.inc

TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
TRUNCATE TABLE performance_schema.events_statements_history;

--echo # concurrency check through multi connections

# Save the initial number of concurrent sessions
--source include/count_sessions.inc

CREATE DATABASE db1;
CREATE DATABASE db2;
CREATE DATABASE db3;

# connection 1
connect (con1,localhost,root,,db1);

USE db1;

CREATE TABLE t1(
   i INT NOT NULL
);

LOAD DATA INFILE '../../std_data/wl5766_data.txt' INTO TABLE t1;

DELIMITER |;
CREATE PROCEDURE proc()
BEGIN
  INSERT INTO t1 SELECT * FROM t1;
END|
DELIMITER ;|

--send CALL proc();

# connection 2
connect (con2,localhost,root,,db2);

USE db2;

CREATE TABLE t2(
   i INT NOT NULL
);

LOAD DATA INFILE '../../std_data/wl5766_data.txt' INTO TABLE t2;
DELIMITER |;
CREATE FUNCTION addition(x INT, y INT) RETURNS INT
BEGIN
  INSERT INTO t2 SELECT * FROM t2;
  RETURN x+y;
END|
DELIMITER ;|

--send SELECT addition(1234,9876);

# connection 3
connect (con3,localhost,root,,db3);

USE db3;

CREATE TABLE t(
   i INT NOT NULL,
   j INT
);

CREATE TABLE t3(
   i INT NOT NULL
);

LOAD DATA INFILE '../../std_data/wl5766_data.txt' INTO TABLE t3;
INSERT INTO t VALUES ( 10,1000 );
CREATE TRIGGER trg AFTER INSERT ON t FOR EACH ROW
  INSERT INTO t3 SELECT * FROM t3;  

--send INSERT INTO t VALUES ( 20,2000);

connection con1;
--reap;

connection con2;
--reap;

connection con3;
--reap;

connection default;
--source include/wait_until_connected_again.inc

SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
       FROM performance_schema.events_statements_summary_by_program
       WHERE OBJECT_SCHEMA='db1' OR OBJECT_SCHEMA='db2' OR OBJECT_SCHEMA='db3'
       ORDER BY OBJECT_SCHEMA, OBJECT_NAME;

SELECT EVENT_NAME, SQL_TEXT, CURRENT_SCHEMA, OBJECT_TYPE, OBJECT_SCHEMA,
       OBJECT_NAME, NESTING_EVENT_TYPE, NESTING_EVENT_LEVEL FROM
       performance_schema.events_statements_history WHERE CURRENT_SCHEMA='db1'
       OR CURRENT_SCHEMA='db2' OR CURRENT_SCHEMA='db3' 
       ORDER BY CURRENT_SCHEMA, OBJECT_NAME;

TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
TRUNCATE TABLE performance_schema.events_statements_history;

--echo # Clean-up

DROP PROCEDURE db1.proc;
DROP FUNCTION db2.addition;
DROP TRIGGER db3.trg;

DROP TABLE db1.t1;
DROP TABLE db2.t2;
DROP TABLE db3.t3;
DROP TABLE db3.t;

DROP DATABASE db1;
DROP DATABASE db2;
DROP DATABASE db3;

disconnect con1;
disconnect con2;
disconnect con3;

# Wait till all disconnects are completed
--source include/wait_until_count_sessions.inc