diff options
Diffstat (limited to 'priv/repo/migrations')
22 files changed, 371 insertions, 6 deletions
diff --git a/priv/repo/migrations/20190408123347_create_conversations.exs b/priv/repo/migrations/20190408123347_create_conversations.exs index d75459e82..3eaa6136c 100644 --- a/priv/repo/migrations/20190408123347_create_conversations.exs +++ b/priv/repo/migrations/20190408123347_create_conversations.exs @@ -1,5 +1,5 @@ # Pleroma: A lightweight social networking server -# Copyright © 2017-2019 Pleroma Authors <https://pleroma.social/> +# Copyright © 2017-2020 Pleroma Authors <https://pleroma.social/> # SPDX-License-Identifier: AGPL-3.0-only defmodule Pleroma.Repo.Migrations.CreateConversations do diff --git a/priv/repo/migrations/20190414125034_migrate_old_bookmarks.exs b/priv/repo/migrations/20190414125034_migrate_old_bookmarks.exs index 99102117f..b6f0ac66b 100644 --- a/priv/repo/migrations/20190414125034_migrate_old_bookmarks.exs +++ b/priv/repo/migrations/20190414125034_migrate_old_bookmarks.exs @@ -3,7 +3,6 @@ defmodule Pleroma.Repo.Migrations.MigrateOldBookmarks do import Ecto.Query alias Pleroma.Activity alias Pleroma.Bookmark - alias Pleroma.User alias Pleroma.Repo def up do @@ -17,7 +16,11 @@ defmodule Pleroma.Repo.Migrations.MigrateOldBookmarks do Repo.stream(query) |> Enum.each(fn %{id: user_id, bookmarks: bookmarks} -> Enum.each(bookmarks, fn ap_id -> - activity = Activity.get_create_by_object_ap_id(ap_id) + activity = + ap_id + |> Activity.create_by_object_ap_id() + |> Repo.one() + unless is_nil(activity), do: {:ok, _} = Bookmark.create(user_id, activity.id) end) end) diff --git a/priv/repo/migrations/20190506054542_add_multi_factor_authentication_settings_to_user.exs b/priv/repo/migrations/20190506054542_add_multi_factor_authentication_settings_to_user.exs new file mode 100644 index 000000000..8b653c61f --- /dev/null +++ b/priv/repo/migrations/20190506054542_add_multi_factor_authentication_settings_to_user.exs @@ -0,0 +1,9 @@ +defmodule Pleroma.Repo.Migrations.AddMultiFactorAuthenticationSettingsToUser do + use Ecto.Migration + + def change do + alter table(:users) do + add(:multi_factor_authentication_settings, :map, default: %{}) + end + end +end diff --git a/priv/repo/migrations/20190508193213_create_mfa_tokens.exs b/priv/repo/migrations/20190508193213_create_mfa_tokens.exs new file mode 100644 index 000000000..da9f8fabe --- /dev/null +++ b/priv/repo/migrations/20190508193213_create_mfa_tokens.exs @@ -0,0 +1,16 @@ +defmodule Pleroma.Repo.Migrations.CreateMfaTokens do + use Ecto.Migration + + def change do + create table(:mfa_tokens) do + add(:user_id, references(:users, type: :uuid, on_delete: :delete_all)) + add(:authorization_id, references(:oauth_authorizations, on_delete: :delete_all)) + add(:token, :string) + add(:valid_until, :naive_datetime_usec) + + timestamps() + end + + create(unique_index(:mfa_tokens, :token)) + end +end 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 a5170d521..44f9891b1 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 @@ -1,7 +1,8 @@ defmodule Pleroma.Repo.Migrations.AddFollowingAddressFromSourceData do - use Ecto.Migration - import Ecto.Query alias Pleroma.User + import Ecto.Query + require Logger + use Ecto.Migration def change do query = @@ -19,6 +20,9 @@ defmodule Pleroma.Repo.Migrations.AddFollowingAddressFromSourceData do :following_address ]) |> Pleroma.Repo.update() + + user -> + Logger.warn("User #{user.id} / #{user.nickname} does not seem to have source_data") end) end end diff --git a/priv/repo/migrations/20190711042021_create_safe_jsonb_set.exs b/priv/repo/migrations/20190711042021_create_safe_jsonb_set.exs index 2f336a5e8..43d616705 100644 --- a/priv/repo/migrations/20190711042021_create_safe_jsonb_set.exs +++ b/priv/repo/migrations/20190711042021_create_safe_jsonb_set.exs @@ -1,6 +1,5 @@ defmodule Pleroma.Repo.Migrations.CreateSafeJsonbSet do use Ecto.Migration - alias Pleroma.User def change do execute(""" 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 fc9bf70ba..bbd502044 100644 --- a/priv/repo/migrations/20190711042024_copy_muted_to_muted_notifications.exs +++ b/priv/repo/migrations/20190711042024_copy_muted_to_muted_notifications.exs @@ -2,6 +2,8 @@ defmodule Pleroma.Repo.Migrations.CopyMutedToMutedNotifications do use Ecto.Migration def change do + execute("update users set info = '{}' where info is null") + execute( "update users set info = safe_jsonb_set(info, '{muted_notifications}', info->'mutes', true) where local = true" ) diff --git a/priv/repo/migrations/20200109123126_add_counter_cache_table.exs b/priv/repo/migrations/20200109123126_add_counter_cache_table.exs new file mode 100644 index 000000000..df9e21193 --- /dev/null +++ b/priv/repo/migrations/20200109123126_add_counter_cache_table.exs @@ -0,0 +1,55 @@ +defmodule Pleroma.Repo.Migrations.AddCounterCacheTable do + use Ecto.Migration + + def up do + create_if_not_exists table(:counter_cache) do + add(:name, :string, null: false) + add(:count, :bigint, null: false, default: 0) + end + + create_if_not_exists(unique_index(:counter_cache, [:name])) + + """ + CREATE OR REPLACE FUNCTION update_status_visibility_counter_cache() + RETURNS TRIGGER AS + $$ + DECLARE + BEGIN + IF TG_OP = 'INSERT' THEN + IF NEW.data->>'type' = 'Create' THEN + EXECUTE 'INSERT INTO counter_cache (name, count) VALUES (''status_visibility_' || activity_visibility(NEW.actor, NEW.recipients, NEW.data) || ''', 1) ON CONFLICT (name) DO UPDATE SET count = counter_cache.count + 1'; + END IF; + RETURN NEW; + ELSIF TG_OP = 'UPDATE' THEN + IF (NEW.data->>'type' = 'Create') and (OLD.data->>'type' = 'Create') and activity_visibility(NEW.actor, NEW.recipients, NEW.data) != activity_visibility(OLD.actor, OLD.recipients, OLD.data) THEN + EXECUTE 'INSERT INTO counter_cache (name, count) VALUES (''status_visibility_' || activity_visibility(NEW.actor, NEW.recipients, NEW.data) || ''', 1) ON CONFLICT (name) DO UPDATE SET count = counter_cache.count + 1'; + EXECUTE 'update counter_cache SET count = counter_cache.count - 1 where count > 0 and name = ''status_visibility_' || activity_visibility(OLD.actor, OLD.recipients, OLD.data) || ''';'; + END IF; + RETURN NEW; + ELSIF TG_OP = 'DELETE' THEN + IF OLD.data->>'type' = 'Create' THEN + EXECUTE 'update counter_cache SET count = counter_cache.count - 1 where count > 0 and name = ''status_visibility_' || activity_visibility(OLD.actor, OLD.recipients, OLD.data) || ''';'; + END IF; + RETURN OLD; + END IF; + END; + $$ + LANGUAGE 'plpgsql'; + """ + |> execute() + + """ + CREATE TRIGGER status_visibility_counter_cache_trigger BEFORE INSERT OR UPDATE of recipients, data OR DELETE ON activities + FOR EACH ROW + EXECUTE PROCEDURE update_status_visibility_counter_cache(); + """ + |> execute() + end + + def down do + execute("drop trigger if exists status_visibility_counter_cache_trigger on activities") + execute("drop function if exists update_status_visibility_counter_cache()") + drop_if_exists(unique_index(:counter_cache, [:name])) + drop_if_exists(table(:counter_cache)) + end +end diff --git a/priv/repo/migrations/20200227122417_add_trusted_to_apps.exs b/priv/repo/migrations/20200227122417_add_trusted_to_apps.exs new file mode 100644 index 000000000..4e2a62af0 --- /dev/null +++ b/priv/repo/migrations/20200227122417_add_trusted_to_apps.exs @@ -0,0 +1,9 @@ +defmodule Pleroma.Repo.Migrations.AddTrustedToApps do + use Ecto.Migration + + def change do + alter table(:apps) do + add(:trusted, :boolean, default: false) + end + end +end diff --git a/priv/repo/migrations/20200307103755_fix_moderation_log_subjects.exs b/priv/repo/migrations/20200307103755_fix_moderation_log_subjects.exs new file mode 100644 index 000000000..d1c8539e1 --- /dev/null +++ b/priv/repo/migrations/20200307103755_fix_moderation_log_subjects.exs @@ -0,0 +1,9 @@ +defmodule Pleroma.Repo.Migrations.FixModerationLogSubjects do + use Ecto.Migration + + def change do + execute( + "update moderation_log set data = safe_jsonb_set(data, '{subject}', safe_jsonb_set('[]'::jsonb, '{0}', data->'subject')) where jsonb_typeof(data->'subject') != 'array' and data->>'action' = ANY('{revoke,grant,activate,deactivate,delete}');" + ) + end +end diff --git a/priv/repo/migrations/20200314123607_config_remove_fetch_initial_posts.exs b/priv/repo/migrations/20200314123607_config_remove_fetch_initial_posts.exs new file mode 100644 index 000000000..392f531e8 --- /dev/null +++ b/priv/repo/migrations/20200314123607_config_remove_fetch_initial_posts.exs @@ -0,0 +1,10 @@ +defmodule Pleroma.Repo.Migrations.ConfigRemoveFetchInitialPosts do + use Ecto.Migration + + def change do + execute( + "delete from config where config.key = ':fetch_initial_posts' and config.group = ':pleroma';", + "" + ) + end +end diff --git a/priv/repo/migrations/20200315125756_delete_fetch_initial_posts_jobs.exs b/priv/repo/migrations/20200315125756_delete_fetch_initial_posts_jobs.exs new file mode 100644 index 000000000..5b8e3ab91 --- /dev/null +++ b/priv/repo/migrations/20200315125756_delete_fetch_initial_posts_jobs.exs @@ -0,0 +1,10 @@ +defmodule Pleroma.Repo.Migrations.DeleteFetchInitialPostsJobs do + use Ecto.Migration + + def change do + execute( + "delete from oban_jobs where worker = 'Pleroma.Workers.BackgroundWorker' and args->>'op' = 'fetch_initial_posts';", + "" + ) + end +end diff --git a/priv/repo/migrations/20200328124805_change_following_relationships_state_to_integer.exs b/priv/repo/migrations/20200328124805_change_following_relationships_state_to_integer.exs new file mode 100644 index 000000000..2b0820f3f --- /dev/null +++ b/priv/repo/migrations/20200328124805_change_following_relationships_state_to_integer.exs @@ -0,0 +1,29 @@ +defmodule Pleroma.Repo.Migrations.ChangeFollowingRelationshipsStateToInteger do + use Ecto.Migration + + @alter_following_relationship_state "ALTER TABLE following_relationships ALTER COLUMN state" + + def up do + execute(""" + #{@alter_following_relationship_state} TYPE integer USING + CASE + WHEN state = 'pending' THEN 1 + WHEN state = 'accept' THEN 2 + WHEN state = 'reject' THEN 3 + ELSE 0 + END; + """) + end + + def down do + execute(""" + #{@alter_following_relationship_state} TYPE varchar(255) USING + CASE + WHEN state = 1 THEN 'pending' + WHEN state = 2 THEN 'accept' + WHEN state = 3 THEN 'reject' + ELSE '' + END; + """) + end +end diff --git a/priv/repo/migrations/20200328130139_add_following_relationships_following_id_index.exs b/priv/repo/migrations/20200328130139_add_following_relationships_following_id_index.exs new file mode 100644 index 000000000..884832f84 --- /dev/null +++ b/priv/repo/migrations/20200328130139_add_following_relationships_following_id_index.exs @@ -0,0 +1,11 @@ +defmodule Pleroma.Repo.Migrations.AddFollowingRelationshipsFollowingIdIndex do + use Ecto.Migration + + # [:follower_index] index is useless because of [:follower_id, :following_id] index + # [:following_id] index makes sense because of user's followers-targeted queries + def change do + drop_if_exists(index(:following_relationships, [:follower_id])) + + create_if_not_exists(index(:following_relationships, [:following_id])) + end +end diff --git a/priv/repo/migrations/20200401030751_users_add_public_key.exs b/priv/repo/migrations/20200401030751_users_add_public_key.exs new file mode 100644 index 000000000..04e5ad1e2 --- /dev/null +++ b/priv/repo/migrations/20200401030751_users_add_public_key.exs @@ -0,0 +1,17 @@ +defmodule Pleroma.Repo.Migrations.UsersAddPublicKey do + use Ecto.Migration + + def up do + alter table(:users) do + add_if_not_exists(:public_key, :text) + end + + execute("UPDATE users SET public_key = source_data->'publicKey'->>'publicKeyPem'") + end + + def down do + alter table(:users) do + remove_if_exists(:public_key, :text) + end + end +end diff --git a/priv/repo/migrations/20200401072456_users_add_inboxes.exs b/priv/repo/migrations/20200401072456_users_add_inboxes.exs new file mode 100644 index 000000000..0947f0ab2 --- /dev/null +++ b/priv/repo/migrations/20200401072456_users_add_inboxes.exs @@ -0,0 +1,20 @@ +defmodule Pleroma.Repo.Migrations.UsersAddInboxes do + use Ecto.Migration + + def up do + alter table(:users) do + add_if_not_exists(:inbox, :text) + add_if_not_exists(:shared_inbox, :text) + end + + execute("UPDATE users SET inbox = source_data->>'inbox'") + execute("UPDATE users SET shared_inbox = source_data->'endpoints'->>'sharedInbox'") + end + + def down do + alter table(:users) do + remove_if_exists(:inbox, :text) + remove_if_exists(:shared_inbox, :text) + end + end +end diff --git a/priv/repo/migrations/20200402063221_update_oban_jobs_table.exs b/priv/repo/migrations/20200402063221_update_oban_jobs_table.exs new file mode 100644 index 000000000..e7ff04008 --- /dev/null +++ b/priv/repo/migrations/20200402063221_update_oban_jobs_table.exs @@ -0,0 +1,11 @@ +defmodule Pleroma.Repo.Migrations.UpdateObanJobsTable do + use Ecto.Migration + + def up do + Oban.Migrations.up(version: 8) + end + + def down do + Oban.Migrations.down(version: 7) + end +end diff --git a/priv/repo/migrations/20200406100225_users_add_emoji.exs b/priv/repo/migrations/20200406100225_users_add_emoji.exs new file mode 100644 index 000000000..f248108de --- /dev/null +++ b/priv/repo/migrations/20200406100225_users_add_emoji.exs @@ -0,0 +1,38 @@ +defmodule Pleroma.Repo.Migrations.UsersPopulateEmoji do + use Ecto.Migration + + import Ecto.Query + + alias Pleroma.User + alias Pleroma.Repo + + def up do + execute("ALTER TABLE users ALTER COLUMN emoji SET DEFAULT '{}'::jsonb") + execute("UPDATE users SET emoji = DEFAULT WHERE emoji = '[]'::jsonb") + + from(u in User) + |> select([u], struct(u, [:id, :ap_id, :source_data])) + |> Repo.stream() + |> Enum.each(fn user -> + emoji = + user.source_data + |> Map.get("tag", []) + |> Enum.filter(fn + %{"type" => "Emoji"} -> true + _ -> false + end) + |> Enum.reduce(%{}, fn %{"icon" => %{"url" => url}, "name" => name}, acc -> + Map.put(acc, String.trim(name, ":"), url) + end) + + user + |> Ecto.Changeset.cast(%{emoji: emoji}, [:emoji]) + |> Repo.update() + end) + end + + def down do + execute("ALTER TABLE users ALTER COLUMN emoji SET DEFAULT '[]'::jsonb") + execute("UPDATE users SET emoji = DEFAULT WHERE emoji = '{}'::jsonb") + end +end diff --git a/priv/repo/migrations/20200406105422_users_remove_source_data.exs b/priv/repo/migrations/20200406105422_users_remove_source_data.exs new file mode 100644 index 000000000..9812d480f --- /dev/null +++ b/priv/repo/migrations/20200406105422_users_remove_source_data.exs @@ -0,0 +1,15 @@ +defmodule Pleroma.Repo.Migrations.UsersRemoveSourceData do + use Ecto.Migration + + def up do + alter table(:users) do + remove_if_exists(:source_data, :map) + end + end + + def down do + alter table(:users) do + add_if_not_exists(:source_data, :map, default: %{}) + end + end +end diff --git a/priv/repo/migrations/20200415181818_update_markers.exs b/priv/repo/migrations/20200415181818_update_markers.exs new file mode 100644 index 000000000..bb9d8e860 --- /dev/null +++ b/priv/repo/migrations/20200415181818_update_markers.exs @@ -0,0 +1,44 @@ +defmodule Pleroma.Repo.Migrations.UpdateMarkers do + use Ecto.Migration + import Ecto.Query + alias Pleroma.Repo + + def up do + update_markers() + end + + def down do + :ok + end + + defp update_markers do + now = NaiveDateTime.utc_now() + + markers_attrs = + from(q in "notifications", + select: %{ + timeline: "notifications", + user_id: q.user_id, + last_read_id: + type(fragment("MAX( CASE WHEN seen = true THEN id ELSE null END )"), :string) + }, + group_by: [q.user_id] + ) + |> Repo.all() + |> Enum.map(fn %{last_read_id: last_read_id} = attrs -> + attrs + |> Map.put(:last_read_id, last_read_id || "") + |> Map.put_new(:inserted_at, now) + |> Map.put_new(:updated_at, now) + end) + + markers_attrs + |> Enum.chunk_every(1000) + |> Enum.each(fn markers_attrs_chunked -> + Repo.insert_all("markers", markers_attrs_chunked, + on_conflict: {:replace, [:last_read_id]}, + conflict_target: [:user_id, :timeline] + ) + end) + end +end diff --git a/priv/repo/migrations/20200428221338_insert_skeletons_for_deleted_users.exs b/priv/repo/migrations/20200428221338_insert_skeletons_for_deleted_users.exs new file mode 100644 index 000000000..2adc38186 --- /dev/null +++ b/priv/repo/migrations/20200428221338_insert_skeletons_for_deleted_users.exs @@ -0,0 +1,45 @@ +defmodule Pleroma.Repo.Migrations.InsertSkeletonsForDeletedUsers do + use Ecto.Migration + + alias Pleroma.User + alias Pleroma.Repo + + import Ecto.Query + + def change do + Application.ensure_all_started(:flake_id) + + local_ap_id = + User.Query.build(%{local: true}) + |> select([u], u.ap_id) + |> limit(1) + |> Repo.one() + + unless local_ap_id == nil do + # Hack to get instance base url because getting it from Phoenix + # would require starting the whole application + instance_uri = + local_ap_id + |> URI.parse() + |> Map.put(:query, nil) + |> Map.put(:path, nil) + |> URI.to_string() + + {:ok, %{rows: ap_ids}} = + Ecto.Adapters.SQL.query( + Repo, + "select distinct unnest(nonexistent_locals.recipients) from activities, lateral (select array_agg(recipient) as recipients from unnest(activities.recipients) as recipient where recipient similar to '#{ + instance_uri + }/users/[A-Za-z0-9]*' and not(recipient in (select ap_id from users))) nonexistent_locals;", + [], + timeout: :infinity + ) + + ap_ids + |> Enum.each(fn [ap_id] -> + Ecto.Changeset.change(%User{}, deactivated: true, ap_id: ap_id) + |> Repo.insert() + end) + end + end +end diff --git a/priv/repo/migrations/20200505072231_remove_magic_key_field.exs b/priv/repo/migrations/20200505072231_remove_magic_key_field.exs new file mode 100644 index 000000000..2635e671b --- /dev/null +++ b/priv/repo/migrations/20200505072231_remove_magic_key_field.exs @@ -0,0 +1,9 @@ +defmodule Pleroma.Repo.Migrations.RemoveMagicKeyField do + use Ecto.Migration + + def change do + alter table(:users) do + remove(:magic_key, :string) + end + end +end |