I would like some advice on how best to implement delays in writing to a SQLite file (in my case using PHP) to maximise concurrent access.
The web application I'm developing mainly issues quick reads and writes on a local SQLite file using PHP 5. This works satisfactorily. However in the future there may occasionally be a relative long running SELECT statement while performing some reporting analysis. I would like to reduce the potential contention with the long read blocking and therefore timing out a write operation. I'm not concerned about the small delay in the database being updated. I'm aware of one technique to create/use temporary tables using a select statement but I would like something more robust since the complexity of the long running SELECT statement is indeterminate since it is modifiable by an authorised user. One idea I had was to use a shared flag across the PHP processes. A potentially long running SELECT statement would set this flag to true. All write operations would check for this flag and on its value being true would open a new SQLite file and write the raw SQL strings to act as queue. Something similar to the Undo example comes to mind (http://www.sqlite.org/cvstrac/wiki?p=UndoRedo). On completion the long running SELECT statement would open the new SQLite file and "play" the SQL strings in order back into the original SQLite file. How would I write binary safe INSERT/UPDATES SQL statement like that in PHP? I'm open to other techniques particularly if they would be simpler to implement and manage! Nick ******************************************************************** This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ******************************************************************** _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
