--echo #
--echo # Bug#9051 - Stored Functions: Database name in fully qualified name
--echo #            is case sensitive.
--echo #
--disable_warnings
DROP DATABASE IF EXISTS bup_xpfm_compat_db1;
DROP DATABASE IF EXISTS BUP_XPFM_COMPAT_DB2;
DROP DATABASE IF EXISTS BupXPfmCompat_db3;
--enable_warnings
#
--echo #
--echo # Check setting of lower_case_table_names.
--echo #
SELECT @@lower_case_table_names;
#
--echo #
--echo # Create some databases.
--echo #
CREATE DATABASE bup_xpfm_compat_db1;
CREATE DATABASE BUP_XPFM_COMPAT_DB2;
CREATE DATABASE BupXPfmCompat_db3;
#
--echo #
--echo # Create stored procedures.
--echo #
delimiter |;
#
CREATE PROCEDURE bup_xpfm_compat_db1.proc1()
BEGIN
  SELECT 'bup_xpfm_compat_db1.proc1';
END;
|
CREATE PROCEDURE bup_xpfm_compat_db1.PROC2()
BEGIN
  SELECT 'bup_xpfm_compat_db1.PROC2';
END;
|
CREATE PROCEDURE bup_xpfm_compat_db1.PrOc3()
BEGIN
  SELECT 'bup_xpfm_compat_db1.PrOc3';
END;
|
CREATE PROCEDURE BUP_XPFM_COMPAT_DB2.proc1()
BEGIN
  SELECT 'BUP_XPFM_COMPAT_DB2.proc1';
END;
|
CREATE PROCEDURE BUP_XPFM_COMPAT_DB2.PROC2()
BEGIN
  SELECT 'BUP_XPFM_COMPAT_DB2.PROC2';
END;
|
CREATE PROCEDURE BUP_XPFM_COMPAT_DB2.PrOc3()
BEGIN
  SELECT 'BUP_XPFM_COMPAT_DB2.PrOc3';
END;
|
CREATE PROCEDURE BupXPfmCompat_db3.proc1()
BEGIN
  SELECT 'BupXPfmCompat_db3.proc1';
END;
|
CREATE PROCEDURE BupXPfmCompat_db3.PROC2()
BEGIN
  SELECT 'BupXPfmCompat_db3.PROC2';
END;
|
CREATE PROCEDURE BupXPfmCompat_db3.PrOc3()
BEGIN
  SELECT 'BupXPfmCompat_db3.PrOc3';
END;
|
delimiter ;|
#
--echo #
--echo # Create stored functions.
--echo #
delimiter |;
#
CREATE FUNCTION bup_xpfm_compat_db1.func1(arg1 INT) RETURNS INTEGER
BEGIN
  RETURN arg1 * 11;
END;
|
CREATE FUNCTION bup_xpfm_compat_db1.FUNC2(arg1 INT) RETURNS INTEGER
BEGIN
  RETURN arg1 * 12;
END;
|
CREATE FUNCTION bup_xpfm_compat_db1.FuNc3(arg1 INT) RETURNS INTEGER
BEGIN
  RETURN arg1 * 13;
END;
|
CREATE FUNCTION BUP_XPFM_COMPAT_DB2.func1(arg1 INT) RETURNS INTEGER
BEGIN
  RETURN arg1 * 21;
END;
|
CREATE FUNCTION BUP_XPFM_COMPAT_DB2.FUNC2(arg1 INT) RETURNS INTEGER
BEGIN
  RETURN arg1 * 22;
END;
|
CREATE FUNCTION BUP_XPFM_COMPAT_DB2.FuNc3(arg1 INT) RETURNS INTEGER
BEGIN
  RETURN arg1 * 23;
END;
|
CREATE FUNCTION BupXPfmCompat_db3.func1(arg1 INT) RETURNS INTEGER
BEGIN
  RETURN arg1 * 31;
END;
|
CREATE FUNCTION BupXPfmCompat_db3.FUNC2(arg1 INT) RETURNS INTEGER
BEGIN
  RETURN arg1 * 32;
END;
|
CREATE FUNCTION BupXPfmCompat_db3.FuNc3(arg1 INT) RETURNS INTEGER
BEGIN
  RETURN arg1 * 33;
END;
|
delimiter ;|
#
--echo #
--echo # Show stored procedures.
--echo #
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE ROUTINE_SCHEMA like 'bup%xpfm%compat%'
    AND ROUTINE_TYPE = 'PROCEDURE'
  ORDER BY LOWER(ROUTINE_SCHEMA), LOWER(ROUTINE_NAME);
#
--echo #
--echo # Show stored functions.
--echo #
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE ROUTINE_SCHEMA like 'bup%xpfm%compat%'
    AND ROUTINE_TYPE = 'FUNCTION'
  ORDER BY LOWER(ROUTINE_SCHEMA), LOWER(ROUTINE_NAME);
#
--echo #
--echo # NOTE: --disable_abort_on_error here !
--echo # NOTE: --disable_abort_on_error here !
--echo # NOTE: --disable_abort_on_error here !
--echo #
--disable_abort_on_error
#
--echo #
--echo # Execute procedures. They show their names.
--echo #
CALL bup_xpfm_compat_db1.proc1;
CALL bup_xpfm_compat_db1.PROC2;
CALL bup_xpfm_compat_db1.PrOc3;
CALL BUP_XPFM_COMPAT_DB2.proc1;
CALL BUP_XPFM_COMPAT_DB2.PROC2;
CALL BUP_XPFM_COMPAT_DB2.PrOc3;
CALL BupXPfmCompat_db3.proc1;
CALL BupXPfmCompat_db3.PROC2;
CALL BupXPfmCompat_db3.PrOc3;
#
--echo #
--echo # Execute functions. They multiply their arg by 11, 12, 13, ..., 32, 33.
--echo #
SELECT bup_xpfm_compat_db1.func1(2);
SELECT bup_xpfm_compat_db1.FUNC2(3);
SELECT bup_xpfm_compat_db1.FuNc3(4);
SELECT BUP_XPFM_COMPAT_DB2.func1(5);
SELECT BUP_XPFM_COMPAT_DB2.FUNC2(6);
SELECT BUP_XPFM_COMPAT_DB2.FuNc3(7);
SELECT BupXPfmCompat_db3.func1(8);
SELECT BupXPfmCompat_db3.FUNC2(9);
SELECT BupXPfmCompat_db3.FuNc3(10);
--echo #
--echo # Cleanup
--echo #
DROP DATABASE bup_xpfm_compat_db1;
DROP DATABASE BUP_XPFM_COMPAT_DB2;
DROP DATABASE BupXPfmCompat_db3;