~drizzle-trunk/drizzle/development

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

410

411

--echo

412

--echo ---> Dump of mysqltest2

413

414

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

415

416

--echo

417

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

418

419

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

420

421

# - Clean mysqltest1, mysqltest2;

422

423

--echo

424

--echo

425

426

DROP DATABASE mysqltest1|

427

DROP DATABASE mysqltest2|

428

429

# - Restore mysqltest1;

430

431

--echo

432

--echo

433

434

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

435

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

436

437

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

438

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

439

440

441

# Third-round checks.

442

443

444

# - Change connection to flush SP-cache;

445

446

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

447

--echo

448

--echo ---> connection: con3

449

450

# - Switch environment variables and trigger loading stored procedures;

451

452

SET @@character_set_client= cp1251|

453

SET @@character_set_results= cp1251|

454

SET @@collation_connection= cp1251_general_ci|

455

456

CALL p1(@a, @b)|

457

CALL p2(@a, @b)|

458

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

459

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

460

461

# - Restore environment;

462

463

set names koi8r|

464

465

# - Check!

466

467

--source include/ddl_i18n.check_sp.inc

468

469

470

# Cleanup.

471

472

473

--connection default

474

--echo

475

--echo ---> connection: default

476

477

--disconnect con2

478

--disconnect con3

479

480

use test|

481

482

DROP DATABASE mysqltest1|

483

DROP DATABASE mysqltest2|

484

485

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

486

487

# * Triggers.

488

489

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

490

491

--echo

492

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

493

--echo Triggers

494

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

495

--echo

496

497

498

# Preparation:

499

500

501

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

502

503

--disable_warnings

504

DROP DATABASE IF EXISTS mysqltest1|

505

DROP DATABASE IF EXISTS mysqltest2|

506

--enable_warnings

507

508

CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

509

CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

510

511

use mysqltest1|

512

513

# - Create tables for triggers;

514

515

CREATE TABLE t1(c INT)|

516

CREATE TABLE mysqltest2.t1(c INT)|

517

518

# - Create log tables;

519

520

CREATE TABLE log(msg VARCHAR(255))|

521

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

522

523

524

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

525

# SP-variable;

526

527

528

--echo

529

530

# - Trigger trg1

531

532

CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW

533

BEGIN

534

DECLARE ��1 CHAR(10);

535

536

INSERT INTO log VALUES(COLLATION(��1));

537

INSERT INTO log VALUES(COLLATION('��'));

538

INSERT INTO log VALUES(COLLATION(_koi8r '��'));

539

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

540

INSERT INTO log VALUES(@@collation_connection);

541

INSERT INTO log VALUES(@@character_set_client);

542

543

SET @a1 = '��';

544

SET @a1 = _koi8r '��';

545

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

546

END|

547

548

--echo

549

550

# - Trigger trg2

551

552

CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW

553

BEGIN

554

DECLARE ��1 CHAR(10) CHARACTER SET utf8;

555

556

INSERT INTO log VALUES(COLLATION(��1));

557

INSERT INTO log VALUES(COLLATION('��'));

558

INSERT INTO log VALUES(COLLATION(_koi8r '��'));

559

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

560

INSERT INTO log VALUES(@@collation_connection);

561

INSERT INTO log VALUES(@@character_set_client);

562

563

SET @b1 = '��';

564

SET @b1 = _koi8r '��';

565

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

566

END|

567

568

--echo

569

570

# - Trigger trg3

571

572

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

573

BEGIN

574

DECLARE ��1 CHAR(10);

575

576

INSERT INTO log VALUES(COLLATION(��1));

577

INSERT INTO log VALUES(COLLATION('��'));

578

INSERT INTO log VALUES(COLLATION(_koi8r '��'));

579

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

580

INSERT INTO log VALUES(@@collation_connection);

581

INSERT INTO log VALUES(@@character_set_client);

582

583

SET @a1 = '��';

584

SET @a1 = _koi8r '��';

585

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

586

END|

587

588

--echo

589

590

# - Trigger trg4

591

592

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

593

BEGIN

594

DECLARE ��1 CHAR(10) CHARACTER SET utf8;

595

596

INSERT INTO log VALUES(COLLATION(��1));

597

INSERT INTO log VALUES(COLLATION('��'));

598

INSERT INTO log VALUES(COLLATION(_koi8r '��'));

599

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

600

INSERT INTO log VALUES(@@collation_connection);

601

INSERT INTO log VALUES(@@character_set_client);

602

603

SET @b1 = '��';

604

SET @b1 = _koi8r '��';

605

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

606

END|

607

608

--echo

609

610

611

# First-round checks.

612

613

614

--source include/ddl_i18n.check_triggers.inc

615

616

617

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

618

# variables).

619

620

621

--echo

622

--echo

623

624

ALTER DATABASE mysqltest1 COLLATE cp866_general_ci|

625

ALTER DATABASE mysqltest2 COLLATE cp866_general_ci|

626

627

628

# Second-round checks:

629

630

631

# - Flush table cache;

632

633

ALTER TABLE t1 ADD COLUMN fake INT|

634

ALTER TABLE t1 DROP COLUMN fake|

635

636

ALTER TABLE mysqltest2.t1 ADD COLUMN fake INT|

637

ALTER TABLE mysqltest2.t1 DROP COLUMN fake|

638

639

# - Switch environment variables and initiate loading of triggers

640

# (connect using NULL database);

641

642

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

643

--echo

644

--echo ---> connection: con2

645

646

SET @@character_set_client= cp1251|

647

SET @@character_set_results= cp1251|

648

SET @@collation_connection= cp1251_general_ci|

649

650

INSERT INTO mysqltest1.t1 VALUES(0)|

651

INSERT INTO mysqltest2.t1 VALUES(0)|

652

653

DELETE FROM mysqltest1.log|

654

DELETE FROM mysqltest2.log|

655

656

# - Restore environment;

657

658

set names koi8r|

659

660

use mysqltest1|

661

662

# - Check!

663

664

--source include/ddl_i18n.check_triggers.inc

665

666

667

# Check mysqldump.

668

669

670

# - Dump mysqltest1, mysqltest2;

671

672

--echo

673

--echo ---> Dump of mysqltest1

674

675

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

676

677

--echo

678

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

679

680

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

681

682

--echo

683

--echo ---> Dump of mysqltest2

684

685

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

686

687

--echo

688

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

689

690

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

691

692

# - Clean mysqltest1, mysqltest2;

693

694

--echo

695

--echo

696

697

DROP DATABASE mysqltest1|

698

DROP DATABASE mysqltest2|

699

700

# - Restore mysqltest1;

701

702

--echo

703

--echo

704

705

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

706

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

707

708

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

709

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

710

711

712

# Third-round checks.

713

714

715

# - Flush table cache;

716

717

ALTER TABLE mysqltest1.t1 ADD COLUMN fake INT|

718

ALTER TABLE mysqltest1.t1 DROP COLUMN fake|

719

720

ALTER TABLE mysqltest2.t1 ADD COLUMN fake INT|

721

ALTER TABLE mysqltest2.t1 DROP COLUMN fake|

722

723

# - Switch environment variables and initiate loading of triggers

724

# (connect using NULL database);

725

726

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

727

--echo

728

--echo ---> connection: con3

729

730

SET @@character_set_client= cp1251|

731

SET @@character_set_results= cp1251|

732

SET @@collation_connection= cp1251_general_ci|

733

734

INSERT INTO mysqltest1.t1 VALUES(0)|

735

INSERT INTO mysqltest2.t1 VALUES(0)|

736

737

DELETE FROM mysqltest1.log|

738

DELETE FROM mysqltest2.log|

739

740

# - Restore environment;

741

742

set names koi8r|

743

744

use mysqltest1|

745

746

# - Check!

747

748

--source include/ddl_i18n.check_triggers.inc

749

750

751

# Cleanup.

752

753

754

--connection default

755

--echo

756

--echo ---> connection: default

757

758

--disconnect con2

759

--disconnect con3

760

761

use test|

762

763

DROP DATABASE mysqltest1|

764

DROP DATABASE mysqltest2|

765

766

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

767

768

# * Events

769

770

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

771

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

772

# statements work properly.

773

774

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

775

776

--echo

777

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

778

--echo Events

779

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

780

--echo

781

782

783

# Preparation:

784

785

786

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

787

788

--disable_warnings

789

DROP DATABASE IF EXISTS mysqltest1|

790

DROP DATABASE IF EXISTS mysqltest2|

791

--enable_warnings

792

793

CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

794

CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

795

796

use mysqltest1|

797

798

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

799

# CHARSET-clause for SP-variable;

800

801

802

--echo

803

804

# - Event ev1

805

806

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

807

BEGIN

808

DECLARE ��1 CHAR(10);

809

810

SELECT

811

COLLATION(��1) AS c1,

812

COLLATION('��') AS c2,

813

COLLATION(_koi8r '��') AS c3,

814

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

815

@@collation_connection AS c5,

816

@@character_set_client AS c6;

817

END|

818

819

--echo

820

821

# - Event ev2

822

823

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

824

BEGIN

825

DECLARE ��1 CHAR(10) CHARACTER SET utf8;

826

827

SELECT

828

COLLATION(��1) AS c1,

829

COLLATION('��') AS c2,

830

COLLATION(_koi8r '��') AS c3,

831

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

832

@@collation_connection AS c5,

833

@@character_set_client AS c6;

834

END|

835

836

--echo

837

838

# - Event ev3

839

840

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

841

BEGIN

842

DECLARE ��1 CHAR(10) CHARACTER SET utf8;

843

844

SELECT

845

COLLATION(��1) AS c1,

846

COLLATION('��') AS c2,

847

COLLATION(_koi8r '��') AS c3,

848

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

849

@@collation_connection AS c5,

850

@@character_set_client AS c6;

851

END|

852

853

--echo

854

855

# - Event ev4

856

857

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

858

BEGIN

859

DECLARE ��1 CHAR(10) CHARACTER SET utf8;

860

861

SELECT

862

COLLATION(��1) AS c1,

863

COLLATION('��') AS c2,

864

COLLATION(_koi8r '��') AS c3,

865

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

866

@@collation_connection AS c5,

867

@@character_set_client AS c6;

868

END|

869

870

--echo

871

872

873

874

# First-round checks.

875

876

877

--source include/ddl_i18n.check_events.inc

878

879

880

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

881

# variables).

882

883

884

--echo

885

--echo

886

887

ALTER DATABASE mysqltest1 COLLATE cp866_general_ci|

888

ALTER DATABASE mysqltest2 COLLATE cp866_general_ci|

889

890

891

# Second-round checks:

892

893

894

# - Change connection to flush cache;

895

896

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

897

--echo

898

--echo ---> connection: con2

899

900

# - Switch environment variables and trigger loading stored procedures;

901

902

SET @@character_set_client= cp1251|

903

SET @@character_set_results= cp1251|

904

SET @@collation_connection= cp1251_general_ci|

905

906

--disable_result_log

907

SHOW CREATE EVENT ev1|

908

SHOW CREATE EVENT ev2|

909

SHOW CREATE EVENT mysqltest2.ev3|

910

SHOW CREATE EVENT mysqltest2.ev4|

911

--enable_result_log

912

913

# - Restore environment;

914

915

set names koi8r|

916

917

# - Check!

918

919

--source include/ddl_i18n.check_events.inc

920

921

922

# Check mysqldump.

923

924

925

# - Dump mysqltest1, mysqltest2;

926

927

--echo

928

--echo ---> Dump of mysqltest1

929

930

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

931

932

--echo

933

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

934

935

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

936

937

--echo

938

--echo ---> Dump of mysqltest2

939

940

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

941

942

--echo

943

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

944

945

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

946

947

# - Clean mysqltest1, mysqltest2;

948

949

--echo

950

--echo

951

952

DROP DATABASE mysqltest1|

953

DROP DATABASE mysqltest2|

954

955

# - Restore mysqltest1;

956

957

--echo

958

--echo

959

960

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

961

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

962

963

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

964

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

965

966

967

# Third-round checks.

968

969

970

# - Change connection to flush cache;

971

972

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

973

--echo

974

--echo ---> connection: con3

975

976

# - Switch environment variables and trigger loading stored procedures;

977

978

SET @@character_set_client= cp1251|

979

SET @@character_set_results= cp1251|

980

SET @@collation_connection= cp1251_general_ci|

981

982

--disable_result_log

983

SHOW CREATE EVENT ev1|

984

SHOW CREATE EVENT ev2|

985

SHOW CREATE EVENT mysqltest2.ev3|

986

SHOW CREATE EVENT mysqltest2.ev4|

987

--enable_result_log

988

989

# - Restore environment;

990

991

set names koi8r|

992

993

# - Check!

994

995

--source include/ddl_i18n.check_events.inc

996

997

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

998

999

# * DDL statements inside stored routine.

1000

1001

# Here we check that DDL statements use actual database collation even if they

1002

# are called from stored routine.

1003

1004

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

1005

1006

--echo

1007

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

1008

--echo DDL statements within stored routine.

1009

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

1010

--echo

1011

1012

1013

# Preparation:

1014

1015

1016

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

1017

1018

--disable_warnings

1019

DROP DATABASE IF EXISTS mysqltest1|

1020

DROP DATABASE IF EXISTS mysqltest2|

1021

--enable_warnings

1022

1023

CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

1024

CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

1025

1026

use mysqltest1|

1027

1028

# - Create procedures;

1029

1030

--echo

1031

1032

CREATE PROCEDURE p1()

1033

BEGIN

1034

CREATE TABLE t1(col1 VARCHAR(10));

1035

SHOW CREATE TABLE t1;

1036

END|

1037

1038

--echo

1039

1040

CREATE PROCEDURE mysqltest2.p2()

1041

BEGIN

1042

CREATE TABLE t2(col1 VARCHAR(10));

1043

SHOW CREATE TABLE t2;

1044

END|

1045

1046

--echo

1047

1048

1049

# First-round checks.

1050

1051

1052

CALL p1()|

1053

1054

--echo

1055

1056

SHOW CREATE TABLE t1|

1057

1058

--echo

1059

--echo

1060

1061

CALL mysqltest2.p2()|

1062

1063

--echo

1064

1065

SHOW CREATE TABLE mysqltest2.t2|

1066

1067

1068

# Alter database.

1069

1070

1071

--echo

1072

1073

ALTER DATABASE mysqltest1 COLLATE cp1251_general_cs|

1074

ALTER DATABASE mysqltest2 COLLATE cp1251_general_cs|

1075

1076

DROP TABLE t1|

1077

DROP TABLE mysqltest2.t2|

1078

1079

--echo

1080

1081

1082

# Second-round checks.

1083

1084

1085

CALL p1()|

1086

1087

--echo

1088

1089

SHOW CREATE TABLE t1|

1090

1091

--echo

1092

--echo

1093

1094

CALL mysqltest2.p2()|

1095

1096

--echo

1097

1098

SHOW CREATE TABLE mysqltest2.t2|

1099

1100

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

1101

1102

# That's it.

1103

1104

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

1105

1106

1107

# Cleanup.

1108

1109

1110

--connection default

1111

--echo

1112

--echo ---> connection: default

1113

1114

--disconnect con2

1115

--disconnect con3

1116

1117

use test|

1118

1119

DROP DATABASE mysqltest1|

1120

DROP DATABASE mysqltest2|

Older »