From 12ebeef1300b70e44b2aa25dbffeb82df261e26d Mon Sep 17 00:00:00 2001 From: Egor Kislitsyn Date: Mon, 7 Oct 2019 19:36:10 +0700 Subject: Add CreateFollowingRelationships migration --- ...191007073319_create_following_relationships.exs | 92 ++++++++++++++++++++++ 1 file changed, 92 insertions(+) create mode 100644 priv/repo/migrations/20191007073319_create_following_relationships.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191007073319_create_following_relationships.exs b/priv/repo/migrations/20191007073319_create_following_relationships.exs new file mode 100644 index 000000000..7b8a2f9bd --- /dev/null +++ b/priv/repo/migrations/20191007073319_create_following_relationships.exs @@ -0,0 +1,92 @@ +defmodule Pleroma.Repo.Migrations.CreateFollowingRelationships do + use Ecto.Migration + + # 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 + add(:follower_id, references(:users, type: :uuid, on_delete: :delete_all), null: false) + add(:following_id, references(:users, type: :uuid, on_delete: :delete_all), null: false) + add(:state, :string, null: false) + + timestamps() + end + + create_if_not_exists(index(:following_relationships, :follower_id)) + create_if_not_exists(unique_index(:following_relationships, [:follower_id, :following_id])) + + execute(insert_following_relationships_rows(), restore_following_column()) + + drop(index(:users, [:following], concurrently: true, using: :gin)) + + alter table(:users) do + remove(:following, {:array, :string}, default: []) + end + end + + defp insert_following_relationships_rows 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') ON CONFLICT DO NOTHING + """ + end + + defp restore_following_column do + """ + UPDATE + users + SET + following = following_query.following, + updated_at = now() + FROM + ( + SELECT + followers.id AS follower_id, + array_prepend( + followers.follower_address, + array_agg(DISTINCT following.ap_id) FILTER ( + WHERE + following.ap_id IS NOT NULL + ) + ) as following + FROM + users AS followers + LEFT OUTER JOIN activities ON ( + followers.ap_id = activities.actor + AND activities.data ->> 'type' = 'Follow' + AND activities.data ->> 'state' IN ('accept', 'pending', 'reject') + ) + LEFT OUTER JOIN users AS following ON (activities.data ->> 'object' = following.ap_id) + WHERE + followers.email IS NOT NULL -- Exclude `internal/fetch` and `relay` + GROUP BY + followers.id, + followers.ap_id + ) AS following_query + WHERE + following_query.follower_id = users.id; + """ + end +end -- cgit v1.2.3 From 6291eaa5902d5c1565e8969117fdf92c373716b6 Mon Sep 17 00:00:00 2001 From: Egor Kislitsyn Date: Tue, 8 Oct 2019 13:06:48 +0700 Subject: Update CreateFollowingRelationships --- ...191007073319_create_following_relationships.exs | 79 ++++++++++++++-------- 1 file changed, 49 insertions(+), 30 deletions(-) (limited to 'priv') diff --git a/priv/repo/migrations/20191007073319_create_following_relationships.exs b/priv/repo/migrations/20191007073319_create_following_relationships.exs index 7b8a2f9bd..fe229240b 100644 --- a/priv/repo/migrations/20191007073319_create_following_relationships.exs +++ b/priv/repo/migrations/20191007073319_create_following_relationships.exs @@ -18,7 +18,8 @@ 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(insert_following_relationships_rows(), restore_following_column()) + execute(import_following_from_users(), "") + execute(import_following_from_activities(), restore_following_column()) drop(index(:users, [:following], concurrently: true, using: :gin)) @@ -27,7 +28,33 @@ defmodule Pleroma.Repo.Migrations.CreateFollowingRelationships do end end - defp insert_following_relationships_rows do + 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 ( @@ -49,7 +76,9 @@ defmodule Pleroma.Repo.Migrations.CreateFollowingRelationships do JOIN users AS following ON (activities.data ->> 'object' = following.ap_id) WHERE activities.data ->> 'type' = 'Follow' - AND activities.data ->> 'state' IN ('accept', 'pending', 'reject') ON CONFLICT DO NOTHING + AND activities.data ->> 'state' IN ('accept', 'pending', 'reject') + ORDER BY activities.updated_at DESC + ON CONFLICT DO NOTHING """ end @@ -58,35 +87,25 @@ defmodule Pleroma.Repo.Migrations.CreateFollowingRelationships do UPDATE users SET - following = following_query.following, + following = following_query.following_array, updated_at = now() - FROM - ( - SELECT - followers.id AS follower_id, - array_prepend( - followers.follower_address, - array_agg(DISTINCT following.ap_id) FILTER ( - WHERE - following.ap_id IS NOT NULL - ) - ) as following - FROM - users AS followers - LEFT OUTER JOIN activities ON ( - followers.ap_id = activities.actor - AND activities.data ->> 'type' = 'Follow' - AND activities.data ->> 'state' IN ('accept', 'pending', 'reject') - ) - LEFT OUTER JOIN users AS following ON (activities.data ->> 'object' = following.ap_id) - WHERE - followers.email IS NOT NULL -- Exclude `internal/fetch` and `relay` - GROUP BY - followers.id, - followers.ap_id - ) AS following_query + 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; + following_query.follower_id = users.id """ end end -- cgit v1.2.3 From 761ad0b48e552e7b199fe1a624e3d03aa91981a3 Mon Sep 17 00:00:00 2001 From: Egor Kislitsyn Date: Tue, 8 Oct 2019 20:27:42 +0700 Subject: Split CreateFollowingRelationships to multiple migrations --- ...191007073319_create_following_relationships.exs | 92 ---------------------- ...91008132217_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 (limited to 'priv') 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 -- cgit v1.2.3 From 2c7ff32e5b95926af9b6573a6dc6ea96e3ba7dd5 Mon Sep 17 00:00:00 2001 From: Egor Kislitsyn Date: Thu, 10 Oct 2019 21:11:34 +0700 Subject: Add `thread_visibility` to migrations --- ...191007073319_create_following_relationships.exs | 136 ++++++++++++++++++++- .../20191008132427_drop_users_following.exs | 2 + 2 files changed, 135 insertions(+), 3 deletions(-) (limited to 'priv') diff --git a/priv/repo/migrations/20191007073319_create_following_relationships.exs b/priv/repo/migrations/20191007073319_create_following_relationships.exs index 8ffc870d9..7daaf0575 100644 --- a/priv/repo/migrations/20191007073319_create_following_relationships.exs +++ b/priv/repo/migrations/20191007073319_create_following_relationships.exs @@ -1,9 +1,6 @@ defmodule Pleroma.Repo.Migrations.CreateFollowingRelationships do use Ecto.Migration - # 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 - def change do create_if_not_exists table(:following_relationships) do add(:follower_id, references(:users, type: :uuid, on_delete: :delete_all), null: false) @@ -15,5 +12,138 @@ 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(update_thread_visibility(), restore_thread_visibility()) + end + + # The only difference with the original verion: `actor_user` replaced with `actor_user_following` + def update_thread_visibility do + """ + CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar) RETURNS boolean AS $$ + DECLARE + public varchar := 'https://www.w3.org/ns/activitystreams#Public'; + child objects%ROWTYPE; + activity activities%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 activity. + SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id; + + LOOP + --- Ensure that we have an activity before continuing. + --- If we don't, the thread is not satisfiable. + IF activity IS NULL THEN + RETURN false; + END IF; + + --- We only care about Create activities. + IF activity.data->>'type' != 'Create' THEN + RETURN true; + END IF; + + --- Normalize the child object into child. + SELECT * INTO child FROM objects + INNER JOIN activities ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id' + WHERE COALESCE(activity.data->'object'->>'id', activity.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 = activity.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 && activity.recipients THEN + --- activity 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 activity FROM activities + INNER JOIN objects ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id' + WHERE child.data->>'inReplyTo' = objects.data->>'id'; + ELSE + RETURN true; + END IF; + END LOOP; + END; + $$ LANGUAGE plpgsql IMMUTABLE; + """ + end + + # priv/repo/migrations/20190515222404_add_thread_visibility_function.exs + def restore_thread_visibility do + """ + CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar) RETURNS boolean AS $$ + DECLARE + public varchar := 'https://www.w3.org/ns/activitystreams#Public'; + child objects%ROWTYPE; + activity activities%ROWTYPE; + actor_user users%ROWTYPE; + author_fa varchar; + valid_recipients varchar[]; + BEGIN + --- Fetch our actor. + SELECT * INTO actor_user FROM users WHERE users.ap_id = actor; + + --- Fetch our initial activity. + SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id; + + LOOP + --- Ensure that we have an activity before continuing. + --- If we don't, the thread is not satisfiable. + IF activity IS NULL THEN + RETURN false; + END IF; + + --- We only care about Create activities. + IF activity.data->>'type' != 'Create' THEN + RETURN true; + END IF; + + --- Normalize the child object into child. + SELECT * INTO child FROM objects + INNER JOIN activities ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id' + WHERE COALESCE(activity.data->'object'->>'id', activity.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 = activity.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 && activity.recipients THEN + --- activity 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 activity FROM activities + INNER JOIN objects ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id' + 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/20191008132427_drop_users_following.exs b/priv/repo/migrations/20191008132427_drop_users_following.exs index 17805db36..21c0af9f4 100644 --- a/priv/repo/migrations/20191008132427_drop_users_following.exs +++ b/priv/repo/migrations/20191008132427_drop_users_following.exs @@ -1,6 +1,8 @@ defmodule Pleroma.Repo.Migrations.DropUsersFollowing do use Ecto.Migration + # 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 -- cgit v1.2.3 From 059005ff829c0313c62ddf5fbcd95f8892920228 Mon Sep 17 00:00:00 2001 From: Egor Kislitsyn Date: Fri, 11 Oct 2019 02:35:32 +0700 Subject: Replace `user.following` with Pleroma.FollowingRelationship --- priv/repo/migrations/20191007073319_create_following_relationships.exs | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'priv') diff --git a/priv/repo/migrations/20191007073319_create_following_relationships.exs b/priv/repo/migrations/20191007073319_create_following_relationships.exs index 7daaf0575..d49e24ee4 100644 --- a/priv/repo/migrations/20191007073319_create_following_relationships.exs +++ b/priv/repo/migrations/20191007073319_create_following_relationships.exs @@ -16,7 +16,7 @@ defmodule Pleroma.Repo.Migrations.CreateFollowingRelationships do execute(update_thread_visibility(), restore_thread_visibility()) end - # The only difference with the original verion: `actor_user` replaced with `actor_user_following` + # The only difference between the original version: `actor_user` replaced with `actor_user_following` def update_thread_visibility do """ CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar) RETURNS boolean AS $$ -- cgit v1.2.3 From 8ad015ef64e0d2a4cd9f2979ff08d28be3a635e5 Mon Sep 17 00:00:00 2001 From: Egor Kislitsyn Date: Mon, 14 Oct 2019 14:16:57 +0700 Subject: Skip deactivated users in followers import --- priv/repo/migrations/20191008132217_migrate_following_relationships.exs | 1 + 1 file changed, 1 insertion(+) (limited to 'priv') diff --git a/priv/repo/migrations/20191008132217_migrate_following_relationships.exs b/priv/repo/migrations/20191008132217_migrate_following_relationships.exs index 7f996f5a5..c9bc890aa 100644 --- a/priv/repo/migrations/20191008132217_migrate_following_relationships.exs +++ b/priv/repo/migrations/20191008132217_migrate_following_relationships.exs @@ -55,6 +55,7 @@ defmodule Pleroma.Repo.Migrations.MigrateFollowingRelationships do WHERE activities.data ->> 'type' = 'Follow' AND activities.data ->> 'state' IN ('accept', 'pending', 'reject') + AND NOT (followers.info ? 'deactivated' AND followers.info -> 'deactivated' @> 'true') ORDER BY activities.updated_at DESC ON CONFLICT DO NOTHING """ -- cgit v1.2.3 From 10ff01acd95d42314b4eb923e5b7a7191356b73e Mon Sep 17 00:00:00 2001 From: Ivan Tashkinov Date: Wed, 16 Oct 2019 21:59:21 +0300 Subject: [#1304] Moved all non-mutes / non-blocks fields from User.Info to User. WIP. --- ...5158_add_following_address_from_source_data.exs | 6 ++- ...711042024_copy_muted_to_muted_notifications.exs | 1 - ...91009154608_copy_users_info_fields_to_users.exs | 54 ++++++++++++++++++++++ 3 files changed, 59 insertions(+), 2 deletions(-) create mode 100644 priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs (limited to 'priv') diff --git a/priv/repo/migrations/20190710125158_add_following_address_from_source_data.exs b/priv/repo/migrations/20190710125158_add_following_address_from_source_data.exs index 779aa382e..a5170d521 100644 --- a/priv/repo/migrations/20190710125158_add_following_address_from_source_data.exs +++ b/priv/repo/migrations/20190710125158_add_following_address_from_source_data.exs @@ -5,7 +5,11 @@ defmodule Pleroma.Repo.Migrations.AddFollowingAddressFromSourceData do def change do query = - User.external_users_query() + User.Query.build(%{ + external: true, + legacy_active: true, + order_by: :id + }) |> select([u], struct(u, [:id, :ap_id, :info])) Pleroma.Repo.stream(query) diff --git a/priv/repo/migrations/20190711042024_copy_muted_to_muted_notifications.exs b/priv/repo/migrations/20190711042024_copy_muted_to_muted_notifications.exs index bc4e828cc..dbddac516 100644 --- a/priv/repo/migrations/20190711042024_copy_muted_to_muted_notifications.exs +++ b/priv/repo/migrations/20190711042024_copy_muted_to_muted_notifications.exs @@ -1,6 +1,5 @@ defmodule Pleroma.Repo.Migrations.CopyMutedToMutedNotifications do use Ecto.Migration - alias Pleroma.User def change do execute( diff --git a/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs b/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs new file mode 100644 index 000000000..b26b122eb --- /dev/null +++ b/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs @@ -0,0 +1,54 @@ +defmodule Pleroma.Repo.Migrations.CopyUsersInfoaddsToUsers do + use Ecto.Migration + + def change do + alter table(:users) do + add(:banner, :map, default: %{}) + add(:background, :map, default: %{}) + add(:source_data, :map, default: %{}) + add(:note_count, :integer, default: 0) + add(:follower_count, :integer, default: 0) + # Should be filled in only for remote users + add(:following_count, :integer, default: nil) + add(:locked, :boolean, default: false) + add(:confirmation_pending, :boolean, default: false) + add(:password_reset_pending, :boolean, default: false) + add(:confirmation_token, :text, default: nil) + add(:default_scope, :string, default: "public") + add(:blocks, {:array, :text}, default: []) + add(:domain_blocks, {:array, :text}, default: []) + add(:mutes, {:array, :text}, default: []) + add(:muted_reblogs, {:array, :text}, default: []) + add(:muted_notifications, {:array, :text}, default: []) + add(:subscribers, {:array, :text}, default: []) + add(:deactivated, :boolean, default: false, null: false) + add(:no_rich_text, :boolean, default: false, null: false) + add(:ap_enabled, :boolean, default: false, null: false) + add(:is_moderator, :boolean, default: false, null: false) + add(:is_admin, :boolean, default: false, null: false) + add(:show_role, :boolean, default: true, null: false) + add(:settings, :map, default: nil) + add(:magic_key, :text, default: nil) + add(:uri, :text, default: nil) + add(:topic, :text, default: nil) + add(:hub, :text, default: nil) + add(:salmon, :text, default: nil) + add(:hide_followers_count, :boolean, default: false, null: false) + add(:hide_follows_count, :boolean, default: false, null: false) + add(:hide_followers, :boolean, default: false, null: false) + add(:hide_follows, :boolean, default: false, null: false) + add(:hide_favorites, :boolean, default: true, null: false) + add(:unread_conversation_count, :integer, default: 0) + add(:pinned_activities, {:array, :text}, default: []) + add(:email_notifications, :map, default: %{"digest" => false}) + add(:mascot, :map, default: nil) + add(:emoji, {:array, :map}, default: []) + add(:pleroma_settings_store, :map, default: %{}) + add(:fields, {:array, :map}, default: nil) + add(:raw_fields, {:array, :map}, default: []) + add(:discoverable, :boolean, default: false, null: false) + add(:notification_settings, :map, default: %{}) + add(:skip_thread_containment, :boolean, default: false, null: false) + end + end +end -- cgit v1.2.3 From 66b5d0ff558bffeddf6475af72b73bf2870512f6 Mon Sep 17 00:00:00 2001 From: Maksim Pechnikov Date: Thu, 17 Oct 2019 15:26:59 +0300 Subject: add Markers /api/v1/markers --- priv/repo/migrations/20191014181019_create_markers.exs | 15 +++++++++++++++ 1 file changed, 15 insertions(+) create mode 100644 priv/repo/migrations/20191014181019_create_markers.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191014181019_create_markers.exs b/priv/repo/migrations/20191014181019_create_markers.exs new file mode 100644 index 000000000..c717831ba --- /dev/null +++ b/priv/repo/migrations/20191014181019_create_markers.exs @@ -0,0 +1,15 @@ +defmodule Pleroma.Repo.Migrations.CreateMarkers do + use Ecto.Migration + + def change do + create_if_not_exists table(:markers) do + add(:user_id, references(:users, type: :uuid, on_delete: :delete_all)) + add(:timeline, :string, default: "", null: false) + add(:last_read_id, :string, default: "", null: false) + add(:lock_version, :integer, default: 0, null: false) + timestamps() + end + + create_if_not_exists(unique_index(:markers, [:user_id, :timeline])) + end +end -- cgit v1.2.3 From 39e996528c1e7551675c0d0f140dcfa01e671004 Mon Sep 17 00:00:00 2001 From: rinpatch Date: Fri, 18 Oct 2019 14:11:30 +0300 Subject: Fix a migration wiping user info of users that don't have any mutes And introduce safe_jsonb_set --- .../20190711042021_create_safe_jsonb_set.exs | 22 ++++++++++++++++++++++ ...711042024_copy_muted_to_muted_notifications.exs | 2 +- 2 files changed, 23 insertions(+), 1 deletion(-) create mode 100644 priv/repo/migrations/20190711042021_create_safe_jsonb_set.exs (limited to 'priv') diff --git a/priv/repo/migrations/20190711042021_create_safe_jsonb_set.exs b/priv/repo/migrations/20190711042021_create_safe_jsonb_set.exs new file mode 100644 index 000000000..2f336a5e8 --- /dev/null +++ b/priv/repo/migrations/20190711042021_create_safe_jsonb_set.exs @@ -0,0 +1,22 @@ +defmodule Pleroma.Repo.Migrations.CreateSafeJsonbSet do + use Ecto.Migration + alias Pleroma.User + + def change do + execute(""" + create or replace function safe_jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean default true) returns jsonb as $$ + declare + result jsonb; + begin + result := jsonb_set(target, path, coalesce(new_value, 'null'::jsonb), create_missing); + if result is NULL then + raise 'jsonb_set tried to wipe the object, please report this incindent to Pleroma bug tracker. https://git.pleroma.social/pleroma/pleroma/issues/new'; + return target; + else + return result; + end if; + end; + $$ language plpgsql; + """) + end +end diff --git a/priv/repo/migrations/20190711042024_copy_muted_to_muted_notifications.exs b/priv/repo/migrations/20190711042024_copy_muted_to_muted_notifications.exs index bc4e828cc..a5eec848b 100644 --- a/priv/repo/migrations/20190711042024_copy_muted_to_muted_notifications.exs +++ b/priv/repo/migrations/20190711042024_copy_muted_to_muted_notifications.exs @@ -4,7 +4,7 @@ defmodule Pleroma.Repo.Migrations.CopyMutedToMutedNotifications do def change do execute( - "update users set info = jsonb_set(info, '{muted_notifications}', info->'mutes', true) where local = true" + "update users set info = safe_jsonb_set(info, '{muted_notifications}', info->'mutes', true) where local = true" ) end end -- cgit v1.2.3 From c9280b97306b894a38377acf43c62d7808df01d8 Mon Sep 17 00:00:00 2001 From: Ariadne Conill Date: Sat, 19 Oct 2019 17:46:24 +0000 Subject: rework to use properties instead of compound typing, per SocialCG --- priv/static/schemas/litepub-0.1.jsonld | 1 + 1 file changed, 1 insertion(+) (limited to 'priv') diff --git a/priv/static/schemas/litepub-0.1.jsonld b/priv/static/schemas/litepub-0.1.jsonld index 1cfcb7ec7..c8e69cab5 100644 --- a/priv/static/schemas/litepub-0.1.jsonld +++ b/priv/static/schemas/litepub-0.1.jsonld @@ -19,6 +19,7 @@ "value": "schema:value", "sensitive": "as:sensitive", "litepub": "http://litepub.social/ns#", + "invisible": "litepub:invisible", "directMessage": "litepub:directMessage", "listMessage": { "@id": "litepub:listMessage", -- cgit v1.2.3 From 34754938ba27a71212899f56f8e089a82facd111 Mon Sep 17 00:00:00 2001 From: Ariadne Conill Date: Sun, 20 Oct 2019 10:39:03 +0000 Subject: add missing migration to drop websub table --- priv/repo/migrations/20191017225002_drop_websub_tables.exs | 8 ++++++++ 1 file changed, 8 insertions(+) create mode 100644 priv/repo/migrations/20191017225002_drop_websub_tables.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191017225002_drop_websub_tables.exs b/priv/repo/migrations/20191017225002_drop_websub_tables.exs new file mode 100644 index 000000000..9e483b2a1 --- /dev/null +++ b/priv/repo/migrations/20191017225002_drop_websub_tables.exs @@ -0,0 +1,8 @@ +defmodule Pleroma.Repo.Migrations.DropWebsubTables do + use Ecto.Migration + + def change do + drop_if_exists(table(:websub_client_subscriptions)) + drop_if_exists(table(:websub_server_subscriptions)) + end +end -- cgit v1.2.3 From e8843974cb9b8adfe8798bb8f7ff17b7a92f5ab8 Mon Sep 17 00:00:00 2001 From: Ivan Tashkinov Date: Sun, 20 Oct 2019 13:42:42 +0300 Subject: [#1304] Moved remaining fields from User.Info to User. Misc. fixes / improvements. --- ...91009154608_copy_users_info_fields_to_users.exs | 139 ++++++++++++++++++++- 1 file changed, 135 insertions(+), 4 deletions(-) (limited to 'priv') diff --git a/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs b/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs index b26b122eb..6b096a962 100644 --- a/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs +++ b/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs @@ -1,6 +1,98 @@ defmodule Pleroma.Repo.Migrations.CopyUsersInfoaddsToUsers do use Ecto.Migration + @info_fields [ + :banner, + :background, + :source_data, + :note_count, + :follower_count, + :following_count, + :locked, + :confirmation_pending, + :password_reset_pending, + :confirmation_token, + :default_scope, + :blocks, + :domain_blocks, + :mutes, + :muted_reblogs, + :muted_notifications, + :subscribers, + :deactivated, + :no_rich_text, + :ap_enabled, + :is_moderator, + :is_admin, + :show_role, + :settings, + :magic_key, + :uri, + :topic, + :hub, + :salmon, + :hide_followers_count, + :hide_follows_count, + :hide_followers, + :hide_follows, + :hide_favorites, + :unread_conversation_count, + :pinned_activities, + :email_notifications, + :mascot, + :emoji, + :pleroma_settings_store, + :fields, + :raw_fields, + :discoverable, + :skip_thread_containment, + :notification_settings + ] + + @jsonb_fields [ + :banner, + :background, + :source_data, + :settings, + :email_notifications, + :mascot, + :pleroma_settings_store, + :notification_settings + ] + + @array_jsonb_fields [:emoji, :fields, :raw_fields] + + @int_fields [:note_count, :follower_count, :following_count, :unread_conversation_count] + + @boolean_fields [ + :locked, + :confirmation_pending, + :password_reset_pending, + :deactivated, + :no_rich_text, + :ap_enabled, + :is_moderator, + :is_admin, + :show_role, + :hide_followers_count, + :hide_follows_count, + :hide_followers, + :hide_follows, + :hide_favorites, + :discoverable, + :skip_thread_containment + ] + + @array_text_fields [ + :blocks, + :domain_blocks, + :mutes, + :muted_reblogs, + :muted_notifications, + :subscribers, + :pinned_activities + ] + def change do alter table(:users) do add(:banner, :map, default: %{}) @@ -8,11 +100,10 @@ defmodule Pleroma.Repo.Migrations.CopyUsersInfoaddsToUsers do add(:source_data, :map, default: %{}) add(:note_count, :integer, default: 0) add(:follower_count, :integer, default: 0) - # Should be filled in only for remote users add(:following_count, :integer, default: nil) - add(:locked, :boolean, default: false) - add(:confirmation_pending, :boolean, default: false) - add(:password_reset_pending, :boolean, default: false) + add(:locked, :boolean, default: false, null: false) + add(:confirmation_pending, :boolean, default: false, null: false) + add(:password_reset_pending, :boolean, default: false, null: false) add(:confirmation_token, :text, default: nil) add(:default_scope, :string, default: "public") add(:blocks, {:array, :text}, default: []) @@ -50,5 +141,45 @@ defmodule Pleroma.Repo.Migrations.CopyUsersInfoaddsToUsers do add(:notification_settings, :map, default: %{}) add(:skip_thread_containment, :boolean, default: false, null: false) end + + if direction == :up do + for f <- @info_fields do + set_field = "update users set #{f} =" + + cond do + f in @jsonb_fields -> + execute("#{set_field} info->'#{f}'") + + f in @array_jsonb_fields -> + execute("#{set_field} ARRAY(SELECT jsonb_array_elements(info->'#{f}'))") + + f in @int_fields -> + execute("#{set_field} (info->>'#{f}')::int") + + f in @boolean_fields -> + execute("#{set_field} coalesce((info->>'#{f}')::boolean, false)") + + f in @array_text_fields -> + execute("#{set_field} ARRAY(SELECT jsonb_array_elements_text(info->'#{f}'))") + + true -> + execute("#{set_field} info->>'#{f}'") + end + end + + for index_name <- [ + :users_deactivated_index, + :users_is_moderator_index, + :users_is_admin_index, + :users_subscribers_index + ] do + drop_if_exists(index(:users, [], name: index_name)) + end + end + + create_if_not_exists(index(:users, [:deactivated])) + create_if_not_exists(index(:users, [:is_moderator])) + create_if_not_exists(index(:users, [:is_admin])) + create_if_not_exists(index(:users, [:subscribers])) end end -- cgit v1.2.3 From ee04fbc35ad56784614c8cdd3f863da284a5725e Mon Sep 17 00:00:00 2001 From: Ivan Tashkinov Date: Sun, 20 Oct 2019 22:29:56 +0300 Subject: [#1304]. Post-merge fixes. Merge branch 'develop' of git.pleroma.social:pleroma/pleroma into 1304-user-info-deprecation # Conflicts: # CHANGELOG.md # lib/pleroma/notification.ex # lib/pleroma/user.ex # lib/pleroma/user/info.ex # lib/pleroma/web/admin_api/admin_api_controller.ex # lib/pleroma/web/ostatus/handlers/follow_handler.ex # lib/pleroma/web/ostatus/ostatus.ex # lib/pleroma/web/salmon/salmon.ex # lib/pleroma/web/websub/websub.ex # test/web/admin_api/admin_api_controller_test.exs # test/web/federator_test.exs # test/web/mastodon_api/controllers/conversation_controller_test.exs # test/web/ostatus/ostatus_controller_test.exs # test/web/ostatus/ostatus_test.exs # test/web/salmon/salmon_test.exs # test/web/websub/websub_test.exs --- .../migrations/20191009154608_copy_users_info_fields_to_users.exs | 6 ------ 1 file changed, 6 deletions(-) (limited to 'priv') diff --git a/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs b/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs index 6b096a962..d9df5bb51 100644 --- a/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs +++ b/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs @@ -28,9 +28,6 @@ defmodule Pleroma.Repo.Migrations.CopyUsersInfoaddsToUsers do :settings, :magic_key, :uri, - :topic, - :hub, - :salmon, :hide_followers_count, :hide_follows_count, :hide_followers, @@ -121,9 +118,6 @@ defmodule Pleroma.Repo.Migrations.CopyUsersInfoaddsToUsers do add(:settings, :map, default: nil) add(:magic_key, :text, default: nil) add(:uri, :text, default: nil) - add(:topic, :text, default: nil) - add(:hub, :text, default: nil) - add(:salmon, :text, default: nil) add(:hide_followers_count, :boolean, default: false, null: false) add(:hide_follows_count, :boolean, default: false, null: false) add(:hide_followers, :boolean, default: false, null: false) -- cgit v1.2.3 From 75da202ab74489af15e086f06810e22211d52d33 Mon Sep 17 00:00:00 2001 From: Ivan Tashkinov Date: Mon, 21 Oct 2019 10:20:28 +0300 Subject: [#1304] Typo fix. --- .../migrations/20191009154608_copy_users_info_fields_to_users.exs | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'priv') diff --git a/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs b/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs index d9df5bb51..72c852d2b 100644 --- a/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs +++ b/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs @@ -1,4 +1,4 @@ -defmodule Pleroma.Repo.Migrations.CopyUsersInfoaddsToUsers do +defmodule Pleroma.Repo.Migrations.CopyUsersInfoFieldsToUsers do use Ecto.Migration @info_fields [ @@ -136,7 +136,7 @@ defmodule Pleroma.Repo.Migrations.CopyUsersInfoaddsToUsers do add(:skip_thread_containment, :boolean, default: false, null: false) end - if direction == :up do + if direction() == :up do for f <- @info_fields do set_field = "update users set #{f} =" -- cgit v1.2.3 From e37d4b2ddf7928437618d3eb7b5da678e39927cf Mon Sep 17 00:00:00 2001 From: Egor Kislitsyn Date: Mon, 21 Oct 2019 14:52:52 +0700 Subject: Revert "Skip deactivated users in followers import" This reverts commit 8ad015ef64e0d2a4cd9f2979ff08d28be3a635e5. --- priv/repo/migrations/20191008132217_migrate_following_relationships.exs | 1 - 1 file changed, 1 deletion(-) (limited to 'priv') diff --git a/priv/repo/migrations/20191008132217_migrate_following_relationships.exs b/priv/repo/migrations/20191008132217_migrate_following_relationships.exs index c9bc890aa..7f996f5a5 100644 --- a/priv/repo/migrations/20191008132217_migrate_following_relationships.exs +++ b/priv/repo/migrations/20191008132217_migrate_following_relationships.exs @@ -55,7 +55,6 @@ defmodule Pleroma.Repo.Migrations.MigrateFollowingRelationships do WHERE activities.data ->> 'type' = 'Follow' AND activities.data ->> 'state' IN ('accept', 'pending', 'reject') - AND NOT (followers.info ? 'deactivated' AND followers.info -> 'deactivated' @> 'true') ORDER BY activities.updated_at DESC ON CONFLICT DO NOTHING """ -- cgit v1.2.3 From f726d953d59b8c4c2099a3c583e7226b99324bb9 Mon Sep 17 00:00:00 2001 From: Egor Kislitsyn Date: Mon, 21 Oct 2019 14:56:39 +0700 Subject: Fix typos --- .../20191008132217_migrate_following_relationships.exs | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'priv') diff --git a/priv/repo/migrations/20191008132217_migrate_following_relationships.exs b/priv/repo/migrations/20191008132217_migrate_following_relationships.exs index 7f996f5a5..9d5c2648f 100644 --- a/priv/repo/migrations/20191008132217_migrate_following_relationships.exs +++ b/priv/repo/migrations/20191008132217_migrate_following_relationships.exs @@ -69,19 +69,19 @@ defmodule Pleroma.Repo.Migrations.MigrateFollowingRelationships do updated_at = now() FROM ( SELECT - follwer.id AS follower_id, - CASE follwer.local + follower.id AS follower_id, + CASE follower.local WHEN TRUE THEN - array_prepend(follwer.follower_address, array_agg(following.follower_address)) + array_prepend(follower.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 + JOIN users AS follower ON follower.id = following_relationships.follower_id + JOIN users AS following ON following.id = following_relationships.following_id GROUP BY - follwer.id) AS following_query + follower.id) AS following_query WHERE following_query.follower_id = users.id """ -- cgit v1.2.3 From 7c7f90bc4f361b8bf4b49790640a4aa490ee619f Mon Sep 17 00:00:00 2001 From: Ivan Tashkinov Date: Mon, 21 Oct 2019 11:58:22 +0300 Subject: [#1304] Merged `develop`, handled User.Info.invisible. --- .../migrations/20191009154608_copy_users_info_fields_to_users.exs | 3 +++ priv/repo/migrations/20191017225002_drop_websub_tables.exs | 4 +++- 2 files changed, 6 insertions(+), 1 deletion(-) (limited to 'priv') diff --git a/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs b/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs index 72c852d2b..87b1e2c8c 100644 --- a/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs +++ b/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs @@ -42,6 +42,7 @@ defmodule Pleroma.Repo.Migrations.CopyUsersInfoFieldsToUsers do :fields, :raw_fields, :discoverable, + :invisible, :skip_thread_containment, :notification_settings ] @@ -77,6 +78,7 @@ defmodule Pleroma.Repo.Migrations.CopyUsersInfoFieldsToUsers do :hide_follows, :hide_favorites, :discoverable, + :invisible, :skip_thread_containment ] @@ -132,6 +134,7 @@ defmodule Pleroma.Repo.Migrations.CopyUsersInfoFieldsToUsers do add(:fields, {:array, :map}, default: nil) add(:raw_fields, {:array, :map}, default: []) add(:discoverable, :boolean, default: false, null: false) + add(:invisible, :boolean, default: false, null: false) add(:notification_settings, :map, default: %{}) add(:skip_thread_containment, :boolean, default: false, null: false) end diff --git a/priv/repo/migrations/20191017225002_drop_websub_tables.exs b/priv/repo/migrations/20191017225002_drop_websub_tables.exs index 9e483b2a1..4cf67a59c 100644 --- a/priv/repo/migrations/20191017225002_drop_websub_tables.exs +++ b/priv/repo/migrations/20191017225002_drop_websub_tables.exs @@ -1,8 +1,10 @@ defmodule Pleroma.Repo.Migrations.DropWebsubTables do use Ecto.Migration - def change do + def up do drop_if_exists(table(:websub_client_subscriptions)) drop_if_exists(table(:websub_server_subscriptions)) end + + def down, do: :noop end -- cgit v1.2.3 From 11cd9944258bfc2123b821b21321e12097d0f19b Mon Sep 17 00:00:00 2001 From: Ivan Tashkinov Date: Wed, 23 Oct 2019 17:15:48 +0300 Subject: [#1304] Fixed null::jsonb handling in User.Info migration. --- .../20191009154608_copy_users_info_fields_to_users.exs | 17 +++++++++++------ 1 file changed, 11 insertions(+), 6 deletions(-) (limited to 'priv') diff --git a/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs b/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs index 87b1e2c8c..9dd27511c 100644 --- a/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs +++ b/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs @@ -1,6 +1,8 @@ defmodule Pleroma.Repo.Migrations.CopyUsersInfoFieldsToUsers do use Ecto.Migration + @jsonb_array_default "'[]'::jsonb" + @info_fields [ :banner, :background, @@ -129,10 +131,10 @@ defmodule Pleroma.Repo.Migrations.CopyUsersInfoFieldsToUsers do add(:pinned_activities, {:array, :text}, default: []) add(:email_notifications, :map, default: %{"digest" => false}) add(:mascot, :map, default: nil) - add(:emoji, {:array, :map}, default: []) + add(:emoji, :map, default: fragment(@jsonb_array_default)) add(:pleroma_settings_store, :map, default: %{}) - add(:fields, {:array, :map}, default: nil) - add(:raw_fields, {:array, :map}, default: []) + add(:fields, :map, default: fragment(@jsonb_array_default)) + add(:raw_fields, :map, default: fragment(@jsonb_array_default)) add(:discoverable, :boolean, default: false, null: false) add(:invisible, :boolean, default: false, null: false) add(:notification_settings, :map, default: %{}) @@ -143,12 +145,15 @@ defmodule Pleroma.Repo.Migrations.CopyUsersInfoFieldsToUsers do for f <- @info_fields do set_field = "update users set #{f} =" + # Coercion of null::jsonb to NULL + jsonb = "case when info->>'#{f}' IS NULL then null else info->'#{f}' end" + cond do f in @jsonb_fields -> - execute("#{set_field} info->'#{f}'") + execute("#{set_field} #{jsonb}") f in @array_jsonb_fields -> - execute("#{set_field} ARRAY(SELECT jsonb_array_elements(info->'#{f}'))") + execute("#{set_field} coalesce(#{jsonb}, #{@jsonb_array_default})") f in @int_fields -> execute("#{set_field} (info->>'#{f}')::int") @@ -157,7 +162,7 @@ defmodule Pleroma.Repo.Migrations.CopyUsersInfoFieldsToUsers do execute("#{set_field} coalesce((info->>'#{f}')::boolean, false)") f in @array_text_fields -> - execute("#{set_field} ARRAY(SELECT jsonb_array_elements_text(info->'#{f}'))") + execute("#{set_field} ARRAY(SELECT jsonb_array_elements_text(#{jsonb}))") true -> execute("#{set_field} info->>'#{f}'") -- cgit v1.2.3 From 12ab7b3280c8cf2469479381e0d363f7638002d8 Mon Sep 17 00:00:00 2001 From: rinpatch Date: Sat, 26 Oct 2019 03:45:24 +0300 Subject: User info migration improvements - Move column additions into a separate migration, so postgres doesn't need an exclusive lock on the table for the main part - Fill in columns by using one big update statement instead of a bunch of small ones because it's much faster (the migration took 140s on patch.cx database) --- .../20191009154606_add_user_info_columns.exs | 53 +++++++++++++ ...91009154608_copy_users_info_fields_to_users.exs | 86 ++++++---------------- 2 files changed, 75 insertions(+), 64 deletions(-) create mode 100644 priv/repo/migrations/20191009154606_add_user_info_columns.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191009154606_add_user_info_columns.exs b/priv/repo/migrations/20191009154606_add_user_info_columns.exs new file mode 100644 index 000000000..22a5a377f --- /dev/null +++ b/priv/repo/migrations/20191009154606_add_user_info_columns.exs @@ -0,0 +1,53 @@ +defmodule Pleroma.Repo.Migrations.AddUsersInfoColumns do + use Ecto.Migration + + @jsonb_array_default "'[]'::jsonb" + + def change do + alter table(:users) do + add_if_not_exists(:banner, :map, default: %{}) + add_if_not_exists(:background, :map, default: %{}) + add_if_not_exists(:source_data, :map, default: %{}) + add_if_not_exists(:note_count, :integer, default: 0) + add_if_not_exists(:follower_count, :integer, default: 0) + add_if_not_exists(:following_count, :integer, default: nil) + add_if_not_exists(:locked, :boolean, default: false, null: false) + add_if_not_exists(:confirmation_pending, :boolean, default: false, null: false) + add_if_not_exists(:password_reset_pending, :boolean, default: false, null: false) + add_if_not_exists(:confirmation_token, :text, default: nil) + add_if_not_exists(:default_scope, :string, default: "public") + add_if_not_exists(:blocks, {:array, :text}, default: []) + add_if_not_exists(:domain_blocks, {:array, :text}, default: []) + add_if_not_exists(:mutes, {:array, :text}, default: []) + add_if_not_exists(:muted_reblogs, {:array, :text}, default: []) + add_if_not_exists(:muted_notifications, {:array, :text}, default: []) + add_if_not_exists(:subscribers, {:array, :text}, default: []) + add_if_not_exists(:deactivated, :boolean, default: false, null: false) + add_if_not_exists(:no_rich_text, :boolean, default: false, null: false) + add_if_not_exists(:ap_enabled, :boolean, default: false, null: false) + add_if_not_exists(:is_moderator, :boolean, default: false, null: false) + add_if_not_exists(:is_admin, :boolean, default: false, null: false) + add_if_not_exists(:show_role, :boolean, default: true, null: false) + add_if_not_exists(:settings, :map, default: nil) + add_if_not_exists(:magic_key, :text, default: nil) + add_if_not_exists(:uri, :text, default: nil) + add_if_not_exists(:hide_followers_count, :boolean, default: false, null: false) + add_if_not_exists(:hide_follows_count, :boolean, default: false, null: false) + add_if_not_exists(:hide_followers, :boolean, default: false, null: false) + add_if_not_exists(:hide_follows, :boolean, default: false, null: false) + add_if_not_exists(:hide_favorites, :boolean, default: true, null: false) + add_if_not_exists(:unread_conversation_count, :integer, default: 0) + add_if_not_exists(:pinned_activities, {:array, :text}, default: []) + add_if_not_exists(:email_notifications, :map, default: %{"digest" => false}) + add_if_not_exists(:mascot, :map, default: nil) + add_if_not_exists(:emoji, :map, default: fragment(@jsonb_array_default)) + add_if_not_exists(:pleroma_settings_store, :map, default: %{}) + add_if_not_exists(:fields, :map, default: fragment(@jsonb_array_default)) + add_if_not_exists(:raw_fields, :map, default: fragment(@jsonb_array_default)) + add_if_not_exists(:discoverable, :boolean, default: false, null: false) + add_if_not_exists(:invisible, :boolean, default: false, null: false) + add_if_not_exists(:notification_settings, :map, default: %{}) + add_if_not_exists(:skip_thread_containment, :boolean, default: false, null: false) + end + end +end diff --git a/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs b/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs index 9dd27511c..cc5ae6920 100644 --- a/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs +++ b/priv/repo/migrations/20191009154608_copy_users_info_fields_to_users.exs @@ -95,79 +95,37 @@ defmodule Pleroma.Repo.Migrations.CopyUsersInfoFieldsToUsers do ] def change do - alter table(:users) do - add(:banner, :map, default: %{}) - add(:background, :map, default: %{}) - add(:source_data, :map, default: %{}) - add(:note_count, :integer, default: 0) - add(:follower_count, :integer, default: 0) - add(:following_count, :integer, default: nil) - add(:locked, :boolean, default: false, null: false) - add(:confirmation_pending, :boolean, default: false, null: false) - add(:password_reset_pending, :boolean, default: false, null: false) - add(:confirmation_token, :text, default: nil) - add(:default_scope, :string, default: "public") - add(:blocks, {:array, :text}, default: []) - add(:domain_blocks, {:array, :text}, default: []) - add(:mutes, {:array, :text}, default: []) - add(:muted_reblogs, {:array, :text}, default: []) - add(:muted_notifications, {:array, :text}, default: []) - add(:subscribers, {:array, :text}, default: []) - add(:deactivated, :boolean, default: false, null: false) - add(:no_rich_text, :boolean, default: false, null: false) - add(:ap_enabled, :boolean, default: false, null: false) - add(:is_moderator, :boolean, default: false, null: false) - add(:is_admin, :boolean, default: false, null: false) - add(:show_role, :boolean, default: true, null: false) - add(:settings, :map, default: nil) - add(:magic_key, :text, default: nil) - add(:uri, :text, default: nil) - add(:hide_followers_count, :boolean, default: false, null: false) - add(:hide_follows_count, :boolean, default: false, null: false) - add(:hide_followers, :boolean, default: false, null: false) - add(:hide_follows, :boolean, default: false, null: false) - add(:hide_favorites, :boolean, default: true, null: false) - add(:unread_conversation_count, :integer, default: 0) - add(:pinned_activities, {:array, :text}, default: []) - add(:email_notifications, :map, default: %{"digest" => false}) - add(:mascot, :map, default: nil) - add(:emoji, :map, default: fragment(@jsonb_array_default)) - add(:pleroma_settings_store, :map, default: %{}) - add(:fields, :map, default: fragment(@jsonb_array_default)) - add(:raw_fields, :map, default: fragment(@jsonb_array_default)) - add(:discoverable, :boolean, default: false, null: false) - add(:invisible, :boolean, default: false, null: false) - add(:notification_settings, :map, default: %{}) - add(:skip_thread_containment, :boolean, default: false, null: false) - end - if direction() == :up do - for f <- @info_fields do - set_field = "update users set #{f} =" + sets = + for f <- @info_fields do + set_field = "#{f} =" - # Coercion of null::jsonb to NULL - jsonb = "case when info->>'#{f}' IS NULL then null else info->'#{f}' end" + # Coercion of null::jsonb to NULL + jsonb = "case when info->>'#{f}' IS NULL then null else info->'#{f}' end" - cond do - f in @jsonb_fields -> - execute("#{set_field} #{jsonb}") + cond do + f in @jsonb_fields -> + "#{set_field} #{jsonb}" - f in @array_jsonb_fields -> - execute("#{set_field} coalesce(#{jsonb}, #{@jsonb_array_default})") + f in @array_jsonb_fields -> + "#{set_field} coalesce(#{jsonb}, #{@jsonb_array_default})" - f in @int_fields -> - execute("#{set_field} (info->>'#{f}')::int") + f in @int_fields -> + "#{set_field} (info->>'#{f}')::int" - f in @boolean_fields -> - execute("#{set_field} coalesce((info->>'#{f}')::boolean, false)") + f in @boolean_fields -> + "#{set_field} coalesce((info->>'#{f}')::boolean, false)" - f in @array_text_fields -> - execute("#{set_field} ARRAY(SELECT jsonb_array_elements_text(#{jsonb}))") + f in @array_text_fields -> + "#{set_field} ARRAY(SELECT jsonb_array_elements_text(#{jsonb}))" - true -> - execute("#{set_field} info->>'#{f}'") + true -> + "#{set_field} info->>'#{f}'" + end end - end + |> Enum.join(", ") + + execute("update users set " <> sets) for index_name <- [ :users_deactivated_index, -- cgit v1.2.3 From 6e7fd364a4de922aab8f3123f0a378e79a7029f1 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:28:18 +0300 Subject: Add migration --- .../20191026190317_set_not_null_for_activities.exs | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 priv/repo/migrations/20191026190317_set_not_null_for_activities.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026190317_set_not_null_for_activities.exs b/priv/repo/migrations/20191026190317_set_not_null_for_activities.exs new file mode 100644 index 000000000..9b66f3cff --- /dev/null +++ b/priv/repo/migrations/20191026190317_set_not_null_for_activities.exs @@ -0,0 +1,17 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForActivities do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE activities + ALTER COLUMN data SET NOT NULL, + ALTER COLUMN local SET NOT NULL") + end + + def down do + execute("ALTER TABLE activities + ALTER COLUMN data DROP NOT NULL, + ALTER COLUMN local DROP NOT NULL") + end +end -- cgit v1.2.3 From cd0218c2053b3f7e04416ea0eb12759d78cfcbf5 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:31:25 +0300 Subject: Add migration --- ...191026190415_set_not_null_for_activity_expirations.exs | 15 +++++++++++++++ 1 file changed, 15 insertions(+) create mode 100644 priv/repo/migrations/20191026190415_set_not_null_for_activity_expirations.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026190415_set_not_null_for_activity_expirations.exs b/priv/repo/migrations/20191026190415_set_not_null_for_activity_expirations.exs new file mode 100644 index 000000000..e41c69e4c --- /dev/null +++ b/priv/repo/migrations/20191026190415_set_not_null_for_activity_expirations.exs @@ -0,0 +1,15 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForActivityExpirations do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE activity_expirations + ALTER COLUMN activity_id SET NOT NULL") + end + + def down do + execute("ALTER TABLE activity_expirations + ALTER COLUMN activity_id DROP NOT NULL") + end +end -- cgit v1.2.3 From 5cb03fe8010d479e32979d2c02ec8bcf1edf1a67 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:32:31 +0300 Subject: Add migration --- .../migrations/20191026190500_set_not_null_for_apps.exs | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 priv/repo/migrations/20191026190500_set_not_null_for_apps.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026190500_set_not_null_for_apps.exs b/priv/repo/migrations/20191026190500_set_not_null_for_apps.exs new file mode 100644 index 000000000..a6a44ddfe --- /dev/null +++ b/priv/repo/migrations/20191026190500_set_not_null_for_apps.exs @@ -0,0 +1,17 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForApps do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE apps + ALTER COLUMN client_name SET NOT NULL, + ALTER COLUMN redirect_uris SET NOT NULL") + end + + def down do + execute("ALTER TABLE apps + ALTER COLUMN client_name DROP NOT NULL, + ALTER COLUMN redirect_uris DROP NOT NULL") + end +end -- cgit v1.2.3 From c1ff8472fd7fb84aa90cc59df99dffb7cccaa005 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:33:18 +0300 Subject: Add migration --- .../20191026190533_set_not_null_for_bookmarks.exs | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 priv/repo/migrations/20191026190533_set_not_null_for_bookmarks.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026190533_set_not_null_for_bookmarks.exs b/priv/repo/migrations/20191026190533_set_not_null_for_bookmarks.exs new file mode 100644 index 000000000..5f3224003 --- /dev/null +++ b/priv/repo/migrations/20191026190533_set_not_null_for_bookmarks.exs @@ -0,0 +1,17 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForBookmarks do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE bookmarks + ALTER COLUMN user_id SET NOT NULL, + ALTER COLUMN activity_id SET NOT NULL") + end + + def down do + execute("ALTER TABLE bookmarks + ALTER COLUMN user_id DROP NOT NULL, + ALTER COLUMN activity_id DROP NOT NULL") + end +end -- cgit v1.2.3 From 7ac42fefe3bd90f20f887e56db4e26e0955128fa Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:36:43 +0300 Subject: Add migration --- .../20191026190622_set_not_null_for_config.exs | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 priv/repo/migrations/20191026190622_set_not_null_for_config.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026190622_set_not_null_for_config.exs b/priv/repo/migrations/20191026190622_set_not_null_for_config.exs new file mode 100644 index 000000000..204272442 --- /dev/null +++ b/priv/repo/migrations/20191026190622_set_not_null_for_config.exs @@ -0,0 +1,17 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForConfig do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE config + ALTER COLUMN key SET NOT NULL, + ALTER COLUMN value SET NOT NULL") + end + + def down do + execute("ALTER TABLE config + ALTER COLUMN key DROP NOT NULL, + ALTER COLUMN value DROP NOT NULL") + end +end -- cgit v1.2.3 From d12555a69ecd002e5c2d2be36e9ec1fa6badf001 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:37:30 +0300 Subject: Add migration --- ...l_for_conversation_participation_recipient_ships.exs | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 priv/repo/migrations/20191026190712_set_not_null_for_conversation_participation_recipient_ships.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026190712_set_not_null_for_conversation_participation_recipient_ships.exs b/priv/repo/migrations/20191026190712_set_not_null_for_conversation_participation_recipient_ships.exs new file mode 100644 index 000000000..a5ab1d3c9 --- /dev/null +++ b/priv/repo/migrations/20191026190712_set_not_null_for_conversation_participation_recipient_ships.exs @@ -0,0 +1,17 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForConversationParticipationRecipientShips do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE conversation_participation_recipient_ships + ALTER COLUMN user_id SET NOT NULL, + ALTER COLUMN participation_id SET NOT NULL") + end + + def down do + execute("ALTER TABLE conversation_participation_recipient_ships + ALTER COLUMN user_id DROP NOT NULL, + ALTER COLUMN participation_id DROP NOT NULL") + end +end -- cgit v1.2.3 From 0223dc4e02cc05eff3f05e4438bbccf29015f10a Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:38:07 +0300 Subject: Add migration --- ...9_set_not_null_for_conversation_participations.exs | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) create mode 100644 priv/repo/migrations/20191026190759_set_not_null_for_conversation_participations.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026190759_set_not_null_for_conversation_participations.exs b/priv/repo/migrations/20191026190759_set_not_null_for_conversation_participations.exs new file mode 100644 index 000000000..cabb1f29f --- /dev/null +++ b/priv/repo/migrations/20191026190759_set_not_null_for_conversation_participations.exs @@ -0,0 +1,19 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForConversationParticipations do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE conversation_participations + ALTER COLUMN user_id SET NOT NULL, + ALTER COLUMN conversation_id SET NOT NULL, + ALTER COLUMN read SET NOT NULL") + end + + def down do + execute("ALTER TABLE conversation_participations + ALTER COLUMN user_id DROP NOT NULL, + ALTER COLUMN conversation_id DROP NOT NULL, + ALTER COLUMN read DROP NOT NULL") + end +end -- cgit v1.2.3 From 382e83fab56ade9f893b117fcac0d66b402ac86f Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:39:24 +0300 Subject: Add migration --- .../20191026190841_set_not_null_for_filters.exs | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) create mode 100644 priv/repo/migrations/20191026190841_set_not_null_for_filters.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026190841_set_not_null_for_filters.exs b/priv/repo/migrations/20191026190841_set_not_null_for_filters.exs new file mode 100644 index 000000000..52d7e687a --- /dev/null +++ b/priv/repo/migrations/20191026190841_set_not_null_for_filters.exs @@ -0,0 +1,19 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForFilters do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE filters + ALTER COLUMN user_id SET NOT NULL, + ALTER COLUMN filter_id SET NOT NULL, + ALTER COLUMN whole_word SET NOT NULL") + end + + def down do + execute("ALTER TABLE filters + ALTER COLUMN user_id DROP NOT NULL, + ALTER COLUMN filter_id DROP NOT NULL, + ALTER COLUMN whole_word DROP NOT NULL") + end +end -- cgit v1.2.3 From b33aacc4fbbefc16fb21df56f9693c466d929558 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:40:51 +0300 Subject: Add migration --- .../20191026191023_set_not_null_for_instances.exs | 15 +++++++++++++++ 1 file changed, 15 insertions(+) create mode 100644 priv/repo/migrations/20191026191023_set_not_null_for_instances.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191023_set_not_null_for_instances.exs b/priv/repo/migrations/20191026191023_set_not_null_for_instances.exs new file mode 100644 index 000000000..4c2560da5 --- /dev/null +++ b/priv/repo/migrations/20191026191023_set_not_null_for_instances.exs @@ -0,0 +1,15 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForInstances do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE instances + ALTER COLUMN host SET NOT NULL") + end + + def down do + execute("ALTER TABLE instances + ALTER COLUMN host DROP NOT NULL") + end +end -- cgit v1.2.3 From b85bee32da237d2e877adc0218c02a663f2d48ae Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:42:24 +0300 Subject: Add migration --- .../migrations/20191026191100_set_not_null_for_lists.exs | 15 +++++++++++++++ 1 file changed, 15 insertions(+) create mode 100644 priv/repo/migrations/20191026191100_set_not_null_for_lists.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191100_set_not_null_for_lists.exs b/priv/repo/migrations/20191026191100_set_not_null_for_lists.exs new file mode 100644 index 000000000..40b8b136a --- /dev/null +++ b/priv/repo/migrations/20191026191100_set_not_null_for_lists.exs @@ -0,0 +1,15 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForLists do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE lists + ALTER COLUMN user_id SET NOT NULL") + end + + def down do + execute("ALTER TABLE lists + ALTER COLUMN user_id DROP NOT NULL") + end +end -- cgit v1.2.3 From 55203c198ba4481204ad898dcee97d4cdcbb0433 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:43:32 +0300 Subject: Add migration --- .../20191026191134_set_not_null_for_markers.exs | 15 +++++++++++++++ 1 file changed, 15 insertions(+) create mode 100644 priv/repo/migrations/20191026191134_set_not_null_for_markers.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191134_set_not_null_for_markers.exs b/priv/repo/migrations/20191026191134_set_not_null_for_markers.exs new file mode 100644 index 000000000..7d7b73e7b --- /dev/null +++ b/priv/repo/migrations/20191026191134_set_not_null_for_markers.exs @@ -0,0 +1,15 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForMarkers do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE markers + ALTER COLUMN user_id SET NOT NULL") + end + + def down do + execute("ALTER TABLE markers + ALTER COLUMN user_id DROP NOT NULL") + end +end -- cgit v1.2.3 From 13cc52dc601f858923d15f3dcf694b7667634bd7 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:44:42 +0300 Subject: Add migration --- .../20191026191218_set_not_null_for_moderation_log.exs | 15 +++++++++++++++ 1 file changed, 15 insertions(+) create mode 100644 priv/repo/migrations/20191026191218_set_not_null_for_moderation_log.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191218_set_not_null_for_moderation_log.exs b/priv/repo/migrations/20191026191218_set_not_null_for_moderation_log.exs new file mode 100644 index 000000000..7238ca7f5 --- /dev/null +++ b/priv/repo/migrations/20191026191218_set_not_null_for_moderation_log.exs @@ -0,0 +1,15 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForModerationLog do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE moderation_log + ALTER COLUMN data SET NOT NULL") + end + + def down do + execute("ALTER TABLE moderation_log + ALTER COLUMN data DROP NOT NULL") + end +end -- cgit v1.2.3 From cf0fa124a22a47ccbc6cfd9f279c93ed681a8bab Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:45:24 +0300 Subject: Add migration --- .../20191026191249_set_not_null_for_notifications.exs | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 priv/repo/migrations/20191026191249_set_not_null_for_notifications.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191249_set_not_null_for_notifications.exs b/priv/repo/migrations/20191026191249_set_not_null_for_notifications.exs new file mode 100644 index 000000000..7e2976bab --- /dev/null +++ b/priv/repo/migrations/20191026191249_set_not_null_for_notifications.exs @@ -0,0 +1,17 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForNotifications do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE notifications + ALTER COLUMN user_id SET NOT NULL, + ALTER COLUMN seen SET NOT NULL") + end + + def down do + execute("ALTER TABLE notifications + ALTER COLUMN user_id DROP NOT NULL, + ALTER COLUMN seen DROP NOT NULL") + end +end -- cgit v1.2.3 From d58cca5f0a407342bdd9a6102df9ee75897dec1d Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:46:09 +0300 Subject: Add migration --- ...26191328_set_not_null_for_oauth_authorizations.exs | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) create mode 100644 priv/repo/migrations/20191026191328_set_not_null_for_oauth_authorizations.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191328_set_not_null_for_oauth_authorizations.exs b/priv/repo/migrations/20191026191328_set_not_null_for_oauth_authorizations.exs new file mode 100644 index 000000000..bc6b36e69 --- /dev/null +++ b/priv/repo/migrations/20191026191328_set_not_null_for_oauth_authorizations.exs @@ -0,0 +1,19 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForOauthAuthorizations do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE oauth_authorizations + ALTER COLUMN app_id SET NOT NULL, + ALTER COLUMN token SET NOT NULL, + ALTER COLUMN used SET NOT NULL") + end + + def down do + execute("ALTER TABLE oauth_authorizations + ALTER COLUMN app_id DROP NOT NULL, + ALTER COLUMN token DROP NOT NULL, + ALTER COLUMN used DROP NOT NULL") + end +end -- cgit v1.2.3 From 5fece5f8bc38d2330c7855c97b44db8a5e2935b1 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:49:25 +0300 Subject: Put correct migration --- ...26191328_set_not_null_for_oauth_authorizations.exs | 19 ------------------- .../20191026191401_set_not_null_for_oauth_tokens.exs | 15 +++++++++++++++ 2 files changed, 15 insertions(+), 19 deletions(-) delete mode 100644 priv/repo/migrations/20191026191328_set_not_null_for_oauth_authorizations.exs create mode 100644 priv/repo/migrations/20191026191401_set_not_null_for_oauth_tokens.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191328_set_not_null_for_oauth_authorizations.exs b/priv/repo/migrations/20191026191328_set_not_null_for_oauth_authorizations.exs deleted file mode 100644 index bc6b36e69..000000000 --- a/priv/repo/migrations/20191026191328_set_not_null_for_oauth_authorizations.exs +++ /dev/null @@ -1,19 +0,0 @@ -defmodule Pleroma.Repo.Migrations.SetNotNullForOauthAuthorizations do - use Ecto.Migration - - # modify/3 function will require index recreation, so using execute/1 instead - - def up do - execute("ALTER TABLE oauth_authorizations - ALTER COLUMN app_id SET NOT NULL, - ALTER COLUMN token SET NOT NULL, - ALTER COLUMN used SET NOT NULL") - end - - def down do - execute("ALTER TABLE oauth_authorizations - ALTER COLUMN app_id DROP NOT NULL, - ALTER COLUMN token DROP NOT NULL, - ALTER COLUMN used DROP NOT NULL") - end -end diff --git a/priv/repo/migrations/20191026191401_set_not_null_for_oauth_tokens.exs b/priv/repo/migrations/20191026191401_set_not_null_for_oauth_tokens.exs new file mode 100644 index 000000000..fe67db8cc --- /dev/null +++ b/priv/repo/migrations/20191026191401_set_not_null_for_oauth_tokens.exs @@ -0,0 +1,15 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForOauthTokens do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE oauth_tokens + ALTER COLUMN app_id SET NOT NULL") + end + + def down do + execute("ALTER TABLE oauth_tokens + ALTER COLUMN app_id DROP NOT NULL") + end +end -- cgit v1.2.3 From c0b0fb19c867ef0ae7f511a572eda54a0683fbb3 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:50:52 +0300 Subject: Add migration --- ...26191328_set_not_null_for_oauth_authorizations.exs | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) create mode 100644 priv/repo/migrations/20191026191328_set_not_null_for_oauth_authorizations.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191328_set_not_null_for_oauth_authorizations.exs b/priv/repo/migrations/20191026191328_set_not_null_for_oauth_authorizations.exs new file mode 100644 index 000000000..bc6b36e69 --- /dev/null +++ b/priv/repo/migrations/20191026191328_set_not_null_for_oauth_authorizations.exs @@ -0,0 +1,19 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForOauthAuthorizations do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE oauth_authorizations + ALTER COLUMN app_id SET NOT NULL, + ALTER COLUMN token SET NOT NULL, + ALTER COLUMN used SET NOT NULL") + end + + def down do + execute("ALTER TABLE oauth_authorizations + ALTER COLUMN app_id DROP NOT NULL, + ALTER COLUMN token DROP NOT NULL, + ALTER COLUMN used DROP NOT NULL") + end +end -- cgit v1.2.3 From 776c31267faeb58536ee93c24d806d9b4416baf7 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:52:49 +0300 Subject: Add migration --- .../20191026191442_set_not_null_for_objects.exs | 15 +++++++++++++++ 1 file changed, 15 insertions(+) create mode 100644 priv/repo/migrations/20191026191442_set_not_null_for_objects.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191442_set_not_null_for_objects.exs b/priv/repo/migrations/20191026191442_set_not_null_for_objects.exs new file mode 100644 index 000000000..59e89d6da --- /dev/null +++ b/priv/repo/migrations/20191026191442_set_not_null_for_objects.exs @@ -0,0 +1,15 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForObjects do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE objects + ALTER COLUMN data SET NOT NULL") + end + + def down do + execute("ALTER TABLE objects + ALTER COLUMN data DROP NOT NULL") + end +end -- cgit v1.2.3 From a4cf6643851b0241e6d57b6140025a49366da1b0 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:53:41 +0300 Subject: Add migration --- ...6191524_set_not_null_for_password_reset_tokens.exs | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) create mode 100644 priv/repo/migrations/20191026191524_set_not_null_for_password_reset_tokens.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191524_set_not_null_for_password_reset_tokens.exs b/priv/repo/migrations/20191026191524_set_not_null_for_password_reset_tokens.exs new file mode 100644 index 000000000..51efadbd3 --- /dev/null +++ b/priv/repo/migrations/20191026191524_set_not_null_for_password_reset_tokens.exs @@ -0,0 +1,19 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForPasswordResetTokens do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE password_reset_tokens + ALTER COLUMN token SET NOT NULL, + ALTER COLUMN user_id SET NOT NULL, + ALTER COLUMN used SET NOT NULL") + end + + def down do + execute("ALTER TABLE password_reset_tokens + ALTER COLUMN token DROP NOT NULL, + ALTER COLUMN user_id DROP NOT NULL, + ALTER COLUMN used DROP NOT NULL") + end +end -- cgit v1.2.3 From cf72b7649e521e03810fcb195ac1262b7d8385dd Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:54:37 +0300 Subject: Add migration --- ...6191603_set_not_null_for_push_subscriptions.exs | 25 ++++++++++++++++++++++ 1 file changed, 25 insertions(+) create mode 100644 priv/repo/migrations/20191026191603_set_not_null_for_push_subscriptions.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191603_set_not_null_for_push_subscriptions.exs b/priv/repo/migrations/20191026191603_set_not_null_for_push_subscriptions.exs new file mode 100644 index 000000000..0f3a73067 --- /dev/null +++ b/priv/repo/migrations/20191026191603_set_not_null_for_push_subscriptions.exs @@ -0,0 +1,25 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForPushSubscriptions do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE push_subscriptions + ALTER COLUMN user_id SET NOT NULL, + ALTER COLUMN token_id SET NOT NULL, + ALTER COLUMN endpoint SET NOT NULL, + ALTER COLUMN key_p256dh SET NOT NULL, + ALTER COLUMN key_auth SET NOT NULL, + ALTER COLUMN data SET NOT NULL") + end + + def down do + execute("ALTER TABLE push_subscriptions + ALTER COLUMN user_id DROP NOT NULL, + ALTER COLUMN token_id DROP NOT NULL, + ALTER COLUMN endpoint DROP NOT NULL, + ALTER COLUMN key_p256dh DROP NOT NULL, + ALTER COLUMN key_auth DROP NOT NULL, + ALTER COLUMN data DROP NOT NULL") + end +end -- cgit v1.2.3 From 281072921874465ac2968c82131c0c4730688de2 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:55:29 +0300 Subject: Add migration --- .../20191026191635_set_not_null_for_registrations.exs | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) create mode 100644 priv/repo/migrations/20191026191635_set_not_null_for_registrations.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191635_set_not_null_for_registrations.exs b/priv/repo/migrations/20191026191635_set_not_null_for_registrations.exs new file mode 100644 index 000000000..ddfbf4c5e --- /dev/null +++ b/priv/repo/migrations/20191026191635_set_not_null_for_registrations.exs @@ -0,0 +1,19 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForRegistrations do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE registrations + ALTER COLUMN provider SET NOT NULL, + ALTER COLUMN uid SET NOT NULL, + ALTER COLUMN info SET NOT NULL") + end + + def down do + execute("ALTER TABLE registrations + ALTER COLUMN provider DROP NOT NULL, + ALTER COLUMN uid DROP NOT NULL, + ALTER COLUMN info DROP NOT NULL") + end +end -- cgit v1.2.3 From 2e608f7cbf46a02004982ddc48e5b0902869b401 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:56:48 +0300 Subject: Add migration --- ...191026191711_set_not_null_for_scheduled_activities.exs | 15 +++++++++++++++ 1 file changed, 15 insertions(+) create mode 100644 priv/repo/migrations/20191026191711_set_not_null_for_scheduled_activities.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191711_set_not_null_for_scheduled_activities.exs b/priv/repo/migrations/20191026191711_set_not_null_for_scheduled_activities.exs new file mode 100644 index 000000000..f1830c8c3 --- /dev/null +++ b/priv/repo/migrations/20191026191711_set_not_null_for_scheduled_activities.exs @@ -0,0 +1,15 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForScheduledActivities do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE scheduled_activities + ALTER COLUMN user_id SET NOT NULL") + end + + def down do + execute("ALTER TABLE scheduled_activities + ALTER COLUMN user_id DROP NOT NULL") + end +end -- cgit v1.2.3 From f5f9197fcee26af8d6a3bc8becb310a1c164623c Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:57:43 +0300 Subject: Add migration --- .../20191026191753_set_not_null_for_thread_mutes.exs | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 priv/repo/migrations/20191026191753_set_not_null_for_thread_mutes.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191753_set_not_null_for_thread_mutes.exs b/priv/repo/migrations/20191026191753_set_not_null_for_thread_mutes.exs new file mode 100644 index 000000000..daa7ce314 --- /dev/null +++ b/priv/repo/migrations/20191026191753_set_not_null_for_thread_mutes.exs @@ -0,0 +1,17 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForThreadMutes do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE thread_mutes + ALTER COLUMN user_id SET NOT NULL, + ALTER COLUMN context SET NOT NULL") + end + + def down do + execute("ALTER TABLE thread_mutes + ALTER COLUMN user_id DROP NOT NULL, + ALTER COLUMN context DROP NOT NULL") + end +end -- cgit v1.2.3 From bdb20394531a71a573a870c7635a4265df7f0cc9 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:58:37 +0300 Subject: Add migration --- ...1026191826_set_not_null_for_user_invite_tokens.exs | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) create mode 100644 priv/repo/migrations/20191026191826_set_not_null_for_user_invite_tokens.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191826_set_not_null_for_user_invite_tokens.exs b/priv/repo/migrations/20191026191826_set_not_null_for_user_invite_tokens.exs new file mode 100644 index 000000000..836544f7f --- /dev/null +++ b/priv/repo/migrations/20191026191826_set_not_null_for_user_invite_tokens.exs @@ -0,0 +1,19 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForUserInviteTokens do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + execute("ALTER TABLE user_invite_tokens + ALTER COLUMN used SET NOT NULL, + ALTER COLUMN uses SET NOT NULL, + ALTER COLUMN invite_type SET NOT NULL") + end + + def down do + execute("ALTER TABLE user_invite_tokens + ALTER COLUMN used DROP NOT NULL, + ALTER COLUMN uses DROP NOT NULL, + ALTER COLUMN invite_type DROP NOT NULL") + end +end -- cgit v1.2.3 From 175f6c83559387f71d49f6f3e4b1d7a12c5d011f Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sat, 26 Oct 2019 22:59:24 +0300 Subject: Add migration --- .../20191026191910_set_not_null_for_users.exs | 46 ++++++++++++++++++++++ 1 file changed, 46 insertions(+) create mode 100644 priv/repo/migrations/20191026191910_set_not_null_for_users.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191026191910_set_not_null_for_users.exs b/priv/repo/migrations/20191026191910_set_not_null_for_users.exs new file mode 100644 index 000000000..f145a89ab --- /dev/null +++ b/priv/repo/migrations/20191026191910_set_not_null_for_users.exs @@ -0,0 +1,46 @@ +defmodule Pleroma.Repo.Migrations.SetNotNullForUsers do + use Ecto.Migration + + # modify/3 function will require index recreation, so using execute/1 instead + + def up do + # irreversible + execute("UPDATE users SET follower_count = 0 WHERE follower_count IS NULL") + + execute("ALTER TABLE users + ALTER COLUMN following SET NOT NULL, + ALTER COLUMN local SET NOT NULL, + ALTER COLUMN source_data SET NOT NULL, + ALTER COLUMN note_count SET NOT NULL, + ALTER COLUMN follower_count SET NOT NULL, + ALTER COLUMN blocks SET NOT NULL, + ALTER COLUMN domain_blocks SET NOT NULL, + ALTER COLUMN mutes SET NOT NULL, + ALTER COLUMN muted_reblogs SET NOT NULL, + ALTER COLUMN muted_notifications SET NOT NULL, + ALTER COLUMN subscribers SET NOT NULL, + ALTER COLUMN pinned_activities SET NOT NULL, + ALTER COLUMN emoji SET NOT NULL, + ALTER COLUMN fields SET NOT NULL, + ALTER COLUMN raw_fields SET NOT NULL") + end + + def down do + execute("ALTER TABLE users + ALTER COLUMN following DROP NOT NULL, + ALTER COLUMN local DROP NOT NULL, + ALTER COLUMN source_data DROP NOT NULL, + ALTER COLUMN note_count DROP NOT NULL, + ALTER COLUMN follower_count DROP NOT NULL, + ALTER COLUMN blocks DROP NOT NULL, + ALTER COLUMN domain_blocks DROP NOT NULL, + ALTER COLUMN mutes DROP NOT NULL, + ALTER COLUMN muted_reblogs DROP NOT NULL, + ALTER COLUMN muted_notifications DROP NOT NULL, + ALTER COLUMN subscribers DROP NOT NULL, + ALTER COLUMN pinned_activities DROP NOT NULL, + ALTER COLUMN emoji DROP NOT NULL, + ALTER COLUMN fields DROP NOT NULL, + ALTER COLUMN raw_fields DROP NOT NULL") + end +end -- cgit v1.2.3 From 0be9cb086b070858b041cd15ee149d1323952aab Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Sun, 27 Oct 2019 19:29:35 +0300 Subject: Add migration --- .../20191027143434_add_defaults_to_all_tables.exs | 52 ++++++++++++++++++++++ 1 file changed, 52 insertions(+) create mode 100644 priv/repo/migrations/20191027143434_add_defaults_to_all_tables.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191027143434_add_defaults_to_all_tables.exs b/priv/repo/migrations/20191027143434_add_defaults_to_all_tables.exs new file mode 100644 index 000000000..ab60f1313 --- /dev/null +++ b/priv/repo/migrations/20191027143434_add_defaults_to_all_tables.exs @@ -0,0 +1,52 @@ +defmodule Pleroma.Repo.Migrations.AddDefaultsToAllTables do + use Ecto.Migration + + def up do + execute("ALTER TABLE activities + ALTER COLUMN recipients SET DEFAULT ARRAY[]::character varying[]") + + execute("ALTER TABLE filters + ALTER COLUMN whole_word SET DEFAULT true") + + execute("ALTER TABLE push_subscriptions + ALTER COLUMN data SET DEFAULT '{}'::jsonb") + + execute(~s(ALTER TABLE users + ALTER COLUMN following SET DEFAULT ARRAY[]::character varying[], + ALTER COLUMN tags SET DEFAULT ARRAY[]::character varying[], + ALTER COLUMN notification_settings SET DEFAULT + '{"followers": true, "follows": true, "non_follows": true, "non_followers": true}'::jsonb)) + + # irreversible updates + + execute( + "UPDATE activities SET recipients = ARRAY[]::character varying[] WHERE recipients IS NULL" + ) + + execute("UPDATE filters SET whole_word = true WHERE whole_word IS NULL") + + execute("UPDATE push_subscriptions SET data = '{}'::jsonb WHERE data IS NULL") + + execute("UPDATE users SET following = ARRAY[]::character varying[] WHERE following IS NULL") + execute("UPDATE users SET tags = ARRAY[]::character varying[] WHERE tags IS NULL") + execute(~s(UPDATE users SET notification_settings = + '{"followers": true, "follows": true, "non_follows": true, "non_followers": true}'::jsonb + WHERE notification_settings = '{}'::jsonb)) + end + + def down do + execute("ALTER TABLE activities + ALTER COLUMN recipients DROP DEFAULT") + + execute("ALTER TABLE filters + ALTER COLUMN whole_word DROP DEFAULT") + + execute("ALTER TABLE push_subscriptions + ALTER COLUMN data DROP DEFAULT") + + execute("ALTER TABLE users + ALTER COLUMN following DROP DEFAULT, + ALTER COLUMN tags DROP DEFAULT, + ALTER COLUMN notification_settings SET DEFAULT '{}'::jsonb") + end +end -- cgit v1.2.3 From 3c86a0ab247acf0c037285fb4d22d47d335fe4c1 Mon Sep 17 00:00:00 2001 From: Egor Kislitsyn Date: Mon, 28 Oct 2019 14:45:50 +0700 Subject: Fix `SetNotNullForUsers` migration --- priv/repo/migrations/20191026191910_set_not_null_for_users.exs | 2 -- 1 file changed, 2 deletions(-) (limited to 'priv') diff --git a/priv/repo/migrations/20191026191910_set_not_null_for_users.exs b/priv/repo/migrations/20191026191910_set_not_null_for_users.exs index f145a89ab..9d8d0ccf8 100644 --- a/priv/repo/migrations/20191026191910_set_not_null_for_users.exs +++ b/priv/repo/migrations/20191026191910_set_not_null_for_users.exs @@ -8,7 +8,6 @@ defmodule Pleroma.Repo.Migrations.SetNotNullForUsers do execute("UPDATE users SET follower_count = 0 WHERE follower_count IS NULL") execute("ALTER TABLE users - ALTER COLUMN following SET NOT NULL, ALTER COLUMN local SET NOT NULL, ALTER COLUMN source_data SET NOT NULL, ALTER COLUMN note_count SET NOT NULL, @@ -27,7 +26,6 @@ defmodule Pleroma.Repo.Migrations.SetNotNullForUsers do def down do execute("ALTER TABLE users - ALTER COLUMN following DROP NOT NULL, ALTER COLUMN local DROP NOT NULL, ALTER COLUMN source_data DROP NOT NULL, ALTER COLUMN note_count DROP NOT NULL, -- cgit v1.2.3 From 4b0893631f2d9f157c200f2787fe5154dc0b811e Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Mon, 28 Oct 2019 19:16:19 +0300 Subject: Complete defaults --- .../20191027143434_add_defaults_to_all_tables.exs | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) (limited to 'priv') diff --git a/priv/repo/migrations/20191027143434_add_defaults_to_all_tables.exs b/priv/repo/migrations/20191027143434_add_defaults_to_all_tables.exs index ab60f1313..0d2794ad3 100644 --- a/priv/repo/migrations/20191027143434_add_defaults_to_all_tables.exs +++ b/priv/repo/migrations/20191027143434_add_defaults_to_all_tables.exs @@ -27,6 +27,25 @@ defmodule Pleroma.Repo.Migrations.AddDefaultsToAllTables do execute("UPDATE push_subscriptions SET data = '{}'::jsonb WHERE data IS NULL") + execute("UPDATE users SET source_data = '{}'::jsonb where source_data IS NULL") + execute("UPDATE users SET note_count = 0 where note_count IS NULL") + execute("UPDATE users SET background = '{}'::jsonb where background IS NULL") + execute("UPDATE users SET follower_count = 0 where follower_count IS NULL") + + execute( + "UPDATE users SET unread_conversation_count = 0 where unread_conversation_count IS NULL" + ) + + execute( + ~s(UPDATE users SET email_notifications = '{"digest": false}'::jsonb where email_notifications IS NULL) + ) + + execute("UPDATE users SET default_scope = 'public' where default_scope IS NULL") + + execute( + "UPDATE users SET pleroma_settings_store = '{}'::jsonb where pleroma_settings_store IS NULL" + ) + execute("UPDATE users SET following = ARRAY[]::character varying[] WHERE following IS NULL") execute("UPDATE users SET tags = ARRAY[]::character varying[] WHERE tags IS NULL") execute(~s(UPDATE users SET notification_settings = -- cgit v1.2.3 From 435d220700c694f1312bf213d0591054a309489a Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Mon, 28 Oct 2019 19:17:50 +0300 Subject: Move setDefaultsToTables to past to run before notNull migrations --- .../20191025143434_add_defaults_to_tables.exs | 71 ++++++++++++++++++++++ .../20191027143434_add_defaults_to_all_tables.exs | 71 ---------------------- 2 files changed, 71 insertions(+), 71 deletions(-) create mode 100644 priv/repo/migrations/20191025143434_add_defaults_to_tables.exs delete mode 100644 priv/repo/migrations/20191027143434_add_defaults_to_all_tables.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191025143434_add_defaults_to_tables.exs b/priv/repo/migrations/20191025143434_add_defaults_to_tables.exs new file mode 100644 index 000000000..d16ab19f9 --- /dev/null +++ b/priv/repo/migrations/20191025143434_add_defaults_to_tables.exs @@ -0,0 +1,71 @@ +defmodule Pleroma.Repo.Migrations.AddDefaultsToTables do + use Ecto.Migration + + def up do + execute("ALTER TABLE activities + ALTER COLUMN recipients SET DEFAULT ARRAY[]::character varying[]") + + execute("ALTER TABLE filters + ALTER COLUMN whole_word SET DEFAULT true") + + execute("ALTER TABLE push_subscriptions + ALTER COLUMN data SET DEFAULT '{}'::jsonb") + + execute(~s(ALTER TABLE users + ALTER COLUMN following SET DEFAULT ARRAY[]::character varying[], + ALTER COLUMN tags SET DEFAULT ARRAY[]::character varying[], + ALTER COLUMN notification_settings SET DEFAULT + '{"followers": true, "follows": true, "non_follows": true, "non_followers": true}'::jsonb)) + + # irreversible updates + + execute( + "UPDATE activities SET recipients = ARRAY[]::character varying[] WHERE recipients IS NULL" + ) + + execute("UPDATE filters SET whole_word = true WHERE whole_word IS NULL") + + execute("UPDATE push_subscriptions SET data = '{}'::jsonb WHERE data IS NULL") + + execute("UPDATE users SET source_data = '{}'::jsonb where source_data IS NULL") + execute("UPDATE users SET note_count = 0 where note_count IS NULL") + execute("UPDATE users SET background = '{}'::jsonb where background IS NULL") + execute("UPDATE users SET follower_count = 0 where follower_count IS NULL") + + execute( + "UPDATE users SET unread_conversation_count = 0 where unread_conversation_count IS NULL" + ) + + execute( + ~s(UPDATE users SET email_notifications = '{"digest": false}'::jsonb where email_notifications IS NULL) + ) + + execute("UPDATE users SET default_scope = 'public' where default_scope IS NULL") + + execute( + "UPDATE users SET pleroma_settings_store = '{}'::jsonb where pleroma_settings_store IS NULL" + ) + + execute("UPDATE users SET following = ARRAY[]::character varying[] WHERE following IS NULL") + execute("UPDATE users SET tags = ARRAY[]::character varying[] WHERE tags IS NULL") + execute(~s(UPDATE users SET notification_settings = + '{"followers": true, "follows": true, "non_follows": true, "non_followers": true}'::jsonb + WHERE notification_settings = '{}'::jsonb)) + end + + def down do + execute("ALTER TABLE activities + ALTER COLUMN recipients DROP DEFAULT") + + execute("ALTER TABLE filters + ALTER COLUMN whole_word DROP DEFAULT") + + execute("ALTER TABLE push_subscriptions + ALTER COLUMN data DROP DEFAULT") + + execute("ALTER TABLE users + ALTER COLUMN following DROP DEFAULT, + ALTER COLUMN tags DROP DEFAULT, + ALTER COLUMN notification_settings SET DEFAULT '{}'::jsonb") + end +end diff --git a/priv/repo/migrations/20191027143434_add_defaults_to_all_tables.exs b/priv/repo/migrations/20191027143434_add_defaults_to_all_tables.exs deleted file mode 100644 index 0d2794ad3..000000000 --- a/priv/repo/migrations/20191027143434_add_defaults_to_all_tables.exs +++ /dev/null @@ -1,71 +0,0 @@ -defmodule Pleroma.Repo.Migrations.AddDefaultsToAllTables do - use Ecto.Migration - - def up do - execute("ALTER TABLE activities - ALTER COLUMN recipients SET DEFAULT ARRAY[]::character varying[]") - - execute("ALTER TABLE filters - ALTER COLUMN whole_word SET DEFAULT true") - - execute("ALTER TABLE push_subscriptions - ALTER COLUMN data SET DEFAULT '{}'::jsonb") - - execute(~s(ALTER TABLE users - ALTER COLUMN following SET DEFAULT ARRAY[]::character varying[], - ALTER COLUMN tags SET DEFAULT ARRAY[]::character varying[], - ALTER COLUMN notification_settings SET DEFAULT - '{"followers": true, "follows": true, "non_follows": true, "non_followers": true}'::jsonb)) - - # irreversible updates - - execute( - "UPDATE activities SET recipients = ARRAY[]::character varying[] WHERE recipients IS NULL" - ) - - execute("UPDATE filters SET whole_word = true WHERE whole_word IS NULL") - - execute("UPDATE push_subscriptions SET data = '{}'::jsonb WHERE data IS NULL") - - execute("UPDATE users SET source_data = '{}'::jsonb where source_data IS NULL") - execute("UPDATE users SET note_count = 0 where note_count IS NULL") - execute("UPDATE users SET background = '{}'::jsonb where background IS NULL") - execute("UPDATE users SET follower_count = 0 where follower_count IS NULL") - - execute( - "UPDATE users SET unread_conversation_count = 0 where unread_conversation_count IS NULL" - ) - - execute( - ~s(UPDATE users SET email_notifications = '{"digest": false}'::jsonb where email_notifications IS NULL) - ) - - execute("UPDATE users SET default_scope = 'public' where default_scope IS NULL") - - execute( - "UPDATE users SET pleroma_settings_store = '{}'::jsonb where pleroma_settings_store IS NULL" - ) - - execute("UPDATE users SET following = ARRAY[]::character varying[] WHERE following IS NULL") - execute("UPDATE users SET tags = ARRAY[]::character varying[] WHERE tags IS NULL") - execute(~s(UPDATE users SET notification_settings = - '{"followers": true, "follows": true, "non_follows": true, "non_followers": true}'::jsonb - WHERE notification_settings = '{}'::jsonb)) - end - - def down do - execute("ALTER TABLE activities - ALTER COLUMN recipients DROP DEFAULT") - - execute("ALTER TABLE filters - ALTER COLUMN whole_word DROP DEFAULT") - - execute("ALTER TABLE push_subscriptions - ALTER COLUMN data DROP DEFAULT") - - execute("ALTER TABLE users - ALTER COLUMN following DROP DEFAULT, - ALTER COLUMN tags DROP DEFAULT, - ALTER COLUMN notification_settings SET DEFAULT '{}'::jsonb") - end -end -- cgit v1.2.3 From 5334190056b853dda57f16cbbaa230e8947821c6 Mon Sep 17 00:00:00 2001 From: kPherox Date: Tue, 29 Oct 2019 19:16:34 +0900 Subject: Migrate missing follow requests --- ...91029101340_migrate_missing_follow_requests.exs | 35 ++++++++++++++++++++++ 1 file changed, 35 insertions(+) create mode 100644 priv/repo/migrations/20191029101340_migrate_missing_follow_requests.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191029101340_migrate_missing_follow_requests.exs b/priv/repo/migrations/20191029101340_migrate_missing_follow_requests.exs new file mode 100644 index 000000000..1b2666f3a --- /dev/null +++ b/priv/repo/migrations/20191029101340_migrate_missing_follow_requests.exs @@ -0,0 +1,35 @@ +defmodule Pleroma.Repo.Migrations.MigrateFollowingRelationships do + use Ecto.Migration + + def change do + execute(import_pending_follows_from_activities(), "") + end + + defp import_pending_follows_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' = 'pending' + ORDER BY activities.updated_at DESC + ON CONFLICT DO NOTHING + """ + end +end -- cgit v1.2.3 From 3e09b7c5ae2d8e6d3abb2020f2c63caad278e73f Mon Sep 17 00:00:00 2001 From: rinpatch Date: Tue, 29 Oct 2019 16:56:24 +0300 Subject: Fix two migrations sharing the same module name This makes ecto execute only the latter one. --- priv/repo/migrations/20191029101340_migrate_missing_follow_requests.exs | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'priv') diff --git a/priv/repo/migrations/20191029101340_migrate_missing_follow_requests.exs b/priv/repo/migrations/20191029101340_migrate_missing_follow_requests.exs index 1b2666f3a..90b18efc8 100644 --- a/priv/repo/migrations/20191029101340_migrate_missing_follow_requests.exs +++ b/priv/repo/migrations/20191029101340_migrate_missing_follow_requests.exs @@ -1,4 +1,4 @@ -defmodule Pleroma.Repo.Migrations.MigrateFollowingRelationships do +defmodule Pleroma.Repo.Migrations.MigrateMissingFollowingRelationships do use Ecto.Migration def change do -- cgit v1.2.3 From 40d5fb6ef875262795d87b95bff1ef44ee75d659 Mon Sep 17 00:00:00 2001 From: Egor Kislitsyn Date: Wed, 30 Oct 2019 15:52:37 +0700 Subject: Add a migration to fix blocked follows --- .../20191029172832_fix_blocked_follows.exs | 112 +++++++++++++++++++++ 1 file changed, 112 insertions(+) create mode 100644 priv/repo/migrations/20191029172832_fix_blocked_follows.exs (limited to 'priv') diff --git a/priv/repo/migrations/20191029172832_fix_blocked_follows.exs b/priv/repo/migrations/20191029172832_fix_blocked_follows.exs new file mode 100644 index 000000000..71f8f1330 --- /dev/null +++ b/priv/repo/migrations/20191029172832_fix_blocked_follows.exs @@ -0,0 +1,112 @@ +defmodule Pleroma.Repo.Migrations.FixBlockedFollows do + use Ecto.Migration + + import Ecto.Query + alias Pleroma.Config + alias Pleroma.Repo + + def up do + unfollow_blocked = Config.get([:activitypub, :unfollow_blocked]) + + if unfollow_blocked do + "activities" + |> where([activity], fragment("? ->> 'type' = 'Block'", activity.data)) + |> distinct([activity], [ + activity.actor, + fragment( + "coalesce((?)->'object'->>'id', (?)->>'object')", + activity.data, + activity.data + ) + ]) + |> order_by([activity], [fragment("? desc nulls last", activity.id)]) + |> select([activity], %{ + blocker: activity.actor, + blocked: + fragment("coalesce((?)->'object'->>'id', (?)->>'object')", activity.data, activity.data), + created_at: activity.id + }) + |> Repo.stream() + |> Enum.map(&unfollow_if_blocked/1) + |> Enum.uniq() + |> Enum.each(&update_follower_count/1) + end + end + + def down do + end + + def unfollow_if_blocked(%{blocker: blocker_id, blocked: blocked_id, created_at: blocked_at}) do + query = + from( + activity in "activities", + where: fragment("? ->> 'type' = 'Follow'", activity.data), + where: activity.actor == ^blocked_id, + # this is to use the index + where: + fragment( + "coalesce((?)->'object'->>'id', (?)->>'object') = ?", + activity.data, + activity.data, + ^blocker_id + ), + where: activity.id > ^blocked_at, + where: fragment("(?)->>'state' = 'accept'", activity.data), + order_by: [fragment("? desc nulls last", activity.id)] + ) + + unless Repo.exists?(query) do + blocker = "users" |> select([:id, :local]) |> Repo.get_by(ap_id: blocker_id) + blocked = "users" |> select([:id]) |> Repo.get_by(ap_id: blocked_id) + + if !is_nil(blocker) && !is_nil(blocked) do + unfollow(blocked, blocker) + end + end + end + + def unfollow(%{id: follower_id}, %{id: followed_id} = followed) do + following_relationship = + "following_relationships" + |> where(follower_id: ^follower_id, following_id: ^followed_id, state: "accept") + |> select([:id]) + |> Repo.one() + + case following_relationship do + nil -> + {:ok, nil} + + %{id: following_relationship_id} -> + "following_relationships" + |> where(id: ^following_relationship_id) + |> Repo.delete_all() + + followed + end + end + + def update_follower_count(%{id: user_id} = user) do + if user.local or !Pleroma.Config.get([:instance, :external_user_synchronization]) do + follower_count_query = + "users" + |> where([u], u.id != ^user_id) + |> where([u], u.deactivated != ^true) + |> join(:inner, [u], r in "following_relationships", + as: :relationships, + on: r.following_id == ^user_id and r.follower_id == u.id + ) + |> where([relationships: r], r.state == "accept") + |> select([u], %{count: count(u.id)}) + + "users" + |> where(id: ^user_id) + |> join(:inner, [u], s in subquery(follower_count_query)) + |> update([u, s], + set: [follower_count: s.count] + ) + |> Repo.update_all([]) + end + end + + def update_follower_count(_), do: :noop +end -- cgit v1.2.3 From c546da7cfe800ee0a0ac2ecf9981bb131cd63291 Mon Sep 17 00:00:00 2001 From: rinpatch Date: Wed, 30 Oct 2019 12:59:14 +0300 Subject: Fix bookmark migration using a query with a schema This resulted in failures when updating from Pleroma <1.0 because of all the new fields that were added to the user schema. --- priv/repo/migrations/20190414125034_migrate_old_bookmarks.exs | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'priv') diff --git a/priv/repo/migrations/20190414125034_migrate_old_bookmarks.exs b/priv/repo/migrations/20190414125034_migrate_old_bookmarks.exs index f3928a149..99102117f 100644 --- a/priv/repo/migrations/20190414125034_migrate_old_bookmarks.exs +++ b/priv/repo/migrations/20190414125034_migrate_old_bookmarks.exs @@ -8,10 +8,10 @@ defmodule Pleroma.Repo.Migrations.MigrateOldBookmarks do def up do query = - from(u in User, + from(u in "users", where: u.local == true, - where: fragment("array_length(bookmarks, 1)") > 0, - select: %{id: u.id, bookmarks: fragment("bookmarks")} + where: fragment("array_length(?, 1)", u.bookmarks) > 0, + select: %{id: u.id, bookmarks: u.bookmarks} ) Repo.stream(query) -- cgit v1.2.3 From 8bb6da7cd604359afb5eeabeeac0207edff07ce3 Mon Sep 17 00:00:00 2001 From: Roman Chvanikov Date: Wed, 30 Oct 2019 18:34:14 +0300 Subject: Remove following column from the migrations --- priv/repo/migrations/20191025143434_add_defaults_to_tables.exs | 3 --- priv/repo/migrations/20191026191910_set_not_null_for_users.exs | 2 -- 2 files changed, 5 deletions(-) (limited to 'priv') diff --git a/priv/repo/migrations/20191025143434_add_defaults_to_tables.exs b/priv/repo/migrations/20191025143434_add_defaults_to_tables.exs index d16ab19f9..a5bc82335 100644 --- a/priv/repo/migrations/20191025143434_add_defaults_to_tables.exs +++ b/priv/repo/migrations/20191025143434_add_defaults_to_tables.exs @@ -12,7 +12,6 @@ defmodule Pleroma.Repo.Migrations.AddDefaultsToTables do ALTER COLUMN data SET DEFAULT '{}'::jsonb") execute(~s(ALTER TABLE users - ALTER COLUMN following SET DEFAULT ARRAY[]::character varying[], ALTER COLUMN tags SET DEFAULT ARRAY[]::character varying[], ALTER COLUMN notification_settings SET DEFAULT '{"followers": true, "follows": true, "non_follows": true, "non_followers": true}'::jsonb)) @@ -46,7 +45,6 @@ defmodule Pleroma.Repo.Migrations.AddDefaultsToTables do "UPDATE users SET pleroma_settings_store = '{}'::jsonb where pleroma_settings_store IS NULL" ) - execute("UPDATE users SET following = ARRAY[]::character varying[] WHERE following IS NULL") execute("UPDATE users SET tags = ARRAY[]::character varying[] WHERE tags IS NULL") execute(~s(UPDATE users SET notification_settings = '{"followers": true, "follows": true, "non_follows": true, "non_followers": true}'::jsonb @@ -64,7 +62,6 @@ defmodule Pleroma.Repo.Migrations.AddDefaultsToTables do ALTER COLUMN data DROP DEFAULT") execute("ALTER TABLE users - ALTER COLUMN following DROP DEFAULT, ALTER COLUMN tags DROP DEFAULT, ALTER COLUMN notification_settings SET DEFAULT '{}'::jsonb") end diff --git a/priv/repo/migrations/20191026191910_set_not_null_for_users.exs b/priv/repo/migrations/20191026191910_set_not_null_for_users.exs index f145a89ab..9d8d0ccf8 100644 --- a/priv/repo/migrations/20191026191910_set_not_null_for_users.exs +++ b/priv/repo/migrations/20191026191910_set_not_null_for_users.exs @@ -8,7 +8,6 @@ defmodule Pleroma.Repo.Migrations.SetNotNullForUsers do execute("UPDATE users SET follower_count = 0 WHERE follower_count IS NULL") execute("ALTER TABLE users - ALTER COLUMN following SET NOT NULL, ALTER COLUMN local SET NOT NULL, ALTER COLUMN source_data SET NOT NULL, ALTER COLUMN note_count SET NOT NULL, @@ -27,7 +26,6 @@ defmodule Pleroma.Repo.Migrations.SetNotNullForUsers do def down do execute("ALTER TABLE users - ALTER COLUMN following DROP NOT NULL, ALTER COLUMN local DROP NOT NULL, ALTER COLUMN source_data DROP NOT NULL, ALTER COLUMN note_count DROP NOT NULL, -- cgit v1.2.3