limit feeds and delete materialized views

This reverts commit 8f279745db.

config/config.example.yml

@@ -151,6 +151,26 @@ db:
 ##
 domain:
 
+##
+## List of alternative domains where the invidious instance is being served.
+## This needs to be set in order to be able to login and update user preferences
+## when using a domain that is not the same as the `domain` configuration,
+## like a .`onion` address, `.i2p` address, `.b32.i2p` address, etc.
+##
+## It will detect the alternative domain trough the `X-Forwarded-Host` header.
+## https://developer.mozilla.org/en-US/docs/Web/HTTP/Reference/Headers/X-Forwarded-Host
+##
+## Accepted values: a list of fully qualified domain names (FQDN)
+## Default: <none>
+##
+## Example:
+## alternative_domains:
+##   - invidious.example.com
+##   - inv.example.com
+##   - videos.example.com
+##
+alternative_domains:
+
 ##
 ## Tell Invidious that it is behind a proxy that provides only
 ## HTTPS, so all links must use the https:// scheme. This

config/migrate-scripts/migrate-db-8bc91ce.sh

@@ -0,0 +1,6 @@
+CREATE INDEX channel_videos_ucid_published_idx
+  ON public.channel_videos
+  USING btree
+  (ucid COLLATE pg_catalog."default", published);
+
+DROP INDEX channel_videos_ucid_idx;

config/sql/channel_videos.sql

@@ -19,12 +19,12 @@ CREATE TABLE IF NOT EXISTS public.channel_videos
 
 GRANT ALL ON TABLE public.channel_videos TO current_user;
 
--- Index: public.channel_videos_ucid_idx
+-- Index: public.channel_videos_ucid_published_idx
 
--- DROP INDEX public.channel_videos_ucid_idx;
+-- DROP INDEX public.channel_videos_ucid_published_idx;
 
-CREATE INDEX IF NOT EXISTS channel_videos_ucid_idx
+CREATE INDEX IF NOT EXISTS channel_videos_ucid_published_idx
   ON public.channel_videos
   USING btree
-  (ucid COLLATE pg_catalog."default");
+  (ucid COLLATE pg_catalog."default", published);
 

kubernetes/values.yaml

@@ -0,0 +1,60 @@
+name: invidious
+
+image:
+  repository: quay.io/invidious/invidious
+  tag: latest
+  pullPolicy: Always
+
+replicaCount: 1
+
+autoscaling:
+  enabled: false
+  minReplicas: 1
+  maxReplicas: 16
+  targetCPUUtilizationPercentage: 50
+
+service:
+  type: ClusterIP
+  port: 3000
+  #loadBalancerIP:
+
+resources: {}
+  #requests:
+  #  cpu: 100m
+  #  memory: 64Mi
+  #limits:
+  #  cpu: 800m
+  #  memory: 512Mi
+
+securityContext:
+  allowPrivilegeEscalation: false
+  runAsUser: 1000
+  runAsGroup: 1000
+  fsGroup: 1000
+
+# See https://github.com/bitnami/charts/tree/master/bitnami/postgresql
+postgresql:
+  image:
+    tag: 13
+  auth:
+    username: kemal
+    password: kemal
+    database: invidious
+  primary:
+    initdb:
+      username: kemal
+      password: kemal
+      scriptsConfigMap: invidious-postgresql-init
+
+# Adapted from ../config/config.yml
+config:
+  channel_threads: 1
+  db:
+    user: kemal
+    password: kemal
+    host: invidious-postgresql
+    port: 5432
+    dbname: invidious
+  full_refresh: false
+  https_only: false
+  domain:

src/invidious.cr

@@ -107,14 +107,6 @@ Kemal.config.extra_options do |parser|
       exit
     end
   end
-  parser.on("-f THREADS", "--feed-threads=THREADS", "Number of threads for refreshing feeds (default: #{CONFIG.feed_threads})") do |number|
-    begin
-      CONFIG.feed_threads = number.to_i
-    rescue ex
-      puts "THREADS must be integer"
-      exit
-    end
-  end
   parser.on("-o OUTPUT", "--output=OUTPUT", "Redirect output (default: #{CONFIG.output})") do |output|
     CONFIG.output = output
   end
@@ -166,10 +158,6 @@ if CONFIG.channel_threads > 0
   Invidious::Jobs.register Invidious::Jobs::RefreshChannelsJob.new(PG_DB)
 end
 
-if CONFIG.feed_threads > 0
-  Invidious::Jobs.register Invidious::Jobs::RefreshFeedsJob.new(PG_DB)
-end
-
 if CONFIG.statistics_enabled
   Invidious::Jobs.register Invidious::Jobs::StatisticsRefreshJob.new(PG_DB, SOFTWARE)
 end

src/invidious/config.cr

@@ -95,8 +95,6 @@ class Config
   # Time interval between two executions of the job that crawls channel videos (subscriptions update).
   @[YAML::Field(converter: Preferences::TimeSpanConverter)]
   property channel_refresh_interval : Time::Span = 30.minutes
-  # Number of threads to use for updating feeds
-  property feed_threads : Int32 = 1
   # Log file path or STDOUT
   property output : String = "STDOUT"
   # Default log level, valid YAML values are ints and strings, see src/invidious/helpers/logger.cr
@@ -121,6 +119,8 @@ class Config
   property hmac_key : String = ""
   # Domain to be used for links to resources on the site where an absolute URL is required
   property domain : String?
+  # Additional domain list that is going to be used for cookie domain validation
+  property alternative_domains : Array(String) = [] of String
   # Subscribe to channels using PubSubHubbub (requires domain, hmac_key)
   property use_pubsub_feeds : Bool | Int32 = false
   property popular_enabled : Bool = true

src/invidious/jobs/refresh_feeds_job.cr

@@ -1,75 +0,0 @@
-class Invidious::Jobs::RefreshFeedsJob < Invidious::Jobs::BaseJob
-  private getter db : DB::Database
-
-  def initialize(@db)
-  end
-
-  def begin
-    max_fibers = CONFIG.feed_threads
-    active_fibers = 0
-    active_channel = ::Channel(Bool).new
-
-    loop do
-      db.query("SELECT email FROM users WHERE feed_needs_update = true OR feed_needs_update IS NULL") do |rs|
-        rs.each do
-          email = rs.read(String)
-          view_name = "subscriptions_#{sha256(email)}"
-
-          if active_fibers >= max_fibers
-            if active_channel.receive
-              active_fibers -= 1
-            end
-          end
-
-          active_fibers += 1
-          spawn do
-            begin
-              # Drop outdated views
-              column_array = Invidious::Database.get_column_array(db, view_name)
-              ChannelVideo.type_array.each_with_index do |name, i|
-                if name != column_array[i]?
-                  LOGGER.info("RefreshFeedsJob: DROP MATERIALIZED VIEW #{view_name}")
-                  db.exec("DROP MATERIALIZED VIEW #{view_name}")
-                  raise "view does not exist"
-                end
-              end
-
-              if !db.query_one("SELECT pg_get_viewdef('#{view_name}')", as: String).includes? "WHERE ((cv.ucid = ANY (u.subscriptions))"
-                LOGGER.info("RefreshFeedsJob: Materialized view #{view_name} is out-of-date, recreating...")
-                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
-              # Rename old views
-              begin
-                legacy_view_name = "subscriptions_#{sha256(email)[0..7]}"
-
-                db.exec("SELECT * FROM #{legacy_view_name} LIMIT 0")
-                LOGGER.info("RefreshFeedsJob: RENAME MATERIALIZED VIEW #{legacy_view_name}")
-                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.info("RefreshFeedsJob: CREATE #{view_name}")
-                    db.exec("CREATE MATERIALIZED VIEW #{view_name} AS #{MATERIALIZED_VIEW_SQL.call(email)}")
-                    db.exec("UPDATE users SET feed_needs_update = false WHERE email = $1", email)
-                  end
-                rescue ex
-                  LOGGER.error("RefreshFeedJobs: REFRESH #{email} : #{ex.message}")
-                end
-              end
-            end
-
-            active_channel.send(true)
-          end
-        end
-      end
-
-      sleep 5.seconds
-      Fiber.yield
-    end
-  end
-end

src/invidious/routes/account.cr

@@ -123,10 +123,8 @@ module Invidious::Routes::Account
       return error_template(400, ex)
     end
 
-    view_name = "subscriptions_#{sha256(user.email)}"
     Invidious::Database::Users.delete(user)
     Invidious::Database::SessionIDs.delete(email: user.email)
-    PG_DB.exec("DROP MATERIALIZED VIEW #{view_name}")
 
     env.request.cookies.each do |cookie|
       cookie.expires = Time.utc(1990, 1, 1)

src/invidious/routes/before_all.cr

@@ -32,6 +32,8 @@ module Invidious::Routes::BeforeAll
     env.response.headers["X-XSS-Protection"] = "1; mode=block"
     env.response.headers["X-Content-Type-Options"] = "nosniff"
 
+    env.set "header_x-forwarded-host", env.request.headers["X-Forwarded-Host"]?
+
     # Only allow the pages at /embed/* to be embedded
     if env.request.resource.starts_with?("/embed")
       frame_ancestors = "'self' file: http: https:"

src/invidious/routes/feeds.cr

@@ -320,7 +320,7 @@ module Invidious::Routes::Feeds
         case attribute.name
         when "url", "href"
           request_target = URI.parse(node[attribute.name]).request_target
-          query_string_opt = request_target.starts_with?("/watch?v=") ? "&#{params}" : ""
+          query_string_opt = request_target.starts_with?("/watch?v=") ? ("&#{params}" if !params.empty?) : ""
           node[attribute.name] = "#{HOST_URL}#{request_target}#{query_string_opt}"
         else nil # Skip
         end

src/invidious/routes/login.cr

@@ -26,6 +26,7 @@ module Invidious::Routes::Login
 
   def self.login(env)
     locale = env.get("preferences").as(Preferences).locale
+    host = env.get("header_x-forwarded-host")
 
     referer = get_referer(env, "/feed/subscriptions")
 
@@ -57,7 +58,11 @@ module Invidious::Routes::Login
           sid = Base64.urlsafe_encode(Random::Secure.random_bytes(32))
           Invidious::Database::SessionIDs.insert(sid, email)
 
-          env.response.cookies["SID"] = Invidious::User::Cookies.sid(CONFIG.domain, sid)
+          if alt = CONFIG.alternative_domains.index(host)
+            env.response.cookies["SID"] = Invidious::User::Cookies.sid(CONFIG.alternative_domains[alt], sid)
+          else
+            env.response.cookies["SID"] = Invidious::User::Cookies.sid(CONFIG.domain, sid)
+          end
         else
           return error_template(401, "Wrong username or password")
         end
@@ -120,10 +125,11 @@ module Invidious::Routes::Login
         Invidious::Database::Users.insert(user)
         Invidious::Database::SessionIDs.insert(sid, email)
 
-        view_name = "subscriptions_#{sha256(user.email)}"
-        PG_DB.exec("CREATE MATERIALIZED VIEW #{view_name} AS #{MATERIALIZED_VIEW_SQL.call(user.email)}")
-
-        env.response.cookies["SID"] = Invidious::User::Cookies.sid(CONFIG.domain, sid)
+        if alt = CONFIG.alternative_domains.index(host)
+          env.response.cookies["SID"] = Invidious::User::Cookies.sid(CONFIG.alternative_domains[alt], sid)
+        else
+          env.response.cookies["SID"] = Invidious::User::Cookies.sid(CONFIG.domain, sid)
+        end
 
         if env.request.cookies["PREFS"]?
           user.preferences = env.get("preferences").as(Preferences)

src/invidious/routes/preferences.cr

@@ -231,7 +231,12 @@ module Invidious::Routes::PreferencesRoute
         File.write("config/config.yml", CONFIG.to_yaml)
       end
     else
-      env.response.cookies["PREFS"] = Invidious::User::Cookies.prefs(CONFIG.domain, preferences)
+      host = env.get("header_x-forwarded-host")
+      if alt = CONFIG.alternative_domains.index(host)
+        env.response.cookies["PREFS"] = Invidious::User::Cookies.prefs(CONFIG.alternative_domains[alt], preferences)
+      else
+        env.response.cookies["PREFS"] = Invidious::User::Cookies.prefs(CONFIG.domain, preferences)
+      end
     end
 
     env.redirect referer
@@ -266,7 +271,12 @@ module Invidious::Routes::PreferencesRoute
         preferences.dark_mode = "dark"
       end
 
-      env.response.cookies["PREFS"] = Invidious::User::Cookies.prefs(CONFIG.domain, preferences)
+      host = env.get("header_x-forwarded-host")
+      if alt = CONFIG.alternative_domains.index(host)
+        env.response.cookies["PREFS"] = Invidious::User::Cookies.prefs(CONFIG.alternative_domains[alt], preferences)
+      else
+        env.response.cookies["PREFS"] = Invidious::User::Cookies.prefs(CONFIG.domain, preferences)
+      end
     end
 
     if redirect

src/invidious/search/processors.cr

@@ -37,18 +37,18 @@ module Invidious::Search
 
     # Search inside of user subscriptions
     def subscriptions(query : Query, user : Invidious::User) : Array(ChannelVideo)
-      view_name = "subscriptions_#{sha256(user.email)}"
-
       return PG_DB.query_all("
         SELECT id,title,published,updated,ucid,author,length_seconds
         FROM (
-          SELECT *,
-          to_tsvector(#{view_name}.title) ||
-          to_tsvector(#{view_name}.author)
-          as document
-          FROM #{view_name}
-        ) v_search WHERE v_search.document @@ plainto_tsquery($1) LIMIT 20 OFFSET $2;",
-        query.text, (query.page - 1) * 20,
+          SELECT cv.*,
+          to_tsvector(cv.title) ||
+          to_tsvector(cv.author) AS document
+          FROM channel_videos cv
+          JOIN users ON cv.ucid = any(users.subscriptions)
+          WHERE users.email = $1 AND published > now() - interval '1 month'
+          ORDER BY published
+        ) v_search WHERE v_search.document @@ plainto_tsquery($2) LIMIT 20 OFFSET $3;",
+        user.email, query.text, (query.page - 1) * 20,
         as: ChannelVideo
       )
     end

src/invidious/user/cookies.cr

@@ -6,17 +6,24 @@ struct Invidious::User
 
     # Note: we use ternary operator because the two variables
     # used in here are not booleans.
-    SECURE = (Kemal.config.ssl || CONFIG.https_only) ? true : false
+    @@secure = (Kemal.config.ssl || CONFIG.https_only) ? true : false
 
     # Session ID (SID) cookie
     # Parameter "domain" comes from the global config
     def sid(domain : String?, sid) : HTTP::Cookie
+      # Not secure if it's being accessed from I2P
+      # Browsers expect the domain to include https. On I2P there is no HTTPS
+      # Tor browser works fine with secure being true
+      if (domain.try &.split(".").last == "i2p") && @@secure
+        @@secure = false
+      end
+
       return HTTP::Cookie.new(
         name: "SID",
         domain: domain,
         value: sid,
         expires: Time.utc + 2.years,
-        secure: SECURE,
+        secure: @@secure,
         http_only: true,
         samesite: HTTP::Cookie::SameSite::Lax
       )
@@ -25,12 +32,19 @@ struct Invidious::User
     # Preferences (PREFS) cookie
     # Parameter "domain" comes from the global config
     def prefs(domain : String?, preferences : Preferences) : HTTP::Cookie
+      # Not secure if it's being accessed from I2P
+      # Browsers expect the domain to include https. On I2P there is no HTTPS
+      # Tor browser works fine with secure being true
+      if (domain.try &.split(".").last == "i2p") && @@secure
+        @@secure = false
+      end
+
       return HTTP::Cookie.new(
         name: "PREFS",
         domain: domain,
         value: URI.encode_www_form(preferences.to_json),
         expires: Time.utc + 2.years,
-        secure: SECURE,
+        secure: @@secure,
         http_only: false,
         samesite: HTTP::Cookie::SameSite::Lax
       )

src/invidious/users.cr

@@ -27,7 +27,6 @@ def get_subscription_feed(user, max_results = 40, page = 1)
   offset = (page - 1) * limit
 
   notifications = Invidious::Database::Users.select_notifications(user)
-  view_name = "subscriptions_#{sha256(user.email)}"
 
   if user.preferences.notifications_only && !notifications.empty?
     # Only show notifications
@@ -53,33 +52,39 @@ def get_subscription_feed(user, max_results = 40, page = 1)
         # Show latest video from a channel that a user hasn't watched
         # "unseen_only" isn't really correct here, more accurate would be "unwatched_only"
 
-        if user.watched.empty?
-          values = "'{}'"
-        else
-          values = "VALUES #{user.watched.map { |id| %(('#{id}')) }.join(",")}"
-        end
-        videos = PG_DB.query_all("SELECT DISTINCT ON (ucid) * FROM #{view_name} WHERE NOT id = ANY (#{values}) ORDER BY ucid, published DESC", as: ChannelVideo)
+        # "SELECT cv.* FROM channel_videos cv JOIN users ON cv.ucid = any(users.subscriptions) WHERE users.email = $1 AND published > now() - interval '1 month' ORDER BY published DESC"
+        # "SELECT DISTINCT ON (cv.ucid) cv.* FROM channel_videos cv JOIN users ON cv.ucid = any(users.subscriptions) WHERE users.email = ? AND NOT cv.id = any(users.watched) AND published > now() - interval '1 month' ORDER BY ucid, published DESC"
+        videos = PG_DB.query_all("SELECT DISTINCT ON (cv.ucid) cv.* " \
+                                 "FROM channel_videos cv " \
+                                 "JOIN users ON cv.ucid = any(users.subscriptions) " \
+                                 "WHERE users.email = $1 AND NOT cv.id = any(users.watched) AND published > now() - interval '1 month' " \
+                                 "ORDER BY ucid, published DESC", user.email, as: ChannelVideo)
       else
         # Show latest video from each channel
 
-        videos = PG_DB.query_all("SELECT DISTINCT ON (ucid) * FROM #{view_name} ORDER BY ucid, published DESC", as: ChannelVideo)
+        videos = PG_DB.query_all("SELECT DISTINCT ON (cv.ucid) cv.* " \
+                                 "FROM channel_videos cv " \
+                                 "JOIN users ON cv.ucid = any(users.subscriptions) " \
+                                 "WHERE users.email = $1 AND published > now() - interval '1 month' " \
+                                 "ORDER BY ucid, published DESC", user.email, as: ChannelVideo)
       end
 
       videos.sort_by!(&.published).reverse!
     else
       if user.preferences.unseen_only
         # Only show unwatched
-
-        if user.watched.empty?
-          values = "'{}'"
-        else
-          values = "VALUES #{user.watched.map { |id| %(('#{id}')) }.join(",")}"
-        end
-        videos = PG_DB.query_all("SELECT * FROM #{view_name} WHERE NOT id = ANY (#{values}) ORDER BY published DESC LIMIT $1 OFFSET $2", limit, offset, as: ChannelVideo)
+        videos = PG_DB.query_all("SELECT cv.* " \
+                                 "FROM channel_videos cv " \
+                                 "JOIN users ON cv.ucid = any(users.subscriptions) " \
+                                 "WHERE users.email = $1 AND NOT cv.id = any(users.watched) AND published > now() - interval '1 month' " \
+                                 "ORDER BY published DESC LIMIT $2 OFFSET $3", user.email, limit, offset, as: ChannelVideo)
       else
         # Sort subscriptions as normal
-
-        videos = PG_DB.query_all("SELECT * FROM #{view_name} ORDER BY published DESC LIMIT $1 OFFSET $2", limit, offset, as: ChannelVideo)
+        videos = PG_DB.query_all("SELECT cv.* " \
+                                 "FROM channel_videos cv " \
+                                 "JOIN users ON cv.ucid = any(users.subscriptions) " \
+                                 "WHERE users.email = $1 AND published > now() - interval '1 month' " \
+                                 "ORDER BY published DESC LIMIT $2 OFFSET $3", user.email, limit, offset, as: ChannelVideo)
       end
     end