summaryrefslogtreecommitdiffstats
path: root/test/vtab5.test
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 14:07:11 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 14:07:11 +0000
commit63847496f14c813a5d80efd5b7de0f1294ffe1e3 (patch)
tree01c7571c7c762ceee70638549a99834fdd7c411b /test/vtab5.test
parentInitial commit. (diff)
downloadsqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.tar.xz
sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.zip
Adding upstream version 3.45.1.upstream/3.45.1
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'test/vtab5.test')
-rw-r--r--test/vtab5.test154
1 files changed, 154 insertions, 0 deletions
diff --git a/test/vtab5.test b/test/vtab5.test
new file mode 100644
index 0000000..bb84a5c
--- /dev/null
+++ b/test/vtab5.test
@@ -0,0 +1,154 @@
+# 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.
+#
+ifcapable altertable {
+ 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