Return to collate6.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

Annotation of embedaddon/sqlite3/test/collate6.test, revision 1.1

1.1     ! misho       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