1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
|
# 2006 June 10
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# $Id: vtab2.test,v 1.9 2008/10/13 10:37:50 danielk1977 Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable !vtab||!schema_pragmas {
finish_test
return
}
register_schema_module [sqlite3_connection_pointer db]
do_test vtab2-1.1 {
execsql {
CREATE VIRTUAL TABLE schema USING schema;
SELECT * FROM schema;
}
} [list \
main schema 0 database {} 0 {} 0 \
main schema 1 tablename {} 0 {} 0 \
main schema 2 cid {} 0 {} 0 \
main schema 3 name {} 0 {} 0 \
main schema 4 type {} 0 {} 0 \
main schema 5 not_null {} 0 {} 0 \
main schema 6 dflt_value {} 0 {} 0 \
main schema 7 pk {} 0 {} 0 \
]
# See ticket #2230.
#
do_test vtab2-1.2 {
execsql {
SELECT length(tablename) FROM schema GROUP by tablename;
}
} {6}
do_test vtab2-1.3 {
execsql {
SELECT tablename FROM schema GROUP by length(tablename);
}
} {schema}
do_test vtab2-1.4 {
execsql {
SELECT length(tablename) FROM schema GROUP by length(tablename);
}
} {6}
register_tclvar_module [sqlite3_connection_pointer db]
do_test vtab2-2.1 {
set ::abc 123
execsql {
CREATE VIRTUAL TABLE vars USING tclvar;
SELECT * FROM vars WHERE name='abc';
}
} [list abc "" 123]
do_test vtab2-2.2 {
set A(1) 1
set A(2) 4
set A(3) 9
execsql {
SELECT * FROM vars WHERE name='A';
}
} [list A 1 1 A 2 4 A 3 9]
unset -nocomplain result
unset -nocomplain var
set result {}
foreach var [lsort [info vars tcl_*]] {
catch {lappend result $var [set $var]}
}
do_test vtab2-2.3 {
execsql {
SELECT name, value FROM vars
WHERE name MATCH 'tcl_*' AND arrayname = ''
ORDER BY name;
}
} $result
unset result
unset var
# Ticket #2894.
#
# Make sure we do call Column(), and Rowid() methods of
# a virtual table when that table is in a LEFT JOIN.
#
do_test vtab2-3.1 {
execsql {
SELECT * FROM schema WHERE dflt_value IS NULL LIMIT 1
}
} {main schema 0 database {} 0 {} 0}
do_test vtab2-3.2 {
execsql {
SELECT *, b.rowid
FROM schema a LEFT JOIN schema b ON a.dflt_value=b.dflt_value
WHERE a.rowid=1
}
} {main schema 0 database {} 0 {} 0 {} {} {} {} {} {} {} {} {}}
do_test vtab2-4.1 {
execsql {
BEGIN TRANSACTION;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
CREATE TABLE fkey(
to_tbl,
to_col
);
INSERT INTO "fkey" VALUES('t1',NULL);
COMMIT;
}
} {}
do_test vtab2-4.2 {
execsql { CREATE VIRTUAL TABLE v_col USING schema }
} {}
do_test vtab2-4.3 {
execsql { SELECT name FROM v_col WHERE tablename = 't1' AND pk }
} {a}
do_test vtab2-4.4 {
execsql {
UPDATE fkey
SET to_col = (SELECT name FROM v_col WHERE tablename = 't1' AND pk);
}
} {}
do_test vtab2-4.5 {
execsql { SELECT * FROM fkey }
} {t1 a}
finish_test
|
