Annotation of embedaddon/sqlite3/test/exists.test, revision 1.1.1.1
1.1 misho 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 * 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to exists.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test