SQL Magic - for me :)

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?” ¹)

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;

This 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.