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,
24 shorts
BOOLEAN DEFAULT NULL,
26 crawled DATETIME
DEFAULT CURRENT_TIMESTAMP);
27 CREATE TABLE IF NOT EXISTS playlist_videos(
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(
41 display
TEXT CHECK(display
IN (NULL, 'pinned', 'hidden')),
42 PRIMARY KEY (user, video_id
));
48 CREATE TABLE IF NOT EXISTS subreddits(
49 subreddit STRING
COLLATE NOCASE
, -- e.g. videos (not /r/...)
51 PRIMARY KEY (subreddit
, user));
57 CREATE TABLE IF NOT EXISTS captcha_requests(
61 timestamp DATETIME
DEFAULT CURRENT_TIMESTAMP);
62 CREATE TABLE IF NOT EXISTS captcha_cookies(
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,
79 CREATE TABLE IF NOT EXISTS user_settings( -- stores per-user settings as a vertical table.
81 setting
TEXT NOT NULL,
83 PRIMARY KEY(user_id
, setting
),
84 FOREIGN KEY(user_id
) REFERENCES users(id));