AryaWu/sqlite

1# 2009 November 102#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.12#13# This file implements tests for the "intarray" object implemented14# in test_intarray.c.15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20ifcapable !vtab {21  return22}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 a36  }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 temp.sqlite_master46     ORDER BY name47  }48} {table ia1 table ia2 table ia3 table ia4}49 50# Verify the ability to DROP and recreate an intarray virtual table.51do_test intarray-1.1b {52  db eval {DROP TABLE ia1}53  set rc [catch {sqlite3_intarray_create db ia1} ia1]54  lappend rc $ia155} {/0 [0-9A-Z]+/} 56 57do_test intarray-1.2 {58  db eval {59    SELECT b FROM t1 WHERE a IN ia3 ORDER BY a60  }61} {}62 63do_test intarray-1.3 {64  sqlite3_intarray_bind $ia3 45 123 67865  db eval {66    SELECT b FROM t1 WHERE a IN ia3 ORDER BY a67  }68} {x045 x123 x678}69 70do_test intarray-1.4 {71  db eval {72    SELECT count(b) FROM t1 WHERE a NOT IN ia3 ORDER BY a73  }74} {996}75 76#explain {SELECT b FROM t1 WHERE a NOT IN ia3}77 78do_test intarray-1.5 {79  set cmd sqlite3_intarray_bind80  lappend cmd $ia181  for {set i 1} {$i<=999} {incr i} {82    lappend cmd $i83    lappend cmd [expr {$i+1000}]84    lappend cmd [expr {$i+2000}]85  }86  eval $cmd87  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 94do_test intarray-1.6 {95  db eval {96    DELETE FROM t1 WHERE a IN ia1;97    SELECT count(*) FROM t1;98  }99} {0}100 101do_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 109do_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 116finish_test117