LiveQuery

Items can go out of focus too, from delete or update.

I was thinking about this a bit, and at first blush, the only generally viable strategy seems to be to just re-run queries when things could have changed.

If a query has no join in it, then each time you insert/delete/update an item in the relevant table, you can check it against your condition explicitly and add it to or remove it from your result set.

If a query has a join in it, then the only useful invariant I can think of is that modifications to tables that aren't involved in the join can't affect the results. But any modification that touches those tables might, in arbitrarily complex ways. (I dunno if we hit the halting problem here, but it actually seems marginally plausible.)

This requires keeping the old result set around somehow. This could just be done in memory; a reasonably slick alternative, without the unbounded memory use, would be to stick it in a temporary table. (SQLite stores these on disk, in unlinked files.) Then you can directly select everything matching the query that is not in the other table, for instance, using EXCEPT or subselect; or just do a query on each, iterate over them in parallel to find added/removed items in a single pass with constant memory overhead.

A very basic start at using louie (http://louie.berlios.de/) for livequery

OK on further consideration I have no freaking idea how this is going to work. One day, though, one day...