modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/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  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