Return to exists.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    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