Re: pysqlite design decisions

On 3 Dec, 09:05 pm, [email protected] wrote:
>Gerhard Häring wrote:
>| A little later during pysqlite2 development, there was Roger Binns who
>| created his own solution APSW because he needed something now instead of
>| later and something that did as little behind the scenes as possible.
>
>Most importantly I knew I was using SQLite and I wanted SQLite semantics
>rather than a layer that tried to make SQLite look more like other
>databases, or conform to a generic spec for databases.

We have added apsw support for Axiom as well, although we still use pysqlite as the primary driver due to its generally greater availability.  The more sqlite-like semantics were handy, but the fact that strings would randomly come back as 8-bit or unicode depending on their contents was annoying :-\.

>When I originally wrote APSW (December 2004), it also out performed
>pysqlite in my tests on my data for my kind of workload, usually by
>around 30%.

I hope that is no longer the case.  Have there been any similar tests recently?

>glyph-TyWPi3/[email protected] wrote:
>| It's clear, to me at least, that the higher levels here are inadequately
>| tested.
>
>SQLite itself sets the bar really high.  Its test suite has something
>like 98% coverage.

As well it should!

>I've got every line of C that it is possible to test
>in APSW covered.  (Some aren't since they require memory exhaustion and
>I have no way to make Python run out of memory at the various places.)

It's good to hear you've paid such attention to detail.  A good reason why it would be nice if the projects unified and apsw became pysqlite2.lowlevel -- as duplication were eliminated, coverage would go up :)

Getting inside Python's head at that level is a real pain in the ass.  (and Python itself doesn't set the bar particularly high :-\.

>I did check the coverage of C code in pysqlite and it was pretty good
>(something like 80% if I remember).  However I didn't check the Python
>code in SQLite.

There's very little python code in pysqlite.  (or do you mean there is actually python code in sqlite itself somewhere?  I don't see any.)

>|  I should have spoken up about this sooner, because #170/#185
>
>These are related to handling SQLITE_SCHEMA errors.  The good news is
>that an upcoming release of SQLite is going to handle them all
>internally.  At that point all code relating to SQLITE_SCHEMA can be
>removed.

That is good news.

>| far as I can tell it's impossible to exercise any control over the
>| statement cache from Python.
>
>I was very surprised at the addition of a statement cache since I don't
>see how you can get it to work if you allow multiple threads to use the
>same sqlite_db pointer.

Keep it locked on the GIL?  I wouldn't know though, from what I've heard, sqlite itself is only middling good at dealing with multiple threads.  I use multiple processes.

>  It is also something that should be part of
>SQLite itself not higher level wrappers (just like SQLITE_SCHEMA should
>have been handled).

Hmm.  I'm not so sure about that.  I don't know if it belongs where it is in particular in pysqlite, but the operations offered by the DB layer should be "compile this statement" and "execute this statement".

>After reading your whole email, I think the best solution for you would
>be to use ctypes and interface directly to the SQLite C API.  The API is
>sufficiently friendly for this.  Both pysqlite and apsw do things like
>hiding prepared statements and making it look more object oriented than
>it really is.

I have considered that a few times, but pysqlite and apsw both do some useful stuff too - converting exceptions, mapping types, etc.  Maybe I could do it better tuned for Axiom if I spent a few months on my own custom ctypes binding, but the point is I don't want to spend a lot of time hacking at this level :).

Also, there is a fair amount of interest in eventually making Axiom work with different databases.  I don't want to diverge unnecessarily far from DB-API bindings to make this more difficult than it needs to be (and it is already very difficult).

>Gerhard Häring wrote:
>| I know of this problem. That's why I have once started with implementing a
>| logging layer for pysqlite:
>
>In apsw I added tracing functions that get called back with each SQL
>statement and bindings, and each returned row.
>
>I am also baffled at the behaviour of pysqlite swallowing exceptions in
>callbacks (eg user defined functions).

I'm pretty sure I've seen a bug reported on this somewhere.  Can't find it now though.

>The next release of apsw is even better.  The various layers of C code
>being called also add themselves to the callback and even setup fake
>local variables in the traceback.  This makes it far clearer as to what
>was being called and why.

This is very cool.

>| It feels unproductive to duplicate all
>| their efforts. I wouldn't object to just distribute APSW as a submodule
>| with pysqlite though.
>
>apsw has exactly the same license as pysqlite so that can be done.  Note
>however that I do track the changes in SQLite itself and so update at
>about the same speed as SQLite.  Now that pysqlite is a standard part of
>Python 2.5, I don't see how it can change semantics except in Python 2.6.

If apsw were to merge with pysqlite, I think the expectations of the different parts of the stability of different parts of the API would remain the same.  If pysqlite were to later come to use any parts of APSW, it would have to mask the differences between the internals to any code calling into the "pysqlite" namespace.

I assume that it would have to go into a new major rev, though, because I do think the main Python devs would object to a whole new module being included.  (Best to ask them though, and not speculate).

>| If it doesn't become clear after reading the DB-API 2.0 spec
>
>At least I don't have to follow that :-)

There would be no point in having two DB-API compatible wrappers in the same repository - the whole point of merging is that they do slightly different, but both useful, things :).

_______________________________________________
pysqlite mailing list
pysqlite-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/pysqlite