config/setup.sql

   1 -- --------------- --
   2 -- YOUTUBE RELATED --
   3 -- --------------- --
   4
   5 CREATE TABLE channels(
   6         id STRING PRIMARY KEY,
   7         name STRING);
   8 -- CREATE TABLE playlists( -- TODO: table not deployed
   9 --      id STRING PRIMARY KEY,
  10 --      name STRING,
  11 --      owner STRING); -- that's the channel_id who created the playlist
  12 CREATE TABLE subscriptions(
  13         channel_id STRING, -- TODO: rename so it makes sense for playlists
  14         user STRING,
  15         type TEXT DEFAULT 'channel' NOT NULL CHECK(type IN ('channel', 'playlist')),
  16         PRIMARY KEY (channel_id, user));
  17 CREATE TABLE videos( -- TODO: which playlist a video is from (so unsubscribing is easy)
  18         id STRING PRIMARY KEY,
  19         channel_id STRING,
  20         title STRING,
  21         published DATETIME,
  22         crawled DATETIME DEFAULT CURRENT_TIMESTAMP);
  23 CREATE TABLE crawler(
  24         channel_id STRING PRIMARY KEY, -- TODO: rename so it makes sense for playlists
  25         crawled_at DATETIME DEFAULT CURRENT_TIMESTAMP);
  26 CREATE TABLE websub(
  27         channel_id STRING PRIMARY KEY, -- TODO: rename so it makes sense for playlists
  28         subscribed_until DATETIME DEFAULT CURRENT_TIMESTAMP,
  29         pending BOOLEAN DEFAULT 0);
  30 CREATE TABLE flags(
  31         user STRING,
  32         video_id STRING,
  33         display TEXT CHECK(display IN (NULL, 'pinned', 'hidden')),
  34         PRIMARY KEY (user, video_id));
  35 CREATE TABLE cipher(
  36         rowid INTEGER PRIMARY KEY CHECK (rowid = 0), -- limit to 1 row
  37         url TEXT,
  38         sts INTEGER,
  39         algorithm TEXT);
  40
  41 -- -------------- --
  42 -- REDDIT RELATED --
  43 -- -------------- --
  44
  45 CREATE TABLE subreddits(
  46         subreddit STRING COLLATE NOCASE, -- e.g. videos (not /r/...)
  47         user STRING,
  48         PRIMARY KEY (subreddit, user));
  49 CREATE TABLE reddit_posts(
  50         id STRING PRIMARY KEY, -- post id (without thing type 't3_')
  51         subreddit STRING,
  52         title STRING);
  53 CREATE TABLE reddit_flags(
  54         user STRING,
  55         post_id STRING,
  56         display TEXT CHECK(display IN (NULL, 'pinned', 'hidden')),
  57         PRIMARY KEY (user, post_id));
  58
  59 -- ------ --
  60 -- SHARED --
  61 -- ------ --
  62
  63 CREATE TABLE users(
  64         id INTEGER PRIMARY KEY AUTOINCREMENT,
  65         name TEXT NOT NULL,
  66         password TEXT NOT NULL,
  67         token TEXT NOT NULL); -- TODO: move into user_tokens table
  68 -- CREATE TABLE user_tokens( -- stores retractable url tokens for feeds.
  69 --      user_id INTEGER NOT NULL,
  70 --      token TEXT NOT NULL);