Hello,
The premise of this is the following: I'm using sqlite3 in a django
application. I'm having it log the SQL that it issues (once you get
passed its magical ORM setup), but that command does not work. I.e. I
have something like the following (the python is not important, but does
set the stage a little):
>>> regex = 'NET | INCOME'
>>> Table.objects.filter(name__iregex=regex).
When I have it print out the SQL sent, I get the following (this is a
simplified, but equivalent version):
SELECT *
FROM "table"
WHERE "table"."name"
REGEXP '(?i)' || 'NET | INCOME'
LIMIT 21
However if I execute that directly against the sqlite database i get the
following error:
sqlite3.OperationalError: no such function: REGEXP
I thought that maybe this magic was handled in python's sqlite3 module,
but if i do it there i get the same error.
Finally I realized it might just be easier to intercept the sqlite3 call
at the dynamic library level. To do this, I wrote the following code:
test.c
---------------------------------------
#define _GNU_SOURCE
#include <stdio.h>
#include <dlfcn.h>
#include <sqlite3.h>
SQLITE_API int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte,
sqlite3_stmt **ppStmt, const char
**pzTail) {
int (*original_sqlite3_prepare)(sqlite3*, const char *, int,
sqlite3_stmt**, const char **);
original_sqlite3_prepare = dlsym(RTLD_NEXT, "sqlite3_prepare");
printf("\n\n***ORIGINAL SQL QUERY***\n\n\n");
printf("%s", zSql);
printf("\n\n***END ORIGINAL SQL QUERY***\n\n\n");
fflush(stdout);
return (*original_sqlite3_prepare)(db, zSql, nByte, ppStmt, pzTail);
}
---------------------------------------
I compiled it as follows:
$ gcc -Wall -fPIC -shared -o mysqlite3_prepare.so test.c -ldl -lsqlite3
When I preload this before running my app, it prints out the following
(slightly cleaned up to make easier to read by removing a couple
irrelevant fields):
SELECT "table"."id", "table"."name", FROM "table" WHERE "table"."name"
REGEXP '(?i)' || ?
I'm not sure what to do with this. It's even missing the final part. So
obviously I'm looking at this in the wrong place. So I guess I have a
couple questions:
1) How can I sort out what the final query actually is? Possibly I
should be intercepting other calls or functions?
2) Is this obvious the readers here (thus making my other investigations
unnecessary) what's going on?
Thanks for any help!
Cheers,
Thomas
P.S. Appologies if this is not appropriate here? Maybe it should be
posted to sqlite-dev?
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
