Re: pysqlite design decisions

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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.

There were various other itches I needed scratching such as multiple
statement support - cursor.execute("select 1; select 2; select 3"),
correct handling of unicode everywhere (now fixed in pysqlite),
releasing the GIL whenever possible, the easy ability to do tracing of
queries and results and even cosmetic things such as cursor.execute
returning self so you could things like:

~  for row in cursor.execute("select * .."): print row

The latter is now in pysqlite as well.  I don't know how pysqlite
reconciles with DB-API now because things like that or adding user
defined functions are not mentioned.  Consequently someone using that
extended functionality won't trivially be able to change a module name
and trivially use a different database.

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%.

All of APSW is entirely in C.  There is no Python code at all.  But you
get those SQLite semantics.  eg there are 5 supported datatypes.
Anything else gets you an exception.  You get your transactions the
SQLite way - nothing is parsing your SQL trying to make it look like
what DBAPI says.

[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.  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.)

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.

|  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.

| 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.  It is also something that should be part of
SQLite itself not higher level wrappers (just like SQLITE_SCHEMA should
have been handled).

|  * Expose the bottom layer to Python applications (perhaps via a
| different module name?) so that it is easier to get "raw" access to
sqlite.

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.

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).

=== pysqlite ===

from pysqlite2 import dbapi2 as sqlite
def badfunc(t):
~    return 1/0

con = sqlite.connect(":memory:")
con.create_function("badfunc", 1, badfunc)
cur = con.cursor()
cur.execute("select badfunc(3)")

$ python func.py
Traceback (most recent call last):
~  File "func.py", line 8, in ?
~    cur.execute("select badfunc(3)")
pysqlite2.dbapi2.OperationalError: user-defined function raised exception

=== apsw ===

import apsw
def badfunc(t):
~    return 1/0

con = apsw.Connection(":memory:")
con.createscalarfunction("badfunc", badfunc, 1)
cur = con.cursor()
cur.execute("select badfunc(3)")

$ python afunc.py
Traceback (most recent call last):
~  File "afunc.py", line 8, in ?
~    cur.execute("select badfunc(3)")
~  File "afunc.py", line 3, in badfunc
~    return 1/0
ZeroDivisionError: integer division or modulo by zero



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.

| 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 it doesn't become clear after reading the DB-API 2.0 spec

At least I don't have to follow that :-)

The next release of APSW (corresponding to SQLite 3.3.8) has these updates:

- - Dummy frames from C code in exception tracebacks
- - String values with embedded nulls work (eg "foo\0bar") rather than
truncating at the \0
- - You can load SQLite shared library extensions
- - Support for SQLites virtual tables
http://www.sqlite.org/cvstrac/wiki?p=VirtualTables

The virtual tables are an IMHO very interesting.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFczwOmOOfHg372QQRAlqeAJ9JbihvXjmlOWskhSm0lY2l2HFcEwCfV+06
lYP2gODPcRwzmVfcTJ0N2h0=
=RKa4
-----END PGP SIGNATURE-----
_______________________________________________
pysqlite mailing list
[email protected]
http://lists.initd.org/mailman/listinfo/pysqlite