1 # Pleroma: A lightweight social networking server
2 # Copyright © 2017-2021 Pleroma Authors <https://pleroma.social/>
3 # SPDX-License-Identifier: AGPL-3.0-only
5 defmodule Mix.Tasks.Pleroma.Database do
6 alias Pleroma.Conversation
7 alias Pleroma.Maintenance
13 require Pleroma.Constants
20 @shortdoc "A collection of database related tasks"
21 @moduledoc File.read!("docs/docs/administration/CLI_tasks/database.md")
23 def run(["remove_embedded_objects" | args]) do
33 Logger.info("Removing embedded objects")
36 "update activities set data = safe_jsonb_set(data, '{object}'::text[], data->'object'->'id') where data->'object'->>'id' is not null;",
41 if Keyword.get(options, :vacuum) do
42 Maintenance.vacuum("full")
46 def run(["bump_all_conversations"]) do
48 Conversation.bump_for_all_activities()
51 def run(["update_users_following_followers_counts"]) do
56 from(u in User, select: u)
58 |> Stream.each(&User.update_follower_count/1)
65 def run(["prune_objects" | args]) do
71 keep_threads: :boolean,
72 keep_non_public: :boolean,
73 prune_orphaned_activities: :boolean
79 deadline = Pleroma.Config.get([:instance, :remote_post_retention_days])
80 time_deadline = NaiveDateTime.utc_now() |> NaiveDateTime.add(-(deadline * 86_400))
82 log_message = "Pruning objects older than #{deadline} days"
85 if Keyword.get(options, :keep_non_public) do
86 log_message <> ", keeping non public posts"
92 if Keyword.get(options, :keep_threads) do
93 log_message <> ", keeping threads intact"
99 if Keyword.get(options, :prune_orphaned_activities) do
100 log_message <> ", pruning orphaned activities"
106 if Keyword.get(options, :vacuum) do
108 ", doing a full vacuum (you shouldn't do this as a recurring maintanance task)"
113 Logger.info(log_message)
115 if Keyword.get(options, :keep_threads) do
116 # We want to delete objects from threads where
117 # 1. the newest post is still old
118 # 2. none of the activities is local
119 # 3. none of the activities is bookmarked
120 # 4. optionally none of the posts is non-public
122 if Keyword.get(options, :keep_non_public) do
124 |> join(:left, [a], b in Pleroma.Bookmark, on: a.id == b.activity_id)
125 |> group_by([a], fragment("? ->> 'context'::text", a.data))
129 # Posts (checked on Create Activity) is non-public
130 "bool_or((not(?->'to' \\? ? OR ?->'cc' \\? ?)) and ? ->> 'type' = 'Create')",
132 ^Pleroma.Constants.as_public(),
134 ^Pleroma.Constants.as_public(),
140 |> join(:left, [a], b in Pleroma.Bookmark, on: a.id == b.activity_id)
141 |> group_by([a], fragment("? ->> 'context'::text", a.data))
143 |> having([a], max(a.updated_at) < ^time_deadline)
144 |> having([a], not fragment("bool_or(?)", a.local))
145 |> having([_, b], fragment("max(?::text) is null", b.id))
146 |> select([a], fragment("? ->> 'context'::text", a.data))
149 |> where([o], fragment("? ->> 'context'::text", o.data) in subquery(deletable_context))
151 if Keyword.get(options, :keep_non_public) do
156 "?->'to' \\? ? OR ?->'cc' \\? ?",
158 ^Pleroma.Constants.as_public(),
160 ^Pleroma.Constants.as_public()
166 |> where([o], o.updated_at < ^time_deadline)
169 fragment("split_part(?->>'actor', '/', 3) != ?", o.data, ^Pleroma.Web.Endpoint.host())
172 |> Repo.delete_all(timeout: :infinity)
174 if Keyword.get(options, :prune_orphaned_activities) do
175 # Prune activities who link to a single object
177 delete from public.activities
179 select a.id from public.activities a
180 left join public.objects o on a.data ->> 'object' = o.data ->> 'id'
181 left join public.activities a2 on a.data ->> 'object' = a2.data ->> 'id'
182 left join public.users u on a.data ->> 'object' = u.ap_id
184 and jsonb_typeof(a."data" -> 'object') = 'string'
190 |> Repo.query([], timeout: :infinity)
192 # Prune activities who link to an array of objects
194 delete from public.activities
196 select a.id from public.activities a
197 join json_array_elements_text((a."data" -> 'object')::json) as j on jsonb_typeof(a."data" -> 'object') = 'array'
198 left join public.objects o on j.value = o.data ->> 'id'
199 left join public.activities a2 on j.value = a2.data ->> 'id'
200 left join public.users u on j.value = u.ap_id
202 having max(o.data ->> 'id') is null
203 and max(a2.data ->> 'id') is null
204 and max(u.ap_id) is null
207 |> Repo.query([], timeout: :infinity)
211 DELETE FROM hashtags AS ht
213 SELECT 1 FROM hashtags_objects hto
214 WHERE ht.id = hto.hashtag_id)
218 if Keyword.get(options, :vacuum) do
219 Maintenance.vacuum("full")
223 def run(["prune_task"]) do
227 |> Pleroma.Workers.Cron.PruneDatabaseWorker.perform()
230 def run(["fix_likes_collections"]) do
233 from(object in Object,
234 where: fragment("(?)->>'likes' is not null", object.data),
235 select: %{id: object.id, likes: fragment("(?)->>'likes'", object.data)}
237 |> Pleroma.Repo.chunk_stream(100, :batches)
238 |> Stream.each(fn objects ->
241 |> Enum.filter(fn object -> object.likes |> Jason.decode!() |> is_map() end)
245 |> where([object], object.id in ^ids)
250 "safe_jsonb_set(?, '{likes}', '[]'::jsonb, true)",
255 |> Repo.update_all([], timeout: :infinity)
260 def run(["vacuum", args]) do
263 Maintenance.vacuum(args)
266 def run(["ensure_expiration"]) do
268 days = Pleroma.Config.get([:mrf_activity_expiration, :days], 365)
271 |> join(:inner, [a], o in Object,
274 "(?->>'id') = COALESCE((?)->'object'->> 'id', (?)->>'object')",
280 |> where(local: true)
281 |> where([a], fragment("(? ->> 'type'::text) = 'Create'", a.data))
282 |> where([_a, o], fragment("?->>'type' = 'Note'", o.data))
283 |> Pleroma.Repo.chunk_stream(100, :batches)
284 |> Stream.each(fn activities ->
285 Enum.each(activities, fn activity ->
288 |> DateTime.from_naive!("Etc/UTC")
289 |> Timex.shift(days: days)
291 Pleroma.Workers.PurgeExpiredActivity.enqueue(%{
292 activity_id: activity.id,
293 expires_at: expires_at
300 def run(["set_text_search_config", tsconfig]) do
302 %{rows: [[tsc]]} = Ecto.Adapters.SQL.query!(Pleroma.Repo, "SHOW default_text_search_config;")
303 shell_info("Current default_text_search_config: #{tsc}")
305 %{rows: [[db]]} = Ecto.Adapters.SQL.query!(Pleroma.Repo, "SELECT current_database();")
306 shell_info("Update default_text_search_config: #{tsconfig}")
309 Ecto.Adapters.SQL.query!(
311 "ALTER DATABASE #{db} SET default_text_search_config = '#{tsconfig}';"
314 # non-exist config will not raise excpetion but only give >0 messages
315 if length(msg) > 0 do
316 shell_info("Error: #{inspect(msg, pretty: true)}")
318 rum_enabled = Pleroma.Config.get([:database, :rum_enabled])
319 shell_info("Recreate index, RUM: #{rum_enabled}")
321 # Note SQL below needs to be kept up-to-date with latest GIN or RUM index definition in future
323 Ecto.Adapters.SQL.query!(
325 "CREATE OR REPLACE FUNCTION objects_fts_update() RETURNS trigger AS $$ BEGIN
326 new.fts_content := to_tsvector(new.data->>'content');
329 $$ LANGUAGE plpgsql",
334 shell_info("Refresh RUM index")
335 Ecto.Adapters.SQL.query!(Pleroma.Repo, "UPDATE objects SET updated_at = NOW();")
337 Ecto.Adapters.SQL.query!(Pleroma.Repo, "DROP INDEX IF EXISTS objects_fts;")
339 Ecto.Adapters.SQL.query!(
341 "CREATE INDEX CONCURRENTLY objects_fts ON objects USING gin(to_tsvector('#{tsconfig}', data->>'content')); ",
351 # Rolls back a specific migration (leaving subsequent migrations applied).
352 # WARNING: imposes a risk of unrecoverable data loss — proceed at your own responsibility.
353 # Based on https://stackoverflow.com/a/53825840
354 def run(["rollback", version]) do
355 prompt = "SEVERE WARNING: this operation may result in unrecoverable data loss. Continue?"
357 if shell_prompt(prompt, "n") in ~w(Yn Y y) do
359 Ecto.Migrator.with_repo(Pleroma.Repo, fn repo ->
360 version = String.to_integer(version)
361 re = ~r/^#{version}_.*\.exs/
362 path = Ecto.Migrator.migrations_path(repo)
364 with {_, "" <> file} <- {:find, Enum.find(File.ls!(path), &String.match?(&1, re))},
365 {_, [{mod, _} | _]} <- {:compile, Code.compile_file(Path.join(path, file))},
366 {_, :ok} <- {:rollback, Ecto.Migrator.down(repo, version, mod)} do
367 {:ok, "Reversed migration: #{file}"}
369 {:find, _} -> {:error, "No migration found with version prefix: #{version}"}
370 {:compile, e} -> {:error, "Problem compiling migration module: #{inspect(e)}"}
371 {:rollback, e} -> {:error, "Problem reversing migration: #{inspect(e)}"}
375 shell_info(inspect(result))