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 199
