Home | History | Annotate | Download | only in test 
      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 sqlite_temp_master
     46      ORDER BY name
     47   }
     48 } {table ia1 table ia2 table ia3 table ia4}
     49 
     50 do_test intarray-1.2 {
     51   db eval {
     52     SELECT b FROM t1 WHERE a IN ia3 ORDER BY a
     53   }
     54 } {}
     55 
     56 do_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 
     63 do_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 
     71 do_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 
     87 do_test intarray-1.6 {
     88   db eval {
     89     DELETE FROM t1 WHERE a IN ia1;
     90     SELECT count(*) FROM t1;
     91   }
     92 } {0}
     93 
     94 do_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 
    102 do_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 
    109 finish_test
    110