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 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18source $testdir/lock_common.tcl 19 20 21foreach jm {rollback wal} { 22 if {![wal_is_capable] && $jm=="wal"} continue 23 24 set testprefix exists-$jm 25 26 # This block of tests is targeted at CREATE XXX IF NOT EXISTS statements. 27 # 28 do_multiclient_test tn { 29 30 # TABLE objects. 31 # 32 do_test 1.$tn.1.1 { 33 if {$jm == "wal"} { sql2 { PRAGMA journal_mode = WAL } } 34 sql2 { CREATE TABLE t1(x) } 35 sql1 { CREATE TABLE IF NOT EXISTS t1(a, b) } 36 sql2 { DROP TABLE t1 } 37 sql1 { CREATE TABLE IF NOT EXISTS t1(a, b) } 38 sql2 { SELECT name FROM sqlite_master WHERE type = 'table' } 39 } {t1} 40 41 do_test 1.$tn.1.2 { 42 sql2 { CREATE TABLE t2(x) } 43 sql1 { CREATE TABLE IF NOT EXISTS t2 AS SELECT * FROM t1 } 44 sql2 { DROP TABLE t2 } 45 sql1 { CREATE TABLE IF NOT EXISTS t2 AS SELECT * FROM t1 } 46 sql2 { SELECT name FROM sqlite_master WHERE type = 'table' } 47 } {t1 t2} 48 49 50 # INDEX objects. 51 # 52 do_test 1.$tn.2 { 53 sql2 { CREATE INDEX i1 ON t1(a) } 54 sql1 { CREATE INDEX IF NOT EXISTS i1 ON t1(a, b) } 55 sql2 { DROP INDEX i1 } 56 sql1 { CREATE INDEX IF NOT EXISTS i1 ON t1(a, b) } 57 sql2 { SELECT name FROM sqlite_master WHERE type = 'index' } 58 } {i1} 59 60 # VIEW objects. 61 # 62 do_test 1.$tn.3 { 63 sql2 { CREATE VIEW v1 AS SELECT * FROM t1 } 64 sql1 { CREATE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1 } 65 sql2 { DROP VIEW v1 } 66 sql1 { CREATE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1 } 67 sql2 { SELECT name FROM sqlite_master WHERE type = 'view' } 68 } {v1} 69 70 # TRIGGER objects. 71 # 72 do_test $tn.4 { 73 sql2 { CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END } 74 sql1 { CREATE TRIGGER IF NOT EXISTS tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END } 75 sql2 { DROP TRIGGER tr1 } 76 sql1 { CREATE TRIGGER IF NOT EXISTS tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END } 77 sql2 { SELECT name FROM sqlite_master WHERE type = 'trigger' } 78 } {tr1} 79 } 80 81 # This block of tests is targeted at DROP XXX IF EXISTS statements. 82 # 83 do_multiclient_test tn { 84 85 # TABLE objects. 86 # 87 do_test 2.$tn.1 { 88 if {$jm == "wal"} { sql1 { PRAGMA journal_mode = WAL } } 89 sql1 { DROP TABLE IF EXISTS t1 } 90 sql2 { CREATE TABLE t1(x) } 91 sql1 { DROP TABLE IF EXISTS t1 } 92 sql2 { SELECT name FROM sqlite_master WHERE type = 'table' } 93 } {} 94 95 # INDEX objects. 96 # 97 do_test 2.$tn.2 { 98 sql1 { CREATE TABLE t2(x) } 99 sql1 { DROP INDEX IF EXISTS i2 } 100 sql2 { CREATE INDEX i2 ON t2(x) } 101 sql1 { DROP INDEX IF EXISTS i2 } 102 sql2 { SELECT name FROM sqlite_master WHERE type = 'index' } 103 } {} 104 105 # VIEW objects. 106 # 107 do_test 2.$tn.3 { 108 sql1 { DROP VIEW IF EXISTS v1 } 109 sql2 { CREATE VIEW v1 AS SELECT * FROM t2 } 110 sql1 { DROP VIEW IF EXISTS v1 } 111 sql2 { SELECT name FROM sqlite_master WHERE type = 'view' } 112 } {} 113 114 # TRIGGER objects. 115 # 116 do_test 2.$tn.4 { 117 sql1 { DROP TRIGGER IF EXISTS tr1 } 118 sql2 { CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN SELECT 1; END } 119 sql1 { DROP TRIGGER IF EXISTS tr1 } 120 sql2 { SELECT name FROM sqlite_master WHERE type = 'trigger' } 121 } {} 122 } 123 124 # This block of tests is targeted at DROP XXX IF EXISTS statements with 125 # attached databases. 126 # 127 do_multiclient_test tn { 128 129 forcedelete test.db2 130 do_test 3.$tn.0 { 131 sql1 { ATTACH 'test.db2' AS aux } 132 sql2 { ATTACH 'test.db2' AS aux } 133 } {} 134 135 # TABLE objects. 136 # 137 do_test 3.$tn.1.1 { 138 sql1 { DROP TABLE IF EXISTS aux.t1 } 139 sql2 { CREATE TABLE aux.t1(x) } 140 sql1 { DROP TABLE IF EXISTS aux.t1 } 141 sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'table' } 142 } {} 143 do_test 3.$tn.1.2 { 144 sql1 { DROP TABLE IF EXISTS t1 } 145 sql2 { CREATE TABLE aux.t1(x) } 146 sql1 { DROP TABLE IF EXISTS t1 } 147 sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'table' } 148 } {} 149 150 # INDEX objects. 151 # 152 do_test 3.$tn.2.1 { 153 sql1 { CREATE TABLE aux.t2(x) } 154 sql1 { DROP INDEX IF EXISTS aux.i2 } 155 sql2 { CREATE INDEX aux.i2 ON t2(x) } 156 sql1 { DROP INDEX IF EXISTS aux.i2 } 157 sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'index' } 158 } {} 159 do_test 3.$tn.2.2 { 160 sql1 { DROP INDEX IF EXISTS i2 } 161 sql2 { CREATE INDEX aux.i2 ON t2(x) } 162 sql1 { DROP INDEX IF EXISTS i2 } 163 sql2 { SELECT * FROM aux.sqlite_master WHERE type = 'index' } 164 } {} 165 166 # VIEW objects. 167 # 168 do_test 3.$tn.3.1 { 169 sql1 { DROP VIEW IF EXISTS aux.v1 } 170 sql2 { CREATE VIEW aux.v1 AS SELECT * FROM t2 } 171 sql1 { DROP VIEW IF EXISTS aux.v1 } 172 sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'view' } 173 } {} 174 do_test 3.$tn.3.2 { 175 sql1 { DROP VIEW IF EXISTS v1 } 176 sql2 { CREATE VIEW aux.v1 AS SELECT * FROM t2 } 177 sql1 { DROP VIEW IF EXISTS v1 } 178 sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'view' } 179 } {} 180 181 # TRIGGER objects. 182 # 183 do_test 3.$tn.4.1 { 184 sql1 { DROP TRIGGER IF EXISTS aux.tr1 } 185 sql2 { CREATE TRIGGER aux.tr1 AFTER INSERT ON t2 BEGIN SELECT 1; END } 186 sql1 { DROP TRIGGER IF EXISTS aux.tr1 } 187 sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'trigger' } 188 } {} 189 do_test 3.$tn.4.2 { 190 sql1 { DROP TRIGGER IF EXISTS tr1 } 191 sql2 { CREATE TRIGGER aux.tr1 AFTER INSERT ON t2 BEGIN SELECT 1; END } 192 sql1 { DROP TRIGGER IF EXISTS tr1 } 193 sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'trigger' } 194 } {} 195 } 196} 197 198 199finish_test 200