gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/exists.test (about)

     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    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  
   199  finish_test