System.Data.SQLite

Check-in [9e2d3ca71a]
Login

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

Overview
Comment:First shot at nested transactions and transaction enlistment
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | sourceforge
Files: files | file ages | folders
SHA1: 9e2d3ca71a92ce163b7121321a294ceecd51c15f
User & Date: rmsimpson 2006-01-27 05:55:12.000
Context
2006-01-27
05:55
Transaction enlistment and nested transaction test added check-in: a15dd26217 user: rmsimpson tags: sourceforge
05:55
First shot at nested transactions and transaction enlistment check-in: 9e2d3ca71a user: rmsimpson tags: sourceforge
2006-01-24
01:59
no message check-in: 4651c69837 user: rmsimpson tags: sourceforge
Changes
Unified Diff Show Whitespace Changes Patch
Changes to System.Data.SQLite/SQLiteCommand.cs.
49
50
51
52
53
54
55




56
57
58
59
60
61
62
    /// The SQL command text, broken into individual SQL statements as they are executed
    /// </summary>
    internal List<SQLiteStatement> _statementList;
    /// <summary>
    /// Unprocessed SQL text that has not been executed
    /// </summary>
    internal string _remainingText;





    ///<overloads>
    /// Constructs a new SQLiteCommand
    /// </overloads>
    /// <summary>
    /// Default constructor
    /// </summary>







>
>
>
>







49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
    /// The SQL command text, broken into individual SQL statements as they are executed
    /// </summary>
    internal List<SQLiteStatement> _statementList;
    /// <summary>
    /// Unprocessed SQL text that has not been executed
    /// </summary>
    internal string _remainingText;
    /// <summary>
    /// Transaction associated with this command
    /// </summary>
    private SQLiteTransaction _transaction;

    ///<overloads>
    /// Constructs a new SQLiteCommand
    /// </overloads>
    /// <summary>
    /// Default constructor
    /// </summary>
115
116
117
118
119
120
121

122
123
124
125
126
127
128
    {
      _statementList = null;
      _isReaderOpen = false;
      _commandTimeout = 30;
      _parameterCollection = new SQLiteParameterCollection(this);
      _designTimeVisible = true;
      _updateRowSource = UpdateRowSource.FirstReturnedRecord;


      if (strSql != null)
        CommandText = strSql;

      if (cnn != null)
        DbConnection = cnn;
    }







>







119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
    {
      _statementList = null;
      _isReaderOpen = false;
      _commandTimeout = 30;
      _parameterCollection = new SQLiteParameterCollection(this);
      _designTimeVisible = true;
      _updateRowSource = UpdateRowSource.FirstReturnedRecord;
      _transaction = null;

      if (strSql != null)
        CommandText = strSql;

      if (cnn != null)
        DbConnection = cnn;
    }
348
349
350
351
352
353
354
355
356
357
358
359



360


361


362
363
364
365
366
367
368

    /// <summary>
    /// The transaction associated with this command.  SQLite only supports one transaction per connection, so this property forwards to the
    /// command's underlying connection.
    /// </summary>
    public new SQLiteTransaction Transaction
    {
      get { return _cnn._activeTransaction; }
      set
      {
        if (_cnn != null)
        {



          if (value != _cnn._activeTransaction && value != null)


            throw new ArgumentOutOfRangeException("SQLiteTransaction", "Transaction is for a different connection than the one associated with this Command");


        }
        else if (value != null)
          throw new ArgumentOutOfRangeException("SQLiteTransaction", "Not associated with a connection");
      }
    }

    /// <summary>







|




>
>
>
|
>
>
|
>
>







353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380

    /// <summary>
    /// The transaction associated with this command.  SQLite only supports one transaction per connection, so this property forwards to the
    /// command's underlying connection.
    /// </summary>
    public new SQLiteTransaction Transaction
    {
      get { return _transaction; }
      set
      {
        if (_cnn != null)
        {
          if (_isReaderOpen)
            throw new InvalidOperationException("Cannot set Transaction while a DataReader is active");

          if (value != null)
          {
            if (value._cnn != _cnn)
              throw new ArgumentException("Transaction is not associated with the command's connection");
          }
          _transaction = value;
        }
        else if (value != null)
          throw new ArgumentOutOfRangeException("SQLiteTransaction", "Not associated with a connection");
      }
    }

    /// <summary>
Changes to System.Data.SQLite/SQLiteConnection.cs.
101
102
103
104
105
106
107


108


109
110
111
112
113
114
115
116
117
    /// </summary>
    private ConnectionState     _connectionState;
    /// <summary>
    /// The connection string
    /// </summary>
    private string              _connectionString;
    /// <summary>


    /// One transaction allowed per connection please!


    /// </summary>
    internal SQLiteTransaction  _activeTransaction;
    /// <summary>
    /// The base SQLite object to interop with
    /// </summary>
    internal SQLiteBase          _sql;
    /// <summary>
    /// Commands associated with this connection
    /// </summary>







>
>
|
>
>

|







101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
    /// </summary>
    private ConnectionState     _connectionState;
    /// <summary>
    /// The connection string
    /// </summary>
    private string              _connectionString;
    /// <summary>
    /// Nesting level of the transactions open on the connection
    /// </summary>
    internal int                 _transactionLevel;
    /// <summary>
    /// Whether or not the connection is enlisted in a distrubuted transaction
    /// </summary>
    internal bool                _enlisted;
    /// <summary>
    /// The base SQLite object to interop with
    /// </summary>
    internal SQLiteBase          _sql;
    /// <summary>
    /// Commands associated with this connection
    /// </summary>
203
204
205
206
207
208
209
210

211
212
213
214
215
216
217
    }

    private void Initialize(string connectionString)
    {
      _sql = null;
      _connectionState = ConnectionState.Closed;
      _connectionString = "";
      _activeTransaction = null;

      _commandList = new List<SQLiteCommand>();

      if (connectionString != null)
        ConnectionString = connectionString;
    }

    /// <summary>







|
>







207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
    }

    private void Initialize(string connectionString)
    {
      _sql = null;
      _connectionState = ConnectionState.Closed;
      _connectionString = "";
      _transactionLevel = 0;
      _enlisted = false;
      _commandList = new List<SQLiteCommand>();

      if (connectionString != null)
        ConnectionString = connectionString;
    }

    /// <summary>
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
    /// environment, one may instead choose to lock the database immediately to avoid any possible writer deadlock.</param>
    /// <returns>Returns a SQLiteTransaction object.</returns>
    public SQLiteTransaction BeginTransaction(bool deferredLock)
    {
      if (_connectionState != ConnectionState.Open)
        throw new InvalidOperationException();

      if (_activeTransaction != null)
        throw new ArgumentException("Transaction already pending");

      _activeTransaction = new SQLiteTransaction(this, deferredLock);
      return _activeTransaction;
    }

    /// <summary>
    /// Creates a new SQLiteTransaction if one isn't already active on the connection.
    /// </summary>
    /// <param name="isolationLevel">SQLite doesn't support varying isolation levels, so this parameter is ignored.</param>
    /// <returns>Returns a SQLiteTransaction object.</returns>







<
<
<
|
<







350
351
352
353
354
355
356



357

358
359
360
361
362
363
364
    /// environment, one may instead choose to lock the database immediately to avoid any possible writer deadlock.</param>
    /// <returns>Returns a SQLiteTransaction object.</returns>
    public SQLiteTransaction BeginTransaction(bool deferredLock)
    {
      if (_connectionState != ConnectionState.Open)
        throw new InvalidOperationException();




      return new SQLiteTransaction(this, deferredLock);

    }

    /// <summary>
    /// Creates a new SQLiteTransaction if one isn't already active on the connection.
    /// </summary>
    /// <param name="isolationLevel">SQLite doesn't support varying isolation levels, so this parameter is ignored.</param>
    /// <returns>Returns a SQLiteTransaction object.</returns>
561
562
563
564
565
566
567













568
569
570
571
572
573
574
      }
      KeyValuePair<string, string>[] ar = new KeyValuePair<string, string>[ls.Count];
      ls.CopyTo(ar, 0);

      // Return the array of key-value pairs
      return ar;
    }














    /// <summary>
    /// Looks for a key in the array of key/values of the parameter string.  If not found, return the specified default value
    /// </summary>
    /// <param name="opts">The Key/Value pair array to look in</param>
    /// <param name="key">The key to find</param>
    /// <param name="defValue">The default value to return if the key is not found</param>







>
>
>
>
>
>
>
>
>
>
>
>
>







562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
      }
      KeyValuePair<string, string>[] ar = new KeyValuePair<string, string>[ls.Count];
      ls.CopyTo(ar, 0);

      // Return the array of key-value pairs
      return ar;
    }

#if !PLATFORM_COMPACTFRAMEWORK
    public override void EnlistTransaction(System.Transactions.Transaction transaction)
    {
      if (_transactionLevel > 0 && transaction != null)
        throw new ArgumentException("Unable to enlist in transaction, a local transaction already exists");

      if (_enlisted == true)
        throw new ArgumentException("Already enlisted in a transaction");

      transaction.EnlistVolatile(new SQLiteEnlistment(this), System.Transactions.EnlistmentOptions.None);
    }
#endif

    /// <summary>
    /// Looks for a key in the array of key/values of the parameter string.  If not found, return the specified default value
    /// </summary>
    /// <param name="opts">The Key/Value pair array to look in</param>
    /// <param name="key">The key to find</param>
    /// <param name="defValue">The default value to return if the key is not found</param>
625
626
627
628
629
630
631





632
633
634
635
636
637
638

        _dataSource = System.IO.Path.GetFileNameWithoutExtension(strFile);

        _sql.Execute(String.Format(CultureInfo.InvariantCulture, "PRAGMA Synchronous={0}", FindKey(opts, "Synchronous", "Normal")));
        _sql.Execute(String.Format(CultureInfo.InvariantCulture, "PRAGMA Cache_Size={0}", FindKey(opts, "Cache Size", "2000")));
        if (String.Compare(":MEMORY:", strFile, true, CultureInfo.InvariantCulture) == 0)
          _sql.Execute(String.Format(CultureInfo.InvariantCulture, "PRAGMA Page_Size={0}", FindKey(opts, "Page Size", "1024")));





      }
      catch (SQLiteException)
      {
        OnStateChange(ConnectionState.Broken);
        throw;
      }
      OnStateChange(ConnectionState.Open);







>
>
>
>
>







639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657

        _dataSource = System.IO.Path.GetFileNameWithoutExtension(strFile);

        _sql.Execute(String.Format(CultureInfo.InvariantCulture, "PRAGMA Synchronous={0}", FindKey(opts, "Synchronous", "Normal")));
        _sql.Execute(String.Format(CultureInfo.InvariantCulture, "PRAGMA Cache_Size={0}", FindKey(opts, "Cache Size", "2000")));
        if (String.Compare(":MEMORY:", strFile, true, CultureInfo.InvariantCulture) == 0)
          _sql.Execute(String.Format(CultureInfo.InvariantCulture, "PRAGMA Page_Size={0}", FindKey(opts, "Page Size", "1024")));

#if !PLATFORM_COMPACTFRAMEWORK
        if (FindKey(opts, "Enlist", "Y").ToUpper()[0] == 'Y' && Transactions.Transaction.Current != null)
          EnlistTransaction(Transactions.Transaction.Current);
#endif
      }
      catch (SQLiteException)
      {
        OnStateChange(ConnectionState.Broken);
        throw;
      }
      OnStateChange(ConnectionState.Open);
Added System.Data.SQLite/SQLiteEnlistment.cs.


































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
/********************************************************
 * ADO.NET 2.0 Data Provider for SQLite Version 3.X
 * Written by Robert Simpson (robert@blackcastlesoft.com)
 * 
 * Released to the public domain, use at your own risk!
 ********************************************************/

#if !PLATFORM_COMPACTFRAMEWORK
namespace System.Data.SQLite
{
  using System;
  using System.Data;
  using System.Data.Common;
  using System.Transactions;

  internal class SQLiteEnlistment : IEnlistmentNotification
  {
    private SQLiteTransaction _transaction;

    internal SQLiteEnlistment(SQLiteConnection cnn)
    {
      _transaction = cnn.BeginTransaction();
      _transaction.Connection._enlisted = true;
    }

    #region IEnlistmentNotification Members

    public void Commit(Enlistment enlistment)
    {
      try
      {
        _transaction.IsValid();
        _transaction.Connection._transactionLevel = 1;
        _transaction.Commit();

        enlistment.Done();
      }
      finally
      {
        _transaction.Connection._enlisted = false;
        _transaction = null;
      }
    }

    public void InDoubt(Enlistment enlistment)
    {
      enlistment.Done();
    }

    public void Prepare(PreparingEnlistment preparingEnlistment)
    {
      try
      {
        _transaction.IsValid();
      }
      catch(Exception e)
      {
        preparingEnlistment.ForceRollback(e);
        return;
      }
      preparingEnlistment.Done();
    }

    public void Rollback(Enlistment enlistment)
    {
      _transaction.Connection._enlisted = false;
      try
      {
        _transaction.Rollback();
        enlistment.Done();
      }
      finally
      {
        _transaction = null;
      }
    }

    #endregion
  }
}
#endif // !PLATFORM_COMPACT_FRAMEWORK
Changes to System.Data.SQLite/SQLiteTransaction.cs.
24
25
26
27
28
29
30



31
32
33
34
35
36
37
38
39
40
41
42

43
44
45

46
47
48
49
50
51

52
53
54
55
56
57
58
59
60
61
62

63

64



65
66
67
68
69
70
71
    /// <summary>
    /// Constructs the transaction object, binding it to the supplied connection
    /// </summary>
    /// <param name="cnn">The connection to open a transaction on</param>
    /// <param name="deferredLock">TRUE to defer the writelock, or FALSE to lock immediately</param>
    internal SQLiteTransaction(SQLiteConnection cnn, bool deferredLock)
    {



      try
      {
        if (!deferredLock)
          cnn._sql.Execute("BEGIN IMMEDIATE");
        else
          cnn._sql.Execute("BEGIN");

        _cnn = cnn;
      }
      catch (SQLiteException)
      {
        BaseDispose();

        throw;
      }
    }


    /// <summary>
    /// Commits the current transaction.
    /// </summary>
    public override void Commit()
    {

      if (_cnn == null)
        throw new ArgumentNullException();

      try
      {
        _cnn._sql.Execute("COMMIT");
      }
      catch (SQLiteException)
      {
        BaseDispose();
        throw;

      }

      BaseDispose();



    }

    /// <summary>
    /// Returns the underlying connection to which this transaction applies.
    /// </summary>
    public new SQLiteConnection Connection
    {







>
>
>






<
<



|
>



>






>
|
|
|




|

<
<
>

>
|
>
>
>







24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39


40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64


65
66
67
68
69
70
71
72
73
74
75
76
77
78
    /// <summary>
    /// Constructs the transaction object, binding it to the supplied connection
    /// </summary>
    /// <param name="cnn">The connection to open a transaction on</param>
    /// <param name="deferredLock">TRUE to defer the writelock, or FALSE to lock immediately</param>
    internal SQLiteTransaction(SQLiteConnection cnn, bool deferredLock)
    {
      _cnn = cnn;
      if (_cnn._transactionLevel++ == 0)
      {
      try
      {
        if (!deferredLock)
          cnn._sql.Execute("BEGIN IMMEDIATE");
        else
          cnn._sql.Execute("BEGIN");


      }
      catch (SQLiteException)
      {
          _cnn._transactionLevel--;
          _cnn = null;
        throw;
      }
    }
    }

    /// <summary>
    /// Commits the current transaction.
    /// </summary>
    public override void Commit()
    {
      IsValid();

      if (--_cnn._transactionLevel == 0)
      {
      try
      {
        _cnn._sql.Execute("COMMIT");
      }
        finally
      {


          _cnn = null;
      }
      }
      else
      {
        _cnn = null;
      }
    }

    /// <summary>
    /// Returns the underlying connection to which this transaction applies.
    /// </summary>
    public new SQLiteConnection Connection
    {
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

115
116
117
118
119

120
121
122
123
124
125


126


127

128
129
130

    /// Disposes the transaction.  If it is currently active, any changes are rolled back.
    /// </summary>
    protected override void Dispose(bool disposing)
    {
      if (_cnn != null) 
        Rollback();

      _cnn = null;

      base.Dispose(disposing);
    }

    /// <summary>
    /// Gets the isolation level of the transaction.  SQLite does not support isolation levels, so this always returns Unspecified.
    /// </summary>
    public override IsolationLevel IsolationLevel
    {
      get { return IsolationLevel.Unspecified; }
    }

    /// <summary>
    /// Rolls back the active transaction.
    /// </summary>
    public override void Rollback()
    {
      if (_cnn == null)
        throw new ArgumentNullException();

      try
      {
        _cnn._sql.Execute("ROLLBACK");

      }
      catch (SQLiteException)
      {
        BaseDispose();
        throw;

      }
      BaseDispose();
    }

    private void BaseDispose()
    {


      _cnn._activeTransaction = null;


      _cnn = null;

    }
  }
}








<
<
















<
|




>

|

<
<
>

<


|

>
>
|
>
>

>



>
91
92
93
94
95
96
97


98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113

114
115
116
117
118
119
120
121
122


123
124

125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
    /// Disposes the transaction.  If it is currently active, any changes are rolled back.
    /// </summary>
    protected override void Dispose(bool disposing)
    {
      if (_cnn != null) 
        Rollback();



      base.Dispose(disposing);
    }

    /// <summary>
    /// Gets the isolation level of the transaction.  SQLite does not support isolation levels, so this always returns Unspecified.
    /// </summary>
    public override IsolationLevel IsolationLevel
    {
      get { return IsolationLevel.Unspecified; }
    }

    /// <summary>
    /// Rolls back the active transaction.
    /// </summary>
    public override void Rollback()
    {

      IsValid();

      try
      {
        _cnn._sql.Execute("ROLLBACK");
        _cnn._transactionLevel = 0;
      }
      finally
      {


        _cnn = null;
      }

    }

    internal void IsValid()
    {
      if (_cnn == null)
        throw new ArgumentNullException("No connection associated with this transaction");

      if (_cnn._transactionLevel == 0)
      {
      _cnn = null;
        throw new SQLiteException((int)SQLiteErrorCode.Misuse, "No transactions active on this connection");
    }
  }
}
}
Changes to System.Data.SQLite/System.Data.SQLite.
40
41
42
43
44
45
46

47
48
49
50
51
52
53
    <GenerateSerializationAssemblies>Off</GenerateSerializationAssemblies>
    <ErrorReport>prompt</ErrorReport>
    <FileAlignment>512</FileAlignment>
  </PropertyGroup>
  <ItemGroup>
    <Reference Include="System" />
    <Reference Include="System.Data" />

    <Reference Include="System.Xml" />
  </ItemGroup>
  <ItemGroup>
    <Compile Include="AssemblyInfo.cs" />
    <Compile Include="SQLite3.cs" />
    <Compile Include="SQLite3_UTF16.cs" />
    <Compile Include="SQLiteBase.cs" />







>







40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
    <GenerateSerializationAssemblies>Off</GenerateSerializationAssemblies>
    <ErrorReport>prompt</ErrorReport>
    <FileAlignment>512</FileAlignment>
  </PropertyGroup>
  <ItemGroup>
    <Reference Include="System" />
    <Reference Include="System.Data" />
    <Reference Include="System.Transactions" />
    <Reference Include="System.Xml" />
  </ItemGroup>
  <ItemGroup>
    <Compile Include="AssemblyInfo.cs" />
    <Compile Include="SQLite3.cs" />
    <Compile Include="SQLite3_UTF16.cs" />
    <Compile Include="SQLiteBase.cs" />
62
63
64
65
66
67
68

69
70
71
72
73
74
75
    </Compile>
    <Compile Include="SQLiteConnectionStringBuilder.cs" />
    <Compile Include="SQLiteConvert.cs" />
    <Compile Include="SQLiteDataAdapter.cs">
      <SubType>Component</SubType>
    </Compile>
    <Compile Include="SQLiteDataReader.cs" />

    <Compile Include="SQLiteException.cs" />
    <Compile Include="SQLiteFactory.cs" />
    <Compile Include="SQLiteFunction.cs" />
    <Compile Include="SQLiteFunctionAttribute.cs" />
    <Compile Include="SQLiteParameter.cs" />
    <Compile Include="SQLiteParameterCollection.cs" />
    <Compile Include="SQLiteStatement.cs" />







>







63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
    </Compile>
    <Compile Include="SQLiteConnectionStringBuilder.cs" />
    <Compile Include="SQLiteConvert.cs" />
    <Compile Include="SQLiteDataAdapter.cs">
      <SubType>Component</SubType>
    </Compile>
    <Compile Include="SQLiteDataReader.cs" />
    <Compile Include="SQLiteEnlistment.cs" />
    <Compile Include="SQLiteException.cs" />
    <Compile Include="SQLiteFactory.cs" />
    <Compile Include="SQLiteFunction.cs" />
    <Compile Include="SQLiteFunctionAttribute.cs" />
    <Compile Include="SQLiteParameter.cs" />
    <Compile Include="SQLiteParameterCollection.cs" />
    <Compile Include="SQLiteStatement.cs" />