5 CREATE TABLE IF NOT EXISTS channels(
8 CREATE TABLE IF NOT EXISTS playlists(
12 CREATE TABLE IF NOT EXISTS subscriptions(
13 channel_id STRING
, -- TODO: rename so it makes sense for playlists
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,
22 livestream
BOOLEAN DEFAULT 0,
23 premiere
BOOLEAN DEFAULT 0,
25 crawled DATETIME
DEFAULT CURRENT_TIMESTAMP);
26 CREATE TABLE IF NOT EXISTS playlist_videos(
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(
40 display
TEXT CHECK(display
IN (NULL, 'pinned', 'hidden')),
41 PRIMARY KEY (user, video_id
));
47 CREATE TABLE IF NOT EXISTS subreddits(
48 subreddit STRING
COLLATE NOCASE
, -- e.g. videos (not /r/...)
50 PRIMARY KEY (subreddit
, user));
56 CREATE TABLE IF NOT EXISTS captcha_requests(
60 timestamp DATETIME
DEFAULT CURRENT_TIMESTAMP);
61 CREATE TABLE IF NOT EXISTS captcha_cookies(
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,