Hi
The python sqlite module automatically commits open transactions
when it encounters a DDL statement. This is unnecessary; DDL is
transaction in my testing (see attached).
Attached patch addresses the issue. Patch is against 2.6.1, but
looking at Trunk in svn, it seems like the patch is needed and
would apply. One issue I could foresee is that this behavior might
depend on the sqlite version in use (I'm on 3.6.10).
Thanks
Scott
diff -Nu -r --show-c-func work/Python-2.6.1/Modules/_sqlite/cursor.c work.new/Python-2.6.1/Modules/_sqlite/cursor.c
--- work/Python-2.6.1/Modules/_sqlite/cursor.c 2008-09-12 15:33:22.000000000 -0700
+++ work.new/Python-2.6.1/Modules/_sqlite/cursor.c 2010-12-17 14:26:48.323301733 -0800
@@ -69,6 +69,14 @@ static pysqlite_StatementKind detect_sta
return STATEMENT_DELETE;
} else if (!strcmp(buf, "replace")) {
return STATEMENT_REPLACE;
+ } else if (!strcmp(buf, "create")) {
+ return STATEMENT_CREATE;
+ } else if (!strcmp(buf, "alter")) {
+ return STATEMENT_ALTER;
+ } else if (!strcmp(buf, "drop")) {
+ return STATEMENT_DROP;
+ } else if (!strcmp(buf, "pragma")) {
+ return STATEMENT_PRAGMA;
} else {
return STATEMENT_OTHER;
}
@@ -560,6 +568,10 @@ PyObject* _pysqlite_query_execute(pysqli
case STATEMENT_DELETE:
case STATEMENT_INSERT:
case STATEMENT_REPLACE:
+ case STATEMENT_CREATE:
+ case STATEMENT_ALTER:
+ case STATEMENT_DROP:
+ case STATEMENT_PRAGMA:
if (!self->connection->inTransaction) {
result = _pysqlite_connection_begin(self->connection);
if (!result) {
@@ -569,8 +581,9 @@ PyObject* _pysqlite_query_execute(pysqli
}
break;
case STATEMENT_OTHER:
- /* it's a DDL statement or something similar
- - we better COMMIT first so it works for all cases */
+ /* something else
+ - we better COMMIT first so it works for all cases.
+ - XXX this is probably a bad idea (surprising) */
if (self->connection->inTransaction) {
result = pysqlite_connection_commit(self->connection, NULL);
if (!result) {
diff -Nu -r --show-c-func work/Python-2.6.1/Modules/_sqlite/cursor.h work.new/Python-2.6.1/Modules/_sqlite/cursor.h
--- work/Python-2.6.1/Modules/_sqlite/cursor.h 2008-05-31 14:33:27.000000000 -0700
+++ work.new/Python-2.6.1/Modules/_sqlite/cursor.h 2010-12-17 14:27:24.893792279 -0800
@@ -48,7 +48,8 @@ typedef struct
typedef enum {
STATEMENT_INVALID, STATEMENT_INSERT, STATEMENT_DELETE,
STATEMENT_UPDATE, STATEMENT_REPLACE, STATEMENT_SELECT,
- STATEMENT_OTHER
+ STATEMENT_CREATE, STATEMENT_ALTER, STATEMENT_DROP,
+ STATEMENT_PRAGMA, STATEMENT_OTHER
} pysqlite_StatementKind;
extern PyTypeObject pysqlite_CursorType;
import atexit
import os
import sqlite3
class FakeError:
pass
def table_exists(conn, table):
sql = 'PRAGMA TABLE_INFO(%s) ' % table
cur = conn.execute(sql)
row = cur.fetchone()
return row is not None
def row_count(conn, table):
cur = conn.execute('SELECT COUNT(*) FROM %s' % table)
row = cur.fetchone()
return row[0]
def index_exists(conn, table):
sql = 'PRAGMA INDEX_INFO(%s) ' % table
cur = conn.execute(sql)
row = cur.fetchone()
return row is not None
def column_exists(conn, table, column):
sql = 'PRAGMA TABLE_INFO(%s) ' % table
cur = conn.execute(sql)
for row in cur:
if row[1] == column:
return True
return False
def create_temp_database():
filename = os.tempnam('/tmp', 'test') + '.db'
#print "using filename", filename
conn = sqlite3.connect(filename)
atexit.register(os.remove, filename)
conn.execute('CREATE TABLE foo (id INTEGER)')
conn.commit()
assert table_exists(conn, 'foo')
return conn
def test_dml():
''' check basic dml is transactional '''
conn = create_temp_database()
conn.execute('INSERT INTO foo VALUES (1)')
conn.rollback()
assert row_count(conn, 'foo') == 0
def test_table_create():
''' check that table create is transactional '''
conn = create_temp_database()
conn.execute('INSERT INTO foo VALUES (1)')
conn.execute('CREATE TABLE bar (id INTEGER)')
assert table_exists(conn, 'bar')
conn.execute('INSERT INTO bar VALUES (1)')
conn.rollback()
assert not table_exists(conn, 'bar')
def test_index_create():
''' check that index create is transactional '''
conn = create_temp_database()
conn.execute('INSERT INTO foo VALUES (1)')
conn.execute('CREATE INDEX foo_by_id ON foo (id)')
assert index_exists(conn, 'foo_by_id')
conn.execute('INSERT INTO foo VALUES (2)')
conn.rollback()
assert not index_exists(conn, 'foo_by_id')
def test_column_add():
''' check that column add is transactional '''
conn = create_temp_database()
conn.execute('INSERT INTO foo VALUES (1)')
conn.execute('ALTER TABLE foo ADD COLUMN stuff INTEGER')
assert column_exists(conn, 'foo', 'stuff')
conn.execute('INSERT INTO foo VALUES (2, 3)')
conn.rollback()
assert not column_exists(conn, 'foo', 'stuff')
def test_rename():
''' check that table rename is transactional '''
conn = create_temp_database()
conn.execute('INSERT INTO foo VALUES (1)')
conn.execute('ALTER TABLE foo RENAME TO bar')
assert not table_exists(conn, 'foo')
assert table_exists(conn, 'bar')
conn.rollback()
assert table_exists(conn, 'foo')
assert not table_exists(conn, 'bar')
def test_drop_table():
''' check that table drop is transactional '''
conn = create_temp_database()
conn.execute('INSERT INTO foo VALUES (1)')
conn.execute('DROP TABLE foo')
assert not table_exists(conn, 'foo')
conn.rollback()
assert table_exists(conn, 'foo')
def test_drop_index():
''' check that index drop is transactional '''
conn = create_temp_database()
conn.execute('CREATE INDEX foo_by_id ON foo (id)')
conn.execute('INSERT INTO foo VALUES (1)')
conn.commit()
assert index_exists(conn, 'foo_by_id')
conn.execute('DROP INDEX foo_by_id')
assert not index_exists(conn, 'foo_by_id')
conn.rollback()
assert index_exists(conn, 'foo_by_id')
_______________________________________________
Python-Dev mailing list
Python-Dev@python.org
http://mail.python.org/mailman/listinfo/python-dev
Unsubscribe:
http://mail.python.org/mailman/options/python-dev/archive%40mail-archive.com
