/ Check-in [bbd25cf1]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix tests in fordelete.test to test for the BTREE_AUXDELETE flag.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | fordelete-assert
Files: files | file ages | folders
SHA1: bbd25cf179df5bda1fe729928d6746248f06e46f
User & Date: dan 2016-01-27 16:17:41
Context
2016-01-27
16:26
Change the name of the BTREE_IDXDELETE flag to BTREE_AUXDELETE, to better reflect its purpose. check-in: 1d3bf6be user: drh tags: fordelete-assert
16:17
Fix tests in fordelete.test to test for the BTREE_AUXDELETE flag. check-in: bbd25cf1 user: dan tags: fordelete-assert
15:49
Add assert() statements on the nExtraDelete variable in vdbe.c to try to verify that the FORDELETE and IDXDELETE flags are being generated correctly. Those flags are not currently generated correctly, and so the assert()s trip on this check-in. check-in: dde1db0d user: drh tags: fordelete-assert
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/fordelete.test.

26
27
28
29
30
31
32
33
34
35







36
37




38

39













40
41
42







43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
proc analyze_delete_program {sql} {
  # Build a map from root page to table/index name.
  db eval {
    SELECT name, rootpage FROM sqlite_master
  } {
    set T($rootpage) $name
  }

  # Calculate the results.
  set res [list]







  db eval "EXPLAIN $sql" R {
    if {$R(opcode) == "OpenWrite"} {




      set obj $T($R(p2))

      if {"0x$R(p5)" & 0x08} { append obj *}













      lappend res $obj
    }
  }








  lsort $res
}

proc do_adp_test {tn sql res} {
  uplevel [list do_test $tn [list analyze_delete_program $sql] [list {*}$res]]
}

do_execsql_test 1.0 {
  CREATE TABLE t1(a PRIMARY KEY, b);
}

foreach {tn sql res} {
  1 { DELETE FROM t1 WHERE a=?}          { sqlite_autoindex_t1_1  t1* }
  2 { DELETE FROM t1 WHERE a=? AND b=? } { sqlite_autoindex_t1_1  t1 }
  3 { DELETE FROM t1 WHERE a>? }         { sqlite_autoindex_t1_1  t1* }
  4 { DELETE FROM t1 WHERE rowid=? }     { sqlite_autoindex_t1_1*  t1 }
} {
  do_adp_test 1.$tn $sql $res
}

do_execsql_test 2.0 {
  CREATE TABLE t2(a, b, c);
  CREATE INDEX t2a ON t2(a);
  CREATE INDEX t2b ON t2(b);
  CREATE INDEX t2c ON t2(c);
}
foreach {tn sql res} {
  1 { DELETE FROM t2 WHERE a=?}          { t2* t2a t2b* t2c* }
  2 { DELETE FROM t2 WHERE a=? AND +b=?} { t2 t2a t2b* t2c* }
  3 { DELETE FROM t2 WHERE a=? OR b=?}   { t2 t2a* t2b* t2c* }
  4 { DELETE FROM t2 WHERE +a=? }        { t2 t2a* t2b* t2c* }
  5 { DELETE FROM t2 WHERE rowid=? }     { t2 t2a* t2b* t2c* }
} {
  do_adp_test 2.$tn $sql $res
}








|
|
|
>
>
>
>
>
>
>

|
>
>
>
>
|
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
|
>
>
>
>
>
>
>













|
|
|
|











|
|







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
proc analyze_delete_program {sql} {
  # Build a map from root page to table/index name.
  db eval {
    SELECT name, rootpage FROM sqlite_master
  } {
    set T($rootpage) $name
  }
  
  # For each OpenWrite instruction generated for the proposed DELETE
  # statement, add the following array entries:
  #
  #   $M(<cursor number>) -> <object name>
  #   $O(<object name>)   -> "*" | ""
  #
  # The O() entry is set to "*" if the BTREE_FORDELETE flag is specified,
  # or "" otherwise.
  #
  db eval "EXPLAIN $sql" R {
    if {$R(opcode)=="OpenWrite"} {
      set root $R(p2)
      set csr $R(p1)
      if {[info exists T($root)]} { set M($csr) $T($root) }

      set obj $T($root)
      set O($obj) ""
      if {"0x$R(p5)" & 0x08} { 
        set O($obj) *
      } else {
        set O($obj) ""
      }
    }
  }

  db eval "EXPLAIN $sql" R {
    if {$R(opcode) == "Delete"} {
      set csr $R(p1)
      if {[info exists M($csr)]} {
        set idxdelete [expr {("0x$R(p5)" & 0x04) ? 1 : 0}]
        if {$idxdelete} {
          append O($M($csr)) "+"
        }
      }
    }
  }

  set res [list]
  foreach {k v} [array get O] {
    lappend res "${k}${v}"
  }

  lsort $res
}

proc do_adp_test {tn sql res} {
  uplevel [list do_test $tn [list analyze_delete_program $sql] [list {*}$res]]
}

do_execsql_test 1.0 {
  CREATE TABLE t1(a PRIMARY KEY, b);
}

foreach {tn sql res} {
  1 { DELETE FROM t1 WHERE a=?}          { sqlite_autoindex_t1_1  t1*+ }
  2 { DELETE FROM t1 WHERE a=? AND b=? } { sqlite_autoindex_t1_1  t1+  }
  3 { DELETE FROM t1 WHERE a>? }         { sqlite_autoindex_t1_1  t1*+ }
  4 { DELETE FROM t1 WHERE rowid=? }     { sqlite_autoindex_t1_1*  t1  }
} {
  do_adp_test 1.$tn $sql $res
}

do_execsql_test 2.0 {
  CREATE TABLE t2(a, b, c);
  CREATE INDEX t2a ON t2(a);
  CREATE INDEX t2b ON t2(b);
  CREATE INDEX t2c ON t2(c);
}
foreach {tn sql res} {
  1 { DELETE FROM t2 WHERE a=?}          { t2*+ t2a t2b* t2c* }
  2 { DELETE FROM t2 WHERE a=? AND +b=?} { t2+ t2a t2b* t2c* }
  3 { DELETE FROM t2 WHERE a=? OR b=?}   { t2 t2a* t2b* t2c* }
  4 { DELETE FROM t2 WHERE +a=? }        { t2 t2a* t2b* t2c* }
  5 { DELETE FROM t2 WHERE rowid=? }     { t2 t2a* t2b* t2c* }
} {
  do_adp_test 2.$tn $sql $res
}