1 # 2008 April 17

     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 the journal mode pragma.

    13 #

    14 # $Id: jrnlmode.test,v 1.6 2008/09/26 21:08:08 drh Exp $

    15 

    16 set testdir [file dirname $argv0]

    17 source $testdir/tester.tcl

    18 

    19 ifcapable {!pager_pragmas} {

    20   finish_test

    21   return

    22 }

    23 

    24 #----------------------------------------------------------------------

    25 # Test cases jrnlmode-1.X test the PRAGMA logic.

    26 #

    27 do_test jrnlmode-1.0 {

    28   execsql {

    29     PRAGMA journal_mode;

    30     PRAGMA main.journal_mode;

    31     PRAGMA temp.journal_mode;

    32   } 

    33 } [list delete delete delete]

    34 do_test jrnlmode-1.1 {

    35   execsql {

    36     PRAGMA journal_mode = persist;

    37   } 

    38 } {persist}

    39 do_test jrnlmode-1.2 {

    40   execsql {

    41     PRAGMA journal_mode;

    42     PRAGMA main.journal_mode;

    43     PRAGMA temp.journal_mode;

    44   } 

    45 } [list persist persist persist]

    46 do_test jrnlmode-1.4 {

    47   execsql {

    48     PRAGMA journal_mode = off;

    49   } 

    50 } {off}

    51 do_test jrnlmode-1.5 {

    52   execsql {

    53     PRAGMA journal_mode;

    54     PRAGMA main.journal_mode;

    55     PRAGMA temp.journal_mode;

    56   } 

    57 } {off off off}

    58 do_test jrnlmode-1.6 {

    59   execsql {

    60     PRAGMA journal_mode = delete;

    61   } 

    62 } {delete}

    63 do_test jrnlmode-1.7 {

    64   execsql {

    65     PRAGMA journal_mode;

    66     PRAGMA main.journal_mode;

    67     PRAGMA temp.journal_mode;

    68   } 

    69 } {delete delete delete}

    70 do_test jrnlmode-1.7.1 {

    71   execsql {

    72     PRAGMA journal_mode = truncate;

    73   } 

    74 } {truncate}

    75 do_test jrnlmode-1.7.2 {

    76   execsql {

    77     PRAGMA journal_mode;

    78     PRAGMA main.journal_mode;

    79     PRAGMA temp.journal_mode;

    80   } 

    81 } {truncate truncate truncate}

    82 do_test jrnlmode-1.8 {

    83   execsql {

    84     PRAGMA journal_mode = off;

    85     PRAGMA journal_mode = invalid;

    86   } 

    87 } {off off}

    88 ifcapable attach {

    89   do_test jrnlmode-1.9 {

    90     execsql {

    91       PRAGMA journal_mode = PERSIST;

    92       ATTACH ':memory:' as aux1;

    93     }

    94     execsql {

    95       PRAGMA main.journal_mode;

    96       PRAGMA aux1.journal_mode;

    97     }

    98   } {persist persist}

    99   do_test jrnlmode-1.10 {

   100     execsql {

   101       PRAGMA main.journal_mode = OFF;

   102     }

   103     execsql {

   104       PRAGMA main.journal_mode;

   105       PRAGMA temp.journal_mode;

   106       PRAGMA aux1.journal_mode;

   107     }

   108   } {off persist persist}

   109   do_test jrnlmode-1.11 {

   110     execsql {

   111       PRAGMA journal_mode;

   112     }

   113   } {persist}

   114   do_test jrnlmode-1.12 {

   115     execsql {

   116       ATTACH ':memory:' as aux2;

   117     }

   118     execsql {

   119       PRAGMA main.journal_mode;

   120       PRAGMA aux1.journal_mode;

   121       PRAGMA aux2.journal_mode;

   122     }

   123   } {off persist persist}

   124   do_test jrnlmode-1.11 {

   125     execsql {

   126       PRAGMA aux1.journal_mode = DELETE;

   127     }

   128     execsql {

   129       PRAGMA main.journal_mode;

   130       PRAGMA aux1.journal_mode;

   131       PRAGMA aux2.journal_mode;

   132     }

   133   } {off delete persist}

   134   do_test jrnlmode-1.12 {

   135     execsql {

   136       PRAGMA journal_mode = delete;

   137     }

   138     execsql {

   139       PRAGMA main.journal_mode;

   140       PRAGMA temp.journal_mode;

   141       PRAGMA aux1.journal_mode;

   142       PRAGMA aux2.journal_mode;

   143     }

   144   } {delete delete delete delete}

   145   do_test jrnlmode-1.13 {

   146     execsql {

   147       ATTACH ':memory:' as aux3;

   148     }

   149     execsql {

   150       PRAGMA main.journal_mode;

   151       PRAGMA temp.journal_mode;

   152       PRAGMA aux1.journal_mode;

   153       PRAGMA aux2.journal_mode;

   154       PRAGMA aux3.journal_mode;

   155     }

   156   } {delete delete delete delete delete}

   157   do_test jrnlmode-1.14 {

   158     execsql {

   159       PRAGMA journal_mode = TRUNCATE;

   160     }

   161     execsql {

   162       PRAGMA main.journal_mode;

   163       PRAGMA temp.journal_mode;

   164       PRAGMA aux1.journal_mode;

   165       PRAGMA aux2.journal_mode;

   166       PRAGMA aux3.journal_mode;

   167     }

   168   } {truncate truncate truncate truncate truncate}

   169   

   170   do_test jrnlmode-1.99 {

   171     execsql {

   172       DETACH aux1;

   173       DETACH aux2;

   174       DETACH aux3;

   175     }

   176   } {}

   177 }

   178 

   179 ifcapable attach {

   180   file delete -force test2.db

   181   do_test jrnlmode-2.1 {

   182     execsql {

   183       ATTACH 'test2.db' AS aux;

   184       PRAGMA main.journal_mode = persist;

   185       PRAGMA aux.journal_mode = persist;

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

   187       CREATE TABLE aux.def(d, e, f);

   188     }

   189     execsql {

   190       BEGIN;

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

   192       INSERT INTO def VALUES(4, 5, 6);

   193       COMMIT;

   194     }

   195     list [file exists test.db-journal] [file exists test2.db-journal]

   196   } {1 1}

   197 

   198   do_test jrnlmode-2.2 {

   199     file size test.db-journal

   200   } {0}

   201 

   202   do_test jrnlmode-2.3 {

   203     execsql {

   204       SELECT * FROM abc;

   205     }

   206   } {1 2 3}

   207 

   208   do_test jrnlmode-2.4 {

   209     file size test.db-journal

   210   } {0}

   211 

   212   do_test jrnlmode-2.5 {

   213     execsql {

   214       SELECT * FROM def;

   215     }

   216   } {4 5 6}

   217 

   218 #----------------------------------------------------------------------

   219 # Test caes jrnlmode-3.X verify that ticket #3127 has been fixed.

   220 #

   221   db close

   222   file delete -force test2.db

   223   file delete -force test.db

   224   sqlite3 db test.db

   225 

   226   do_test jrnlmode-3.1 {

   227     execsql { 

   228       CREATE TABLE x(n INTEGER); 

   229       ATTACH 'test2.db' AS a; 

   230       create table a.x ( n integer ); 

   231       insert into a.x values(1); 

   232       insert into a.x values (2); 

   233       insert into a.x values (3); 

   234       insert into a.x values (4); 

   235     }

   236   } {}

   237   

   238   do_test jrnlmode-3.2 {

   239     execsql { PRAGMA journal_mode=off; }

   240     execsql { 

   241       BEGIN IMMEDIATE;

   242       INSERT OR IGNORE INTO main.x SELECT * FROM a.x;

   243       COMMIT;

   244     }

   245   } {}

   246 }

   247 

   248 ifcapable autovacuum&&pragma {

   249   db close

   250   file delete -force test.db

   251   sqlite3 db test.db

   252   do_test jrnlmode-4.1 {

   253     execsql {

   254       PRAGMA cache_size = 1;

   255       PRAGMA auto_vacuum = 1;

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

   257     }

   258     execsql { PRAGMA page_count }

   259   } {3}

   260 

   261   do_test jrnlmode-4.2 {

   262     execsql { PRAGMA journal_mode = off }

   263   } {off}

   264 

   265   do_test jrnlmode-4.3 {

   266     execsql { INSERT INTO abc VALUES(1, 2, randomblob(2000)) }

   267   } {}

   268 

   269   # This will attempt to truncate the database file. Check that this

   270   # is not a problem when journal_mode=off.

   271   do_test jrnlmode-4.4 {

   272     execsql { DELETE FROM abc }

   273   } {}

   274 

   275   integrity_check jrnlmode-4.5

   276 }

   277 

   278 #------------------------------------------------------------------------

   279 # The following test caes, jrnlmode-5.*, test the journal_size_limit

   280 # pragma.

   281 ifcapable pragma {

   282   db close

   283   file delete -force test.db test2.db test3.db

   284   sqlite3 db test.db

   285 

   286   do_test jrnlmode-5.1 {

   287     execsql {pragma page_size=1024}

   288     execsql {pragma journal_mode=persist}

   289   } {persist}

   290 

   291   do_test jrnlmode-5.2 {

   292     execsql { PRAGMA journal_size_limit }

   293   } {-1}

   294   do_test jrnlmode-5.3 {

   295     execsql { 

   296       ATTACH 'test2.db' AS aux;

   297       PRAGMA aux.journal_size_limit;

   298     }

   299   } {-1}

   300   do_test jrnlmode-5.4 {

   301     execsql { PRAGMA aux.journal_size_limit = 10240 }

   302   } {10240}

   303   do_test jrnlmode-5.5 {

   304     execsql { PRAGMA main.journal_size_limit = 20480 }

   305   } {20480}

   306   do_test jrnlmode-5.6 {

   307     execsql { PRAGMA journal_size_limit }

   308   } {20480}

   309   do_test jrnlmode-5.7 {

   310     execsql { PRAGMA aux.journal_size_limit }

   311   } {10240}

   312 

   313   do_test jrnlmode-5.8 {

   314     execsql { ATTACH 'test3.db' AS aux2 }

   315   } {}

   316 

   317   do_test jrnlmode-5.9 {

   318     execsql {

   319       CREATE TABLE main.t1(a, b, c);

   320       CREATE TABLE aux.t2(a, b, c);

   321       CREATE TABLE aux2.t3(a, b, c);

   322     }

   323   } {}

   324   do_test jrnlmode-5.10 {

   325     list \

   326       [file exists test.db-journal]  \

   327       [file exists test2.db-journal] \

   328       [file exists test3.db-journal]

   329   } {1 1 1}

   330   do_test jrnlmode-5.11 {

   331     execsql {

   332       BEGIN;

   333       INSERT INTO t3 VALUES(randomblob(1000),randomblob(1000),randomblob(1000));

   334       INSERT INTO t3 

   335           SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;

   336       INSERT INTO t3 

   337           SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;

   338       INSERT INTO t3 

   339           SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;

   340       INSERT INTO t3 

   341           SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;

   342       INSERT INTO t3 

   343           SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;

   344       INSERT INTO t2 SELECT * FROM t3;

   345       INSERT INTO t1 SELECT * FROM t2;

   346       COMMIT;

   347     }

   348     list \

   349       [file exists test.db-journal]  \

   350       [file exists test2.db-journal] \

   351       [file exists test3.db-journal] \

   352       [file size test.db-journal]    \

   353       [file size test2.db-journal]   \

   354       [file size test3.db-journal]

   355   } {1 1 1 0 0 0}

   356 

   357   do_test jrnlmode-5.12 {

   358     execsql {

   359       BEGIN;

   360       UPDATE t1 SET a = randomblob(1000);

   361     }

   362     expr {[file size test.db-journal]>30000}

   363   } {1}

   364   do_test jrnlmode-5.13 {

   365     execsql COMMIT

   366     file size test.db-journal

   367   } {20480}

   368 

   369   do_test jrnlmode-5.14 {

   370     execsql {

   371       BEGIN;

   372       UPDATE t2 SET a = randomblob(1000);

   373     }

   374     expr {[file size test2.db-journal]>30000}

   375   } {1}

   376   do_test jrnlmode-5.15 {

   377     execsql COMMIT

   378     file size test2.db-journal

   379   } {10240}

   380 

   381   do_test jrnlmode-5.16 {

   382     execsql {

   383       BEGIN;

   384       UPDATE t3 SET a = randomblob(1000);

   385     }

   386     set journalsize [file size test3.db-journal]

   387     expr {$journalsize>30000}

   388   } {1}

   389   do_test jrnlmode-5.17 {

   390     execsql COMMIT

   391     set sz [file size test3.db-journal]

   392     expr {$sz>=$journalsize}

   393   } {1}

   394 

   395   do_test jrnlmode-5.18 {

   396     execsql {

   397       PRAGMA journal_size_limit = -4;

   398       BEGIN;

   399       UPDATE t1 SET a = randomblob(1000);

   400     }

   401     set journalsize [file size test.db-journal]

   402     expr {$journalsize>30000}

   403   } {1}

   404   do_test jrnlmode-5.19 {

   405     execsql COMMIT

   406     set sz [file size test.db-journal]

   407     expr {$sz>=$journalsize}

   408   } {1}

   409 }

   410 

   411 finish_test