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 154
