1 #

     2 # 2001 September 15

     3 #

     4 # The author disclaims copyright to this source code.  In place of

     5 # a legal notice, here is a blessing:

     6 #

     7 #    May you do good and not evil.

     8 #    May you find forgiveness for yourself and forgive others.

     9 #    May you share freely, never taking more than you give.

    10 #

    11 #***********************************************************************

    12 # This file implements regression tests for SQLite library.  The

    13 # focus of this script is collation sequences in concert with triggers.

    14 #

    15 # $Id: collate6.test,v 1.4 2007/07/30 14:40:48 danielk1977 Exp $

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 # There are no tests in this file that will work without

    21 # trigger support.

    22 #

    23 ifcapable {!trigger} {

    24   finish_test

    25   return

    26 }

    27 

    28 # Create a case-insensitive collation type NOCASE for use in testing. 

    29 # Normally, capital letters are less than their lower-case counterparts.

    30 db collate NOCASE nocase_collate

    31 proc nocase_collate {a b} {

    32   return [string compare -nocase $a $b]

    33 }

    34 

    35 #

    36 # Tests are organized as follows:

    37 # collate6-1.* - triggers.

    38 #

    39 

    40 do_test collate6-1.0 {

    41   execsql {

    42     CREATE TABLE collate6log(a, b);

    43     CREATE TABLE collate6tab(a COLLATE NOCASE, b COLLATE BINARY);

    44   }

    45 } {}

    46 

    47 # Test that the default collation sequence applies to new.* references 

    48 # in WHEN clauses.

    49 do_test collate6-1.1 {

    50   execsql {

    51     CREATE TRIGGER collate6trig BEFORE INSERT ON collate6tab 

    52       WHEN new.a = 'a' BEGIN

    53         INSERT INTO collate6log VALUES(new.a, new.b);

    54     END;

    55   }

    56 } {}

    57 do_test collate6-1.2 {

    58   execsql {

    59     INSERT INTO collate6tab VALUES('a', 'b');

    60     SELECT * FROM collate6log;

    61   }

    62 } {a b}

    63 do_test collate6-1.3 {

    64   execsql {

    65     INSERT INTO collate6tab VALUES('A', 'B');

    66     SELECT * FROM collate6log;

    67   }

    68 } {a b A B}

    69 do_test collate6-1.4 {

    70   execsql {

    71     DROP TRIGGER collate6trig;

    72     DELETE FROM collate6log;

    73   } 

    74 } {}

    75 

    76 # Test that the default collation sequence applies to new.* references 

    77 # in the body of triggers.

    78 do_test collate6-1.5 {

    79   execsql {

    80     CREATE TRIGGER collate6trig BEFORE INSERT ON collate6tab BEGIN

    81       INSERT INTO collate6log VALUES(new.a='a', new.b='b');

    82     END;

    83   }

    84 } {}

    85 do_test collate6-1.6 {

    86   execsql {

    87     INSERT INTO collate6tab VALUES('a', 'b');

    88     SELECT * FROM collate6log;

    89   }

    90 } {1 1}

    91 do_test collate6-1.7 {

    92   execsql {

    93     INSERT INTO collate6tab VALUES('A', 'B');

    94     SELECT * FROM collate6log;

    95   }

    96 } {1 1 1 0}

    97 do_test collate6-1.8 {

    98   execsql {

    99     DROP TRIGGER collate6trig;

   100     DELETE FROM collate6log;

   101   } 

   102 } {}

   103 

   104 do_test collate6-1.9 {

   105   execsql {

   106     DROP TABLE collate6tab;

   107   }

   108 } {}

   109 

   110 # Test that an explicit collation sequence overrides an implicit 

   111 # one attached to a 'new' reference.

   112 #

   113 do_test collate6-2.1 {

   114   execsql {

   115     CREATE TABLE abc(a COLLATE binary, b, c);

   116     CREATE TABLE def(a, b, c);

   117     CREATE TRIGGER abc_t1 AFTER INSERT ON abc BEGIN

   118       INSERT INTO def SELECT * FROM abc WHERE a < new.a COLLATE nocase;

   119     END

   120   }

   121 } {}

   122 do_test collate6-2.2 {

   123   execsql {

   124     INSERT INTO abc VALUES('One', 'Two', 'Three');

   125     INSERT INTO abc VALUES('one', 'two', 'three');

   126     SELECT * FROM def;

   127   }

   128 } {}

   129 do_test collate6-2.3 {

   130   execsql {

   131     UPDATE abc SET a = 'four' WHERE a = 'one';

   132     CREATE TRIGGER abc_t2 AFTER UPDATE ON abc BEGIN

   133       INSERT INTO def SELECT * FROM abc WHERE a < new.a COLLATE nocase;

   134     END;

   135     SELECT * FROM def;

   136   }

   137 } {}

   138 

   139 # At one point the 6-3.2 (but not 6-3.1) was causing an assert() to fail.

   140 #

   141 do_test collate6-3.1 {

   142   execsql {

   143     SELECT 1 FROM sqlite_master WHERE name COLLATE nocase = 'hello';

   144   }

   145 } {}

   146 do_test collate6-3.2 {

   147   execsql {

   148     SELECT 1 FROM sqlite_master WHERE 'hello' = name COLLATE nocase;

   149   }

   150 } {}

   151 

   152 

   153 finish_test