xref: /sqlite-3.40.0/test/vtabA.test (revision c2df4d6a) 
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}}
77do_execsql_test vtabA-1.7 {
78  DELETE FROM t1e;
79  INSERT INTO t1e SELECT 'abc','def';
80} {}
81do_execsql_test vtabA-1.8 {
82  INSERT INTO t1e VALUES('ghi','jkl'),('mno','pqr'),('stu','vwx');
83} {}
84do_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#
92do_test vtabA-1.20 {
93  execsql {
94    INSERT INTO t1e SELECT * FROM t1e;
95  }
96} {}
97do_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#
106do_test vtabA-1.22 {
107  get_decltype t1e b
108} {VARCHAR}
109do_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#
117proc 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
129do_test vtabA-2.1 {
130  analyse_parse {(a text, b integer hidden, c hidden)} {a b c}
131} {a TEXT integer {}}
132
133do_test vtabA-2.2 {
134  analyse_parse {(a hidden   , b integerhidden, c hidden1)} {a b c}
135} {{b c} {} integerhidden hidden1}
136
137do_test vtabA-2.3 {
138  analyse_parse {(a HiDden, b HIDDEN, c hidden)} {a b c}
139} {{} {} {} {}}
140
141do_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#
149do_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
164finish_test
165