config/setup.sql

   1 CREATE TABLE channels(
   2         id STRING PRIMARY KEY,
   3         name STRING);
   4 -- CREATE TABLE playlists( -- TODO: table not deployed
   5 --      id STRING PRIMARY KEY,
   6 --      name STRING,
   7 --      owner STRING); -- that's the channel_id who created the playlist
   8 CREATE TABLE subscriptions(
   9         channel_id STRING, -- TODO: rename so it makes sense for playlists
  10         user STRING,
  11         type TEXT DEFAULT 'channel' NOT NULL CHECK(type IN ('channel', 'playlist')),
  12         PRIMARY KEY (channel_id, user));
  13 CREATE TABLE videos( -- TODO: which playlist a video is from (so unsubscribing is easy)
  14         id STRING PRIMARY KEY,
  15         channel_id STRING,
  16         title STRING,
  17         published DATETIME,
  18         crawled DATETIME DEFAULT CURRENT_TIMESTAMP);
  19 CREATE TABLE crawler(
  20         channel_id STRING PRIMARY KEY, -- TODO: rename so it makes sense for playlists
  21         crawled_at DATETIME DEFAULT CURRENT_TIMESTAMP);
  22 CREATE TABLE websub(
  23         channel_id STRING PRIMARY KEY, -- TODO: rename so it makes sense for playlists
  24         subscribed_until DATETIME DEFAULT CURRENT_TIMESTAMP,
  25         pending BOOLEAN DEFAULT 0);
  26 CREATE TABLE flags(
  27         user STRING,
  28         video_id STRING,
  29         display TEXT CHECK(display IN (NULL, 'pinned', 'hidden')),
  30         PRIMARY KEY (user, video_id));
  31 CREATE TABLE cipher(
  32         url TEXT,
  33         sts INTEGER,
  34         algorithm TEXT);
  35 CREATE TABLE users(
  36         id INTEGER PRIMARY KEY AUTOINCREMENT,
  37         name TEXT NOT NULL,
  38         password TEXT NOT NULL,
  39         token TEXT NOT NULL); -- TODO: move into user_tokens table
  40 -- CREATE TABLE user_tokens( -- stores retractable url tokens for feeds.
  41 --      user_id INTEGER NOT NULL,
  42 --      token TEXT NOT NULL);