github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/schema3.test (about)

     1  # 2010 Jun 28
     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.
    12  #
    13  
    14  set testdir [file dirname $argv0]
    15  source $testdir/tester.tcl
    16  source $testdir/malloc_common.tcl
    17  source $testdir/lock_common.tcl
    18  
    19  # This block tests that if one client modifies the database schema, a
    20  # second client updates its internal cache of the database schema before
    21  # executing any queries. Specifically, it does not return a "no such column"
    22  # or "no such table" error if the table or column in question does exist
    23  # but was added after the second client loaded its cache of the database
    24  # schema.
    25  #
    26  # Types of schema modifications:
    27  #
    28  #   1. Adding a database table.
    29  #   2. Adding a database view.
    30  #   3. Adding a database index.
    31  #   4. Adding a database trigger.
    32  #   5. Adding a column to an existing table (ALTER TABLE).
    33  #
    34  do_multiclient_test tn {
    35  
    36    # Have connections [db1] and [db2] load the current database schema.
    37    #
    38    sql1 { SELECT * FROM sqlite_master }
    39    sql2 { SELECT * FROM sqlite_master }
    40  
    41    foreach {tn2 c1 c2} {
    42      1  { CREATE TABLE t1(a, b) }       { SELECT * FROM t1            }
    43      2  { CREATE TABLE t2(a, b) }       { UPDATE t2 SET a = b         }
    44      3  { CREATE TABLE t3(a, b) }       { DELETE FROM t3              }
    45      4  { CREATE TABLE t4(a, b) }       { INSERT INTO t4 VALUES(1, 2) }
    46      5  { CREATE TABLE t5(a, b) }       { DROP TABLE t5 }
    47      6  { CREATE TABLE t6(a, b) }       { CREATE INDEX i1 ON t6(a) }
    48  
    49      7  { ALTER TABLE t1 ADD COLUMN c } { SELECT a, b, c FROM t1 }
    50      8  { ALTER TABLE t2 ADD COLUMN c } { UPDATE t2 SET a = c }
    51      9  { ALTER TABLE t2 ADD COLUMN d } { UPDATE t2 SET d = c }
    52      10 { ALTER TABLE t3 ADD COLUMN c } { DELETE FROM t3 WHERE c>10 }
    53      11 { ALTER TABLE t4 ADD COLUMN c } { INSERT INTO t4(a,b,c) VALUES(1,2,3) }
    54      12 { ALTER TABLE t6 ADD COLUMN c } { CREATE INDEX i2 ON t6(c) }
    55      13 { ALTER TABLE t6 ADD COLUMN d } { 
    56           CREATE TRIGGER tr1 AFTER UPDATE OF d ON t6 BEGIN
    57             SELECT 1, 2, 3;
    58           END;
    59      }
    60  
    61      14 { CREATE INDEX i3 ON t1(a) }    { DROP INDEX i3 }
    62      15 { CREATE INDEX i4 ON t2(a) }    {
    63           SELECT * FROM t2 INDEXED BY i4 ORDER BY a
    64      }
    65  
    66      16 { CREATE TRIGGER tr2 AFTER INSERT ON t3 BEGIN SELECT 1 ; END } {
    67           DROP TRIGGER tr2
    68      }
    69  
    70      17  { CREATE VIEW v1 AS SELECT * FROM t1 } { SELECT a,b,c   FROM v1 }
    71      18  { ALTER TABLE t1 ADD COLUMN d        } { SELECT a,b,c,d FROM v1 }
    72  
    73      19  { CREATE TABLE t7(a, b) } {
    74            DROP TABLE IF EXISTS t7; CREATE TABLE t7(c, d);
    75      }
    76      20  { CREATE INDEX i5 ON t7(c, d) } {
    77            DROP INDEX IF EXISTS i5; CREATE INDEX i5 ON t7(c)
    78      }
    79      21  { CREATE TRIGGER tr3 BEFORE DELETE ON t7 BEGIN SELECT 1, 2, 3 ; END } {
    80            DROP TRIGGER IF EXISTS tr3;
    81            CREATE TRIGGER tr3 AFTER INSERT ON t7 BEGIN SELECT 1, 2, 3 ; END 
    82      }
    83  
    84      22  { CREATE TABLE t8(a, b) }       {
    85           CREATE TRIGGER tr4 AFTER UPDATE OF a ON t8 BEGIN
    86             SELECT 1, 2, 3;
    87           END;
    88      }
    89    } {
    90      do_test schema3-1.$tn.$tn2 {
    91        sql1 $c1
    92        sql2 $c2
    93      } {}
    94    }
    95  }
    96  
    97  finish_test