Today, I pondered the question if there were a possibility to make PulpFiction behave as I want it.
The case is the following: Whenever an already present article of a feed is updated, PulpFiction would not indicate this. The article would just remain silently and only an occasional peek at it would reveal that something had changed.
Under the hood, however, PF would duly track the “modified” date (if present) and the real changes as well.
Luckily for me, I had a discussion about SQLite’s capabilities with Ölbaum. He shortly mentioned that he used
[CREATE TRIGGER] to create a trigger on a
VIEW. (This basically translates to “Why work yourself if the database will do it for you?” 1)
Later today, I was thinking about that
UPDATE scenario again and suddenly got hooked on that
[CREATE TRIGGER] command. My use case had the exact properties for such a
TRIGGER: The database gets an
UPDATE and does some magic itself in case a specific set of expressions validates.
Et voilà, here’s the SQL Code I came up with:
CREATE TRIGGER update_article_read_status UPDATE ON articles WHEN old.modified <> new.modified OR old.data <> new.data OR old.title <> new.title BEGINUPDATE articles SET read = 0 WHERE aid = new.aid; END;
Put this into the database schema and you’re done. Whenever an article gets an update, it will be marked as unread.
One open point is performance. I do not yet know how the whole system will react to tons of
UPDATEs … We’ll see :)
There’s basically a second possibility for the above code that could improve the speed as well. Here’s how it looks like:
CREATE TRIGGER update_article_read_status UPDATE OF modified, data, title ON articles WHEN ( old.modified <> new.modified OR old.data <> new.data OR old.title <> new.title ) AND old.fid <> -3 BEGINUPDATE articles SET read = 0 WHERE aid = new.aid; END;
TRIGGER only fires if one of the really interesting columns changes and won’t keep the database busy if only a flag or a label is set.
Update: They work both. No idea if N° 2 is faster. It probably is, as only updates of the content and date will trigger the
UPDATE. Use it at your own risk.
I also modified the last
TRIGGER so that it won’t update articles that are already in the Trash (
fid = -3), based on the assumption that you normally won’t have a look at already deleted articles.