ANN - SQLite 3.6.8 adds nested transactions
Darren Duncan
The SQLite DBMS, as of the new version 3.6.8, now has nested transaction support.
See http://www.sqlite.org/releaselog/3_6_8.html for the release announcement,
and http://www.sqlite.org/lang_savepoint.html for documentation of the feature.
As far as I was concerned, and AFAIK had argued in the past, that was the single
most important piece of missing functionality. Something whose presence can
make reliable database development an order of magnitude easier. Unlike some
add-on features which could be done in wrappers, nested transactions was
definitely something that was only appropriate to be implemented in the same low
level as normal transactions in the DBMS. Its about code being able to declare
and have enforced at any level of granularity that a set of operations is
atomic, as a transaction, without worrying about what calling code is doing with
transactions, and making it easier to do error handling and retry/abort etc.
I'm letting you know about this update today as encouragement to make use of the
feature in any of your applications or DBMS toolkits that do or want to support
SQLite. It is now also easier to implement TTM's concept of a "D" language
(which cares about reliability and ease of use) over SQLite.
Note that I'm promoting this under the assumption the new feature works as
advertised and is efficient; if not, presumably any deficiencies will be soon
fixed; I will be testing it myself before too long.
Similarly, you should exploit any native nested transaction support of any other
DBMS you are using.
-- Darren Duncan
John Siracusa
> Note that I was also thinking about the fact that 1.14 is known in some
> circles to have introduced bugs that 1.13 didn't have, so being able to
> address this would take more C-fu than simply updating the SQLite source
> files.
Both DBD::SQLite 1.13 and DBD::SQLite 1.14 have bugs that prevent the
Rose::DB::Object test suite from passing. (I'd link to one, but
rt.cpan.org seems unresponsive at the moment.) In fact, the only
versions that the test suite will even attempt to use are 1.12 and
1.13, which are from 2005 and 2006, respectively. So, basically,
DBD::SQLite has been broken for a long time--years!--and desperately
needs some attention from a knowledgeable C/XS programmer.
-John
Darren Duncan
> The disconnection code is borked too; if you connect, do some work with
> statement handles, and disconnect again, and then repeat many times in
> the same process, you'll crash out eventually.
>
> The annoying thing is that there are trivial patches on rt.cpan.org to
> fix it, and they've been there for ages, and they aren't getting
> applied. :(
Well, if that's the case, and one or more people can supply compatible patches
for various problems, then maybe someone with less C-fu than otherwise can then
co-maintain DBD::SQLite and apply the patches.
For example, it would be easier for me to become co-maintainer if such
help-me-fix-it feedback is readily available. And I do want to see SQLite
succeed with Perl, now and indefinitely, so maybe I'll go ahead and ask for
co-maintainership. Any objections or support? Note that it is likely to be a
few weeks before I release anything.
In that vein, here's a question:
To your knowledge, were the problems with 1.13+ due to changes in the Perl
binding itself, or due to a lack of changes in the Perl binding that were
required to maintain SQLite compatibility?
If the problem was due to changes rather than a lack of changes, I could then
just use version 1.12 as a point of departure for a new 1.15_01 or what-have-you
release.
Do any reported problems with 1.13+ also apply to 1.12?
> Do you know if Audrey's fork has fixes applied, or is it just simply an
> upgraded-to-version-3.6 thing?
I don't know about fixes; haven't looked close enough at it. But I do know that
the source files are significantly different. The main point of the
amalgamation source, which is supplied officially by SQLite, is to let less
capable dev environments like Windows make and build it more easily, because
lots of pre-compilation work has been done already.
-- Darren Duncan
John Siracusa
> For example, it would be easier for me to become co-maintainer if such
> help-me-fix-it feedback is readily available. And I do want to see SQLite
> succeed with Perl, now and indefinitely, so maybe I'll go ahead and ask for
> co-maintainership. Any objections or support? Note that it is likely to be a
> few weeks before I release anything.
Anyone doing anything would be an improvement at this point.
DBD::SQLite desperately needs a new maintainer.
> To your knowledge, were the problems with 1.13+ due to changes in the Perl
> binding itself, or due to a lack of changes in the Perl binding that were
> required to maintain SQLite compatibility?
No idea, but take a look at this (show-stopper, as far as the RDBO
test are concerned) bug I filed in 2006:
http://rt.cpan.org/Public/Bug/Display.html?id=21472
"I tried chasing it through the C code and found a situation where the
number '?' of placeholders found by DBD::SQLite was one less than the
actual number of '?' in the SQL. (p->nVar was 3 when there were
clearly 4 '?' placeholders in p->zSql.) This was not a complex query
either, just a standard INSERT into a table with four INTEGER
columns."
I can't imagine a bug based on that kind of fundamental disconnect in
basic operations persisting in such a widely used product as SQLite
proper, so my inclination is to point to the Perl bindings. But then,
I really don't know enough about Perl 5 XS or SQLite to say that with
much confidence.
-John