Sanity check for pysqlite wrt. transaction handling
===================================================

>>> import sqlite3

Trying to describe what I expect from the SQLite bindings. For compatibility
with older code, these should continue to work:

Backwards compatibility
-----------------------

Select should not lock the database
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

If the main thread queries the database, the original SQLite bindings did not
start a transaction until some DML command is issued. If you use only SELECT,
the internal SQLite connection stays in autocommit mode.

Of course, the database is still locked until all result rows are fetched, for
example via cursor.fetchall()

>>> conn = sqlite3.connect("sample.db")
>>> cursor = conn.cursor()
>>> unused = cursor.execute("create table if not exists users "
...         "(id integer primary key, login varchar, gecos varchar)")
>>> conn.commit()

>>> unused = cursor.execute("select * from users").fetchall()
>>> import threading
>>> def background_access():
...     conn = sqlite3.connect("sample.db")
...     cursor = conn.cursor()
...     cursor.execute(
...             "insert into users (login, gecos) values (?, ?)",
...             ('martin', 'Martin Mustermann'))
...     conn.commit()

>>> thread = threading.Thread(target=background_access)
>>> thread.start()
>>> thread.join(4)
>>> assert not thread.is_alive()


pragma foreign_keys works
^^^^^^^^^^^^^^^^^^^^^^^^^

More or less by accident, setting the foreign_keys pragma works with the original
sqlite3 module. The setting can only be changed outside a transaction (in autocommit
mode). Otherwise, the operation completes without error but foreign keys stay
disabled.

>>> unused = cursor.execute("pragma foreign_keys=1")
>>> cursor.execute("pragma foreign_keys").fetchall()
[(1,)]

For reference, this is the behaviour with the sqlite3 command line tool:

    torsten@defiant:~$ sqlite3 test.db
    SQLite version 3.7.9 2011-11-01 00:52:41
    Enter ".help" for instructions
    Enter SQL statements terminated with a ";"
    sqlite> begin;
    sqlite> pragma foreign_keys=1;
    sqlite> pragma foreign_keys;
    0
    sqlite> rollback;
    sqlite> pragma foreign_keys=1;
    sqlite> pragma foreign_keys;
    1

Observe how changing the setting inside a transaction has no effect. I'd consider
this a bug in SQLite.


Improvements in transaction handling
------------------------------------

Savepoints should work
^^^^^^^^^^^^^^^^^^^^^^

>>> unused = cursor.execute("drop table if exists sampledata")
>>> unused = cursor.execute("create table sampledata (value varchar)")
>>> conn.commit()

>>> unused = cursor.execute("insert into sampledata values (?)", ("outer_transaction",))
>>> unused = cursor.execute("savepoint first_savepoint")
>>> unused = cursor.execute("insert into sampledata values (?)", ("first_savepoint_rolled_back",))
>>> unused = cursor.execute("rollback to first_savepoint")
>>> unused = cursor.execute("insert into sampledata values (?)", ("outer_transaction2",))
>>> unused = cursor.execute("savepoint second_savepoint")
>>> unused = cursor.execute("insert into sampledata values (?)", ("in_second_savepoint",))
>>> unused = cursor.execute("release second_savepoint")
>>> unused = cursor.execute("savepoint last_savepoint")
>>> unused = cursor.execute("delete from sampledata")
>>> unused = cursor.execute("rollback to last_savepoint")
>>> conn.commit()

>>> cursor.execute("select * from sampledata").fetchall()
[(u'outer_transaction',), (u'outer_transaction2',), (u'in_second_savepoint',)]