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

     1  # 2015-01-30
     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  #
    12  # This file implements tests for SQLite library.
    13  #
    14  # The focus of this file is adding extra entries in the symbol table
    15  # using sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER) and verifying that
    16  # SQLite handles those as expected.
    17  #
    18  
    19  set testdir [file dirname $argv0]
    20  source $testdir/tester.tcl
    21  set testprefix imposter
    22  
    23  # Create a bunch of data to sort against
    24  #
    25  do_test imposter-1.0 {
    26    execsql {
    27      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d NOT NULL);
    28      CREATE INDEX t1b ON t1(b);
    29      CREATE UNIQUE INDEX t1c ON t1(c);
    30      WITH RECURSIVE c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<30)
    31        INSERT INTO t1(a,b,c,d) SELECT i,1000+i,2000+i,3000+i FROM c;
    32    }
    33    set t1_root [db one {SELECT rootpage FROM sqlite_master WHERE name='t1'}]
    34    set t1b_root [db one {SELECT rootpage FROM sqlite_master WHERE name='t1b'}]
    35    set t1c_root [db one {SELECT rootpage FROM sqlite_master WHERE name='t1c'}]
    36  
    37    # Create an imposter table that uses the same b-tree as t1 but which does
    38    # not have the indexes
    39    #
    40    sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $t1_root
    41    db eval {CREATE TABLE xt1(a,b,c,d)}
    42  
    43    # And create an imposter table for the t1c index.
    44    sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $t1c_root
    45    db eval {CREATE TABLE xt1c(c,rowid,PRIMARY KEY(c,rowid))WITHOUT ROWID;}
    46  
    47    # Go out of imposter mode for now.
    48    sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0
    49  
    50    # Create triggers to record changes to xt1.
    51    #
    52    db eval {
    53      CREATE TEMP TABLE chnglog(desc TEXT);
    54      CREATE TEMP TRIGGER xt1_del AFTER DELETE ON xt1 BEGIN
    55        INSERT INTO chnglog VALUES(
    56             printf('DELETE t1: rowid=%d, a=%s, b=%s, c=%s, d=%s',
    57                    old.rowid, quote(old.a), quote(old.b), quote(old.c),
    58                    quote(old.d)));
    59      END;
    60      CREATE TEMP TRIGGER xt1_ins AFTER INSERT ON xt1 BEGIN
    61        INSERT INTO chnglog VALUES(
    62             printf('INSERT t1:  rowid=%d, a=%s, b=%s, c=%s, d=%s',
    63                    new.rowid, quote(new.a), quote(new.b), quote(new.c),
    64                    quote(new.d)));
    65      END;
    66    }
    67  } {}
    68  
    69  # The xt1 table has separate xt1.rowid and xt1.a columns.  The xt1.rowid
    70  # column corresponds to t1.rowid and t1.a, but the xt1.a column is always
    71  # NULL
    72  #
    73  do_execsql_test imposter-1.1 {
    74    SELECT rowid FROM xt1 WHERE a IS NOT NULL;
    75  } {}
    76  do_execsql_test imposter-1.2 {
    77    SELECT a,b,c,d FROM t1 EXCEPT SELECT rowid,b,c,d FROM xt1;
    78    SELECT rowid,b,c,d FROM xt1 EXCEPT SELECT a,b,c,d FROM t1;
    79  } {}
    80  
    81  
    82  # Make changes via the xt1 shadow table.  This will not update the
    83  # indexes on t1 nor check the uniqueness constraint on t1.c nor check
    84  # the NOT NULL constraint on t1.d, resulting in a logically inconsistent
    85  # database.
    86  #
    87  do_execsql_test imposter-1.3 {
    88    DELETE FROM xt1 WHERE rowid=5;
    89    INSERT INTO xt1(rowid,a,b,c,d) VALUES(99,'hello',1099,2022,NULL);
    90    SELECT * FROM chnglog ORDER BY rowid;
    91  } [list \
    92    {DELETE t1: rowid=5, a=NULL, b=1005, c=2005, d=3005} \
    93    {INSERT t1:  rowid=99, a='hello', b=1099, c=2022, d=NULL} \
    94  ]
    95  
    96  do_execsql_test imposter-1.4a {
    97    PRAGMA integrity_check;
    98  } {/NULL value in t1.d/}
    99  do_execsql_test imposter-1.4b {
   100    PRAGMA integrity_check;
   101  } {/row # missing from index t1b/}
   102  do_execsql_test imposter-1.4c {
   103    PRAGMA integrity_check;
   104  } {/row # missing from index t1c/}
   105  
   106  # Cleanup the corruption.
   107  # Then demonstrate that the xt1c imposter table can insert non-unique
   108  # and NULL values into the UNIQUE index.
   109  #
   110  do_execsql_test imposter-2.0 {
   111    DELETE FROM t1;
   112    WITH RECURSIVE c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<10)
   113     INSERT INTO t1(a,b,c,d) SELECT i,i,i,i FROM c;
   114    UPDATE xt1c SET c=NULL WHERE rowid=5;
   115    PRAGMA integrity_check;
   116  } {/row # missing from index t1c/}
   117  
   118  do_execsql_test imposter-2.1 {
   119    DELETE FROM t1;
   120    WITH RECURSIVE c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<10)
   121     INSERT INTO t1(a,b,c,d) SELECT i,i,i,i FROM c;
   122    UPDATE xt1c SET c=99 WHERE rowid IN (5,7,9);
   123    SELECT c FROM t1 ORDER BY c;
   124  } {1 2 3 4 6 8 10 99 99 99}
   125  do_execsql_test imposter-2.2 {
   126    UPDATE xt1 SET c=99 WHERE rowid IN (5,7,9);
   127    PRAGMA integrity_check;
   128  } {/non-unique entry in index t1c/}
   129  
   130  # Erase the imposter tables
   131  #
   132  do_test imposter-3.1 {
   133    sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1
   134    db eval {
   135      DELETE FROM t1 WHERE rowid IN (5,7,9);
   136      PRAGMA integrity_check;
   137    }
   138  } {ok}
   139  
   140  
   141  finish_test