AryaWu/sqlite

1# 2001 September 152#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# This file implements regression tests for SQLite library.  The12# focus of this file is testing the SELECT statement.13#14# $Id: select1.test,v 1.70 2009/05/28 01:00:56 drh Exp $15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18set ::testprefix fordelete19 20# This function returns a list of the tables or indexes opened with 21# OP_OpenWrite instructions when the SQL statement passed as the only 22# argument is executed. If the OPFLAG_FORDELETE flag is specified on23# the OP_OpenWrite, an asterix is appended to the object name. The list 24# is sorted in [lsort] order before it is returned.25#26proc analyze_delete_program {sql} {27  # Build a map from root page to table/index name.28  db eval {29    SELECT name, rootpage FROM sqlite_master30  } {31    set T($rootpage) $name32  }33  34  # For each OpenWrite instruction generated for the proposed DELETE35  # statement, add the following array entries:36  #37  #   $M(<cursor number>) -> <object name>38  #   $O(<object name>)   -> "*" | ""39  #40  # The O() entry is set to "*" if the BTREE_FORDELETE flag is specified,41  # or "" otherwise.42  #43  db eval "EXPLAIN $sql" R {44    if {$R(opcode)=="OpenWrite"} {45      set root $R(p2)46      set csr $R(p1)47      if {[info exists T($root)]} { set M($csr) $T($root) }48 49      set obj $T($root)50      set O($obj) ""51      if {$R(p5) & 0x08} { 52        set O($obj) *53      } else {54        set O($obj) ""55      }56    }57  }58 59  db eval "EXPLAIN $sql" R {60    if {$R(opcode) == "Delete"} {61      set csr $R(p1)62      if {[info exists M($csr)]} {63        set idxdelete [expr {("0x$R(p5)" & 0x04) ? 1 : 0}]64        if {$idxdelete} {65          append O($M($csr)) "+"66        }67      }68    }69  }70 71  set res [list]72  foreach {k v} [array get O] {73    lappend res "${k}${v}"74  }75 76  lsort $res77}78 79proc do_adp_test {tn sql res} {80  uplevel [list do_test $tn [list analyze_delete_program $sql] [list {*}$res]]81}82 83do_execsql_test 1.0 {84  CREATE TABLE t1(a PRIMARY KEY, b);85}86 87foreach {tn sql res} {88  1 { DELETE FROM t1 WHERE a=?}          { sqlite_autoindex_t1_1  t1*+ }89  2 { DELETE FROM t1 WHERE a=? AND b=? } { sqlite_autoindex_t1_1  t1+  }90  3 { DELETE FROM t1 WHERE a>? }         { sqlite_autoindex_t1_1  t1*+ }91  4 { DELETE FROM t1 WHERE rowid=? }     { sqlite_autoindex_t1_1*  t1  }92} {93  do_adp_test 1.$tn $sql $res94}95 96do_execsql_test 2.0 {97  CREATE TABLE t2(a, b, c);98  CREATE INDEX t2a ON t2(a);99  CREATE INDEX t2b ON t2(b);100  CREATE INDEX t2c ON t2(c);101}102foreach {tn sql res} {103  1 { DELETE FROM t2 WHERE a=?}          { t2*+ t2a t2b* t2c* }104  2 { DELETE FROM t2 WHERE a=? AND +b=?} { t2+ t2a t2b* t2c* }105  3 { DELETE FROM t2 WHERE a=? OR b=?}   { t2 t2a* t2b* t2c* }106  4 { DELETE FROM t2 WHERE +a=? }        { t2 t2a* t2b* t2c* }107  5 { DELETE FROM t2 WHERE rowid=? }     { t2 t2a* t2b* t2c* }108} {109  do_adp_test 2.$tn $sql $res110}111 112#-------------------------------------------------------------------------113# Test that a record that consists of the bytes:114#115#   0x01 0x00116#117# is interpreted by OP_Column as a vector of NULL values (assuming the 118# default column values are NULL). Also test that:119#120#   0x00121#122# is handled in the same way.123#124do_execsql_test 3.0 {125  CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c, d);126  CREATE TABLE x2(a INTEGER PRIMARY KEY, b, c, d);127}128 129do_test 3.1 {130  set root [db one { SELECT rootpage FROM sqlite_master WHERE name = 'x1' }]131  db eval { 132    BEGIN IMMEDIATE;133  }134  set bt [btree_from_db db]135  set csr [btree_cursor $bt $root 1]136  btree_insert $csr 5 "\000"137  btree_close_cursor $csr138  db eval { COMMIT }139 140  db eval {141    SELECT * FROM x1;142  }143} {5 {} {} {}}144 145do_test 3.2 {146  set root [db one { SELECT rootpage FROM sqlite_master WHERE name = 'x2' }]147  db eval { 148    BEGIN IMMEDIATE;149  }150  set bt [btree_from_db db]151  set csr [btree_cursor $bt $root 1]152  btree_insert $csr 6 "\000"153  btree_close_cursor $csr154  db eval { COMMIT }155 156  db eval {157    SELECT * FROM x2;158  }159} {6 {} {} {}}160 161 162#-------------------------------------------------------------------------163#164reset_db 165do_execsql_test 4.0 {166  CREATE TABLE log(x);167  CREATE TABLE p1(one PRIMARY KEY, two);168 169  CREATE TRIGGER tr_bd BEFORE DELETE ON p1 BEGIN170    INSERT INTO log VALUES('delete');171  END;172  INSERT INTO p1 VALUES('a', 'A'), ('b', 'B'), ('c', 'C');173  DELETE FROM p1 WHERE one = 'a';174}175 176reset_db177do_execsql_test 4.1 {178  BEGIN TRANSACTION;179  CREATE TABLE tbl(a PRIMARY KEY, b, c);180  CREATE TABLE log(a, b, c);181  INSERT INTO "tbl" VALUES(1,2,3);182  CREATE TRIGGER the_trigger BEFORE DELETE ON tbl BEGIN 183    INSERT INTO log VALUES(1, 2,3);184  END;185  COMMIT;186  DELETE FROM tbl WHERE a=1;187}188 189reset_db190do_execsql_test 5.1 {191  PRAGMA foreign_keys = 1;192  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);193  CREATE TABLE t2(194      c INTEGER PRIMARY KEY,195      d INTEGER DEFAULT 1 REFERENCES t1 ON DELETE SET DEFAULT196  );197} {}198do_execsql_test 5.2 {199  INSERT INTO t1 VALUES(1, 'one');200  INSERT INTO t1 VALUES(2, 'two');201  INSERT INTO t2 VALUES(1, 2);202  SELECT * FROM t2;203} {1 2}204do_execsql_test 5.3 {205  DELETE FROM t1 WHERE a = 2;206} {}207 208 209finish_test210