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

     1  # 2020 April 29
     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  
    13  set testdir [file dirname $argv0]
    14  source $testdir/tester.tcl
    15  set testprefix upfromfault
    16  
    17  foreach {tn sql} {
    18    1 {
    19      CREATE TABLE t1(x PRIMARY KEY, y, z UNIQUE);
    20      CREATE INDEX t1y ON t1(y);
    21    }
    22    2 {
    23      CREATE TABLE t1(x PRIMARY KEY, y, z UNIQUE) WITHOUT ROWID;
    24      CREATE INDEX t1y ON t1(y);
    25    }
    26    3 {
    27      CREATE TABLE t1(x, y, z UNIQUE, PRIMARY KEY(x,y)) WITHOUT ROWID;
    28    }
    29    4 {
    30      CREATE VIRTUAL TABLE t1 USING fts5(x, y, z);
    31    }
    32    5 {
    33      CREATE TABLE real(x, y, z);
    34      CREATE VIEW t1 AS SELECT * FROM real;
    35      CREATE TRIGGER t1_insert INSTEAD OF INSERT ON t1 BEGIN
    36        INSERT INTO real VALUES(new.x, new.y, new.z);
    37      END;
    38      CREATE TRIGGER t1_update INSTEAD OF UPDATE ON t1 BEGIN
    39        INSERT INTO log VALUES(old.z || '->' || new.z);
    40        UPDATE real SET y=new.y, z=new.z WHERE x=old.x;
    41      END;
    42    }
    43  } {
    44  if {$tn<5} continue
    45    reset_db
    46  
    47    ifcapable !fts5 { if {$tn==4} continue }
    48  
    49    execsql $sql
    50    do_execsql_test 1.$tn.0 {
    51      CREATE TABLE log(t TEXT);
    52  
    53      INSERT INTO t1 VALUES(1, 'i',   'one');
    54      INSERT INTO t1 VALUES(2, 'ii',  'two');
    55      INSERT INTO t1 VALUES(3, 'iii', 'three');
    56      INSERT INTO t1 VALUES(4, 'iv',  'four');
    57    }
    58    if {$tn!=4 && $tn!=5} {
    59      do_execsql_test 1.$tn.0b {
    60        CREATE TRIGGER tr1 BEFORE UPDATE ON t1 BEGIN
    61          INSERT INTO log VALUES(old.z || '->' || new.z);
    62        END;
    63        CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN
    64          INSERT INTO log VALUES(old.y || '->' || new.y);
    65        END;
    66      }
    67    }
    68    
    69    faultsim_save_and_close
    70  
    71    do_faultsim_test 1.$tn -prep {
    72      faultsim_restore_and_reopen
    73      execsql { SELECT * FROM t1 }
    74    } -body {
    75      execsql {
    76        WITH data(k, v) AS (
    77            VALUES(3, 'thirty'), (1, 'ten')
    78        )
    79        UPDATE t1 SET z=v FROM data WHERE x=k;
    80      }
    81    } -test {
    82      faultsim_test_result {0 {}} {1 {vtable constructor failed: t1}}
    83      if {$testrc==0} {
    84        set res [execsql { SELECT * FROM t1 }]
    85        if {$res!="1 i ten 2 ii two 3 iii thirty 4 iv four"} {
    86          error "unexpected result: $res"
    87        }
    88      }
    89    }
    90  }
    91  
    92  reset_db
    93  do_execsql_test 2.0 {
    94    CREATE TABLE t1(a, b, c);
    95    CREATE TABLE t2(x, y, z);
    96  }
    97  faultsim_save_and_close
    98  do_faultsim_test 2.1 -prep {
    99    faultsim_restore_and_reopen
   100  } -body {
   101    execsql {
   102      CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
   103        UPDATE t2 SET x=a FROM t1 WHERE c=z;
   104      END;
   105    }
   106  } -test {
   107      faultsim_test_result {0 {}}
   108  }
   109  
   110  faultsim_restore_and_reopen
   111  do_execsql_test 2.2 {
   112    CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
   113      UPDATE t1 SET a=x FROM t2 WHERE c=z;
   114    END;
   115  
   116    INSERT INTO t2 VALUES(1, 1, 1);
   117    INSERT INTO t2 VALUES(2, 2, 2);
   118    INSERT INTO t2 VALUES(3, 3, 3);
   119  } 
   120  faultsim_save_and_close
   121  
   122  do_faultsim_test 2.3 -prep {
   123    faultsim_restore_and_reopen
   124  } -body {
   125    execsql {
   126      INSERT INTO t1 VALUES(NULL, NULL, 1), (NULL, NULL, 3);
   127    }
   128  } -test {
   129    faultsim_test_result {0 {}}
   130    if {$testrc==0} {
   131      set res [execsql { SELECT * FROM t1 }]
   132      if {$res!="1 {} 1 3 {} 3"} {
   133        error "unexpected result: $res"
   134      }
   135    }
   136  }
   137  
   138  
   139  finish_test