Home | History | Annotate | Download | only in test 
      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
    155