Home | History | Annotate | Download | only in test 
      1 # 2007 June 26
      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.  The
     12 # focus of this file is 'hidden' virtual table columns.
     13 #
     14 # $Id: vtabA.test,v 1.2 2008/07/12 14:52:21 drh Exp $
     15 
     16 set testdir [file dirname $argv0]
     17 source $testdir/tester.tcl
     18 
     19 ifcapable !vtab {
     20   finish_test
     21   return
     22 }
     23 
     24 proc get_decltype {table col} {
     25   set STMT [sqlite3_prepare $::DB "SELECT $col FROM $table" -1 TAIL]
     26   set decltype [sqlite3_column_decltype $STMT 0]
     27   sqlite3_finalize $STMT
     28   set decltype
     29 }
     30 
     31 proc get_collist {table} {
     32   set ret [list]
     33   db eval "PRAGMA table_info($table)" { lappend ret $name }
     34   set ret
     35 }
     36 
     37 # Register the echo module
     38 register_echo_module [sqlite3_connection_pointer db]
     39 
     40 # Create a virtual table with a 'hidden' column (column b).
     41 #
     42 do_test vtabA-1.1 {
     43   execsql { CREATE TABLE t1(a, b HIDDEN VARCHAR, c INTEGER) }
     44 } {}
     45 do_test vtabA-1.2 {
     46   execsql { CREATE VIRTUAL TABLE t1e USING echo(t1) }
     47 } {}
     48 
     49 # Test that the hidden column is not listed by [PRAGMA table_info].
     50 #
     51 do_test vtabA-1.3 {
     52   execsql { PRAGMA table_info(t1e) }
     53 } [list              \
     54   0 a {}      0 {} 0 \
     55   1 c INTEGER 0 {} 0 \
     56 ]
     57 
     58 # Test that the hidden column is not require in the default column
     59 # list for an INSERT statement.
     60 #
     61 do_test vtabA-1.4 {
     62   catchsql {
     63     INSERT INTO t1e VALUES('value a', 'value c');
     64   }
     65 } {0 {}}
     66 do_test vtabA-1.5 {
     67   execsql {
     68     SELECT a, b, c FROM t1e;
     69   }
     70 } {{value a} {} {value c}}
     71 
     72 do_test vtabA-1.6 {
     73   execsql {
     74     SELECT * FROM t1e;
     75   }
     76 } {{value a} {value c}}
     77 
     78 # Test that the expansion of a '*' expression in the result set of
     79 # a SELECT does not include the hidden column.
     80 #
     81 do_test vtabA-1.7 {
     82   execsql {
     83     INSERT INTO t1e SELECT * FROM t1e;
     84   }
     85 } {}
     86 do_test vtabA-1.8 {
     87   execsql {
     88     SELECT * FROM t1e;
     89   }
     90 } {{value a} {value c} {value a} {value c}}
     91 
     92 # Test that the declaration type of the hidden column does not include
     93 # the token "HIDDEN".
     94 #
     95 do_test vtabA-1.9 {
     96   get_decltype t1e b
     97 } {VARCHAR}
     98 do_test vtabA-1.10 {
     99   get_collist t1e
    100 } {a c}
    101 
    102 #----------------------------------------------------------------------
    103 # These tests vtabA-2.* concentrate on testing that the HIDDEN token
    104 # is detected and handled correctly in various declarations.
    105 #
    106 proc analyse_parse {columns decltype_list} {
    107   db eval { DROP TABLE IF EXISTS t1e; }
    108   db eval { DROP TABLE IF EXISTS t1; }
    109   db eval " CREATE TABLE t1 $columns "
    110   db eval { CREATE VIRTUAL TABLE t1e USING echo(t1) }
    111   set ret [list [get_collist t1e]]
    112   foreach c $decltype_list {
    113     lappend ret [get_decltype t1e $c]
    114   }
    115   set ret
    116 }
    117 
    118 do_test vtabA-2.1 {
    119   analyse_parse {(a text, b integer hidden, c hidden)} {a b c}
    120 } {a text integer {}}
    121 
    122 do_test vtabA-2.2 {
    123   analyse_parse {(a hidden   , b integerhidden, c hidden1)} {a b c}
    124 } {{b c} {} integerhidden hidden1}
    125 
    126 do_test vtabA-2.3 {
    127   analyse_parse {(a HiDden, b HIDDEN, c hidden)} {a b c}
    128 } {{} {} {} {}}
    129 
    130 do_test vtabA-2.4 {
    131   analyse_parse {(a whatelse can i hidden test, b HIDDEN hidden)} {a b}
    132 } {{} {whatelse can i test} hidden}
    133 
    134 
    135 # Ticket [d2f02d37f52bfe23e421f2c60fbb8586ac76ff01]:
    136 # assertion failure on an UPDATE involving two virtual tables.
    137 #
    138 do_test vtabA-3.1 {
    139   db eval {
    140     DROP TABLE IF EXISTS t1;
    141     DROP TABLE IF EXISTS t2;
    142     CREATE TABLE t1(a,b);
    143     INSERT INTO t1 VALUES(1,2);
    144     CREATE TABLE t2(x,y);
    145     INSERT INTO t2 VALUES(3,4);
    146     CREATE VIRTUAL TABLE vt1 USING echo(t1);
    147     CREATE VIRTUAL TABLE vt2 USING echo(t2);
    148     UPDATE vt2 SET x=(SELECT a FROM vt1 WHERE b=2) WHERE y=4;
    149     SELECT * FROM t2;
    150   }
    151 } {1 4}
    152 
    153 finish_test
    154