Feed Reader

schema.sql 27 lines

CREATE TABLE feeds (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL /* Subscription title shown in the reader. */,
  url TEXT NOT NULL UNIQUE /* Subscription feed URL, stored as https. An http address is upgraded when subscribing. */,
  checked_at TEXT /* Last check attempt, UTC ISO 8601. */,
  error TEXT /* Last check error, or null after success. */,
  subscribed INTEGER NOT NULL DEFAULT 1 CHECK (subscribed IN (0, 1)) /* 0 retains item identity after unsubscribing. */
);
CREATE TABLE items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  feed_id INTEGER REFERENCES feeds(id) /* Subscription; null for standalone or file-imported items. */,
  guid TEXT,
  guid_is_perma_link INTEGER NOT NULL DEFAULT 1 CHECK (guid_is_perma_link IN (0, 1)),
  title TEXT,
  link TEXT,
  description TEXT,
  author TEXT,
  category TEXT,
  comments TEXT,
  pub_date TEXT,
  source TEXT /* RSS <source>: original publication channel, not the subscription. */,
  source_url TEXT /* RSS <source> URL: original publication feed, not the subscription URL. */,
  enclosure_url TEXT,
  enclosure_length INTEGER CHECK (enclosure_length >= 0),
  enclosure_type TEXT,
  CHECK (title IS NOT NULL OR description IS NOT NULL)
);