Return to vtab5.test CVS log

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

sqlite3

    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.1.1.1 2012/02/21 17:04:16 misho 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