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