# 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.10 2006/06/13 15:00:55 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !vtab { finish_test return } # 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 {unknown module: echo}} do_test vtab1-1.2 { execsql { SELECT name FROM sqlite_master ORDER BY 1 } } {} # After we register the echo module, we are allowed to create # virtual tables using that module. # do_test vtab1-1.3 { register_echo_module [sqlite3_connection_pointer db] catchsql { CREATE VIRTUAL TABLE t1 USING echo; } } {0 {}} do_test vtab1-1.4 { set echo_module } {xCreate echo} do_test vtab1-1.5 { execsql { SELECT name, sql FROM sqlite_master ORDER BY 1 } } {t1 {CREATE VIRTUAL TABLE t1 USING echo}} # 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 t2 USING echo(template); } execsql { PRAGMA table_info(t2); } } [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 each of the two virtual tables - t1 and t2. do_test vtab1-2.3 { breakpoint set echo_module [list] db close set echo_module } [list xDisconnect xDisconnect] set echo_module [list] # Re-open the database. Check that the schema of the virtual # table is still correct. do_test vtab1-2.4 { sqlite3 db test.db db cache size 0 register_echo_module [sqlite3_connection_pointer db] execsql { PRAGMA table_info(t2); } } [list \ 0 a {} 0 {} 0 \ 1 b {} 0 {} 0 \ 2 c {} 0 {} 0 \ ] # Drop the table t2. This should cause the xDestroy (but not xDisconnect) # method to be invoked. do_test vtab1-2.5 { set echo_module [list] execsql { DROP TABLE t2; } set echo_module } [list xDestroy] do_test vtab1-2.6 { execsql { PRAGMA table_info(t2); } } {} do_test vtab1-2.7 { execsql { SELECT sql FROM sqlite_master; } } [list {CREATE VIRTUAL TABLE t1 USING echo} \ {CREATE TABLE template(a, b, c)} \ ] do_test vtab1-2.8 { set echo_module [list] execsql { DROP TABLE t1; DROP TABLE template; } set echo_module } [list] do_test vtab1-2.9 { execsql { SELECT sql FROM sqlite_master; } } [list] #---------------------------------------------------------------------- # Test case vtab1-3 tests simple linear scans (no filter conditions) of # virtual table modules. do_test vtab1-3.1 { set echo_module "" execsql { CREATE TABLE treal(a INTEGER, b VARCHAR(32), c); CREATE INDEX treal_idx ON treal(b); CREATE VIRTUAL TABLE t1 USING echo(treal); } set echo_module } [list xCreate echo treal] do_test vtab1-3.2 { # Test that a SELECT on t2 doesn't crash. No rows are returned # because the underlying real table, is currently empty. execsql { SELECT a, b, c FROM t1; } } {} do_test vtab1-3.3 { # Put some data into the table treal. Then try a select on t1. 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; } set echo_module } {xBestIndex {} {} xFilter {}} do_test vtab1-3.7 { set echo_module "" execsql { SELECT * FROM t1 WHERE b = 10; } set echo_module } {xBestIndex {WHERE b = ?} {} xFilter {WHERE b = ?} 10} do_test vtab1-3.8 { set echo_module "" execsql { SELECT * FROM t1 WHERE b >= 5 AND b <= 10; } set echo_module } {xBestIndex {WHERE b >= ? AND b <= ?} {} xFilter {WHERE b >= ? AND b <= ?} 5 10} do_test vtab1-3.9 { set echo_module "" execsql { SELECT * FROM t1 WHERE b BETWEEN 5 AND 10; } set echo_module } {xBestIndex {WHERE b >= ? AND b <= ?} {} xFilter {WHERE b >= ? AND b <= ?} 5 10} finish_test