diff options
-rw-r--r-- | config/migrate-scripts/migrate-db-8bc91ce.sh | 6 | ||||
-rw-r--r-- | config/sql/channel_videos.sql | 8 | ||||
-rw-r--r-- | kubernetes/values.yaml | 60 | ||||
-rw-r--r-- | src/invidious.cr | 12 | ||||
-rw-r--r-- | src/invidious/config.cr | 2 | ||||
-rw-r--r-- | src/invidious/jobs/refresh_feeds_job.cr | 75 | ||||
-rw-r--r-- | src/invidious/routes/account.cr | 2 | ||||
-rw-r--r-- | src/invidious/routes/login.cr | 3 | ||||
-rw-r--r-- | src/invidious/search/processors.cr | 18 | ||||
-rw-r--r-- | src/invidious/users.cr | 39 |
10 files changed, 101 insertions, 124 deletions
diff --git a/config/migrate-scripts/migrate-db-8bc91ce.sh b/config/migrate-scripts/migrate-db-8bc91ce.sh new file mode 100644 index 00000000..04388175 --- /dev/null +++ b/config/migrate-scripts/migrate-db-8bc91ce.sh | |||
@@ -0,0 +1,6 @@ | |||
1 | CREATE INDEX channel_videos_ucid_published_idx | ||
2 | ON public.channel_videos | ||
3 | USING btree | ||
4 | (ucid COLLATE pg_catalog."default", published); | ||
5 | |||
6 | DROP INDEX channel_videos_ucid_idx; \ No newline at end of file | ||
diff --git a/config/sql/channel_videos.sql b/config/sql/channel_videos.sql index cd4e0ffd..f2ac4876 100644 --- a/config/sql/channel_videos.sql +++ b/config/sql/channel_videos.sql | |||
@@ -19,12 +19,12 @@ CREATE TABLE IF NOT EXISTS public.channel_videos | |||
19 | 19 | ||
20 | GRANT ALL ON TABLE public.channel_videos TO current_user; | 20 | GRANT ALL ON TABLE public.channel_videos TO current_user; |
21 | 21 | ||
22 | -- Index: public.channel_videos_ucid_idx | 22 | -- Index: public.channel_videos_ucid_published_idx |
23 | 23 | ||
24 | -- DROP INDEX public.channel_videos_ucid_idx; | 24 | -- DROP INDEX public.channel_videos_ucid_published_idx; |
25 | 25 | ||
26 | CREATE INDEX IF NOT EXISTS channel_videos_ucid_idx | 26 | CREATE INDEX IF NOT EXISTS channel_videos_ucid_published_idx |
27 | ON public.channel_videos | 27 | ON public.channel_videos |
28 | USING btree | 28 | USING btree |
29 | (ucid COLLATE pg_catalog."default"); | 29 | (ucid COLLATE pg_catalog."default", published); |
30 | 30 | ||
diff --git a/kubernetes/values.yaml b/kubernetes/values.yaml new file mode 100644 index 00000000..17d69b5d --- /dev/null +++ b/kubernetes/values.yaml | |||
@@ -0,0 +1,60 @@ | |||
1 | name: invidious | ||
2 | |||
3 | image: | ||
4 | repository: quay.io/invidious/invidious | ||
5 | tag: latest | ||
6 | pullPolicy: Always | ||
7 | |||
8 | replicaCount: 1 | ||
9 | |||
10 | autoscaling: | ||
11 | enabled: false | ||
12 | minReplicas: 1 | ||
13 | maxReplicas: 16 | ||
14 | targetCPUUtilizationPercentage: 50 | ||
15 | |||
16 | service: | ||
17 | type: ClusterIP | ||
18 | port: 3000 | ||
19 | #loadBalancerIP: | ||
20 | |||
21 | resources: {} | ||
22 | #requests: | ||
23 | # cpu: 100m | ||
24 | # memory: 64Mi | ||
25 | #limits: | ||
26 | # cpu: 800m | ||
27 | # memory: 512Mi | ||
28 | |||
29 | securityContext: | ||
30 | allowPrivilegeEscalation: false | ||
31 | runAsUser: 1000 | ||
32 | runAsGroup: 1000 | ||
33 | fsGroup: 1000 | ||
34 | |||
35 | # See https://github.com/bitnami/charts/tree/master/bitnami/postgresql | ||
36 | postgresql: | ||
37 | image: | ||
38 | tag: 13 | ||
39 | auth: | ||
40 | username: kemal | ||
41 | password: kemal | ||
42 | database: invidious | ||
43 | primary: | ||
44 | initdb: | ||
45 | username: kemal | ||
46 | password: kemal | ||
47 | scriptsConfigMap: invidious-postgresql-init | ||
48 | |||
49 | # Adapted from ../config/config.yml | ||
50 | config: | ||
51 | channel_threads: 1 | ||
52 | db: | ||
53 | user: kemal | ||
54 | password: kemal | ||
55 | host: invidious-postgresql | ||
56 | port: 5432 | ||
57 | dbname: invidious | ||
58 | full_refresh: false | ||
59 | https_only: false | ||
60 | domain: | ||
diff --git a/src/invidious.cr b/src/invidious.cr index 3804197e..961ae872 100644 --- a/src/invidious.cr +++ b/src/invidious.cr | |||
@@ -103,14 +103,6 @@ Kemal.config.extra_options do |parser| | |||
103 | exit | 103 | exit |
104 | end | 104 | end |
105 | end | 105 | end |
106 | parser.on("-f THREADS", "--feed-threads=THREADS", "Number of threads for refreshing feeds (default: #{CONFIG.feed_threads})") do |number| | ||
107 | begin | ||
108 | CONFIG.feed_threads = number.to_i | ||
109 | rescue ex | ||
110 | puts "THREADS must be integer" | ||
111 | exit | ||
112 | end | ||
113 | end | ||
114 | parser.on("-o OUTPUT", "--output=OUTPUT", "Redirect output (default: #{CONFIG.output})") do |output| | 106 | parser.on("-o OUTPUT", "--output=OUTPUT", "Redirect output (default: #{CONFIG.output})") do |output| |
115 | CONFIG.output = output | 107 | CONFIG.output = output |
116 | end | 108 | end |
@@ -168,10 +160,6 @@ if CONFIG.channel_threads > 0 | |||
168 | Invidious::Jobs.register Invidious::Jobs::RefreshChannelsJob.new(PG_DB) | 160 | Invidious::Jobs.register Invidious::Jobs::RefreshChannelsJob.new(PG_DB) |
169 | end | 161 | end |
170 | 162 | ||
171 | if CONFIG.feed_threads > 0 | ||
172 | Invidious::Jobs.register Invidious::Jobs::RefreshFeedsJob.new(PG_DB) | ||
173 | end | ||
174 | |||
175 | if CONFIG.statistics_enabled | 163 | if CONFIG.statistics_enabled |
176 | Invidious::Jobs.register Invidious::Jobs::StatisticsRefreshJob.new(PG_DB, SOFTWARE) | 164 | Invidious::Jobs.register Invidious::Jobs::StatisticsRefreshJob.new(PG_DB, SOFTWARE) |
177 | end | 165 | end |
diff --git a/src/invidious/config.cr b/src/invidious/config.cr index c4ddcdb3..2e6df47a 100644 --- a/src/invidious/config.cr +++ b/src/invidious/config.cr | |||
@@ -62,8 +62,6 @@ class Config | |||
62 | # Time interval between two executions of the job that crawls channel videos (subscriptions update). | 62 | # Time interval between two executions of the job that crawls channel videos (subscriptions update). |
63 | @[YAML::Field(converter: Preferences::TimeSpanConverter)] | 63 | @[YAML::Field(converter: Preferences::TimeSpanConverter)] |
64 | property channel_refresh_interval : Time::Span = 30.minutes | 64 | property channel_refresh_interval : Time::Span = 30.minutes |
65 | # Number of threads to use for updating feeds | ||
66 | property feed_threads : Int32 = 1 | ||
67 | # Log file path or STDOUT | 65 | # Log file path or STDOUT |
68 | property output : String = "STDOUT" | 66 | property output : String = "STDOUT" |
69 | # Default log level, valid YAML values are ints and strings, see src/invidious/helpers/logger.cr | 67 | # Default log level, valid YAML values are ints and strings, see src/invidious/helpers/logger.cr |
diff --git a/src/invidious/jobs/refresh_feeds_job.cr b/src/invidious/jobs/refresh_feeds_job.cr deleted file mode 100644 index 4f8130df..00000000 --- a/src/invidious/jobs/refresh_feeds_job.cr +++ /dev/null | |||
@@ -1,75 +0,0 @@ | |||
1 | class Invidious::Jobs::RefreshFeedsJob < Invidious::Jobs::BaseJob | ||
2 | private getter db : DB::Database | ||
3 | |||
4 | def initialize(@db) | ||
5 | end | ||
6 | |||
7 | def begin | ||
8 | max_fibers = CONFIG.feed_threads | ||
9 | active_fibers = 0 | ||
10 | active_channel = ::Channel(Bool).new | ||
11 | |||
12 | loop do | ||
13 | db.query("SELECT email FROM users WHERE feed_needs_update = true OR feed_needs_update IS NULL") do |rs| | ||
14 | rs.each do | ||
15 | email = rs.read(String) | ||
16 | view_name = "subscriptions_#{sha256(email)}" | ||
17 | |||
18 | if active_fibers >= max_fibers | ||
19 | if active_channel.receive | ||
20 | active_fibers -= 1 | ||
21 | end | ||
22 | end | ||
23 | |||
24 | active_fibers += 1 | ||
25 | spawn do | ||
26 | begin | ||
27 | # Drop outdated views | ||
28 | column_array = Invidious::Database.get_column_array(db, view_name) | ||
29 | ChannelVideo.type_array.each_with_index do |name, i| | ||
30 | if name != column_array[i]? | ||
31 | LOGGER.info("RefreshFeedsJob: DROP MATERIALIZED VIEW #{view_name}") | ||
32 | db.exec("DROP MATERIALIZED VIEW #{view_name}") | ||
33 | raise "view does not exist" | ||
34 | end | ||
35 | end | ||
36 | |||
37 | if !db.query_one("SELECT pg_get_viewdef('#{view_name}')", as: String).includes? "WHERE ((cv.ucid = ANY (u.subscriptions))" | ||
38 | LOGGER.info("RefreshFeedsJob: Materialized view #{view_name} is out-of-date, recreating...") | ||
39 | db.exec("DROP MATERIALIZED VIEW #{view_name}") | ||
40 | end | ||
41 | |||
42 | db.exec("REFRESH MATERIALIZED VIEW #{view_name}") | ||
43 | db.exec("UPDATE users SET feed_needs_update = false WHERE email = $1", email) | ||
44 | rescue ex | ||
45 | # Rename old views | ||
46 | begin | ||
47 | legacy_view_name = "subscriptions_#{sha256(email)[0..7]}" | ||
48 | |||
49 | db.exec("SELECT * FROM #{legacy_view_name} LIMIT 0") | ||
50 | LOGGER.info("RefreshFeedsJob: RENAME MATERIALIZED VIEW #{legacy_view_name}") | ||
51 | db.exec("ALTER MATERIALIZED VIEW #{legacy_view_name} RENAME TO #{view_name}") | ||
52 | rescue ex | ||
53 | begin | ||
54 | # While iterating through, we may have an email stored from a deleted account | ||
55 | if db.query_one?("SELECT true FROM users WHERE email = $1", email, as: Bool) | ||
56 | LOGGER.info("RefreshFeedsJob: CREATE #{view_name}") | ||
57 | db.exec("CREATE MATERIALIZED VIEW #{view_name} AS #{MATERIALIZED_VIEW_SQL.call(email)}") | ||
58 | db.exec("UPDATE users SET feed_needs_update = false WHERE email = $1", email) | ||
59 | end | ||
60 | rescue ex | ||
61 | LOGGER.error("RefreshFeedJobs: REFRESH #{email} : #{ex.message}") | ||
62 | end | ||
63 | end | ||
64 | end | ||
65 | |||
66 | active_channel.send(true) | ||
67 | end | ||
68 | end | ||
69 | end | ||
70 | |||
71 | sleep 5.seconds | ||
72 | Fiber.yield | ||
73 | end | ||
74 | end | ||
75 | end | ||
diff --git a/src/invidious/routes/account.cr b/src/invidious/routes/account.cr index dd65e7a6..8086a54e 100644 --- a/src/invidious/routes/account.cr +++ b/src/invidious/routes/account.cr | |||
@@ -123,10 +123,8 @@ module Invidious::Routes::Account | |||
123 | return error_template(400, ex) | 123 | return error_template(400, ex) |
124 | end | 124 | end |
125 | 125 | ||
126 | view_name = "subscriptions_#{sha256(user.email)}" | ||
127 | Invidious::Database::Users.delete(user) | 126 | Invidious::Database::Users.delete(user) |
128 | Invidious::Database::SessionIDs.delete(email: user.email) | 127 | Invidious::Database::SessionIDs.delete(email: user.email) |
129 | PG_DB.exec("DROP MATERIALIZED VIEW #{view_name}") | ||
130 | 128 | ||
131 | env.request.cookies.each do |cookie| | 129 | env.request.cookies.each do |cookie| |
132 | cookie.expires = Time.utc(1990, 1, 1) | 130 | cookie.expires = Time.utc(1990, 1, 1) |
diff --git a/src/invidious/routes/login.cr b/src/invidious/routes/login.cr index d0f7ac22..add9f75d 100644 --- a/src/invidious/routes/login.cr +++ b/src/invidious/routes/login.cr | |||
@@ -160,9 +160,6 @@ module Invidious::Routes::Login | |||
160 | Invidious::Database::Users.insert(user) | 160 | Invidious::Database::Users.insert(user) |
161 | Invidious::Database::SessionIDs.insert(sid, email) | 161 | Invidious::Database::SessionIDs.insert(sid, email) |
162 | 162 | ||
163 | view_name = "subscriptions_#{sha256(user.email)}" | ||
164 | PG_DB.exec("CREATE MATERIALIZED VIEW #{view_name} AS #{MATERIALIZED_VIEW_SQL.call(user.email)}") | ||
165 | |||
166 | env.response.cookies["SID"] = Invidious::User::Cookies.sid(CONFIG.domain, sid) | 163 | env.response.cookies["SID"] = Invidious::User::Cookies.sid(CONFIG.domain, sid) |
167 | 164 | ||
168 | if env.request.cookies["PREFS"]? | 165 | if env.request.cookies["PREFS"]? |
diff --git a/src/invidious/search/processors.cr b/src/invidious/search/processors.cr index 25edb936..10b81c59 100644 --- a/src/invidious/search/processors.cr +++ b/src/invidious/search/processors.cr | |||
@@ -37,18 +37,18 @@ module Invidious::Search | |||
37 | 37 | ||
38 | # Search inside of user subscriptions | 38 | # Search inside of user subscriptions |
39 | def subscriptions(query : Query, user : Invidious::User) : Array(ChannelVideo) | 39 | def subscriptions(query : Query, user : Invidious::User) : Array(ChannelVideo) |
40 | view_name = "subscriptions_#{sha256(user.email)}" | ||
41 | |||
42 | return PG_DB.query_all(" | 40 | return PG_DB.query_all(" |
43 | SELECT id,title,published,updated,ucid,author,length_seconds | 41 | SELECT id,title,published,updated,ucid,author,length_seconds |
44 | FROM ( | 42 | FROM ( |
45 | SELECT *, | 43 | SELECT cv.*, |
46 | to_tsvector(#{view_name}.title) || | 44 | to_tsvector(cv.title) || |
47 | to_tsvector(#{view_name}.author) | 45 | to_tsvector(cv.author) AS document |
48 | as document | 46 | FROM channel_videos cv |
49 | FROM #{view_name} | 47 | JOIN users ON cv.ucid = any(users.subscriptions) |
50 | ) v_search WHERE v_search.document @@ plainto_tsquery($1) LIMIT 20 OFFSET $2;", | 48 | WHERE users.email = $1 AND published > now() - interval '1 month' |
51 | query.text, (query.page - 1) * 20, | 49 | ORDER BY published |
50 | ) v_search WHERE v_search.document @@ plainto_tsquery($2) LIMIT 20 OFFSET $3;", | ||
51 | user.email, query.text, (query.page - 1) * 20, | ||
52 | as: ChannelVideo | 52 | as: ChannelVideo |
53 | ) | 53 | ) |
54 | end | 54 | end |
diff --git a/src/invidious/users.cr b/src/invidious/users.cr index 65566d20..0b2d1ef5 100644 --- a/src/invidious/users.cr +++ b/src/invidious/users.cr | |||
@@ -27,7 +27,6 @@ def get_subscription_feed(user, max_results = 40, page = 1) | |||
27 | offset = (page - 1) * limit | 27 | offset = (page - 1) * limit |
28 | 28 | ||
29 | notifications = Invidious::Database::Users.select_notifications(user) | 29 | notifications = Invidious::Database::Users.select_notifications(user) |
30 | view_name = "subscriptions_#{sha256(user.email)}" | ||
31 | 30 | ||
32 | if user.preferences.notifications_only && !notifications.empty? | 31 | if user.preferences.notifications_only && !notifications.empty? |
33 | # Only show notifications | 32 | # Only show notifications |
@@ -53,33 +52,39 @@ def get_subscription_feed(user, max_results = 40, page = 1) | |||
53 | # Show latest video from a channel that a user hasn't watched | 52 | # Show latest video from a channel that a user hasn't watched |
54 | # "unseen_only" isn't really correct here, more accurate would be "unwatched_only" | 53 | # "unseen_only" isn't really correct here, more accurate would be "unwatched_only" |
55 | 54 | ||
56 | if user.watched.empty? | 55 | # "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" |
57 | values = "'{}'" | 56 | # "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" |
58 | else | 57 | videos = PG_DB.query_all("SELECT DISTINCT ON (cv.ucid) cv.* " \ |
59 | values = "VALUES #{user.watched.map { |id| %(('#{id}')) }.join(",")}" | 58 | "FROM channel_videos cv " \ |
60 | end | 59 | "JOIN users ON cv.ucid = any(users.subscriptions) " \ |
61 | videos = PG_DB.query_all("SELECT DISTINCT ON (ucid) * FROM #{view_name} WHERE NOT id = ANY (#{values}) ORDER BY ucid, published DESC", as: ChannelVideo) | 60 | "WHERE users.email = $1 AND NOT cv.id = any(users.watched) AND published > now() - interval '1 month' " \ |
61 | "ORDER BY ucid, published DESC", user.email, as: ChannelVideo) | ||
62 | else | 62 | else |
63 | # Show latest video from each channel | 63 | # Show latest video from each channel |
64 | 64 | ||
65 | videos = PG_DB.query_all("SELECT DISTINCT ON (ucid) * FROM #{view_name} ORDER BY ucid, published DESC", as: ChannelVideo) | 65 | videos = PG_DB.query_all("SELECT DISTINCT ON (cv.ucid) cv.* " \ |
66 | "FROM channel_videos cv " \ | ||
67 | "JOIN users ON cv.ucid = any(users.subscriptions) " \ | ||
68 | "WHERE users.email = $1 AND published > now() - interval '1 month' " \ | ||
69 | "ORDER BY ucid, published DESC", user.email, as: ChannelVideo) | ||
66 | end | 70 | end |
67 | 71 | ||
68 | videos.sort_by!(&.published).reverse! | 72 | videos.sort_by!(&.published).reverse! |
69 | else | 73 | else |
70 | if user.preferences.unseen_only | 74 | if user.preferences.unseen_only |
71 | # Only show unwatched | 75 | # Only show unwatched |
72 | 76 | videos = PG_DB.query_all("SELECT cv.* " \ | |
73 | if user.watched.empty? | 77 | "FROM channel_videos cv " \ |
74 | values = "'{}'" | 78 | "JOIN users ON cv.ucid = any(users.subscriptions) " \ |
75 | else | 79 | "WHERE users.email = $1 AND NOT cv.id = any(users.watched) AND published > now() - interval '1 month' " \ |
76 | values = "VALUES #{user.watched.map { |id| %(('#{id}')) }.join(",")}" | 80 | "ORDER BY published DESC LIMIT $2 OFFSET $3", user.email, limit, offset, as: ChannelVideo) |
77 | end | ||
78 | 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) | ||
79 | else | 81 | else |
80 | # Sort subscriptions as normal | 82 | # Sort subscriptions as normal |
81 | 83 | videos = PG_DB.query_all("SELECT cv.* " \ | |
82 | videos = PG_DB.query_all("SELECT * FROM #{view_name} ORDER BY published DESC LIMIT $1 OFFSET $2", limit, offset, as: ChannelVideo) | 84 | "FROM channel_videos cv " \ |
85 | "JOIN users ON cv.ucid = any(users.subscriptions) " \ | ||
86 | "WHERE users.email = $1 AND published > now() - interval '1 month' " \ | ||
87 | "ORDER BY published DESC LIMIT $2 OFFSET $3", user.email, limit, offset, as: ChannelVideo) | ||
83 | end | 88 | end |
84 | end | 89 | end |
85 | 90 | ||