PulpFiction is teaching me SQL

[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 SELECTs 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 lidof 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