intarray.test revision 5821806d5e7f356e8fa4b058a389a808ea183019 
1# 2009 November 10
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# This file implements regression tests for SQLite library.
12#
13# This file implements tests for the "intarray" object implemented
14# in test_intarray.c.
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20ifcapable !vtab {
21  return
22}
23
24do_test intarray-1.0 {
25  db eval {
26    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
27  }
28  for {set i 1} {$i<=999} {incr i} {
29    set b [format {x%03d} $i]
30    db eval {INSERT INTO t1(a,b) VALUES($i,$b)}
31  }
32  db eval {
33    CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
34    INSERT INTO t2 SELECT * FROM t1;
35    SELECT b FROM t1 WHERE a IN (12,34,56,78) ORDER BY a
36  }
37} {x012 x034 x056 x078}
38
39do_test intarray-1.1 {
40  set ia1 [sqlite3_intarray_create db ia1]
41  set ia2 [sqlite3_intarray_create db ia2]
42  set ia3 [sqlite3_intarray_create db ia3]
43  set ia4 [sqlite3_intarray_create db ia4]
44  db eval {
45    SELECT type, name FROM sqlite_temp_master
46     ORDER BY name
47  }
48} {table ia1 table ia2 table ia3 table ia4}
49
50do_test intarray-1.2 {
51  db eval {
52    SELECT b FROM t1 WHERE a IN ia3 ORDER BY a
53  }
54} {}
55
56do_test intarray-1.3 {
57  sqlite3_intarray_bind $ia3 45 123 678
58  db eval {
59    SELECT b FROM t1 WHERE a IN ia3 ORDER BY a
60  }
61} {x045 x123 x678}
62
63do_test intarray-1.4 {
64  db eval {
65    SELECT count(b) FROM t1 WHERE a NOT IN ia3 ORDER BY a
66  }
67} {996}
68
69#explain {SELECT b FROM t1 WHERE a NOT IN ia3}
70
71do_test intarray-1.5 {
72  set cmd sqlite3_intarray_bind
73  lappend cmd $ia1
74  for {set i 1} {$i<=999} {incr i} {
75    lappend cmd $i
76    lappend cmd [expr {$i+1000}]
77    lappend cmd [expr {$i+2000}]
78  }
79  eval $cmd
80  db eval {
81    REPLACE INTO t1 SELECT * FROM t2;
82    DELETE FROM t1 WHERE a NOT IN ia1;
83    SELECT count(*) FROM t1;
84  }
85} {999}
86
87do_test intarray-1.6 {
88  db eval {
89    DELETE FROM t1 WHERE a IN ia1;
90    SELECT count(*) FROM t1;
91  }
92} {0}
93
94do_test intarray-2.1 {
95  db eval {
96    CREATE TEMP TABLE t3(p,q);
97    INSERT INTO t3 SELECT * FROM t2;
98    SELECT count(*) FROM t3 WHERE p IN ia1;
99  }
100} {999}
101
102do_test intarray-2.2 {
103  set ia5 [sqlite3_intarray_create db ia5]
104  db eval {
105    SELECT count(*) FROM t3 WHERE p IN ia1;
106  }
107} {999}
108
109finish_test
110