- NullPool is now used by default for SQLite file-

 based databases.  :memory: databases will continue
 to select SingletonThreadPool by default.
 [ticket:1921]

4 files changed, 46 insertions, 34 deletions

diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlite.py b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
index b2295f49b..fa43262e2 100644
--- a/lib/sqlalchemy/dialects/sqlite/pysqlite.py
+++ b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
@@ -87,7 +87,7 @@ processing. Execution of "func.current_date()" will return a string.
 "func.current_timestamp()" is registered as returning a DATETIME type in
 SQLAlchemy, so this function still receives SQLAlchemy-level result processing.
 
-Threading Behavior
+Pooling Behavior
 ------------------
 
 Pysqlite connections do not support being moved between threads, unless
@@ -100,26 +100,22 @@ application **cannot** share data from a ``:memory:`` database across threads
 unless access to the connection is limited to a single worker thread which communicates
 through a queueing mechanism to concurrent threads.
 
-To provide a default which accomodates SQLite's default threading capabilities
-somewhat reasonably, the SQLite dialect will specify that the :class:`~sqlalchemy.pool.SingletonThreadPool`
-be used by default.  This pool maintains a single SQLite connection per thread
-that is held open up to a count of five concurrent threads.  When more than five threads
-are used, a cleanup mechanism will dispose of excess unused connections.   
-
-Two optional pool implementations that may be appropriate for particular SQLite usage scenarios:
-
- * the :class:`sqlalchemy.pool.StaticPool` might be appropriate for a multithreaded
-   application using an in-memory database, assuming the threading issues inherent in 
-   pysqlite are somehow accomodated for.  This pool holds persistently onto a single connection
-   which is never closed, and is returned for all requests.
+To provide for these two behaviors, the pysqlite dialect will select a :class:`.Pool`
+implementation suitable:
+
+* When a ``:memory:`` SQLite database is specified, the dialect will use :class:`.SingletonThreadPool`.
+  This pool maintains a single connection per thread, so that all access to the engine within
+  the current thread use the same ``:memory:`` database.
+* When a file-based database is specified, the dialect will use :class:`.NullPool` as the source 
+  of connections.  This pool closes and discards connections which are returned to the pool immediately.
+  SQLite file-based connections have extermely low overhead, so pooling is not necessary.
+  The scheme also prevents a connection from being used again in a different thread
+  and works best with SQLite's coarse-grained file locking.
    
- * the :class:`sqlalchemy.pool.NullPool` might be appropriate for an application that
-   makes use of a file-based sqlite database.  This pool disables any actual "pooling"
-   behavior, and simply opens and closes real connections corresonding to the :func:`connect()`
-   and :func:`close()` methods.  SQLite can "connect" to a particular file with very high 
-   efficiency, so this option may actually perform better without the extra overhead
-   of :class:`SingletonThreadPool`.  NullPool will of course render a ``:memory:`` connection
-   useless since the database would be lost as soon as the connection is "returned" to the pool.
+  .. note:: The default selection of :class:`.NullPool` for SQLite file-based databases 
+              is new in SQLAlchemy 0.7. Previous versions
+              select :class:`.SingletonThreadPool` by
+              default for all SQLite databases.
 
 Unicode
 -------
@@ -171,7 +167,6 @@ class _SQLite_pysqliteDate(DATE):
 
 class SQLiteDialect_pysqlite(SQLiteDialect):
     default_paramstyle = 'qmark'
-    poolclass = pool.SingletonThreadPool
 
     colspecs = util.update_copy(
         SQLiteDialect.colspecs,
@@ -209,6 +204,13 @@ class SQLiteDialect_pysqlite(SQLiteDialect):
                 raise e
         return sqlite
 
+    @classmethod
+    def get_pool_class(cls, url):
+        if url.database and url.database != ':memory:':
+            return pool.NullPool
+        else:
+            return pool.SingletonThreadPool
+
     def _get_server_version_info(self, connection):
         return self.dbapi.sqlite_version_info
 
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index 13755d49a..587dbf92f 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -15,7 +15,7 @@ as the base class for their own corresponding classes.
 import re, random
 from sqlalchemy.engine import base, reflection
 from sqlalchemy.sql import compiler, expression
-from sqlalchemy import exc, types as sqltypes, util
+from sqlalchemy import exc, types as sqltypes, util, pool
 
 AUTOCOMMIT_REGEXP = re.compile(
             r'\s*(?:UPDATE|INSERT|CREATE|DELETE|DROP|ALTER)',
@@ -143,6 +143,10 @@ class DefaultDialect(base.Dialect):
     @property
     def dialect_description(self):
         return self.name + "+" + self.driver
+    
+    @classmethod
+    def get_pool_class(cls, url):
+        return getattr(cls, 'poolclass', pool.QueuePool)
         
     def initialize(self, connection):
         try:
diff --git a/lib/sqlalchemy/engine/strategies.py b/lib/sqlalchemy/engine/strategies.py
index fe0abd4b7..8de29271b 100644
--- a/lib/sqlalchemy/engine/strategies.py
+++ b/lib/sqlalchemy/engine/strategies.py
@@ -37,8 +37,6 @@ class EngineStrategy(object):
 class DefaultEngineStrategy(EngineStrategy):
     """Base class for built-in stratgies."""
 
-    pool_threadlocal = False
-    
     def create(self, name_or_url, **kwargs):
         # create url.URL object
         u = url.make_url(name_or_url)
@@ -84,8 +82,9 @@ class DefaultEngineStrategy(EngineStrategy):
                     
             creator = kwargs.pop('creator', connect)
 
-            poolclass = (kwargs.pop('poolclass', None) or
-                         getattr(dialect_cls, 'poolclass', poollib.QueuePool))
+            poolclass = kwargs.pop('poolclass', None)
+            if poolclass is None:
+                poolclass = dialect_cls.get_pool_class(u)
             pool_args = {}
 
             # consume pool arguments from kwargs, translating a few of
@@ -100,7 +99,6 @@ class DefaultEngineStrategy(EngineStrategy):
                 tk = translate.get(k, k)
                 if tk in kwargs:
                     pool_args[k] = kwargs.pop(tk)
-            pool_args.setdefault('use_threadlocal', self.pool_threadlocal)
             pool = poolclass(creator, **pool_args)
         else:
             if isinstance(pool, poollib._DBProxy):
@@ -163,7 +161,6 @@ class ThreadLocalEngineStrategy(DefaultEngineStrategy):
     """Strategy for configuring an Engine with thredlocal behavior."""
     
     name = 'threadlocal'
-    pool_threadlocal = True
     engine_cls = threadlocal.TLEngine
 
 ThreadLocalEngineStrategy()
diff --git a/lib/sqlalchemy/pool.py b/lib/sqlalchemy/pool.py
index 280292908..e01bc00de 100644
--- a/lib/sqlalchemy/pool.py
+++ b/lib/sqlalchemy/pool.py
@@ -529,15 +529,15 @@ class SingletonThreadPool(Pool):
     Maintains one connection per each thread, never moving a connection to a
     thread other than the one which it was created in.
 
-    This is used for SQLite, which both does not handle multithreading by
-    default, and also requires a singleton connection if a :memory: database
-    is being used.
-
     Options are the same as those of :class:`Pool`, as well as:
 
     :param pool_size: The number of threads in which to maintain connections 
         at once.  Defaults to five.
-      
+
+    :class:`.SingletonThreadPool` is used by the SQLite dialect
+    automatically when a memory-based database is used.  
+    See :ref:`sqlite_toplevel`.
+
     """
 
     def __init__(self, creator, pool_size=5, **kw):
@@ -604,7 +604,12 @@ class SingletonThreadPool(Pool):
         return c
 
 class QueuePool(Pool):
-    """A Pool that imposes a limit on the number of open connections."""
+    """A :class:`Pool` that imposes a limit on the number of open connections.
+    
+    :class:`.QueuePool` is the default pooling implementation used for 
+    all :class:`.Engine` objects, unless the SQLite dialect is in use.
+    
+    """
 
     def __init__(self, creator, pool_size=5, max_overflow=10, timeout=30,
                  **kw):
@@ -776,6 +781,10 @@ class NullPool(Pool):
     Reconnect-related functions such as ``recycle`` and connection
     invalidation are not supported by this Pool implementation, since
     no connections are held persistently.
+    
+    :class:`.NullPool` is used by the SQlite dilalect automatically
+    when a file-based database is used (as of SQLAlchemy 0.7).   
+    See :ref:`sqlite_toplevel`.
 
     """