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

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