System.Data.SQLite
Check-in [50b4844f0c]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix for [448d663d11], with tests. Also fixes [2a0c90d7b0]. See also [08f3d3daf2].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 50b4844f0c46535945f6ab5fa094eb8d3086ce7e
User & Date: mistachkin 2011-07-05 06:07:58
Original Comment: Fix for [448d663d11], with tests.
References
2011-07-14
12:37 Closed ticket [e6d0ed896b]: Add WAL journal mode plus 4 other changes artifact: 8bb16172df user: mistachkin
2011-07-07
04:36 Ticket [2a0c90d7b0] Connection string bug in SQLiteConnection status still Closed with 2 other changes artifact: a60f7f54d2 user: mistachkin
04:36 Ticket [448d663d11] Add missing journal modes to connection string status still Closed with 2 other changes artifact: 3c0fc2e2e3 user: mistachkin
2011-07-05
06:27 Closed ticket [448d663d11]. artifact: 932fe19654 user: mistachkin
06:24 Closed ticket [2a0c90d7b0]: Connection string bug in SQLiteConnection plus 2 other changes artifact: df3fc79acc user: mistachkin
Context
2011-07-05
07:47
Fix for [2c630bffa7] and [b0a5990f48]. check-in: eb6a22a1a9 user: mistachkin tags: trunk
06:07
Fix for [448d663d11], with tests. Also fixes [2a0c90d7b0]. See also [08f3d3daf2]. check-in: 50b4844f0c user: mistachkin tags: trunk
04:40
Add tests for ticket [448d663d11]. Further refinements to unit testing infrastructure. check-in: 11f11f0c83 user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to System.Data.SQLite/SQLiteConnection.cs.

   885    885               defValue = FindKey(opts, "Cache Size", "2000");
   886    886               if (Convert.ToInt32(defValue, CultureInfo.InvariantCulture) != 2000)
   887    887               {
   888    888                 cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA cache_size={0}", defValue);
   889    889                 cmd.ExecuteNonQuery();
   890    890               }
   891    891   
   892         -            defValue = FindKey(opts, "Journal Mode", "Delete");
          892  +            defValue = FindKey(opts, "Journal Mode", "Default");
   893    893               if (String.Compare(defValue, "Default", StringComparison.OrdinalIgnoreCase) != 0)
   894    894               {
   895    895                 cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA journal_mode={0}", defValue);
   896    896                 cmd.ExecuteNonQuery();
   897    897               }
   898    898   
   899    899               defValue = FindKey(opts, "Foreign Keys", Boolean.FalseString);

Changes to System.Data.SQLite/SQLiteConvert.cs.

   761    761     /// 
   762    762     /// If a program or virus scanner is interfering with SQLite's journal file, you may receive errors like "unable to open database file"
   763    763     /// when starting a transaction.  If this is happening, you may want to change the default journal mode to Persist.
   764    764     /// </remarks>
   765    765     public enum SQLiteJournalModeEnum
   766    766     {
   767    767       /// <summary>
   768         -    /// The default mode, this causes SQLite to create and destroy the journal file as-needed.
          768  +    /// The default mode, this causes SQLite to use the existing journaling mode for the database.
          769  +    /// </summary>
          770  +    Default = -1,
          771  +    /// <summary>
          772  +    /// SQLite will create and destroy the journal file as-needed.
   769    773       /// </summary>
   770    774       Delete = 0,
   771    775       /// <summary>
   772    776       /// When this is set, SQLite will keep the journal file even after a transaction has completed.  It's contents will be erased,
   773    777       /// and the journal re-used as often as needed.  If it is deleted, it will be recreated the next time it is needed.
   774    778       /// </summary>
   775    779       Persist = 1,
   776    780       /// <summary>
   777    781       /// This option disables the rollback journal entirely.  Interrupted transactions or a program crash can cause database
   778    782       /// corruption in this mode!
   779    783       /// </summary>
   780         -    Off = 2
          784  +    Off = 2,
          785  +    /// <summary>
          786  +    /// SQLite will truncate the journal file to zero-length instead of deleting it.
          787  +    /// </summary>
          788  +    Truncate = 3,
          789  +    /// <summary>
          790  +    /// SQLite will store the journal in volatile RAM.  This saves disk I/O but at the expense of database safety and integrity.
          791  +    /// If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the
          792  +    /// database file will very likely go corrupt.
          793  +    /// </summary>
          794  +    Memory = 4,
          795  +    /// <summary>
          796  +    /// SQLite uses a write-ahead log instead of a rollback journal to implement transactions.  The WAL journaling mode is persistent;
          797  +    /// after being set it stays in effect across multiple database connections and after closing and reopening the database. A database
          798  +    /// in WAL journaling mode can only be accessed by SQLite version 3.7.0 or later.
          799  +    /// </summary>
          800  +    Wal = 5
   781    801     }
   782    802   
   783    803     /// <summary>
   784    804     /// Struct used internally to determine the datatype of a column in a resultset
   785    805     /// </summary>
   786    806     internal class SQLiteType
   787    807     {

Changes to Tests/common.eagle.

    25     25         #
    26     26         return $fileName
    27     27       }
    28     28   
    29     29       return ""
    30     30     }
    31     31   
    32         -  proc setupDb {fileName {mode ""} {varName db}} {
           32  +  proc setupDb {fileName {mode ""} {delete true} {varName db}} {
    33     33       set fileName [file join [getTemporaryPath] $fileName]
    34         -    catch {file delete $fileName}
           34  +
           35  +    if {$delete} then {
           36  +      catch {file delete $fileName}
           37  +    }
           38  +
    35     39       upvar 1 $varName db
    36     40   
    37     41       set connection {Data Source=${fileName}}
    38     42   
    39     43       if {[string length $mode] > 0} then {
    40     44         append connection {;Journal Mode=${mode}}
    41     45       }

Changes to Tests/tkt-448d663d11.eagle.

    15     15   ###############################################################################
    16     16   
    17     17   source [file join $path common.eagle]
    18     18   loadAssembly System.Data.SQLite.dll
    19     19   
    20     20   ###############################################################################
    21     21   
    22         -runTest {test tkt-448d663d11-1.1 {'Default' journal mode, new db} -body {
    23         -  setupDb [set fileName tkt-448d663d11-1.1.db] Default
           22  +runTest {test tkt-448d663d11-1.1 {missing journal mode, new db} -body {
           23  +  setupDb [set fileName tkt-448d663d11-1.1.db]
           24  +  sql execute -execute scalar $db "PRAGMA journal_mode;"
           25  +} -cleanup {
           26  +  cleanupDb $fileName
           27  +  unset -nocomplain db fileName
           28  +} -constraints \
           29  +{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}}
           30  +
           31  +###############################################################################
           32  +
           33  +runTest {test tkt-448d663d11-1.2 {missing journal mode, WAL db} -body {
           34  +  set fileName tkt-448d663d11-1.2.db
           35  +  file copy -force [file join $path wal.db] \
           36  +      [file join [getTemporaryPath] $fileName]
           37  +  setupDb $fileName "" false
           38  +  sql execute -execute scalar $db "PRAGMA journal_mode;"
           39  +} -cleanup {
           40  +  cleanupDb $fileName
           41  +  unset -nocomplain db fileName
           42  +} -constraints \
           43  +{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {wal}}
           44  +
           45  +###############################################################################
           46  +
           47  +runTest {test tkt-448d663d11-1.3 {missing journal mode, non-WAL db} -body {
           48  +  set fileName tkt-448d663d11-1.3.db
           49  +  file copy -force [file join $path nonWal.db] \
           50  +      [file join [getTemporaryPath] $fileName]
           51  +  setupDb $fileName "" false
           52  +  sql execute -execute scalar $db "PRAGMA journal_mode;"
           53  +} -cleanup {
           54  +  cleanupDb $fileName
           55  +  unset -nocomplain db fileName
           56  +} -constraints \
           57  +{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}}
           58  +
           59  +###############################################################################
           60  +
           61  +runTest {test tkt-448d663d11-1.4 {'Default' journal mode, new db} -body {
           62  +  setupDb [set fileName tkt-448d663d11-1.4.db] Default
    24     63     sql execute -execute scalar $db "PRAGMA journal_mode;"
    25     64   } -cleanup {
    26     65     cleanupDb $fileName
    27     66     unset -nocomplain db fileName
    28     67   } -constraints \
    29     68   {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}}
    30     69   
    31     70   ###############################################################################
    32     71   
    33         -runTest {test tkt-448d663d11-1.2 {'Default' journal mode, WAL db} -body {
    34         -  set fileName tkt-448d663d11-1.2.db
           72  +runTest {test tkt-448d663d11-1.5 {'Default' journal mode, WAL db} -body {
           73  +  set fileName tkt-448d663d11-1.5.db
    35     74     file copy -force [file join $path wal.db] \
    36     75         [file join [getTemporaryPath] $fileName]
    37         -  setupDb $fileName Default
           76  +  setupDb $fileName Default false
    38     77     sql execute -execute scalar $db "PRAGMA journal_mode;"
    39     78   } -cleanup {
    40     79     cleanupDb $fileName
    41     80     unset -nocomplain db fileName
    42     81   } -constraints \
    43     82   {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {wal}}
    44     83   
    45     84   ###############################################################################
    46     85   
    47         -runTest {test tkt-448d663d11-1.3 {'Default' journal mode, non-WAL db} -body {
    48         -  set fileName tkt-448d663d11-1.3.db
           86  +runTest {test tkt-448d663d11-1.6 {'Default' journal mode, non-WAL db} -body {
           87  +  set fileName tkt-448d663d11-1.6.db
    49     88     file copy -force [file join $path nonWal.db] \
    50     89         [file join [getTemporaryPath] $fileName]
    51         -  setupDb $fileName Default
           90  +  setupDb $fileName Default false
           91  +  sql execute -execute scalar $db "PRAGMA journal_mode;"
           92  +} -cleanup {
           93  +  cleanupDb $fileName
           94  +  unset -nocomplain db fileName
           95  +} -constraints \
           96  +{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}}
           97  +
           98  +###############################################################################
           99  +
          100  +runTest {test tkt-448d663d11-1.7 {'Delete' journal mode, new db} -body {
          101  +  setupDb [set fileName tkt-448d663d11-1.7.db] Delete
    52    102     sql execute -execute scalar $db "PRAGMA journal_mode;"
    53    103   } -cleanup {
    54    104     cleanupDb $fileName
    55    105     unset -nocomplain db fileName
    56    106   } -constraints \
    57    107   {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}}
    58    108   
    59    109   ###############################################################################
    60    110   
    61         -runTest {test tkt-448d663d11-1.4 {'Delete' journal mode, new db} -body {
    62         -  setupDb [set fileName tkt-448d663d11-1.4.db] Delete
          111  +runTest {test tkt-448d663d11-1.8 {'Delete' journal mode, WAL db} -body {
          112  +  set fileName tkt-448d663d11-1.8.db
          113  +  file copy -force [file join $path wal.db] \
          114  +      [file join [getTemporaryPath] $fileName]
          115  +  setupDb $fileName Delete false
    63    116     sql execute -execute scalar $db "PRAGMA journal_mode;"
    64    117   } -cleanup {
    65    118     cleanupDb $fileName
    66    119     unset -nocomplain db fileName
    67    120   } -constraints \
    68    121   {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}}
    69    122   
    70    123   ###############################################################################
    71    124   
    72         -runTest {test tkt-448d663d11-1.5 {'Delete' journal mode, WAL db} -body {
    73         -  set fileName tkt-448d663d11-1.5.db
    74         -  file copy -force [file join $path wal.db] \
          125  +runTest {test tkt-448d663d11-1.9 {'Delete' journal mode, non-WAL db} -body {
          126  +  set fileName tkt-448d663d11-1.9.db
          127  +  file copy -force [file join $path nonWal.db] \
    75    128         [file join [getTemporaryPath] $fileName]
    76         -  setupDb $fileName Delete
          129  +  setupDb $fileName Delete false
    77    130     sql execute -execute scalar $db "PRAGMA journal_mode;"
    78    131   } -cleanup {
    79    132     cleanupDb $fileName
    80    133     unset -nocomplain db fileName
    81    134   } -constraints \
    82    135   {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}}
    83    136   
    84    137   ###############################################################################
    85    138   
    86         -runTest {test tkt-448d663d11-1.6 {'Delete' journal mode, non-WAL db} -body {
    87         -  set fileName tkt-448d663d11-1.6.db
    88         -  file copy -force [file join $path nonWal.db] \
    89         -      [file join [getTemporaryPath] $fileName]
    90         -  setupDb $fileName Delete
    91         -  sql execute -execute scalar $db "PRAGMA journal_mode;"
    92         -} -cleanup {
    93         -  cleanupDb $fileName
    94         -  unset -nocomplain db fileName
    95         -} -constraints \
    96         -{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}}
    97         -
    98         -###############################################################################
    99         -
   100         -runTest {test tkt-448d663d11-1.7 {'Persist' journal mode, new db} -body {
   101         -  setupDb [set fileName tkt-448d663d11-1.7.db] Persist
          139  +runTest {test tkt-448d663d11-1.10 {'Persist' journal mode, new db} -body {
          140  +  setupDb [set fileName tkt-448d663d11-1.10.db] Persist
   102    141     sql execute -execute scalar $db "PRAGMA journal_mode;"
   103    142   } -cleanup {
   104    143     cleanupDb $fileName
   105    144     unset -nocomplain db fileName
   106    145   } -constraints \
   107    146   {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {persist}}
   108    147   
   109    148   ###############################################################################
   110    149   
   111         -runTest {test tkt-448d663d11-1.8 {'Off' journal mode, new db} -body {
   112         -  setupDb [set fileName tkt-448d663d11-1.8.db] Off
          150  +runTest {test tkt-448d663d11-1.11 {'Off' journal mode, new db} -body {
          151  +  setupDb [set fileName tkt-448d663d11-1.11.db] Off
   113    152     sql execute -execute scalar $db "PRAGMA journal_mode;"
   114    153   } -cleanup {
   115    154     cleanupDb $fileName
   116    155     unset -nocomplain db fileName
   117    156   } -constraints \
   118    157   {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {off}}
   119    158   
   120    159   ###############################################################################
   121    160   
   122         -runTest {test tkt-448d663d11-1.9 {'Truncate' journal mode, new db} -body {
   123         -  setupDb [set fileName tkt-448d663d11-1.9.db] Truncate
          161  +runTest {test tkt-448d663d11-1.12 {'Truncate' journal mode, new db} -body {
          162  +  setupDb [set fileName tkt-448d663d11-1.12.db] Truncate
   124    163     sql execute -execute scalar $db "PRAGMA journal_mode;"
   125    164   } -cleanup {
   126    165     cleanupDb $fileName
   127    166     unset -nocomplain db fileName
   128    167   } -constraints \
   129    168   {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {truncate}}
   130    169   
   131    170   ###############################################################################
   132    171   
   133         -runTest {test tkt-448d663d11-1.10 {'Memory' journal mode, new db} -body {
   134         -  setupDb [set fileName tkt-448d663d11-1.10.db] Memory
          172  +runTest {test tkt-448d663d11-1.13 {'Memory' journal mode, new db} -body {
          173  +  setupDb [set fileName tkt-448d663d11-1.13.db] Memory
   135    174     sql execute -execute scalar $db "PRAGMA journal_mode;"
   136    175   } -cleanup {
   137    176     cleanupDb $fileName
   138    177     unset -nocomplain db fileName
   139    178   } -constraints \
   140    179   {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {memory}}
   141    180   
   142    181   ###############################################################################
   143    182   
   144         -runTest {test tkt-448d663d11-1.11 {'Wal' journal mode, new db} -body {
   145         -  setupDb [set fileName tkt-448d663d11-1.11.db] Wal
          183  +runTest {test tkt-448d663d11-1.14 {'Wal' journal mode, new db} -body {
          184  +  setupDb [set fileName tkt-448d663d11-1.14.db] Wal
          185  +  sql execute -execute scalar $db "PRAGMA journal_mode;"
          186  +} -cleanup {
          187  +  cleanupDb $fileName
          188  +  unset -nocomplain db fileName
          189  +} -constraints \
          190  +{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {wal}}
          191  +
          192  +###############################################################################
          193  +
          194  +runTest {test tkt-448d663d11-1.15 {'Wal' journal mode, non-WAL db} -body {
          195  +  set fileName tkt-448d663d11-1.15.db
          196  +  file copy -force [file join $path nonWal.db] \
          197  +      [file join [getTemporaryPath] $fileName]
          198  +  setupDb $fileName Wal false
   146    199     sql execute -execute scalar $db "PRAGMA journal_mode;"
   147    200   } -cleanup {
   148    201     cleanupDb $fileName
   149    202     unset -nocomplain db fileName
   150    203   } -constraints \
   151    204   {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {wal}}
   152    205   
   153    206   ###############################################################################
   154    207   
   155         -runTest {test tkt-448d663d11-1.12 {'Wal' journal mode, non-WAL db} -body {
   156         -  set fileName tkt-448d663d11-1.12.db
   157         -  file copy -force [file join $path nonWal.db] \
          208  +runTest {test tkt-448d663d11-1.16 {'Wal' journal mode, WAL db} -body {
          209  +  set fileName tkt-448d663d11-1.16.db
          210  +  file copy -force [file join $path wal.db] \
   158    211         [file join [getTemporaryPath] $fileName]
   159         -  setupDb $fileName Wal
          212  +  setupDb $fileName Wal false
   160    213     sql execute -execute scalar $db "PRAGMA journal_mode;"
   161    214   } -cleanup {
   162    215     cleanupDb $fileName
   163    216     unset -nocomplain db fileName
   164    217   } -constraints \
   165    218   {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {wal}}
   166    219   
   167    220   ###############################################################################
   168    221   
   169         -runTest {test tkt-448d663d11-1.13 {'Wal' journal mode, WAL db} -body {
   170         -  set fileName tkt-448d663d11-1.13.db
          222  +runTest {test tkt-448d663d11-1.17 {'Bad' journal mode, new db} -body {
          223  +  setupDb [set fileName tkt-448d663d11-1.17.db] Bad
          224  +  sql execute -execute scalar $db "PRAGMA journal_mode;"
          225  +} -cleanup {
          226  +  cleanupDb $fileName
          227  +  unset -nocomplain db fileName
          228  +} -constraints \
          229  +{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}}
          230  +
          231  +###############################################################################
          232  +
          233  +runTest {test tkt-448d663d11-1.18 {'Bad' journal mode, non-WAL db} -body {
          234  +  set fileName tkt-448d663d11-1.18.db
          235  +  file copy -force [file join $path nonWal.db] \
          236  +      [file join [getTemporaryPath] $fileName]
          237  +  setupDb $fileName Bad false
          238  +  sql execute -execute scalar $db "PRAGMA journal_mode;"
          239  +} -cleanup {
          240  +  cleanupDb $fileName
          241  +  unset -nocomplain db fileName
          242  +} -constraints \
          243  +{eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {delete}}
          244  +
          245  +###############################################################################
          246  +
          247  +runTest {test tkt-448d663d11-1.19 {'Bad' journal mode, WAL db} -body {
          248  +  set fileName tkt-448d663d11-1.19.db
   171    249     file copy -force [file join $path wal.db] \
   172    250         [file join [getTemporaryPath] $fileName]
   173         -  setupDb $fileName Wal
          251  +  setupDb $fileName Bad false
   174    252     sql execute -execute scalar $db "PRAGMA journal_mode;"
   175    253   } -cleanup {
   176    254     cleanupDb $fileName
   177    255     unset -nocomplain db fileName
   178    256   } -constraints \
   179    257   {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} -result {wal}}
   180    258   
   181    259   ###############################################################################
   182    260   
   183    261   runTestEpilogue