diff options
| -rw-r--r-- | src/invidious.cr | 6 | ||||
| -rw-r--r-- | src/invidious/helpers/jobs.cr | 11 | ||||
| -rw-r--r-- | src/invidious/users.cr | 12 |
3 files changed, 17 insertions, 12 deletions
diff --git a/src/invidious.cr b/src/invidious.cr index 296db08a..0049bfdc 100644 --- a/src/invidious.cr +++ b/src/invidious.cr @@ -1224,9 +1224,9 @@ post "/login" do |env| view_name = "subscriptions_#{sha256(user.email)}" PG_DB.exec("CREATE MATERIALIZED VIEW #{view_name} AS \ - SELECT * FROM channel_videos WHERE \ - ucid = ANY ((SELECT subscriptions FROM users WHERE email = E'#{user.email.gsub("'", "\\'")}')::text[]) \ - ORDER BY published DESC;") + SELECT * FROM channel_videos WHERE + ucid IN (SELECT unnest(subscriptions) FROM users WHERE email = E'#{user.email.gsub("'", "\\'")}') + ORDER BY published DESC") if Kemal.config.ssl || config.https_only secure = true diff --git a/src/invidious/helpers/jobs.cr b/src/invidious/helpers/jobs.cr index 1dd81cf5..63f7c16f 100644 --- a/src/invidious/helpers/jobs.cr +++ b/src/invidious/helpers/jobs.cr @@ -74,6 +74,11 @@ def refresh_feeds(db, logger, config) end end + if db.query_one("SELECT pg_get_viewdef('#{view_name}')", as: String).includes? "ucid = ANY" + logger.write("Materialized view #{view_name} is out-of-date, recreating...\n") + db.exec("DROP MATERIALIZED VIEW #{view_name}") + end + db.exec("REFRESH MATERIALIZED VIEW #{view_name}") db.exec("UPDATE users SET feed_needs_update = false WHERE email = $1", email) rescue ex @@ -90,9 +95,9 @@ def refresh_feeds(db, logger, config) if db.query_one?("SELECT true FROM users WHERE email = $1", email, as: Bool) logger.write("CREATE #{view_name}\n") db.exec("CREATE MATERIALIZED VIEW #{view_name} AS \ - SELECT * FROM channel_videos WHERE \ - ucid = ANY ((SELECT subscriptions FROM users WHERE email = E'#{email.gsub("'", "\\'")}')::text[]) \ - ORDER BY published DESC;") + SELECT * FROM channel_videos WHERE + ucid IN (SELECT unnest(subscriptions) FROM users WHERE email = E'#{email.gsub("'", "\\'")}') + ORDER BY published DESC") db.exec("UPDATE users SET feed_needs_update = false WHERE email = $1", email) end rescue ex diff --git a/src/invidious/users.cr b/src/invidious/users.cr index ceaac9f1..a8764a14 100644 --- a/src/invidious/users.cr +++ b/src/invidious/users.cr @@ -149,9 +149,9 @@ def get_user(sid, headers, db, refresh = true) begin view_name = "subscriptions_#{sha256(user.email)}" db.exec("CREATE MATERIALIZED VIEW #{view_name} AS \ - SELECT * FROM channel_videos WHERE \ - ucid = ANY ((SELECT subscriptions FROM users WHERE email = E'#{user.email.gsub("'", "\\'")}')::text[]) \ - ORDER BY published DESC;") + SELECT * FROM channel_videos WHERE + ucid IN (SELECT unnest(subscriptions) FROM users WHERE email = E'#{user.email.gsub("'", "\\'")}') + ORDER BY published DESC") rescue ex end end @@ -171,9 +171,9 @@ def get_user(sid, headers, db, refresh = true) begin view_name = "subscriptions_#{sha256(user.email)}" db.exec("CREATE MATERIALIZED VIEW #{view_name} AS \ - SELECT * FROM channel_videos WHERE \ - ucid = ANY ((SELECT subscriptions FROM users WHERE email = E'#{user.email.gsub("'", "\\'")}')::text[]) \ - ORDER BY published DESC;") + SELECT * FROM channel_videos WHERE + ucid IN (SELECT unnest(subscriptions) FROM users WHERE email = E'#{user.email.gsub("'", "\\'")}') + ORDER BY published DESC") rescue ex end end |