-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Jim Wilcoxson wrote: > I did some checking on the pysqlite mailing list. Apparently the > change to reset all cursors before commit is recent, and the reason is > that pysqlite or sqlite itself would return bogus rows.
It is pysqlite returning the bogus rows, and again entirely due to pysqlite doing transaction management behind the scenes. Use whatever isolation level means it stops doing that, accept things the way they are, or use APSW. > Returning old or new data would be one thing, but in this example, > totally wrong data is returned. There's no explanation of why it was > happening. Long boring section: The data is "totally wrong" in the sense that outstanding cursors would still return data from before rollbacks. It won't return random rubbish from thin air. Duplication arises because of how cursors are implemented behind the scenes. They have to be both greedy and passive. The statement is initially prepared using sqlite3_prepare but nothing happens till sqlite3_step is called which will execute the statement to completion (eg if it was "create table foo(x)") or till a row is returned (eg if it was "select * from foo"). Calling next()/using the cursor as an iterator will call sqlite3_step to get the next row (being passive) but you don't want the user to have to call next() for "create table foo(x)" to be executed so the initial execute is greedy in case the statement executes to completion. The cursor has internal state tracking (entirely within pysqlite or APSW for that matter) as to whether it is fresh (ie just prepared/in the statement cache), done initial execution, sitting at rows, row consumed, exhausted etc with various calls/being used as an iterator adjusting that state. Depending on the interaction between pysqlite behind your back transaction management, iteration, state of the cursors and possibly a bug or too, you could get the same row appear to be returned twice as well as a result row ignoring rollbacks. I don't see how something like that could happen with commit, but then again I wrote my interface layer so that it works the way SQLite does and does not try to fight SQLite :-) Look at http://oss.itsystementwicklung.de/trac/pysqlite/browser/src/cursor.c for the pysqlite code. That file is the second largest in the source to give you an idea of the complexity (the same is true for APSW). BTW there is even more complexity than I described. The cursor execution also has to take into account bindings sequences such as when executemany was called. pysqlite chickened out and won't execute multiple statements (eg cursor.execute("do one thing;do another"). It does have executescript that will execute them all, but that ignores any returned data! This page gives you some idea of the differences between a wrapper that follows DBAPI and pretends SQLite does too (ie pysqlite) and a wrapper that slavishly follows how SQLite works even if that is different to other databases (ie APSW): http://apsw.googlecode.com/svn/publish/pysqlite.html Roger -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkpK1ygACgkQmOOfHg372QRg9wCgvDDrA6lduq+4CilO5NCiuGlR nAwAoMziOD7TnsrR0xTNiPlX8GK1NsJ0 =OJfe -----END PGP SIGNATURE----- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
