diff options
Diffstat (limited to 'priv')
30 files changed, 898 insertions, 64 deletions
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..d49e24ee4 --- /dev/null +++ b/priv/repo/migrations/20191007073319_create_following_relationships.exs @@ -0,0 +1,149 @@ +defmodule Pleroma.Repo.Migrations.CreateFollowingRelationships do + use Ecto.Migration + + 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(update_thread_visibility(), restore_thread_visibility()) + end + + # 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 $$ + 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/20191008132217_migrate_following_relationships.exs b/priv/repo/migrations/20191008132217_migrate_following_relationships.exs new file mode 100644 index 000000000..9d5c2648f --- /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 + follower.id AS follower_id, + CASE follower.local + WHEN TRUE THEN + 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 follower ON follower.id = following_relationships.follower_id + JOIN users AS following ON following.id = following_relationships.following_id + GROUP BY + follower.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..21c0af9f4 --- /dev/null +++ b/priv/repo/migrations/20191008132427_drop_users_following.exs @@ -0,0 +1,16 @@ +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 + + def change do + drop(index(:users, [:following], concurrently: true, using: :gin)) + + alter table(:users) do + remove(:following, {:array, :string}, default: []) + end + end +end 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, 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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..9d8d0ccf8 --- /dev/null +++ b/priv/repo/migrations/20191026191910_set_not_null_for_users.exs @@ -0,0 +1,44 @@ +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 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 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 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..90b18efc8 --- /dev/null +++ b/priv/repo/migrations/20191029101340_migrate_missing_follow_requests.exs @@ -0,0 +1,35 @@ +defmodule Pleroma.Repo.Migrations.MigrateMissingFollowingRelationships 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 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 |