Skip Menu |

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

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

People
Owner: Nobody in particular
Requestors: ptushnik [...] gmail.com
Cc:
AdminCc:

Bug Information
Severity: Wishlist
Broken in: (no value)
Fixed in: (no value)



Subject: Fast DBH->do
This allow to use sqlite3_exec() directly from DBH->do. In result many commands executes faster, especially if you have big set of SQL command like "dump" file.
Subject: DBD-SQLite-1.14.fast_do
diff -pruN DBD-SQLite-1.14.orig/SQLite.xs DBD-SQLite-1.14/SQLite.xs --- DBD-SQLite-1.14.orig/SQLite.xs 2007-08-23 02:01:07.000000000 +0200 +++ DBD-SQLite-1.14/SQLite.xs 2008-02-09 09:36:53.000000000 +0100 @@ -63,6 +63,21 @@ busy_timeout(dbh, timeout=0) OUTPUT: RETVAL +void +_do(dbh, statement) + SV * dbh + char * statement + CODE: + { + D_imp_dbh(dbh); + IV retval; + retval = sqlite_db_do(dbh, imp_dbh, statement); + if (retval == 0) + XST_mPV(0, "0E0"); /* (true but zero) */ + else + XST_mUNDEF(0); /* <= -2 means error */ + } + MODULE = DBD::SQLite PACKAGE = DBD::SQLite::st PROTOTYPES: DISABLE diff -pruN DBD-SQLite-1.14.orig/dbdimp.c DBD-SQLite-1.14/dbdimp.c --- DBD-SQLite-1.14.orig/dbdimp.c 2007-08-24 04:51:25.000000000 +0200 +++ DBD-SQLite-1.14/dbdimp.c 2008-02-09 13:26:06.000000000 +0100 @@ -224,6 +224,25 @@ sqlite_db_commit(SV *dbh, imp_dbh_t *imp } int +sqlite_db_do(SV *dbh, imp_dbh_t *imp_dbh, char *statement) +{ + dTHR; + int retval; + char *errmsg; + + sqlite_trace(2, "DO"); + if ((retval = sqlite3_exec(imp_dbh->db, statement, + NULL, NULL, &errmsg)) + != SQLITE_OK) + { + sqlite_error(dbh, (imp_xxh_t*)imp_dbh, retval, errmsg); + return -2; + } + + return 0; +} + +int sqlite_discon_all(SV *drh, imp_drh_t *imp_drh) { dTHR; diff -pruN DBD-SQLite-1.14.orig/lib/DBD/SQLite.pm DBD-SQLite-1.14/lib/DBD/SQLite.pm --- DBD-SQLite-1.14.orig/lib/DBD/SQLite.pm 2007-08-23 02:11:41.000000000 +0200 +++ DBD-SQLite-1.14/lib/DBD/SQLite.pm 2008-02-09 09:37:12.000000000 +0100 @@ -62,6 +62,15 @@ sub connect { package DBD::SQLite::db; +sub do { + my($dbh, $statement, $attr, @params) = @_; + return DBD::SQLite::db::_do($dbh, $statement) unless defined $attr && $#params > -1; + my $sth = $dbh->prepare($statement, $attr) or return undef; + $sth->execute(@params) or return undef; + my $rows = $sth->rows; + ($rows == 0) ? "0E0" : $rows; +} + sub prepare { my ($dbh, $statement, @attribs) = @_;
We agree in principle, but this patch isn't good enough. Tests fail. Marking as stalled pending a better implementation of this.
Here is new patch that pass all tests. But discovered new problem: busy_timeout() doesn't work now. Looks like this is sqlite3 problem. I have asked about this at SQLite3-Maillist, waiting for answer... Here is example of this bug: -------%<-------------------------------------------------- #include <stdio.h> #include <sqlite3.h> int check_error (int rc, char *zErrMsg) { if( rc!=SQLITE_OK ){ fprintf(stderr, "SQL error: %s\n", zErrMsg); sqlite3_free(zErrMsg); } } int main(int argc, char **argv){ sqlite3 *db, *db2; char *zErrMsg = 0; int rc; rc = sqlite3_open("test.db", &db); rc = sqlite3_open("test.db", &db2); printf("db1 start trans\n"); rc = sqlite3_exec(db, "BEGIN TRANSACTION" , NULL, NULL, &zErrMsg); check_error(rc, zErrMsg); printf("db1 insert\n"); rc = sqlite3_exec(db, "INSERT INTO Blah VALUES ( 1, 'Test1' )" , NULL, NULL, &zErrMsg); check_error(rc, zErrMsg); sqlite3_busy_timeout(db2, 30000); printf("db2 start trans\n"); rc = sqlite3_exec(db2, "BEGIN TRANSACTION" , NULL, NULL, &zErrMsg); check_error(rc, zErrMsg); /* SQLITE should wait for 3 second before returning error, but it doesn't */ printf("db2 insert\n"); rc = sqlite3_exec(db2, "INSERT INTO Blah VALUES ( 1, 'Test1' )" , NULL, NULL, &zErrMsg); check_error(rc, zErrMsg); sqlite3_close(db); return 0; } -------%<--------------------------------------------------
Download DBD-SQLite-1.19_10.fast_do
application/octet-stream 2.6k

Message body not shown because it is not plain text.

From: ptushnik [...] gmail.com
This is know situation described at http://www.sqlite.org/c3ref/busy_handler.html The presence of a busy handler does not guarantee that it will be invoked when there is lock contention. If SQLite determines that invoking the busy handler could result in a deadlock, it will go ahead and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of invoking the busy handler. Consider a scenario where one process is holding a read lock that it is trying to promote to a reserved lock and a second process is holding a reserved lock that it is trying to promote to an exclusive lock. The first process cannot proceed because it is blocked by the second and the second process cannot proceed because it is blocked by the first. If both processes invoke the busy handlers, neither will make any progress. Therefore, SQLite returns SQLITE_BUSY for the first process, hoping that this will induce the first process to release its read lock and allow the second process to proceed. This is all means that now my patch work correctly. PS. My question at SQLite mail-list can be found here http://thread.gmane.org/gmane.comp.db.sqlite.general/45858
Implemented faster do in the master (sorry for the long delay). https://github.com/DBD-SQLite/DBD-SQLite/commit/7a234eb71b955064ce498e95d6ecc50d1f0580a7 On Mon Apr 06 07:52:36 2009, xrb wrote: Show quoted text
> Here is new patch that pass all tests. > But discovered new problem: busy_timeout() doesn't work now. > Looks like this is sqlite3 problem. I have asked about this at > SQLite3-Maillist, waiting for > answer... > > Here is example of this bug: > -------%<-------------------------------------------------- > #include <stdio.h> > #include <sqlite3.h> > > > int check_error (int rc, char *zErrMsg) > { > if( rc!=SQLITE_OK ){ > fprintf(stderr, "SQL error: %s\n", zErrMsg); > sqlite3_free(zErrMsg); > } > } > > int main(int argc, char **argv){ > sqlite3 *db, *db2; > char *zErrMsg = 0; > int rc; > > rc = sqlite3_open("test.db", &db); > rc = sqlite3_open("test.db", &db2); > > printf("db1 start trans\n"); > rc = sqlite3_exec(db, "BEGIN TRANSACTION" , NULL, NULL, &zErrMsg); > check_error(rc, zErrMsg); > > printf("db1 insert\n"); > rc = sqlite3_exec(db, "INSERT INTO Blah VALUES ( 1, 'Test1' )" , > NULL, NULL, &zErrMsg); > check_error(rc, zErrMsg); > > > sqlite3_busy_timeout(db2, 30000); > > printf("db2 start trans\n"); > rc = sqlite3_exec(db2, "BEGIN TRANSACTION" , NULL, NULL, &zErrMsg); > check_error(rc, zErrMsg); > > /* SQLITE should wait for 3 second before returning error, but it > doesn't */ > printf("db2 insert\n"); > rc = sqlite3_exec(db2, "INSERT INTO Blah VALUES ( 1, 'Test1' )" , > NULL, NULL, &zErrMsg); > check_error(rc, zErrMsg); > > sqlite3_close(db); > return 0; > } > > -------%<--------------------------------------------------
On Tue Feb 17 15:28:45 2015, ISHIGAKI wrote: Show quoted text
> Implemented faster do in the master (sorry for the long delay). > > https://github.com/DBD-SQLite/DBD- > SQLite/commit/7a234eb71b955064ce498e95d6ecc50d1f0580a7 > > On Mon Apr 06 07:52:36 2009, xrb wrote:
> > Here is new patch that pass all tests. > > But discovered new problem: busy_timeout() doesn't work now. > > Looks like this is sqlite3 problem. I have asked about this at > > SQLite3-Maillist, waiting for > > answer... > > > > Here is example of this bug: > > -------%<-------------------------------------------------- > > #include <stdio.h> > > #include <sqlite3.h> > > > > > > int check_error (int rc, char *zErrMsg) > > { > > if( rc!=SQLITE_OK ){ > > fprintf(stderr, "SQL error: %s\n", zErrMsg); > > sqlite3_free(zErrMsg); > > } > > } > > > > int main(int argc, char **argv){ > > sqlite3 *db, *db2; > > char *zErrMsg = 0; > > int rc; > > > > rc = sqlite3_open("test.db", &db); > > rc = sqlite3_open("test.db", &db2); > > > > printf("db1 start trans\n"); > > rc = sqlite3_exec(db, "BEGIN TRANSACTION" , NULL, NULL, &zErrMsg); > > check_error(rc, zErrMsg); > > > > printf("db1 insert\n"); > > rc = sqlite3_exec(db, "INSERT INTO Blah VALUES ( 1, 'Test1' )" , > > NULL, NULL, &zErrMsg); > > check_error(rc, zErrMsg); > > > > > > sqlite3_busy_timeout(db2, 30000); > > > > printf("db2 start trans\n"); > > rc = sqlite3_exec(db2, "BEGIN TRANSACTION" , NULL, NULL, &zErrMsg); > > check_error(rc, zErrMsg); > > > > /* SQLITE should wait for 3 second before returning error, but it > > doesn't */ > > printf("db2 insert\n"); > > rc = sqlite3_exec(db2, "INSERT INTO Blah VALUES ( 1, 'Test1' )" , > > NULL, NULL, &zErrMsg); > > check_error(rc, zErrMsg); > > > > sqlite3_close(db); > > return 0; > > } > > > > -------%<--------------------------------------------------
Closed as 1.48 was out. Thanks.