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