X-Git-Url: http://git.squeep.com/?a=blobdiff_plain;f=lib%2Fmix%2Ftasks%2Fpleroma%2Fdatabase.ex;h=0f428ca0340e6881639bc5d8d5ac88dbfae23945;hb=a7ec6e039cdc9ca5a0cc744fbd51511c5ede9f29;hp=bcde07774fb71b9365bec0bdc75169ebc4196aa6;hpb=5be9d139816fa40ff6227950b58f3c6cea01fc81;p=akkoma diff --git a/lib/mix/tasks/pleroma/database.ex b/lib/mix/tasks/pleroma/database.ex index bcde07774..0f428ca03 100644 --- a/lib/mix/tasks/pleroma/database.ex +++ b/lib/mix/tasks/pleroma/database.ex @@ -18,7 +18,7 @@ defmodule Mix.Tasks.Pleroma.Database do use Mix.Task @shortdoc "A collection of database related tasks" - @moduledoc File.read!("docs/administration/CLI_tasks/database.md") + @moduledoc File.read!("docs/docs/administration/CLI_tasks/database.md") def run(["remove_embedded_objects" | args]) do {options, [], []} = @@ -67,40 +67,137 @@ defmodule Mix.Tasks.Pleroma.Database do OptionParser.parse( args, strict: [ - vacuum: :boolean + vacuum: :boolean, + keep_threads: :boolean, + keep_non_public: :boolean, + prune_orphaned_activities: :boolean ] ) start_pleroma() deadline = Pleroma.Config.get([:instance, :remote_post_retention_days]) + time_deadline = NaiveDateTime.utc_now() |> NaiveDateTime.add(-(deadline * 86_400)) - Logger.info("Pruning objects older than #{deadline} days") + log_message = "Pruning objects older than #{deadline} days" - time_deadline = - NaiveDateTime.utc_now() - |> NaiveDateTime.add(-(deadline * 86_400)) + log_message = + if Keyword.get(options, :keep_non_public) do + log_message <> ", keeping non public posts" + else + log_message + end - from(o in Object, - where: - fragment( - "?->'to' \\? ? OR ?->'cc' \\? ?", - o.data, - ^Pleroma.Constants.as_public(), - o.data, - ^Pleroma.Constants.as_public() - ), - where: o.inserted_at < ^time_deadline, - where: + log_message = + if Keyword.get(options, :keep_threads) do + log_message <> ", keeping threads intact" + else + log_message + end + + log_message = + if Keyword.get(options, :prune_orphaned_activities) do + log_message <> ", pruning orphaned activities" + else + log_message + end + + log_message = + if Keyword.get(options, :vacuum) do + log_message <> + ", doing a full vacuum (you shouldn't do this as a recurring maintanance task)" + else + log_message + end + + Logger.info(log_message) + + if Keyword.get(options, :keep_threads) do + # We want to delete objects from threads where + # 1. the newest post is still old + # 2. none of the activities is local + # 3. none of the activities is bookmarked + # 4. optionally none of the posts is non-public + deletable_context = + if Keyword.get(options, :keep_non_public) do + Pleroma.Activity + |> join(:left, [a], b in Pleroma.Bookmark, on: a.id == b.activity_id) + |> group_by([a], fragment("? ->> 'context'::text", a.data)) + |> having( + [a], + not fragment( + # Posts (checked on Create Activity) is non-public + "bool_or((not(?->'to' \\? ? OR ?->'cc' \\? ?)) and ? ->> 'type' = 'Create')", + a.data, + ^Pleroma.Constants.as_public(), + a.data, + ^Pleroma.Constants.as_public(), + a.data + ) + ) + else + Pleroma.Activity + |> join(:left, [a], b in Pleroma.Bookmark, on: a.id == b.activity_id) + |> group_by([a], fragment("? ->> 'context'::text", a.data)) + end + |> having([a], max(a.updated_at) < ^time_deadline) + |> having([a], not fragment("bool_or(?)", a.local)) + |> having([_, b], fragment("max(?::text) is null", b.id)) + |> select([a], fragment("? ->> 'context'::text", a.data)) + + Pleroma.Object + |> where([o], fragment("? ->> 'context'::text", o.data) in subquery(deletable_context)) + else + if Keyword.get(options, :keep_non_public) do + Pleroma.Object + |> where( + [o], + fragment( + "?->'to' \\? ? OR ?->'cc' \\? ?", + o.data, + ^Pleroma.Constants.as_public(), + o.data, + ^Pleroma.Constants.as_public() + ) + ) + else + Pleroma.Object + end + |> where([o], o.updated_at < ^time_deadline) + |> where( + [o], fragment("split_part(?->>'actor', '/', 3) != ?", o.data, ^Pleroma.Web.Endpoint.host()) - ) + ) + end |> Repo.delete_all(timeout: :infinity) + if Keyword.get(options, :prune_orphaned_activities) do + """ + delete from public.activities + where id in ( + select a.id from public.activities a + left join public.objects o on a.data ->> 'object' = o.data ->> 'id' + left join public.activities a2 on a.data ->> 'object' = a2.data ->> 'id' + left join public.users u on a.data ->> 'object' = u.ap_id + -- Only clean up remote activities + where not a.local + -- For now we only focus on activities with direct links to objects + -- e.g. not json objects (in case of embedded objects) or json arrays (in case of multiple objects) + and jsonb_typeof(a."data" -> 'object') = 'string' + -- Find Activities that don't have existing objects + and o.id is null + and a2.id is null + and u.id is null + ) + """ + |> Repo.query() + end + prune_hashtags_query = """ - delete from hashtags as ht - where not exists ( - select 1 from hashtags_objects hto - where ht.id = hto.hashtag_id) + DELETE FROM hashtags AS ht + WHERE NOT EXISTS ( + SELECT 1 FROM hashtags_objects hto + WHERE ht.id = hto.hashtag_id) """ Repo.query(prune_hashtags_query) @@ -110,6 +207,13 @@ defmodule Mix.Tasks.Pleroma.Database do end end + def run(["prune_task"]) do + start_pleroma() + + nil + |> Pleroma.Workers.Cron.PruneDatabaseWorker.perform() + end + def run(["fix_likes_collections"]) do start_pleroma() @@ -209,7 +313,9 @@ defmodule Mix.Tasks.Pleroma.Database do new.fts_content := to_tsvector(new.data->>'content'); RETURN new; END - $$ LANGUAGE plpgsql" + $$ LANGUAGE plpgsql", + [], + timeout: :infinity ) shell_info("Refresh RUM index") @@ -219,7 +325,9 @@ defmodule Mix.Tasks.Pleroma.Database do Ecto.Adapters.SQL.query!( Pleroma.Repo, - "CREATE INDEX objects_fts ON objects USING gin(to_tsvector('#{tsconfig}', data->>'content')); " + "CREATE INDEX CONCURRENTLY objects_fts ON objects USING gin(to_tsvector('#{tsconfig}', data->>'content')); ", + [], + timeout: :infinity ) end