# 2008 April 17

#

# The author disclaims copyright to this source code.  In place of

# a legal notice, here is a blessing:

#

#    May you do good and not evil.

#    May you find forgiveness for yourself and forgive others.

#    May you share freely, never taking more than you give.

#

#***********************************************************************

# This file implements regression tests for SQLite library. The focus

# of these tests is the journal mode pragma.

#

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

set testdir [file dirname $argv0]

source $testdir/tester.tcl

ifcapable {!pager_pragmas} {

  finish_test

  return

}

#----------------------------------------------------------------------

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

#

do_test jrnlmode-1.0 {

  execsql {

    PRAGMA journal_mode;

    PRAGMA main.journal_mode;

    PRAGMA temp.journal_mode;

  } 

} [list delete delete delete]

do_test jrnlmode-1.1 {

  execsql {

    PRAGMA journal_mode = persist;

  } 

} {persist}

do_test jrnlmode-1.2 {

  execsql {

    PRAGMA journal_mode;

    PRAGMA main.journal_mode;

    PRAGMA temp.journal_mode;

  } 

} [list persist persist persist]

do_test jrnlmode-1.4 {

  execsql {

    PRAGMA journal_mode = off;

  } 

} {off}

do_test jrnlmode-1.5 {

  execsql {

    PRAGMA journal_mode;

    PRAGMA main.journal_mode;

    PRAGMA temp.journal_mode;

  } 

} {off off off}

do_test jrnlmode-1.6 {

  execsql {

    PRAGMA journal_mode = delete;

  } 

} {delete}

do_test jrnlmode-1.7 {

  execsql {

    PRAGMA journal_mode;

    PRAGMA main.journal_mode;

    PRAGMA temp.journal_mode;

  } 

} {delete delete delete}

do_test jrnlmode-1.7.1 {

  execsql {

    PRAGMA journal_mode = truncate;

  } 

} {truncate}

do_test jrnlmode-1.7.2 {

  execsql {

    PRAGMA journal_mode;

    PRAGMA main.journal_mode;

    PRAGMA temp.journal_mode;

  } 

} {truncate truncate truncate}

do_test jrnlmode-1.8 {

  execsql {

    PRAGMA journal_mode = off;

    PRAGMA journal_mode = invalid;

  } 

} {off off}

ifcapable attach {

  do_test jrnlmode-1.9 {

    execsql {

      PRAGMA journal_mode = PERSIST;

      ATTACH ':memory:' as aux1;

    }

    execsql {

      PRAGMA main.journal_mode;

      PRAGMA aux1.journal_mode;

    }

  } {persist persist}

  do_test jrnlmode-1.10 {

    execsql {

      PRAGMA main.journal_mode = OFF;

    }

    execsql {

      PRAGMA main.journal_mode;

      PRAGMA temp.journal_mode;

      PRAGMA aux1.journal_mode;

    }

  } {off persist persist}

  do_test jrnlmode-1.11 {

    execsql {

      PRAGMA journal_mode;

    }

  } {persist}

  do_test jrnlmode-1.12 {

    execsql {

      ATTACH ':memory:' as aux2;

    }

    execsql {

      PRAGMA main.journal_mode;

      PRAGMA aux1.journal_mode;

      PRAGMA aux2.journal_mode;

    }

  } {off persist persist}

  do_test jrnlmode-1.11 {

    execsql {

      PRAGMA aux1.journal_mode = DELETE;

    }

    execsql {

      PRAGMA main.journal_mode;

      PRAGMA aux1.journal_mode;

      PRAGMA aux2.journal_mode;

    }

  } {off delete persist}

  do_test jrnlmode-1.12 {

    execsql {

      PRAGMA journal_mode = delete;

    }

    execsql {

      PRAGMA main.journal_mode;

      PRAGMA temp.journal_mode;

      PRAGMA aux1.journal_mode;

      PRAGMA aux2.journal_mode;

    }

  } {delete delete delete delete}

  do_test jrnlmode-1.13 {

    execsql {

      ATTACH ':memory:' as aux3;

    }

    execsql {

      PRAGMA main.journal_mode;

      PRAGMA temp.journal_mode;

      PRAGMA aux1.journal_mode;

      PRAGMA aux2.journal_mode;

      PRAGMA aux3.journal_mode;

    }

  } {delete delete delete delete delete}

  do_test jrnlmode-1.14 {

    execsql {

      PRAGMA journal_mode = TRUNCATE;

    }

    execsql {

      PRAGMA main.journal_mode;

      PRAGMA temp.journal_mode;

      PRAGMA aux1.journal_mode;

      PRAGMA aux2.journal_mode;

      PRAGMA aux3.journal_mode;

    }

  } {truncate truncate truncate truncate truncate}

  do_test jrnlmode-1.99 {

    execsql {

      DETACH aux1;

      DETACH aux2;

      DETACH aux3;

    }

  } {}

}

ifcapable attach {

  file delete -force test2.db

  do_test jrnlmode-2.1 {

    execsql {

      ATTACH 'test2.db' AS aux;

      PRAGMA main.journal_mode = persist;

      PRAGMA aux.journal_mode = persist;

      CREATE TABLE abc(a, b, c);

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

    }

    execsql {

      BEGIN;

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

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

      COMMIT;

    }

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

  } {1 1}

  do_test jrnlmode-2.2 {

    file size test.db-journal

  } {0}

  do_test jrnlmode-2.3 {

    execsql {

      SELECT * FROM abc;

    }

  } {1 2 3}

  do_test jrnlmode-2.4 {

    file size test.db-journal

  } {0}

  do_test jrnlmode-2.5 {

    execsql {

      SELECT * FROM def;

    }

  } {4 5 6}

#----------------------------------------------------------------------

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

#

  db close

  file delete -force test2.db

  file delete -force test.db

  sqlite3 db test.db

  do_test jrnlmode-3.1 {

    execsql { 

      CREATE TABLE x(n INTEGER); 

      ATTACH 'test2.db' AS a; 

      create table a.x ( n integer ); 

      insert into a.x values(1); 

      insert into a.x values (2); 

      insert into a.x values (3); 

      insert into a.x values (4); 

    }

  } {}

  do_test jrnlmode-3.2 {

    execsql { PRAGMA journal_mode=off; }

    execsql { 

      BEGIN IMMEDIATE;

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

      COMMIT;

    }

  } {}

}

ifcapable autovacuum&&pragma {

  db close

  file delete -force test.db

  sqlite3 db test.db

  do_test jrnlmode-4.1 {

    execsql {

      PRAGMA cache_size = 1;

      PRAGMA auto_vacuum = 1;

      CREATE TABLE abc(a, b, c);

    }

    execsql { PRAGMA page_count }

  } {3}

  do_test jrnlmode-4.2 {

    execsql { PRAGMA journal_mode = off }

  } {off}

  do_test jrnlmode-4.3 {

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

  } {}

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

  # is not a problem when journal_mode=off.

  do_test jrnlmode-4.4 {

    execsql { DELETE FROM abc }

  } {}

  integrity_check jrnlmode-4.5

}

#------------------------------------------------------------------------

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

# pragma.

ifcapable pragma {

  db close

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

  sqlite3 db test.db

  do_test jrnlmode-5.1 {

    execsql {pragma page_size=1024}

    execsql {pragma journal_mode=persist}

  } {persist}

  do_test jrnlmode-5.2 {

    execsql { PRAGMA journal_size_limit }

  } {-1}

  do_test jrnlmode-5.3 {

    execsql { 

      ATTACH 'test2.db' AS aux;

      PRAGMA aux.journal_size_limit;

    }

  } {-1}

  do_test jrnlmode-5.4 {

    execsql { PRAGMA aux.journal_size_limit = 10240 }

  } {10240}

  do_test jrnlmode-5.5 {

    execsql { PRAGMA main.journal_size_limit = 20480 }

  } {20480}

  do_test jrnlmode-5.6 {

    execsql { PRAGMA journal_size_limit }

  } {20480}

  do_test jrnlmode-5.7 {

    execsql { PRAGMA aux.journal_size_limit }

  } {10240}

  do_test jrnlmode-5.8 {

    execsql { ATTACH 'test3.db' AS aux2 }

  } {}

  do_test jrnlmode-5.9 {

    execsql {

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

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

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

    }

  } {}

  do_test jrnlmode-5.10 {

    list \

      [file exists test.db-journal]  \

      [file exists test2.db-journal] \

      [file exists test3.db-journal]

  } {1 1 1}

  do_test jrnlmode-5.11 {

    execsql {

      BEGIN;

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

      INSERT INTO t3 

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

      INSERT INTO t3 

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

      INSERT INTO t3 

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

      INSERT INTO t3 

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

      INSERT INTO t3 

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

      INSERT INTO t2 SELECT * FROM t3;

      INSERT INTO t1 SELECT * FROM t2;

      COMMIT;

    }

    list \

      [file exists test.db-journal]  \

      [file exists test2.db-journal] \

      [file exists test3.db-journal] \

      [file size test.db-journal]    \

      [file size test2.db-journal]   \

      [file size test3.db-journal]

  } {1 1 1 0 0 0}

  do_test jrnlmode-5.12 {

    execsql {

      BEGIN;

      UPDATE t1 SET a = randomblob(1000);

    }

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

  } {1}

  do_test jrnlmode-5.13 {

    execsql COMMIT

    file size test.db-journal

  } {20480}

  do_test jrnlmode-5.14 {

    execsql {

      BEGIN;

      UPDATE t2 SET a = randomblob(1000);

    }

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

  } {1}

  do_test jrnlmode-5.15 {

    execsql COMMIT

    file size test2.db-journal

  } {10240}

  do_test jrnlmode-5.16 {

    execsql {

      BEGIN;

      UPDATE t3 SET a = randomblob(1000);

    }

    set journalsize [file size test3.db-journal]

    expr {$journalsize>30000}

  } {1}

  do_test jrnlmode-5.17 {

    execsql COMMIT

    set sz [file size test3.db-journal]

    expr {$sz>=$journalsize}

  } {1}

  do_test jrnlmode-5.18 {

    execsql {

      PRAGMA journal_size_limit = -4;

      BEGIN;

      UPDATE t1 SET a = randomblob(1000);

    }

    set journalsize [file size test.db-journal]

    expr {$journalsize>30000}

  } {1}

  do_test jrnlmode-5.19 {

    execsql COMMIT

    set sz [file size test.db-journal]

    expr {$sz>=$journalsize}

  } {1}

}

finish_test