Debian Codegraph @

test/intarray.test

raw
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
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19
20 ifcapable !vtab {
21 return
22 }
23
24 do_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
39 do_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 temp.sqlite_master
46 ORDER BY name
47 }
48 } {table ia1 table ia2 table ia3 table ia4}
49
50 # Verify the ability to DROP and recreate an intarray virtual table.
51 do_test intarray-1.1b {
52 db eval {DROP TABLE ia1}
53 set rc [catch {sqlite3_intarray_create db ia1} ia1]
54 lappend rc $ia1
55 } {/0 [0-9A-Z]+/}
56
57 do_test intarray-1.2 {
58 db eval {
59 SELECT b FROM t1 WHERE a IN ia3 ORDER BY a
60 }
61 } {}
62
63 do_test intarray-1.3 {
64 sqlite3_intarray_bind $ia3 45 123 678
65 db eval {
66 SELECT b FROM t1 WHERE a IN ia3 ORDER BY a
67 }
68 } {x045 x123 x678}
69
70 do_test intarray-1.4 {
71 db eval {
72 SELECT count(b) FROM t1 WHERE a NOT IN ia3 ORDER BY a
73 }
74 } {996}
75
76 #explain {SELECT b FROM t1 WHERE a NOT IN ia3}
77
78 do_test intarray-1.5 {
79 set cmd sqlite3_intarray_bind
80 lappend cmd $ia1
81 for {set i 1} {$i<=999} {incr i} {
82 lappend cmd $i
83 lappend cmd [expr {$i+1000}]
84 lappend cmd [expr {$i+2000}]
85 }
86 eval $cmd
87 db eval {
88 REPLACE INTO t1 SELECT * FROM t2;
89 DELETE FROM t1 WHERE a NOT IN ia1;
90 SELECT count(*) FROM t1;
91 }
92 } {999}
93
94 do_test intarray-1.6 {
95 db eval {
96 DELETE FROM t1 WHERE a IN ia1;
97 SELECT count(*) FROM t1;
98 }
99 } {0}
100
101 do_test intarray-2.1 {
102 db eval {
103 CREATE TEMP TABLE t3(p,q);
104 INSERT INTO t3 SELECT * FROM t2;
105 SELECT count(*) FROM t3 WHERE p IN ia1;
106 }
107 } {999}
108
109 do_test intarray-2.2 {
110 set ia5 [sqlite3_intarray_create db ia5]
111 db eval {
112 SELECT count(*) FROM t3 WHERE p IN ia1;
113 }
114 } {999}
115
116 finish_test