Allow reuse of SQLite3ConnectionFactory across multiple threads

[sujitpal]

Hi,

I am using SpringPython to develop a webapp with an embedded SQLite3 database. Here are the details of my environment: The initial code for the app was generated by coily.

OS: Mac OS X (Snow Leopard)
Python: 2.6.1
SpringPython: 1.1.0-RC1
SQLite3: 3.6.20

In the app_context.py, I have defined a dataSource as follows:

@Object
def dataSource(self):
..return Sqlite3ConnectionFactory("/path/to/myapp.db")

This is being injected into various classes, which then create a DatabaseTemplate and do SQL operations on it. The problem is (was) that since this is a web application, the connection is created by the container thread, but is being used from different request threads, which SQLite did not like, and would throw an exception saying that the connection was created in Thread X but was being used from Thread Y.

Based on the information on this page:
http://stackoverflow.com/questions/3...nd-concurrency

I changed the Sqlite3ConnectionFactory connect method to add the check_same_thread=False argument, and since then the above code is running great without complaints (I initially attempted to work around the problem by scoping the dataSource to PROTOTYPE, but that would error out with the same complaint as above once the max number of connections were consumed).

My local Sqlite3ConnectionFactory.connect method looks like this:

def connect(self):
..try:
....import sqlite3
....return sqlite3.connect(self.db, check_same_thread=False)
..except:
....import sqlite
....self.using_sqlite3 = False
....return sqlite.connect(self.db, check_same_thread=False)

This may also only be a Mac OS X thing, going by the statement:
"On some operating systems, a database connection should always be used in the same thread in which it was originally created. "
in this page: http://www.sqlite.org/cvstrac/wiki?p=MultiThreading

Would it make sense to update the SpringPython code to include this extra parameter?

Thanks
Sujit

[gregturn]

It certainly does. If this is a flag you need, it makes sense to let you inject through the factory.

[gregturn]

I just opened https://jira.springsource.org/browse...NGPYTHONPY-152 to track this feature improvement. Its targeted for the new Spring Python 1.2 release.

[sujitpal]

Thanks Greg, much appreciated.

[gregturn]

I coded a patch. See if this looks okay to you:

(sp)gturnquist-mbp:spring-python gturnquist$ git diff
diff --git a/src/springpython/database/factory.py b/src/springpython/database/factory.py
index 53e5aff..6e171bc 100644
--- a/src/springpython/database/factory.py
+++ b/src/springpython/database/factory.py
@@ -92,20 +92,21 @@ class PgdbConnectionFactory(ConnectionFactory):
         return types.LongType
 
 class Sqlite3ConnectionFactory(ConnectionFactory):
-    def __init__(self, db = None):
+    def __init__(self, db = None, check_same_thread=True):
         ConnectionFactory.__init__(self, [types.TupleType])
         self.db = db
+        self.check_same_thread = check_same_thread
         self.using_sqlite3 = True
 
     def connect(self):
         """The import statement is delayed so the library is loaded ONLY if this factory is really used."""
         try:
             import sqlite3
-            return sqlite3.connect(self.db)               
+            return sqlite3.connect(self.db, check_same_thread=self.check_same_thread)               
         except:
             import sqlite
             self.using_sqlite3 = False
-            return sqlite.connect(self.db)
+            return sqlite.connect(self.db, check_same_thread=self.check_same_thread)
 
     def in_transaction(self):
         return True
diff --git a/test/springpythontest/databaseCoreTestCases.py b/test/springpythontest/databaseCoreTestCases.py
index 66a309a..c27c25d 100644
--- a/test/springpythontest/databaseCoreTestCases.py
+++ b/test/springpythontest/databaseCoreTestCases.py
@@ -62,6 +62,15 @@ class ConnectionFactoryTestCase(MockTestCase):
 
         del(sys.modules["sqlite3"])
 
+    def testConnectingToSqliteWithSpecialCheck(self):
+        sys.modules["sqlite3"] = self.mock()
+        sys.modules["sqlite3"].expects(once()).method("connect")
+
+        connection_factory = factory.Sqlite3ConnectionFactory(db="/tmp/foobar", check_same_thread=False)
+        #connection_factory = factory.Sqlite3ConnectionFactory(db="/tmp/foobar")
+        connection = connection_factory.connect()
+
+        del(sys.modules["sqlite3"])
 
     def testConnectingToOracle(self):
         sys.modules["cx_Oracle"] = self.mock()

[gregturn]

Issue is closed and coded on the master branch (version 1.2), ready for the next release.

[sujitpal]

Yes, thanks, this should work fine.