~drizzle-trunk/drizzle/development

# Objects to test:

#   - stored procedures/functions;

#   - triggers;

#   - events;

#   - views;

#

# For stored routines:

#   - create a database with collation utf8_unicode_ci;

#   - create an object, which

#     - contains SP-var with explicit CHARSET-clause;

#     - contains SP-var without CHARSET-clause;

#     - contains text constant;

#     - has localized routine/parameter names;

#   - check:

#     - execute;

#     - SHOW CREATE output;

#     - SHOW output;

#     - SELECT FROM INFORMATION_SCHEMA output;

#   - alter database character set;

#   - change connection collation;

#   - check again;

#   - dump definition using mysqldump;

#   - drop object;

#   - restore object;

# 

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

#

# NOTE: this file contains text in UTF8 and KOI8-R encodings.

#

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

--source include/have_utf8.inc

--source include/have_cp866.inc

--source include/have_cp1251.inc

--source include/have_koi8r.inc

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

set names koi8r;

delimiter |;

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

#

# * Views.

#

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

--echo

--echo -------------------------------------------------------------------

--echo Views

--echo -------------------------------------------------------------------

--echo

#

# Preparation:

#

#   - Create database with fixed, pre-defined character set.

--disable_warnings

DROP DATABASE IF EXISTS mysqltest1|

--enable_warnings

CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

use mysqltest1|

CREATE TABLE t1(ËÏÌ INT)|

INSERT INTO t1 VALUES(1)|

#   - Create views;

--echo

CREATE VIEW v1 AS

  SELECT 'ÔÅÓÔ' AS c1, ËÏÌ AS c2

  FROM t1|

--echo

CREATE VIEW v2 AS SELECT _utf8'тест' as c1|

--echo

CREATE VIEW v3 AS SELECT _utf8'тест'|

--echo

#

# First-round checks.

#

--source include/ddl_i18n.check_views.inc

#

# Change running environment (alter database character set, change session

# variables).

#

--echo

--echo

ALTER DATABASE mysqltest1 COLLATE cp866_general_ci|

#

# Second-round checks:

#

#   - Change connection to flush cache;

--connect (con2,localhost,root,,)

--echo

--echo ---> connection: con2

#   - Switch environment variables and trigger loading views;

SET @@character_set_client= cp1251|

SET @@character_set_results= cp1251|

SET @@collation_connection= cp1251_general_ci|

--disable_result_log

SELECT * FROM mysqltest1.v1|

SELECT * FROM mysqltest1.v2|

SELECT * FROM mysqltest1.v3|

--enable_result_log

use mysqltest1|

#   - Restore environment;

set names koi8r|

#   - Check!

--source include/ddl_i18n.check_views.inc

#

# Check mysqldump.

#

#  - Dump mysqltest1;

--echo

--echo ---> Dumping mysqltest1 to ddl_i18n_koi8r.views.mysqltest1.sql

--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.views.mysqltest1.sql

#   - Clean mysqltest1;

--echo

--echo

DROP DATABASE mysqltest1|

#   - Restore mysqltest1;

--echo

--echo

--echo ---> Restoring mysqltest1...

--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.views.mysqltest1.sql

#

# Third-round checks.

#

#   - Change connection to flush cache;

--connect (con3,localhost,root,,)

--echo

--echo ---> connection: con3

#   - Switch environment variables and trigger loading views;

SET @@character_set_client= cp1251|

SET @@character_set_results= cp1251|

SET @@collation_connection= cp1251_general_ci|

--disable_result_log

SELECT * FROM mysqltest1.v1|

SELECT * FROM mysqltest1.v2|

SELECT * FROM mysqltest1.v3|

--enable_result_log

use mysqltest1|

#   - Restore environment;

set names koi8r|

#   - Check!

--source include/ddl_i18n.check_views.inc

#

# Cleanup.

#

--connection default

--echo

--echo ---> connection: default

--disconnect con2

--disconnect con3

use test|

DROP DATABASE mysqltest1|

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

#

# * Stored procedures/functions.

#

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

--echo

--echo -------------------------------------------------------------------

--echo Stored procedures/functions

--echo -------------------------------------------------------------------

--echo

#

# Preparation:

#

#   - Create database with fixed, pre-defined character set.

--disable_warnings

DROP DATABASE IF EXISTS mysqltest1|

DROP DATABASE IF EXISTS mysqltest2|

--enable_warnings

CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

use mysqltest1|

#   - Create two stored routines -- with and without explicit

#     CHARSET-clause for SP-variable;

#

--echo

#     - Procedure p1

CREATE PROCEDURE p1(

  INOUT ÐÁÒÁÍ1 CHAR(10),

  OUT ÐÁÒÁÍ2 CHAR(10))

BEGIN

  DECLARE ÐÅÒÅÍ1 CHAR(10);

  SELECT

    COLLATION(ÐÅÒÅÍ1) AS c1,

    COLLATION(ÐÁÒÁÍ1) AS c2,

    COLLATION(ÐÁÒÁÍ2) AS c3;

  SELECT

    COLLATION('ÔÅËÓÔ') AS c4,

    COLLATION(_koi8r    'ÔÅËÓÔ') AS c5,

    COLLATION(_utf8 'текст') AS c6,

    @@collation_connection AS c7,

    @@character_set_client AS c8;

  SET ÐÁÒÁÍ1 = 'a';

  SET ÐÁÒÁÍ2 = 'b';

END|

--echo

#     - Procedure p2

CREATE PROCEDURE p2(

  INOUT ÐÁÒÁÍ1 CHAR(10) CHARACTER SET utf8,

  OUT ÐÁÒÁÍ2 CHAR(10) CHARACTER SET utf8)

BEGIN

  DECLARE ÐÅÒÅÍ1 CHAR(10) CHARACTER SET utf8;

  SELECT

    COLLATION(ÐÅÒÅÍ1) AS c1,

    COLLATION(ÐÁÒÁÍ1) AS c2,

    COLLATION(ÐÁÒÁÍ2) AS c3;

  SELECT

    COLLATION('ÔÅËÓÔ') AS c4,

    COLLATION(_koi8r    'ÔÅËÓÔ') AS c5,

    COLLATION(_utf8 'текст') AS c6,

    @@collation_connection AS c7,

    @@character_set_client AS c8;

  SET ÐÁÒÁÍ1 = 'a';

  SET ÐÁÒÁÍ2 = 'b';

END|

--echo

#     - Procedure p3

CREATE PROCEDURE mysqltest2.p3(

  INOUT ÐÁÒÁÍ1 CHAR(10),

  OUT ÐÁÒÁÍ2 CHAR(10))

BEGIN

  DECLARE ÐÅÒÅÍ1 CHAR(10);

  SELECT

    COLLATION(ÐÅÒÅÍ1) AS c1,

    COLLATION(ÐÁÒÁÍ1) AS c2,

    COLLATION(ÐÁÒÁÍ2) AS c3;

  SELECT

    COLLATION('ÔÅËÓÔ') AS c4,

    COLLATION(_koi8r    'ÔÅËÓÔ') AS c5,

    COLLATION(_utf8 'текст') AS c6,

    @@collation_connection AS c7,

    @@character_set_client AS c8;

  SET ÐÁÒÁÍ1 = 'a';

  SET ÐÁÒÁÍ2 = 'b';

END|

--echo

#     - Procedure p4

CREATE PROCEDURE mysqltest2.p4(

  INOUT ÐÁÒÁÍ1 CHAR(10) CHARACTER SET utf8,

  OUT ÐÁÒÁÍ2 CHAR(10) CHARACTER SET utf8)

BEGIN

  DECLARE ÐÅÒÅÍ1 CHAR(10) CHARACTER SET utf8;

  SELECT

    COLLATION(ÐÅÒÅÍ1) AS c1,

    COLLATION(ÐÁÒÁÍ1) AS c2,

    COLLATION(ÐÁÒÁÍ2) AS c3;

  SELECT

    COLLATION('ÔÅËÓÔ') AS c4,

    COLLATION(_koi8r    'ÔÅËÓÔ') AS c5,

    COLLATION(_utf8 'текст') AS c6,

    @@collation_connection AS c7,

    @@character_set_client AS c8;

  SET ÐÁÒÁÍ1 = 'a';

  SET ÐÁÒÁÍ2 = 'b';

END|

#

# First-round checks.

#

--source include/ddl_i18n.check_sp.inc

#

# Change running environment (alter database character set, change session

# variables).

#

--echo

--echo

ALTER DATABASE mysqltest1 COLLATE cp866_general_ci|

ALTER DATABASE mysqltest2 COLLATE cp866_general_ci|

#

# Second-round checks:

#

#   - Change connection to flush SP-cache;

--connect (con2,localhost,root,,mysqltest1)

--echo

--echo ---> connection: con2

#   - Switch environment variables and trigger loading stored procedures;

SET @@character_set_client= cp1251|

SET @@character_set_results= cp1251|

SET @@collation_connection= cp1251_general_ci|

CALL p1(@a, @b)|

CALL p2(@a, @b)|

CALL mysqltest2.p3(@a, @b)|

CALL mysqltest2.p4(@a, @b)|

#   - Restore environment;

set names koi8r|

#   - Check!

--source include/ddl_i18n.check_sp.inc

#

# Check mysqldump.

#

#  - Dump mysqltest1, mysqltest2;

--echo

--echo ---> Dump of mysqltest1

--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --routines --databases mysqltest1

--echo

--echo ---> Dumping mysqltest1 to ddl_i18n_koi8r.sp.mysqltest1.sql

--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --routines --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.sp.mysqltest1.sql

--echo

--echo ---> Dump of mysqltest2

--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --routines --databases mysqltest2

--echo

--echo ---> Dumping mysqltest2 to ddl_i18n_koi8r.sp.mysqltest2.sql

--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --routines --databases mysqltest2 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.sp.mysqltest2.sql

#   - Clean mysqltest1, mysqltest2;

--echo

--echo

DROP DATABASE mysqltest1|

DROP DATABASE mysqltest2|

#   - Restore mysqltest1;

--echo

--echo

--echo ---> Restoring mysqltest1...

--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.sp.mysqltest1.sql

--echo ---> Restoring mysqltest2...

--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.sp.mysqltest2.sql

#

# Third-round checks.

#

#   - Change connection to flush SP-cache;

--connect (con3,localhost,root,,mysqltest1)

--echo

--echo ---> connection: con3

#   - Switch environment variables and trigger loading stored procedures;

SET @@character_set_client= cp1251|

SET @@character_set_results= cp1251|

SET @@collation_connection= cp1251_general_ci|

CALL p1(@a, @b)|

CALL p2(@a, @b)|

CALL mysqltest2.p3(@a, @b)|

CALL mysqltest2.p4(@a, @b)|

#   - Restore environment;

set names koi8r|

#   - Check!

--source include/ddl_i18n.check_sp.inc

#

# Cleanup.

#

--connection default

--echo

--echo ---> connection: default

--disconnect con2

--disconnect con3

use test|

DROP DATABASE mysqltest1|

DROP DATABASE mysqltest2|

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

#

# * Triggers.

#

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

--echo

--echo -------------------------------------------------------------------

--echo Triggers

--echo -------------------------------------------------------------------

--echo

#

# Preparation:

#

#   - Create database with fixed, pre-defined character set;

--disable_warnings

DROP DATABASE IF EXISTS mysqltest1|

DROP DATABASE IF EXISTS mysqltest2|

--enable_warnings

CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

use mysqltest1|

#   - Create tables for triggers;

CREATE TABLE t1(c INT)|

CREATE TABLE mysqltest2.t1(c INT)|

#   - Create log tables;

CREATE TABLE log(msg VARCHAR(255))|

CREATE TABLE mysqltest2.log(msg VARCHAR(255))|

#   - Create triggers -- with and without explicit CHARSET-clause for

#     SP-variable;

#

--echo

#     - Trigger trg1

CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW

BEGIN

  DECLARE ÐÅÒÅÍ1 CHAR(10);

  INSERT INTO log VALUES(COLLATION(ÐÅÒÅÍ1));

  INSERT INTO log VALUES(COLLATION('ÔÅËÓÔ'));

  INSERT INTO log VALUES(COLLATION(_koi8r    'ÔÅËÓÔ'));

  INSERT INTO log VALUES(COLLATION(_utf8 'текст'));

  INSERT INTO log VALUES(@@collation_connection);

  INSERT INTO log VALUES(@@character_set_client);

  SET @a1 = 'ÔÅËÓÔ';

  SET @a1 = _koi8r    'ÔÅËÓÔ';

  SET @a2 = _utf8 'текст';

END|

--echo

#     - Trigger trg2

CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW

BEGIN

  DECLARE ÐÅÒÅÍ1 CHAR(10) CHARACTER SET utf8;

  INSERT INTO log VALUES(COLLATION(ÐÅÒÅÍ1));

  INSERT INTO log VALUES(COLLATION('ÔÅËÓÔ'));

  INSERT INTO log VALUES(COLLATION(_koi8r    'ÔÅËÓÔ'));

  INSERT INTO log VALUES(COLLATION(_utf8 'текст'));

  INSERT INTO log VALUES(@@collation_connection);

  INSERT INTO log VALUES(@@character_set_client);

  SET @b1 = 'ÔÅËÓÔ';

  SET @b1 = _koi8r    'ÔÅËÓÔ';

  SET @b2 = _utf8 'текст';

END|

--echo

#     - Trigger trg3

CREATE TRIGGER mysqltest2.trg3 BEFORE INSERT ON mysqltest2.t1 FOR EACH ROW

BEGIN

  DECLARE ÐÅÒÅÍ1 CHAR(10);

  INSERT INTO log VALUES(COLLATION(ÐÅÒÅÍ1));

  INSERT INTO log VALUES(COLLATION('ÔÅËÓÔ'));

  INSERT INTO log VALUES(COLLATION(_koi8r    'ÔÅËÓÔ'));

  INSERT INTO log VALUES(COLLATION(_utf8 'текст'));

  INSERT INTO log VALUES(@@collation_connection);

  INSERT INTO log VALUES(@@character_set_client);

  SET @a1 = 'ÔÅËÓÔ';

  SET @a1 = _koi8r    'ÔÅËÓÔ';

  SET @a2 = _utf8 'текст';

END|

--echo

#     - Trigger trg4

CREATE TRIGGER mysqltest2.trg4 AFTER INSERT ON mysqltest2.t1 FOR EACH ROW

BEGIN

  DECLARE ÐÅÒÅÍ1 CHAR(10) CHARACTER SET utf8;

  INSERT INTO log VALUES(COLLATION(ÐÅÒÅÍ1));

  INSERT INTO log VALUES(COLLATION('ÔÅËÓÔ'));

  INSERT INTO log VALUES(COLLATION(_koi8r    'ÔÅËÓÔ'));

  INSERT INTO log VALUES(COLLATION(_utf8 'текст'));

  INSERT INTO log VALUES(@@collation_connection);

  INSERT INTO log VALUES(@@character_set_client);

  SET @b1 = 'ÔÅËÓÔ';

  SET @b1 = _koi8r    'ÔÅËÓÔ';

  SET @b2 = _utf8 'текст';

END|

--echo

#

# First-round checks.

#

--source include/ddl_i18n.check_triggers.inc

#

# Change running environment (alter database character set, change session

# variables).

#

--echo

--echo

ALTER DATABASE mysqltest1 COLLATE cp866_general_ci|

ALTER DATABASE mysqltest2 COLLATE cp866_general_ci|

#

# Second-round checks:

#

#  - Flush table cache;

ALTER TABLE t1 ADD COLUMN fake INT|

ALTER TABLE t1 DROP COLUMN fake|

ALTER TABLE mysqltest2.t1 ADD COLUMN fake INT|

ALTER TABLE mysqltest2.t1 DROP COLUMN fake|

#   - Switch environment variables and initiate loading of triggers

#     (connect using NULL database);

--connect (con2,localhost,root,,)

--echo

--echo ---> connection: con2

SET @@character_set_client= cp1251|

SET @@character_set_results= cp1251|

SET @@collation_connection= cp1251_general_ci|

INSERT INTO mysqltest1.t1 VALUES(0)|

INSERT INTO mysqltest2.t1 VALUES(0)|

DELETE FROM mysqltest1.log|

DELETE FROM mysqltest2.log|

#   - Restore environment;

set names koi8r|

use mysqltest1|

#   - Check!

--source include/ddl_i18n.check_triggers.inc

#

# Check mysqldump.

#

#  - Dump mysqltest1, mysqltest2;

--echo

--echo ---> Dump of mysqltest1

--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --triggers --databases mysqltest1

--echo

--echo ---> Dumping mysqltest1 to ddl_i18n_koi8r.triggers.mysqltest1.sql

--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --triggers --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.triggers.mysqltest1.sql

--echo

--echo ---> Dump of mysqltest2

--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --triggers --databases mysqltest2

--echo

--echo ---> Dumping mysqltest2 to ddl_i18n_koi8r.triggers.mysqltest2.sql

--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --triggers --databases mysqltest2 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.triggers.mysqltest2.sql

#   - Clean mysqltest1, mysqltest2;

--echo

--echo

DROP DATABASE mysqltest1|

DROP DATABASE mysqltest2|

#   - Restore mysqltest1;

--echo

--echo

--echo ---> Restoring mysqltest1...

--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.triggers.mysqltest1.sql

--echo ---> Restoring mysqltest2...

--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_koi8r.triggers.mysqltest2.sql

#

# Third-round checks.

#

#   - Flush table cache;

ALTER TABLE mysqltest1.t1 ADD COLUMN fake INT|

ALTER TABLE mysqltest1.t1 DROP COLUMN fake|

ALTER TABLE mysqltest2.t1 ADD COLUMN fake INT|

ALTER TABLE mysqltest2.t1 DROP COLUMN fake|

#   - Switch environment variables and initiate loading of triggers

#     (connect using NULL database);

--connect (con3,localhost,root,,)

--echo

--echo ---> connection: con3

SET @@character_set_client= cp1251|

SET @@character_set_results= cp1251|

SET @@collation_connection= cp1251_general_ci|

INSERT INTO mysqltest1.t1 VALUES(0)|

INSERT INTO mysqltest2.t1 VALUES(0)|

DELETE FROM mysqltest1.log|

DELETE FROM mysqltest2.log|

#   - Restore environment;

set names koi8r|

use mysqltest1|

#   - Check!

--source include/ddl_i18n.check_triggers.inc

#

# Cleanup.

#

--connection default

--echo

--echo ---> connection: default

--disconnect con2

--disconnect con3

use test|

DROP DATABASE mysqltest1|

DROP DATABASE mysqltest2|

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

#

# * Events

#

# We don't have EXECUTE EVENT so far, so this test is limited. It checks that

# event with non-latin1 symbols can be created, dumped, restored and SHOW

# statements work properly.

#

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

--echo

--echo -------------------------------------------------------------------

--echo Events

--echo -------------------------------------------------------------------

--echo

#

# Preparation:

#

#   - Create database with fixed, pre-defined character set.

--disable_warnings

DROP DATABASE IF EXISTS mysqltest1|

DROP DATABASE IF EXISTS mysqltest2|

--enable_warnings

CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

use mysqltest1|

#   - Create two stored routines -- with and without explicit

#     CHARSET-clause for SP-variable;

#

--echo

#     - Event ev1

CREATE EVENT ev1 ON SCHEDULE AT '2030-01-01 00:00:00' DO

BEGIN

  DECLARE ÐÅÒÅÍ1 CHAR(10);

  SELECT

    COLLATION(ÐÅÒÅÍ1) AS c1,

    COLLATION('ÔÅËÓÔ') AS c2,

    COLLATION(_koi8r    'ÔÅËÓÔ') AS c3,

    COLLATION(_utf8 'текст') AS c4,

    @@collation_connection AS c5,

    @@character_set_client AS c6;

END|

--echo

#     - Event ev2

CREATE EVENT ev2 ON SCHEDULE AT '2030-01-01 00:00:00' DO

BEGIN

  DECLARE ÐÅÒÅÍ1 CHAR(10) CHARACTER SET utf8;

  SELECT

    COLLATION(ÐÅÒÅÍ1) AS c1,

    COLLATION('ÔÅËÓÔ') AS c2,

    COLLATION(_koi8r    'ÔÅËÓÔ') AS c3,

    COLLATION(_utf8 'текст') AS c4,

    @@collation_connection AS c5,

    @@character_set_client AS c6;

END|

--echo

#     - Event ev3

CREATE EVENT mysqltest2.ev3 ON SCHEDULE AT '2030-01-01 00:00:00' DO

BEGIN

  DECLARE ÐÅÒÅÍ1 CHAR(10) CHARACTER SET utf8;

  SELECT

    COLLATION(ÐÅÒÅÍ1) AS c1,

    COLLATION('ÔÅËÓÔ') AS c2,

    COLLATION(_koi8r    'ÔÅËÓÔ') AS c3,

    COLLATION(_utf8 'текст') AS c4,

    @@collation_connection AS c5,

    @@character_set_client AS c6;

END|

--echo

#     - Event ev4

CREATE EVENT mysqltest2.ev4 ON SCHEDULE AT '2030-01-01 00:00:00' DO

BEGIN

  DECLARE ÐÅÒÅÍ1 CHAR(10) CHARACTER SET utf8;

  SELECT