From 761ad0b48e552e7b199fe1a624e3d03aa91981a3 Mon Sep 17 00:00:00 2001 From: Egor Kislitsyn Date: Tue, 8 Oct 2019 20:27:42 +0700 Subject: [PATCH] Split CreateFollowingRelationships to multiple migrations --- ...7073319_create_following_relationships.exs | 92 ------------------- ...132217_migrate_following_relationships.exs | 89 ++++++++++++++++++ .../20191008132427_drop_users_following.exs | 14 +++ 3 files changed, 103 insertions(+), 92 deletions(-) create mode 100644 priv/repo/migrations/20191008132217_migrate_following_relationships.exs create mode 100644 priv/repo/migrations/20191008132427_drop_users_following.exs diff --git a/priv/repo/migrations/20191007073319_create_following_relationships.exs b/priv/repo/migrations/20191007073319_create_following_relationships.exs index fe229240b..8ffc870d9 100644 --- a/priv/repo/migrations/20191007073319_create_following_relationships.exs +++ b/priv/repo/migrations/20191007073319_create_following_relationships.exs @@ -3,8 +3,6 @@ defmodule Pleroma.Repo.Migrations.CreateFollowingRelationships do # had to disable these to be able to restore `following` index concurrently # https://hexdocs.pm/ecto_sql/Ecto.Migration.html#index/3-adding-dropping-indexes-concurrently - @disable_ddl_transaction true - @disable_migration_lock true def change do create_if_not_exists table(:following_relationships) do @@ -17,95 +15,5 @@ defmodule Pleroma.Repo.Migrations.CreateFollowingRelationships do create_if_not_exists(index(:following_relationships, :follower_id)) create_if_not_exists(unique_index(:following_relationships, [:follower_id, :following_id])) - - execute(import_following_from_users(), "") - execute(import_following_from_activities(), restore_following_column()) - - drop(index(:users, [:following], concurrently: true, using: :gin)) - - alter table(:users) do - remove(:following, {:array, :string}, default: []) - end - end - - defp import_following_from_users do - """ - INSERT INTO following_relationships (follower_id, following_id, state, inserted_at, updated_at) - SELECT - relations.follower_id, - following.id, - 'accept', - now(), - now() - FROM ( - SELECT - users.id AS follower_id, - unnest(users.following) AS following_ap_id - FROM - users - WHERE - users.following != '{}' - AND users.local = false OR users.local = true AND users.email IS NOT NULL -- Exclude `internal/fetch` and `relay` - ) AS relations - JOIN users AS "following" ON "following".follower_address = relations.following_ap_id - - WHERE relations.follower_id != following.id - ON CONFLICT DO NOTHING - """ - end - - defp import_following_from_activities do - """ - INSERT INTO - following_relationships ( - follower_id, - following_id, - state, - inserted_at, - updated_at - ) - SELECT - followers.id, - following.id, - activities.data ->> 'state', - (activities.data ->> 'published') :: timestamp, - now() - FROM - activities - JOIN users AS followers ON (activities.actor = followers.ap_id) - JOIN users AS following ON (activities.data ->> 'object' = following.ap_id) - WHERE - activities.data ->> 'type' = 'Follow' - AND activities.data ->> 'state' IN ('accept', 'pending', 'reject') - ORDER BY activities.updated_at DESC - ON CONFLICT DO NOTHING - """ - end - - defp restore_following_column do - """ - UPDATE - users - SET - following = following_query.following_array, - updated_at = now() - FROM ( - SELECT - follwer.id AS follower_id, - CASE follwer.local - WHEN TRUE THEN - array_prepend(follwer.follower_address, array_agg(following.follower_address)) - ELSE - array_agg(following.follower_address) - END AS following_array - FROM - following_relationships - JOIN users AS follwer ON follwer.id = following_relationships.follower_id - JOIN users AS FOLLOWING ON following.id = following_relationships.following_id - GROUP BY - follwer.id) AS following_query - WHERE - following_query.follower_id = users.id - """ end end diff --git a/priv/repo/migrations/20191008132217_migrate_following_relationships.exs b/priv/repo/migrations/20191008132217_migrate_following_relationships.exs new file mode 100644 index 000000000..7f996f5a5 --- /dev/null +++ b/priv/repo/migrations/20191008132217_migrate_following_relationships.exs @@ -0,0 +1,89 @@ +defmodule Pleroma.Repo.Migrations.MigrateFollowingRelationships do + use Ecto.Migration + + def change do + execute(import_following_from_users(), "") + execute(import_following_from_activities(), restore_following_column()) + end + + defp import_following_from_users do + """ + INSERT INTO following_relationships (follower_id, following_id, state, inserted_at, updated_at) + SELECT + relations.follower_id, + following.id, + 'accept', + now(), + now() + FROM ( + SELECT + users.id AS follower_id, + unnest(users.following) AS following_ap_id + FROM + users + WHERE + users.following != '{}' + AND users.local = false OR users.local = true AND users.email IS NOT NULL -- Exclude `internal/fetch` and `relay` + ) AS relations + JOIN users AS "following" ON "following".follower_address = relations.following_ap_id + + WHERE relations.follower_id != following.id + ON CONFLICT DO NOTHING + """ + end + + defp import_following_from_activities do + """ + INSERT INTO + following_relationships ( + follower_id, + following_id, + state, + inserted_at, + updated_at + ) + SELECT + followers.id, + following.id, + activities.data ->> 'state', + (activities.data ->> 'published') :: timestamp, + now() + FROM + activities + JOIN users AS followers ON (activities.actor = followers.ap_id) + JOIN users AS following ON (activities.data ->> 'object' = following.ap_id) + WHERE + activities.data ->> 'type' = 'Follow' + AND activities.data ->> 'state' IN ('accept', 'pending', 'reject') + ORDER BY activities.updated_at DESC + ON CONFLICT DO NOTHING + """ + end + + defp restore_following_column do + """ + UPDATE + users + SET + following = following_query.following_array, + updated_at = now() + FROM ( + SELECT + follwer.id AS follower_id, + CASE follwer.local + WHEN TRUE THEN + array_prepend(follwer.follower_address, array_agg(following.follower_address)) + ELSE + array_agg(following.follower_address) + END AS following_array + FROM + following_relationships + JOIN users AS follwer ON follwer.id = following_relationships.follower_id + JOIN users AS FOLLOWING ON following.id = following_relationships.following_id + GROUP BY + follwer.id) AS following_query + WHERE + following_query.follower_id = users.id + """ + end +end diff --git a/priv/repo/migrations/20191008132427_drop_users_following.exs b/priv/repo/migrations/20191008132427_drop_users_following.exs new file mode 100644 index 000000000..17805db36 --- /dev/null +++ b/priv/repo/migrations/20191008132427_drop_users_following.exs @@ -0,0 +1,14 @@ +defmodule Pleroma.Repo.Migrations.DropUsersFollowing do + use Ecto.Migration + + @disable_ddl_transaction true + @disable_migration_lock true + + def change do + drop(index(:users, [:following], concurrently: true, using: :gin)) + + alter table(:users) do + remove(:following, {:array, :string}, default: []) + end + end +end -- 2.45.2