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