On Wed, Jul 2, 2008 at 4:58 AM, Dan <[EMAIL PROTECTED]> wrote: > The idea here is to use a temp table as a cache for write operations > on the > real table, correct? To minimize the number of writes (and therefore > fsync() > operations) on the real table? Yes, that is the overall goal.
> And the cache has to support both UPDATE and INSERT operations? How > about > DELETE? DELETE isn't something that I had thought about actually, but I think that we'd have to bite the bullet with delete and actually remove the record (or use a temp table to track which entries have been deleted, and remove those from results?) > Is the following correct? > > * When inserting a new row into the system, it should be added to the > temporary table. > > * When updating a row, if it is not already in the temp table, it > should > be copied from the real table into the temp table and then the temp > table copy updated. > > * When reading from the system, we want to return all the records > from > the temp table, and all those records from the "real" table that do > not have corresponding temp table records. Yes to all of the above. > I think that supporting the UPDATE operation makes it more difficult to > arrange all this using SQL triggers and views than it would be if each > record existed in either the temp or real tables (but not both). Fair. However, we are working with browser history, so I can't assume anything like that. > So we now have a system that supports UPDATE and INSERT. So long as one > doesn't UPDATE the table's primary key. Still not sure how to support > DELETE operations. This is actually OK. For our tables (http://dietrich.ganx4.com/mozilla/places-erd.png), we use integers as primary keys. However, we also have some indexes for uniqeness which would have to be enforced by a trigger now... > I'm starting to wonder if using an SQL view and triggers is the best way > to implement this. It seems pretty straightforward to support INSERT, > but UPDATE and DELETE start getting a bit complicated. It could be that > the logic for distributing records between the temporary and real > tables would be better done using a procedural programming language. > Either as part of application logic or as a virtual table. The insert is easy actually - we always insert into the temp table. The issues that we hit are: 1) Updating, which we have a solution for. 2) Performance - right now we see a big hit on read queries by selecting from the temp and permanent table. Cheers, Shawn _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
