PulpFiction is teaching me SQL
· Updated: · 3 min read[PulpFiction][pf] still is my favourite newsreader – ever :) And it even has some side-benefits: I learn (better) SQL.
Yesterday, I found myself with a problem: I am subscribed to a number of high-volume, high-volatility feeds like [The Register][], MacNN or [Heise][].
As I am lazy, I normally don’t delete read articles as I skim through them when reading my news dose. Nevertheless, I want to get rid of most articles from these special subscriptions when I read them. So I came up with a plan to accomplish this:
- Assign a unique label to these subscriptions, something like “high-volume” or so* Move/delete these subscriptions’ articles when they are read but not flagged* Use the first to accomplish the second
Simple, huh? Because [sqlite][] was at hand, I decided to go
with a simple
UPDATE articles SET fid = -1 WHERE ...
, which was
easy to figure. I duly included the lid
(the id of
the unique label) in my WHERE
clause and added one
for being read/not flagged as well and … nothing!
All these shiny, yellow-tagged articles remained in their folders. But I didn’t want to employ AppleScript for that! It would not be right thing to do. It’s a bulk transition and SQL ist the language of choice for such stuff!
After some SELECT
s on the articles and some intense
moments of studying the database schema, I found out that the
setting of attributing a label on a subscription does not affect
the lid
of the articles. Instead, it only affects
the lid
on the subscriptions table.
CREATE TABLE articles ( ... lid INT, ...);
CREATE TABLE subscriptions ( ... lid INT, ...);
CREATE TABLE labels ( lid INTEGER PRIMARY KEY, name VARCHAR(255) NOT NULL, color CHAR(6) NOT NULL );
«Of course!», I thought, «I’ll only have to
SET
the fid
of the affected articles
to -3
and I’m done.» Only, how do I
get the affected articles? In my
articles
table, I don’t have any clue about
the label, and I’m not doing a simple
SELECT * FROM articles, subscriptions WHERE subscriptions.lid
= "high-volume";
where I can combine tables at my leisure. Bummer.
Well, to make a rather long story short: After some fiddling, I
found out that I can combine a carefully crafted
SELECT
statement with my
UPDATE
statement such that I obtain the desired
result – a very elegant SQL query:
UPDATE articles SET fid = -3 WHERE read = 1 AND flag = 0 AND lid IS NULLAND sid IN ( SELECT sid FROM subscriptions WHERE subscriptions.lid = 111 );
This query, executed within [sqlite][], will move all articles
whose subscription has the “high-volume” label (lid
111
) to the trash (folder ID -3
), as
long as they are read, not flagged and have no other label
attached to them. Sweet. Thanks, Erik, and Thanks,
[SQLite][sqlite]!
Oh, and by the way: I know that I would’ve found out about that in a good book about SQL. But I don’t own any of these.
PulpFiction 1.0.1 BETA#
[Erik][] has posted a rolling-beta of [PulpFiction][pf] over at
his blog. Amongst the many visible changes, there’s also a
new AppleScript command called do sql query
.
Sweet!
do sql query: Runs an SQL query on the database do sql query [reference]{style=“color: blue;”} – the object for the command Result: [string]{style=“color: blue;”} – the reply for the command
[pf]: http://www.freshlysqueezedsoftware.com/products/pulpfiction[nnw]: http://ranchero.com/netnewswire/ [FFS]: http://www.freshlysqueezedsoftware.com [The Register]: http://www.theregister.co.uk[Heise]: http://heise.de/newsticker[sqlite]: http://www.sqlite.org[Erik]: http://nslog.com
Comments.
No comments. Be the first to add one!
Add a comment.