# 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: vtab5.test,v 1.8 2008/07/12 14:52:21 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !vtab { finish_test return } # The following tests - vtab5-1.* - ensure that an INSERT, DELETE or UPDATE # statement can be executed immediately after a CREATE or schema reload. The # point here is testing that the parser always calls xConnect() before the # schema of a virtual table is used. # register_echo_module [sqlite3_connection_pointer db] do_test vtab5-1.1 { execsql { CREATE TABLE treal(a VARCHAR(16), b INTEGER, c FLOAT); INSERT INTO treal VALUES('a', 'b', 'c'); CREATE VIRTUAL TABLE techo USING echo(treal); } } {} do_test vtab5.1.2 { execsql { SELECT * FROM techo; } } {a b c} do_test vtab5.1.3 { db close sqlite3 db test.db register_echo_module [sqlite3_connection_pointer db] execsql { INSERT INTO techo VALUES('c', 'd', 'e'); SELECT * FROM techo; } } {a b c c d e} do_test vtab5.1.4 { db close sqlite3 db test.db register_echo_module [sqlite3_connection_pointer db] execsql { UPDATE techo SET a = 10; SELECT * FROM techo; } } {10 b c 10 d e} do_test vtab5.1.5 { db close sqlite3 db test.db register_echo_module [sqlite3_connection_pointer db] execsql { DELETE FROM techo WHERE b > 'c'; SELECT * FROM techo; } } {10 b c} do_test vtab5.1.X { execsql { DROP TABLE techo; DROP TABLE treal; } } {} # The following tests - vtab5-2.* - ensure that collation sequences # assigned to virtual table columns via the "CREATE TABLE" statement # passed to sqlite3_declare_vtab() are used correctly. # do_test vtab5.2.1 { execsql { CREATE TABLE strings(str COLLATE NOCASE); INSERT INTO strings VALUES('abc1'); INSERT INTO strings VALUES('Abc3'); INSERT INTO strings VALUES('ABc2'); INSERT INTO strings VALUES('aBc4'); SELECT str FROM strings ORDER BY 1; } } {abc1 ABc2 Abc3 aBc4} do_test vtab5.2.2 { execsql { CREATE VIRTUAL TABLE echo_strings USING echo(strings); SELECT str FROM echo_strings ORDER BY 1; } } {abc1 ABc2 Abc3 aBc4} do_test vtab5.2.3 { execsql { SELECT str||'' FROM echo_strings ORDER BY 1; } } {ABc2 Abc3 aBc4 abc1} # Test that it is impossible to create a triggger on a virtual table. # ifcapable trigger { do_test vtab5.3.1 { catchsql { CREATE TRIGGER trig INSTEAD OF INSERT ON echo_strings BEGIN SELECT 1, 2, 3; END; } } {1 {cannot create triggers on virtual tables}} do_test vtab5.3.2 { catchsql { CREATE TRIGGER trig AFTER INSERT ON echo_strings BEGIN SELECT 1, 2, 3; END; } } {1 {cannot create triggers on virtual tables}} do_test vtab5.3.2 { catchsql { CREATE TRIGGER trig BEFORE INSERT ON echo_strings BEGIN SELECT 1, 2, 3; END; } } {1 {cannot create triggers on virtual tables}} } # Test that it is impossible to create an index on a virtual table. # do_test vtab5.4.1 { catchsql { CREATE INDEX echo_strings_i ON echo_strings(str); } } {1 {virtual tables may not be indexed}} # Test that it is impossible to add a column to a virtual table. # do_test vtab5.4.2 { catchsql { ALTER TABLE echo_strings ADD COLUMN col2; } } {1 {virtual tables may not be altered}} # Test that it is impossible to rename a virtual table. # UPDATE: It is now possible. # # do_test vtab5.4.3 { # catchsql { # ALTER TABLE echo_strings RENAME TO echo_strings2; # } # } {1 {virtual tables may not be altered}} finish_test