1 # 2007 March 24

     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. The focus

    12 # of these tests is exclusive access mode (i.e. the thing activated by 

    13 # "PRAGMA locking_mode = EXCLUSIVE").

    14 #

    15 # $Id: exclusive.test,v 1.9 2008/09/24 14:03:43 danielk1977 Exp $

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 ifcapable {!pager_pragmas} {

    21   finish_test

    22   return

    23 }

    24 

    25 file delete -force test2.db-journal

    26 file delete -force test2.db

    27 file delete -force test3.db-journal

    28 file delete -force test3.db

    29 file delete -force test4.db-journal

    30 file delete -force test4.db

    31 

    32 # The locking mode for the TEMP table is always "exclusive" for

    33 # on-disk tables and "normal" for in-memory tables.

    34 #

    35 if {[info exists TEMP_STORE] && $TEMP_STORE>=2} {

    36   set temp_mode normal

    37 } else {

    38   set temp_mode exclusive

    39 }

    40 

    41 #----------------------------------------------------------------------

    42 # Test cases exclusive-1.X test the PRAGMA logic.

    43 #

    44 do_test exclusive-1.0 {

    45   execsql {

    46     pragma locking_mode;

    47     pragma main.locking_mode;

    48     pragma temp.locking_mode;

    49   } 

    50 } [list normal normal $temp_mode]

    51 do_test exclusive-1.1 {

    52   execsql {

    53     pragma locking_mode = exclusive;

    54   } 

    55 } {exclusive}

    56 do_test exclusive-1.2 {

    57   execsql {

    58     pragma locking_mode;

    59     pragma main.locking_mode;

    60     pragma temp.locking_mode;

    61   } 

    62 } [list exclusive exclusive $temp_mode]

    63 do_test exclusive-1.3 {

    64   execsql {

    65     pragma locking_mode = normal;

    66   } 

    67 } {normal}

    68 do_test exclusive-1.4 {

    69   execsql {

    70     pragma locking_mode;

    71     pragma main.locking_mode;

    72     pragma temp.locking_mode;

    73   } 

    74 } [list normal normal $temp_mode]

    75 do_test exclusive-1.5 {

    76   execsql {

    77     pragma locking_mode = invalid;

    78   } 

    79 } {normal}

    80 do_test exclusive-1.6 {

    81   execsql {

    82     pragma locking_mode;

    83     pragma main.locking_mode;

    84     pragma temp.locking_mode;

    85   } 

    86 } [list normal normal $temp_mode]

    87 ifcapable attach {

    88   do_test exclusive-1.7 {

    89     execsql {

    90       pragma locking_mode = exclusive;

    91       ATTACH 'test2.db' as aux;

    92     }

    93     execsql {

    94       pragma main.locking_mode;

    95       pragma aux.locking_mode;

    96     }

    97   } {exclusive exclusive}

    98   do_test exclusive-1.8 {

    99     execsql {

   100       pragma main.locking_mode = normal;

   101     }

   102     execsql {

   103       pragma main.locking_mode;

   104       pragma temp.locking_mode;

   105       pragma aux.locking_mode;

   106     }

   107   } [list normal $temp_mode exclusive]

   108   do_test exclusive-1.9 {

   109     execsql {

   110       pragma locking_mode;

   111     }

   112   } {exclusive}

   113   do_test exclusive-1.10 {

   114     execsql {

   115       ATTACH 'test3.db' as aux2;

   116     }

   117     execsql {

   118       pragma main.locking_mode;

   119       pragma aux.locking_mode;

   120       pragma aux2.locking_mode;

   121     }

   122   } {normal exclusive exclusive}

   123   do_test exclusive-1.11 {

   124     execsql {

   125       pragma aux.locking_mode = normal;

   126     }

   127     execsql {

   128       pragma main.locking_mode;

   129       pragma aux.locking_mode;

   130       pragma aux2.locking_mode;

   131     }

   132   } {normal normal exclusive}

   133   do_test exclusive-1.12 {

   134     execsql {

   135       pragma locking_mode = normal;

   136     }

   137     execsql {

   138       pragma main.locking_mode;

   139       pragma temp.locking_mode;

   140       pragma aux.locking_mode;

   141       pragma aux2.locking_mode;

   142     }

   143   } [list normal $temp_mode normal normal]

   144   do_test exclusive-1.13 {

   145     execsql {

   146       ATTACH 'test4.db' as aux3;

   147     }

   148     execsql {

   149       pragma main.locking_mode;

   150       pragma temp.locking_mode;

   151       pragma aux.locking_mode;

   152       pragma aux2.locking_mode;

   153       pragma aux3.locking_mode;

   154     }

   155   } [list normal $temp_mode normal normal normal]

   156   

   157   do_test exclusive-1.99 {

   158     execsql {

   159       DETACH aux;

   160       DETACH aux2;

   161       DETACH aux3;

   162     }

   163   } {}

   164 }

   165 

   166 #----------------------------------------------------------------------

   167 # Test cases exclusive-2.X verify that connections in exclusive 

   168 # locking_mode do not relinquish locks.

   169 #

   170 do_test exclusive-2.0 {

   171   execsql {

   172     CREATE TABLE abc(a, b, c);

   173     INSERT INTO abc VALUES(1, 2, 3);

   174     PRAGMA locking_mode = exclusive;

   175   }

   176 } {exclusive}

   177 do_test exclusive-2.1 {

   178   sqlite3 db2 test.db

   179   execsql {

   180     INSERT INTO abc VALUES(4, 5, 6);

   181     SELECT * FROM abc;

   182   } db2

   183 } {1 2 3 4 5 6}

   184 do_test exclusive-2.2 {

   185   # This causes connection 'db' (in exclusive mode) to establish 

   186   # a shared-lock on the db. The other connection should now be

   187   # locked out as a writer.

   188   execsql {

   189     SELECT * FROM abc;

   190   } db

   191 } {1 2 3 4 5 6}

   192 do_test exclusive-2.4 {

   193   execsql {

   194     SELECT * FROM abc;

   195   } db2

   196 } {1 2 3 4 5 6}

   197 do_test exclusive-2.5 {

   198   catchsql {

   199     INSERT INTO abc VALUES(7, 8, 9);

   200   } db2

   201 } {1 {database is locked}}

   202 sqlite3_soft_heap_limit 0

   203 do_test exclusive-2.6 {

   204   # Because connection 'db' only has a shared-lock, the other connection

   205   # will be able to get a RESERVED, but will fail to upgrade to EXCLUSIVE.

   206   execsql {

   207     BEGIN;

   208     INSERT INTO abc VALUES(7, 8, 9);

   209   } db2

   210   catchsql {

   211     COMMIT

   212   } db2

   213 } {1 {database is locked}}

   214 do_test exclusive-2.7 {

   215   catchsql {

   216     COMMIT

   217   } db2

   218 } {1 {database is locked}}

   219 do_test exclusive-2.8 {

   220   execsql {

   221     ROLLBACK;

   222   } db2

   223 } {}

   224 sqlite3_soft_heap_limit $soft_limit

   225 

   226 do_test exclusive-2.9 {

   227   # Write the database to establish the exclusive lock with connection 'db.

   228   execsql {

   229     INSERT INTO abc VALUES(7, 8, 9);

   230   } db

   231   catchsql {

   232     SELECT * FROM abc;

   233   } db2

   234 } {1 {database is locked}}

   235 do_test exclusive-2.10 {

   236   # Changing the locking-mode does not release any locks.

   237   execsql {

   238     PRAGMA locking_mode = normal;

   239   } db

   240   catchsql {

   241     SELECT * FROM abc;

   242   } db2

   243 } {1 {database is locked}}

   244 do_test exclusive-2.11 {

   245   # After changing the locking mode, accessing the db releases locks.

   246   execsql {

   247     SELECT * FROM abc;

   248   } db

   249   execsql {

   250     SELECT * FROM abc;

   251   } db2

   252 } {1 2 3 4 5 6 7 8 9}

   253 db2 close

   254 

   255 #----------------------------------------------------------------------

   256 # Tests exclusive-3.X - test that a connection in exclusive mode 

   257 # truncates instead of deletes the journal file when committing 

   258 # a transaction.

   259 #

   260 # These tests are not run on windows because the windows backend

   261 # opens the journal file for exclusive access, preventing its contents 

   262 # from being inspected externally.

   263 #

   264 if {$tcl_platform(platform) != "windows"} {

   265   proc filestate {fname} {

   266     set exists 0

   267     set content 0

   268     if {[file exists $fname]} {

   269       set exists 1

   270       set hdr [hexio_read $fname 0 28]

   271       set content \

   272        [expr {$hdr!="00000000000000000000000000000000000000000000000000000000"}]

   273     }

   274     list $exists $content

   275   }

   276   do_test exclusive-3.0 {

   277     filestate test.db-journal

   278   } {0 0}

   279   do_test exclusive-3.1 {

   280     execsql {

   281       PRAGMA locking_mode = exclusive;

   282       BEGIN;

   283       DELETE FROM abc;

   284     }

   285     filestate test.db-journal

   286   } {1 1}

   287   do_test exclusive-3.2 {

   288     execsql {

   289       COMMIT;

   290     }

   291     filestate test.db-journal

   292   } {1 0}

   293   do_test exclusive-3.3 {

   294     execsql {

   295       INSERT INTO abc VALUES('A', 'B', 'C');

   296       SELECT * FROM abc;

   297     }

   298   } {A B C}

   299   do_test exclusive-3.4 {

   300     execsql {

   301       BEGIN;

   302       UPDATE abc SET a = 1, b = 2, c = 3;

   303       ROLLBACK;

   304       SELECT * FROM abc;

   305     }

   306   } {A B C}

   307   do_test exclusive-3.5 {

   308     filestate test.db-journal

   309   } {1 0}

   310   do_test exclusive-3.6 {

   311     execsql {

   312       PRAGMA locking_mode = normal;

   313       SELECT * FROM abc;

   314     }

   315     filestate test.db-journal

   316   } {0 0}

   317 }

   318 

   319 #----------------------------------------------------------------------

   320 # Tests exclusive-4.X - test that rollback works correctly when

   321 # in exclusive-access mode.

   322 #

   323 

   324 # The following procedure computes a "signature" for table "t3".  If

   325 # T3 changes in any way, the signature should change.  

   326 #

   327 # This is used to test ROLLBACK.  We gather a signature for t3, then

   328 # make lots of changes to t3, then rollback and take another signature.

   329 # The two signatures should be the same.

   330 #

   331 proc signature {} {

   332   return [db eval {SELECT count(*), md5sum(x) FROM t3}]

   333 }

   334 

   335 do_test exclusive-4.0 {

   336   execsql { PRAGMA locking_mode = exclusive; }

   337   execsql { PRAGMA default_cache_size = 10; }

   338   execsql {

   339     BEGIN;

   340     CREATE TABLE t3(x TEXT);

   341     INSERT INTO t3 VALUES(randstr(10,400));

   342     INSERT INTO t3 VALUES(randstr(10,400));

   343     INSERT INTO t3 SELECT randstr(10,400) FROM t3;

   344     INSERT INTO t3 SELECT randstr(10,400) FROM t3;

   345     INSERT INTO t3 SELECT randstr(10,400) FROM t3;

   346     INSERT INTO t3 SELECT randstr(10,400) FROM t3;

   347     COMMIT;

   348   }

   349   execsql {SELECT count(*) FROM t3;}

   350 } {32}

   351 

   352 set ::X [signature]

   353 do_test exclusive-4.1 {

   354   execsql {

   355     BEGIN;

   356     DELETE FROM t3 WHERE random()%10!=0;

   357     INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;

   358     INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;

   359     SELECT count(*) FROM t3;

   360     ROLLBACK;

   361   }

   362   signature

   363 } $::X

   364 

   365 do_test exclusive-4.2 {

   366   execsql {

   367     BEGIN;

   368     DELETE FROM t3 WHERE random()%10!=0;

   369     INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;

   370     DELETE FROM t3 WHERE random()%10!=0;

   371     INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;

   372     ROLLBACK;

   373   }

   374   signature

   375 } $::X

   376 

   377 do_test exclusive-4.3 {

   378   execsql {

   379     INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0;

   380   }

   381 } {}

   382 

   383 do_test exclusive-4.4 {

   384   catch {set ::X [signature]}

   385 } {0}

   386 do_test exclusive-4.5 {

   387   execsql {

   388     PRAGMA locking_mode = NORMAL;

   389     DROP TABLE t3;

   390     DROP TABLE abc;

   391   }

   392 } {normal}

   393 

   394 #----------------------------------------------------------------------

   395 # Tests exclusive-5.X - test that statement journals are truncated

   396 # instead of deleted when in exclusive access mode.

   397 #

   398 

   399 # Close and reopen the database so that the temp database is no

   400 # longer active.

   401 #

   402 db close

   403 sqlite db test.db

   404 

   405 

   406 do_test exclusive-5.0 {

   407   execsql {

   408     CREATE TABLE abc(a UNIQUE, b UNIQUE, c UNIQUE);

   409     BEGIN;

   410     INSERT INTO abc VALUES(1, 2, 3);

   411     INSERT INTO abc SELECT a+1, b+1, c+1 FROM abc;

   412   }

   413 } {}

   414 do_test exclusive-5.1 {

   415   # Three files are open: The db, journal and statement-journal.

   416   set sqlite_open_file_count

   417 } {3}

   418 do_test exclusive-5.2 {

   419   execsql {

   420     COMMIT;

   421   }

   422   # One file open: the db.

   423   set sqlite_open_file_count

   424 } {1}

   425 do_test exclusive-5.3 {

   426   execsql {

   427     PRAGMA locking_mode = exclusive;

   428     BEGIN;

   429     INSERT INTO abc VALUES(5, 6, 7);

   430   }

   431   # Two files open: the db and journal.

   432   set sqlite_open_file_count

   433 } {2}

   434 do_test exclusive-5.4 {

   435   execsql {

   436     INSERT INTO abc SELECT a+10, b+10, c+10 FROM abc;

   437   }

   438   # Three files are open: The db, journal and statement-journal.

   439   set sqlite_open_file_count

   440 } {3}

   441 do_test exclusive-5.5 {

   442   execsql {

   443     COMMIT;

   444   }

   445   # Three files are still open: The db, journal and statement-journal.

   446   set sqlite_open_file_count

   447 } {3}

   448 do_test exclusive-5.6 {

   449   execsql {

   450     PRAGMA locking_mode = normal;

   451     SELECT * FROM abc;

   452   }

   453 } {normal 1 2 3 2 3 4 5 6 7 11 12 13 12 13 14 15 16 17}

   454 do_test exclusive-5.7 {

   455   # Just the db open.

   456   set sqlite_open_file_count

   457 } {1}

   458 

   459 finish_test