gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/intarray.test (about) 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