Adding the SQLite storage to Mwuki was (after actually installing the BBCode parser extension for PHP) one of the most time-consuming parts of writing the script, even though it uses up very very little code. Partly this is because I'm very new to SQL and always have to look stuff up when I try writing it (I forgot half of what I learnt the last time I used it), and partly because the PHP SQLite interface has really shitty error messages that were very little use to figuring out what I'd done wrong.
Not a lot I can do to address the latter, other than note that the "last error" string you get with the last_error() function, is the same as the main SQL warning message it prints for you on the webpage, and the error string, if you give the function one to fill in, is the same as the not-bolded part of the SQL warning message it prints. In other words, unless the script is going to interpret these messages directly (rather than giving them to you), they are completely redundant.
But this page is mostly here to address the former: info about using SQL and its synax. This will also mostly apply to MySQL when I get access to that. Except I think that is more strongly-typed than SQLite (not difficult, everything is...)
SQL Syntax
SQLite's SQL syntax page which is terribly hard to work with.
CREATE TABLE sometable (field [type] [, field[type]+]);
INSERT INTO sometable VALUES (fieldvalue, fieldvalue);
DELETE FROM sometable WHERE (selection expression);
SELECT (field(s) or * for row) FROM sometable WHERE (selection expression);
Selection expressions: If you want to match a field against a value, QUOTE THE VALUE, don't just escape it. Although you must of course escape it unless it's given as a literal, in order to avoid the risk of SQL Injection Attacks(TM).
Hence: WHERE page='hamster page' uses quotes around the value
but WHERE money>sense would be appropriate for comparing different fields to each other, even though it does not use quotes.
record replacement
Record replacement in SQLite is awkward, because field identifiers are not normally unique, and if they are it seems to make everything a metric crapton slower. So I simply delete and then reinsert. I'm not sure if this is any improvement, it seems unlikely. I think the thing I had found was faster in the past, was simply dropping entire tables when refilling them from scratch. That may have been necessary for speed because there you'd be writing enormous amounts in one sitting, and each addition would be having to check against more and more items. Deletion would have to check against more items when rewriting a page in a fuller database, but you only do one page at a time there. So anyway I'm not sure that deleting one record and rewriting it is any different at all to doing insert-or-replace with unique fields, nor if it would be necessary to worry for one record.
I wrote that some time ago now, and it looks rather as though I turn round and contradict myself. I don't really remember what the deal was there. As I say, I think I was referring to the thing I found in writing another SQLite-based script that had to do a lot of inserts to rebuild a table, and it was far, far faster to drop the whole table and restart it from scratch, than to make the keys unique (so that rows would be replaced).