Mis-coding SQLite/PHP leads to silent failure

Damn rookie errors! I spent over an hour last night trying to work out why my database code suddenly and mysteriously stopped working. It was compounded by the fact that I checked in a change and around the same time patched and rebooted my machine and mistakenly assumed that it was the latter and and not my dud code that was causing the problem!

function create() {
   $stmt = "CREATE TABLE if not exists Worklog(".
      "Id integer PRIMARY KEY, ".
      "Lastmod TIMESTAMP NOT NULL DEFAULT (strftime('%s', 'now')), ".
      "Start TIMESTAMP, ".
      "End TIMESTAMP, ".
      "Ticket text, ".
      "Desc text, ".
      "Cat1 text, ".
      "Cat2 text)";
   if (! $this->dbh->query($stmt)) {
      die("Cannot create table '$stmt': $error");
   }
   $stmt = "BEGIN TRANSACTION; ".
      "CREATE TRIGGER insert_worklog_lastmod ".
      "After update on Worklog begin update Worklog ".
      "set Lastmod = strftime('%s','now') where rowid=new.rowid; end; ".
      "COMMIT;";
   if (! $this->dbh->query($stmt)) {
      die("Cannot create trigger '$stmt': $error");
   }
}

(some lines edited/removed to simplify display)

So, in retrospect, yes I made a fine lot of errors for such a small snippet of code. But the major one, the show-stopper that was causing my CRUD to fail silently? Acting the real rookie, I tried to shove more than one statement into a query statement. In retrospect, it's pretty bloody obvious that the BEGIN TRANSACTION (which was only there I might add, because I lazily copied this trigger creation from another source) was going to be the only section of that statement applied. With the actual trigger creation and - most importantly - the COMMIT being rightly ignored.

Any wonder my application stopped writing: the whole damn thing was sitting on a TRANSACTION request that never completed.

So what other mistakes did I manage to fit in?

• Use of query rather than exec to execute a result-less query
• Constantly attempting to create the trigger (not protected by if not exists)