From 28d77e373cbaf0908f86973a873c9bfd6c3221cb Mon Sep 17 00:00:00 2001 From: href Date: Wed, 9 Jan 2019 16:08:24 +0100 Subject: Flake Ids for Users and Activities --- ...0181218172826_users_and_activities_flake_id.exs | 52 ++++++++++++++++++++++ 1 file changed, 52 insertions(+) create mode 100644 priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs (limited to 'priv/repo/migrations') diff --git a/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs b/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs new file mode 100644 index 000000000..6e5dfaa77 --- /dev/null +++ b/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs @@ -0,0 +1,52 @@ +defmodule Pleroma.Repo.Migrations.UsersAndActivitiesFlakeId do + use Ecto.Migration + + # This migrates from int serial IDs to custom Flake: + # 1- create a temporary uuid column + # 2- fill this column with compatibility ids (see below) + # 3- remove pkeys constraints + # 4- update relation pkeys with the new ids + # 5- rename the temporary column to id + # 6- re-create the constraints + def change do + # Old serial int ids are transformed to 128bits with extra padding. + # The application (in `Pleroma.FlakeId`) handles theses IDs properly as integers; to keep compatibility + # with previously issued ids. + #execute "update activities set external_id = CAST( LPAD( TO_HEX(id), 32, '0' ) AS uuid);" + #execute "update users set external_id = CAST( LPAD( TO_HEX(id), 32, '0' ) AS uuid);" + + execute "ALTER TABLE activities DROP CONSTRAINT activities_pkey CASCADE;" + execute "ALTER TABLE users DROP CONSTRAINT users_pkey CASCADE;" + + execute "ALTER TABLE activities ALTER COLUMN id DROP default;" + execute "ALTER TABLE users ALTER COLUMN id DROP default;" + + execute "ALTER TABLE activities ALTER COLUMN id SET DATA TYPE uuid USING CAST( LPAD( TO_HEX(id), 32, '0' ) AS uuid);" + execute "ALTER TABLE users ALTER COLUMN id SET DATA TYPE uuid USING CAST( LPAD( TO_HEX(id), 32, '0' ) AS uuid);" + + execute "ALTER TABLE activities ADD PRIMARY KEY (id);" + execute "ALTER TABLE users ADD PRIMARY KEY (id);" + + # Fkeys: + # Activities - Referenced by: + # TABLE "notifications" CONSTRAINT "notifications_activity_id_fkey" FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE + # Users - Referenced by: + # TABLE "filters" CONSTRAINT "filters_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE + # TABLE "lists" CONSTRAINT "lists_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE + # TABLE "notifications" CONSTRAINT "notifications_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE + # TABLE "oauth_authorizations" CONSTRAINT "oauth_authorizations_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) + # TABLE "oauth_tokens" CONSTRAINT "oauth_tokens_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) + # TABLE "password_reset_tokens" CONSTRAINT "password_reset_tokens_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) + # TABLE "push_subscriptions" CONSTRAINT "push_subscriptions_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE + # TABLE "websub_client_subscriptions" CONSTRAINT "websub_client_subscriptions_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) + + execute "ALTER TABLE notifications ALTER COLUMN activity_id SET DATA TYPE uuid USING CAST( LPAD( TO_HEX(activity_id), 32, '0' ) AS uuid);" + execute "ALTER TABLE notifications ADD CONSTRAINT notifications_activity_id_fkey FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE;" + + for table <- ~w(notifications filters lists oauth_authorizations oauth_tokens password_reset_tokens push_subscriptions websub_client_subscriptions) do + execute "ALTER TABLE #{table} ALTER COLUMN user_id SET DATA TYPE uuid USING CAST( LPAD( TO_HEX(user_id), 32, '0' ) AS uuid);" + execute "ALTER TABLE #{table} ADD CONSTRAINT #{table}_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;" + end + + end +end -- cgit v1.2.3 From 465fb4327dab5eccdb8b88d6da5670007bd7110e Mon Sep 17 00:00:00 2001 From: href Date: Mon, 21 Jan 2019 13:10:48 +0100 Subject: Lock activities/users table during flake migration. --- priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'priv/repo/migrations') diff --git a/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs b/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs index 6e5dfaa77..39d45f7e8 100644 --- a/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs +++ b/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs @@ -15,6 +15,10 @@ defmodule Pleroma.Repo.Migrations.UsersAndActivitiesFlakeId do #execute "update activities set external_id = CAST( LPAD( TO_HEX(id), 32, '0' ) AS uuid);" #execute "update users set external_id = CAST( LPAD( TO_HEX(id), 32, '0' ) AS uuid);" + # Lock both tables to avoid a running server to meddling with our transaction + execute "LOCK TABLE activities;" + execute "LOCK TABLE users;" + execute "ALTER TABLE activities DROP CONSTRAINT activities_pkey CASCADE;" execute "ALTER TABLE users DROP CONSTRAINT users_pkey CASCADE;" -- cgit v1.2.3 From a92c43bc4be914ac9f8118cae18bc82e2b9d1664 Mon Sep 17 00:00:00 2001 From: href Date: Wed, 23 Jan 2019 11:21:52 +0100 Subject: Clippy! --- ...0181218172826_users_and_activities_flake_id.exs | 55 ++++++++++++++++++++++ 1 file changed, 55 insertions(+) (limited to 'priv/repo/migrations') diff --git a/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs b/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs index 39d45f7e8..69a27e0c8 100644 --- a/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs +++ b/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs @@ -1,5 +1,9 @@ defmodule Pleroma.Repo.Migrations.UsersAndActivitiesFlakeId do use Ecto.Migration + alias Pleroma.Clippy + require Integer + import Ecto.Query + alias Pleroma.Repo # This migrates from int serial IDs to custom Flake: # 1- create a temporary uuid column @@ -15,6 +19,8 @@ defmodule Pleroma.Repo.Migrations.UsersAndActivitiesFlakeId do #execute "update activities set external_id = CAST( LPAD( TO_HEX(id), 32, '0' ) AS uuid);" #execute "update users set external_id = CAST( LPAD( TO_HEX(id), 32, '0' ) AS uuid);" + clippy = start_clippy_heartbeats() + # Lock both tables to avoid a running server to meddling with our transaction execute "LOCK TABLE activities;" execute "LOCK TABLE users;" @@ -52,5 +58,54 @@ defmodule Pleroma.Repo.Migrations.UsersAndActivitiesFlakeId do execute "ALTER TABLE #{table} ADD CONSTRAINT #{table}_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;" end + stop_clippy_heartbeats(clippy) end + + defp start_clippy_heartbeats() do + count = from(a in "activities", select: count(a.id)) |> Repo.one! + + pid = if count > 5000 do + heartbeat_interval = :timer.minutes(2) + :timer.seconds(30) + all_tips = Clippy.tips() ++ [ + "The migration is still running, maybe it's time for another tea?", + "Happy rabbits practice a cute behavior known as a\n“binky:” they jump up in the air\nand twist\nand spin around!", + "Nothing and everything.\n\nI still work.", + "Pleroma runs on a Raspberry Pi!\n\n … but this migration will take forever if you\nactually run on a raspberry pi", + "Status? Stati? Post? Note? Toot?\nRepeat? Reboost? Boost? Retweet? Retoot??\n\nI-I'm confused.", + ] + + heartbeat = fn(heartbeat, runs, all_tips, tips) -> + tips = if Integer.is_even(runs) do + tips = if tips == [], do: all_tips, else: tips + [tip | tips] = Enum.shuffle(tips) + Clippy.puts(tip) + tips + else + IO.puts "\n -- #{DateTime.to_string(DateTime.utc_now())} Migration still running, please wait…\n" + tips + end + :timer.sleep(heartbeat_interval) + heartbeat.(heartbeat, runs + 1, all_tips, tips) + end + + Clippy.puts [ + [:red, :bright, "It looks like you are running an older instance!"], + [""], + [:bright, "This migration may take a long time", :reset, " -- so you probably should"], + ["go drink a coffee, or a tea, or a beer, a whiskey, a vodka,"], + ["while it runs to deal with your temporary fediverse pause!"] + ] + :timer.sleep(heartbeat_interval) + spawn_link(fn() -> heartbeat.(heartbeat, 1, all_tips, []) end) + end + end + + defp stop_clippy_heartbeats(pid) do + if pid do + Process.unlink(pid) + Process.exit(pid, :kill) + Clippy.puts [[:green, :bright, "Hurray!!", "", "", "Migration completed!"]] + end + end + end -- cgit v1.2.3 From e67bd93d9036770359da731b1ddad6f3da4fef66 Mon Sep 17 00:00:00 2001 From: href Date: Wed, 23 Jan 2019 11:22:31 +0100 Subject: Flake: migrate pinned_activities in jsonb --- priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs | 2 ++ 1 file changed, 2 insertions(+) (limited to 'priv/repo/migrations') diff --git a/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs b/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs index 69a27e0c8..70ec58cd2 100644 --- a/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs +++ b/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs @@ -37,6 +37,8 @@ defmodule Pleroma.Repo.Migrations.UsersAndActivitiesFlakeId do execute "ALTER TABLE activities ADD PRIMARY KEY (id);" execute "ALTER TABLE users ADD PRIMARY KEY (id);" + execute "UPDATE users SET info = jsonb_set(info, '{pinned_activities}', array_to_json(ARRAY(select jsonb_array_elements_text(info->'pinned_activities')))::jsonb);" + # Fkeys: # Activities - Referenced by: # TABLE "notifications" CONSTRAINT "notifications_activity_id_fkey" FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE -- cgit v1.2.3 From 9ba6e1fabed3a583ba09c730efb144939a3a15a8 Mon Sep 17 00:00:00 2001 From: href Date: Wed, 23 Jan 2019 12:09:21 +0100 Subject: FlakeId: chain alter statements --- ...0181218172826_users_and_activities_flake_id.exs | 40 ++++++++++++++-------- 1 file changed, 25 insertions(+), 15 deletions(-) (limited to 'priv/repo/migrations') diff --git a/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs b/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs index 70ec58cd2..848d507e5 100644 --- a/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs +++ b/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs @@ -25,17 +25,21 @@ defmodule Pleroma.Repo.Migrations.UsersAndActivitiesFlakeId do execute "LOCK TABLE activities;" execute "LOCK TABLE users;" - execute "ALTER TABLE activities DROP CONSTRAINT activities_pkey CASCADE;" - execute "ALTER TABLE users DROP CONSTRAINT users_pkey CASCADE;" - - execute "ALTER TABLE activities ALTER COLUMN id DROP default;" - execute "ALTER TABLE users ALTER COLUMN id DROP default;" - - execute "ALTER TABLE activities ALTER COLUMN id SET DATA TYPE uuid USING CAST( LPAD( TO_HEX(id), 32, '0' ) AS uuid);" - execute "ALTER TABLE users ALTER COLUMN id SET DATA TYPE uuid USING CAST( LPAD( TO_HEX(id), 32, '0' ) AS uuid);" - - execute "ALTER TABLE activities ADD PRIMARY KEY (id);" - execute "ALTER TABLE users ADD PRIMARY KEY (id);" + execute """ + ALTER TABLE activities + DROP CONSTRAINT activities_pkey CASCADE, + ALTER COLUMN id DROP default, + ALTER COLUMN id SET DATA TYPE uuid USING CAST( LPAD( TO_HEX(id), 32, '0' ) AS uuid), + ADD PRIMARY KEY (id); + """ + + execute """ + ALTER TABLE users + DROP CONSTRAINT users_pkey CASCADE, + ALTER COLUMN id DROP default, + ALTER COLUMN id SET DATA TYPE uuid USING CAST( LPAD( TO_HEX(id), 32, '0' ) AS uuid), + ADD PRIMARY KEY (id); + """ execute "UPDATE users SET info = jsonb_set(info, '{pinned_activities}', array_to_json(ARRAY(select jsonb_array_elements_text(info->'pinned_activities')))::jsonb);" @@ -52,12 +56,18 @@ defmodule Pleroma.Repo.Migrations.UsersAndActivitiesFlakeId do # TABLE "push_subscriptions" CONSTRAINT "push_subscriptions_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE # TABLE "websub_client_subscriptions" CONSTRAINT "websub_client_subscriptions_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) - execute "ALTER TABLE notifications ALTER COLUMN activity_id SET DATA TYPE uuid USING CAST( LPAD( TO_HEX(activity_id), 32, '0' ) AS uuid);" - execute "ALTER TABLE notifications ADD CONSTRAINT notifications_activity_id_fkey FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE;" + execute """ + ALTER TABLE notifications + ALTER COLUMN activity_id SET DATA TYPE uuid USING CAST( LPAD( TO_HEX(activity_id), 32, '0' ) AS uuid), + ADD CONSTRAINT notifications_activity_id_fkey FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE; + """ for table <- ~w(notifications filters lists oauth_authorizations oauth_tokens password_reset_tokens push_subscriptions websub_client_subscriptions) do - execute "ALTER TABLE #{table} ALTER COLUMN user_id SET DATA TYPE uuid USING CAST( LPAD( TO_HEX(user_id), 32, '0' ) AS uuid);" - execute "ALTER TABLE #{table} ADD CONSTRAINT #{table}_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;" + execute """ + ALTER TABLE #{table} + ALTER COLUMN user_id SET DATA TYPE uuid USING CAST( LPAD( TO_HEX(user_id), 32, '0' ) AS uuid), + ADD CONSTRAINT #{table}_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; + """ end stop_clippy_heartbeats(clippy) -- cgit v1.2.3 From a3ba72d97849ba12a5ea4008bd0b97e80bdd588b Mon Sep 17 00:00:00 2001 From: href Date: Thu, 24 Jan 2019 16:15:13 +0100 Subject: Fix clippy with one/five lines --- .../migrations/20181218172826_users_and_activities_flake_id.exs | 8 +++++--- 1 file changed, 5 insertions(+), 3 deletions(-) (limited to 'priv/repo/migrations') diff --git a/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs b/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs index 848d507e5..47d2d02da 100644 --- a/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs +++ b/priv/repo/migrations/20181218172826_users_and_activities_flake_id.exs @@ -70,16 +70,18 @@ defmodule Pleroma.Repo.Migrations.UsersAndActivitiesFlakeId do """ end + flush() + stop_clippy_heartbeats(clippy) end defp start_clippy_heartbeats() do count = from(a in "activities", select: count(a.id)) |> Repo.one! - pid = if count > 5000 do + if count > 5000 do heartbeat_interval = :timer.minutes(2) + :timer.seconds(30) all_tips = Clippy.tips() ++ [ - "The migration is still running, maybe it's time for another tea?", + "The migration is still running, maybe it's time for another “tea”?", "Happy rabbits practice a cute behavior known as a\n“binky:” they jump up in the air\nand twist\nand spin around!", "Nothing and everything.\n\nI still work.", "Pleroma runs on a Raspberry Pi!\n\n … but this migration will take forever if you\nactually run on a raspberry pi", @@ -104,7 +106,7 @@ defmodule Pleroma.Repo.Migrations.UsersAndActivitiesFlakeId do [:red, :bright, "It looks like you are running an older instance!"], [""], [:bright, "This migration may take a long time", :reset, " -- so you probably should"], - ["go drink a coffee, or a tea, or a beer, a whiskey, a vodka,"], + ["go drink a cofe, or a tea, or a beer, a whiskey, a vodka,"], ["while it runs to deal with your temporary fediverse pause!"] ] :timer.sleep(heartbeat_interval) -- cgit v1.2.3