Add primitive postgres support

5 files changed, 106 insertions, 31 deletions

diff --git a/invidious.sql b/invidious.sql
new file mode 100644
index 00000000..5585bd89
--- /dev/null
+++ b/invidious.sql
@@ -0,0 +1,36 @@
+-- Table: public.invidious
+
+-- DROP TABLE public.invidious;
+
+CREATE TABLE public.invidious
+(
+    last_updated timestamp with time zone,
+    video_id text COLLATE pg_catalog."default" NOT NULL,
+    video_info text COLLATE pg_catalog."default",
+    video_html text COLLATE pg_catalog."default",
+    views bigint,
+    likes integer,
+    dislikes integer,
+    rating double precision,
+    CONSTRAINT invidious_pkey PRIMARY KEY (video_id)
+)
+WITH (
+    OIDS = FALSE
+)
+TABLESPACE pg_default;
+
+ALTER TABLE public.invidious
+    OWNER to omar;
+
+GRANT ALL ON TABLE public.invidious TO kemal;
+
+GRANT ALL ON TABLE public.invidious TO omar;
+
+-- Index: invidious_video_id_idx
+
+-- DROP INDEX public.invidious_video_id_idx;
+
+CREATE INDEX invidious_video_id_idx
+    ON public.invidious USING btree
+    (video_id COLLATE pg_catalog."default")
+    TABLESPACE pg_default;
diff --git a/shard.lock b/shard.lock
index 1e791997..d90fbc3d 100644
--- a/shard.lock
+++ b/shard.lock
@@ -6,7 +6,7 @@ shards:
 
   kemal:
     github: kemalcr/kemal
-    version: 0.21.0
+    commit: 8cb9770ec3c6cf5897e644229dad8d0b5c360941
 
   kilt:
     github: jeromegn/kilt
@@ -14,7 +14,7 @@ shards:
 
   pg:
     github: will/crystal-pg
-    version: 0.13.4
+    commit: cafe58314bbbf0e6273963b1447e1c2fbeaf41ff
 
   radix:
     github: luislavena/radix
diff --git a/shard.yml b/shard.yml
index 3fbae5e2..9831ff5e 100644
--- a/shard.yml
+++ b/shard.yml
@@ -11,9 +11,11 @@ targets:
 dependencies:
   kemal:
      github: kemalcr/kemal
+     branch: master
   pg:
     github: will/crystal-pg
+    branch: crystalv024
 
-crystal: 0.23.1
+crystal: 0.24.0
 
 license: MIT
diff --git a/src/invidious.cr b/src/invidious.cr
index 5a1c605a..e8df4e62 100644
--- a/src/invidious.cr
+++ b/src/invidious.cr
@@ -3,6 +3,7 @@ require "json"
 require "kemal"
 require "pg"
 require "xml"
+require "time"
 
 class AdaptiveFmts
   JSON.mapping(
@@ -121,29 +122,73 @@ class VideoInfo
   )
 end
 
+class Record
+end
+
 macro templated(filename)
   render "src/views/#{{{filename}}}.ecr", "src/views/layout.ecr"
 end
 
+pg = DB.open "postgres://kemal:kemal@localhost:5432/invidious"
 context = OpenSSL::SSL::Context::Client.insecure
 
-# client = HTTP::Client.new("www.youtube.com", 443, context)
-# video_id = "Vufba_ZcoR0"
-# video_info = client.get("/get_video_info?video_id=#{video_id}&el=info&ps=default&eurl=&gl=US&hl=en").body
-# p VideoInfo.from_json(video_info)
-
 get "/" do |env|
   templated "index"
 end
 
+def update_record(context, pg, video_id)
+  client = HTTP::Client.new("www.youtube.com", 443, context)
+  video_info = client.get("/get_video_info?video_id=#{video_id}&el=info&ps=default&eurl=&gl=US&hl=en").body
+  info = HTTP::Params.parse(video_info)
+  video_html = client.get("/watch?v=#{video_id}").body
+  html = XML.parse(video_html)
+  views = info["view_count"]
+  rating = info["avg_rating"].to_f64
+
+  like = html.xpath_node(%q(//button[@title="I like this"]/span))
+  if like
+    likes = like.content.delete(",").to_i
+  else
+    likes = 1
+  end
+
+  # css query [title = "I like this"] > span
+  # css query [title = "I dislike this"] > span
+  dislike = html.xpath_node(%q(//button[@title="I dislike this"]/span))
+  if dislike
+    dislikes = dislike.content.delete(",").to_i
+  else
+    dislikes = 1
+  end
+
+  pg.exec("update invidious set last_updated = $1, video_info = $2, video_html = $3, views = $4, likes = $5,\
+          dislikes = $6, rating = $7 where video_id = $8",
+    Time.now, video_info, video_html, views, likes, dislikes, rating, video_id)
+
+  return {Time.now, video_id, video_info, video_html, views, likes, dislikes, rating}
+end
+
 get "/watch/:video_id" do |env|
   video_id = env.params.url["video_id"]
 
-  client = HTTP::Client.new("www.youtube.com", 443, context)
-  video_info = client.get("/get_video_info?video_id=#{video_id}&el=info&ps=default&eurl=&gl=US&hl=en").body
-  video_info = HTTP::Params.parse(video_info)
-  pageContent = client.get("/watch?v=#{video_id}").body
-  doc = XML.parse(pageContent)
+  # last_updated, video_id, video_info, video_html, views, likes, dislikes, rating
+  video_record = pg.query_one?("select * from invidious where video_id = $1",
+    video_id,
+    as: {Time, String, String, String, Int64, Int32, Int32, Float64})
+
+  # If record was last updated less than 5 minutes ago, use data, otherwise refresh
+  if video_record.nil?
+    video_record = update_record(context, pg, video_id)
+  elsif Time.now.epoch - video_record[0].epoch > 300
+    video_record = update_record(context, pg, video_id)
+  end
+
+  video_info = HTTP::Params.parse(video_record[2])
+  video_html = XML.parse(video_record[3])
+  views = video_record[4]
+  likes = video_record[5]
+  dislikes = video_record[6]
+  rating = video_record[7]
 
   fmt_stream = [] of HTTP::Params
   video_info["url_encoded_fmt_stream_map"].split(",") do |string|
@@ -151,24 +196,16 @@ get "/watch/:video_id" do |env|
   end
 
   fmt_stream.reverse! # We want lowest quality first
-  # css query [title="I like this"] > span
-  likes = doc.xpath_node(%q(//button[@title="I like this"]/span))
-  if likes
-    likes = likes.content.delete(",").to_i
-  else
-    likes = 1
-  end
 
-  # css query [title="I dislike this"] > span
-  dislikes = doc.xpath_node(%q(//button[@title="I dislike this"]/span))
-  if dislikes
-    dislikes = dislikes.content.delete(",").to_i
-  else
-    dislikes = 1
-  end
+  likes = likes.to_f
+  dislikes = dislikes.to_f
+  views = views.to_f
+  engagement = (((dislikes + likes)*100)/views).significant(2)
+  calculated_rating = likes/(likes + dislikes) * 4 + 1
 
-  engagement = ((dislikes.to_f32 + likes.to_f32)*100 / video_info["view_count"].to_f32).significant(2)
-  calculated_rating = likes.to_f32/(likes.to_f32 + dislikes.to_f32)*4 + 1
+  likes = likes.to_s
+  dislikes = dislikes.to_s
+  views = views.to_s
 
   templated "watch"
 end
diff --git a/src/views/watch.ecr b/src/views/watch.ecr
index 199643df..05101806 100644
--- a/src/views/watch.ecr
+++ b/src/views/watch.ecr
@@ -11,8 +11,8 @@
         <p>-&nbsp;  <%= dislikes %></p>
     </div>
     <div class="pure-u-1 pure-u-md-3-5">
-        <p>Views : <%= video_info["view_count"] %></p>
-        <p>Rating : <%= video_info["avg_rating"] %></p>
+        <p>Views : <%= views %></p>
+        <p>Rating : <%= rating %></p>
         <p>Calculated Rating : <%= calculated_rating %></p>
         <p>Engagement : <%= engagement %>%</p>
     </div>