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.16 2009/06/05 17:09:12 drh Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19ifcapable {!pager_pragmas} {
20  finish_test
21  return
22}
23
24if {[info exists TEMP_STORE] && $TEMP_STORE>=2} {
25  set temp_persist memory
26  set temp_delete memory
27  set temp_truncate memory
28  set temp_off off
29} else {
30  set temp_persist persist
31  set temp_delete delete
32  set temp_truncate truncate
33  set temp_off off
34}
35
36proc temp_journal_mode {newmode} {
37  if {[info exists ::TEMP_STORE] && $::TEMP_STORE>=2} {
38    if {$newmode ne "off" && $newmode ne "memory"} {
39      execsql {PRAGMA temp.journal_mode}
40      set newmode [db one {PRAGMA temp.journal_mode}]
41    }
42  }
43  set newmode
44}
45
46#----------------------------------------------------------------------
47# Test cases jrnlmode-1.X test the PRAGMA logic.
48#
49do_test jrnlmode-1.0 {
50  execsql {
51    PRAGMA journal_mode;
52    PRAGMA main.journal_mode;
53    PRAGMA temp.journal_mode;
54  } 
55} [list delete delete [temp_journal_mode delete]]
56do_test jrnlmode-1.1 {
57  execsql {
58    PRAGMA journal_mode = persist;
59  } 
60} {persist}
61do_test jrnlmode-1.2 {
62  execsql {
63    PRAGMA journal_mode;
64    PRAGMA main.journal_mode;
65    PRAGMA temp.journal_mode;
66  } 
67} [list persist persist [temp_journal_mode persist]]
68do_test jrnlmode-1.4 {
69  execsql {
70    PRAGMA journal_mode = off;
71  } 
72} {off}
73do_test jrnlmode-1.5 {
74  execsql {
75    PRAGMA journal_mode;
76    PRAGMA main.journal_mode;
77    PRAGMA temp.journal_mode;
78  } 
79} [list off off [temp_journal_mode off]]
80do_test jrnlmode-1.6 {
81  execsql {
82    PRAGMA journal_mode = delete;
83  } 
84} {delete}
85do_test jrnlmode-1.7 {
86  execsql {
87    PRAGMA journal_mode;
88    PRAGMA main.journal_mode;
89    PRAGMA Temp.journal_mode;
90  } 
91} [list delete delete [temp_journal_mode delete]]
92do_test jrnlmode-1.7.1 {
93  execsql {
94    PRAGMA journal_mode = truncate;
95  } 
96} {truncate}
97do_test jrnlmode-1.7.2 {
98  execsql {
99    PRAGMA journal_mode;
100    PRAGMA main.journal_mode;
101    PRAGMA temp.journal_mode;
102  } 
103} [list truncate truncate [temp_journal_mode truncate]]
104do_test jrnlmode-1.8 {
105  execsql {
106    PRAGMA journal_mode = off;
107    PRAGMA journal_mode = invalid;
108  } 
109} {off off}
110ifcapable attach {
111  do_test jrnlmode-1.9 {
112    execsql {
113      PRAGMA journal_mode = PERSIST;
114      ATTACH ':memory:' as aux1;
115    }
116    execsql {
117      PRAGMA main.journal_mode;
118      PRAGMA aux1.journal_mode;
119    }
120  } {persist memory}
121  do_test jrnlmode-1.10 {
122    execsql {
123      PRAGMA main.journal_mode = OFF;
124    }
125    execsql {
126      PRAGMA main.journal_mode;
127      PRAGMA temp.journal_mode;
128      PRAGMA aux1.journal_mode;
129    }
130  } [list off [temp_journal_mode persist] memory]
131  do_test jrnlmode-1.11 {
132    execsql {
133      PRAGMA journal_mode;
134    }
135  } {off}
136  do_test jrnlmode-1.12 {
137    execsql {
138      ATTACH ':memory:' as aux2;
139    }
140    execsql {
141      PRAGMA main.journal_mode;
142      PRAGMA aux1.journal_mode;
143      PRAGMA aux2.journal_mode;
144    }
145  } {off memory memory}
146  do_test jrnlmode-1.13 {
147    # The journal-mode used by in-memory databases cannot be changed.
148    execsql {
149      PRAGMA aux1.journal_mode = DELETE;
150    }
151    execsql {
152      PRAGMA main.journal_mode;
153      PRAGMA aux1.journal_mode;
154      PRAGMA aux2.journal_mode;
155    }
156  } {off memory memory}
157  do_test jrnlmode-1.14 {
158    execsql {
159      PRAGMA journal_mode = delete;
160    }
161    execsql {
162      PRAGMA main.journal_mode;
163      PRAGMA temp.journal_mode;
164      PRAGMA aux1.journal_mode;
165      PRAGMA aux2.journal_mode;
166    }
167  } [list delete [temp_journal_mode delete] memory memory]
168  do_test jrnlmode-1.15 {
169    execsql {
170      ATTACH ':memory:' as aux3;
171    }
172    execsql {
173      PRAGMA main.journal_mode;
174      PRAGMA temp.journal_mode;
175      PRAGMA aux1.journal_mode;
176      PRAGMA aux2.journal_mode;
177      PRAGMA aux3.journal_mode;
178    }
179  } [list delete [temp_journal_mode delete] memory memory memory]
180  do_test jrnlmode-1.16 {
181    execsql {
182      PRAGMA journal_mode = TRUNCATE;
183    }
184    execsql {
185      PRAGMA main.journal_mode;
186      PRAGMA temp.journal_mode;
187      PRAGMA aux1.journal_mode;
188      PRAGMA aux2.journal_mode;
189      PRAGMA aux3.journal_mode;
190    }
191  } [list truncate [temp_journal_mode truncate] memory memory memory]
192
193  do_test jrnlmode-1.99 {
194    execsql {
195      DETACH aux1;
196      DETACH aux2;
197      DETACH aux3;
198    }
199  } {}
200}
201
202ifcapable attach {
203  file delete -force test2.db
204  do_test jrnlmode-2.1 {
205    execsql {
206      ATTACH 'test2.db' AS aux;
207      PRAGMA main.journal_mode = persist;
208      PRAGMA aux.journal_mode = persist;
209      CREATE TABLE abc(a, b, c);
210      CREATE TABLE aux.def(d, e, f);
211    }
212    execsql {
213      BEGIN;
214      INSERT INTO abc VALUES(1, 2, 3);
215      INSERT INTO def VALUES(4, 5, 6);
216      COMMIT;
217    }
218    list [file exists test.db-journal] [file exists test2.db-journal]
219  } {1 1}
220
221  do_test jrnlmode-2.2 {
222    file size test.db-journal
223  } {0}
224
225  do_test jrnlmode-2.3 {
226    execsql {
227      SELECT * FROM abc;
228    }
229  } {1 2 3}
230
231  do_test jrnlmode-2.4 {
232    file size test.db-journal
233  } {0}
234
235  do_test jrnlmode-2.5 {
236    execsql {
237      SELECT * FROM def;
238    }
239  } {4 5 6}
240
241#----------------------------------------------------------------------
242# Test caes jrnlmode-3.X verify that ticket #3127 has been fixed.
243#
244  db close
245  file delete -force test2.db
246  file delete -force test.db
247  sqlite3 db test.db
248
249  do_test jrnlmode-3.1 {
250    execsql { 
251      CREATE TABLE x(n INTEGER); 
252      ATTACH 'test2.db' AS a; 
253      create table a.x ( n integer ); 
254      insert into a.x values(1); 
255      insert into a.x values (2); 
256      insert into a.x values (3); 
257      insert into a.x values (4); 
258    }
259  } {}
260  
261  do_test jrnlmode-3.2 {
262    execsql { PRAGMA journal_mode=off; }
263    execsql { 
264      BEGIN IMMEDIATE;
265      INSERT OR IGNORE INTO main.x SELECT * FROM a.x;
266      COMMIT;
267    }
268  } {}
269}
270
271ifcapable autovacuum&&pragma {
272  db close
273  file delete -force test.db
274  sqlite3 db test.db
275  do_test jrnlmode-4.1 {
276    execsql {
277      PRAGMA cache_size = 1;
278      PRAGMA auto_vacuum = 1;
279      CREATE TABLE abc(a, b, c);
280    }
281    execsql { PRAGMA page_count }
282  } {3}
283
284  do_test jrnlmode-4.2 {
285    execsql { PRAGMA journal_mode = off }
286  } {off}
287
288  do_test jrnlmode-4.3 {
289    execsql { INSERT INTO abc VALUES(1, 2, randomblob(2000)) }
290  } {}
291
292  # This will attempt to truncate the database file. Check that this
293  # is not a problem when journal_mode=off.
294  do_test jrnlmode-4.4 {
295    execsql { DELETE FROM abc }
296  } {}
297
298  integrity_check jrnlmode-4.5
299}
300
301#------------------------------------------------------------------------
302# The following test caes, jrnlmode-5.*, test the journal_size_limit
303# pragma.
304ifcapable pragma {
305  db close
306  file delete -force test.db test2.db test3.db
307  sqlite3 db test.db
308
309  do_test jrnlmode-5.1 {
310    execsql {pragma page_size=1024}
311    execsql {pragma journal_mode=persist}
312  } {persist}
313
314  do_test jrnlmode-5.2 {
315    execsql { PRAGMA journal_size_limit }
316  } {-1}
317  do_test jrnlmode-5.3 {
318    execsql { 
319      ATTACH 'test2.db' AS aux;
320      PRAGMA aux.journal_mode=persist;
321      PRAGMA aux.journal_size_limit;
322    }
323  } {persist -1}
324  do_test jrnlmode-5.4.1 {
325    execsql { PRAGMA aux.journal_size_limit = 999999999999 }
326  } {999999999999}
327  do_test jrnlmode-5.4.2 {
328    execsql { PRAGMA aux.journal_size_limit = 10240 }
329  } {10240}
330  do_test jrnlmode-5.5 {
331    execsql { PRAGMA main.journal_size_limit = 20480 }
332  } {20480}
333  do_test jrnlmode-5.6 {
334    execsql { PRAGMA journal_size_limit }
335  } {20480}
336  do_test jrnlmode-5.7 {
337    execsql { PRAGMA aux.journal_size_limit }
338  } {10240}
339
340  do_test jrnlmode-5.8 {
341    execsql {
342      ATTACH 'test3.db' AS aux2;
343      PRAGMA aux2.journal_mode=persist;
344    }
345  } {persist}
346
347  do_test jrnlmode-5.9 {
348    execsql {
349      CREATE TABLE main.t1(a, b, c);
350      CREATE TABLE aux.t2(a, b, c);
351      CREATE TABLE aux2.t3(a, b, c);
352    }
353  } {}
354  do_test jrnlmode-5.10 {
355    list \
356      [file exists test.db-journal]  \
357      [file exists test2.db-journal] \
358      [file exists test3.db-journal]
359  } {1 1 1}
360  do_test jrnlmode-5.11 {
361    execsql {
362      BEGIN;
363      INSERT INTO t3 VALUES(randomblob(1000),randomblob(1000),randomblob(1000));
364      INSERT INTO t3 
365          SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
366      INSERT INTO t3 
367          SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
368      INSERT INTO t3 
369          SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
370      INSERT INTO t3 
371          SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
372      INSERT INTO t3 
373          SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
374      INSERT INTO t2 SELECT * FROM t3;
375      INSERT INTO t1 SELECT * FROM t2;
376      COMMIT;
377    }
378    list \
379      [file exists test.db-journal]  \
380      [file exists test2.db-journal] \
381      [file exists test3.db-journal] \
382      [file size test.db-journal]    \
383      [file size test2.db-journal]   \
384      [file size test3.db-journal]
385  } {1 1 1 0 0 0}
386
387  do_test jrnlmode-5.12 {
388    execsql {
389      BEGIN;
390      UPDATE t1 SET a = randomblob(1000);
391    }
392    expr {[file size test.db-journal]>30000}
393  } {1}
394  do_test jrnlmode-5.13 {
395    execsql COMMIT
396    file size test.db-journal
397  } {20480}
398
399  do_test jrnlmode-5.14 {
400    execsql {
401      BEGIN;
402      UPDATE t2 SET a = randomblob(1000);
403    }
404    expr {[file size test2.db-journal]>30000}
405  } {1}
406  do_test jrnlmode-5.15 {
407    execsql COMMIT
408    file size test2.db-journal
409  } {10240}
410
411  do_test jrnlmode-5.16 {
412    execsql {
413      BEGIN;
414      UPDATE t3 SET a = randomblob(1000);
415    }
416    set journalsize [file size test3.db-journal]
417    expr {$journalsize>30000}
418  } {1}
419  do_test jrnlmode-5.17 {
420    execsql COMMIT
421    set sz [file size test3.db-journal]
422    expr {$sz>=$journalsize}
423  } {1}
424
425  do_test jrnlmode-5.18 {
426    execsql {
427      PRAGMA journal_size_limit = -4;
428      BEGIN;
429      UPDATE t1 SET a = randomblob(1000);
430    }
431    set journalsize [file size test.db-journal]
432    expr {$journalsize>30000}
433  } {1}
434  do_test jrnlmode-5.19 {
435    execsql COMMIT
436    set sz [file size test.db-journal]
437    expr {$sz>=$journalsize}
438  } {1}
439
440  # Test a size-limit of 0.
441  #
442  do_test jrnlmode-5.20 {
443    execsql {
444      PRAGMA journal_size_limit = 0;
445      BEGIN;
446      UPDATE t1 SET a = randomblob(1000);
447    }
448  } {0}
449  do_test jrnlmode-5.21 {
450    expr {[file size test.db-journal] > 1024}
451  } {1}
452  do_test jrnlmode-5.22 {
453    execsql COMMIT
454    list [file exists test.db-journal] [file size test.db-journal]
455  } {1 0}
456}
457
458ifcapable pragma {
459  # These tests are not run as part of the "journaltest" permutation,
460  # as the test_journal.c layer is incompatible with in-memory journaling.
461  if {[permutation] ne "journaltest"} {
462
463    do_test jrnlmode-6.1 {
464      execsql {
465        PRAGMA journal_mode = truncate;
466        CREATE TABLE t4(a, b);
467        BEGIN;
468          INSERT INTO t4 VALUES(1, 2);
469          PRAGMA journal_mode = memory;
470      }
471    } {truncate truncate}
472    do_test jrnlmode-6.2 {
473      file exists test.db-journal
474    } {1}
475    do_test jrnlmode-6.3 {
476      execsql {
477        COMMIT;
478        SELECT * FROM t4;
479      }
480    } {1 2}
481    do_test jrnlmode-6.4 {
482      file exists test.db-journal
483    } {1}
484    do_test jrnlmode-6.5 {
485      execsql {
486        PRAGMA journal_mode = MEMORY;
487        BEGIN;
488          INSERT INTO t4 VALUES(3, 4);
489      }
490      file exists test.db-journal
491    } {0}
492    do_test jrnlmode-6.7 {
493      execsql {
494        COMMIT;
495        SELECT * FROM t4;
496      }
497    } {1 2 3 4}
498    do_test jrnlmode-6.8 {
499      file exists test.db-journal
500    } {0}
501    do_test jrnlmode-6.9 {
502      execsql {
503        PRAGMA journal_mode = DELETE;
504        BEGIN IMMEDIATE; INSERT INTO t4 VALUES(1,2); COMMIT;
505      }
506      file exists test.db-journal
507    } {0}
508  }
509}
510
511ifcapable pragma {
512  catch { db close }
513  do_test jrnlmode-7.1 {
514    foreach f [glob -nocomplain test.db*] { file delete -force $f }
515    sqlite3 db test.db
516    execsql {
517      PRAGMA journal_mode = memory;
518      PRAGMA auto_vacuum = 0;
519      PRAGMA page_size = 1024;
520      PRAGMA user_version = 5;
521      PRAGMA user_version;
522    }
523  } {memory 5}
524  do_test jrnlmode-7.2 { file size test.db } {1024}
525}
526
527do_execsql_test jrnlmode-8.1  { PRAGMA locking_mode=EXCLUSIVE } {exclusive}
528do_execsql_test jrnlmode-8.2  { CREATE TABLE t1(x) }            {}
529do_execsql_test jrnlmode-8.3  { INSERT INTO t1 VALUES(123) }    {}
530do_execsql_test jrnlmode-8.4  { SELECT * FROM t1 }              {123}
531do_execsql_test jrnlmode-8.5  { PRAGMA journal_mode=PERSIST }   {persist}
532do_execsql_test jrnlmode-8.6  { PRAGMA journal_mode=DELETE }    {delete}
533do_execsql_test jrnlmode-8.7  { PRAGMA journal_mode=TRUNCATE }  {truncate}
534do_execsql_test jrnlmode-8.8  { PRAGMA journal_mode=DELETE }    {delete}
535do_execsql_test jrnlmode-8.9  { CREATE TABLE t2(y) }            {}
536do_execsql_test jrnlmode-8.10 { INSERT INTO t2 VALUES(456) }    {}
537do_execsql_test jrnlmode-8.11 { SELECT * FROM t1, t2 }          {123 456}
538do_execsql_test jrnlmode-8.12 { PRAGMA locking_mode=NORMAL }    {normal}
539do_execsql_test jrnlmode-8.13 { PRAGMA journal_mode=PERSIST }   {persist}
540do_execsql_test jrnlmode-8.14 { PRAGMA journal_mode=TRUNCATE }  {truncate}
541do_execsql_test jrnlmode-8.15 { PRAGMA journal_mode=PERSIST }   {persist}
542do_execsql_test jrnlmode-8.16 { PRAGMA journal_mode=DELETE }    {delete}
543do_execsql_test jrnlmode-8.17 { PRAGMA journal_mode=TRUNCATE }  {truncate}
544do_execsql_test jrnlmode-8.18 { PRAGMA locking_mode=EXCLUSIVE } {exclusive}
545do_execsql_test jrnlmode-8.19 { CREATE TABLE t3(z) }            {}
546do_execsql_test jrnlmode-8.20 { BEGIN IMMEDIATE }               {}
547do_execsql_test jrnlmode-8.21 { PRAGMA journal_mode=DELETE }    {delete}
548do_execsql_test jrnlmode-8.22 { COMMIT }                        {}
549do_execsql_test jrnlmode-8.23 { PRAGMA journal_mode=DELETE }    {delete}
550do_execsql_test jrnlmode-8.24 { PRAGMA journal_mode=TRUNCATE }  {truncate}
551do_execsql_test jrnlmode-8.25 { PRAGMA locking_mode=NORMAL }    {normal}
552do_execsql_test jrnlmode-8.26 { CREATE TABLE t4(w) }            {}
553do_execsql_test jrnlmode-8.27 { BEGIN IMMEDIATE }               {}
554do_execsql_test jrnlmode-8.28 { PRAGMA journal_mode=DELETE }    {delete}
555do_execsql_test jrnlmode-8.29 { COMMIT }                        {}
556do_execsql_test jrnlmode-8.30 { PRAGMA journal_mode=DELETE }    {delete}
557
558finish_test
559