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 do_test vtab5.4.2 {

   138   catchsql {

   139     ALTER TABLE echo_strings ADD COLUMN col2;

   140   }

   141 } {1 {virtual tables may not be altered}}

   142 

   143 # Test that it is impossible to rename a virtual table.

   144 # UPDATE: It is now possible.

   145 #

   146 # do_test vtab5.4.3 {

   147 #   catchsql {

   148 #     ALTER TABLE echo_strings RENAME TO echo_strings2;

   149 #   }

   150 # } {1 {virtual tables may not be altered}}

   151 

   152 finish_test