System.Data.SQLite
Check-in [44b92245a6]
Not logged in

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

Overview
Comment:Add initial draft of the custom connection pool feature.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | customPool
Files: files | file ages | folders
SHA1: 44b92245a6037ad146db6b392d7a16b0ffce554f
User & Date: mistachkin 2013-02-15 08:36:02
Context
2013-02-16
00:23
Add custom connection pool changes to the version history docs. Pursuant to [393d954be0]. check-in: abc8fb3a6f user: mistachkin tags: customPool
2013-02-15
08:36
Add initial draft of the custom connection pool feature. check-in: 44b92245a6 user: mistachkin tags: customPool
2013-02-13
21:59
Add doc wording to clarify that the connection pool will not work properly in WPF applications. Pursuant to [393d954be0]. check-in: dabfe38a9e user: mistachkin tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

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

195
196
197
198
199
200
201

202
203
204
205
206
207
208
209
210
211
212
...
634
635
636
637
638
639
640






































641
642
643
644
645
646
647
....
1173
1174
1175
1176
1177
1178
1179

1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
  /// <description>Y</description>
  /// </item>
  /// <item>
  /// <description>Pooling</description>
  /// <description>
  /// <b>True</b> - Use connection pooling.<br/>
  /// <b>False</b> - Do not use connection pooling.<br/><br/>

  /// <b>WARNING:</b> Setting this property to True should be avoided by
  /// applications that make use of WPF (either directly or indirectly) due
  /// to possible deadlocks that can occur during the finalization of some
  /// WPF objects.
  /// </description>
  /// <description>N</description>
  /// <description>False</description>
  /// </item>
  /// <item>
  /// <description>FailIfMissing</description>
  /// <description><b>True</b> - Don't create the database if it does not exist, throw an error instead<br/><b>False</b> - Automatically create the database if it does not exist</description>
................................................................................
        {
            lock (_syncRoot)
            {
                _handlers -= value;
            }
        }
    }







































    ///////////////////////////////////////////////////////////////////////////////////////////////

    #region Backup API Members
    /// <summary>
    /// Backs up the database, using the specified database connection as the
    /// destination.
................................................................................
    /// <description>Y</description>
    /// </item>
    /// <item>
    /// <description>Pooling</description>
    /// <description>
    /// <b>True</b> - Use connection pooling.<br/>
    /// <b>False</b> - Do not use connection pooling.<br/><br/>

    /// <b>WARNING:</b> Setting this property to True should be avoided by
    /// applications that make use of WPF (either directly or indirectly) due
    /// to possible deadlocks that can occur during the finalization of some
    /// WPF objects.
    /// </description>
    /// <description>N</description>
    /// <description>False</description>
    /// </item>
    /// <item>
    /// <description>FailIfMissing</description>
    /// <description><b>True</b> - Don't create the database if it does not exist, throw an error instead<br/><b>False</b> - Automatically create the database if it does not exist</description>







>
|
|
|
<







 







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







 







>
|
|
|
<







195
196
197
198
199
200
201
202
203
204
205

206
207
208
209
210
211
212
...
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
....
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221

1222
1223
1224
1225
1226
1227
1228
  /// <description>Y</description>
  /// </item>
  /// <item>
  /// <description>Pooling</description>
  /// <description>
  /// <b>True</b> - Use connection pooling.<br/>
  /// <b>False</b> - Do not use connection pooling.<br/><br/>
  /// <b>WARNING:</b> When using the default connection pool implementation,
  /// setting this property to True should be avoided by applications that make
  /// use of COM (either directly or indirectly) due to possible deadlocks that
  /// can occur during the finalization of some COM objects.

  /// </description>
  /// <description>N</description>
  /// <description>False</description>
  /// </item>
  /// <item>
  /// <description>FailIfMissing</description>
  /// <description><b>True</b> - Don't create the database if it does not exist, throw an error instead<br/><b>False</b> - Automatically create the database if it does not exist</description>
................................................................................
        {
            lock (_syncRoot)
            {
                _handlers -= value;
            }
        }
    }

    ///////////////////////////////////////////////////////////////////////////////////////////////

    /// <summary>
    /// This property is used to obtain or set the custom connection pool
    /// implementation to use, if any.  Setting this property to null will
    /// cause the default connection pool implementation to be used.
    /// </summary>
    public static ISQLiteConnectionPool ConnectionPool
    {
        get { return SQLiteConnectionPool.GetConnectionPool(); }
        set { SQLiteConnectionPool.SetConnectionPool(value); }
    }

    ///////////////////////////////////////////////////////////////////////////////////////////////

    /// <summary>
    /// Creates and returns a new managed database connection handle.  This
    /// method is intended to be used by implementations of the
    /// <see cref="ISQLiteConnectionPool" /> interface only.  In theory, it
    /// could be used by other classes; however, that usage is not supported.
    /// </summary>
    /// <param name="nativeHandle">
    /// This must be a native database connection handle returned by the
    /// SQLite core library and it must remain valid and open during the
    /// entire duration of the calling method.
    /// </param>
    /// <returns>
    /// The new managed database connection handle or null if it cannot be
    /// created.
    /// </returns>
    public static object CreateHandle(
        IntPtr nativeHandle
        )
    {
        if (nativeHandle == IntPtr.Zero) return null;
        return new SQLiteConnectionHandle(nativeHandle);
    }

    ///////////////////////////////////////////////////////////////////////////////////////////////

    #region Backup API Members
    /// <summary>
    /// Backs up the database, using the specified database connection as the
    /// destination.
................................................................................
    /// <description>Y</description>
    /// </item>
    /// <item>
    /// <description>Pooling</description>
    /// <description>
    /// <b>True</b> - Use connection pooling.<br/>
    /// <b>False</b> - Do not use connection pooling.<br/><br/>
    /// <b>WARNING:</b> When using the default connection pool implementation,
    /// setting this property to True should be avoided by applications that
    /// make use of COM (either directly or indirectly) due to possible
    /// deadlocks that can occur during the finalization of some COM objects.

    /// </description>
    /// <description>N</description>
    /// <description>False</description>
    /// </item>
    /// <item>
    /// <description>FailIfMissing</description>
    /// <description><b>True</b> - Don't create the database if it does not exist, throw an error instead<br/><b>False</b> - Automatically create the database if it does not exist</description>

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

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
82
83
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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
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
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415











































































































































































































































































































































































 * Written by Robert Simpson (robert@blackcastlesoft.com)
 * 
 * Released to the public domain, use at your own risk!
 ********************************************************/

namespace System.Data.SQLite
{
  using System;
  using System.Collections.Generic;
  using System.Threading;

  /// <summary>
  /// This class should not be used by applications that make use of WPF
  /// (either directly or indirectly) due to possible deadlocks that can occur
  /// during finalization of some WPF objects.
  /// </summary>
  internal static class SQLiteConnectionPool
  {
    /// <summary>
    /// Keeps track of connections made on a specified file.  The PoolVersion dictates whether old objects get
    /// returned to the pool or discarded when no longer in use.
    /// </summary>
    internal class Pool
    {
      internal readonly Queue<WeakReference> Queue = new Queue<WeakReference>();
      internal int PoolVersion;
      internal int MaxPoolSize;

      internal Pool(int version, int maxSize)
      {
        PoolVersion = version;
        MaxPoolSize = maxSize;
      }
    }

    /// <summary>
    /// The connection pool object
    /// </summary>
    private static SortedList<string, Pool> _connections = new SortedList<string, Pool>(StringComparer.OrdinalIgnoreCase);

    /// <summary>
    /// The default version number new pools will get
    /// </summary>
    private static int _poolVersion = 1;

    /// <summary>
    /// The number of connections successfully opened from any pool.
    /// This value is incremented by the Remove method.
    /// </summary>
    private static int _poolOpened = 0;

    /// <summary>
    /// The number of connections successfully closed from any pool.
    /// This value is incremented by the Add method.
    /// </summary>
    private static int _poolClosed = 0;

    /// <summary>
    /// Counts the number of pool entries matching the specified file name.
    /// </summary>
    /// <param name="fileName">The file name to match or null to match all files.</param>
    /// <param name="counts">The pool entry counts for each matching file.</param>
    /// <param name="openCount">The total number of connections successfully opened from any pool.</param>
    /// <param name="closeCount">The total number of connections successfully closed from any pool.</param>
    /// <param name="totalCount">The total number of pool entries for all matching files.</param>
    internal static void GetCounts(
        string fileName,
        ref Dictionary<string, int> counts,
        ref int openCount,
        ref int closeCount,
        ref int totalCount
        )
    {
        lock (_connections)
        {
            openCount = _poolOpened;
            closeCount = _poolClosed;

            if (counts == null)
            {
                counts = new Dictionary<string, int>(
                    StringComparer.OrdinalIgnoreCase);
            }

            if (fileName != null)
            {
                Pool queue;

                if (_connections.TryGetValue(fileName, out queue))
                {
                    Queue<WeakReference> poolQueue = queue.Queue;
                    int count = (poolQueue != null) ? poolQueue.Count : 0;

                    counts.Add(fileName, count);
                    totalCount += count;
                }
            }
            else
            {
                foreach (KeyValuePair<string, Pool> pair in _connections)
                {
                    if (pair.Value == null)
                        continue;

                    Queue<WeakReference> poolQueue = pair.Value.Queue;
                    int count = (poolQueue != null) ? poolQueue.Count : 0;

                    counts.Add(pair.Key, count);
                    totalCount += count;
                }
            }
        }
    }

    /// <summary>
    /// Attempt to pull a pooled connection out of the queue for active duty
    /// </summary>
    /// <param name="fileName">The filename for a desired connection</param>
    /// <param name="maxPoolSize">The maximum size the connection pool for the filename can be</param>
    /// <param name="version">The pool version the returned connection will belong to</param>
    /// <returns>Returns NULL if no connections were available.  Even if none are, the poolversion will still be a valid pool version</returns>
    internal static SQLiteConnectionHandle Remove(string fileName, int maxPoolSize, out int version)
    {
        int localVersion;
        Queue<WeakReference> poolQueue;

        //
        // NOTE: This lock cannot be held while checking the queue for available
        //       connections because other methods of this class are called from
        //       the GC finalizer thread and we use the WaitForPendingFinalizers
        //       method (below).  Holding this lock while calling that method
        //       would therefore result in a deadlock.  Instead, this lock is
        //       held only while a temporary copy of the queue is created, and
        //       if necessary, when committing changes back to that original
        //       queue prior to returning from this method.
        //
        lock (_connections)
        {
            Pool queue;

            // Default to the highest pool version
            version = _poolVersion;

            // If we didn't find a pool for this file, create one even though it will be empty.
            // We have to do this here because otherwise calling ClearPool() on the file will not work for active connections
            // that have never seen the pool yet.
            if (_connections.TryGetValue(fileName, out queue) == false)
            {
                queue = new Pool(_poolVersion, maxPoolSize);
                _connections.Add(fileName, queue);

                return null;
            }

            // We found a pool for this file, so use its version number
            version = localVersion = queue.PoolVersion;
            queue.MaxPoolSize = maxPoolSize;

            ResizePool(queue, false);

            // Try and get a pooled connection from the queue
            poolQueue = queue.Queue;
            if (poolQueue == null) return null;

            //
            // NOTE: Temporarily tranfer the queue for this file into a local
            //       variable.  The queue for this file will be modified and
            //       then committed back to the real pool list (below) prior
            //       to returning from this method.
            //
            _connections.Remove(fileName);
            poolQueue = new Queue<WeakReference>(poolQueue);
        }

        try
        {
            while (poolQueue.Count > 0)
            {
                WeakReference cnn = poolQueue.Dequeue();
                if (cnn == null) continue;

                SQLiteConnectionHandle hdl = cnn.Target as SQLiteConnectionHandle;
                if (hdl == null) continue;

                //
                // BUGFIX: For ticket [996d13cd87], step #1.  After this point,
                //         make sure that the finalizer for the connection
                //         handle just obtained from the queue cannot START
                //         running (i.e. it may still be pending but it will no
                //         longer start after this point).
                //
                GC.SuppressFinalize(hdl);

                try
                {
                    //
                    // BUGFIX: For ticket [996d13cd87], step #2.  Now, we must wait
                    //         for all pending finalizers which have STARTED running
                    //         and have not yet COMPLETED.  This must be done just
                    //         in case the finalizer for the connection handle just
                    //         obtained from the queue has STARTED running at some
                    //         point before SuppressFinalize was called on it.
                    //
                    //         After this point, checking properties of the
                    //         connection handle (e.g. IsClosed) should work
                    //         reliably without having to worry that they will
                    //         (due to the finalizer) change out from under us.
                    //
                    GC.WaitForPendingFinalizers();

                    //
                    // BUGFIX: For ticket [996d13cd87], step #3.  Next, verify that
                    //         the connection handle is actually valid and [still?]
                    //         not closed prior to actually returning it to our
                    //         caller.
                    //
                    if (!hdl.IsInvalid && !hdl.IsClosed)
                    {
                        Interlocked.Increment(ref _poolOpened);
                        return hdl;
                    }
                }
                finally
                {
                    //
                    // BUGFIX: For ticket [996d13cd87], step #4.  Next, we must
                    //         re-register the connection handle for finalization
                    //         now that we have a strong reference to it (i.e. the
                    //         finalizer will not run at least until the connection
                    //         is subsequently closed).
                    //
                    GC.ReRegisterForFinalize(hdl);
                }

                GC.KeepAlive(hdl);
            }
        }
        finally
        {
            //
            // BUGFIX: For ticket [996d13cd87], step #5.  Finally, commit any
            //         changes to the pool/queue for this database file.
            //
            lock (_connections)
            {
                //
                // NOTE: We must check [again] if a pool exists for this file
                //       because one may have been added while the search for
                //       an available connection was in progress (above).
                //
                Pool queue;
                Queue<WeakReference> newPoolQueue;
                bool addPool;

                if (_connections.TryGetValue(fileName, out queue))
                {
                    addPool = false;
                }
                else
                {
                    addPool = true;
                    queue = new Pool(localVersion, maxPoolSize);
                }

                newPoolQueue = queue.Queue;

                while (poolQueue.Count > 0)
                    newPoolQueue.Enqueue(poolQueue.Dequeue());

                ResizePool(queue, false);

                if (addPool)
                    _connections.Add(fileName, queue);
            }
        }

        return null;
    }

    /// <summary>
    /// Clears out all pooled connections and rev's up the default pool version to force all old active objects
    /// not in the pool to get discarded rather than returned to their pools.
    /// </summary>
    internal static void ClearAllPools()
    {
      lock (_connections)
      {
        foreach (KeyValuePair<string, Pool> pair in _connections)
        {
          if (pair.Value == null)
            continue;

          Queue<WeakReference> poolQueue = pair.Value.Queue;

          while (poolQueue.Count > 0)
          {
            WeakReference cnn = poolQueue.Dequeue();
            if (cnn == null) continue;
            SQLiteConnectionHandle hdl = cnn.Target as SQLiteConnectionHandle;
            if (hdl != null)
            {
              hdl.Dispose();
            }
            GC.KeepAlive(hdl);
          }
          
          // Keep track of the highest revision so we can go one higher when we're finished
          if (_poolVersion <= pair.Value.PoolVersion)
            _poolVersion = pair.Value.PoolVersion + 1;
        }
        // All pools are cleared and we have a new highest version number to force all old version active items to get discarded
        // instead of going back to the queue when they are closed.
        // We can get away with this because we've pumped up the _poolVersion out of range of all active connections, so they
        // will all get discarded when they try to put themselves back in their pool.
        _connections.Clear();
      }
    }

    /// <summary>
    /// Clear a given pool for a given filename.  Discards anything in the pool for the given file, and revs the pool
    /// version so current active objects on the old version of the pool will get discarded rather than be returned to the pool.
    /// </summary>
    /// <param name="fileName">The filename of the pool to clear</param>
    internal static void ClearPool(string fileName)
    {
      lock (_connections)
      {
        Pool queue;
        if (_connections.TryGetValue(fileName, out queue) == true)
        {
          queue.PoolVersion++;

          Queue<WeakReference> poolQueue = queue.Queue;
          if (poolQueue == null) return;

          while (poolQueue.Count > 0)
          {
            WeakReference cnn = poolQueue.Dequeue();
            if (cnn == null) continue;
            SQLiteConnectionHandle hdl = cnn.Target as SQLiteConnectionHandle;
            if (hdl != null)
            {
              hdl.Dispose();
            }
            GC.KeepAlive(hdl);
          }
        }
      }
    }

    /// <summary>
    /// Return a connection to the pool for someone else to use.
    /// </summary>
    /// <param name="fileName">The filename of the pool to use</param>
    /// <param name="hdl">The connection handle to pool</param>
    /// <param name="version">The pool version the handle was created under</param>
    /// <remarks>
    /// If the version numbers don't match between the connection and the pool, then the handle is discarded.
    /// </remarks>
    internal static void Add(string fileName, SQLiteConnectionHandle hdl, int version)
    {
      lock (_connections)
      {
        // If the queue doesn't exist in the pool, then it must've been cleared sometime after the connection was created.
        Pool queue;
        if (_connections.TryGetValue(fileName, out queue) == true && version == queue.PoolVersion)
        {
          ResizePool(queue, true);

          Queue<WeakReference> poolQueue = queue.Queue;
          if (poolQueue == null) return;

          poolQueue.Enqueue(new WeakReference(hdl, false));
          Interlocked.Increment(ref _poolClosed);
        }
        else
        {
          hdl.Close();
        }
        GC.KeepAlive(hdl);
      }
    }

    /// <summary>
    /// We don't have to thread-lock anything in this function, because it's only called by other functions above
    /// which already have a thread-safe lock.
    /// </summary>
    /// <param name="queue">The queue to resize</param>
    /// <param name="forAdding">If a function intends to add to the pool, this is true, which forces the resize
    /// to take one more than it needs from the pool</param>
    private static void ResizePool(Pool queue, bool forAdding)
    {
      int target = queue.MaxPoolSize;

      if (forAdding && target > 0) target--;

      Queue<WeakReference> poolQueue = queue.Queue;
      if (poolQueue == null) return;

      while (poolQueue.Count > target)
      {
        WeakReference cnn = poolQueue.Dequeue();
        if (cnn == null) continue;
        SQLiteConnectionHandle hdl = cnn.Target as SQLiteConnectionHandle;
        if (hdl != null)
        {
          hdl.Dispose();
        }
        GC.KeepAlive(hdl);
      }
    }
  }
}


















































































































































































































































































































































































|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
82
83
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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
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
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
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
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
 * Written by Robert Simpson (robert@blackcastlesoft.com)
 * 
 * Released to the public domain, use at your own risk!
 ********************************************************/

namespace System.Data.SQLite
{
    using System;
    using System.Collections.Generic;
    using System.Threading;

    ///////////////////////////////////////////////////////////////////////////

    #region Public Connection Pool Interface
    /// <summary>
    /// This interface represents a custom connection pool implementation
    /// usable by System.Data.SQLite.
    /// </summary>
    public interface ISQLiteConnectionPool
    {
        /// <summary>
        /// Counts the number of pool entries matching the specified file name.
        /// </summary>
        /// <param name="fileName">
        /// The file name to match or null to match all files.
        /// </param>
        /// <param name="counts">
        /// The pool entry counts for each matching file.
        /// </param>
        /// <param name="openCount">
        /// The total number of connections successfully opened from any pool.
        /// </param>
        /// <param name="closeCount">
        /// The total number of connections successfully closed from any pool.
        /// </param>
        /// <param name="totalCount">
        /// The total number of pool entries for all matching files.
        /// </param>
        void GetCounts(string fileName, ref Dictionary<string, int> counts,
            ref int openCount, ref int closeCount, ref int totalCount);

        /// <summary>
        /// Disposes of all pooled connections associated with the specified
        /// database file name.
        /// </summary>
        /// <param name="fileName">
        /// The database file name.
        /// </param>
        void ClearPool(string fileName);

        /// <summary>
        /// Disposes of all pooled connections.
        /// </summary>
        void ClearAllPools();

        /// <summary>
        /// Adds a connection to the pool of those associated with the
        /// specified database file name.
        /// </summary>
        /// <param name="fileName">
        /// The database file name.
        /// </param>
        /// <param name="handle">
        /// The database connection handle.
        /// </param>
        /// <param name="version">
        /// The connection pool version at the point the database connection
        /// handle was received from the connection pool.  This is also the
        /// connection pool version that the database connection handle was
        /// created under.
        /// </param>
        void Add(string fileName, object handle, int version);

        /// <summary>
        /// Removes a connection from the pool of those associated with the
        /// specified database file name with the intent of using it to
        /// interact with the database.
        /// </summary>
        /// <param name="fileName">
        /// The database file name.
        /// </param>
        /// <param name="maxPoolSize">
        /// The new maximum size of the connection pool for the specified
        /// database file name.
        /// </param>
        /// <param name="version">
        /// The connection pool version associated with the returned database
        /// connection handle, if any.
        /// </param>
        /// <returns>
        /// The database connection handle associated with the specified
        /// database file name or null if it cannot be obtained.
        /// </returns>
        object Remove(string fileName, int maxPoolSize, out int version);
    }
    #endregion

    ///////////////////////////////////////////////////////////////////////////

    /// <summary>
    /// This default method implementations in this class should not be used by
    /// applications that make use of COM (either directly or indirectly) due to
    /// possible deadlocks that can occur during finalization of some COM objects.
    /// </summary>
    internal static class SQLiteConnectionPool
    {
        #region Private Pool Class
        /// <summary>
        /// Keeps track of connections made on a specified file.  The PoolVersion
        /// dictates whether old objects get returned to the pool or discarded
        /// when no longer in use.
        /// </summary>
        private sealed class PoolQueue
        {
            #region Private Data
            /// <summary>
            /// The queue of weak references to the actual database connection
            /// handles.
            /// </summary>
            internal readonly Queue<WeakReference> Queue =
                new Queue<WeakReference>();

            ///////////////////////////////////////////////////////////////////

            /// <summary>
            /// This pool version associated with the database connection
            /// handles in this pool queue.
            /// </summary>
            internal int PoolVersion;

            ///////////////////////////////////////////////////////////////////

            /// <summary>
            /// The maximum size of this pool queue.
            /// </summary>
            internal int MaxPoolSize;
            #endregion

            ///////////////////////////////////////////////////////////////////

            #region Private Constructors
            /// <summary>
            /// Constructs a connection pool queue using the specified version
            /// and maximum size.  Normally, all the database connection
            /// handles in this pool are associated with a single database file
            /// name.
            /// </summary>
            /// <param name="version">
            /// The initial pool version for this connection pool queue.
            /// </param>
            /// <param name="maxSize">
            /// The initial maximum size for this connection pool queue.
            /// </param>
            internal PoolQueue(
                int version,
                int maxSize
                )
            {
                PoolVersion = version;
                MaxPoolSize = maxSize;
            }
            #endregion
        }
        #endregion

        ///////////////////////////////////////////////////////////////////////

        #region Private Static Data
        /// <summary>
        /// This field is used to synchronize access to the private static data
        /// in this class.
        /// </summary>
        private static readonly object _syncRoot = new object();

        ///////////////////////////////////////////////////////////////////////

        /// <summary>
        /// When this field is non-null, it will be used to provide the
        /// implementation of all the connection pool methods; otherwise,
        /// the default method implementations will be used.
        /// </summary>
        private static ISQLiteConnectionPool _connectionPool = null;

        ///////////////////////////////////////////////////////////////////////

        /// <summary>
        /// The dictionary of connection pools, based on the normalized file
        /// name of the SQLite database.
        /// </summary>
        private static SortedList<string, PoolQueue> _queueList =
            new SortedList<string, PoolQueue>(StringComparer.OrdinalIgnoreCase);

        ///////////////////////////////////////////////////////////////////////

        /// <summary>
        /// The default version number new pools will get.
        /// </summary>
        private static int _poolVersion = 1;

        ///////////////////////////////////////////////////////////////////////

        /// <summary>
        /// The number of connections successfully opened from any pool.
        /// This value is incremented by the Remove method.
        /// </summary>
        private static int _poolOpened = 0;

        ///////////////////////////////////////////////////////////////////////

        /// <summary>
        /// The number of connections successfully closed from any pool.
        /// This value is incremented by the Add method.
        /// </summary>
        private static int _poolClosed = 0;
        #endregion

        ///////////////////////////////////////////////////////////////////////

        #region ISQLiteConnectionPool Members (Static, Non-Formal)
        /// <summary>
        /// Counts the number of pool entries matching the specified file name.
        /// </summary>
        /// <param name="fileName">
        /// The file name to match or null to match all files.
        /// </param>
        /// <param name="counts">
        /// The pool entry counts for each matching file.
        /// </param>
        /// <param name="openCount">
        /// The total number of connections successfully opened from any pool.
        /// </param>
        /// <param name="closeCount">
        /// The total number of connections successfully closed from any pool.
        /// </param>
        /// <param name="totalCount">
        /// The total number of pool entries for all matching files.
        /// </param>
        internal static void GetCounts(
            string fileName,
            ref Dictionary<string, int> counts,
            ref int openCount,
            ref int closeCount,
            ref int totalCount
            )
        {
            ISQLiteConnectionPool connectionPool = GetConnectionPool();

            if (connectionPool != null)
            {
                connectionPool.GetCounts(
                    fileName, ref counts, ref openCount, ref closeCount,
                    ref totalCount);
            }
            else
            {
                lock (_syncRoot)
                {
                    openCount = _poolOpened;
                    closeCount = _poolClosed;

                    if (counts == null)
                    {
                        counts = new Dictionary<string, int>(
                            StringComparer.OrdinalIgnoreCase);
                    }

                    if (fileName != null)
                    {
                        PoolQueue queue;

                        if (_queueList.TryGetValue(fileName, out queue))
                        {
                            Queue<WeakReference> poolQueue = queue.Queue;
                            int count = (poolQueue != null) ? poolQueue.Count : 0;

                            counts.Add(fileName, count);
                            totalCount += count;
                        }
                    }
                    else
                    {
                        foreach (KeyValuePair<string, PoolQueue> pair in _queueList)
                        {
                            if (pair.Value == null)
                                continue;

                            Queue<WeakReference> poolQueue = pair.Value.Queue;
                            int count = (poolQueue != null) ? poolQueue.Count : 0;

                            counts.Add(pair.Key, count);
                            totalCount += count;
                        }
                    }
                }
            }
        }

        ///////////////////////////////////////////////////////////////////////

        /// <summary>
        /// Disposes of all pooled connections associated with the specified
        /// database file name.
        /// </summary>
        /// <param name="fileName">
        /// The database file name.
        /// </param>
        internal static void ClearPool(string fileName)
        {
            ISQLiteConnectionPool connectionPool = GetConnectionPool();

            if (connectionPool != null)
            {
                connectionPool.ClearPool(fileName);
            }
            else
            {
                lock (_syncRoot)
                {
                    PoolQueue queue;

                    if (_queueList.TryGetValue(fileName, out queue))
                    {
                        queue.PoolVersion++;

                        Queue<WeakReference> poolQueue = queue.Queue;
                        if (poolQueue == null) return;

                        while (poolQueue.Count > 0)
                        {
                            WeakReference connection = poolQueue.Dequeue();

                            if (connection == null) continue;

                            SQLiteConnectionHandle handle =
                                connection.Target as SQLiteConnectionHandle;

                            if (handle != null)
                                handle.Dispose();

                            GC.KeepAlive(handle);
                        }
                    }
                }
            }
        }

        ///////////////////////////////////////////////////////////////////////

        /// <summary>
        /// Disposes of all pooled connections.
        /// </summary>
        internal static void ClearAllPools()
        {
            ISQLiteConnectionPool connectionPool = GetConnectionPool();

            if (connectionPool != null)
            {
                connectionPool.ClearAllPools();
            }
            else
            {
                lock (_syncRoot)
                {
                    foreach (KeyValuePair<string, PoolQueue> pair in _queueList)
                    {
                        if (pair.Value == null)
                            continue;

                        Queue<WeakReference> poolQueue = pair.Value.Queue;

                        while (poolQueue.Count > 0)
                        {
                            WeakReference connection = poolQueue.Dequeue();

                            if (connection == null) continue;

                            SQLiteConnectionHandle handle =
                                connection.Target as SQLiteConnectionHandle;

                            if (handle != null)
                                handle.Dispose();

                            GC.KeepAlive(handle);
                        }

                        //
                        // NOTE: Keep track of the highest revision so we can
                        //       go one higher when we are finished.
                        //
                        if (_poolVersion <= pair.Value.PoolVersion)
                            _poolVersion = pair.Value.PoolVersion + 1;
                    }

                    //
                    // NOTE: All pools are cleared and we have a new highest
                    //       version number to force all old version active
                    //       items to get discarded instead of going back to
                    //       the queue when they are closed.  We can get away
                    //       with this because we have pumped up the pool
                    //       version out of range of all active connections,
                    //       so they will all get discarded when they try to
                    //       put themselves back into their pools.
                    //
                    _queueList.Clear();
                }
            }
        }

        ///////////////////////////////////////////////////////////////////////

        /// <summary>
        /// Adds a connection to the pool of those associated with the
        /// specified database file name.
        /// </summary>
        /// <param name="fileName">
        /// The database file name.
        /// </param>
        /// <param name="handle">
        /// The database connection handle.
        /// </param>
        /// <param name="version">
        /// The connection pool version at the point the database connection
        /// handle was received from the connection pool.  This is also the
        /// connection pool version that the database connection handle was
        /// created under.
        /// </param>
        internal static void Add(
            string fileName,
            SQLiteConnectionHandle handle,
            int version
            )
        {
            ISQLiteConnectionPool connectionPool = GetConnectionPool();

            if (connectionPool != null)
            {
                connectionPool.Add(fileName, handle, version);
            }
            else
            {
                lock (_syncRoot)
                {
                    //
                    // NOTE: If the queue does not exist in the pool, then it
                    //       must have been cleared sometime after the
                    //       connection was created.
                    //
                    PoolQueue queue;

                    if (_queueList.TryGetValue(fileName, out queue) &&
                        (version == queue.PoolVersion))
                    {
                        ResizePool(queue, true);

                        Queue<WeakReference> poolQueue = queue.Queue;
                        if (poolQueue == null) return;

                        poolQueue.Enqueue(new WeakReference(handle, false));
                        Interlocked.Increment(ref _poolClosed);
                    }
                    else
                    {
                        handle.Close();
                    }

                    GC.KeepAlive(handle);
                }
            }
        }

        ///////////////////////////////////////////////////////////////////////

        /// <summary>
        /// Removes a connection from the pool of those associated with the
        /// specified database file name with the intent of using it to
        /// interact with the database.
        /// </summary>
        /// <param name="fileName">
        /// The database file name.
        /// </param>
        /// <param name="maxPoolSize">
        /// The new maximum size of the connection pool for the specified
        /// database file name.
        /// </param>
        /// <param name="version">
        /// The connection pool version associated with the returned database
        /// connection handle, if any.
        /// </param>
        /// <returns>
        /// The database connection handle associated with the specified
        /// database file name or null if it cannot be obtained.
        /// </returns>
        internal static SQLiteConnectionHandle Remove(
            string fileName,
            int maxPoolSize,
            out int version
            )
        {
            ISQLiteConnectionPool connectionPool = GetConnectionPool();

            if (connectionPool != null)
            {
                return connectionPool.Remove(fileName, maxPoolSize,
                    out version) as SQLiteConnectionHandle;
            }
            else
            {
                int localVersion;
                Queue<WeakReference> poolQueue;

                //
                // NOTE: This lock cannot be held while checking the queue for
                //       available connections because other methods of this
                //       class are called from the GC finalizer thread and we
                //       use the WaitForPendingFinalizers method (below).
                //       Holding this lock while calling that method would
                //       therefore result in a deadlock.  Instead, this lock
                //       is held only while a temporary copy of the queue is
                //       created, and if necessary, when committing changes
                //       back to that original queue prior to returning from
                //       this method.
                //
                lock (_syncRoot)
                {
                    PoolQueue queue;

                    //
                    // NOTE: Default to the highest pool version.
                    //
                    version = _poolVersion;

                    //
                    // NOTE: If we didn't find a pool for this file, create one
                    //       even though it will be empty.  We have to do this
                    //       here because otherwise calling ClearPool() on the
                    //       file will not work for active connections that have
                    //       never seen the pool yet.
                    //
                    if (!_queueList.TryGetValue(fileName, out queue))
                    {
                        queue = new PoolQueue(_poolVersion, maxPoolSize);
                        _queueList.Add(fileName, queue);

                        return null;
                    }

                    //
                    // NOTE: We found a pool for this file, so use its version
                    //       number.
                    //
                    version = localVersion = queue.PoolVersion;
                    queue.MaxPoolSize = maxPoolSize;

                    //
                    // NOTE: Now, resize the pool to the new maximum size, if
                    //       necessary.
                    //
                    ResizePool(queue, false);

                    //
                    // NOTE: Try and get a pooled connection from the queue.
                    //
                    poolQueue = queue.Queue;
                    if (poolQueue == null) return null;

                    //
                    // NOTE: Temporarily tranfer the queue for this file into
                    //       a local variable.  The queue for this file will
                    //       be modified and then committed back to the real
                    //       pool list (below) prior to returning from this
                    //       method.
                    //
                    _queueList.Remove(fileName);
                    poolQueue = new Queue<WeakReference>(poolQueue);
                }

                try
                {
                    while (poolQueue.Count > 0)
                    {
                        WeakReference connection = poolQueue.Dequeue();

                        if (connection == null) continue;

                        SQLiteConnectionHandle handle =
                            connection.Target as SQLiteConnectionHandle;

                        if (handle == null) continue;

                        //
                        // BUGFIX: For ticket [996d13cd87], step #1.  After
                        //         this point, make sure that the finalizer for
                        //         the connection handle just obtained from the
                        //         queue cannot START running (i.e. it may
                        //         still be pending but it will no longer start
                        //         after this point).
                        //
                        GC.SuppressFinalize(handle);

                        try
                        {
                            //
                            // BUGFIX: For ticket [996d13cd87], step #2.  Now,
                            //         we must wait for all pending finalizers
                            //         which have STARTED running and have not
                            //         yet COMPLETED.  This must be done just
                            //         in case the finalizer for the connection
                            //         handle just obtained from the queue has
                            //         STARTED running at some point before
                            //         SuppressFinalize was called on it.
                            //
                            //         After this point, checking properties of
                            //         the connection handle (e.g. IsClosed)
                            //         should work reliably without having to
                            //         worry that they will (due to the
                            //         finalizer) change out from under us.
                            //
                            GC.WaitForPendingFinalizers();

                            //
                            // BUGFIX: For ticket [996d13cd87], step #3.  Next,
                            //         verify that the connection handle is
                            //         actually valid and [still?] not closed
                            //         prior to actually returning it to our
                            //         caller.
                            //
                            if (!handle.IsInvalid && !handle.IsClosed)
                            {
                                Interlocked.Increment(ref _poolOpened);
                                return handle;
                            }
                        }
                        finally
                        {
                            //
                            // BUGFIX: For ticket [996d13cd87], step #4.  Next,
                            //         we must re-register the connection
                            //         handle for finalization now that we have
                            //         a strong reference to it (i.e. the
                            //         finalizer will not run at least until
                            //         the connection is subsequently closed).
                            //
                            GC.ReRegisterForFinalize(handle);
                        }

                        GC.KeepAlive(handle);
                    }
                }
                finally
                {
                    //
                    // BUGFIX: For ticket [996d13cd87], step #5.  Finally,
                    //         commit any changes to the pool/queue for this
                    //         database file.
                    //
                    lock (_syncRoot)
                    {
                        //
                        // NOTE: We must check [again] if a pool exists for
                        //       this file because one may have been added
                        //       while the search for an available connection
                        //       was in progress (above).
                        //
                        PoolQueue queue;
                        Queue<WeakReference> newPoolQueue;
                        bool addPool;

                        if (_queueList.TryGetValue(fileName, out queue))
                        {
                            addPool = false;
                        }
                        else
                        {
                            addPool = true;
                            queue = new PoolQueue(localVersion, maxPoolSize);
                        }

                        newPoolQueue = queue.Queue;

                        while (poolQueue.Count > 0)
                            newPoolQueue.Enqueue(poolQueue.Dequeue());

                        ResizePool(queue, false);

                        if (addPool)
                            _queueList.Add(fileName, queue);
                    }
                }

                return null;
            }
        }
        #endregion

        ///////////////////////////////////////////////////////////////////////

        #region Private Helper Methods
        /// <summary>
        /// This method is used to obtain a reference to the custom connection
        /// pool implementation currently in use, if any.
        /// </summary>
        /// <returns>
        /// The custom connection pool implementation or null if the default
        /// connection pool implementation should be used.
        /// </returns>
        internal static ISQLiteConnectionPool GetConnectionPool()
        {
            lock (_syncRoot)
            {
                return _connectionPool;
            }
        }

        ///////////////////////////////////////////////////////////////////////

        /// <summary>
        /// This method is used to set the reference to the custom connection
        /// pool implementation to use, if any.
        /// </summary>
        /// <param name="connectionPool">
        /// The custom connection pool implementation to use or null if the
        /// default connection pool implementation should be used.
        /// </param>
        internal static void SetConnectionPool(
            ISQLiteConnectionPool connectionPool
            )
        {
            lock (_syncRoot)
            {
                _connectionPool = connectionPool;
            }
        }

        ///////////////////////////////////////////////////////////////////////

        /// <summary>
        /// We do not have to thread-lock anything in this function, because it
        /// is only called by other functions above which already take the lock.
        /// </summary>
        /// <param name="queue">
        /// The pool queue to resize.
        /// </param>
        /// <param name="add">
        /// If a function intends to add to the pool, this is true, which
        /// forces the resize to take one more than it needs from the pool.
        /// </param>
        private static void ResizePool(
            PoolQueue queue,
            bool add
            )
        {
            int target = queue.MaxPoolSize;

            if (add && target > 0) target--;

            Queue<WeakReference> poolQueue = queue.Queue;
            if (poolQueue == null) return;

            while (poolQueue.Count > target)
            {
                WeakReference connection = poolQueue.Dequeue();

                if (connection == null) continue;

                SQLiteConnectionHandle handle =
                    connection.Target as SQLiteConnectionHandle;

                if (handle != null)
                    handle.Dispose();

                GC.KeepAlive(handle);
            }
        }
        #endregion
    }
}