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 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19ifcapable !vtab { 20 finish_test 21 return 22} 23 24proc 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 31proc 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 38register_echo_module [sqlite3_connection_pointer db] 39 40# Create a virtual table with a 'hidden' column (column b). 41# 42do_test vtabA-1.1 { 43 execsql { CREATE TABLE t1(a, b HIDDEN VARCHAR, c INTEGER) } 44} {} 45do_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# 51do_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# 61do_test vtabA-1.4 { 62 catchsql { 63 INSERT INTO t1e VALUES('value a', 'value c'); 64 } 65} {0 {}} 66do_test vtabA-1.5 { 67 execsql { 68 SELECT a, b, c FROM t1e; 69 } 70} {{value a} {} {value c}} 71 72do_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# 81do_test vtabA-1.7 { 82 execsql { 83 INSERT INTO t1e SELECT * FROM t1e; 84 } 85} {} 86do_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# 95do_test vtabA-1.9 { 96 get_decltype t1e b 97} {VARCHAR} 98do_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# 106proc 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 118do_test vtabA-2.1 { 119 analyse_parse {(a text, b integer hidden, c hidden)} {a b c} 120} {a text integer {}} 121 122do_test vtabA-2.2 { 123 analyse_parse {(a hidden , b integerhidden, c hidden1)} {a b c} 124} {{b c} {} integerhidden hidden1} 125 126do_test vtabA-2.3 { 127 analyse_parse {(a HiDden, b HIDDEN, c hidden)} {a b c} 128} {{} {} {} {}} 129 130do_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# 138do_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 153finish_test 154