Subject: | DBD::SQLite, SELECT and transactions (with patch) |
The DBD::SQLite module starts a transaction even when the
statement to be executed is a SELECT, meaning that all
SELECTs on an SQLite database are serialised needlessly
when executed from perl.
(The problem is that the code starts a transaction for any
statement, since otherwise SQLite wraps each statement in
a transaction, which is not what we want unless AutoCommit
is turned on.)
The patch fixes this, by adding an is_select field to the
imp_sth_st structure, filled in by comparing the first
nonblank part of the statement to "SELECT" in a
case-insensitive fashion. I've tested this (but not
extensively) and it seems to work fine.
diff -urN DBD-SQLite-0.31/dbdimp.c DBD-SQLite-0.31-new/dbdimp.c
--- DBD-SQLite-0.31/dbdimp.c Sat Feb 14 17:36:38 2004
+++ DBD-SQLite-0.31-new/dbdimp.c Fri Mar 5 12:38:01 2004
@@ -189,6 +189,11 @@
chunk = NEWSV(0, strlen(statement));
sv_setpv(chunk, "");
+ if (strncasecmp(statement + strspn(statement, " \t\n"), "select", 6) == 0)
+ imp_sth->is_select = 1;
+ else
+ imp_sth->is_select = 0;
+
/* warn("parsing: %s\n", statement); */
while (*statement) {
@@ -376,7 +381,7 @@
}
/* warn("Executing: %s;\n", SvPV_nolen(sql)); */
- if ( (!DBIc_is(imp_dbh, DBIcf_AutoCommit)) && (!imp_dbh->in_tran) ) {
+ if ( (!DBIc_is(imp_dbh, DBIcf_AutoCommit)) && (!imp_dbh->in_tran) && (!imp_sth->is_select) ) {
if (retval = sqlite_exec(imp_dbh->db, "BEGIN TRANSACTION",
NULL, NULL, &errmsg)
!= SQLITE_OK)
diff -urN DBD-SQLite-0.31/dbdimp.h DBD-SQLite-0.31-new/dbdimp.h
--- DBD-SQLite-0.31/dbdimp.h Tue Aug 19 08:43:10 2003
+++ DBD-SQLite-0.31-new/dbdimp.h Fri Mar 5 12:33:58 2004
@@ -31,6 +31,7 @@
struct imp_sth_st {
dbih_stc_t com;
/* sqlite specific bits */
+ int is_select;
AV *sql;
sqlite_vm *vm;
char **results;