Index: System.Data.SQLite/SQLiteConnection.cs ================================================================== --- System.Data.SQLite/SQLiteConnection.cs +++ System.Data.SQLite/SQLiteConnection.cs @@ -887,11 +887,11 @@ { cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA cache_size={0}", defValue); cmd.ExecuteNonQuery(); } - defValue = FindKey(opts, "Journal Mode", "Delete"); + defValue = FindKey(opts, "Journal Mode", "Default"); if (String.Compare(defValue, "Default", StringComparison.OrdinalIgnoreCase) != 0) { cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA journal_mode={0}", defValue); cmd.ExecuteNonQuery(); } Index: System.Data.SQLite/SQLiteConvert.cs ================================================================== --- System.Data.SQLite/SQLiteConvert.cs +++ System.Data.SQLite/SQLiteConvert.cs @@ -763,11 +763,15 @@ /// when starting a transaction. If this is happening, you may want to change the default journal mode to Persist. /// public enum SQLiteJournalModeEnum { /// - /// The default mode, this causes SQLite to create and destroy the journal file as-needed. + /// The default mode, this causes SQLite to use the existing journaling mode for the database. + /// + Default = -1, + /// + /// SQLite will create and destroy the journal file as-needed. /// Delete = 0, /// /// When this is set, SQLite will keep the journal file even after a transaction has completed. It's contents will be erased, /// and the journal re-used as often as needed. If it is deleted, it will be recreated the next time it is needed. @@ -775,11 +779,27 @@ Persist = 1, /// /// This option disables the rollback journal entirely. Interrupted transactions or a program crash can cause database /// corruption in this mode! /// - Off = 2 + Off = 2, + /// + /// SQLite will truncate the journal file to zero-length instead of deleting it. + /// + Truncate = 3, + /// + /// SQLite will store the journal in volatile RAM. This saves disk I/O but at the expense of database safety and integrity. + /// If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the + /// database file will very likely go corrupt. + /// + Memory = 4, + /// + /// SQLite uses a write-ahead log instead of a rollback journal to implement transactions. The WAL journaling mode is persistent; + /// after being set it stays in effect across multiple database connections and after closing and reopening the database. A database + /// in WAL journaling mode can only be accessed by SQLite version 3.7.0 or later. + /// + Wal = 5 } /// /// Struct used internally to determine the datatype of a column in a resultset /// Index: Tests/common.eagle ================================================================== --- Tests/common.eagle +++ Tests/common.eagle @@ -27,13 +27,17 @@ } return "" } - proc setupDb {fileName {mode ""} {varName db}} { + proc setupDb {fileName {mode ""} {delete true} {varName db}} { set fileName [file join [getTemporaryPath] $fileName] - catch {file delete $fileName} + + if {$delete} then { + catch {file delete $fileName} + } + upvar 1 $varName db set connection {Data Source=${fileName}} if {[string length $mode] > 0} then { Index: Tests/tkt-448d663d11.eagle ================================================================== --- Tests/tkt-448d663d11.eagle +++ Tests/tkt-448d663d11.eagle @@ -17,162 +17,240 @@ source [file join $path common.eagle] loadAssembly System.Data.SQLite.dll ############################################################################### -runTest {test tkt-448d663d11-1.1 {'Default' journal mode, new db} -body { - setupDb [set fileName tkt-448d663d11-1.1.db] Default +runTest {test tkt-448d663d11-1.1 {missing journal mode, new db} -body { + setupDb [set fileName tkt-448d663d11-1.1.db] sql execute -execute scalar $db "PRAGMA journal_mode;" } -cleanup { cleanupDb $fileName unset -nocomplain db fileName } -constraints \ {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}} ############################################################################### -runTest {test tkt-448d663d11-1.2 {'Default' journal mode, WAL db} -body { +runTest {test tkt-448d663d11-1.2 {missing journal mode, WAL db} -body { set fileName tkt-448d663d11-1.2.db file copy -force [file join $path wal.db] \ [file join [getTemporaryPath] $fileName] - setupDb $fileName Default + setupDb $fileName "" false + sql execute -execute scalar $db "PRAGMA journal_mode;" +} -cleanup { + cleanupDb $fileName + unset -nocomplain db fileName +} -constraints \ +{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {wal}} + +############################################################################### + +runTest {test tkt-448d663d11-1.3 {missing journal mode, non-WAL db} -body { + set fileName tkt-448d663d11-1.3.db + file copy -force [file join $path nonWal.db] \ + [file join [getTemporaryPath] $fileName] + setupDb $fileName "" false + sql execute -execute scalar $db "PRAGMA journal_mode;" +} -cleanup { + cleanupDb $fileName + unset -nocomplain db fileName +} -constraints \ +{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}} + +############################################################################### + +runTest {test tkt-448d663d11-1.4 {'Default' journal mode, new db} -body { + setupDb [set fileName tkt-448d663d11-1.4.db] Default + sql execute -execute scalar $db "PRAGMA journal_mode;" +} -cleanup { + cleanupDb $fileName + unset -nocomplain db fileName +} -constraints \ +{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}} + +############################################################################### + +runTest {test tkt-448d663d11-1.5 {'Default' journal mode, WAL db} -body { + set fileName tkt-448d663d11-1.5.db + file copy -force [file join $path wal.db] \ + [file join [getTemporaryPath] $fileName] + setupDb $fileName Default false sql execute -execute scalar $db "PRAGMA journal_mode;" } -cleanup { cleanupDb $fileName unset -nocomplain db fileName } -constraints \ {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {wal}} ############################################################################### -runTest {test tkt-448d663d11-1.3 {'Default' journal mode, non-WAL db} -body { - set fileName tkt-448d663d11-1.3.db +runTest {test tkt-448d663d11-1.6 {'Default' journal mode, non-WAL db} -body { + set fileName tkt-448d663d11-1.6.db file copy -force [file join $path nonWal.db] \ [file join [getTemporaryPath] $fileName] - setupDb $fileName Default + setupDb $fileName Default false + sql execute -execute scalar $db "PRAGMA journal_mode;" +} -cleanup { + cleanupDb $fileName + unset -nocomplain db fileName +} -constraints \ +{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}} + +############################################################################### + +runTest {test tkt-448d663d11-1.7 {'Delete' journal mode, new db} -body { + setupDb [set fileName tkt-448d663d11-1.7.db] Delete sql execute -execute scalar $db "PRAGMA journal_mode;" } -cleanup { cleanupDb $fileName unset -nocomplain db fileName } -constraints \ {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}} ############################################################################### -runTest {test tkt-448d663d11-1.4 {'Delete' journal mode, new db} -body { - setupDb [set fileName tkt-448d663d11-1.4.db] Delete +runTest {test tkt-448d663d11-1.8 {'Delete' journal mode, WAL db} -body { + set fileName tkt-448d663d11-1.8.db + file copy -force [file join $path wal.db] \ + [file join [getTemporaryPath] $fileName] + setupDb $fileName Delete false sql execute -execute scalar $db "PRAGMA journal_mode;" } -cleanup { cleanupDb $fileName unset -nocomplain db fileName } -constraints \ {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}} ############################################################################### -runTest {test tkt-448d663d11-1.5 {'Delete' journal mode, WAL db} -body { - set fileName tkt-448d663d11-1.5.db - file copy -force [file join $path wal.db] \ +runTest {test tkt-448d663d11-1.9 {'Delete' journal mode, non-WAL db} -body { + set fileName tkt-448d663d11-1.9.db + file copy -force [file join $path nonWal.db] \ [file join [getTemporaryPath] $fileName] - setupDb $fileName Delete + setupDb $fileName Delete false sql execute -execute scalar $db "PRAGMA journal_mode;" } -cleanup { cleanupDb $fileName unset -nocomplain db fileName } -constraints \ {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}} ############################################################################### -runTest {test tkt-448d663d11-1.6 {'Delete' journal mode, non-WAL db} -body { - set fileName tkt-448d663d11-1.6.db - file copy -force [file join $path nonWal.db] \ - [file join [getTemporaryPath] $fileName] - setupDb $fileName Delete - sql execute -execute scalar $db "PRAGMA journal_mode;" -} -cleanup { - cleanupDb $fileName - unset -nocomplain db fileName -} -constraints \ -{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}} - -############################################################################### - -runTest {test tkt-448d663d11-1.7 {'Persist' journal mode, new db} -body { - setupDb [set fileName tkt-448d663d11-1.7.db] Persist +runTest {test tkt-448d663d11-1.10 {'Persist' journal mode, new db} -body { + setupDb [set fileName tkt-448d663d11-1.10.db] Persist sql execute -execute scalar $db "PRAGMA journal_mode;" } -cleanup { cleanupDb $fileName unset -nocomplain db fileName } -constraints \ {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {persist}} ############################################################################### -runTest {test tkt-448d663d11-1.8 {'Off' journal mode, new db} -body { - setupDb [set fileName tkt-448d663d11-1.8.db] Off +runTest {test tkt-448d663d11-1.11 {'Off' journal mode, new db} -body { + setupDb [set fileName tkt-448d663d11-1.11.db] Off sql execute -execute scalar $db "PRAGMA journal_mode;" } -cleanup { cleanupDb $fileName unset -nocomplain db fileName } -constraints \ {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {off}} ############################################################################### -runTest {test tkt-448d663d11-1.9 {'Truncate' journal mode, new db} -body { - setupDb [set fileName tkt-448d663d11-1.9.db] Truncate +runTest {test tkt-448d663d11-1.12 {'Truncate' journal mode, new db} -body { + setupDb [set fileName tkt-448d663d11-1.12.db] Truncate sql execute -execute scalar $db "PRAGMA journal_mode;" } -cleanup { cleanupDb $fileName unset -nocomplain db fileName } -constraints \ {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {truncate}} ############################################################################### -runTest {test tkt-448d663d11-1.10 {'Memory' journal mode, new db} -body { - setupDb [set fileName tkt-448d663d11-1.10.db] Memory +runTest {test tkt-448d663d11-1.13 {'Memory' journal mode, new db} -body { + setupDb [set fileName tkt-448d663d11-1.13.db] Memory sql execute -execute scalar $db "PRAGMA journal_mode;" } -cleanup { cleanupDb $fileName unset -nocomplain db fileName } -constraints \ {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {memory}} ############################################################################### -runTest {test tkt-448d663d11-1.11 {'Wal' journal mode, new db} -body { - setupDb [set fileName tkt-448d663d11-1.11.db] Wal +runTest {test tkt-448d663d11-1.14 {'Wal' journal mode, new db} -body { + setupDb [set fileName tkt-448d663d11-1.14.db] Wal + sql execute -execute scalar $db "PRAGMA journal_mode;" +} -cleanup { + cleanupDb $fileName + unset -nocomplain db fileName +} -constraints \ +{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {wal}} + +############################################################################### + +runTest {test tkt-448d663d11-1.15 {'Wal' journal mode, non-WAL db} -body { + set fileName tkt-448d663d11-1.15.db + file copy -force [file join $path nonWal.db] \ + [file join [getTemporaryPath] $fileName] + setupDb $fileName Wal false sql execute -execute scalar $db "PRAGMA journal_mode;" } -cleanup { cleanupDb $fileName unset -nocomplain db fileName } -constraints \ {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {wal}} ############################################################################### -runTest {test tkt-448d663d11-1.12 {'Wal' journal mode, non-WAL db} -body { - set fileName tkt-448d663d11-1.12.db - file copy -force [file join $path nonWal.db] \ +runTest {test tkt-448d663d11-1.16 {'Wal' journal mode, WAL db} -body { + set fileName tkt-448d663d11-1.16.db + file copy -force [file join $path wal.db] \ [file join [getTemporaryPath] $fileName] - setupDb $fileName Wal + setupDb $fileName Wal false sql execute -execute scalar $db "PRAGMA journal_mode;" } -cleanup { cleanupDb $fileName unset -nocomplain db fileName } -constraints \ {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {wal}} ############################################################################### -runTest {test tkt-448d663d11-1.13 {'Wal' journal mode, WAL db} -body { - set fileName tkt-448d663d11-1.13.db +runTest {test tkt-448d663d11-1.17 {'Bad' journal mode, new db} -body { + setupDb [set fileName tkt-448d663d11-1.17.db] Bad + sql execute -execute scalar $db "PRAGMA journal_mode;" +} -cleanup { + cleanupDb $fileName + unset -nocomplain db fileName +} -constraints \ +{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}} + +############################################################################### + +runTest {test tkt-448d663d11-1.18 {'Bad' journal mode, non-WAL db} -body { + set fileName tkt-448d663d11-1.18.db + file copy -force [file join $path nonWal.db] \ + [file join [getTemporaryPath] $fileName] + setupDb $fileName Bad false + sql execute -execute scalar $db "PRAGMA journal_mode;" +} -cleanup { + cleanupDb $fileName + unset -nocomplain db fileName +} -constraints \ +{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}} + +############################################################################### + +runTest {test tkt-448d663d11-1.19 {'Bad' journal mode, WAL db} -body { + set fileName tkt-448d663d11-1.19.db file copy -force [file join $path wal.db] \ [file join [getTemporaryPath] $fileName] - setupDb $fileName Wal + setupDb $fileName Bad false sql execute -execute scalar $db "PRAGMA journal_mode;" } -cleanup { cleanupDb $fileName unset -nocomplain db fileName } -constraints \