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 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# There are no tests in this file that will work without 21# trigger support. 22# 23ifcapable {!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. 30db collate NOCASE nocase_collate 31proc 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 40do_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. 49do_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} {} 57do_test collate6-1.2 { 58 execsql { 59 INSERT INTO collate6tab VALUES('a', 'b'); 60 SELECT * FROM collate6log; 61 } 62} {a b} 63do_test collate6-1.3 { 64 execsql { 65 INSERT INTO collate6tab VALUES('A', 'B'); 66 SELECT * FROM collate6log; 67 } 68} {a b A B} 69do_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. 78do_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} {} 85do_test collate6-1.6 { 86 execsql { 87 INSERT INTO collate6tab VALUES('a', 'b'); 88 SELECT * FROM collate6log; 89 } 90} {1 1} 91do_test collate6-1.7 { 92 execsql { 93 INSERT INTO collate6tab VALUES('A', 'B'); 94 SELECT * FROM collate6log; 95 } 96} {1 1 1 0} 97do_test collate6-1.8 { 98 execsql { 99 DROP TRIGGER collate6trig; 100 DELETE FROM collate6log; 101 } 102} {} 103 104do_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# 113do_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} {} 122do_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} {} 129do_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# 141do_test collate6-3.1 { 142 execsql { 143 SELECT 1 FROM sqlite_master WHERE name COLLATE nocase = 'hello'; 144 } 145} {} 146do_test collate6-3.2 { 147 execsql { 148 SELECT 1 FROM sqlite_master WHERE 'hello' = name COLLATE nocase; 149 } 150} {} 151 152 153finish_test 154