# 2007 March 24

#

# 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 exclusive access mode (i.e. the thing activated by 

# "PRAGMA locking_mode = EXCLUSIVE").

#

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

set testdir [file dirname $argv0]

source $testdir/tester.tcl

ifcapable {!pager_pragmas} {

  finish_test

  return

}

file delete -force test2.db-journal

file delete -force test2.db

file delete -force test3.db-journal

file delete -force test3.db

file delete -force test4.db-journal

file delete -force test4.db

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

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

#

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

  set temp_mode normal

} else {

  set temp_mode exclusive

}

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

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

#

do_test exclusive-1.0 {

  execsql {

    pragma locking_mode;

    pragma main.locking_mode;

    pragma temp.locking_mode;

  } 

} [list normal normal $temp_mode]

do_test exclusive-1.1 {

  execsql {

    pragma locking_mode = exclusive;

  } 

} {exclusive}

do_test exclusive-1.2 {

  execsql {

    pragma locking_mode;

    pragma main.locking_mode;

    pragma temp.locking_mode;

  } 

} [list exclusive exclusive $temp_mode]

do_test exclusive-1.3 {

  execsql {

    pragma locking_mode = normal;

  } 

} {normal}

do_test exclusive-1.4 {

  execsql {

    pragma locking_mode;

    pragma main.locking_mode;

    pragma temp.locking_mode;

  } 

} [list normal normal $temp_mode]

do_test exclusive-1.5 {

  execsql {

    pragma locking_mode = invalid;

  } 

} {normal}

do_test exclusive-1.6 {

  execsql {

    pragma locking_mode;

    pragma main.locking_mode;

    pragma temp.locking_mode;

  } 

} [list normal normal $temp_mode]

ifcapable attach {

  do_test exclusive-1.7 {

    execsql {

      pragma locking_mode = exclusive;

      ATTACH 'test2.db' as aux;

    }

    execsql {

      pragma main.locking_mode;

      pragma aux.locking_mode;

    }

  } {exclusive exclusive}

  do_test exclusive-1.8 {

    execsql {

      pragma main.locking_mode = normal;

    }

    execsql {

      pragma main.locking_mode;

      pragma temp.locking_mode;

      pragma aux.locking_mode;

    }

  } [list normal $temp_mode exclusive]

  do_test exclusive-1.9 {

    execsql {

      pragma locking_mode;

    }

  } {exclusive}

  do_test exclusive-1.10 {

    execsql {

      ATTACH 'test3.db' as aux2;

    }

    execsql {

      pragma main.locking_mode;

      pragma aux.locking_mode;

      pragma aux2.locking_mode;

    }

  } {normal exclusive exclusive}

  do_test exclusive-1.11 {

    execsql {

      pragma aux.locking_mode = normal;

    }

    execsql {

      pragma main.locking_mode;

      pragma aux.locking_mode;

      pragma aux2.locking_mode;

    }

  } {normal normal exclusive}

  do_test exclusive-1.12 {

    execsql {

      pragma locking_mode = normal;

    }

    execsql {

      pragma main.locking_mode;

      pragma temp.locking_mode;

      pragma aux.locking_mode;

      pragma aux2.locking_mode;

    }

  } [list normal $temp_mode normal normal]

  do_test exclusive-1.13 {

    execsql {

      ATTACH 'test4.db' as aux3;

    }

    execsql {

      pragma main.locking_mode;

      pragma temp.locking_mode;

      pragma aux.locking_mode;

      pragma aux2.locking_mode;

      pragma aux3.locking_mode;

    }

  } [list normal $temp_mode normal normal normal]

  do_test exclusive-1.99 {

    execsql {

      DETACH aux;

      DETACH aux2;

      DETACH aux3;

    }

  } {}

}

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

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

# locking_mode do not relinquish locks.

#

do_test exclusive-2.0 {

  execsql {

    CREATE TABLE abc(a, b, c);

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

    PRAGMA locking_mode = exclusive;

  }

} {exclusive}

do_test exclusive-2.1 {

  sqlite3 db2 test.db

  execsql {

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

    SELECT * FROM abc;

  } db2

} {1 2 3 4 5 6}

do_test exclusive-2.2 {

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

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

  # locked out as a writer.

  execsql {

    SELECT * FROM abc;

  } db

} {1 2 3 4 5 6}

do_test exclusive-2.4 {

  execsql {

    SELECT * FROM abc;

  } db2

} {1 2 3 4 5 6}

do_test exclusive-2.5 {

  catchsql {

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

  } db2

} {1 {database is locked}}

sqlite3_soft_heap_limit 0

do_test exclusive-2.6 {

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

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

  execsql {

    BEGIN;

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

  } db2

  catchsql {

    COMMIT

  } db2

} {1 {database is locked}}

do_test exclusive-2.7 {

  catchsql {

    COMMIT

  } db2

} {1 {database is locked}}

do_test exclusive-2.8 {

  execsql {

    ROLLBACK;

  } db2

} {}

sqlite3_soft_heap_limit $soft_limit

do_test exclusive-2.9 {

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

  execsql {

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

  } db

  catchsql {

    SELECT * FROM abc;

  } db2

} {1 {database is locked}}

do_test exclusive-2.10 {

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

  execsql {

    PRAGMA locking_mode = normal;

  } db

  catchsql {

    SELECT * FROM abc;

  } db2

} {1 {database is locked}}

do_test exclusive-2.11 {

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

  execsql {

    SELECT * FROM abc;

  } db

  execsql {

    SELECT * FROM abc;

  } db2

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

db2 close

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

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

# truncates instead of deletes the journal file when committing 

# a transaction.

#

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

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

# from being inspected externally.

#

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

  proc filestate {fname} {

    set exists 0

    set content 0

    if {[file exists $fname]} {

      set exists 1

      set hdr [hexio_read $fname 0 28]

      set content \

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

    }

    list $exists $content

  }

  do_test exclusive-3.0 {

    filestate test.db-journal

  } {0 0}

  do_test exclusive-3.1 {

    execsql {

      PRAGMA locking_mode = exclusive;

      BEGIN;

      DELETE FROM abc;

    }

    filestate test.db-journal

  } {1 1}

  do_test exclusive-3.2 {

    execsql {

      COMMIT;

    }

    filestate test.db-journal

  } {1 0}

  do_test exclusive-3.3 {

    execsql {

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

      SELECT * FROM abc;

    }

  } {A B C}

  do_test exclusive-3.4 {

    execsql {

      BEGIN;

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

      ROLLBACK;

      SELECT * FROM abc;

    }

  } {A B C}

  do_test exclusive-3.5 {

    filestate test.db-journal

  } {1 0}

  do_test exclusive-3.6 {

    execsql {

      PRAGMA locking_mode = normal;

      SELECT * FROM abc;

    }

    filestate test.db-journal

  } {0 0}

}

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

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

# in exclusive-access mode.

#

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

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

#

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

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

# The two signatures should be the same.

#

proc signature {} {

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

}

do_test exclusive-4.0 {

  execsql { PRAGMA locking_mode = exclusive; }

  execsql { PRAGMA default_cache_size = 10; }

  execsql {

    BEGIN;

    CREATE TABLE t3(x TEXT);

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

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

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

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

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

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

    COMMIT;

  }

  execsql {SELECT count(*) FROM t3;}

} {32}

set ::X [signature]

do_test exclusive-4.1 {

  execsql {

    BEGIN;

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

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

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

    SELECT count(*) FROM t3;

    ROLLBACK;

  }

  signature

} $::X

do_test exclusive-4.2 {

  execsql {

    BEGIN;

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

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

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

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

    ROLLBACK;

  }

  signature

} $::X

do_test exclusive-4.3 {

  execsql {

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

  }

} {}

do_test exclusive-4.4 {

  catch {set ::X [signature]}

} {0}

do_test exclusive-4.5 {

  execsql {

    PRAGMA locking_mode = NORMAL;

    DROP TABLE t3;

    DROP TABLE abc;

  }

} {normal}

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

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

# instead of deleted when in exclusive access mode.

#

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

# longer active.

#

db close

sqlite db test.db

do_test exclusive-5.0 {

  execsql {

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

    BEGIN;

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

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

  }

} {}

do_test exclusive-5.1 {

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

  set sqlite_open_file_count

} {3}

do_test exclusive-5.2 {

  execsql {

    COMMIT;

  }

  # One file open: the db.

  set sqlite_open_file_count

} {1}

do_test exclusive-5.3 {

  execsql {

    PRAGMA locking_mode = exclusive;

    BEGIN;

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

  }

  # Two files open: the db and journal.

  set sqlite_open_file_count

} {2}

do_test exclusive-5.4 {

  execsql {

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

  }

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

  set sqlite_open_file_count

} {3}

do_test exclusive-5.5 {

  execsql {

    COMMIT;

  }

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

  set sqlite_open_file_count

} {3}

do_test exclusive-5.6 {

  execsql {

    PRAGMA locking_mode = normal;

    SELECT * FROM abc;

  }

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

do_test exclusive-5.7 {

  # Just the db open.

  set sqlite_open_file_count

} {1}

finish_test