diff options
Diffstat (limited to 'priv')
8 files changed, 305 insertions, 5 deletions
diff --git a/priv/repo/migrations/20190414125034_migrate_old_bookmarks.exs b/priv/repo/migrations/20190414125034_migrate_old_bookmarks.exs index b6f0ac66b..cbc9884a5 100644 --- a/priv/repo/migrations/20190414125034_migrate_old_bookmarks.exs +++ b/priv/repo/migrations/20190414125034_migrate_old_bookmarks.exs @@ -1,8 +1,8 @@ defmodule Pleroma.Repo.Migrations.MigrateOldBookmarks do use Ecto.Migration import Ecto.Query - alias Pleroma.Activity alias Pleroma.Bookmark + alias Pleroma.MigrationHelper.LegacyActivity alias Pleroma.Repo def up do @@ -18,7 +18,7 @@ defmodule Pleroma.Repo.Migrations.MigrateOldBookmarks do Enum.each(bookmarks, fn ap_id -> activity = ap_id - |> Activity.create_by_object_ap_id() + |> LegacyActivity.create_by_object_ap_id() |> Repo.one() unless is_nil(activity), do: {:ok, _} = Bookmark.create(user_id, activity.id) diff --git a/priv/repo/migrations/20200527163635_delete_notifications_from_invisible_users.exs b/priv/repo/migrations/20200527163635_delete_notifications_from_invisible_users.exs index 9e95a8111..a2010e188 100644 --- a/priv/repo/migrations/20200527163635_delete_notifications_from_invisible_users.exs +++ b/priv/repo/migrations/20200527163635_delete_notifications_from_invisible_users.exs @@ -6,7 +6,7 @@ defmodule Pleroma.Repo.Migrations.DeleteNotificationsFromInvisibleUsers do def up do Pleroma.Notification - |> join(:inner, [n], activity in assoc(n, :activity)) + |> join(:inner, [n], activity in "activities") |> where( [n, a], fragment("? in (SELECT ap_id FROM users WHERE invisible = true)", a.actor) diff --git a/priv/repo/migrations/20200914105638_delete_notification_without_activity.exs b/priv/repo/migrations/20200914105638_delete_notification_without_activity.exs index 9333fc5a1..252eb0716 100644 --- a/priv/repo/migrations/20200914105638_delete_notification_without_activity.exs +++ b/priv/repo/migrations/20200914105638_delete_notification_without_activity.exs @@ -7,7 +7,7 @@ defmodule Pleroma.Repo.Migrations.DeleteNotificationWithoutActivity do def up do from( q in Pleroma.Notification, - left_join: c in assoc(q, :activity), + left_join: c in "activities", select: %{id: type(q.id, :integer)}, where: is_nil(c.id) ) diff --git a/priv/repo/migrations/20210205145000_move_pinned_activities_into_pinned_objects.exs b/priv/repo/migrations/20210205145000_move_pinned_activities_into_pinned_objects.exs index 9aee545e3..a6dd886bf 100644 --- a/priv/repo/migrations/20210205145000_move_pinned_activities_into_pinned_objects.exs +++ b/priv/repo/migrations/20210205145000_move_pinned_activities_into_pinned_objects.exs @@ -3,6 +3,7 @@ defmodule Pleroma.Repo.Migrations.MovePinnedActivitiesIntoPinnedObjects do import Ecto.Query + alias Pleroma.MigrationHelper.LegacyActivity alias Pleroma.Repo alias Pleroma.User @@ -11,7 +12,7 @@ defmodule Pleroma.Repo.Migrations.MovePinnedActivitiesIntoPinnedObjects do |> select([u], {u.id, fragment("?.pinned_activities", u)}) |> Repo.stream() |> Stream.each(fn {user_id, pinned_activities_ids} -> - pinned_activities = Pleroma.Activity.all_by_ids_with_object(pinned_activities_ids) + pinned_activities = LegacyActivity.all_by_ids_with_object(pinned_activities_ids) pins = Map.new(pinned_activities, fn %{object: %{data: %{"id" => object_id}}} -> diff --git a/priv/repo/migrations/20211218181632_change_object_id_to_flake.exs b/priv/repo/migrations/20211218181632_change_object_id_to_flake.exs new file mode 100644 index 000000000..da62063f5 --- /dev/null +++ b/priv/repo/migrations/20211218181632_change_object_id_to_flake.exs @@ -0,0 +1,51 @@ +defmodule Pleroma.Repo.Migrations.ChangeObjectIdToFlake do + @moduledoc """ + Convert object IDs to FlakeIds. + Fortunately only a few tables have a foreign key to objects. Update them. + """ + use Ecto.Migration + + def up do + # Switch object IDs to FlakeIds + execute(""" + alter table objects + drop constraint objects_pkey cascade, + alter column id drop default, + alter column id set data type uuid using cast( lpad( to_hex(id), 32, '0') as uuid), + add primary key (id) + """) + + # Update data_migration_failed_ids + execute(""" + alter table data_migration_failed_ids + drop constraint data_migration_failed_ids_pkey cascade, + alter column record_id set data type uuid using cast( lpad( to_hex(record_id), 32, '0') as uuid), + add primary key (data_migration_id, record_id) + """) + + # Update chat message foreign key + execute(""" + alter table chat_message_references + alter column object_id set data type uuid using cast( lpad( to_hex(object_id), 32, '0') as uuid), + add constraint chat_message_references_object_id_fkey foreign key (object_id) references objects(id) on delete cascade + """) + + # Update delivery foreign key + execute(""" + alter table deliveries + alter column object_id set data type uuid using cast( lpad( to_hex(object_id), 32, '0') as uuid), + add constraint deliveries_object_id_fkey foreign key (object_id) references objects(id) on delete cascade + """) + + # Update hashtag many-to-many foreign key + execute(""" + alter table hashtags_objects + alter column object_id set data type uuid using cast( lpad( to_hex(object_id), 32, '0') as uuid), + add constraint hashtags_objects_object_id_fkey foreign key (object_id) references objects(id) on delete cascade + """) + end + + def down do + :ok + end +end diff --git a/priv/repo/migrations/20211218181640_resolve_activity_object_conflicts.exs b/priv/repo/migrations/20211218181640_resolve_activity_object_conflicts.exs new file mode 100644 index 000000000..71bca1cbe --- /dev/null +++ b/priv/repo/migrations/20211218181640_resolve_activity_object_conflicts.exs @@ -0,0 +1,49 @@ +defmodule Pleroma.Repo.Migrations.ResolveActivityObjectConflicts do + @moduledoc """ + Find objects with a conflicting activity ID, and update them. + This should only happen on servers that existed before "20181218172826_users_and_activities_flake_id". + """ + use Ecto.Migration + + alias Pleroma.Object + alias Pleroma.MigrationHelper.ObjectId + alias Pleroma.Repo + + import Ecto.Query + + def up do + # Lock relevant tables + execute("LOCK TABLE objects") + execute("LOCK TABLE chat_message_references") + execute("LOCK TABLE deliveries") + execute("LOCK TABLE hashtags_objects") + + # Temporarily disable triggers (and by consequence, fkey constraints) + # https://stackoverflow.com/a/18709987 + Repo.query!("SET session_replication_role = replica") + + # Update conflicting objects + activity_conflict_query() + |> Repo.stream() + |> Stream.each(&update_object!/1) + |> Stream.run() + + # Re-enable triggers + Repo.query!("SET session_replication_role = DEFAULT") + end + + # Get only objects with a conflicting activity ID. + defp activity_conflict_query() do + join(Object, :inner, [o], a in "activities", on: a.id == o.id) + end + + # Update the object and its relations with a newly-generated ID. + defp update_object!(object) do + new_id = ObjectId.flake_from_time(object.inserted_at) + {:ok, %Object{}} = ObjectId.change_id(object, new_id) + end + + def down do + :ok + end +end diff --git a/priv/repo/migrations/20211218181647_combine_activities_and_objects.exs b/priv/repo/migrations/20211218181647_combine_activities_and_objects.exs new file mode 100644 index 000000000..7fabf72f3 --- /dev/null +++ b/priv/repo/migrations/20211218181647_combine_activities_and_objects.exs @@ -0,0 +1,159 @@ +defmodule Pleroma.Repo.Migrations.CombineActivitiesAndObjects do + use Ecto.Migration + + @function_name "update_status_visibility_counter_cache" + @trigger_name "status_visibility_counter_cache_trigger" + + def up do + # Lock both tables to avoid a running server meddling with our transaction + execute("LOCK TABLE activities") + execute("LOCK TABLE objects") + + # Add missing fields to objects table + alter table(:objects) do + add(:local, :boolean, null: false, default: true) + add(:actor, :string) + add(:recipients, {:array, :string}, default: []) + end + + # Add missing indexes to objects + create_if_not_exists(index(:objects, [:local])) + create_if_not_exists(index(:objects, [:actor, "id DESC NULLS LAST"])) + create_if_not_exists(index(:objects, [:recipients], using: :gin)) + + # Intentionally omit these. According to LiveDashboard they're not used: + # + # create_if_not_exists( + # index(:objects, ["(data->'to')"], name: :objects_to_index, using: :gin) + # ) + # + # create_if_not_exists( + # index(:objects, ["(data->'cc')"], name: :objects_cc_index, using: :gin) + # ) + + create_if_not_exists( + index(:objects, ["(data->>'actor')", "inserted_at desc"], name: :objects_actor_index) + ) + + # Some obscure Fediverse backends (WordPress, Juick) send a Create and a Note + # with the exact same ActivityPub ID. This violates the spec and doesn't + # work in the new system. WordPress devs were notified. + execute( + "DELETE FROM activities USING objects WHERE activities.data->>'id' = objects.data->>'id'" + ) + + # Copy all activities into the newly formatted objects table + execute( + "INSERT INTO objects (id, data, local, actor, recipients, inserted_at, updated_at) SELECT id, data, local, actor, recipients, inserted_at, updated_at FROM activities ON CONFLICT DO NOTHING" + ) + + # Update notifications foreign key + execute("alter table notifications drop constraint notifications_activity_id_fkey") + + execute( + "alter table notifications add constraint notifications_object_id_fkey foreign key (activity_id) references objects(id) on delete cascade" + ) + + # Update bookmarks foreign key + execute("alter table bookmarks drop constraint bookmarks_activity_id_fkey") + + execute( + "alter table bookmarks add constraint bookmarks_object_id_fkey foreign key (activity_id) references objects(id) on delete cascade" + ) + + # Update report notes foreign key + execute("alter table report_notes drop constraint report_notes_activity_id_fkey") + + execute( + "alter table report_notes add constraint report_notes_object_id_fkey foreign key (activity_id) references objects(id)" + ) + + # Nuke the old activities table + execute("drop table activities") + + # Update triggers + """ + CREATE TRIGGER #{@trigger_name} + BEFORE + INSERT + OR UPDATE of recipients, data + OR DELETE + ON objects + FOR EACH ROW + EXECUTE PROCEDURE #{@function_name}(); + """ + |> execute() + + execute("drop function if exists thread_visibility(actor varchar, activity_id varchar)") + execute(update_thread_visibility()) + end + + def down do + raise "Lol, there's no going back from this." + end + + # It acts upon objects instead of activities now + def update_thread_visibility do + """ + CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, object_id varchar) RETURNS boolean AS $$ + DECLARE + public varchar := 'https://www.w3.org/ns/activitystreams#Public'; + child objects%ROWTYPE; + object objects%ROWTYPE; + author_fa varchar; + valid_recipients varchar[]; + actor_user_following varchar[]; + BEGIN + --- Fetch actor following + SELECT array_agg(following.follower_address) INTO actor_user_following FROM following_relationships + JOIN users ON users.id = following_relationships.follower_id + JOIN users AS following ON following.id = following_relationships.following_id + WHERE users.ap_id = actor; + + --- Fetch our initial object. + SELECT * INTO object FROM objects WHERE objects.data->>'id' = object_id; + + LOOP + --- Ensure that we have an object before continuing. + --- If we don't, the thread is not satisfiable. + IF object IS NULL THEN + RETURN false; + END IF; + + --- We only care about Create objects. + IF object.data->>'type' != 'Create' THEN + RETURN true; + END IF; + + --- Normalize the child object into child. + SELECT * INTO child FROM objects + WHERE COALESCE(object.data->'object'->>'id', object.data->>'object') = objects.data->>'id'; + + --- Fetch the author's AS2 following collection. + SELECT COALESCE(users.follower_address, '') INTO author_fa FROM users WHERE users.ap_id = object.actor; + + --- Prepare valid recipients array. + valid_recipients := ARRAY[actor, public]; + IF ARRAY[author_fa] && actor_user_following THEN + valid_recipients := valid_recipients || author_fa; + END IF; + + --- Check visibility. + IF NOT valid_recipients && object.recipients THEN + --- object not visible, break out of the loop + RETURN false; + END IF; + + --- If there's a parent, load it and do this all over again. + IF (child.data->'inReplyTo' IS NOT NULL) AND (child.data->'inReplyTo' != 'null'::jsonb) THEN + SELECT * INTO object FROM objects + WHERE child.data->>'inReplyTo' = objects.data->>'id'; + ELSE + RETURN true; + END IF; + END LOOP; + END; + $$ LANGUAGE plpgsql IMMUTABLE; + """ + end +end diff --git a/priv/repo/migrations/20211231013155_add_object_concurrent_indexes.exs b/priv/repo/migrations/20211231013155_add_object_concurrent_indexes.exs new file mode 100644 index 000000000..3d112d172 --- /dev/null +++ b/priv/repo/migrations/20211231013155_add_object_concurrent_indexes.exs @@ -0,0 +1,40 @@ +defmodule Pleroma.Repo.Migrations.AddObjectConcurrentIndexes do + use Ecto.Migration + @disable_migration_lock true + @disable_ddl_transaction true + + def change do + create_if_not_exists(index(:objects, [:actor, "id DESC NULLS LAST"], concurrently: true)) + + create_if_not_exists( + index(:objects, ["(data->>'type')", "(data->>'context')"], + name: :objects_context_index, + concurrently: true + ) + ) + + create_if_not_exists( + index(:objects, ["(split_part(actor, '/', 3))"], + concurrently: true, + name: :objects_hosts + ) + ) + + create_if_not_exists(index(:objects, ["id desc nulls last", "local"], concurrently: true)) + + create_if_not_exists( + index(:objects, ["activity_visibility(actor, recipients, data)", "id DESC NULLS LAST"], + name: :objects_visibility_index, + concurrently: true, + where: "data->>'type' = 'Create'" + ) + ) + + create_if_not_exists( + index(:objects, ["(coalesce(data->'object'->>'id', data->>'object'))"], + name: :objects_create_objects_index, + concurrently: true + ) + ) + end +end |