classification
Title: Expose sqlite3 connection inTransaction as read-only in_transaction attribute
Type: enhancement Stage: resolved
Components: Extension Modules, Macintosh Versions: Python 3.2
process
Status: closed Resolution: fixed
Dependencies: Superseder:
Assigned To: r.david.murray Nosy List: exarkun, flox, ghaering, haypo, janssen, l0nwlf, loewis, ned.deily, pitrou, r.david.murray
Priority: normal Keywords: buildbot, easy, patch

Created on 2010-05-28 17:54 by r.david.murray, last changed 2010-10-17 21:19 by r.david.murray. This issue is now closed.

Files
File name Uploaded Description Edit
sqlite.in_transaction.patch r.david.murray, 2010-05-28 17:54
dbapi.patch l0nwlf, 2010-05-28 20:31 unit-test added
sqlite3_in_transaction.patch r.david.murray, 2010-05-30 14:33 complete patch
Messages (19)
msg106683 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2010-05-28 17:54
I have a use case where I'd like to be able to check whether or not there is an uncommitted transaction.  The use case is a REPL database editor.  If the user issues the 'save' command a commit is done.  When they quit the application, I'd like to be able to prompt them with a 'save or discard' if and only if they have made changes since the last save.  Exposing the connection's inTransaction attribute would allow me to do this.  The attached patch does this as a read only attribute named in_transaction.  I'll add unit tests if this idea isn't rejected.
msg106686 - (view) Author: Shashwat Anand (l0nwlf) Date: 2010-05-28 19:33
Tested this patch, works perfectly fine. Also it suits for the particular use case which David mentioned where there is no better alternate approach.
msg106687 - (view) Author: Jean-Paul Calderone (exarkun) * (Python committer) Date: 2010-05-28 19:37
> If the user issues the 'save' command a commit is done.  When they quit the application, I'd like to be able to prompt them with a 'save or discard' if and only if they have made changes since the last save.

Isn't this the same as "if they have issued any non-SELECT" statements?  And isn't that pretty easy to track?

(Not that I'm opposed to making the SQLite3 wrapper more complete.)
msg106688 - (view) Author: Shashwat Anand (l0nwlf) Date: 2010-05-28 19:46
>>> conn = sqlite3.connect('dbdump.sqlite')
>>> c = conn.cursor()
>>> conn.in_transaction
False
>>> c.execute('CREATE TABLE foo (id integer, name text)')
<sqlite3.Cursor object at 0x1004a7730>
>>> conn.in_transaction
False

It gives True for Insert and False for Select. perfect Ok. However It should give True when we Create tables ?

Adding unit-test which upon showing this behavior fails the test.
msg106689 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2010-05-28 20:23
@exarkun: yes, but since the module is already tracking that information, it seems silly to duplicate it in my code, especially since that duplication could include data-losing bugs.

@l0nwlf: its behaviour is in accord with the module documentation, which talks about the fact that issuing a create statement (for example) commits the current transaction.  So in_transaction is an accurate representation of what sqlite3 is actually doing.
msg106690 - (view) Author: Shashwat Anand (l0nwlf) Date: 2010-05-28 20:31
Ok then.

Uploading unit-test which takes value of in_transaction as False after issuing a Create statement. It passes with the patch applied.
msg106694 - (view) Author: Martin v. Löwis (loewis) * (Python committer) Date: 2010-05-28 22:37
The patch lacks documentation. Otherwise, I think it's fine.
msg106761 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2010-05-30 14:29
Here is a complete patch, including documentation.  I tweaked Shashwat's unit test a bit, and added one to make sure the attribute is read only.

I'll apply this soonish if there are no objections.
msg106821 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2010-06-01 01:34
Committed in r81632.
msg107617 - (view) Author: STINNER Victor (haypo) * (Python committer) Date: 2010-06-12 00:43
Reopen the issue: the test fails on Sparc.

http://www.python.org/dev/buildbot/builders/sparc%20Ubuntu%203.x/builds/222

======================================================================
FAIL: CheckInTransaction (sqlite3.test.dbapi.ConnectionTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/pybot/buildarea/3.x.klose-ubuntu-sparc/build/Lib/sqlite3/test/dbapi.py", line 152, in CheckInTransaction
    self.assertEqual(cx.in_transaction, True)
AssertionError: False != True
msg107619 - (view) Author: STINNER Victor (haypo) * (Python committer) Date: 2010-06-12 00:51
Same error on builder "PPC Leopard 3.x".

http://www.python.org/dev/buildbot/builders/PPC%20Leopard%203.x/builds/87/steps/test/logs/stdio

test_sqlite
test test_sqlite failed -- Traceback (most recent call last):
  File "/Users/buildbot/buildarea/3.x.parc-leopard-1/build/Lib/sqlite3/test/dbapi.py", line 152, in CheckInTransaction
    self.assertEqual(cx.in_transaction, True)
AssertionError: False != True
msg114712 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2010-08-22 22:10
The sparc ubuntu buildbot appears to no longer exist.  The sparc solaris buildbot doesn't have a problem with it.  So right now only PPC Tiger is failing.  An endianness issue was suggested, but since T_BOOL is used by _io, and that is tested and passes on the PPC buildbot, I'm thinking that is probably not the problem.

Antoine also suggested it might be the particular version of sqlite in use on the buildbot, but I don't currently know what version that is.  Bill, can you clue me in?
msg116921 - (view) Author: Antoine Pitrou (pitrou) * (Python committer) Date: 2010-09-20 09:14
> Antoine also suggested it might be the particular version of sqlite in 
> use on the buildbot, but I don't currently know what version that is.  
> Bill, can you clue me in?

You could simply print out sqlite3.sqlite_version at the beginning of test_sqlite when it is run in verbose mode, as is done in test_ssl.
msg117573 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2010-09-29 01:23
Added version print to test_sqlite in r85807.
msg118884 - (view) Author: Bill Janssen (janssen) * (Python committer) Date: 2010-10-16 19:25
PPC Tiger is using Python 2.7, so it's 3.6.11.

Python 3.X also seems to be failing the sqlite tests on PPC Leopard.  Is the required version # different between 3.1 and 3.x?
msg118895 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2010-10-16 21:03
The attribute and test aren't in 3.1 (or 2.7), so this issue only applies to 3.x trunk.
msg118911 - (view) Author: Ned Deily (ned.deily) * (Python committer) Date: 2010-10-17 02:04
FYI, in a recent py3k installer build test using the same installer image (2-way i386/ppc universal with statically linked sqlite 3.6.11), the test fails on ppc G3 (10.4), ppc g4 (10.5) but passes on i386 (10.6) so I'd go along with Bill's big- vs little-endian hypothesis.
msg118971 - (view) Author: Martin v. Löwis (loewis) * (Python committer) Date: 2010-10-17 19:49
This is now fixed in r85660. The field associated with a T_BOOL must be of type char, so I changed the type of inTransaction.
msg118985 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2010-10-17 21:19
Thank you, Martin.
History
Date User Action Args
2010-10-17 21:19:15r.david.murraysetmessages: + msg118985
2010-10-17 19:49:56loewissetstatus: open -> closed
resolution: fixed
messages: + msg118971
2010-10-17 02:04:22ned.deilysetnosy: + ned.deily
messages: + msg118911
2010-10-16 21:03:52r.david.murraysetmessages: + msg118895
2010-10-16 19:25:12janssensetmessages: + msg118884
2010-09-29 01:23:40r.david.murraysetmessages: + msg117573
2010-09-20 09:14:38pitrousetnosy: + pitrou
messages: + msg116921
2010-08-22 22:10:45r.david.murraysetnosy: + janssen
messages: + msg114712
2010-08-15 15:49:11floxsetnosy: + flox
2010-08-15 15:48:48floxsetkeywords: + buildbot
nosy: loewis, ghaering, exarkun, haypo, r.david.murray, l0nwlf
components: + Macintosh
2010-06-12 00:51:45hayposetmessages: + msg107619
2010-06-12 00:43:20hayposetstatus: closed -> open

nosy: + haypo
messages: + msg107617

resolution: accepted -> (no value)
2010-06-01 01:34:09r.david.murraysetstatus: open -> closed
resolution: accepted
messages: + msg106821

stage: patch review -> resolved
2010-05-30 14:33:49r.david.murraysetfiles: + sqlite3_in_transaction.patch
2010-05-30 14:33:08r.david.murraysetfiles: - sqlite3_in_transaction.patch
2010-05-30 14:29:51r.david.murraysetfiles: + sqlite3_in_transaction.patch
assignee: r.david.murray
messages: + msg106761

stage: test needed -> patch review
2010-05-28 22:37:01loewissetnosy: + loewis
messages: + msg106694
2010-05-28 20:31:59l0nwlfsetfiles: + dbapi.patch

messages: + msg106690
2010-05-28 20:31:28l0nwlfsetfiles: - dbapi.patch
2010-05-28 20:23:45r.david.murraysetmessages: + msg106689
2010-05-28 19:46:48l0nwlfsetfiles: + dbapi.patch

messages: + msg106688
2010-05-28 19:42:42l0nwlfsetfiles: - dbapi.patch
2010-05-28 19:37:03exarkunsetnosy: + exarkun
messages: + msg106687
2010-05-28 19:33:46l0nwlfsetfiles: + dbapi.patch

messages: + msg106686
2010-05-28 17:56:55l0nwlfsetnosy: + l0nwlf
2010-05-28 17:54:08r.david.murraycreate