Feeds

[moshee]

Instead of (or maybe in addition to) lists, I think it would be nice to try implementing feeds. Rather than lists of series, these would be descriptions, constraints, filters.

• A feed could capture all of one or more tags, authors, etc. Any metadata that's already available that makes sense.
• Feeds may be sortable by anything.
• Feeds may be made public or private.
• Feeds may be subscribed to. This will manifest itself as a frontpage item and a unique RSS feed link.
  • The frontpage items can be enabled or disabled. Any feeds may be chosen to show up, including such pre-defined feeds as recent releases and such. A complete list of the pre-defined feeds and a user's feeds may be found on their profile.
  • In the case of a private RSS feed, consider giving each user a unique key like on private torrent trackers. This would be part of the request URL of any RSS feed requested by any user—in the case that they have permission to view that particular feed, it will show up. Otherwise there will be some sort of error. Public feeds would accept any key (unless the user is banned or something).
• Owners of a feed may allow certain people to edit them.

What first comes to mind for the database side:

CREATE TABLE books.feeds (
    id serial PRIMARY KEY,
    kind integer NOT NULL DEFAULT 0, -- Predefined, user, ...
    key bytea NOT NULL UNIQUE, -- A random string of bytes that identifies this feed (don't use the id)
    spec text NOT NULL, -- Could be in any format. Dunno yet. Description of feed contents.
    owner integer NOT NULL REFERENCES books.users,
    name text,
    description text,
    date_created timestamptz NOT NULL DEFAULT 'now'::timestamptz
)

CREATE TABLE books.feed_permissions (
    id serial PRIMARY KEY,
    feed_id integer NOT NULL REFERENCES books.feeds,
    user_id integer NOT NULL REFERENCES books.users,
    action integer NOT NULL -- allow, disallow, ...
)

[moshee]

Brainstorming

• Different input data sets (include 1 or more, exclude 0 or more). This determines which table to join to.
  • author (series property, requires join to series_credits and authors)
  • series (easy)
  • group (release property, requires join to translation_groups and releases_translators)
  • tag (series property, requires join to book_tags and book_tag_names)
  • demographic (series property)
  • magazine (series property, requires join to magazines)
  • publisher (series property, requires join to magazines and publishers)
• Different output data sets. This determines which table to query from.
  • releases (sort by release_date)
  • series (sort by date_added)
• Sort by time descending (must be able to identify which column is "time" in each output data set case)
  • Sorting is weird. If it were sortable by anything, it would be a list, not a feed. Lists would be nice too, but where does the similarity end? Should they be associated with the same type?
• Accompanying RSS feed with something akin to API tokens for public/private control
• Cache query results per user (perhaps into another table)
  • What to cache?
  • IDs?
  • JSON?
    • Risk duplicating the entire database many times?
  • How many rows to cache?
  • cache all new stuff?
  • how often to update? put another way, whose feed to update when?
  • Update if user.last_viewed_time_staleness > 'some duration', else serve cached?

The feed spec would need

• A list of things to include (and exclude, if any)
• The input and output data sets
• Cache information

Something to think about in the far future: related feeds. A feed that displays releases/series info of series related to one or more series.

Release feeds

SELECT *
FROM
    recent_releases releases,
    -- ...
WHERE...

author → releases

releases.series_id = series.id
author.id = credits.author_id
    AND credits.series_id = series.id
    AND author.id = $1

series → releases

releases.series_id = series.id
series.id = $1

group → releases

releases.translator_id = releases_translators.translator_id
    AND releases_translators.translator_id = $1

tag → releases

releases.series_id = series.id
series.id = tag.series_id
    AND tag_names.id = tag.tag_id
    AND tag_names.name = $1

demographic → releases

releases.series_id = series.id
    AND series.demographic = $1

Note: $1 is not a foreign key, but an enum, in this case.

magazine → releases

releases.series_id = series.id
series.magazine_id = magazine.id
    AND magazine.id = $1

publishers → releases

releases.series_id = series.id
series.magazine_id = magazine.id
    AND magazine.publisher_id = publisher.id
    AND publisher.id = $1

Series feeds

SELECT DISTINCT ON (series.id)
    series.id,
    series.name,
    -- ...
FROM
    book_series series,
    -- ...
WHERE...

In many cases, the series queries are degenerate cases of the release ones, just lacking the join to releases.

author → series

author.id = credits.author_id
    AND credits.series_id = series.id
    AND author.id = $1

series → series

series.id = $1

group → series

releases.series_id = series.id
releases.translator_id = releases_translators.translator_id
    AND releases_translators.translator_id = $1

tag → series

series.id = tag.series_id
    AND tag_names.id = tag.tag_id
    AND tag_names.name = $1

demographic → series

series.demographic = $1

Note: $1 is not a foreign key, but an enum, in this case.

magazine → series

series.magazine_id = magazine.id
    AND magazine.id = $1

publishers → series

series.magazine_id = magazine.id
    AND magazine.publisher_id = publisher.id
    AND publisher.id = $1