1# 2006 June 10 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. 12# 13 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16set testprefix vtab2 17 18ifcapable !vtab||!schema_pragmas { 19 finish_test 20 return 21} 22 23register_schema_module [sqlite3_connection_pointer db] 24do_test vtab2-1.1 { 25 execsql { 26 CREATE VIRTUAL TABLE schema USING schema; 27 SELECT * FROM schema; 28 } 29} [list \ 30 main schema 0 database {} 0 {} 0 \ 31 main schema 1 tablename {} 0 {} 0 \ 32 main schema 2 cid {} 0 {} 0 \ 33 main schema 3 name {} 0 {} 0 \ 34 main schema 4 type {} 0 {} 0 \ 35 main schema 5 not_null {} 0 {} 0 \ 36 main schema 6 dflt_value {} 0 {} 0 \ 37 main schema 7 pk {} 0 {} 0 \ 38] 39 40# See ticket #2230. 41# 42do_test vtab2-1.2 { 43 execsql { 44 SELECT length(tablename) FROM schema GROUP by tablename; 45 } 46} {6} 47do_test vtab2-1.3 { 48 execsql { 49 SELECT tablename FROM schema GROUP by length(tablename); 50 } 51} {schema} 52do_test vtab2-1.4 { 53 execsql { 54 SELECT length(tablename) FROM schema GROUP by length(tablename); 55 } 56} {6} 57 58register_tclvar_module [sqlite3_connection_pointer db] 59do_test vtab2-2.1 { 60 set ::abc 123 61 execsql { 62 CREATE VIRTUAL TABLE vars USING tclvar; 63 SELECT * FROM vars WHERE name='abc'; 64 } 65} [list abc "" 123] 66do_test vtab2-2.2 { 67 set A(1) 1 68 set A(2) 4 69 set A(3) 9 70 execsql { 71 SELECT * FROM vars WHERE name='A'; 72 } 73} [list A 1 1 A 2 4 A 3 9] 74unset -nocomplain result 75unset -nocomplain var 76set result {} 77foreach var [lsort [info vars tcl_*]] { 78 catch {lappend result $var [set $var]} 79} 80do_test vtab2-2.3 { 81 execsql { 82 SELECT name, value FROM vars 83 WHERE name MATCH 'tcl_*' AND arrayname = '' 84 ORDER BY name; 85 } 86} $result 87unset result 88unset var 89 90# Ticket #2894. 91# 92# Make sure we do call Column(), and Rowid() methods of 93# a virtual table when that table is in a LEFT JOIN. 94# 95do_test vtab2-3.1 { 96 execsql { 97 SELECT * FROM schema WHERE dflt_value IS NULL LIMIT 1 98 } 99} {main schema 0 database {} 0 {} 0} 100do_test vtab2-3.2 { 101 execsql { 102 SELECT *, b.rowid 103 FROM schema a LEFT JOIN schema b ON a.dflt_value=b.dflt_value 104 WHERE a.rowid=1 105 } 106} {main schema 0 database {} 0 {} 0 {} {} {} {} {} {} {} {} {}} 107do_test vtab2-3.3 { 108 execsql { 109 SELECT *, b.rowid 110 FROM schema a LEFT JOIN schema b ON a.dflt_value IS b.dflt_value 111 AND a.dflt_value IS NOT NULL 112 WHERE a.rowid=1 113 } 114} {main schema 0 database {} 0 {} 0 {} {} {} {} {} {} {} {} {}} 115 116do_test vtab2-4.1 { 117 execsql { 118 BEGIN TRANSACTION; 119 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); 120 CREATE TABLE fkey( 121 to_tbl, 122 to_col 123 ); 124 INSERT INTO "fkey" VALUES('t1',NULL); 125 COMMIT; 126 } 127} {} 128do_test vtab2-4.2 { 129 execsql { CREATE VIRTUAL TABLE v_col USING schema } 130} {} 131do_test vtab2-4.3 { 132 execsql { SELECT name FROM v_col WHERE tablename = 't1' AND pk } 133} {a} 134do_test vtab2-4.4 { 135 execsql { 136 UPDATE fkey 137 SET to_col = (SELECT name FROM v_col WHERE tablename = 't1' AND pk); 138 } 139} {} 140do_test vtab2-4.5 { 141 execsql { SELECT * FROM fkey } 142} {t1 a} 143 144#------------------------------------------------------------------------- 145# 146ifcapable fts3 { 147 reset_db 148 do_execsql_test 5.1 { 149 PRAGMA encoding='UTF16'; 150 } 151 152 do_test 5.2 { 153 sqlite3_exec_hex db { CREATE VIRTUAL TABLE %C8 USING fts3 } 154 } {0 {}} 155 156 do_test 5.3 { 157 sqlite3_exec_hex db { CREATE VIRTUAL TABLE %C9 USING s } 158 } {/1 {malformed database schema.* already exists}/} 159} 160 161 162 163finish_test 164