Update view names to avoid collisions

3 files changed, 33 insertions, 23 deletions

diff --git a/src/invidious.cr b/src/invidious.cr
index adc69829..2a314218 100644
--- a/src/invidious.cr
+++ b/src/invidious.cr
@@ -666,7 +666,7 @@ get "/search" do |env|
   user = env.get? "user"
   if user
     user = user.as(User)
-    view_name = "subscriptions_#{sha256(user.email)[0..7]}"
+    view_name = "subscriptions_#{sha256(user.email)}"
   end
 
   channel = nil
@@ -1114,7 +1114,7 @@ post "/login" do |env|
       PG_DB.exec("INSERT INTO users VALUES (#{args})", user_array)
       PG_DB.exec("INSERT INTO session_ids VALUES ($1, $2, $3)", sid, email, Time.now)
 
-      view_name = "subscriptions_#{sha256(user.email)[0..7]}"
+      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[]) \
@@ -1834,7 +1834,7 @@ post "/delete_account" do |env|
       next templated "error"
     end
 
-    view_name = "subscriptions_#{sha256(user.email)[0..7]}"
+    view_name = "subscriptions_#{sha256(user.email)}"
     PG_DB.exec("DELETE FROM users * WHERE email = $1", user.email)
     PG_DB.exec("DELETE FROM session_ids * WHERE email = $1", user.email)
     PG_DB.exec("DROP MATERIALIZED VIEW #{view_name}")
@@ -1969,7 +1969,7 @@ get "/feed/subscriptions" do |env|
 
     notifications = PG_DB.query_one("SELECT notifications FROM users WHERE email = $1", user.email,
       as: Array(String))
-    view_name = "subscriptions_#{sha256(user.email)[0..7]}"
+    view_name = "subscriptions_#{sha256(user.email)}"
 
     if preferences.notifications_only && !notifications.empty?
       # Only show notifications
@@ -2253,7 +2253,7 @@ get "/feed/private" do |env|
   sort = env.params.query["sort"]?
   sort ||= "published"
 
-  view_name = "subscriptions_#{sha256(user.email)[0..7]}"
+  view_name = "subscriptions_#{sha256(user.email)}"
 
   if latest_only
     videos = PG_DB.query_all("SELECT DISTINCT ON (ucid) * FROM #{view_name} \
diff --git a/src/invidious/helpers/jobs.cr b/src/invidious/helpers/jobs.cr
index ad9e8ada..9afd859f 100644
--- a/src/invidious/helpers/jobs.cr
+++ b/src/invidious/helpers/jobs.cr
@@ -54,7 +54,7 @@ def refresh_feeds(db, logger, max_threads = 1)
       db.query("SELECT email FROM users") do |rs|
         rs.each do
           email = rs.read(String)
-          view_name = "subscriptions_#{sha256(email)[0..7]}"
+          view_name = "subscriptions_#{sha256(email)}"
 
           if active_threads >= max_threads
             if active_channel.receive
@@ -65,28 +65,38 @@ def refresh_feeds(db, logger, max_threads = 1)
           active_threads += 1
           spawn do
             begin
-              db.query("SELECT * FROM #{view_name} LIMIT 1") do |rs|
-                # Drop view that doesn't contain same number of rows as ChannelVideo
-                if ChannelVideo.from_rs(rs)[0]?.try &.to_a.size.try &.!= rs.column_count
+              # Drop outdated views
+              column_array = get_column_array(db, view_name)
+              ChannelVideo.to_type_tuple.each_with_index do |name, i|
+                if name != column_array[i]?
+                  logger.write("DROP MATERIALIZED VIEW #{view_name}\n")
                   db.exec("DROP MATERIALIZED VIEW #{view_name}")
-                  raise "valid schema does not exist"
+                  raise "view does not exist"
                 end
               end
 
               db.exec("REFRESH MATERIALIZED VIEW #{view_name}")
             rescue ex
-              # Create view if it doesn't exist
-              if ex.message.try &.ends_with?("does not exist")
-                # While iterating through, we may have an email stored from a deleted account
-                if db.query_one?("SELECT true FROM users WHERE email = $1", email, as: Bool)
-                  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;")
-                  logger.write("CREATE #{view_name}\n")
+              # Rename old views
+              begin
+                legacy_view_name = "subscriptions_#{sha256(email)[0..7]}"
+
+                db.exec("SELECT * FROM #{legacy_view_name} LIMIT 0")
+                logger.write("RENAME MATERIALIZED VIEW #{legacy_view_name}\n")
+                db.exec("ALTER MATERIALIZED VIEW #{legacy_view_name} RENAME TO #{view_name}")
+              rescue ex
+                begin
+                  # While iterating through, we may have an email stored from a deleted account
+                  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;")
+                  end
+                rescue ex
+                  logger.write("REFRESH #{email} : #{ex.message}\n")
                 end
-              else
-                logger.write("REFRESH #{email} : #{ex.message}\n")
               end
             end
 
diff --git a/src/invidious/users.cr b/src/invidious/users.cr
index ce0bd0ab..f34d14ab 100644
--- a/src/invidious/users.cr
+++ b/src/invidious/users.cr
@@ -125,7 +125,7 @@ def get_user(sid, headers, db, refresh = true)
       ON CONFLICT (id) DO NOTHING", sid, user.email, Time.now)
 
       begin
-        view_name = "subscriptions_#{sha256(user.email)[0..7]}"
+        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[]) \
@@ -147,7 +147,7 @@ def get_user(sid, headers, db, refresh = true)
     ON CONFLICT (id) DO NOTHING", sid, user.email, Time.now)
 
     begin
-      view_name = "subscriptions_#{sha256(user.email)[0..7]}"
+      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[]) \