SQLite trace callback #55897

Bluehorn · 2011-03-26T22:09:20Z

BPO	11688
Nosy	@pitrou, @Bluehorn
Files	sqlite_trace_py3.diff: Patch for default branch sqlite_trace_py27.diff: Patch for Python 2.7 sqlite_trace.diff: Updated patch for default branch

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = None
closed_at = <Date 2011-04-04.00:22:00.668>
created_at = <Date 2011-03-26.22:09:20.469>
labels = ['type-feature', 'library']
title = 'SQLite trace callback'
updated_at = <Date 2011-04-04.00:22:00.666>
user = 'https://github.com/Bluehorn'

bugs.python.org fields:

activity = <Date 2011-04-04.00:22:00.666>
actor = 'pitrou'
assignee = 'none'
closed = True
closed_date = <Date 2011-04-04.00:22:00.668>
closer = 'pitrou'
components = ['Library (Lib)']
creation = <Date 2011-03-26.22:09:20.469>
creator = 'torsten'
dependencies = []
files = ['21413', '21414', '21468']
hgrepos = []
issue_num = 11688
keywords = ['patch']
message_count = 14.0
messages = ['132275', '132276', '132332', '132353', '132552', '132604', '132608', '132610', '132893', '132894', '132903', '132907', '132908', '132910']
nosy_count = 4.0
nosy_names = ['ghaering', 'pitrou', 'torsten', 'python-dev']
pr_nums = []
priority = 'normal'
resolution = 'fixed'
stage = 'resolved'
status = 'closed'
superseder = None
type = 'enhancement'
url = 'https://bugs.python.org/issue11688'
versions = ['Python 3.3']

Bluehorn · 2011-03-26T22:09:20Z

I'd like to access the SQLite trace callback from Python to actually see the same queries that SQLite actually gets to see.

The C API of SQLite supports this via the sqlite3_trace function. I added support to this to the sqlite3 module plus unit tests testing that it is called, can be removed again and that unicode arguments arrive correctly at the callback.

Please consider applying the patch. I am not sure if this should go to the pysqlite project on Google Code directly, I am just submitting it here because I worked in the python source tree as I am more used to its build system. I am sorry, Gerhard, if this is the wrong way.

Bluehorn · 2011-03-26T22:11:22Z

Here is the same change for Python 2.7. I don't expect this to get merged on the Python 2 branch, put perhaps it is useful to somebody.

pitrou · 2011-03-27T14:31:53Z

Thanks for the patch.

A couple of comments:

this is a new feature, so can only go in in 3.x: no need to post a 2.7 patch (unless this helps Gerhard for his standalone project)
you need to document the new API in Doc/library/sqlite3.rst

About the patch: looks mostly good!

+ self.assertTrue([x for x in traced_statements if x.find("create table foo") != -1])

This looks a bit complicated, why not something like
any("create table foo" in x for x in traced_statements)?

(y in x is simper and more readable than x.find(y) != -1)

+ sqlite3_trace(self->db, _trace_callback, trace_callback);
+ if (PyDict_SetItem(self->function_pinboard, trace_callback, Py_None) == -1)
+ return NULL;

Shouldn't sqlite3_trace() be called only after PyDict_SetItem() succeeds?

Bluehorn · 2011-03-27T17:38:22Z

A couple of comments:

this is a new feature, so can only go in in 3.x: no need to post a 2.7 patch (unless this helps Gerhard for his standalone project)

The motivation for the 2.7er patch is mostly that we are still using Python 2.x at work and I made this patch for 2.7 first. Actually I will need to backport it to 2.6 but I guess there are no differences. And maybe I should have submitted this against pysqlite directly which is (AFAICT) also still targetting 2.x.

you need to document the new API in Doc/library/sqlite3.rst

Sure. I was just filing the report to have the code on file and this was only fallout from another patch. I will create an updated patch including documentation about the feature.

About the patch: looks mostly good!

Thanks.

   self.assertTrue([x for x in traced_statements if x.find("create table foo") != -1])
This looks a bit complicated, why not something like
any("create table foo" in x for x in traced_statements)?

Fine with me. I did not know that "bar" in "foobarbaz" works (I was expecting it to act as if the right hand string is a list of characters). Obviously I was wrong. Further I thought "any" was new in 2.6 and therefore not suitable for use in pysqlite which was also wrong.

(y in x is simper and more readable than x.find(y) != -1)

I agree, I just did not know it works for substrings.

   sqlite3_trace(self-\>db, \_trace_callback, trace_callback);

   if (PyDict_SetItem(self-\>function_pinboard, trace_callback, Py_None) == -1)

```
       return NULL;
```

Shouldn't sqlite3_trace() be called only after PyDict_SetItem() succeeds?

Good catch. If SetItem fails, trace_callback may become invalid and the _trace_callback will crash. I did not think about that as I only derived that function from similar code in the module. I will have to fix this as well.

Bluehorn · 2011-03-29T23:02:03Z

you need to document the new API in Doc/library/sqlite3.rst

Included in the updated patch.

   self.assertTrue([x for x in traced_statements if x.find("create table foo") != -1])
This looks a bit complicated, why not something like
any("create table foo" in x for x in traced_statements)?

Fixed.

   sqlite3_trace(self-\>db, \_trace_callback, trace_callback);

   if (PyDict_SetItem(self-\>function_pinboard, trace_callback, Py_None) == -1)

```
       return NULL;
```

Shouldn't sqlite3_trace() be called only after PyDict_SetItem() succeeds?

Fixed as well. I just reversed the calls. What I dislike about this function pinboard approach is that every function registered as a callback stays pinned to the SQLite connection for the lifetime of the latter. But that belongs into another patch, I guess.

pitrou · 2011-03-30T19:01:00Z

The patch looks good to me, thank you!
Gerhard, would you like to tackle this? Otherwise I'll commit in a couple of days.

Bluehorn · 2011-03-30T19:40:26Z

The patch looks good to me, thank you!
Gerhard, would you like to tackle this? Otherwise I'll commit in a couple of days.

What I am still wondering about is if it would make sense to use the text factory here. It might make sense to pass through the bytes directly without translation into unicode (especially for big amounts).

OTOH, this is mostly a debugging aid and nothing that would be enabled in production. And premature optimization is the root of all evil...

pitrou · 2011-03-30T19:52:06Z

OTOH, this is mostly a debugging aid and nothing that would be enabled
in production. And premature optimization is the root of all evil...

Agreed.

python-dev · 2011-04-03T22:13:07Z

New changeset 575ee55081dc by Antoine Pitrou in branch 'default':
Issue bpo-11688: Add sqlite3.Connection.set_trace_callback(). Patch by Torsten Landschoff.
http://hg.python.org/cpython/rev/575ee55081dc

pitrou · 2011-04-03T22:14:13Z

Patch committed, thank you!

pitrou · 2011-04-03T23:10:31Z

Reopening, a failure appeared on some of the buildbots. I've made the failure message a bit more explicit:

test_sqlite: testing with version '2.6.0', sqlite_version '3.6.12'
[...]

======================================================================
FAIL: CheckUnicodeContent (sqlite3.test.hooks.TraceCallbackTests)
----------------------------------------------------------------------

Traceback (most recent call last):
  File "/Users/buildbot/buildarea/3.x.parc-snowleopard-1/build/Lib/sqlite3/test/hooks.py", line 220, in CheckUnicodeContent
    % (ascii(unicode_value), ', '.join(map(ascii, traced_statements))))
AssertionError: False is not true : Unicode data '\xf6\xe4\xfc\xd6\xc4\xdc\xdf\u20ac' garbled in trace callback: 'create table foo(x)', 'BEGIN ', 'insert into foo(x) values (?)', 'COMMIT'

See e.g. http://www.python.org/dev/buildbot/all/builders/AMD64%20Snow%20Leopard%202%203.x/builds/168/steps/test/logs/stdio

pitrou · 2011-04-03T23:42:26Z

Ok, it seems that expanding the value of bound parameters in the statement passed to the trace callback is a recent SQLite feature:

http://www.sqlite.org/draft/releaselog/3_6_21.html
“The SQL output resulting from sqlite3_trace() is now modified to include the values of bound parameters.”

python-dev · 2011-04-03T23:50:58Z

New changeset ce37570768f5 by Antoine Pitrou in branch 'default':
Fix TraceCallbackTests to not use bound parameters (followup to issue bpo-11688)
http://hg.python.org/cpython/rev/ce37570768f5

pitrou · 2011-04-04T00:22:01Z

Should be fixed now.

Bluehorn mannequin added stdlib Python modules in the Lib dir type-feature A feature request or enhancement labels Mar 26, 2011

benjaminp assigned ghaering Mar 26, 2011

pitrou closed this as completed Apr 3, 2011

pitrou unassigned ghaering Apr 3, 2011

pitrou reopened this Apr 3, 2011

pitrou closed this as completed Apr 4, 2011

ezio-melotti transferred this issue from another repository Apr 10, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SQLite trace callback #55897

SQLite trace callback #55897

Bluehorn mannequin commented Mar 26, 2011

Bluehorn mannequin commented Mar 26, 2011

Bluehorn mannequin commented Mar 26, 2011

pitrou commented Mar 27, 2011

Bluehorn mannequin commented Mar 27, 2011

Bluehorn mannequin commented Mar 29, 2011

pitrou commented Mar 30, 2011

Bluehorn mannequin commented Mar 30, 2011

pitrou commented Mar 30, 2011

python-dev mannequin commented Apr 3, 2011

pitrou commented Apr 3, 2011

pitrou commented Apr 3, 2011

pitrou commented Apr 3, 2011

python-dev mannequin commented Apr 3, 2011

pitrou commented Apr 4, 2011

SQLite trace callback #55897

SQLite trace callback #55897

Comments

Bluehorn mannequin commented Mar 26, 2011

Bluehorn mannequin commented Mar 26, 2011

Bluehorn mannequin commented Mar 26, 2011

pitrou commented Mar 27, 2011

Bluehorn mannequin commented Mar 27, 2011

Bluehorn mannequin commented Mar 29, 2011

pitrou commented Mar 30, 2011

Bluehorn mannequin commented Mar 30, 2011

pitrou commented Mar 30, 2011

python-dev mannequin commented Apr 3, 2011

pitrou commented Apr 3, 2011

pitrou commented Apr 3, 2011

pitrou commented Apr 3, 2011

python-dev mannequin commented Apr 3, 2011

pitrou commented Apr 4, 2011