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         length INTEGER,
  22         livestream BOOLEAN DEFAULT 0,
  23         published DATETIME,
  24         crawled DATETIME DEFAULT CURRENT_TIMESTAMP);
  25 CREATE TABLE playlist_videos(
  26         video_id STRING,
  27         playlist_id STRING,
  28         PRIMARY KEY (video_id, playlist_id));
  29 CREATE TABLE crawler(
  30         channel_id STRING PRIMARY KEY, -- TODO: rename so it makes sense for playlists
  31         crawled_at DATETIME DEFAULT CURRENT_TIMESTAMP);
  32 CREATE TABLE websub(
  33         channel_id STRING PRIMARY KEY,
  34         subscribed_until DATETIME DEFAULT CURRENT_TIMESTAMP,
  35         pending BOOLEAN DEFAULT 0);
  36 CREATE TABLE flags(
  37         user STRING,
  38         video_id STRING,
  39         display TEXT CHECK(display IN (NULL, 'pinned', 'hidden')),
  40         PRIMARY KEY (user, video_id));
  41
  42 -- -------------- --
  43 -- REDDIT RELATED --
  44 -- -------------- --
  45
  46 CREATE TABLE subreddits(
  47         subreddit STRING COLLATE NOCASE, -- e.g. videos (not /r/...)
  48         user STRING,
  49         PRIMARY KEY (subreddit, user));
  50
  51 -- ------------ --
  52 -- ANTI CAPTCHA --
  53 -- ------------ --
  54
  55 CREATE TABLE captcha_requests(
  56         nonce STRING,
  57         url STRING,
  58         action STRING,
  59         timestamp DATETIME DEFAULT CURRENT_TIMESTAMP);
  60 CREATE TABLE captcha_cookies(
  61         name STRING,
  62         value STRING);
  63
  64 -- ------ --
  65 -- SHARED --
  66 -- ------ --
  67
  68 CREATE TABLE users(
  69         id INTEGER PRIMARY KEY AUTOINCREMENT,
  70         name TEXT NOT NULL,
  71         password TEXT NOT NULL,
  72         token TEXT NOT NULL); -- TODO: deprecated; use users.id instead
  73 CREATE TABLE user_tokens( -- stores revocable url tokens for feeds.
  74         user_id INTEGER PRIMARY KEY NOT NULL,
  75         token TEXT NOT NULL);