AryaWu/sqlite

1# 2017 Jan 302#3# The author disclaims copyright to this source code.  In place of4# 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# The tests in this file focus on the pre-update hook.12# 13 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set ::testprefix hook217 18ifcapable !preupdate {19  finish_test20  return21}22 23#-------------------------------------------------------------------------24proc do_preupdate_test {tn sql x} {25  set X [list]26  foreach elem $x {lappend X $elem}27  uplevel do_test $tn [list "28    set ::preupdate \[list\]29    execsql { $sql }30    set ::preupdate31  "] [list $X]32}33 34proc preupdate_hook {args} {35  set type [lindex $args 0]36  eval lappend ::preupdate $args37  if {$type != "INSERT"} {38    for {set i 0} {$i < [db preupdate count]} {incr i} {39      lappend ::preupdate [db preupdate old $i]40    }41  }42  if {$type != "DELETE"} {43    for {set i 0} {$i < [db preupdate count]} {incr i} {44      set rc [catch { db preupdate new $i } v]45      lappend ::preupdate $v46    }47  }48}49 50#-------------------------------------------------------------------------51# Simple tests - INSERT, UPDATE and DELETE on a WITHOUT ROWID table.52#53db preupdate hook preupdate_hook54do_execsql_test 1.0 {55  CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT ROWID;56}57do_preupdate_test 1.1 {58  INSERT INTO t1 VALUES('one', 1);59} {60  INSERT main t1 0 0  one 161}62do_preupdate_test 1.2 {63  UPDATE t1 SET b=2 WHERE a='one';64} {65  UPDATE main t1 0 0  one 1 one 266}67do_preupdate_test 1.3 {68  DELETE FROM t1 WHERE a='one';69} {70  DELETE main t1 0 0  one 271}72 73#-------------------------------------------------------------------------74# Some more complex tests for the pre-update callback on WITHOUT ROWID75# tables.76#77#   2.1.1 - INSERT statement.78#   2.1.2 - INSERT INTO ... SELECT statement.79#   2.1.3 - REPLACE INTO ... (PK conflict)80#   2.1.4 - REPLACE INTO ... (other index conflicts)81#   2.1.5 - REPLACE INTO ... (both PK and other index conflicts)82#83#   2.2.1 - DELETE statement.84#   2.2.2 - DELETE statement that uses the truncate optimization.85#86#   2.3.1 - UPDATE statement.87#   2.3.2 - UPDATE statement that modifies the PK.88#   2.3.3 - UPDATE OR REPLACE ... (PK conflict).89#   2.3.4 - UPDATE OR REPLACE ... (other index conflicts)90#   2.3.4 - UPDATE OR REPLACE ... (both PK and other index conflicts)91#92do_execsql_test 2.0 {93  CREATE TABLE t2(a DEFAULT 4, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID;94  CREATE UNIQUE INDEX t2a ON t2(a);95}96 97do_preupdate_test 2.1.1 {98  INSERT INTO t2(b, c) VALUES(1, 1);99} {100  INSERT main t2 0 0  4 1 1101}102 103do_execsql_test 2.1.2.0 {104  CREATE TABLE d1(a DEFAULT 4, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID;105  CREATE UNIQUE INDEX d1a ON d1(a);106  INSERT INTO d1 VALUES(1, 2, 3);107  INSERT INTO d1 VALUES(11, 12, 13);108}109do_preupdate_test 2.1.2.1 {110  INSERT INTO t2 SELECT * FROM d1;111} {112  INSERT main t2 0 0  1 2 3113  INSERT main t2 0 0  11 12 13114}115do_preupdate_test 2.1.2.2 {116  INSERT INTO t2 SELECT a+20, b+20, c+20 FROM d1;117} {118  INSERT main t2 0 0  21 22 23119  INSERT main t2 0 0  31 32 33120}121do_execsql_test 2.1.2.3 {122  SELECT * FROM t2 ORDER BY b, c;123} {124  4 1 1125  1 2 3126  11 12 13127  21 22 23128  31 32 33129}130do_preupdate_test 2.1.3 {131  REPLACE INTO t2 VALUES(45, 22, 23);132} {133  DELETE main t2 0 0 21 22 23134  INSERT main t2 0 0 45 22 23135}136do_preupdate_test 2.1.4 {137  REPLACE INTO t2 VALUES(11, 100, 100);138} {139  DELETE main t2 0 0 11 12 13140  INSERT main t2 0 0 11 100 100141}142do_preupdate_test 2.1.5 {143  REPLACE INTO t2(c, b) VALUES(33, 32)144} {145  DELETE main t2 0 0 4 1 1 146  DELETE main t2 0 0 31 32 33147  INSERT main t2 0 0 4 32 33148}149 150do_execsql_test 2.2.0 {151  SELECT * FROM t2 ORDER BY b,c;152} {153  1    2   3 154  45  22  23 155  4   32  33 156  11 100 100157}158do_preupdate_test 2.2.1 {159  DELETE FROM t2 WHERE b=22;160} {161  DELETE main t2 0 0  45 22 23162}163do_preupdate_test 2.2.2 {164  DELETE FROM t2;165} {166  DELETE main t2 0 0 1 2 3 167  DELETE main t2 0 0 4 32 33 168  DELETE main t2 0 0 11 100 100169}170 171do_execsql_test 2.3.0 {172  CREATE TABLE t3(x, y PRIMARY KEY, z UNIQUE) WITHOUT ROWID;173  INSERT INTO t3 VALUES('a', 'b', 'c');174  INSERT INTO t3 VALUES('d', 'e', 'f');175 176  INSERT INTO t3 VALUES(1, 1, 1);177  INSERT INTO t3 VALUES(2, 2, 2);178  INSERT INTO t3 VALUES(3, 3, 3);179}180 181do_preupdate_test 2.3.1 {182  UPDATE t3 SET x=4 WHERE y IN ('b', 'e', 'x');183} {184  UPDATE main t3 0 0  a b c   4 b c185  UPDATE main t3 0 0  d e f   4 e f186}187 188do_preupdate_test 2.3.2 {189  UPDATE t3 SET y=y||y WHERE z IN('c', 'f');190} {191  UPDATE main t3 0 0  4 b c   4 bb c192  UPDATE main t3 0 0  4 e f   4 ee f193}194 195do_preupdate_test 2.3.3 {196  UPDATE OR REPLACE t3 SET y='bb' WHERE z='f'197} {198  DELETE main t3 0 0  4 bb c199  UPDATE main t3 0 0  4 ee f   4 bb f200}201 202do_preupdate_test 2.3.4 {203  UPDATE OR REPLACE t3 SET z=2 WHERE y=1;204} {205  DELETE main t3 0 0  2 2 2206  UPDATE main t3 0 0  1 1 1  1 1 2207}208 209do_preupdate_test 2.3.5 {210  UPDATE OR REPLACE t3 SET z=2, y='bb' WHERE y=3;211} {212  DELETE main t3 0 0  1 1 2213  DELETE main t3 0 0  4 bb f214  UPDATE main t3 0 0  3 3 3  3 bb 2215}216  217 218finish_test219