Mobile offline RSS reader, Part 4

In part 3 of this series, Eric discussed three programs that do the back-end work of RSS aggregation - scanning feed lists, caching summaries and IDs, creating data files as we go.

Before we get into using that information in part 5, we need to adjust one of the schemas a bit.

The items table in each feed’s database is set up as:

CREATE VIRTUAL TABLE items 
USING zumero
(
  id TEXT PRIMARY KEY NOT NULL, 
  title TEXT NOT NULL,
  summary TEXT NOT NULL,
  pubdate_unix_time INTEGER NOT NULL
);

Where the id field comes from the feed items’s id or guid element, or from the first link found in the entry. In many RSS feeds, id will also be the permalink to the entry’s “destination”, e.g.

<item>
  <title>Enterprise mobile will have a lot of SQL going on</title>
  <guid>http://www.ericsink.com/entries/mobile_sql.html</guid>
  ...

But that’s not mandatory. For example:

<entry>
  <id>http://zumero.com/2013/04/04/just-released-the-zumero-development-server</id>
  <link type="text/html" rel="alternate" 
   href="http://zumero.com/2013/04/04/just-released-the-zumero-development-server.html"/>
  <title>Just Released: the Zumero Development Server</title>
  ...

We still want that unique id, but if a link is available, we’ll want to store that, too.

Easy enough when creating a new items table. We’ll alter z_rss_create.cs to read:

db.Execute(
        @"CREATE VIRTUAL TABLE 
        cur.items 
        USING zumero
        (
          id TEXT PRIMARY KEY NOT NULL, 
          title TEXT NOT NULL,
          summary TEXT NOT NULL,
          pubdate_unix_time INTEGER NOT NULL,
          permalink TEXT
        );"
        );

But what about items tables that already exist? Unfortunately, SQLite does not allow alter table ... add column to run against a virtual table like Zumero’s. We’ll use the Zumero-provided alternative, zumero_alter_table_add_column().

In z_rss_update.cs, we’ll see if the new column already exists:

var query = "pragma cur.table_info(\"items\")";
List<SQLiteConnection.ColumnInfo> cols = db.Query<SQLiteConnection.ColumnInfo> (query);

foreach (var c in cols) {
    found = (string.Compare ("permalink", c.Name, StringComparison.OrdinalIgnoreCase) == 0);
    if (found)
        break;
}

If not…

db.Execute("BEGIN TRANSACTION;");
db.ExecuteScalar<string>(@"select zumero_alter_table_add_column(
   'cur', 'items', 'permalink TEXT');");
db.Execute("COMMIT TRANSACTION;");

// after altering a zumero table, the dbfile connection must be reopened

db.Execute("DETACH cur;", dbfile_name_for_this_feed);
db.Execute("ATTACH ? AS cur;", dbfile_name_for_this_feed);

See this fork of z_rss for all the necessary code.

In part 5, we’ll see a simple iOS RSS reader that pulls these databases in the background, and lets you browse the feed summaries and link through to the full articles.