From dbacfa8f449c50a3e12d4bc257eb0641c163eade Mon Sep 17 00:00:00 2001 From: girst Date: Fri, 15 Oct 2021 03:05:19 +0200 Subject: [PATCH] allow running setup.sql/guest.sql multiple times --- config/guest.sql | 66 ++++++++++++++++++++++++------------------------ config/setup.sql | 28 ++++++++++---------- 2 files changed, 47 insertions(+), 47 deletions(-) diff --git a/config/guest.sql b/config/guest.sql index 088bd4d..4974cff 100644 --- a/config/guest.sql +++ b/config/guest.sql @@ -1,34 +1,34 @@ -INSERT INTO "subscriptions" VALUES('UCYO_jab_esuFRV4b17AJtAw','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCiDJtJKMICpb9B1qf7qjEOA','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCivA7_KLKWo43tFcCkFvydw','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCS0N5baNlQWJCUrhCEo8WlA','guest','channel'); -INSERT INTO "subscriptions" VALUES('UC2C_jShtL725hvbm1arSV9w','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCworsKCR-Sx6R6-BnIjS2MA','guest','channel'); -INSERT INTO "subscriptions" VALUES('UC9-y-6csu5WGm29I7JiwpnA','guest','channel'); -INSERT INTO "subscriptions" VALUES('UC2DjFE7Xf11URZqWBigcVOQ','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCJ0-OtVpF0wOKEqT2Z1HEtA','guest','channel'); -INSERT INTO "subscriptions" VALUES('UC2bkHVIDjXS7sgrgjFtzOXQ','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCnv0gfLQFNGPJ5MHSGuIAkw','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCXuqSBlHAE6Xw-yeJA0Tunw','guest','channel'); -INSERT INTO "subscriptions" VALUES('UClcE-kVhqyiHCcjYwcpfj9w','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCm9K6rby98W8JigLoZOh6FQ','guest','channel'); -INSERT INTO "subscriptions" VALUES('UC1O0jDlG51N3jGf6_9t-9mw','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCckETVOT59aYw80B36aP9vw','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCFhXFikryT4aFcLkLw2LBLA','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCNyGbxoEo6CQvaRVEvItxkA','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCMOqf8ab-42UUQIdVoKwjlQ','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCfa7jJFYnn3P5LdJXsFkrjw','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCCbvX81KC1LCOn8jjsqoR7Q','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCy0tKL1T7wFoYcxCe0xjN6Q','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCUK0HBIBWgM2c4vsPhkYY4w','guest','channel'); -INSERT INTO "subscriptions" VALUES('UC5NO8MgTQKHAWXp6z8Xl7yQ','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCBa659QWEk1AI4Tg--mrJ2A','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCEbYhDd6c6vngsF5PQpFVWg','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCHsqqRjziUaMwZgQZHw-n1w','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCcXhhVwCT6_WqjkEniejRJQ','guest','channel'); -INSERT INTO "subscriptions" VALUES('UCEIwxahdLz7bap-VDs9h35A','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCYO_jab_esuFRV4b17AJtAw','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCiDJtJKMICpb9B1qf7qjEOA','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCivA7_KLKWo43tFcCkFvydw','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCS0N5baNlQWJCUrhCEo8WlA','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UC2C_jShtL725hvbm1arSV9w','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCworsKCR-Sx6R6-BnIjS2MA','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UC9-y-6csu5WGm29I7JiwpnA','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UC2DjFE7Xf11URZqWBigcVOQ','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCJ0-OtVpF0wOKEqT2Z1HEtA','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UC2bkHVIDjXS7sgrgjFtzOXQ','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCnv0gfLQFNGPJ5MHSGuIAkw','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCXuqSBlHAE6Xw-yeJA0Tunw','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UClcE-kVhqyiHCcjYwcpfj9w','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCm9K6rby98W8JigLoZOh6FQ','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UC1O0jDlG51N3jGf6_9t-9mw','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCckETVOT59aYw80B36aP9vw','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCFhXFikryT4aFcLkLw2LBLA','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCNyGbxoEo6CQvaRVEvItxkA','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCMOqf8ab-42UUQIdVoKwjlQ','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCfa7jJFYnn3P5LdJXsFkrjw','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCCbvX81KC1LCOn8jjsqoR7Q','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCy0tKL1T7wFoYcxCe0xjN6Q','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCUK0HBIBWgM2c4vsPhkYY4w','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UC5NO8MgTQKHAWXp6z8Xl7yQ','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCBa659QWEk1AI4Tg--mrJ2A','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCEbYhDd6c6vngsF5PQpFVWg','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCHsqqRjziUaMwZgQZHw-n1w','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCcXhhVwCT6_WqjkEniejRJQ','guest','channel'); +INSERT OR REPLACE INTO "subscriptions" VALUES('UCEIwxahdLz7bap-VDs9h35A','guest','channel'); -INSERT INTO "subreddits" VALUES('curiousvideos','guest'); -INSERT INTO "subreddits" VALUES('Documentaries','guest'); -INSERT INTO "subreddits" VALUES('mealtimevideos','guest'); -INSERT INTO "subreddits" VALUES('videos','guest'); +INSERT OR REPLACE INTO "subreddits" VALUES('curiousvideos','guest'); +INSERT OR REPLACE INTO "subreddits" VALUES('Documentaries','guest'); +INSERT OR REPLACE INTO "subreddits" VALUES('mealtimevideos','guest'); +INSERT OR REPLACE INTO "subreddits" VALUES('videos','guest'); diff --git a/config/setup.sql b/config/setup.sql index 5888a15..4d6ff93 100644 --- a/config/setup.sql +++ b/config/setup.sql @@ -2,19 +2,19 @@ -- YOUTUBE RELATED -- -- --------------- -- -CREATE TABLE channels( +CREATE TABLE IF NOT EXISTS channels( id STRING PRIMARY KEY, name STRING); -CREATE TABLE playlists( +CREATE TABLE IF NOT EXISTS playlists( id STRING PRIMARY KEY, name STRING, author STRING); -CREATE TABLE subscriptions( +CREATE TABLE IF NOT EXISTS subscriptions( channel_id STRING, -- TODO: rename so it makes sense for playlists user STRING, type TEXT DEFAULT 'channel' NOT NULL CHECK(type IN ('channel', 'playlist')), PRIMARY KEY (channel_id, user)); -CREATE TABLE videos( +CREATE TABLE IF NOT EXISTS videos( id STRING PRIMARY KEY, channel_id STRING, title STRING, @@ -22,18 +22,18 @@ CREATE TABLE videos( livestream BOOLEAN DEFAULT 0, published DATETIME, crawled DATETIME DEFAULT CURRENT_TIMESTAMP); -CREATE TABLE playlist_videos( +CREATE TABLE IF NOT EXISTS playlist_videos( video_id STRING, playlist_id STRING, PRIMARY KEY (video_id, playlist_id)); -CREATE TABLE crawler( +CREATE TABLE IF NOT EXISTS crawler( channel_id STRING PRIMARY KEY, -- TODO: rename so it makes sense for playlists crawled_at DATETIME DEFAULT CURRENT_TIMESTAMP); -CREATE TABLE websub( +CREATE TABLE IF NOT EXISTS websub( channel_id STRING PRIMARY KEY, subscribed_until DATETIME DEFAULT CURRENT_TIMESTAMP, pending BOOLEAN DEFAULT 0); -CREATE TABLE flags( +CREATE TABLE IF NOT EXISTS flags( user STRING, video_id STRING, display TEXT CHECK(display IN (NULL, 'pinned', 'hidden')), @@ -43,7 +43,7 @@ CREATE TABLE flags( -- REDDIT RELATED -- -- -------------- -- -CREATE TABLE subreddits( +CREATE TABLE IF NOT EXISTS subreddits( subreddit STRING COLLATE NOCASE, -- e.g. videos (not /r/...) user STRING, PRIMARY KEY (subreddit, user)); @@ -52,12 +52,12 @@ CREATE TABLE subreddits( -- ANTI CAPTCHA -- -- ------------ -- -CREATE TABLE captcha_requests( +CREATE TABLE IF NOT EXISTS captcha_requests( nonce STRING, url STRING, action STRING, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP); -CREATE TABLE captcha_cookies( +CREATE TABLE IF NOT EXISTS captcha_cookies( name STRING, value STRING); @@ -65,11 +65,11 @@ CREATE TABLE captcha_cookies( -- SHARED -- -- ------ -- -CREATE TABLE users( +CREATE TABLE IF NOT EXISTS users( id INTEGER PRIMARY KEY AUTOINCREMENT, - name TEXT NOT NULL, + name TEXT NOT NULL UNIQUE, password TEXT NOT NULL, token TEXT NOT NULL); -- TODO: deprecated; use users.id instead -CREATE TABLE user_tokens( -- stores revocable url tokens for feeds. +CREATE TABLE IF NOT EXISTS user_tokens( -- stores revocable url tokens for feeds. user_id INTEGER PRIMARY KEY NOT NULL, token TEXT NOT NULL); -- 2.39.3