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 CREATE TABLE cipher(
  42         rowid INTEGER PRIMARY KEY CHECK (rowid = 0), -- limit to 1 row
  43         url TEXT,
  44         sts INTEGER,
  45         algorithm TEXT);
  46
  47 -- -------------- --
  48 -- REDDIT RELATED --
  49 -- -------------- --
  50
  51 CREATE TABLE subreddits(
  52         subreddit STRING COLLATE NOCASE, -- e.g. videos (not /r/...)
  53         user STRING,
  54         PRIMARY KEY (subreddit, user));
  55
  56 -- ------ --
  57 -- SHARED --
  58 -- ------ --
  59
  60 CREATE TABLE users(
  61         id INTEGER PRIMARY KEY AUTOINCREMENT,
  62         name TEXT NOT NULL,
  63         password TEXT NOT NULL,
  64         token TEXT NOT NULL); -- TODO: move into user_tokens table
  65 -- CREATE TABLE user_tokens( -- stores retractable url tokens for feeds.
  66 --      user_id INTEGER NOT NULL,
  67 --      token TEXT NOT NULL);