config/setup.sql

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