Add users table

6 files changed, 89 insertions, 16 deletions

diff --git a/config/sql/channel_videos.sql b/config/sql/channel_videos.sql
index e6cb3c8f..98d4d7de 100644
--- a/config/sql/channel_videos.sql
+++ b/config/sql/channel_videos.sql
@@ -18,3 +18,12 @@ WITH (
 TABLESPACE pg_default;
 
 GRANT ALL ON TABLE public.channel_videos TO kemal;
+
+-- Index: channel_videos_published_idx
+
+-- DROP INDEX public.channel_videos_published_idx;
+
+CREATE INDEX channel_videos_published_idx
+    ON public.channel_videos USING btree
+    (published)
+    TABLESPACE pg_default;
\ No newline at end of file
diff --git a/config/sql/channels.sql b/config/sql/channels.sql
index d24329f3..a328bcad 100644
--- a/config/sql/channels.sql
+++ b/config/sql/channels.sql
@@ -6,7 +6,8 @@ CREATE TABLE public.channels
 (
     id text COLLATE pg_catalog."default" NOT NULL,
     author text COLLATE pg_catalog."default",
-    updated timestamp with time zone
+    updated timestamp with time zone,
+    CONSTRAINT channels_id_key UNIQUE (id)
 )
 WITH (
     OIDS = FALSE
@@ -14,3 +15,12 @@ WITH (
 TABLESPACE pg_default;
 
 GRANT ALL ON TABLE public.channels TO kemal;
+
+-- Index: channels_id_idx
+
+-- DROP INDEX public.channels_id_idx;
+
+CREATE INDEX channels_id_idx
+    ON public.channels USING btree
+    (id COLLATE pg_catalog."default")
+    TABLESPACE pg_default;
\ No newline at end of file
diff --git a/config/sql/users.sql b/config/sql/users.sql
new file mode 100644
index 00000000..dde3b924
--- /dev/null
+++ b/config/sql/users.sql
@@ -0,0 +1,17 @@
+-- Table: public.users
+
+-- DROP TABLE public.users;
+
+CREATE TABLE public.users
+(
+    id text COLLATE pg_catalog."default" NOT NULL,
+    updated timestamp with time zone,
+    notifications integer,
+    subscriptions text[] COLLATE pg_catalog."default"
+)
+WITH (
+    OIDS = FALSE
+)
+TABLESPACE pg_default;
+
+GRANT ALL ON TABLE public.users TO kemal;
diff --git a/setup.sh b/setup.sh
index 664073e4..64097c3c 100755
--- a/setup.sh
+++ b/setup.sh
@@ -5,3 +5,4 @@ createuser kemal
 psql invidious < config/sql/channels.sql
 psql invidious < config/sql/videos.sql
 psql invidious < config/sql/channel_videos.sql
+psql invidious < config/sql/users.sql
diff --git a/src/helpers.cr b/src/helpers.cr
index 33035d60..2e2d9de4 100644
--- a/src/helpers.cr
+++ b/src/helpers.cr
@@ -79,6 +79,15 @@ class ChannelVideo
   })
 end
 
+class User
+  add_mapping({
+    id:            String,
+    updated:       Time,
+    notifications: Int32,
+    subscriptions: Array(String),
+  })
+end
+
 class RedditSubmit
   JSON.mapping({
     data: RedditSubmitData,
@@ -545,3 +554,40 @@ def fetch_channel(id, client, db)
 
   return channel
 end
+
+def get_user(sid, client, headers, db)
+  if db.query_one?("SELECT EXISTS (SELECT true FROM users WHERE id = $1)", sid, as: Bool)
+    user = db.query_one("SELECT * FROM users WHERE id = $1", sid, as: User)
+
+    if Time.now - user.updated > 1.minutes
+      user = fetch_user(sid, client, headers)
+      user_array = user.to_a
+      args = arg_array(user_array)
+      
+      db.exec("INSERT INTO users VALUES (#{args}) \
+      ON CONFLICT (id) DO UPDATE SET updated = $2, subscriptions = $4", user_array)
+    end
+  else
+    user = fetch_user(sid, client, headers)
+    args = arg_array(user.to_a)
+    db.exec("INSERT INTO users VALUES (#{args})", user.to_a)
+  end
+
+  return user
+end
+
+def fetch_user(sid, client, headers)
+  feed = client.get("/subscription_manager?action_takeout=1", headers).body
+
+  channels = [] of String
+  feed = XML.parse_html(feed)
+  feed.xpath_nodes("//opml/outline/outline").each do |channel|
+    id = channel["xmlurl"][-24..-1]
+    get_channel(id, client, PG_DB)
+
+    channels << id
+  end
+
+  user = User.new(sid, Time.now, 0, channels)
+  return user
+end
diff --git a/src/invidious.cr b/src/invidious.cr
index 82a40ed1..c91b75f8 100644
--- a/src/invidious.cr
+++ b/src/invidious.cr
@@ -567,29 +567,19 @@ get "/feed/subscriptions" do |env|
     page = env.params.query["page"]?.try &.to_i
     page ||= 1
 
-    client = get_client(youtube_pool)
-
     headers = HTTP::Headers.new
     headers["Cookie"] = env.request.headers["Cookie"]
 
-    feed = client.get("/subscription_manager?action_takeout=1", headers).body
-
-    channels = [] of String
+    sid = env.request.cookies["SID"].value
 
-    feed = XML.parse_html(feed)
-    feed.xpath_nodes("//opml/outline/outline").each do |channel|
-      id = channel["xmlurl"][-24..-1]
-      get_channel(id, client, PG_DB)
-
-      channels << id
-    end
+    client = get_client(youtube_pool)
+    user = get_user(sid, client, headers, PG_DB)
     youtube_pool << client
 
-    time = Time.now
-    args = arg_array(channels)
+    args = arg_array(user.subscriptions)
     offset = (page - 1) * max_results
     videos = PG_DB.query_all("SELECT * FROM channel_videos WHERE ucid IN (#{args})\
-      ORDER BY published DESC LIMIT #{max_results} OFFSET #{offset}", channels, as: ChannelVideo)
+      ORDER BY published DESC LIMIT #{max_results} OFFSET #{offset}", user.subscriptions, as: ChannelVideo)
 
     templated "subscriptions"
   else