wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2011 April 9
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing the various schema modification statements
13 # that feature "IF EXISTS" or "IF NOT EXISTS" clauses.
14 #
15  
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 source $testdir/lock_common.tcl
19  
20  
21 foreach jm {rollback wal} {
22  
23 set testprefix exists-$jm
24  
25 # This block of tests is targeted at CREATE XXX IF NOT EXISTS statements.
26 #
27 do_multiclient_test tn {
28  
29 # TABLE objects.
30 #
31 do_test 1.$tn.1.1 {
32 if {$jm == "wal"} { sql2 { PRAGMA journal_mode = WAL } }
33 sql2 { CREATE TABLE t1(x) }
34 sql1 { CREATE TABLE IF NOT EXISTS t1(a, b) }
35 sql2 { DROP TABLE t1 }
36 sql1 { CREATE TABLE IF NOT EXISTS t1(a, b) }
37 sql2 { SELECT name FROM sqlite_master WHERE type = 'table' }
38 } {t1}
39  
40 do_test 1.$tn.1.2 {
41 sql2 { CREATE TABLE t2(x) }
42 sql1 { CREATE TABLE IF NOT EXISTS t2 AS SELECT * FROM t1 }
43 sql2 { DROP TABLE t2 }
44 sql1 { CREATE TABLE IF NOT EXISTS t2 AS SELECT * FROM t1 }
45 sql2 { SELECT name FROM sqlite_master WHERE type = 'table' }
46 } {t1 t2}
47  
48  
49 # INDEX objects.
50 #
51 do_test 1.$tn.2 {
52 sql2 { CREATE INDEX i1 ON t1(a) }
53 sql1 { CREATE INDEX IF NOT EXISTS i1 ON t1(a, b) }
54 sql2 { DROP INDEX i1 }
55 sql1 { CREATE INDEX IF NOT EXISTS i1 ON t1(a, b) }
56 sql2 { SELECT name FROM sqlite_master WHERE type = 'index' }
57 } {i1}
58  
59 # VIEW objects.
60 #
61 do_test 1.$tn.3 {
62 sql2 { CREATE VIEW v1 AS SELECT * FROM t1 }
63 sql1 { CREATE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1 }
64 sql2 { DROP VIEW v1 }
65 sql1 { CREATE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1 }
66 sql2 { SELECT name FROM sqlite_master WHERE type = 'view' }
67 } {v1}
68  
69 # TRIGGER objects.
70 #
71 do_test $tn.4 {
72 sql2 { CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END }
73 sql1 { CREATE TRIGGER IF NOT EXISTS tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END }
74 sql2 { DROP TRIGGER tr1 }
75 sql1 { CREATE TRIGGER IF NOT EXISTS tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END }
76 sql2 { SELECT name FROM sqlite_master WHERE type = 'trigger' }
77 } {tr1}
78 }
79  
80 # This block of tests is targeted at DROP XXX IF EXISTS statements.
81 #
82 do_multiclient_test tn {
83  
84 # TABLE objects.
85 #
86 do_test 2.$tn.1 {
87 if {$jm == "wal"} { sql1 { PRAGMA journal_mode = WAL } }
88 sql1 { DROP TABLE IF EXISTS t1 }
89 sql2 { CREATE TABLE t1(x) }
90 sql1 { DROP TABLE IF EXISTS t1 }
91 sql2 { SELECT name FROM sqlite_master WHERE type = 'table' }
92 } {}
93  
94 # INDEX objects.
95 #
96 do_test 2.$tn.2 {
97 sql1 { CREATE TABLE t2(x) }
98 sql1 { DROP INDEX IF EXISTS i2 }
99 sql2 { CREATE INDEX i2 ON t2(x) }
100 sql1 { DROP INDEX IF EXISTS i2 }
101 sql2 { SELECT name FROM sqlite_master WHERE type = 'index' }
102 } {}
103  
104 # VIEW objects.
105 #
106 do_test 2.$tn.3 {
107 sql1 { DROP VIEW IF EXISTS v1 }
108 sql2 { CREATE VIEW v1 AS SELECT * FROM t2 }
109 sql1 { DROP VIEW IF EXISTS v1 }
110 sql2 { SELECT name FROM sqlite_master WHERE type = 'view' }
111 } {}
112  
113 # TRIGGER objects.
114 #
115 do_test 2.$tn.4 {
116 sql1 { DROP TRIGGER IF EXISTS tr1 }
117 sql2 { CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN SELECT 1; END }
118 sql1 { DROP TRIGGER IF EXISTS tr1 }
119 sql2 { SELECT name FROM sqlite_master WHERE type = 'trigger' }
120 } {}
121 }
122  
123 # This block of tests is targeted at DROP XXX IF EXISTS statements with
124 # attached databases.
125 #
126 do_multiclient_test tn {
127  
128 forcedelete test.db2
129 do_test 3.$tn.0 {
130 sql1 { ATTACH 'test.db2' AS aux }
131 sql2 { ATTACH 'test.db2' AS aux }
132 } {}
133  
134 # TABLE objects.
135 #
136 do_test 3.$tn.1.1 {
137 sql1 { DROP TABLE IF EXISTS aux.t1 }
138 sql2 { CREATE TABLE aux.t1(x) }
139 sql1 { DROP TABLE IF EXISTS aux.t1 }
140 sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'table' }
141 } {}
142 do_test 3.$tn.1.2 {
143 sql1 { DROP TABLE IF EXISTS t1 }
144 sql2 { CREATE TABLE aux.t1(x) }
145 sql1 { DROP TABLE IF EXISTS t1 }
146 sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'table' }
147 } {}
148  
149 # INDEX objects.
150 #
151 do_test 3.$tn.2.1 {
152 sql1 { CREATE TABLE aux.t2(x) }
153 sql1 { DROP INDEX IF EXISTS aux.i2 }
154 sql2 { CREATE INDEX aux.i2 ON t2(x) }
155 sql1 { DROP INDEX IF EXISTS aux.i2 }
156 sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'index' }
157 } {}
158 do_test 3.$tn.2.2 {
159 sql1 { DROP INDEX IF EXISTS i2 }
160 sql2 { CREATE INDEX aux.i2 ON t2(x) }
161 sql1 { DROP INDEX IF EXISTS i2 }
162 sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'index' }
163 } {}
164  
165 # VIEW objects.
166 #
167 do_test 3.$tn.3.1 {
168 sql1 { DROP VIEW IF EXISTS aux.v1 }
169 sql2 { CREATE VIEW aux.v1 AS SELECT * FROM t2 }
170 sql1 { DROP VIEW IF EXISTS aux.v1 }
171 sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'view' }
172 } {}
173 do_test 3.$tn.3.2 {
174 sql1 { DROP VIEW IF EXISTS v1 }
175 sql2 { CREATE VIEW aux.v1 AS SELECT * FROM t2 }
176 sql1 { DROP VIEW IF EXISTS v1 }
177 sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'view' }
178 } {}
179  
180 # TRIGGER objects.
181 #
182 do_test 3.$tn.4.1 {
183 sql1 { DROP TRIGGER IF EXISTS aux.tr1 }
184 sql2 { CREATE TRIGGER aux.tr1 AFTER INSERT ON t2 BEGIN SELECT 1; END }
185 sql1 { DROP TRIGGER IF EXISTS aux.tr1 }
186 sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'trigger' }
187 } {}
188 do_test 3.$tn.4.2 {
189 sql1 { DROP TRIGGER IF EXISTS tr1 }
190 sql2 { CREATE TRIGGER aux.tr1 AFTER INSERT ON t2 BEGIN SELECT 1; END }
191 sql1 { DROP TRIGGER IF EXISTS tr1 }
192 sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'trigger' }
193 } {}
194 }
195 }
196  
197  
198 finish_test