8 -- CREATE TABLE playlists( -- TODO: table not deployed
9 -- id STRING PRIMARY KEY,
11 -- owner STRING); -- that's the channel_id who created the playlist
12 CREATE TABLE 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 videos( -- TODO: which playlist a video is from (so unsubscribing is easy)
18 id STRING
PRIMARY KEY,
22 crawled DATETIME
DEFAULT CURRENT_TIMESTAMP);
24 channel_id STRING
PRIMARY KEY, -- TODO: rename so it makes sense for playlists
25 crawled_at DATETIME
DEFAULT CURRENT_TIMESTAMP);
27 channel_id STRING
PRIMARY KEY, -- TODO: rename so it makes sense for playlists
28 subscribed_until DATETIME
DEFAULT CURRENT_TIMESTAMP,
29 pending
BOOLEAN DEFAULT 0);
33 display
TEXT CHECK(display
IN (NULL, 'pinned', 'hidden')),
34 PRIMARY KEY (user, video_id
));
36 rowid INTEGER PRIMARY KEY CHECK (rowid = 0), -- limit to 1 row
45 CREATE TABLE subreddits(
46 subreddit STRING
COLLATE NOCASE
, -- e.g. videos (not /r/...)
48 PRIMARY KEY (subreddit
, user));
49 CREATE TABLE reddit_posts(
50 id STRING
PRIMARY KEY, -- post id (without thing type 't3_')
53 CREATE TABLE reddit_flags(
56 display
TEXT CHECK(display
IN (NULL, 'pinned', 'hidden')),
57 PRIMARY KEY (user, post_id
));
64 id INTEGER PRIMARY KEY AUTOINCREMENT
,
66 password TEXT NOT NULL,
67 token TEXT NOT NULL); -- TODO: move into user_tokens table
68 -- CREATE TABLE user_tokens( -- stores retractable url tokens for feeds.
69 -- user_id INTEGER NOT NULL,
70 -- token TEXT NOT NULL);