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