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

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