Repair pysqlite transaction support

This patch applies the events discussed in
SQLAlchemy documentation at
http://docs.sqlalchemy.org/en/rel_0_9/dialects/sqlite.html#serializable-isolation-savepoints-transactional-ddl
which correct for pysqlite's modifications to SQLite's transactional
cycle.   We turn off pysqlite's automatic emit of BEGIN
as well as COMMIT before any DDL, and instead emit our own
BEGIN at the point where SQLAlchemy knows a transaction is to start.
A proof of concept is added to illustrate SAVEPOINT working
completely.

SAVEPOINT support will be needed in order to support
transactional testing with rollbacks inside of tests.

Change-Id: Ie39bd2287173327903523b66160653608d9e8652

2 files changed, 93 insertions, 0 deletions

diff --git a/oslo/db/sqlalchemy/session.py b/oslo/db/sqlalchemy/session.py
index 5c8688c..3d62e8f 100644
--- a/oslo/db/sqlalchemy/session.py
+++ b/oslo/db/sqlalchemy/session.py
@@ -491,10 +491,22 @@ def _init_events(engine, sqlite_synchronous=True, sqlite_fk=False, **kw):
             # Switch sqlite connections to non-synchronous mode
             dbapi_con.execute("PRAGMA synchronous = OFF")
 
+        # Disable pysqlite's emitting of the BEGIN statement entirely.
+        # Also stops it from emitting COMMIT before any DDL.
+        # below, we emit BEGIN ourselves.
+        # see http://docs.sqlalchemy.org/en/rel_0_9/dialects/\
+        # sqlite.html#serializable-isolation-savepoints-transactional-ddl
+        dbapi_con.isolation_level = None
+
         if sqlite_fk:
             # Ensures that the foreign key constraints are enforced in SQLite.
             dbapi_con.execute('pragma foreign_keys=ON')
 
+    @sqlalchemy.event.listens_for(engine, "begin")
+    def _sqlite_emit_begin(conn):
+        # emit our own BEGIN
+        conn.execute("BEGIN")
+
 
 def _test_connection(engine, max_retries, retry_interval):
     if max_retries == -1:
diff --git a/tests/sqlalchemy/test_sqlalchemy.py b/tests/sqlalchemy/test_sqlalchemy.py
index 88ec605..5099c44 100644
--- a/tests/sqlalchemy/test_sqlalchemy.py
+++ b/tests/sqlalchemy/test_sqlalchemy.py
@@ -86,6 +86,87 @@ class RegexpFilterTestCase(test_base.DbTestCase):
         self._test_regexp_filter(u'♦', [])
 
 
+class SQLiteSavepointTest(test_base.DbTestCase):
+    def setUp(self):
+        super(SQLiteSavepointTest, self).setUp()
+        meta = MetaData()
+        self.test_table = Table(
+            "test_table", meta,
+            Column('id', Integer, primary_key=True),
+            Column('data', String(10)))
+        self.test_table.create(self.engine)
+        self.addCleanup(self.test_table.drop, self.engine)
+
+    def test_plain_transaction(self):
+        conn = self.engine.connect()
+        trans = conn.begin()
+        conn.execute(
+            self.test_table.insert(),
+            {'data': 'data 1'}
+        )
+        self.assertEqual(
+            [(1, 'data 1')],
+            self.engine.execute(
+                self.test_table.select().
+                order_by(self.test_table.c.id)
+            ).fetchall()
+        )
+        trans.rollback()
+        self.assertEqual(
+            0,
+            self.engine.scalar(self.test_table.count())
+        )
+
+    def test_savepoint_middle(self):
+        with self.engine.begin() as conn:
+            conn.execute(
+                self.test_table.insert(),
+                {'data': 'data 1'}
+            )
+
+            savepoint = conn.begin_nested()
+            conn.execute(
+                self.test_table.insert(),
+                {'data': 'data 2'}
+            )
+            savepoint.rollback()
+
+            conn.execute(
+                self.test_table.insert(),
+                {'data': 'data 3'}
+            )
+
+        self.assertEqual(
+            [(1, 'data 1'), (2, 'data 3')],
+            self.engine.execute(
+                self.test_table.select().
+                order_by(self.test_table.c.id)
+            ).fetchall()
+        )
+
+    def test_savepoint_beginning(self):
+        with self.engine.begin() as conn:
+            savepoint = conn.begin_nested()
+            conn.execute(
+                self.test_table.insert(),
+                {'data': 'data 1'}
+            )
+            savepoint.rollback()
+
+            conn.execute(
+                self.test_table.insert(),
+                {'data': 'data 2'}
+            )
+
+        self.assertEqual(
+            [(1, 'data 2')],
+            self.engine.execute(
+                self.test_table.select().
+                order_by(self.test_table.c.id)
+            ).fetchall()
+        )
+
+
 class FakeDBAPIConnection():
     def cursor(self):
         return FakeCursor()