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

     1  # 2010 September 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. The
    12  # focus of this file is testing that a trigger may have the same
    13  # name as an index, view or table in the same database.
    14  #
    15  
    16  set testdir [file dirname $argv0]
    17  source $testdir/tester.tcl
    18  
    19  #--------------------------------------------------------------------------
    20  # Test organization:
    21  #
    22  #   schema4-1.*: Dropping and creating triggers and other objects where
    23  #     triggers and at least on other object share a name.
    24  #
    25  #   schema4-2.*: Renaming tables where there is a trigger that shares the
    26  #     name of the table or one of its indices.
    27  #
    28  
    29  do_execsql_test schema4-1.1 {
    30    CREATE TABLE log(x, a, b);
    31    CREATE TABLE tbl(a, b);
    32  
    33    CREATE TABLE t1(a, b);
    34    CREATE VIEW v1 AS SELECT * FROM tbl;
    35    CREATE INDEX i1 ON tbl(a);
    36  } {}
    37  
    38  do_execsql_test schema4-1.2 {
    39    CREATE TRIGGER t1 AFTER INSERT ON tbl BEGIN
    40      INSERT INTO log VALUES('after insert', new.a, new.b);
    41    END;
    42    CREATE TRIGGER v1 AFTER UPDATE ON tbl BEGIN
    43      INSERT INTO log VALUES('after update', new.a, new.b);
    44    END;
    45    CREATE TRIGGER i1 AFTER DELETE ON tbl BEGIN
    46      INSERT INTO log VALUES('after delete', old.a, old.b);
    47    END;
    48  } {}
    49  
    50  do_execsql_test schema4-1.3 {
    51    INSERT INTO tbl VALUES(1, 2);
    52    UPDATE tbl SET b=a+b, a=a+1;
    53    DELETE FROM tbl;
    54  
    55    SELECT x, a, b FROM log;
    56  } {{after insert} 1 2 {after update} 2 3 {after delete} 2 3}
    57  
    58  do_execsql_test schema4-1.4 {
    59    DELETE FROM log;
    60  
    61    DROP INDEX i1;
    62    DROP TABLE t1;
    63    DROP VIEW v1;
    64  
    65    INSERT INTO tbl VALUES(1, 2);
    66    UPDATE tbl SET b=a+b, a=a+1;
    67    DELETE FROM tbl;
    68  
    69    SELECT x, a, b FROM log;
    70  } {{after insert} 1 2 {after update} 2 3 {after delete} 2 3}
    71  
    72  db close
    73  sqlite3 db test.db
    74  
    75  do_execsql_test schema4-1.5 {
    76    DELETE FROM log;
    77    INSERT INTO tbl VALUES(1, 2);
    78    UPDATE tbl SET b=a+b, a=a+1;
    79    DELETE FROM tbl;
    80    SELECT x, a, b FROM log;
    81  } {{after insert} 1 2 {after update} 2 3 {after delete} 2 3}
    82  
    83  do_execsql_test schema4-1.6 {
    84    CREATE TABLE t1(a, b);
    85    CREATE VIEW v1 AS SELECT * FROM tbl;
    86    CREATE INDEX i1 ON tbl(a);
    87  } {}
    88  
    89  ifcapable fts3 {
    90    do_execsql_test schema4-1.7 {
    91      DROP TABLE t1;
    92      CREATE VIRTUAL TABLE t1 USING fts3;
    93    } {}
    94  
    95    do_execsql_test schema4-1.8 {
    96      DELETE FROM log;
    97      DROP TABLE t1;
    98      INSERT INTO tbl VALUES(1, 2);
    99      UPDATE tbl SET b=a+b, a=a+1;
   100      DELETE FROM tbl;
   101      SELECT x, a, b FROM log;
   102    } {{after insert} 1 2 {after update} 2 3 {after delete} 2 3}
   103  }
   104  
   105  ifcapable altertable {
   106    drop_all_tables
   107    do_execsql_test schema4-2.1 {
   108      CREATE TABLE log(x, a, b);
   109      CREATE TABLE tbl(a, b);
   110    
   111      CREATE TABLE t1(a, b);
   112      CREATE INDEX i1 ON t1(a, b);
   113    } {}
   114    
   115    do_execsql_test schema4-2.2 {
   116      CREATE TRIGGER t1 AFTER INSERT ON tbl BEGIN
   117        INSERT INTO log VALUES('after insert', new.a, new.b);
   118      END;
   119      CREATE TRIGGER i1 AFTER DELETE ON tbl BEGIN
   120        INSERT INTO log VALUES('after delete', old.a, old.b);
   121      END;
   122    } {}
   123  
   124    do_execsql_test schema4-2.3 { ALTER TABLE t1 RENAME TO t2 } {}
   125  
   126    do_execsql_test schema4-2.4 { 
   127      INSERT INTO tbl VALUES('a', 'b');
   128      DELETE FROM tbl;
   129      SELECT * FROM log;
   130    } {{after insert} a b {after delete} a b}
   131  
   132    db close
   133    sqlite3 db test.db
   134  
   135    do_execsql_test schema4-2.5 { 
   136      DELETE FROM log;
   137      INSERT INTO tbl VALUES('c', 'd');
   138      DELETE FROM tbl;
   139      SELECT * FROM log;
   140    } {{after insert} c d {after delete} c d}
   141  
   142    do_execsql_test schema4-2.6 {
   143      CREATE TEMP TRIGGER x1 AFTER UPDATE ON tbl BEGIN
   144        INSERT INTO log VALUES('after update', new.a, new.b);
   145      END;
   146  
   147      CREATE TEMP TABLE x1(x);
   148      INSERT INTO x1 VALUES(123);
   149    } {}
   150  
   151    do_execsql_test schema4-2.8 {
   152      select sql from temp.sqlite_master WHERE type='table';
   153    } {{CREATE TABLE x1(x)}}
   154  
   155    do_execsql_test schema4-2.7 { ALTER TABLE tbl RENAME TO tbl2 } {}
   156  
   157    do_execsql_test schema4-2.9 {
   158      select sql from sqlite_temp_master WHERE type='table';
   159    } {{CREATE TABLE x1(x)}}
   160  
   161    do_execsql_test schema4-2.10 { 
   162      DELETE FROM log;
   163      INSERT INTO tbl2 VALUES('e', 'f');
   164      UPDATE tbl2 SET a='g', b='h';
   165      DELETE FROM tbl2;
   166      SELECT * FROM log;
   167    } {{after insert} e f {after update} g h {after delete} g h}
   168  
   169    do_execsql_test schema4-2.11 {
   170      INSERT INTO x1 VALUES(456);
   171      SELECT * FROM x1
   172    } {123 456}
   173  }
   174  
   175  finish_test