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# $Id: vtab5.test,v 1.8 2008/07/12 14:52:21 drh Exp $ 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18ifcapable !vtab { 19 finish_test 20 return 21} 22 23# The following tests - vtab5-1.* - ensure that an INSERT, DELETE or UPDATE 24# statement can be executed immediately after a CREATE or schema reload. The 25# point here is testing that the parser always calls xConnect() before the 26# schema of a virtual table is used. 27# 28register_echo_module [sqlite3_connection_pointer db] 29do_test vtab5-1.1 { 30 execsql { 31 CREATE TABLE treal(a VARCHAR(16), b INTEGER, c FLOAT); 32 INSERT INTO treal VALUES('a', 'b', 'c'); 33 CREATE VIRTUAL TABLE techo USING echo(treal); 34 } 35} {} 36do_test vtab5.1.2 { 37 execsql { 38 SELECT * FROM techo; 39 } 40} {a b c} 41do_test vtab5.1.3 { 42 db close 43 sqlite3 db test.db 44 register_echo_module [sqlite3_connection_pointer db] 45 execsql { 46 INSERT INTO techo VALUES('c', 'd', 'e'); 47 SELECT * FROM techo; 48 } 49} {a b c c d e} 50do_test vtab5.1.4 { 51 db close 52 sqlite3 db test.db 53 register_echo_module [sqlite3_connection_pointer db] 54 execsql { 55 UPDATE techo SET a = 10; 56 SELECT * FROM techo; 57 } 58} {10 b c 10 d e} 59do_test vtab5.1.5 { 60 db close 61 sqlite3 db test.db 62 register_echo_module [sqlite3_connection_pointer db] 63 execsql { 64 DELETE FROM techo WHERE b > 'c'; 65 SELECT * FROM techo; 66 } 67} {10 b c} 68do_test vtab5.1.X { 69 execsql { 70 DROP TABLE techo; 71 DROP TABLE treal; 72 } 73} {} 74 75# The following tests - vtab5-2.* - ensure that collation sequences 76# assigned to virtual table columns via the "CREATE TABLE" statement 77# passed to sqlite3_declare_vtab() are used correctly. 78# 79do_test vtab5.2.1 { 80 execsql { 81 CREATE TABLE strings(str COLLATE NOCASE); 82 INSERT INTO strings VALUES('abc1'); 83 INSERT INTO strings VALUES('Abc3'); 84 INSERT INTO strings VALUES('ABc2'); 85 INSERT INTO strings VALUES('aBc4'); 86 SELECT str FROM strings ORDER BY 1; 87 } 88} {abc1 ABc2 Abc3 aBc4} 89do_test vtab5.2.2 { 90 execsql { 91 CREATE VIRTUAL TABLE echo_strings USING echo(strings); 92 SELECT str FROM echo_strings ORDER BY 1; 93 } 94} {abc1 ABc2 Abc3 aBc4} 95do_test vtab5.2.3 { 96 execsql { 97 SELECT str||'' FROM echo_strings ORDER BY 1; 98 } 99} {ABc2 Abc3 aBc4 abc1} 100 101# Test that it is impossible to create a triggger on a virtual table. 102# 103ifcapable trigger { 104 do_test vtab5.3.1 { 105 catchsql { 106 CREATE TRIGGER trig INSTEAD OF INSERT ON echo_strings BEGIN 107 SELECT 1, 2, 3; 108 END; 109 } 110 } {1 {cannot create triggers on virtual tables}} 111 do_test vtab5.3.2 { 112 catchsql { 113 CREATE TRIGGER trig AFTER INSERT ON echo_strings BEGIN 114 SELECT 1, 2, 3; 115 END; 116 } 117 } {1 {cannot create triggers on virtual tables}} 118 do_test vtab5.3.2 { 119 catchsql { 120 CREATE TRIGGER trig BEFORE INSERT ON echo_strings BEGIN 121 SELECT 1, 2, 3; 122 END; 123 } 124 } {1 {cannot create triggers on virtual tables}} 125} 126 127# Test that it is impossible to create an index on a virtual table. 128# 129do_test vtab5.4.1 { 130 catchsql { 131 CREATE INDEX echo_strings_i ON echo_strings(str); 132 } 133} {1 {virtual tables may not be indexed}} 134 135# Test that it is impossible to add a column to a virtual table. 136# 137ifcapable altertable { 138 do_test vtab5.4.2 { 139 catchsql { 140 ALTER TABLE echo_strings ADD COLUMN col2; 141 } 142 } {1 {virtual tables may not be altered}} 143} 144 145# Test that it is impossible to rename a virtual table. 146# UPDATE: It is now possible. 147# 148# do_test vtab5.4.3 { 149# catchsql { 150# ALTER TABLE echo_strings RENAME TO echo_strings2; 151# } 152# } {1 {virtual tables may not be altered}} 153 154finish_test 155