[Date Prev][Date Next] [Thread Prev][Thread Next] [Date Index] [Thread Index]

Re: php and sqlite

Alan Chandler wrote:
I seem to be stuck (slightly) with converting an some postgres and mysql based php web applications to sqlite.

There seems to be two possible interfaces to the database SQLite3 and PDO

I have discovered that SQLite3 seems to set the busy timeout to 0 - meaning that if there is any other activity (from another web thread) on the database there is a strong chance of a lock failure. There doesn't seem to be way to change the value.

PDO, on the other hand seems to have this set at 60 seconds, and it is possible to change it, but doesn't seems to support the "reset" function to return a cursor to the begining of a rowset during a query.

Is there anyone more expert than me on this who can tell me whether I can either (a) get round the SQLite3 problem, or (b) get round the PDO problem.

I am going with PDO at the moment, because the reset problem is minor but it would be nice to know if I could solve the issue.

(I would also be interested to know if the PDO::beginTransaction and the equivalent PDO::commit and PDO:rollBack do anything different that just including a query with the sql commands in them).

I know this was a few days ago, and nobody replied, but I just thought I would share what I have learned since.

Neither PDO or SQLite3 really seamlessly handle parallel transactions. The underlying engine returns a busy to both PDO and SQLite3 when a lock contention occurs.

You really need to handle this by rolling back the transaction yourself and starting it again. That is - except for when the lock contention comes on the commit, when you just retry the commit until it gets the lock to enable it to happen.

Having understood how the underlying C API works, it would say that the SQLite3 is closer to it and am currently writing a class to manage it in the way most suitable for my application. The core of it is something like the following (not yet tested) where begin and commit are the names of preprepared begin and commit statements.

do {

    try {
    	if(!$this->begin->execute()) throw DBCheck("BEGIN");


        while (!$this->commit->execute()) {
    	    if($this->db->lastErrorCode() != SQLITE_BUSY) {
                throw DBError();
     } catch(DBCheck $e) {
        if($this->db->lastErrorCode() != SQLITE_BUSY) {
            throw DBError($e->getMessage());
     } catch(DBError $e) {
        throw new Exception("<p>DATABASE ERROR:".
     } catch(DBRollBack $e) {
     foreach($this->statements as $statement) {

} while(true);

Alan Chandler

Reply to: