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