1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/vtab5.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,152 @@
1.4 +# 2006 June 10
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library.
1.15 +#
1.16 +# $Id: vtab5.test,v 1.8 2008/07/12 14:52:21 drh Exp $
1.17 +
1.18 +set testdir [file dirname $argv0]
1.19 +source $testdir/tester.tcl
1.20 +
1.21 +ifcapable !vtab {
1.22 + finish_test
1.23 + return
1.24 +}
1.25 +
1.26 +# The following tests - vtab5-1.* - ensure that an INSERT, DELETE or UPDATE
1.27 +# statement can be executed immediately after a CREATE or schema reload. The
1.28 +# point here is testing that the parser always calls xConnect() before the
1.29 +# schema of a virtual table is used.
1.30 +#
1.31 +register_echo_module [sqlite3_connection_pointer db]
1.32 +do_test vtab5-1.1 {
1.33 + execsql {
1.34 + CREATE TABLE treal(a VARCHAR(16), b INTEGER, c FLOAT);
1.35 + INSERT INTO treal VALUES('a', 'b', 'c');
1.36 + CREATE VIRTUAL TABLE techo USING echo(treal);
1.37 + }
1.38 +} {}
1.39 +do_test vtab5.1.2 {
1.40 + execsql {
1.41 + SELECT * FROM techo;
1.42 + }
1.43 +} {a b c}
1.44 +do_test vtab5.1.3 {
1.45 + db close
1.46 + sqlite3 db test.db
1.47 + register_echo_module [sqlite3_connection_pointer db]
1.48 + execsql {
1.49 + INSERT INTO techo VALUES('c', 'd', 'e');
1.50 + SELECT * FROM techo;
1.51 + }
1.52 +} {a b c c d e}
1.53 +do_test vtab5.1.4 {
1.54 + db close
1.55 + sqlite3 db test.db
1.56 + register_echo_module [sqlite3_connection_pointer db]
1.57 + execsql {
1.58 + UPDATE techo SET a = 10;
1.59 + SELECT * FROM techo;
1.60 + }
1.61 +} {10 b c 10 d e}
1.62 +do_test vtab5.1.5 {
1.63 + db close
1.64 + sqlite3 db test.db
1.65 + register_echo_module [sqlite3_connection_pointer db]
1.66 + execsql {
1.67 + DELETE FROM techo WHERE b > 'c';
1.68 + SELECT * FROM techo;
1.69 + }
1.70 +} {10 b c}
1.71 +do_test vtab5.1.X {
1.72 + execsql {
1.73 + DROP TABLE techo;
1.74 + DROP TABLE treal;
1.75 + }
1.76 +} {}
1.77 +
1.78 +# The following tests - vtab5-2.* - ensure that collation sequences
1.79 +# assigned to virtual table columns via the "CREATE TABLE" statement
1.80 +# passed to sqlite3_declare_vtab() are used correctly.
1.81 +#
1.82 +do_test vtab5.2.1 {
1.83 + execsql {
1.84 + CREATE TABLE strings(str COLLATE NOCASE);
1.85 + INSERT INTO strings VALUES('abc1');
1.86 + INSERT INTO strings VALUES('Abc3');
1.87 + INSERT INTO strings VALUES('ABc2');
1.88 + INSERT INTO strings VALUES('aBc4');
1.89 + SELECT str FROM strings ORDER BY 1;
1.90 + }
1.91 +} {abc1 ABc2 Abc3 aBc4}
1.92 +do_test vtab5.2.2 {
1.93 + execsql {
1.94 + CREATE VIRTUAL TABLE echo_strings USING echo(strings);
1.95 + SELECT str FROM echo_strings ORDER BY 1;
1.96 + }
1.97 +} {abc1 ABc2 Abc3 aBc4}
1.98 +do_test vtab5.2.3 {
1.99 + execsql {
1.100 + SELECT str||'' FROM echo_strings ORDER BY 1;
1.101 + }
1.102 +} {ABc2 Abc3 aBc4 abc1}
1.103 +
1.104 +# Test that it is impossible to create a triggger on a virtual table.
1.105 +#
1.106 +ifcapable trigger {
1.107 + do_test vtab5.3.1 {
1.108 + catchsql {
1.109 + CREATE TRIGGER trig INSTEAD OF INSERT ON echo_strings BEGIN
1.110 + SELECT 1, 2, 3;
1.111 + END;
1.112 + }
1.113 + } {1 {cannot create triggers on virtual tables}}
1.114 + do_test vtab5.3.2 {
1.115 + catchsql {
1.116 + CREATE TRIGGER trig AFTER INSERT ON echo_strings BEGIN
1.117 + SELECT 1, 2, 3;
1.118 + END;
1.119 + }
1.120 + } {1 {cannot create triggers on virtual tables}}
1.121 + do_test vtab5.3.2 {
1.122 + catchsql {
1.123 + CREATE TRIGGER trig BEFORE INSERT ON echo_strings BEGIN
1.124 + SELECT 1, 2, 3;
1.125 + END;
1.126 + }
1.127 + } {1 {cannot create triggers on virtual tables}}
1.128 +}
1.129 +
1.130 +# Test that it is impossible to create an index on a virtual table.
1.131 +#
1.132 +do_test vtab5.4.1 {
1.133 + catchsql {
1.134 + CREATE INDEX echo_strings_i ON echo_strings(str);
1.135 + }
1.136 +} {1 {virtual tables may not be indexed}}
1.137 +
1.138 +# Test that it is impossible to add a column to a virtual table.
1.139 +#
1.140 +do_test vtab5.4.2 {
1.141 + catchsql {
1.142 + ALTER TABLE echo_strings ADD COLUMN col2;
1.143 + }
1.144 +} {1 {virtual tables may not be altered}}
1.145 +
1.146 +# Test that it is impossible to rename a virtual table.
1.147 +# UPDATE: It is now possible.
1.148 +#
1.149 +# do_test vtab5.4.3 {
1.150 +# catchsql {
1.151 +# ALTER TABLE echo_strings RENAME TO echo_strings2;
1.152 +# }
1.153 +# } {1 {virtual tables may not be altered}}
1.154 +
1.155 +finish_test