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(
   9         id STRING PRIMARY KEY,
  10         name STRING,
  11         author STRING);
  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(
  18         id STRING PRIMARY KEY,
  19         channel_id STRING,
  20         title STRING,
  21         published DATETIME,
  22         crawled DATETIME DEFAULT CURRENT_TIMESTAMP);
  23 CREATE TABLE playlist_videos(
  24         video_id STRING,
  25         playlist_id STRING,
  26         PRIMARY KEY (video_id, playlist_id));
  27 CREATE TABLE crawler(
  28         channel_id STRING PRIMARY KEY, -- TODO: rename so it makes sense for playlists
  29         crawled_at DATETIME DEFAULT CURRENT_TIMESTAMP);
  30 CREATE TABLE websub(
  31         channel_id STRING PRIMARY KEY,
  32         subscribed_until DATETIME DEFAULT CURRENT_TIMESTAMP,
  33         pending BOOLEAN DEFAULT 0);
  34 CREATE TABLE flags(
  35         user STRING,
  36         video_id STRING,
  37         display TEXT CHECK(display IN (NULL, 'pinned', 'hidden')),
  38         PRIMARY KEY (user, video_id));
  39 CREATE TABLE cipher(
  40         rowid INTEGER PRIMARY KEY CHECK (rowid = 0), -- limit to 1 row
  41         url TEXT,
  42         sts INTEGER,
  43         algorithm TEXT);
  44
  45 -- -------------- --
  46 -- REDDIT RELATED --
  47 -- -------------- --
  48
  49 CREATE TABLE subreddits(
  50         subreddit STRING COLLATE NOCASE, -- e.g. videos (not /r/...)
  51         user STRING,
  52         PRIMARY KEY (subreddit, user));
  53
  54 -- ------ --
  55 -- SHARED --
  56 -- ------ --
  57
  58 CREATE TABLE users(
  59         id INTEGER PRIMARY KEY AUTOINCREMENT,
  60         name TEXT NOT NULL,
  61         password TEXT NOT NULL,
  62         token TEXT NOT NULL); -- TODO: move into user_tokens table
  63 -- CREATE TABLE user_tokens( -- stores retractable url tokens for feeds.
  64 --      user_id INTEGER NOT NULL,
  65 --      token TEXT NOT NULL);