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