Skip Menu |

This queue is for tickets about the DBD-SQLite CPAN distribution.

Report information
The Basics
Id: 5568
Status: resolved
Priority: 0/
Queue: DBD-SQLite

People
Owner: Nobody in particular
Requestors: chris [...] ex-parrot.com
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 0.31
Fixed in: (no value)



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;
From: david_dick [...] iprimus.com.au
Show quoted text
> 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.
Would it be possible to make this behaviour configurable at connection time? An application that I am responsible for uses "SELECT ... FOR UPDATE" queries. I am porting it to SQLite by stripping off the "FOR UPDATE" parts of a query, because i could rely on the DBD::SQLite driver compensating for it. With this patch, I would have no way forward. :)
From: david_dick [...] iprimus.com.au
disregard my last comment. :)