SQL Magic - for me :)· Updated: · 3 min read
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
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
[CREATE TRIGGER] command. My use case had
the exact properties for such a
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
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 (
= -3), based on the assumption that you normally won’t
have a look at already deleted articles.
No comments. Be the first to add one!
Add a comment.