Re: [pysqlite] tracing

Hello,

Having the cursor method available as a class is not an issue if this appears difficult. One can still make an adapter class around it.

I have two usage cases, forgive me if it is all experimental use. First is a module apsw_dbapi2 that provides a Pysqlite/DBAPI2 compatible interface. It is intended to check if application errors might be resolved through APSW instead of Pysqlita (especially for the locking mechanism).

Second is a pass-time project for a SQL-based chess engine. The performance is determined for 90 % in SQLite. To measure this, the Execution Trace option won't do. You need not only the start but also the end of each execute call.

In both cases, the sub-classing is disadvantagous for the speed. I'll see what the tracers and especially the Connection hooks can do. The fact that the execution tracer breaks down multiple statements can improve apsw_dbapi2. 

You pointed out an option like:

>    python -m mylogger /path/to/app.py arg1 arg2 etc

This is indeed a perfect solution to leave the application unchanged. Possibly one may publish his mylogger.

Thanks and sorry for not RTFM'ing,

Edzard


--- [email protected] wrote:

From: Roger Binns <[email protected]>
To: [email protected],  "About pysqlite and APSW." <list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org>
Subject: Re: tracing
Date: Sat, 22 Nov 2008 20:20:22 -0800

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

Edzard Pasma wrote:
> With regards to the execution trace option in APSW, I would rather write my own
> Cursor sub-class and add any kind of tracing there.

The tracing has to be initiated from within APSW.  This is because APSW
supports multiple statements per execute/executemany and there is no way
for other code to know how much of a string constitutes a query and how
many of the bindings were used.

All the APSW code does is call the registered callback with the query
string and bindings.  It is up to the callback to do anything meaningful
with it.

> This is still a bit inconvenient though, as the cursor class is not exposed, only 
> the method. Is that something that can be changed in the next release too?

This is fairly difficult due to all the plumbing behind the scenes.
Other than logging do you have any example use cases?

> Possibly someone can ever publish an APSW_TRACE module (or Pysqlite), that can be imported instead of the plain module and leave the application further unchanged. 

If the goal is to touch the application as little as possible then the
best approach is to add a connection hook:

http://apsw.googlecode.com/svn/publish/apsw.html#apsw.connection_hooks

The connection hook can then add the connection level tracers.  I'll
also add some informational attributes to connections such as what
filename was used, vfs etc.  If this approach is taken then you don't
need to touch the application at all.  Just write a module that imports
apsw, adds a callback to connection_hooks, shuffles sys.argv along by
one and executes argv[0].  You would then be able to do:

  python -m mylogger /path/to/app.py arg1 arg2 etc

In case anyone is wondering, the way I came up with the feature set for
APSW is when I was adapting BitPim to use SQLite instead of the prior
behaviour of pretty printing dicts as text.  I started with pysqlite and
wrapped its connection/cursor with how I wanted things done.  That for
example is how the idea of the cursor also being an iterator started as
well as the tracing.  I also didn't like some things such as Unicode
handling and not handling multiple statements.  For example this is the
only sane way of doing an update and getting the id:

  rowid=execute("begin ; insert ... ; select last_insert_rowid(); end")

The rowid as returned by SQLite is a connection level property - ie it
is the last rowid of any cursor on the connection although DBAPI makes
it a cursor property.  Unless there is serious parsing and transaction
monitoring going on, the above is a nice sensible race free way of doing it.

My performance measurements showed that having extra Python gunk layered
over the C based cursor object added a significant penalty.  Given that
plus the other quirks, desire for multiple statements etc I wrote APSW
in about a week and a half.

Since then APSW and pysqlite have been "borrowing" features from each
other.  APSW has focussed on staying up to date with each new piece of
SQLite functionality while pysqlite has cared about that whole DBAPI
thing, staying compatible with older SQLite versions and being in the
Python standard library.  APSW is still way faster though :-)

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

iEYEARECAAYFAkko2gIACgkQmOOfHg372QQYNgCdGUzhLG07Ba5DgyZYr7KObsrX
dSMAoKfYoSjjTrOHkq5eujeTn8TdDp0Q
=U+QY
-----END PGP SIGNATURE-----