Return to vtab_shared.test CVS log

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

sqlite3

    1: # 2007 April 16
    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 tests interactions between the virtual table and
   12: # shared-schema functionality.
   13: #
   14: # $Id: vtab_shared.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
   15: 
   16: set testdir [file dirname $argv0]
   17: source $testdir/tester.tcl
   18: 
   19: ifcapable !vtab||!shared_cache {
   20:   finish_test
   21:   return
   22: }
   23: 
   24: db close
   25: sqlite3_enable_shared_cache 1
   26: sqlite3 db  test.db
   27: sqlite3 db2 test.db
   28: 
   29: do_test vtab_shared-1.1 {
   30:   register_echo_module [sqlite3_connection_pointer db]
   31:   execsql {
   32:     CREATE TABLE t0(a, b, c);
   33:     INSERT INTO t0 VALUES(1, 2, 3);
   34:     CREATE VIRTUAL TABLE t1 USING echo(t0);
   35:   }
   36: } {}
   37: 
   38: do_test vtab_shared-1.2 {
   39:   execsql { SELECT * FROM t1 } db
   40: } {1 2 3}
   41: 
   42: # Fails because the 'echo' module has not been registered with connection db2
   43: do_test vtab_shared-1.3 {
   44:   catchsql { SELECT * FROM t1 } db2
   45: } {1 {no such module: echo}}
   46: 
   47: do_test vtab_shared-1.4 {
   48:   execsql { SELECT * FROM t0 } db2
   49: } {1 2 3}
   50: 
   51: do_test vtab_shared-1.5 {
   52:   register_echo_module [sqlite3_connection_pointer db2]
   53:   execsql { SELECT * FROM t1 } db
   54: } {1 2 3}
   55: 
   56: # Works after the module is registered with db2
   57: do_test vtab_shared-1.6 {
   58:   execsql { SELECT * FROM t1 } db2
   59: } {1 2 3}
   60: 
   61: # Set a write-lock on table t0 using connection [db]. Then try to read from
   62: # virtual table t1 using [db2]. That this returns an SQLITE_LOCKED error
   63: # shows that the correct sqlite3_vtab is being used.
   64: #
   65: do_test vtab_shared-1.8.1 {
   66:   execsql { 
   67:     BEGIN;
   68:     INSERT INTO t1 VALUES(4, 5, 6);
   69:     SELECT * FROM t1;
   70:   }
   71: } {1 2 3 4 5 6}
   72: do_test vtab_shared-1.8.2 {
   73:   catchsql { SELECT * FROM t1 } db2
   74: } {1 {database table is locked}}
   75: do_test vtab_shared-1.8.3 {
   76:   catchsql { SELECT *  FROM t0 } db2
   77: } {1 {database table is locked: t0}}
   78: do_test vtab_shared-1.8.4 {
   79:   execsql { SELECT * FROM t0 } db
   80: } {1 2 3 4 5 6}
   81: do_test vtab_shared-1.8.5 {
   82:   execsql { COMMIT } db
   83:   execsql { SELECT *  FROM t1 } db2
   84: } {1 2 3 4 5 6}
   85: 
   86: # While a SELECT is active on virtual table t1 via connection [db], close 
   87: # [db2]. This causes the schema to be reset internally. Verify that this
   88: # does not cause a problem.
   89: #
   90: foreach {iTest dbSelect dbClose} {
   91:   1 db  db2
   92:   2 db  db2
   93:   3 db2 db
   94: } {
   95:   do_test vtab_shared-1.9.$iTest {
   96:     set res [list]
   97:     $dbSelect eval { SELECT * FROM t1 } {
   98:       if {$a == 1} {$dbClose close}
   99:       lappend res $a $b $c
  100:     }
  101:     sqlite3 $dbClose test.db
  102:     register_echo_module [sqlite3_connection_pointer $dbClose]
  103:     set res
  104:   } {1 2 3 4 5 6}
  105: }
  106: 
  107: # Ensure that it is not possible for one connection to DROP a virtual
  108: # table while a second connection is reading from the database.
  109: #
  110: do_test vtab_shared-1.10 {
  111:   db eval { SELECT * FROM t1 } {
  112:     set error [catchsql { DROP TABLE t1 } db2]
  113:     break
  114:   }
  115:   set error
  116: } {1 {database table is locked: sqlite_master}}
  117: 
  118: do_test vtab_shared-1.11 {
  119: breakpoint
  120:   execsql {
  121:     CREATE VIRTUAL TABLE t2 USING echo(t0);
  122:     CREATE VIRTUAL TABLE t3 USING echo(t0);
  123:   }
  124:   execsql { SELECT * FROM t3 } db2
  125: } {1 2 3 4 5 6}
  126: 
  127: do_test vtab_shared-1.12.1 {
  128:   db close
  129:   execsql { 
  130:     SELECT * FROM t1 UNION ALL
  131:     SELECT * FROM t2 UNION ALL
  132:     SELECT * FROM t3 
  133:   } db2
  134: } {1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6}
  135: do_test vtab_shared-1.12.2 {
  136:   sqlite3 db test.db
  137:   register_echo_module [sqlite3_connection_pointer db]
  138:   execsql { 
  139:     SELECT * FROM t1 UNION ALL
  140:     SELECT * FROM t2 UNION ALL
  141:     SELECT * FROM t3 
  142:   } db
  143: } {1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6}
  144: 
  145: # Try a rename or two.
  146: #
  147: ifcapable altertable {
  148:   do_test vtab_shared-1.13.1 {
  149:     execsql { ALTER TABLE t1 RENAME TO t4 }
  150:     execsql { SELECT * FROM t4 } db
  151:   } {1 2 3 4 5 6}
  152:   do_test vtab_shared-1.13.2 {
  153:     execsql { SELECT * FROM t4 } db2
  154:   } {1 2 3 4 5 6}
  155:   do_test vtab_shared-1.13.3 {
  156:     execsql { ALTER TABLE t2 RENAME TO t5 }
  157:     execsql { SELECT * FROM t4 } db2
  158:   } {1 2 3 4 5 6}
  159: }
  160: 
  161: # Try an UPDATE/INSERT/DELETE on a shared vtab as the first statement after a
  162: # schema is loaded.
  163: do_test vtab_shared_1.14.1 {
  164:   db2 close
  165:   sqlite3 db2 test.db
  166:   register_echo_module [sqlite3_connection_pointer db2]
  167:   execsql { SELECT * FROM t3 }
  168: } {1 2 3 4 5 6}
  169: do_test vtab_shared_1.14.2 {
  170:   execsql { 
  171:     UPDATE t3 SET c = 'six' WHERE c = 6;
  172:     SELECT * FROM t3;
  173:   } db2
  174: } {1 2 3 4 5 six}
  175: do_test vtab_shared_1.14.3 {
  176:   db2 close
  177:   sqlite3 db2 test.db
  178:   register_echo_module [sqlite3_connection_pointer db2]
  179:   execsql { SELECT * FROM t3 }
  180: } {1 2 3 4 5 six}
  181: do_test vtab_shared_1.14.4 {
  182:   execsql { 
  183:     DELETE FROM t3 WHERE c = 'six';
  184:     SELECT * FROM t3;
  185:   } db2
  186: } {1 2 3}
  187: do_test vtab_shared_1.14.5 {
  188:   db2 close
  189:   sqlite3 db2 test.db
  190:   register_echo_module [sqlite3_connection_pointer db2]
  191:   execsql { SELECT * FROM t3 }
  192: } {1 2 3}
  193: do_test vtab_shared_1.14.6 {
  194:   execsql { 
  195:     INSERT INTO t3 VALUES(4, 5, 6);
  196:     SELECT * FROM t3;
  197:   } db2
  198: } {1 2 3 4 5 6}
  199: 
  200: do_test vtab_shared_1.15.1 {
  201:   db2 close
  202:   sqlite3 db2 test.db
  203:   register_echo_module [sqlite3_connection_pointer db2]
  204:   execsql { 
  205:     UPDATE t3 SET c = 'six' WHERE c = 6;
  206:     SELECT * FROM t3;
  207:   } db2
  208: } {1 2 3 4 5 six}
  209: do_test vtab_shared_1.15.2 {
  210:   db2 close
  211:   sqlite3 db2 test.db
  212:   register_echo_module [sqlite3_connection_pointer db2]
  213:   execsql { 
  214:     DELETE FROM t3 WHERE c = 'six';
  215:     SELECT * FROM t3;
  216:   } db2
  217: } {1 2 3}
  218: do_test vtab_shared_1.15.3 {
  219:   db2 close
  220:   sqlite3 db2 test.db
  221:   register_echo_module [sqlite3_connection_pointer db2]
  222:   execsql { 
  223:     INSERT INTO t3 VALUES(4, 5, 6);
  224:     SELECT * FROM t3;
  225:   }
  226: } {1 2 3 4 5 6}
  227: 
  228: db close
  229: db2 close
  230: sqlite3_enable_shared_cache 0
  231: finish_test