sl@0: # 2006 June 10
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file implements regression tests for SQLite library.
sl@0: #
sl@0: # $Id: vtab2.test,v 1.8 2008/01/31 15:53:46 drh Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: ifcapable !vtab||!schema_pragmas {
sl@0:   finish_test
sl@0:   return
sl@0: }
sl@0: 
sl@0: register_schema_module [sqlite3_connection_pointer db]
sl@0: do_test vtab2-1.1 {
sl@0:   execsql {
sl@0:     CREATE VIRTUAL TABLE schema USING schema;
sl@0:     SELECT * FROM schema;
sl@0:   }
sl@0: } [list \
sl@0:   main schema 0 database   {} 0 {} 0 \
sl@0:   main schema 1 tablename  {} 0 {} 0 \
sl@0:   main schema 2 cid        {} 0 {} 0 \
sl@0:   main schema 3 name       {} 0 {} 0 \
sl@0:   main schema 4 type       {} 0 {} 0 \
sl@0:   main schema 5 not_null   {} 0 {} 0 \
sl@0:   main schema 6 dflt_value {} 0 {} 0 \
sl@0:   main schema 7 pk         {} 0 {} 0 \
sl@0: ]
sl@0: 
sl@0: # See ticket #2230.
sl@0: #
sl@0: do_test vtab2-1.2 {
sl@0:   execsql {
sl@0:     SELECT length(tablename) FROM schema GROUP by tablename;
sl@0:   }
sl@0: } {6}
sl@0: do_test vtab2-1.3 {
sl@0:   execsql {
sl@0:     SELECT tablename FROM schema GROUP by length(tablename);
sl@0:   }
sl@0: } {schema}
sl@0: do_test vtab2-1.4 {
sl@0:   execsql {
sl@0:     SELECT length(tablename) FROM schema GROUP by length(tablename);
sl@0:   }
sl@0: } {6}
sl@0: 
sl@0: register_tclvar_module [sqlite3_connection_pointer db]
sl@0: do_test vtab2-2.1 {
sl@0:   set ::abc 123
sl@0:   execsql {
sl@0:     CREATE VIRTUAL TABLE vars USING tclvar;
sl@0:     SELECT * FROM vars WHERE name='abc';
sl@0:   }
sl@0: } [list abc "" 123]
sl@0: do_test vtab2-2.2 {
sl@0:   set A(1) 1
sl@0:   set A(2) 4
sl@0:   set A(3) 9
sl@0:   execsql {
sl@0:     SELECT * FROM vars WHERE name='A';
sl@0:   }
sl@0: } [list A 1 1 A 2 4 A 3 9]
sl@0: unset -nocomplain result
sl@0: unset -nocomplain var
sl@0: set result {}
sl@0: foreach var [lsort [info vars tcl_*]] {
sl@0:   catch {lappend result $var [set $var]}
sl@0: }
sl@0: do_test vtab2-2.3 {
sl@0:   execsql {
sl@0:     SELECT name, value FROM vars
sl@0:       WHERE name MATCH 'tcl_*' AND arrayname = '' 
sl@0:       ORDER BY name;
sl@0:   }
sl@0: } $result
sl@0: unset result
sl@0: unset var
sl@0: 
sl@0: # Ticket #2894.
sl@0: #
sl@0: # Make sure we do call Column(), and Rowid() methods of
sl@0: # a virtual table when that table is in a LEFT JOIN.
sl@0: #
sl@0: do_test vtab2-3.1 {
sl@0:   execsql {
sl@0:     SELECT * FROM schema WHERE dflt_value IS NULL LIMIT 1
sl@0:   }
sl@0: } {main schema 0 database {} 0 {} 0}
sl@0: do_test vtab2-3.2 {
sl@0:   execsql {
sl@0:     SELECT *, b.rowid
sl@0:       FROM schema a LEFT JOIN schema b ON a.dflt_value=b.dflt_value
sl@0:      WHERE a.rowid=1
sl@0:   }
sl@0: } {main schema 0 database {} 0 {} 0 {} {} {} {} {} {} {} {} {}}
sl@0: 
sl@0: finish_test