Re: php and sqlite

[Alan Chandler <alan@chandlerfamily.org.uk>]

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");

	$this->doWork();

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

} while(true);




--
Alan Chandler
http://www.chandlerfamily.org.uk