AryaWu/sqlite
0
1# 2011 April 92#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is testing the various schema modification statements13# that feature "IF EXISTS" or "IF NOT EXISTS" clauses.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18source $testdir/lock_common.tcl19 20 21foreach jm {rollback wal} {22 if {![wal_is_capable] && $jm=="wal"} continue23 24 set testprefix exists-$jm25 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 with125 # attached databases.126 #127 do_multiclient_test tn {128 129 forcedelete test.db2130 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_test200