# 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. The # focus of this file is creating and dropping virtual tables. # # $Id: vtab1.test,v 1.13 2006/06/14 07:41:32 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !vtab { finish_test return } #---------------------------------------------------------------------- # Organization of tests in this file: # # vtab1-1.*: Error conditions and other issues surrounding creation/connection # of a virtual module. # vtab1-2.*: Test sqlite3_declare_vtab() and the xConnect/xDisconnect methods. # vtab1-3.*: Table scans and WHERE clauses. # vtab1-4.*: Table scans and ORDER BY clauses. # vtab1-5.*: Test queries that include joins. This brings the # sqlite3_index_info.estimatedCost variable into play. # # This file uses the "echo" module (see src/test8.c). Refer to comments # in that file for the special behaviour of the Tcl $echo_module variable. # # TODO: # # * How to test the sqlite3_index_constraint_usage.omit field? Or # sqlite3_index_info.orderByConsumed? # #---------------------------------------------------------------------- # Test cases vtab1.1.* # # We cannot create a virtual table if the module has not been registered. # do_test vtab1-1.1 { catchsql { CREATE VIRTUAL TABLE t1 USING echo; } } {1 {no such module: echo}} do_test vtab1-1.2 { execsql { SELECT name FROM sqlite_master ORDER BY 1 } } {} # Register the module register_echo_module [sqlite3_connection_pointer db] # Once a module has been registered, virtual tables using that module # may be created. However if a module xCreate() fails to call # sqlite3_declare_vtab() an error will be raised and the table not created. # # The "echo" module does not invoke sqlite3_declare_vtab() if it is # passed zero arguments. # do_test vtab1-1.3 { catchsql { CREATE VIRTUAL TABLE t1 USING echo; } } {1 {vtable constructor did not declare schema: t1}} do_test vtab1-1.4 { execsql { SELECT name FROM sqlite_master ORDER BY 1 } } {} # The "echo" module xCreate method returns an error and does not create # the virtual table if it is passed an argument that does not correspond # to an existing real table in the same database. # do_test vtab1-1.5 { catchsql { CREATE VIRTUAL TABLE t1 USING echo(no_such_table); } } {1 {vtable constructor failed: t1}} do_test vtab1-1.6 { execsql { SELECT name FROM sqlite_master ORDER BY 1 } } {} # Test to make sure nothing goes wrong and no memory is leaked if we # select an illegal table-name (i.e a reserved name or the name of a # table that already exists). # do_test vtab1-1.7 { catchsql { CREATE VIRTUAL TABLE sqlite_master USING echo; } } {1 {object name reserved for internal use: sqlite_master}} do_test vtab1-1.8 { catchsql { CREATE TABLE treal(a, b, c); CREATE VIRTUAL TABLE treal USING echo(treal); } } {1 {table treal already exists}} do_test vtab1-1.9 { execsql { DROP TABLE treal; SELECT name FROM sqlite_master ORDER BY 1 } } {} #---------------------------------------------------------------------- # Test cases vtab1.2.* # # At this point, the database is completely empty. The echo module # has already been registered. # If a single argument is passed to the echo module during table # creation, it is assumed to be the name of a table in the same # database. The echo module attempts to set the schema of the # new virtual table to be the same as the existing database table. # do_test vtab1-2.1 { execsql { CREATE TABLE template(a, b, c); } execsql { PRAGMA table_info(template); } } [list \ 0 a {} 0 {} 0 \ 1 b {} 0 {} 0 \ 2 c {} 0 {} 0 \ ] do_test vtab1-2.2 { execsql { CREATE VIRTUAL TABLE t1 USING echo(template); } execsql { PRAGMA table_info(t1); } } [list \ 0 a {} 0 {} 0 \ 1 b {} 0 {} 0 \ 2 c {} 0 {} 0 \ ] # Test that the database can be unloaded. This should invoke the xDisconnect() # callback for the successfully create virtual table (t1). # do_test vtab1-2.3 { set echo_module [list] db close set echo_module } [list xDisconnect] # Re-open the database. This should not cause any virtual methods to # be called. The invocation of xConnect() is delayed until the virtual # table schema is first required by the compiler. # do_test vtab1-2.4 { set echo_module [list] sqlite3 db test.db db cache size 0 set echo_module } {} # Try to query the virtual table schema. This should fail, as the # echo module has not been registered with this database connection. # do_test vtab1.2.6 { breakpoint catchsql { PRAGMA table_info(t1); } } {1 {no such module: echo}} # Register the module register_echo_module [sqlite3_connection_pointer db] # Try to query the virtual table schema again. This time it should # invoke the xConnect method and succeed. # do_test vtab1.2.7 { execsql { PRAGMA table_info(t1); } } [list \ 0 a {} 0 {} 0 \ 1 b {} 0 {} 0 \ 2 c {} 0 {} 0 \ ] do_test vtab1.2.8 { set echo_module } {xConnect echo template} # Drop table t1. This should cause the xDestroy (but not xDisconnect) method # to be invoked. do_test vtab1-2.5 { set echo_module "" execsql { DROP TABLE t1; } set echo_module } {xDestroy} do_test vtab1-2.6 { execsql { PRAGMA table_info(t1); } } {} do_test vtab1-2.7 { execsql { SELECT sql FROM sqlite_master; } } [list {CREATE TABLE template(a, b, c)}] # Clean up other test artifacts: do_test vtab1-2.8 { execsql { DROP TABLE template; SELECT sql FROM sqlite_master; } } [list] #---------------------------------------------------------------------- # Test case vtab1-3 test table scans and the echo module's # xBestIndex/xFilter handling of WHERE conditions. do_test vtab1-3.1 { set echo_module "" execsql { CREATE TABLE treal(a INTEGER, b INTEGER, c); CREATE INDEX treal_idx ON treal(b); CREATE VIRTUAL TABLE t1 USING echo(treal); } set echo_module } [list xCreate echo treal] # Test that a SELECT on t1 doesn't crash. No rows are returned # because the underlying real table is currently empty. # do_test vtab1-3.2 { execsql { SELECT a, b, c FROM t1; } } {} # Put some data into the table treal. Then try a few simple SELECT # statements on t1. # do_test vtab1-3.3 { execsql { INSERT INTO treal VALUES(1, 2, 3); INSERT INTO treal VALUES(4, 5, 6); SELECT * FROM t1; } } {1 2 3 4 5 6} do_test vtab1-3.4 { execsql { SELECT a FROM t1; } } {1 4} do_test vtab1-3.5 { execsql { SELECT rowid FROM t1; } } {1 2} do_test vtab1-3.6 { set echo_module "" execsql { SELECT * FROM t1; } } {1 2 3 4 5 6} do_test vtab1-3.7 { execsql { SELECT rowid, * FROM t1; } } {1 1 2 3 2 4 5 6} # Execute some SELECT statements with WHERE clauses on the t1 table. # Then check the echo_module variable (written to by the module methods # in test8.c) to make sure the xBestIndex() and xFilter() methods were # called correctly. # do_test vtab1-3.8 { set echo_module "" execsql { SELECT * FROM t1; } set echo_module } [list xBestIndex {SELECT rowid, * FROM 'treal'} \ xFilter {SELECT rowid, * FROM 'treal'} ] do_test vtab1-3.9 { set echo_module "" execsql { SELECT * FROM t1 WHERE b = 5; } } {4 5 6} do_test vtab1-3.10 { set echo_module } [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b = ?} \ xFilter {SELECT rowid, * FROM 'treal' WHERE b = ?} 5 ] do_test vtab1-3.10 { set echo_module "" execsql { SELECT * FROM t1 WHERE b >= 5 AND b <= 10; } } {4 5 6} do_test vtab1-3.11 { set echo_module } [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} \ xFilter {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 5 10 ] do_test vtab1-3.12 { set echo_module "" execsql { SELECT * FROM t1 WHERE b BETWEEN 2 AND 10; } } {1 2 3 4 5 6} do_test vtab1-3.13 { set echo_module } [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} \ xFilter {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 2 10 ] #---------------------------------------------------------------------- # Test case vtab1-3 test table scans and the echo module's # xBestIndex/xFilter handling of ORDER BY clauses. # This procedure executes the SQL. Then it checks to see if the OP_Sort # opcode was executed. If an OP_Sort did occur, then "sort" is appended # to the result. If no OP_Sort happened, then "nosort" is appended. # # This procedure is used to check to make sure sorting is or is not # occurring as expected. # proc cksort {sql} { set ::sqlite_sort_count 0 set data [execsql $sql] if {$::sqlite_sort_count} {set x sort} {set x nosort} lappend data $x return $data } do_test vtab1-4.1 { set echo_module "" cksort { SELECT b FROM t1 ORDER BY b; } } {2 5 nosort} do_test vtab1-4.2 { set echo_module } [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b ASC} \ xFilter {SELECT rowid, * FROM 'treal' ORDER BY b ASC} ] do_test vtab1-4.3 { set echo_module "" cksort { SELECT b FROM t1 ORDER BY b DESC; } } {5 2 nosort} do_test vtab1-4.4 { set echo_module } [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b DESC} \ xFilter {SELECT rowid, * FROM 'treal' ORDER BY b DESC} ] do_test vtab1-4.3 { set echo_module "" cksort { SELECT b FROM t1 ORDER BY b||''; } } {2 5 sort} do_test vtab1-4.4 { set echo_module } [list xBestIndex {SELECT rowid, * FROM 'treal'} \ xFilter {SELECT rowid, * FROM 'treal'} ] finish_test