~drizzle-trunk/drizzle/development

--exec $DRIZZLE_DUMP --character-sets-dir=$CHARSETSDIR --compact --routines --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8sp.mysqltest1.sql

408

409

--echo

410

--echo ---> Dump of mysqltest2

411

412

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

413

414

--echo

415

--echo ---> Dumping mysqltest2 to ddl_i18n_utf8sp.mysqltest2.sql

416

417

--exec $DRIZZLE_DUMP --character-sets-dir=$CHARSETSDIR --compact --routines --databases mysqltest2 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8sp.mysqltest2.sql

418

419

# - Clean mysqltest1, mysqltest2;

420

421

--echo

422

--echo

423

424

DROP DATABASE mysqltest1|

425

DROP DATABASE mysqltest2|

426

427

# - Restore mysqltest1;

428

429

--echo

430

--echo

431

432

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

433

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

434

435

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

436

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

437

438

439

# Third-round checks.

440

441

442

# - Change connection to flush SP-cache;

443

444

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

445

--echo

446

--echo ---> connection: con3

447

448

# - Switch environment variables and trigger loading stored procedures;

449

450

SET @@character_set_client= cp1251|

451

SET @@character_set_results= cp1251|

452

SET @@collation_connection= cp1251_general_ci|

453

454

CALL p1(@a, @b)|

455

CALL p2(@a, @b)|

456

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

457

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

458

459

# - Restore environment;

460

461

set names utf8|

462

463

# - Check!

464

465

--source include/ddl_i18n.check_sp.inc

466

467

468

# Cleanup.

469

470

471

--connection default

472

--echo

473

--echo ---> connection: default

474

475

--disconnect con2

476

--disconnect con3

477

478

use test|

479

480

DROP DATABASE mysqltest1|

481

DROP DATABASE mysqltest2|

482

483

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

484

485

# * Triggers.

486

487

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

488

489

--echo

490

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

491

--echo Triggers

492

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

493

--echo

494

495

496

# Preparation:

497

498

499

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

500

501

--disable_warnings

502

DROP DATABASE IF EXISTS mysqltest1|

503

DROP DATABASE IF EXISTS mysqltest2|

504

--enable_warnings

505

506

CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

507

CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

508

509

use mysqltest1|

510

511

# - Create tables for triggers;

512

513

CREATE TABLE t1(c INT)|

514

CREATE TABLE mysqltest2.t1(c INT)|

515

516

# - Create log tables;

517

518

CREATE TABLE log(msg VARCHAR(255))|

519

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

520

521

522

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

523

# SP-variable;

524

525

526

--echo

527

528

# - Trigger trg1

529

530

CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW

531

BEGIN

532

DECLARE перем1 CHAR(10);

533

534

INSERT INTO log VALUES(COLLATION(перем1));

535

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

536

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

537

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

538

INSERT INTO log VALUES(@@collation_connection);

539

INSERT INTO log VALUES(@@character_set_client);

540

541

SET @a1 = 'текст';

542

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

543

SET @a3 = _koi8r '��';

544

END|

545

546

--echo

547

548

# - Trigger trg2

549

550

CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW

551

BEGIN

552

DECLARE перем1 CHAR(10) CHARACTER SET utf8;

553

554

INSERT INTO log VALUES(COLLATION(перем1));

555

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

556

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

557

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

558

INSERT INTO log VALUES(@@collation_connection);

559

INSERT INTO log VALUES(@@character_set_client);

560

561

SET @b1 = 'текст';

562

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

563

SET @b3 = _koi8r '��';

564

END|

565

566

--echo

567

568

# - Trigger trg3

569

570

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

571

BEGIN

572

DECLARE перем1 CHAR(10);

573

574

INSERT INTO log VALUES(COLLATION(перем1));

575

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

576

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

577

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

578

INSERT INTO log VALUES(@@collation_connection);

579

INSERT INTO log VALUES(@@character_set_client);

580

581

SET @a1 = 'текст';

582

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

583

SET @a3 = _koi8r '��';

584

END|

585

586

--echo

587

588

# - Trigger trg4

589

590

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

591

BEGIN

592

DECLARE перем1 CHAR(10) CHARACTER SET utf8;

593

594

INSERT INTO log VALUES(COLLATION(перем1));

595

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

596

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

597

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

598

INSERT INTO log VALUES(@@collation_connection);

599

INSERT INTO log VALUES(@@character_set_client);

600

601

SET @b1 = 'текст';

602

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

603

SET @b3 = _koi8r '��';

604

END|

605

606

--echo

607

608

609

# First-round checks.

610

611

612

--source include/ddl_i18n.check_triggers.inc

613

614

615

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

616

# variables).

617

618

619

--echo

620

--echo

621

622

ALTER DATABASE mysqltest1 COLLATE cp866_general_ci|

623

ALTER DATABASE mysqltest2 COLLATE cp866_general_ci|

624

625

626

# Second-round checks:

627

628

629

# - Flush table cache;

630

631

ALTER TABLE t1 ADD COLUMN fake INT|

632

ALTER TABLE t1 DROP COLUMN fake|

633

634

ALTER TABLE mysqltest2.t1 ADD COLUMN fake INT|

635

ALTER TABLE mysqltest2.t1 DROP COLUMN fake|

636

637

# - Switch environment variables and initiate loading of triggers

638

# (connect using NULL database);

639

640

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

641

--echo

642

--echo ---> connection: con2

643

644

SET @@character_set_client= cp1251|

645

SET @@character_set_results= cp1251|

646

SET @@collation_connection= cp1251_general_ci|

647

648

INSERT INTO mysqltest1.t1 VALUES(0)|

649

INSERT INTO mysqltest2.t1 VALUES(0)|

650

651

DELETE FROM mysqltest1.log|

652

DELETE FROM mysqltest2.log|

653

654

# - Restore environment;

655

656

set names utf8|

657

658

use mysqltest1|

659

660

# - Check!

661

662

--source include/ddl_i18n.check_triggers.inc

663

664

665

# Check mysqldump.

666

667

668

# - Dump mysqltest1, mysqltest2;

669

670

--echo

671

--echo ---> Dump of mysqltest1

672

673

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

674

675

--echo

676

--echo ---> Dumping mysqltest1 to ddl_i18n_utf8triggers.mysqltest1.sql

677

678

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

679

680

--echo

681

--echo ---> Dump of mysqltest2

682

683

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

684

685

--echo

686

--echo ---> Dumping mysqltest2 to ddl_i18n_utf8triggers.mysqltest2.sql

687

688

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

689

690

# - Clean mysqltest1, mysqltest2;

691

692

--echo

693

--echo

694

695

DROP DATABASE mysqltest1|

696

DROP DATABASE mysqltest2|

697

698

# - Restore mysqltest1;

699

700

--echo

701

--echo

702

703

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

704

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

705

706

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

707

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

708

709

710

# Third-round checks.

711

712

713

# - Flush table cache;

714

715

ALTER TABLE mysqltest1.t1 ADD COLUMN fake INT|

716

ALTER TABLE mysqltest1.t1 DROP COLUMN fake|

717

718

ALTER TABLE mysqltest2.t1 ADD COLUMN fake INT|

719

ALTER TABLE mysqltest2.t1 DROP COLUMN fake|

720

721

# - Switch environment variables and initiate loading of triggers

722

# (connect using NULL database);

723

724

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

725

--echo

726

--echo ---> connection: con3

727

728

SET @@character_set_client= cp1251|

729

SET @@character_set_results= cp1251|

730

SET @@collation_connection= cp1251_general_ci|

731

732

INSERT INTO mysqltest1.t1 VALUES(0)|

733

INSERT INTO mysqltest2.t1 VALUES(0)|

734

735

DELETE FROM mysqltest1.log|

736

DELETE FROM mysqltest2.log|

737

738

# - Restore environment;

739

740

set names utf8|

741

742

use mysqltest1|

743

744

# - Check!

745

746

--source include/ddl_i18n.check_triggers.inc

747

748

749

# Cleanup.

750

751

752

--connection default

753

--echo

754

--echo ---> connection: default

755

756

--disconnect con2

757

--disconnect con3

758

759

use test|

760

761

DROP DATABASE mysqltest1|

762

DROP DATABASE mysqltest2|

763

764

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

765

766

# * Events

767

768

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

769

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

770

# statements work properly.

771

772

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

773

774

--echo

775

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

776

--echo Events

777

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

778

--echo

779

780

781

# Preparation:

782

783

784

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

785

786

--disable_warnings

787

DROP DATABASE IF EXISTS mysqltest1|

788

DROP DATABASE IF EXISTS mysqltest2|

789

--enable_warnings

790

791

CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

792

CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

793

794

use mysqltest1|

795

796

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

797

# CHARSET-clause for SP-variable;

798

799

800

--echo

801

802

# - Event ev1

803

804

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

805

BEGIN

806

DECLARE перем1 CHAR(10);

807

808

SELECT

809

COLLATION(перем1) AS c1,

810

COLLATION('текст') AS c2,

811

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

812

COLLATION(_koi8r '��') AS c4,

813

@@collation_connection AS c5,

814

@@character_set_client AS c6;

815

END|

816

817

--echo

818

819

# - Event ev2

820

821

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

822

BEGIN

823

DECLARE перем1 CHAR(10) CHARACTER SET utf8;

824

825

SELECT

826

COLLATION(перем1) AS c1,

827

COLLATION('текст') AS c2,

828

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

829

COLLATION(_koi8r '��') AS c4,

830

@@collation_connection AS c5,

831

@@character_set_client AS c6;

832

END|

833

834

--echo

835

836

# - Event ev3

837

838

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

839

BEGIN

840

DECLARE перем1 CHAR(10) CHARACTER SET utf8;

841

842

SELECT

843

COLLATION(перем1) AS c1,

844

COLLATION('текст') AS c2,

845

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

846

COLLATION(_koi8r '��') AS c4,

847

@@collation_connection AS c5,

848

@@character_set_client AS c6;

849

END|

850

851

--echo

852

853

# - Event ev4

854

855

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

856

BEGIN

857

DECLARE перем1 CHAR(10) CHARACTER SET utf8;

858

859

SELECT

860

COLLATION(перем1) AS c1,

861

COLLATION('текст') AS c2,

862

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

863

COLLATION(_koi8r '��') AS c4,

864

@@collation_connection AS c5,

865

@@character_set_client AS c6;

866

END|

867

868

--echo

869

870

871

872

# First-round checks.

873

874

875

--source include/ddl_i18n.check_events.inc

876

877

878

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

879

# variables).

880

881

882

--echo

883

--echo

884

885

ALTER DATABASE mysqltest1 COLLATE cp866_general_ci|

886

ALTER DATABASE mysqltest2 COLLATE cp866_general_ci|

887

888

889

# Second-round checks:

890

891

892

# - Change connection to flush cache;

893

894

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

895

--echo

896

--echo ---> connection: con2

897

898

# - Switch environment variables and trigger loading stored procedures;

899

900

SET @@character_set_client= cp1251|

901

SET @@character_set_results= cp1251|

902

SET @@collation_connection= cp1251_general_ci|

903

904

--disable_result_log

905

SHOW CREATE EVENT ev1|

906

SHOW CREATE EVENT ev2|

907

SHOW CREATE EVENT mysqltest2.ev3|

908

SHOW CREATE EVENT mysqltest2.ev4|

909

--enable_result_log

910

911

# - Restore environment;

912

913

set names utf8|

914

915

# - Check!

916

917

--source include/ddl_i18n.check_events.inc

918

919

920

# Check mysqldump.

921

922

923

# - Dump mysqltest1, mysqltest2;

924

925

--echo

926

--echo ---> Dump of mysqltest1

927

928

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

929

930

--echo

931

--echo ---> Dumping mysqltest1 to ddl_i18n_utf8events.mysqltest1.sql

932

933

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

934

935

--echo

936

--echo ---> Dump of mysqltest2

937

938

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

939

940

--echo

941

--echo ---> Dumping mysqltest2 to ddl_i18n_utf8events.mysqltest2.sql

942

943

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

944

945

# - Clean mysqltest1, mysqltest2;

946

947

--echo

948

--echo

949

950

DROP DATABASE mysqltest1|

951

DROP DATABASE mysqltest2|

952

953

# - Restore mysqltest1;

954

955

--echo

956

--echo

957

958

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

959

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

960

961

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

962

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

963

964

965

# Third-round checks.

966

967

968

# - Change connection to flush cache;

969

970

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

971

--echo

972

--echo ---> connection: con3

973

974

# - Switch environment variables and trigger loading stored procedures;

975

976

SET @@character_set_client= cp1251|

977

SET @@character_set_results= cp1251|

978

SET @@collation_connection= cp1251_general_ci|

979

980

--disable_result_log

981

SHOW CREATE EVENT ev1|

982

SHOW CREATE EVENT ev2|

983

SHOW CREATE EVENT mysqltest2.ev3|

984

SHOW CREATE EVENT mysqltest2.ev4|

985

--enable_result_log

986

987

# - Restore environment;

988

989

set names utf8|

990

991

# - Check!

992

993

--source include/ddl_i18n.check_events.inc

994

995

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

996

997

# * DDL statements inside stored routine.

998

999

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

1000

# are called from stored routine.

1001

1002

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

1003

1004

--echo

1005

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

1006

--echo DDL statements within stored routine.

1007

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

1008

--echo

1009

1010

1011

# Preparation:

1012

1013

1014

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

1015

1016

--disable_warnings

1017

DROP DATABASE IF EXISTS mysqltest1|

1018

DROP DATABASE IF EXISTS mysqltest2|

1019

--enable_warnings

1020

1021

CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

1022

CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|

1023

1024

use mysqltest1|

1025

1026

# - Create procedures;

1027

1028

--echo

1029

1030

CREATE PROCEDURE p1()

1031

BEGIN

1032

CREATE TABLE t1(col1 VARCHAR(10));

1033

SHOW CREATE TABLE t1;

1034

END|

1035

1036

--echo

1037

1038

CREATE PROCEDURE mysqltest2.p2()

1039

BEGIN

1040

CREATE TABLE t2(col1 VARCHAR(10));

1041

SHOW CREATE TABLE t2;

1042

END|

1043

1044

--echo

1045

1046

1047

# First-round checks.

1048

1049

1050

CALL p1()|

1051

1052

--echo

1053

1054

SHOW CREATE TABLE t1|

1055

1056

--echo

1057

--echo

1058

1059

CALL mysqltest2.p2()|

1060

1061

--echo

1062

1063

SHOW CREATE TABLE mysqltest2.t2|

1064

1065

1066

# Alter database.

1067

1068

1069

--echo

1070

1071

ALTER DATABASE mysqltest1 COLLATE cp1251_general_cs|

1072

ALTER DATABASE mysqltest2 COLLATE cp1251_general_cs|

1073

1074

DROP TABLE t1|

1075

DROP TABLE mysqltest2.t2|

1076

1077

--echo

1078

1079

1080

# Second-round checks.

1081

1082

1083

CALL p1()|

1084

1085

--echo

1086

1087

SHOW CREATE TABLE t1|

1088

1089

--echo

1090

--echo

1091

1092

CALL mysqltest2.p2()|

1093

1094

--echo

1095

1096

SHOW CREATE TABLE mysqltest2.t2|

1097

1098

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

1099

1100

# That's it.

1101

1102

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

1103

1104

1105

# Cleanup.

1106

1107

1108

--connection default

1109

--echo

1110

--echo ---> connection: default

1111

1112

--disconnect con2

1113

--disconnect con3

1114

1115

use test|

1116

1117

DROP DATABASE mysqltest1|

1118

DROP DATABASE mysqltest2|

Older »