diff options
| -rw-r--r-- | src/invidious.cr | 2 | ||||
| -rw-r--r-- | src/invidious/database/playlists.cr | 80 | ||||
| -rw-r--r-- | src/invidious/playlists.cr | 2 | ||||
| -rw-r--r-- | src/invidious/routes/api/v1/authenticated.cr | 20 | ||||
| -rw-r--r-- | src/invidious/routes/feeds.cr | 4 | ||||
| -rw-r--r-- | src/invidious/routes/playlists.cr | 18 | ||||
| -rw-r--r-- | src/invidious/views/playlist.ecr | 2 |
7 files changed, 108 insertions, 20 deletions
diff --git a/src/invidious.cr b/src/invidious.cr index 28d8ddac..93b3357a 100644 --- a/src/invidious.cr +++ b/src/invidious.cr @@ -790,7 +790,7 @@ post "/data_control" do |env| next if !privacy playlist = create_playlist(PG_DB, title, privacy, user) - PG_DB.exec("UPDATE playlists SET description = $1 WHERE id = $2", description, playlist.id) + Invidious::Database::Playlists.update_description(playlist.id, description) videos = item["videos"]?.try &.as_a?.try &.each_with_index do |video_id, idx| raise InfoException.new("Playlist cannot have more than 500 videos") if idx > 500 diff --git a/src/invidious/database/playlists.cr b/src/invidious/database/playlists.cr index 037e25b7..1dba64f3 100644 --- a/src/invidious/database/playlists.cr +++ b/src/invidious/database/playlists.cr @@ -37,6 +37,36 @@ module Invidious::Database::Playlists # Update # ------------------- + def update(id : String, title : String, privacy, description, updated) + request = <<-SQL + UPDATE playlists + SET title = $1, privacy = $2, description = $3, updated = $4 + WHERE id = $5 + SQL + + PG_DB.exec(request, title, privacy, description, updated, id) + end + + def update_description(id : String, description) + request = <<-SQL + UPDATE playlists + SET description = $1 + WHERE id = $2 + SQL + + PG_DB.exec(request, description, id) + end + + def update_subscription_time(id : String) + request = <<-SQL + UPDATE playlists + SET subscribed = $1 + WHERE id = $2 + SQL + + PG_DB.exec(request, Time.utc, id) + end + def update_video_added(id : String, index : String | Int64) request = <<-SQL UPDATE playlists @@ -60,6 +90,56 @@ module Invidious::Database::Playlists PG_DB.exec(request, index, Time.utc, id) end + + # ------------------- + # Salect + # ------------------- + + def select(*, id : String, raise_on_fail : Bool = false) : InvidiousPlaylist? + request = <<-SQL + SELECT * FROM playlists + WHERE id = $1 + SQL + + if raise_on_fail + return PG_DB.query_one(request, id, as: InvidiousPlaylist) + else + return PG_DB.query_one?(request, id, as: InvidiousPlaylist) + end + end + + def select_all(*, author : String) : Array(InvidiousPlaylist) + request = <<-SQL + SELECT * FROM playlists + WHERE author = $1 + SQL + + return PG_DB.query_all(request, author, as: InvidiousPlaylist) + end + + # ------------------- + # Misc checks + # ------------------- + + # Check if given playlist ID exists + def exists?(id : String) : Bool + request = <<-SQL + SELECT id FROM playlists + WHERE id = $1 + SQL + + return PG_DB.query_one?(request, id, as: String).nil? + end + + # Count how many playlist a user has created. + def count_owned_by(author : String) : Int64 + request = <<-SQL + SELECT count(*) FROM playlists + WHERE author = $1 + SQL + + return PG_DB.query_one?(request, author, as: Int64) || 0_i64 + end end # diff --git a/src/invidious/playlists.cr b/src/invidious/playlists.cr index 685fa1c7..f68dc3b0 100644 --- a/src/invidious/playlists.cr +++ b/src/invidious/playlists.cr @@ -323,7 +323,7 @@ end def get_playlist(db, plid, locale, refresh = true, force_refresh = false) if plid.starts_with? "IV" - if playlist = db.query_one?("SELECT * FROM playlists WHERE id = $1", plid, as: InvidiousPlaylist) + if playlist = Invidious::Database::Playlists.select(id: plid) return playlist else raise InfoException.new("Playlist does not exist.") diff --git a/src/invidious/routes/api/v1/authenticated.cr b/src/invidious/routes/api/v1/authenticated.cr index 4fe8cd30..d74dca5c 100644 --- a/src/invidious/routes/api/v1/authenticated.cr +++ b/src/invidious/routes/api/v1/authenticated.cr @@ -127,7 +127,7 @@ module Invidious::Routes::API::V1::Authenticated env.response.content_type = "application/json" user = env.get("user").as(User) - playlists = PG_DB.query_all("SELECT * FROM playlists WHERE author = $1", user.email, as: InvidiousPlaylist) + playlists = Invidious::Database::Playlists.select_all(author: user.email) JSON.build do |json| json.array do @@ -153,7 +153,7 @@ module Invidious::Routes::API::V1::Authenticated return error_json(400, "Invalid privacy setting.") end - if PG_DB.query_one("SELECT count(*) FROM playlists WHERE author = $1", user.email, as: Int64) >= 100 + if Invidious::Database::Playlists.count_owned_by(user.email) >= 100 return error_json(400, "User cannot have more than 100 playlists.") end @@ -172,9 +172,12 @@ module Invidious::Routes::API::V1::Authenticated env.response.content_type = "application/json" user = env.get("user").as(User) - plid = env.params.url["plid"] + plid = env.params.url["plid"]? + if !plid || plid.empty? + return error_json(400, "A playlist ID is required") + end - playlist = PG_DB.query_one?("SELECT * FROM playlists WHERE id = $1", plid, as: InvidiousPlaylist) + playlist = Invidious::Database::Playlists.select(id: plid) if !playlist || playlist.author != user.email && playlist.privacy.private? return error_json(404, "Playlist does not exist.") end @@ -195,7 +198,8 @@ module Invidious::Routes::API::V1::Authenticated updated = playlist.updated end - PG_DB.exec("UPDATE playlists SET title = $1, privacy = $2, description = $3, updated = $4 WHERE id = $5", title, privacy, description, updated, plid) + Invidious::Database::Playlists.update(plid, title, privacy, description, updated) + env.response.status_code = 204 end @@ -207,7 +211,7 @@ module Invidious::Routes::API::V1::Authenticated plid = env.params.url["plid"] - playlist = PG_DB.query_one?("SELECT * FROM playlists WHERE id = $1", plid, as: InvidiousPlaylist) + playlist = Invidious::Database::Playlists.select(id: plid) if !playlist || playlist.author != user.email && playlist.privacy.private? return error_json(404, "Playlist does not exist.") end @@ -229,7 +233,7 @@ module Invidious::Routes::API::V1::Authenticated plid = env.params.url["plid"] - playlist = PG_DB.query_one?("SELECT * FROM playlists WHERE id = $1", plid, as: InvidiousPlaylist) + playlist = Invidious::Database::Playlists.select(id: plid) if !playlist || playlist.author != user.email && playlist.privacy.private? return error_json(404, "Playlist does not exist.") end @@ -285,7 +289,7 @@ module Invidious::Routes::API::V1::Authenticated plid = env.params.url["plid"] index = env.params.url["index"].to_i64(16) - playlist = PG_DB.query_one?("SELECT * FROM playlists WHERE id = $1", plid, as: InvidiousPlaylist) + playlist = Invidious::Database::Playlists.select(id: plid) if !playlist || playlist.author != user.email && playlist.privacy.private? return error_json(404, "Playlist does not exist.") end diff --git a/src/invidious/routes/feeds.cr b/src/invidious/routes/feeds.cr index 9650bcf4..6424ab47 100644 --- a/src/invidious/routes/feeds.cr +++ b/src/invidious/routes/feeds.cr @@ -264,7 +264,7 @@ module Invidious::Routes::Feeds path = env.request.path if plid.starts_with? "IV" - if playlist = PG_DB.query_one?("SELECT * FROM playlists WHERE id = $1", plid, as: InvidiousPlaylist) + if playlist = Invidious::Database::Playlists.select(id: plid) videos = get_playlist_videos(PG_DB, playlist, offset: 0, locale: locale) return XML.build(indent: " ", encoding: "UTF-8") do |xml| @@ -364,7 +364,7 @@ module Invidious::Routes::Feeds if ucid = HTTP::Params.parse(URI.parse(topic).query.not_nil!)["channel_id"]? PG_DB.exec("UPDATE channels SET subscribed = $1 WHERE id = $2", Time.utc, ucid) elsif plid = HTTP::Params.parse(URI.parse(topic).query.not_nil!)["playlist_id"]? - PG_DB.exec("UPDATE playlists SET subscribed = $1 WHERE id = $2", Time.utc, ucid) + Invidious::Database::Playlists.update_subscription_time(plid) else haltf env, status_code: 400 end diff --git a/src/invidious/routes/playlists.cr b/src/invidious/routes/playlists.cr index d29aef09..b73782d5 100644 --- a/src/invidious/routes/playlists.cr +++ b/src/invidious/routes/playlists.cr @@ -46,7 +46,7 @@ module Invidious::Routes::Playlists return error_template(400, "Invalid privacy setting.") end - if PG_DB.query_one("SELECT count(*) FROM playlists WHERE author = $1", user.email, as: Int64) >= 100 + if Invidious::Database::Playlists.count_owned_by(user.email) >= 100 return error_template(400, "User cannot have more than 100 playlists.") end @@ -85,7 +85,11 @@ module Invidious::Routes::Playlists sid = sid.as(String) plid = env.params.query["list"]? - playlist = PG_DB.query_one?("SELECT * FROM playlists WHERE id = $1", plid, as: InvidiousPlaylist) + if !plid || plid.empty? + return error_template(400, "A playlist ID is required") + end + + playlist = Invidious::Database::Playlists.select(id: plid) if !playlist || playlist.author != user.email return env.redirect referer end @@ -117,7 +121,7 @@ module Invidious::Routes::Playlists return error_template(400, ex) end - playlist = PG_DB.query_one?("SELECT * FROM playlists WHERE id = $1", plid, as: InvidiousPlaylist) + playlist = Invidious::Database::Playlists.select(id: plid) if !playlist || playlist.author != user.email return env.redirect referer end @@ -148,7 +152,7 @@ module Invidious::Routes::Playlists page ||= 1 begin - playlist = PG_DB.query_one("SELECT * FROM playlists WHERE id = $1", plid, as: InvidiousPlaylist) + playlist = Invidious::Database::Playlists.select(id: plid, raise_on_fail: true) if !playlist || playlist.author != user.email return env.redirect referer end @@ -189,7 +193,7 @@ module Invidious::Routes::Playlists return error_template(400, ex) end - playlist = PG_DB.query_one?("SELECT * FROM playlists WHERE id = $1", plid, as: InvidiousPlaylist) + playlist = Invidious::Database::Playlists.select(id: plid) if !playlist || playlist.author != user.email return env.redirect referer end @@ -206,7 +210,7 @@ module Invidious::Routes::Playlists updated = playlist.updated end - PG_DB.exec("UPDATE playlists SET title = $1, privacy = $2, description = $3, updated = $4 WHERE id = $5", title, privacy, description, updated, plid) + Invidious::Database::Playlists.update(plid, title, privacy, description, updated) env.redirect "/playlist?list=#{plid}" end @@ -232,7 +236,7 @@ module Invidious::Routes::Playlists page ||= 1 begin - playlist = PG_DB.query_one("SELECT * FROM playlists WHERE id = $1", plid, as: InvidiousPlaylist) + playlist = Invidious::Database::Playlists.select(id: plid, raise_on_fail: true) if !playlist || playlist.author != user.email return env.redirect referer end diff --git a/src/invidious/views/playlist.ecr b/src/invidious/views/playlist.ecr index 136981da..7825b1f0 100644 --- a/src/invidious/views/playlist.ecr +++ b/src/invidious/views/playlist.ecr @@ -61,7 +61,7 @@ <div class="pure-u-1-3"><a href="/edit_playlist?list=<%= plid %>"><i class="icon ion-md-create"></i></a></div> <div class="pure-u-1-3"><a href="/delete_playlist?list=<%= plid %>"><i class="icon ion-md-trash"></i></a></div> <% else %> - <% if PG_DB.query_one?("SELECT id FROM playlists WHERE id = $1", playlist.id, as: String).nil? %> + <% if Invidious::Database::Playlists.exists?(playlist.id) %> <div class="pure-u-1-3"><a href="/subscribe_playlist?list=<%= plid %>"><i class="icon ion-md-add"></i></a></div> <% else %> <div class="pure-u-1-3"><a href="/delete_playlist?list=<%= plid %>"><i class="icon ion-md-trash"></i></a></div> |