- factor consistent set_isolation_level(), get_isolation_level()

per-connection methods for sqlite, postgresql, psycopg2 dialects
- move isolation test suite to test engines/test_transaction
- preparing for [ticket:2001]

3 files changed, 75 insertions, 30 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index a8fb4e51a..9097c3a6e 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -769,16 +769,36 @@ class PGDialect(default.DefaultDialect):
     def on_connect(self):
         if self.isolation_level is not None:
             def connect(conn):
-                cursor = conn.cursor()
-                cursor.execute(
-                    "SET SESSION CHARACTERISTICS AS TRANSACTION "
-                    "ISOLATION LEVEL %s" % self.isolation_level)
-                cursor.execute("COMMIT")
-                cursor.close()
+                self.set_isolation_level(conn, self.isolation_level)
             return connect
         else:
             return None
 
+    _isolation_lookup = set(['SERIALIZABLE', 
+                'READ UNCOMMITTED', 'READ COMMITTED', 'REPEATABLE READ'])
+
+    def set_isolation_level(self, connection, level):
+        level = level.replace('_', ' ')
+        if level not in self._isolation_lookup:
+            raise exc.ArgumentError(
+                "Invalid value '%s' for isolation_level. "
+                "Valid isolation levels for %s are %s" % 
+                (self.name, level, ", ".join(self._isolation_lookup))
+                ) 
+        cursor = connection.cursor()
+        cursor.execute(
+            "SET SESSION CHARACTERISTICS AS TRANSACTION "
+            "ISOLATION LEVEL %s" % level)
+        cursor.execute("COMMIT")
+        cursor.close()
+
+    def get_isolation_level(self, connection):
+        cursor = connection.cursor()
+        cursor.execute('show transaction isolation level')
+        val = cursor.fetchone()[0]
+        cursor.close()
+        return val.upper()
+
     def do_begin_twophase(self, connection, xid):
         self.do_begin(connection.connection)
 
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index 411bd42bd..806ba41f8 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -243,23 +243,32 @@ class PGDialect_psycopg2(PGDialect):
         psycopg = __import__('psycopg2')
         return psycopg
 
-    def on_connect(self):
-        if self.isolation_level is not None:
-            extensions = __import__('psycopg2.extensions').extensions
-            isol = {
+    @util.memoized_property
+    def _isolation_lookup(self):
+        extensions = __import__('psycopg2.extensions').extensions
+        return {
             'READ_COMMITTED':extensions.ISOLATION_LEVEL_READ_COMMITTED, 
             'READ_UNCOMMITTED':extensions.ISOLATION_LEVEL_READ_UNCOMMITTED, 
             'REPEATABLE_READ':extensions.ISOLATION_LEVEL_REPEATABLE_READ,
             'SERIALIZABLE':extensions.ISOLATION_LEVEL_SERIALIZABLE
+        }
 
-            }
+    def set_isolation_level(self, connection, level):
+        try:
+            level = self._isolation_lookup[level.replace(' ', '_')]
+        except KeyError:
+            raise exc.ArgumentError(
+                "Invalid value '%s' for isolation_level. "
+                "Valid isolation levels for %s are %s" % 
+                (self.name, level, ", ".join(self._isolation_lookup))
+                ) 
+
+        connection.set_isolation_level(level)
+
+    def on_connect(self):
+        if self.isolation_level is not None:
             def base_on_connect(conn):
-                try:
-                    conn.set_isolation_level(isol[self.isolation_level])
-                except:
-                    raise exc.InvalidRequestError(
-                                "Invalid isolation level: '%s'" % 
-                                self.isolation_level)
+                self.set_isolation_level(conn, self.isolation_level)
         else:
             base_on_connect = None
 
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index f732f1f44..9f1f64325 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -372,11 +372,6 @@ class SQLiteDialect(default.DefaultDialect):
 
     def __init__(self, isolation_level=None, native_datetime=False, **kwargs):
         default.DefaultDialect.__init__(self, **kwargs)
-        if isolation_level and isolation_level not in ('SERIALIZABLE',
-                'READ UNCOMMITTED'):
-            raise exc.ArgumentError("Invalid value for isolation_level. "
-                "Valid isolation levels for sqlite are 'SERIALIZABLE' and "
-                "'READ UNCOMMITTED'.")
         self.isolation_level = isolation_level
 
         # this flag used by pysqlite dialect, and perhaps others in the
@@ -391,18 +386,39 @@ class SQLiteDialect(default.DefaultDialect):
             self.supports_cast = \
                                 self.dbapi.sqlite_version_info >= (3, 2, 3)
 
+    _isolation_lookup = {
+        'READ UNCOMMITTED':1,
+        'SERIALIZABLE':0
+    }
+    def set_isolation_level(self, connection, level):
+        try:
+            isolation_level = self._isolation_lookup[level.replace('_', ' ')]
+        except KeyError:
+            raise exc.ArgumentError(
+                "Invalid value '%s' for isolation_level. "
+                "Valid isolation levels for %s are %s" % 
+                (self.name, level, ", ".join(self._isolation_lookup))
+                ) 
+        cursor = connection.cursor()
+        cursor.execute("PRAGMA read_uncommitted = %d" % isolation_level)
+        cursor.close()
+
+    def get_isolation_level(self, connection):
+        cursor = connection.cursor()
+        cursor.execute('PRAGMA read_uncommitted')
+        value = cursor.fetchone()[0]
+        cursor.close()
+        if value == 0:
+            return "SERIALIZABLE"
+        elif value == 1:
+            return "READ UNCOMMITTED"
+        else:
+            assert False, "Unknown isolation level %s" % value
 
     def on_connect(self):
         if self.isolation_level is not None:
-            if self.isolation_level == 'READ UNCOMMITTED':
-                isolation_level = 1
-            else:
-                isolation_level = 0
-
             def connect(conn):
-                cursor = conn.cursor()
-                cursor.execute("PRAGMA read_uncommitted = %d" % isolation_level)
-                cursor.close()
+                self.set_isolation_level(conn, self.isolation_level)
             return connect
         else:
             return None