Return to vtab5.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

Annotation of embedaddon/sqlite3/test/vtab5.test, revision 1.1

1.1     ! misho       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: 
        !            15: set testdir [file dirname $argv0]
        !            16: source $testdir/tester.tcl
        !            17: 
        !            18: ifcapable !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: #
        !            28: register_echo_module [sqlite3_connection_pointer db]
        !            29: do_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: } {}
        !            36: do_test vtab5.1.2 {
        !            37:   execsql {
        !            38:     SELECT * FROM techo;
        !            39:   }
        !            40: } {a b c}
        !            41: do_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}
        !            50: do_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}
        !            59: do_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}
        !            68: do_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: #
        !            79: do_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}
        !            89: do_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}
        !            95: do_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: #
        !           103: ifcapable 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: #
        !           129: do_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: #
        !           137: ifcapable 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: 
        !           154: finish_test