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

Comments.

No comments. Be the first to add one!
Add a comment.
We'll never share your email with anyone else. We use the Gravatar system to pull in pictures based on an anonymous hash.
Once you submit your comment, it will be moderated and then show up here shortly after.