github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/vtabA.test (about)

     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  do_execsql_test vtabA-1.7 {
    78    DELETE FROM t1e;
    79    INSERT INTO t1e SELECT 'abc','def';
    80  } {}
    81  do_execsql_test vtabA-1.8 {
    82    INSERT INTO t1e VALUES('ghi','jkl'),('mno','pqr'),('stu','vwx');
    83  } {}
    84  do_execsql_test vtabA-1.9 {
    85    SELECT a,b,c, '|' FROM t1e ORDER BY 1;
    86  } {abc {} def | ghi {} jkl | mno {} pqr | stu {} vwx |}
    87  
    88  
    89  # Test that the expansion of a '*' expression in the result set of
    90  # a SELECT does not include the hidden column.
    91  #
    92  do_test vtabA-1.20 {
    93    execsql {
    94      INSERT INTO t1e SELECT * FROM t1e;
    95    }
    96  } {}
    97  do_test vtabA-1.21 {
    98    execsql {
    99      SELECT * FROM t1e ORDER BY 1;
   100    }
   101  } {abc def abc def ghi jkl ghi jkl mno pqr mno pqr stu vwx stu vwx}
   102  
   103  # Test that the declaration type of the hidden column does not include
   104  # the token "HIDDEN".
   105  #
   106  do_test vtabA-1.22 {
   107    get_decltype t1e b
   108  } {VARCHAR}
   109  do_test vtabA-1.23 {
   110    get_collist t1e
   111  } {a c}
   112  
   113  #----------------------------------------------------------------------
   114  # These tests vtabA-2.* concentrate on testing that the HIDDEN token
   115  # is detected and handled correctly in various declarations.
   116  #
   117  proc analyse_parse {columns decltype_list} {
   118    db eval { DROP TABLE IF EXISTS t1e; }
   119    db eval { DROP TABLE IF EXISTS t1; }
   120    db eval " CREATE TABLE t1 $columns "
   121    db eval { CREATE VIRTUAL TABLE t1e USING echo(t1) }
   122    set ret [list [get_collist t1e]]
   123    foreach c $decltype_list {
   124      lappend ret [get_decltype t1e $c]
   125    }
   126    set ret
   127  }
   128  
   129  do_test vtabA-2.1 {
   130    analyse_parse {(a text, b integer hidden, c hidden)} {a b c}
   131  } {a text integer {}}
   132  
   133  do_test vtabA-2.2 {
   134    analyse_parse {(a hidden   , b integerhidden, c hidden1)} {a b c}
   135  } {{b c} {} integerhidden hidden1}
   136  
   137  do_test vtabA-2.3 {
   138    analyse_parse {(a HiDden, b HIDDEN, c hidden)} {a b c}
   139  } {{} {} {} {}}
   140  
   141  do_test vtabA-2.4 {
   142    analyse_parse {(a whatelse can i hidden test, b HIDDEN hidden)} {a b}
   143  } {{} {whatelse can i test} hidden}
   144  
   145  
   146  # Ticket [d2f02d37f52bfe23e421f2c60fbb8586ac76ff01]:
   147  # assertion failure on an UPDATE involving two virtual tables.
   148  #
   149  do_test vtabA-3.1 {
   150    db eval {
   151      DROP TABLE IF EXISTS t1;
   152      DROP TABLE IF EXISTS t2;
   153      CREATE TABLE t1(a,b);
   154      INSERT INTO t1 VALUES(1,2);
   155      CREATE TABLE t2(x,y);
   156      INSERT INTO t2 VALUES(3,4);
   157      CREATE VIRTUAL TABLE vt1 USING echo(t1);
   158      CREATE VIRTUAL TABLE vt2 USING echo(t2);
   159      UPDATE vt2 SET x=(SELECT a FROM vt1 WHERE b=2) WHERE y=4;
   160      SELECT * FROM t2;
   161    }
   162  } {1 4}
   163  
   164  finish_test