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