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.1.1.1 2012/02/21 17:04:16 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>