PulpFiction 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!

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 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 – the object for the command
    Result: string – the reply for the command