diff options
| -rw-r--r-- | app/models/account_domain_block.rb | 4 | ||||
| -rw-r--r-- | app/models/block.rb | 6 | ||||
| -rw-r--r-- | app/models/conversation_mute.rb | 4 | ||||
| -rw-r--r-- | app/models/domain_block.rb | 2 | ||||
| -rw-r--r-- | app/models/favourite.rb | 6 | ||||
| -rw-r--r-- | app/models/follow.rb | 6 | ||||
| -rw-r--r-- | app/models/follow_request.rb | 6 | ||||
| -rw-r--r-- | app/models/import.rb | 4 | ||||
| -rw-r--r-- | app/models/mention.rb | 4 | ||||
| -rw-r--r-- | app/models/mute.rb | 6 | ||||
| -rw-r--r-- | app/models/report.rb | 6 | ||||
| -rw-r--r-- | app/models/setting.rb | 4 | ||||
| -rw-r--r-- | app/models/stream_entry.rb | 5 | ||||
| -rw-r--r-- | app/models/subscription.rb | 4 | ||||
| -rw-r--r-- | app/models/web/setting.rb | 4 | ||||
| -rw-r--r-- | db/migrate/20170918125918_ids_to_bigints.rb | 232 | ||||
| -rw-r--r-- | db/schema.rb | 134 | ||||
| -rw-r--r-- | lib/mastodon/migration_helpers.rb | 988 |
18 files changed, 1202 insertions, 223 deletions
diff --git a/app/models/account_domain_block.rb b/app/models/account_domain_block.rb index bdd64c01a..fb695e473 100644 --- a/app/models/account_domain_block.rb +++ b/app/models/account_domain_block.rb @@ -3,11 +3,11 @@ # # Table name: account_domain_blocks # -# id :integer not null, primary key -# account_id :integer # domain :string # created_at :datetime not null # updated_at :datetime not null +# account_id :integer +# id :integer not null, primary key # class AccountDomainBlock < ApplicationRecord diff --git a/app/models/block.rb b/app/models/block.rb index edb0d2d11..a913782ed 100644 --- a/app/models/block.rb +++ b/app/models/block.rb @@ -3,11 +3,11 @@ # # Table name: blocks # -# id :integer not null, primary key -# account_id :integer not null -# target_account_id :integer not null # created_at :datetime not null # updated_at :datetime not null +# account_id :integer not null +# id :integer not null, primary key +# target_account_id :integer not null # class Block < ApplicationRecord diff --git a/app/models/conversation_mute.rb b/app/models/conversation_mute.rb index 79299b995..8d2399adf 100644 --- a/app/models/conversation_mute.rb +++ b/app/models/conversation_mute.rb @@ -3,9 +3,9 @@ # # Table name: conversation_mutes # -# id :integer not null, primary key -# account_id :integer not null # conversation_id :integer not null +# account_id :integer not null +# id :integer not null, primary key # class ConversationMute < ApplicationRecord diff --git a/app/models/domain_block.rb b/app/models/domain_block.rb index aea8919af..1268290bc 100644 --- a/app/models/domain_block.rb +++ b/app/models/domain_block.rb @@ -3,12 +3,12 @@ # # Table name: domain_blocks # -# id :integer not null, primary key # domain :string default(""), not null # created_at :datetime not null # updated_at :datetime not null # severity :integer default("silence") # reject_media :boolean default(FALSE), not null +# id :integer not null, primary key # class DomainBlock < ApplicationRecord diff --git a/app/models/favourite.rb b/app/models/favourite.rb index 53c79ccea..d28d5c05b 100644 --- a/app/models/favourite.rb +++ b/app/models/favourite.rb @@ -3,11 +3,11 @@ # # Table name: favourites # -# id :integer not null, primary key -# account_id :integer not null -# status_id :integer not null # created_at :datetime not null # updated_at :datetime not null +# account_id :integer not null +# id :integer not null, primary key +# status_id :integer not null # class Favourite < ApplicationRecord diff --git a/app/models/follow.rb b/app/models/follow.rb index 62f6fb670..667720a88 100644 --- a/app/models/follow.rb +++ b/app/models/follow.rb @@ -3,11 +3,11 @@ # # Table name: follows # -# id :integer not null, primary key -# account_id :integer not null -# target_account_id :integer not null # created_at :datetime not null # updated_at :datetime not null +# account_id :integer not null +# id :integer not null, primary key +# target_account_id :integer not null # class Follow < ApplicationRecord diff --git a/app/models/follow_request.rb b/app/models/follow_request.rb index 458c3a2cd..60036d903 100644 --- a/app/models/follow_request.rb +++ b/app/models/follow_request.rb @@ -3,11 +3,11 @@ # # Table name: follow_requests # -# id :integer not null, primary key -# account_id :integer not null -# target_account_id :integer not null # created_at :datetime not null # updated_at :datetime not null +# account_id :integer not null +# id :integer not null, primary key +# target_account_id :integer not null # class FollowRequest < ApplicationRecord diff --git a/app/models/import.rb b/app/models/import.rb index 4656c3af6..8ae7e3a46 100644 --- a/app/models/import.rb +++ b/app/models/import.rb @@ -3,8 +3,6 @@ # # Table name: imports # -# id :integer not null, primary key -# account_id :integer not null # type :integer not null # approved :boolean default(FALSE), not null # created_at :datetime not null @@ -13,6 +11,8 @@ # data_content_type :string # data_file_size :integer # data_updated_at :datetime +# account_id :integer not null +# id :integer not null, primary key # class Import < ApplicationRecord diff --git a/app/models/mention.rb b/app/models/mention.rb index 7450b1b85..3700c781c 100644 --- a/app/models/mention.rb +++ b/app/models/mention.rb @@ -3,11 +3,11 @@ # # Table name: mentions # -# id :integer not null, primary key -# account_id :integer # status_id :integer # created_at :datetime not null # updated_at :datetime not null +# account_id :integer +# id :integer not null, primary key # class Mention < ApplicationRecord diff --git a/app/models/mute.rb b/app/models/mute.rb index 00e5661a7..6e64848c7 100644 --- a/app/models/mute.rb +++ b/app/models/mute.rb @@ -3,11 +3,11 @@ # # Table name: mutes # -# id :integer not null, primary key -# account_id :integer not null -# target_account_id :integer not null # created_at :datetime not null # updated_at :datetime not null +# account_id :integer not null +# id :integer not null, primary key +# target_account_id :integer not null # class Mute < ApplicationRecord diff --git a/app/models/report.rb b/app/models/report.rb index 479aa17bb..bffb42b48 100644 --- a/app/models/report.rb +++ b/app/models/report.rb @@ -3,15 +3,15 @@ # # Table name: reports # -# id :integer not null, primary key -# account_id :integer not null -# target_account_id :integer not null # status_ids :integer default([]), not null, is an Array # comment :text default(""), not null # action_taken :boolean default(FALSE), not null # created_at :datetime not null # updated_at :datetime not null +# account_id :integer not null # action_taken_by_account_id :integer +# id :integer not null, primary key +# target_account_id :integer not null # class Report < ApplicationRecord diff --git a/app/models/setting.rb b/app/models/setting.rb index 340552581..a14f156a1 100644 --- a/app/models/setting.rb +++ b/app/models/setting.rb @@ -3,13 +3,13 @@ # # Table name: settings # -# id :integer not null, primary key # var :string not null # value :text # thing_type :string -# thing_id :integer # created_at :datetime # updated_at :datetime +# id :integer not null, primary key +# thing_id :integer # class Setting < RailsSettings::Base diff --git a/app/models/stream_entry.rb b/app/models/stream_entry.rb index 44aac39b3..b51fe9ad7 100644 --- a/app/models/stream_entry.rb +++ b/app/models/stream_entry.rb @@ -1,16 +1,15 @@ # frozen_string_literal: true - # == Schema Information # # Table name: stream_entries # -# id :integer not null, primary key -# account_id :integer # activity_id :integer # activity_type :string # created_at :datetime not null # updated_at :datetime not null # hidden :boolean default(FALSE), not null +# account_id :integer +# id :integer not null, primary key # class StreamEntry < ApplicationRecord diff --git a/app/models/subscription.rb b/app/models/subscription.rb index 14f1a140c..39860196b 100644 --- a/app/models/subscription.rb +++ b/app/models/subscription.rb @@ -3,16 +3,16 @@ # # Table name: subscriptions # -# id :integer not null, primary key # callback_url :string default(""), not null # secret :string # expires_at :datetime # confirmed :boolean default(FALSE), not null -# account_id :integer not null # created_at :datetime not null # updated_at :datetime not null # last_successful_delivery_at :datetime # domain :string +# account_id :integer not null +# id :integer not null, primary key # class Subscription < ApplicationRecord diff --git a/app/models/web/setting.rb b/app/models/web/setting.rb index 04a049523..1b0bfb2b7 100644 --- a/app/models/web/setting.rb +++ b/app/models/web/setting.rb @@ -3,11 +3,11 @@ # # Table name: web_settings # -# id :integer not null, primary key -# user_id :integer # data :json # created_at :datetime not null # updated_at :datetime not null +# id :integer not null, primary key +# user_id :integer # class Web::Setting < ApplicationRecord diff --git a/db/migrate/20170918125918_ids_to_bigints.rb b/db/migrate/20170918125918_ids_to_bigints.rb index 7483dd77a..c6feed8f9 100644 --- a/db/migrate/20170918125918_ids_to_bigints.rb +++ b/db/migrate/20170918125918_ids_to_bigints.rb @@ -1,127 +1,119 @@ +require Rails.root.join('lib', 'mastodon', 'migration_helpers') + class IdsToBigints < ActiveRecord::Migration[5.1] + include Mastodon::MigrationHelpers + + disable_ddl_transaction! + + INCLUDED_COLUMNS = [ + [:account_domain_blocks, :account_id], + [:account_domain_blocks, :id], + [:accounts, :id], + [:blocks, :account_id], + [:blocks, :id], + [:blocks, :target_account_id], + [:conversation_mutes, :account_id], + [:conversation_mutes, :id], + [:domain_blocks, :id], + [:favourites, :account_id], + [:favourites, :id], + [:favourites, :status_id], + [:follow_requests, :account_id], + [:follow_requests, :id], + [:follow_requests, :target_account_id], + [:follows, :account_id], + [:follows, :id], + [:follows, :target_account_id], + [:imports, :account_id], + [:imports, :id], + [:media_attachments, :account_id], + [:media_attachments, :id], + [:mentions, :account_id], + [:mentions, :id], + [:mutes, :account_id], + [:mutes, :id], + [:mutes, :target_account_id], + [:notifications, :account_id], + [:notifications, :from_account_id], + [:notifications, :id], + [:oauth_access_grants, :application_id], + [:oauth_access_grants, :id], + [:oauth_access_grants, :resource_owner_id], + [:oauth_access_tokens, :application_id], + [:oauth_access_tokens, :id], + [:oauth_access_tokens, :resource_owner_id], + [:oauth_applications, :id], + [:oauth_applications, :owner_id], + [:reports, :account_id], + [:reports, :action_taken_by_account_id], + [:reports, :id], + [:reports, :target_account_id], + [:session_activations, :access_token_id], + [:session_activations, :user_id], + [:session_activations, :web_push_subscription_id], + [:settings, :id], + [:settings, :thing_id], + [:statuses, :account_id], + [:statuses, :application_id], + [:statuses, :in_reply_to_account_id], + [:stream_entries, :account_id], + [:stream_entries, :id], + [:subscriptions, :account_id], + [:subscriptions, :id], + [:tags, :id], + [:users, :account_id], + [:users, :id], + [:web_settings, :id], + [:web_settings, :user_id], + ] + INCLUDED_COLUMNS << [:deprecated_preview_cards, :id] if table_exists?(:deprecated_preview_cards) + + def migrate_columns(to_type) + # Print out a warning that this will probably take a while. + say '' + say 'WARNING: This migration may take a *long* time for large instances' + say 'It will *not* lock tables for any significant time, but it may run' + say 'for a very long time. We will pause for 10 seconds to allow you to' + say 'interrupt this migration if you are not ready.' + say '' + say 'This migration has some sections that can be safely interrupted' + say 'and restarted later, and will tell you when those are occurring.' + say '' + say 'For more information, see https://github.com/tootsuite/mastodon/pull/5088' + + 10.downto(1) do |i| + say "Continuing in #{i} second#{i == 1 ? '' : 's'}...", true + sleep 1 + end + + tables = INCLUDED_COLUMNS.map(&:first).uniq + table_sizes = {} + + # Sort tables by their size + tables.each do |table| + table_sizes[table] = estimate_rows_in_table(table) + end + + ordered_columns = INCLUDED_COLUMNS.sort_by do |col_parts| + [-table_sizes[col_parts.first], col_parts.last] + end + + ordered_columns.each do |column_parts| + table, column = column_parts + + # Skip this if we're resuming and already did this one. + next if column_for(table, column).sql_type == to_type.to_s + + change_column_type_concurrently table, column, to_type + cleanup_concurrent_column_type_change table, column + end + end + def up - change_column :account_domain_blocks, :account_id, :bigint - change_column :account_domain_blocks, :id, :bigint - change_column :accounts, :id, :bigint - change_column :blocks, :account_id, :bigint - change_column :blocks, :id, :bigint - change_column :blocks, :target_account_id, :bigint - change_column :conversation_mutes, :account_id, :bigint - change_column :conversation_mutes, :id, :bigint - change_column :deprecated_preview_cards, :id, :bigint if table_exists?(:deprecated_preview_cards) - change_column :domain_blocks, :id, :bigint - change_column :favourites, :account_id, :bigint - change_column :favourites, :id, :bigint - change_column :favourites, :status_id, :bigint - change_column :follow_requests, :account_id, :bigint - change_column :follow_requests, :id, :bigint - change_column :follow_requests, :target_account_id, :bigint - change_column :follows, :account_id, :bigint - change_column :follows, :id, :bigint - change_column :follows, :target_account_id, :bigint - change_column :imports, :account_id, :bigint - change_column :imports, :id, :bigint - change_column :media_attachments, :account_id, :bigint - change_column :media_attachments, :id, :bigint - change_column :mentions, :account_id, :bigint - change_column :mentions, :id, :bigint - change_column :mutes, :account_id, :bigint - change_column :mutes, :id, :bigint - change_column :mutes, :target_account_id, :bigint - change_column :notifications, :account_id, :bigint - change_column :notifications, :from_account_id, :bigint - change_column :notifications, :id, :bigint - change_column :oauth_access_grants, :application_id, :bigint - change_column :oauth_access_grants, :id, :bigint - change_column :oauth_access_grants, :resource_owner_id, :bigint - change_column :oauth_access_tokens, :application_id, :bigint - change_column :oauth_access_tokens, :id, :bigint - change_column :oauth_access_tokens, :resource_owner_id, :bigint - change_column :oauth_applications, :id, :bigint - change_column :oauth_applications, :owner_id, :bigint - change_column :reports, :account_id, :bigint - change_column :reports, :action_taken_by_account_id, :bigint - change_column :reports, :id, :bigint - change_column :reports, :target_account_id, :bigint - change_column :session_activations, :access_token_id, :bigint - change_column :session_activations, :user_id, :bigint - change_column :session_activations, :web_push_subscription_id, :bigint - change_column :settings, :id, :bigint - change_column :settings, :thing_id, :bigint - change_column :statuses, :account_id, :bigint - change_column :statuses, :application_id, :bigint - change_column :statuses, :in_reply_to_account_id, :bigint - change_column :stream_entries, :account_id, :bigint - change_column :stream_entries, :id, :bigint - change_column :subscriptions, :account_id, :bigint - change_column :subscriptions, :id, :bigint - change_column :tags, :id, :bigint - change_column :users, :account_id, :bigint - change_column :users, :id, :bigint - change_column :web_settings, :id, :bigint - change_column :web_settings, :user_id, :bigint + migrate_columns(:bigint) end def down - change_column :account_domain_blocks, :account_id, :integer - change_column :account_domain_blocks, :id, :integer - change_column :accounts, :id, :integer - change_column :blocks, :account_id, :integer - change_column :blocks, :id, :integer - change_column :blocks, :target_account_id, :integer - change_column :conversation_mutes, :account_id, :integer - change_column :conversation_mutes, :id, :integer - change_column :deprecated_preview_cards, :id, :integer if table_exists?(:deprecated_preview_cards) - change_column :domain_blocks, :id, :integer - change_column :favourites, :account_id, :integer - change_column :favourites, :id, :integer - change_column :favourites, :status_id, :integer - change_column :follow_requests, :account_id, :integer - change_column :follow_requests, :id, :integer - change_column :follow_requests, :target_account_id, :integer - change_column :follows, :account_id, :integer - change_column :follows, :id, :integer - change_column :follows, :target_account_id, :integer - change_column :imports, :account_id, :integer - change_column :imports, :id, :integer - change_column :media_attachments, :account_id, :integer - change_column :media_attachments, :id, :integer - change_column :mentions, :account_id, :integer - change_column :mentions, :id, :integer - change_column :mutes, :account_id, :integer - change_column :mutes, :id, :integer - change_column :mutes, :target_account_id, :integer - change_column :notifications, :account_id, :integer - change_column :notifications, :from_account_id, :integer - change_column :notifications, :id, :integer - change_column :oauth_access_grants, :application_id, :integer - change_column :oauth_access_grants, :id, :integer - change_column :oauth_access_grants, :resource_owner_id, :integer - change_column :oauth_access_tokens, :application_id, :integer - change_column :oauth_access_tokens, :id, :integer - change_column :oauth_access_tokens, :resource_owner_id, :integer - change_column :oauth_applications, :id, :integer - change_column :oauth_applications, :owner_id, :integer - change_column :reports, :account_id, :integer - change_column :reports, :action_taken_by_account_id, :integer - change_column :reports, :id, :integer - change_column :reports, :target_account_id, :integer - change_column :session_activations, :access_token_id, :integer - change_column :session_activations, :user_id, :integer - change_column :session_activations, :web_push_subscription_id, :integer - change_column :settings, :id, :integer - change_column :settings, :thing_id, :integer - change_column :statuses, :account_id, :integer - change_column :statuses, :application_id, :integer - change_column :statuses, :in_reply_to_account_id, :integer - change_column :stream_entries, :account_id, :integer - change_column :stream_entries, :id, :integer - change_column :subscriptions, :account_id, :integer - change_column :subscriptions, :id, :integer - change_column :tags, :id, :integer - change_column :users, :account_id, :integer - change_column :users, :id, :integer - change_column :web_settings, :id, :integer - change_column :web_settings, :user_id, :integer + migrate_columns(:integer) end end diff --git a/db/schema.rb b/db/schema.rb index 90f8a5683..2cb105553 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -16,10 +16,10 @@ ActiveRecord::Schema.define(version: 20170927215609) do enable_extension "plpgsql" create_table "account_domain_blocks", force: :cascade do |t| - t.bigint "account_id" t.string "domain" t.datetime "created_at", null: false t.datetime "updated_at", null: false + t.bigint "account_id" t.index ["account_id", "domain"], name: "index_account_domain_blocks_on_account_id_and_domain", unique: true end @@ -69,16 +69,16 @@ ActiveRecord::Schema.define(version: 20170927215609) do end create_table "blocks", force: :cascade do |t| - t.bigint "account_id", null: false - t.bigint "target_account_id", null: false t.datetime "created_at", null: false t.datetime "updated_at", null: false + t.bigint "account_id", null: false + t.bigint "target_account_id", null: false t.index ["account_id", "target_account_id"], name: "index_blocks_on_account_id_and_target_account_id", unique: true end create_table "conversation_mutes", force: :cascade do |t| - t.bigint "account_id", null: false t.bigint "conversation_id", null: false + t.bigint "account_id", null: false t.index ["account_id", "conversation_id"], name: "index_conversation_mutes_on_account_id_and_conversation_id", unique: true end @@ -111,33 +111,32 @@ ActiveRecord::Schema.define(version: 20170927215609) do end create_table "favourites", force: :cascade do |t| - t.bigint "account_id", null: false - t.bigint "status_id", null: false t.datetime "created_at", null: false t.datetime "updated_at", null: false + t.bigint "account_id", null: false + t.bigint "status_id", null: false t.index ["account_id", "id"], name: "index_favourites_on_account_id_and_id" t.index ["account_id", "status_id"], name: "index_favourites_on_account_id_and_status_id", unique: true t.index ["status_id"], name: "index_favourites_on_status_id" end create_table "follow_requests", force: :cascade do |t| - t.bigint "account_id", null: false - t.bigint "target_account_id", null: false t.datetime "created_at", null: false t.datetime "updated_at", null: false + t.bigint "account_id", null: false + t.bigint "target_account_id", null: false t.index ["account_id", "target_account_id"], name: "index_follow_requests_on_account_id_and_target_account_id", unique: true end create_table "follows", force: :cascade do |t| - t.bigint "account_id", null: false - t.bigint "target_account_id", null: false t.datetime "created_at", null: false t.datetime "updated_at", null: false + t.bigint "account_id", null: false + t.bigint "target_account_id", null: false t.index ["account_id", "target_account_id"], name: "index_follows_on_account_id_and_target_account_id", unique: true end create_table "imports", force: :cascade do |t| - t.bigint "account_id", null: false t.integer "type", null: false t.boolean "approved", default: false, null: false t.datetime "created_at", null: false @@ -146,6 +145,7 @@ ActiveRecord::Schema.define(version: 20170927215609) do t.string "data_content_type" t.integer "data_file_size" t.datetime "data_updated_at" + t.bigint "account_id", null: false end create_table "media_attachments", force: :cascade do |t| @@ -155,12 +155,12 @@ ActiveRecord::Schema.define(version: 20170927215609) do t.integer "file_file_size" t.datetime "file_updated_at" t.string "remote_url", default: "", null: false - t.bigint "account_id" t.datetime "created_at", null: false t.datetime "updated_at", null: false t.string "shortcode" t.integer "type", default: 0, null: false t.json "file_meta" + t.bigint "account_id" t.text "description" t.index ["account_id"], name: "index_media_attachments_on_account_id" t.index ["shortcode"], name: "index_media_attachments_on_shortcode", unique: true @@ -168,28 +168,28 @@ ActiveRecord::Schema.define(version: 20170927215609) do end create_table "mentions", force: :cascade do |t| - t.bigint "account_id" t.bigint "status_id" t.datetime "created_at", null: false t.datetime "updated_at", null: false + t.bigint "account_id" t.index ["account_id", "status_id"], name: "index_mentions_on_account_id_and_status_id", unique: true t.index ["status_id"], name: "index_mentions_on_status_id" end create_table "mutes", force: :cascade do |t| - t.bigint "account_id", null: false - t.bigint "target_account_id", null: false t.datetime "created_at", null: false t.datetime "updated_at", null: false + t.bigint "account_id", null: false + t.bigint "target_account_id", null: false t.index ["account_id", "target_account_id"], name: "index_mutes_on_account_id_and_target_account_id", unique: true end create_table "notifications", force: :cascade do |t| - t.bigint "account_id" t.bigint "activity_id" t.string "activity_type" t.datetime "created_at", null: false t.datetime "updated_at", null: false + t.bigint "account_id" t.bigint "from_account_id" t.index ["account_id", "activity_id", "activity_type"], name: "account_activity", unique: true t.index ["activity_id", "activity_type"], name: "index_notifications_on_activity_id_and_activity_type" @@ -197,26 +197,26 @@ ActiveRecord::Schema.define(version: 20170927215609) do end create_table "oauth_access_grants", force: :cascade do |t| - t.bigint "resource_owner_id", null: false - t.bigint "application_id", null: false t.string "token", null: false t.integer "expires_in", null: false t.text "redirect_uri", null: false t.datetime "created_at", null: false t.datetime "revoked_at" t.string "scopes" + t.bigint "application_id", null: false + t.bigint "resource_owner_id", null: false t.index ["token"], name: "index_oauth_access_grants_on_token", unique: true end create_table "oauth_access_tokens", force: :cascade do |t| - t.bigint "resource_owner_id" - t.bigint "application_id" t.string "token", null: false t.string "refresh_token" t.integer "expires_in" t.datetime "revoked_at" t.datetime "created_at", null: false t.string "scopes" + t.bigint "application_id" + t.bigint "resource_owner_id" t.index ["refresh_token"], name: "index_oauth_access_tokens_on_refresh_token", unique: true t.index ["resource_owner_id"], name: "index_oauth_access_tokens_on_resource_owner_id" t.index ["token"], name: "index_oauth_access_tokens_on_token", unique: true @@ -232,8 +232,8 @@ ActiveRecord::Schema.define(version: 20170927215609) do t.datetime "updated_at" t.boolean "superapp", default: false, null: false t.string "website" - t.bigint "owner_id" t.string "owner_type" + t.bigint "owner_id" t.index ["owner_id", "owner_type"], name: "index_oauth_applications_on_owner_id_and_owner_type" t.index ["uid"], name: "index_oauth_applications_on_uid", unique: true end @@ -266,26 +266,26 @@ ActiveRecord::Schema.define(version: 20170927215609) do end create_table "reports", force: :cascade do |t| - t.bigint "account_id", null: false - t.bigint "target_account_id", null: false t.bigint "status_ids", default: [], null: false, array: true t.text "comment", default: "", null: false t.boolean "action_taken", default: false, null: false t.datetime "created_at", null: false t.datetime "updated_at", null: false + t.bigint "account_id", null: false t.bigint "action_taken_by_account_id" + t.bigint "target_account_id", null: false t.index ["account_id"], name: "index_reports_on_account_id" t.index ["target_account_id"], name: "index_reports_on_target_account_id" end create_table "session_activations", force: :cascade do |t| - t.bigint "user_id", null: false t.string "session_id", null: false t.datetime "created_at", null: false t.datetime "updated_at", null: false t.string "user_agent", default: "", null: false t.inet "ip" t.bigint "access_token_id" + t.bigint "user_id", null: false t.bigint "web_push_subscription_id" t.index ["session_id"], name: "index_session_activations_on_session_id", unique: true t.index ["user_id"], name: "index_session_activations_on_user_id" @@ -295,9 +295,9 @@ ActiveRecord::Schema.define(version: 20170927215609) do t.string "var", null: false t.text "value" t.string "thing_type" - t.bigint "thing_id" t.datetime "created_at" t.datetime "updated_at" + t.bigint "thing_id" t.index ["thing_type", "thing_id", "var"], name: "index_settings_on_thing_type_and_thing_id_and_var", unique: true end @@ -323,7 +323,6 @@ ActiveRecord::Schema.define(version: 20170927215609) do create_table "statuses", force: :cascade do |t| t.string "uri" - t.bigint "account_id", null: false t.text "text", default: "", null: false t.datetime "created_at", null: false t.datetime "updated_at", null: false @@ -332,8 +331,6 @@ ActiveRecord::Schema.define(version: 20170927215609) do t.string "url" t.boolean "sensitive", default: false, null: false t.integer "visibility", default: 0, null: false - t.bigint "in_reply_to_account_id" - t.bigint "application_id" t.text "spoiler_text", default: "", null: false t.boolean "reply", default: false, null: false t.integer "favourites_count", default: 0, null: false @@ -341,6 +338,9 @@ ActiveRecord::Schema.define(version: 20170927215609) do t.string "language" t.bigint "conversation_id" t.boolean "local" + t.bigint "account_id", null: false + t.bigint "application_id" + t.bigint "in_reply_to_account_id" t.index ["account_id", "id"], name: "index_statuses_on_account_id_id" t.index ["conversation_id"], name: "index_statuses_on_conversation_id" t.index ["in_reply_to_id"], name: "index_statuses_on_in_reply_to_id" @@ -356,12 +356,12 @@ ActiveRecord::Schema.define(version: 20170927215609) do end create_table "stream_entries", force: :cascade do |t| - t.bigint "account_id" t.bigint "activity_id" t.string "activity_type" t.datetime "created_at", null: false t.datetime "updated_at", null: false t.boolean "hidden", default: false, null: false + t.bigint "account_id" t.index ["account_id"], name: "index_stream_entries_on_account_id" t.index ["activity_id", "activity_type"], name: "index_stream_entries_on_activity_id_and_activity_type" end @@ -371,11 +371,11 @@ ActiveRecord::Schema.define(version: 20170927215609) do t.string "secret" t.datetime "expires_at" t.boolean "confirmed", default: false, null: false - t.bigint "account_id", null: false t.datetime "created_at", null: false t.datetime "updated_at", null: false t.datetime "last_successful_delivery_at" t.string "domain" + t.bigint "account_id", null: false t.index ["account_id", "callback_url"], name: "index_subscriptions_on_account_id_and_callback_url", unique: true end @@ -389,7 +389,6 @@ ActiveRecord::Schema.define(version: 20170927215609) do create_table "users", force: :cascade do |t| t.string "email", default: "", null: false - t.bigint "account_id", null: false t.datetime "created_at", null: false t.datetime "updated_at", null: false t.string "encrypted_password", default: "", null: false @@ -415,6 +414,7 @@ ActiveRecord::Schema.define(version: 20170927215609) do t.datetime "last_emailed_at" t.string "otp_backup_codes", array: true t.string "filtered_languages", default: [], null: false, array: true + t.bigint "account_id", null: false t.index ["account_id"], name: "index_users_on_account_id" t.index ["confirmation_token"], name: "index_users_on_confirmation_token", unique: true t.index ["email"], name: "index_users_on_email", unique: true @@ -432,53 +432,53 @@ ActiveRecord::Schema.define(version: 20170927215609) do end create_table "web_settings", force: :cascade do |t| - t.bigint "user_id" t.json "data" t.datetime "created_at", null: false t.datetime "updated_at", null: false + t.bigint "user_id" t.index ["user_id"], name: "index_web_settings_on_user_id", unique: true end - add_foreign_key "account_domain_blocks", "accounts", on_delete: :cascade - add_foreign_key "blocks", "accounts", column: "target_account_id", on_delete: :cascade - add_foreign_key "blocks", "accounts", on_delete: :cascade - add_foreign_key "conversation_mutes", "accounts", on_delete: :cascade + add_foreign_key "account_domain_blocks", "accounts", name: "fk_206c6029bd", on_delete: :cascade + add_foreign_key "blocks", "accounts", column: "target_account_id", name: "fk_9571bfabc1", on_delete: :cascade + add_foreign_key "blocks", "accounts", name: "fk_4269e03e65", on_delete: :cascade + add_foreign_key "conversation_mutes", "accounts", name: "fk_225b4212bb", on_delete: :cascade add_foreign_key "conversation_mutes", "conversations", on_delete: :cascade - add_foreign_key "favourites", "accounts", on_delete: :cascade - add_foreign_key "favourites", "statuses", on_delete: :cascade - add_foreign_key "follow_requests", "accounts", column: "target_account_id", on_delete: :cascade - add_foreign_key "follow_requests", "accounts", on_delete: :cascade - add_foreign_key "follows", "accounts", column: "target_account_id", on_delete: :cascade - add_foreign_key "follows", "accounts", on_delete: :cascade - add_foreign_key "imports", "accounts", on_delete: :cascade - add_foreign_key "media_attachments", "accounts", on_delete: :nullify + add_foreign_key "favourites", "accounts", name: "fk_5eb6c2b873", on_delete: :cascade + add_foreign_key "favourites", "statuses", name: "fk_b0e856845e", on_delete: :cascade + add_foreign_key "follow_requests", "accounts", column: "target_account_id", name: "fk_9291ec025d", on_delete: :cascade + add_foreign_key "follow_requests", "accounts", name: "fk_76d644b0e7", on_delete: :cascade + add_foreign_key "follows", "accounts", column: "target_account_id", name: "fk_745ca29eac", on_delete: :cascade + add_foreign_key "follows", "accounts", name: "fk_32ed1b5560", on_delete: :cascade + add_foreign_key "imports", "accounts", name: "fk_6db1b6e408", on_delete: :cascade + add_foreign_key "media_attachments", "accounts", name: "fk_96dd81e81b", on_delete: :nullify add_foreign_key "media_attachments", "statuses", on_delete: :nullify - add_foreign_key "mentions", "accounts", on_delete: :cascade + add_foreign_key "mentions", "accounts", name: "fk_970d43f9d1", on_delete: :cascade add_foreign_key "mentions", "statuses", on_delete: :cascade - add_foreign_key "mutes", "accounts", column: "target_account_id", on_delete: :cascade - add_foreign_key "mutes", "accounts", on_delete: :cascade - add_foreign_key "notifications", "accounts", column: "from_account_id", on_delete: :cascade - add_foreign_key "notifications", "accounts", on_delete: :cascade - add_foreign_key "oauth_access_grants", "oauth_applications", column: "application_id", on_delete: :cascade - add_foreign_key "oauth_access_grants", "users", column: "resource_owner_id", on_delete: :cascade - add_foreign_key "oauth_access_tokens", "oauth_applications", column: "application_id", on_delete: :cascade - add_foreign_key "oauth_access_tokens", "users", column: "resource_owner_id", on_delete: :cascade - add_foreign_key "oauth_applications", "users", column: "owner_id", on_delete: :cascade - add_foreign_key "reports", "accounts", column: "action_taken_by_account_id", on_delete: :nullify - add_foreign_key "reports", "accounts", column: "target_account_id", on_delete: :cascade - add_foreign_key "reports", "accounts", on_delete: :cascade - add_foreign_key "session_activations", "oauth_access_tokens", column: "access_token_id", on_delete: :cascade - add_foreign_key "session_activations", "users", on_delete: :cascade - add_foreign_key "status_pins", "accounts", on_delete: :cascade + add_foreign_key "mutes", "accounts", column: "target_account_id", name: "fk_eecff219ea", on_delete: :cascade + add_foreign_key "mutes", "accounts", name: "fk_b8d8daf315", on_delete: :cascade + add_foreign_key "notifications", "accounts", column: "from_account_id", name: "fk_fbd6b0bf9e", on_delete: :cascade + add_foreign_key "notifications", "accounts", name: "fk_c141c8ee55", on_delete: :cascade + add_foreign_key "oauth_access_grants", "oauth_applications", column: "application_id", name: "fk_34d54b0a33", on_delete: :cascade + add_foreign_key "oauth_access_grants", "users", column: "resource_owner_id", name: "fk_63b044929b", on_delete: :cascade + add_foreign_key "oauth_access_tokens", "oauth_applications", column: "application_id", name: "fk_f5fc4c1ee3", on_delete: :cascade + add_foreign_key "oauth_access_tokens", "users", column: "resource_owner_id", name: "fk_e84df68546", on_delete: :cascade + add_foreign_key "oauth_applications", "users", column: "owner_id", name: "fk_b0988c7c0a", on_delete: :cascade + add_foreign_key "reports", "accounts", column: "action_taken_by_account_id", name: "fk_bca45b75fd", on_delete: :nullify + add_foreign_key "reports", "accounts", column: "target_account_id", name: "fk_eb37af34f0", on_delete: :cascade + add_foreign_key "reports", "accounts", name: "fk_4b81f7522c", on_delete: :cascade + add_foreign_key "session_activations", "oauth_access_tokens", column: "access_token_id", name: "fk_957e5bda89", on_delete: :cascade + add_foreign_key "session_activations", "users", name: "fk_e5fda67334", on_delete: :cascade + add_foreign_key "status_pins", "accounts", name: "fk_d4cb435b62", on_delete: :cascade add_foreign_key "status_pins", "statuses", on_delete: :cascade - add_foreign_key "statuses", "accounts", column: "in_reply_to_account_id", on_delete: :nullify - add_foreign_key "statuses", "accounts", on_delete: :cascade + add_foreign_key "statuses", "accounts", column: "in_reply_to_account_id", name: "fk_c7fa917661", on_delete: :nullify + add_foreign_key "statuses", "accounts", name: "fk_9bda1543f7", on_delete: :cascade add_foreign_key "statuses", "statuses", column: "in_reply_to_id", on_delete: :nullify add_foreign_key "statuses", "statuses", column: "reblog_of_id", on_delete: :cascade add_foreign_key "statuses_tags", "statuses", on_delete: :cascade - add_foreign_key "statuses_tags", "tags", on_delete: :cascade - add_foreign_key "stream_entries", "accounts", on_delete: :cascade - add_foreign_key "subscriptions", "accounts", on_delete: :cascade - add_foreign_key "users", "accounts", on_delete: :cascade - add_foreign_key "web_settings", "users", on_delete: :cascade + add_foreign_key "statuses_tags", "tags", name: "fk_3081861e21", on_delete: :cascade + add_foreign_key "stream_entries", "accounts", name: "fk_5659b17554", on_delete: :cascade + add_foreign_key "subscriptions", "accounts", name: "fk_9847d1cbb5", on_delete: :cascade + add_foreign_key "users", "accounts", name: "fk_50500f500d", on_delete: :cascade + add_foreign_key "web_settings", "users", name: "fk_11910667b2", on_delete: :cascade end diff --git a/lib/mastodon/migration_helpers.rb b/lib/mastodon/migration_helpers.rb new file mode 100644 index 000000000..ed716501e --- /dev/null +++ b/lib/mastodon/migration_helpers.rb @@ -0,0 +1,988 @@ +# frozen_string_literal: true + +# This file is copied almost entirely from GitLab, which has done a large +# amount of work to ensure that migrations can happen with minimal downtime. +# Many thanks to those engineers. + +# Changes have been made to remove dependencies on other GitLab files and to +# shorten temporary column names. + +# Documentation on using these functions (and why one might do so): +# https://gitlab.com/gitlab-org/gitlab-ce/blob/master/doc/development/what_requires_downtime.md + +# The file itself: +# https://gitlab.com/gitlab-org/gitlab-ce/blob/master/lib/gitlab/database/migration_helpers.rb + +# It is licensed as follows: + +# Copyright (c) 2011-2017 GitLab B.V. + +# Permission is hereby granted, free of charge, to any person obtaining a copy +# of this software and associated documentation files (the "Software"), to deal +# in the Software without restriction, including without limitation the rights +# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell +# copies of the Software, and to permit persons to whom the Software is +# furnished to do so, subject to the following conditions: + +# The above copyright notice and this permission notice shall be included in +# all copies or substantial portions of the Software. + +# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR +# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, +# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE +# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER +# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, +# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN +# THE SOFTWARE. + +# This is bad form, but there are enough differences that it's impractical to do +# otherwise: +# rubocop:disable all + +module Mastodon + module MigrationHelpers + # Stub for Database.postgresql? from GitLab + def self.postgresql? + ActiveRecord::Base.configurations[Rails.env]['adapter'].casecmp('postgresql').zero? + end + + # Stub for Database.mysql? from GitLab + def self.mysql? + ActiveRecord::Base.configurations[Rails.env]['adapter'].casecmp('mysql2').zero? + end + + # Model that can be used for querying permissions of a SQL user. + class Grant < ActiveRecord::Base + self.table_name = + if Mastodon::MigrationHelpers.postgresql? + 'information_schema.role_table_grants' + else + 'mysql.user' + end + + def self.scope_to_current_user + if Mastodon::MigrationHelpers.postgresql? + where('grantee = user') + else + where("CONCAT(User, '@', Host) = current_user()") + end + end + + # Returns true if the current user can create and execute triggers on the + # given table. + def self.create_and_execute_trigger?(table) + priv = + if Mastodon::MigrationHelpers.postgresql? + where(privilege_type: 'TRIGGER', table_name: table) + else + where(Trigger_priv: 'Y') + end + + priv.scope_to_current_user.any? + end + end + + BACKGROUND_MIGRATION_BATCH_SIZE = 1000 # Number of rows to process per job + BACKGROUND_MIGRATION_JOB_BUFFER_SIZE = 1000 # Number of jobs to bulk queue at a time + + # Gets an estimated number of rows for a table + def estimate_rows_in_table(table_name) + exec_query('SELECT reltuples FROM pg_class WHERE relname = ' + + "'#{table_name}'").to_a.first['reltuples'] + end + + # Adds `created_at` and `updated_at` columns with timezone information. + # + # This method is an improved version of Rails' built-in method `add_timestamps`. + # + # Available options are: + # default - The default value for the column. + # null - When set to `true` the column will allow NULL values. + # The default is to not allow NULL values. + def add_timestamps_with_timezone(table_name, options = {}) + options[:null] = false if options[:null].nil? + + [:created_at, :updated_at].each do |column_name| + if options[:default] && transaction_open? + raise '`add_timestamps_with_timezone` with default value cannot be run inside a transaction. ' \ + 'You can disable transactions by calling `disable_ddl_transaction!` ' \ + 'in the body of your migration class' + end + + # If default value is presented, use `add_column_with_default` method instead. + if options[:default] + add_column_with_default( + table_name, + column_name, + :datetime_with_timezone, + default: options[:default], + allow_null: options[:null] + ) + else + add_column(table_name, column_name, :datetime_with_timezone, options) + end + end + end + + # Creates a new index, concurrently when supported + # + # On PostgreSQL this method creates an index concurrently, on MySQL this + # creates a regular index. + # + # Example: + # + # add_concurrent_index :users, :some_column + # + # See Rails' `add_index` for more info on the available arguments. + def add_concurrent_index(table_name, column_name, options = {}) + if transaction_open? + raise 'add_concurrent_index can not be run inside a transaction, ' \ + 'you can disable transactions by calling disable_ddl_transaction! ' \ + 'in the body of your migration class' + end + + if MigrationHelpers.postgresql? + options = options.merge({ algorithm: :concurrently }) + disable_statement_timeout + end + + add_index(table_name, column_name, options) + end + + # Removes an existed index, concurrently when supported + # + # On PostgreSQL this method removes an index concurrently. + # + # Example: + # + # remove_concurrent_index :users, :some_column + # + # See Rails' `remove_index` for more info on the available arguments. + def remove_concurrent_index(table_name, column_name, options = {}) + if transaction_open? + raise 'remove_concurrent_index can not be run inside a transaction, ' \ + 'you can disable transactions by calling disable_ddl_transaction! ' \ + 'in the body of your migration class' + end + + if supports_drop_index_concurrently? + options = options.merge({ algorithm: :concurrently }) + disable_statement_timeout + end + + remove_index(table_name, options.merge({ column: column_name })) + end + + # Removes an existing index, concurrently when supported + # + # On PostgreSQL this method removes an index concurrently. + # + # Example: + # + # remove_concurrent_index :users, "index_X_by_Y" + # + # See Rails' `remove_index` for more info on the available arguments. + def remove_concurrent_index_by_name(table_name, index_name, options = {}) + if transaction_open? + raise 'remove_concurrent_index_by_name can not be run inside a transaction, ' \ + 'you can disable transactions by calling disable_ddl_transaction! ' \ + 'in the body of your migration class' + end + + if supports_drop_index_concurrently? + options = options.merge({ algorithm: :concurrently }) + disable_statement_timeout + end + + remove_index(table_name, options.merge({ name: index_name })) + end + + # Only available on Postgresql >= 9.2 + def supports_drop_index_concurrently? + return false unless MigrationHelpers.postgresql? + + version = select_one("SELECT current_setting('server_version_num') AS v")['v'].to_i + + version >= 90200 + end + + # Adds a foreign key with only minimal locking on the tables involved. + # + # This method only requires minimal locking when using PostgreSQL. When + # using MySQL this method will use Rails' default `add_foreign_key`. + # + # source - The source table containing the foreign key. + # target - The target table the key points to. + # column - The name of the column to create the foreign key on. + # on_delete - The action to perform when associated data is removed, + # defaults to "CASCADE". + def add_concurrent_foreign_key(source, target, column:, on_delete: :cascade, target_col: 'id') + # Transactions would result in ALTER TABLE locks being held for the + # duration of the transaction, defeating the purpose of this method. + if transaction_open? + raise 'add_concurrent_foreign_key can not be run inside a transaction' + end + + # While MySQL does allow disabling of foreign keys it has no equivalent + # of PostgreSQL's "VALIDATE CONSTRAINT". As a result we'll just fall + # back to the normal foreign key procedure. + if MigrationHelpers.mysql? + return add_foreign_key(source, target, + column: column, + on_delete: on_delete) + else + on_delete = 'SET NULL' if on_delete == :nullify + end + + disable_statement_timeout + + key_name = concurrent_foreign_key_name(source, column, target_col) + + # Using NOT VALID allows us to create a key without immediately + # validating it. This means we keep the ALTER TABLE lock only for a + # short period of time. The key _is_ enforced for any newly created + # data. + execute <<-EOF.strip_heredoc + ALTER TABLE #{source} + ADD CONSTRAINT #{key_name} + FOREIGN KEY (#{column}) + REFERENCES #{target} (#{target_col}) + #{on_delete ? "ON DELETE #{on_delete.upcase}" : ''} + NOT VALID; + EOF + + # Validate the existing constraint. This can potentially take a very + # long time to complete, but fortunately does not lock the source table + # while running. + execute("ALTER TABLE #{source} VALIDATE CONSTRAINT #{key_name};") + end + + # Returns the name for a concurrent foreign key. + # + # PostgreSQL constraint names have a limit of 63 bytes. The logic used + # here is based on Rails' foreign_key_name() method, which unfortunately + # is private so we can't rely on it directly. + def concurrent_foreign_key_name(table, column, target_col) + "fk_#{Digest::SHA256.hexdigest("#{table}_#{column}_#{target_col}_fk").first(10)}" + end + + # Long-running migrations may take more than the timeout allowed by + # the database. Disable the session's statement timeout to ensure + # migrations don't get killed prematurely. (PostgreSQL only) + def disable_statement_timeout + execute('SET statement_timeout TO 0') if MigrationHelpers.postgresql? + end + + # Updates the value of a column in batches. + # + # This method updates the table in batches of 5% of the total row count. + # This method will continue updating rows until no rows remain. + # + # When given a block this method will yield two values to the block: + # + # 1. An instance of `Arel::Table` for the table that is being updated. + # 2. The query to run as an Arel object. + # + # By supplying a block one can add extra conditions to the queries being + # executed. Note that the same block is used for _all_ queries. + # + # Example: + # + # update_column_in_batches(:projects, :foo, 10) do |table, query| + # query.where(table[:some_column].eq('hello')) + # end + # + # This would result in this method updating only rows where + # `projects.some_column` equals "hello". + # + # table - The name of the table. + # column - The name of the column to update. + # value - The value for the column. + # + # Rubocop's Metrics/AbcSize metric is disabled for this method as Rubocop + # determines this method to be too complex while there's no way to make it + # less "complex" without introducing extra methods (which actually will + # make things _more_ complex). + # + # rubocop: disable Metrics/AbcSize + def update_column_in_batches(table_name, column, value) + if transaction_open? + raise 'update_column_in_batches can not be run inside a transaction, ' \ + 'you can disable transactions by calling disable_ddl_transaction! ' \ + 'in the body of your migration class' + end + + table = Arel::Table.new(table_name) + + total = estimate_rows_in_table(table_name).to_i + if total == 0 + count_arel = table.project(Arel.star.count.as('count')) + count_arel = yield table, count_arel if block_given? + + total = exec_query(count_arel.to_sql).to_hash.first['count'].to_i + + return if total == 0 + end + + # Update in batches of 5% until we run out of any rows to update. + batch_size = ((total / 100.0) * 5.0).ceil + max_size = 1000 + + # The upper limit is 1000 to ensure we don't lock too many rows. For + # example, for "merge_requests" even 1% of the table is around 35 000 + # rows for GitLab.com. + batch_size = max_size if batch_size > max_size + + start_arel = table.project(table[:id]).order(table[:id].asc).take(1) + start_arel = yield table, start_arel if block_given? + start_id = exec_query(start_arel.to_sql).to_hash.first['id'].to_i + + say "Migrating #{table_name}.#{column} (~#{total.to_i} rows)" + + started_time = Time.now + last_time = Time.now + migrated = 0 + loop do + stop_row = nil + + suppress_messages do + stop_arel = table.project(table[:id]) + .where(table[:id].gteq(start_id)) + .order(table[:id].asc) + .take(1) + .skip(batch_size) + + stop_arel = yield table, stop_arel if block_given? + stop_row = exec_query(stop_arel.to_sql).to_hash.first + + update_arel = Arel::UpdateManager.new + .table(table) + .set([[table[column], value]]) + .where(table[:id].gteq(start_id)) + + if stop_row + stop_id = stop_row['id'].to_i + start_id = stop_id + update_arel = update_arel.where(table[:id].lt(stop_id)) + end + + update_arel = yield table, update_arel if block_given? + + execute(update_arel.to_sql) + end + + migrated += batch_size + if Time.now - last_time > 1 + status = "Migrated #{migrated} rows" + + percentage = 100.0 * migrated / total + status += " (~#{sprintf('%.2f', percentage)}%, " + + remaining_time = (100.0 - percentage) * (Time.now - started_time) / percentage + + status += "#{(remaining_time / 60).to_i}:" + status += sprintf('%02d', remaining_time.to_i % 60) + status += ' remaining, ' + + # Tell users not to interrupt if we're almost done. + if remaining_time > 10 + status += 'safe to interrupt' + else + status += 'DO NOT interrupt' + end + + status += ')' + + say status, true + last_time = Time.now + end + + # There are no more rows left to update. + break unless stop_row + end + end + + # Adds a column with a default value without locking an entire table. + # + # This method runs the following steps: + # + # 1. Add the column with a default value of NULL. + # 2. Change the default value of the column to the specified value. + # 3. Update all existing rows in batches. + # 4. Set a `NOT NULL` constraint on the column if desired (the default). + # + # These steps ensure a column can be added to a large and commonly used + # table without locking the entire table for the duration of the table + # modification. + # + # table - The name of the table to update. + # column - The name of the column to add. + # type - The column type (e.g. `:integer`). + # default - The default value for the column. + # limit - Sets a column limit. For example, for :integer, the default is + # 4-bytes. Set `limit: 8` to allow 8-byte integers. + # allow_null - When set to `true` the column will allow NULL values, the + # default is to not allow NULL values. + # + # This method can also take a block which is passed directly to the + # `update_column_in_batches` method. + def add_column_with_default(table, column, type, default:, limit: nil, allow_null: false, &block) + if transaction_open? + raise 'add_column_with_default can not be run inside a transaction, ' \ + 'you can disable transactions by calling disable_ddl_transaction! ' \ + 'in the body of your migration class' + end + + disable_statement_timeout + + transaction do + if limit + add_column(table, column, type, default: nil, limit: limit) + else + add_column(table, column, type, default: nil) + end + + # Changing the default before the update ensures any newly inserted + # rows already use the proper default value. + change_column_default(table, column, default) + end + + begin + update_column_in_batches(table, column, default, &block) + + change_column_null(table, column, false) unless allow_null + # We want to rescue _all_ exceptions here, even those that don't inherit + # from StandardError. + rescue Exception => error # rubocop: disable all + remove_column(table, column) + + raise error + end + end + + # Renames a column without requiring downtime. + # + # Concurrent renames work by using database triggers to ensure both the + # old and new column are in sync. However, this method will _not_ remove + # the triggers or the old column automatically; this needs to be done + # manually in a post-deployment migration. This can be done using the + # method `cleanup_concurrent_column_rename`. + # + # table - The name of the database table containing the column. + # old - The old column name. + # new - The new column name. + # type - The type of the new column. If no type is given the old column's + # type is used. + def rename_column_concurrently(table, old, new, type: nil) + if transaction_open? + raise 'rename_column_concurrently can not be run inside a transaction' + end + + check_trigger_permissions!(table) + trigger_name = rename_trigger_name(table, old, new) + + # If we were in the middle of update_column_in_batches, we should remove + # the old column and start over, as we have no idea where we were. + if column_for(table, new) + if MigrationHelpers.postgresql? + remove_rename_triggers_for_postgresql(table, trigger_name) + else + remove_rename_triggers_for_mysql(trigger_name) + end + + remove_column(table, new) + end + + old_col = column_for(table, old) + new_type = type || old_col.type + + col_opts = { + precision: old_col.precision, + scale: old_col.scale, + } + + # We may be trying to reset the limit on an integer column type, so let + # Rails handle that. + unless [:bigint, :integer].include?(new_type) + col_opts[:limit] = old_col.limit + end + + add_column(table, new, new_type, col_opts) + + # We set the default value _after_ adding the column so we don't end up + # updating any existing data with the default value. This isn't + # necessary since we copy over old values further down. + change_column_default(table, new, old_col.default) if old_col.default + + quoted_table = quote_table_name(table) + quoted_old = quote_column_name(old) + quoted_new = quote_column_name(new) + + if MigrationHelpers.postgresql? + install_rename_triggers_for_postgresql(trigger_name, quoted_table, + quoted_old, quoted_new) + else + install_rename_triggers_for_mysql(trigger_name, quoted_table, + quoted_old, quoted_new) + end + + update_column_in_batches(table, new, Arel::Table.new(table)[old]) + + change_column_null(table, new, false) unless old_col.null + + copy_indexes(table, old, new) + copy_foreign_keys(table, old, new) + end + + # Changes the type of a column concurrently. + # + # table - The table containing the column. + # column - The name of the column to change. + # new_type - The new column type. + def change_column_type_concurrently(table, column, new_type) + temp_column = rename_column_name(column) + + rename_column_concurrently(table, column, temp_column, type: new_type) + + # Primary keys don't necessarily have an associated index. + if ActiveRecord::Base.get_primary_key(table) == column.to_s + old_pk_index_name = "index_#{table}_on_#{column}" + new_pk_index_name = "index_#{table}_on_#{column}_cm" + + unless indexes_for(table, column).find{|i| i.name == old_pk_index_name} + add_concurrent_index(table, [temp_column], { + unique: true, + name: new_pk_index_name + }) + end + end + end + + # Performs cleanup of a concurrent type change. + # + # table - The table containing the column. + # column - The name of the column to change. + # new_type - The new column type. + def cleanup_concurrent_column_type_change(table, column) + temp_column = rename_column_name(column) + + # Wait for the indices to be built + indexes_for(table, column).each do |index| + expected_name = index.name + '_cm' + + puts "Waiting for index #{expected_name}" + sleep 1 until indexes_for(table, temp_column).find {|i| i.name == expected_name } + end + + was_primary = (ActiveRecord::Base.get_primary_key(table) == column.to_s) + old_default_fn = column_for(table, column).default_function + + old_fks = [] + if was_primary + # Get any foreign keys pointing at this column we need to recreate, and + # remove the old ones. + # Based on code from: + # http://errorbank.blogspot.com/2011/03/list-all-foreign-keys-references-for.html + old_fks_res = execute <<-EOF.strip_heredoc + select m.relname as src_table, + (select a.attname + from pg_attribute a + where a.attrelid = m.oid + and a.attnum = o.conkey[1] + and a.attisdropped = false) as src_col, + o.conname as name, + o.confdeltype as on_delete + from pg_constraint o + left join pg_class f on f.oid = o.confrelid + left join pg_class c on c.oid = o.conrelid + left join pg_class m on m.oid = o.conrelid + where o.contype = 'f' + and o.conrelid in ( + select oid from pg_class c where c.relkind = 'r') + and f.relname = '#{table}'; + EOF + old_fks = old_fks_res.to_a + old_fks.each do |old_fk| + add_concurrent_foreign_key( + old_fk['src_table'], + table, + column: old_fk['src_col'], + target_col: temp_column, + on_delete: extract_foreign_key_action(old_fk['on_delete']) + ) + + remove_foreign_key(old_fk['src_table'], name: old_fk['name']) + end + end + + # If there was a sequence owned by the old column, make it owned by the + # new column, as it will otherwise be deleted when we get rid of the + # old column. + if (seq_match = /^nextval\('([^']*)'(::text|::regclass)?\)/.match(old_default_fn)) + seq_name = seq_match[1] + execute("ALTER SEQUENCE #{seq_name} OWNED BY #{table}.#{temp_column}") + end + + transaction do + # This has to be performed in a transaction as otherwise we might have + # inconsistent data. + + cleanup_concurrent_column_rename(table, column, temp_column) + rename_column(table, temp_column, column) + + # If there was an old default function, we didn't copy it. Do that now + # in the transaction, so we don't miss anything. + change_column_default(table, column, -> { old_default_fn }) if old_default_fn + end + + # Rename any indices back to what they should be. + indexes_for(table, column).each do |index| + next unless index.name.end_with?('_cm') + + real_index_name = index.name.sub(/_cm$/, '') + rename_index(table, index.name, real_index_name) + end + + # Rename any foreign keys back to names based on the real column. + foreign_keys_for(table, column).each do |fk| + old_fk_name = concurrent_foreign_key_name(fk.from_table, temp_column, 'id') + new_fk_name = concurrent_foreign_key_name(fk.from_table, column, 'id') + execute("ALTER TABLE #{fk.from_table} RENAME CONSTRAINT " + + "#{old_fk_name} TO #{new_fk_name}") + end + + # Rename any foreign keys from other tables to names based on the real + # column. + old_fks.each do |old_fk| + old_fk_name = concurrent_foreign_key_name(old_fk['src_table'], + old_fk['src_col'], temp_column) + new_fk_name = concurrent_foreign_key_name(old_fk['src_table'], + old_fk['src_col'], column) + execute("ALTER TABLE #{old_fk['src_table']} RENAME CONSTRAINT " + + "#{old_fk_name} TO #{new_fk_name}") + end + + # If the old column was a primary key, mark the new one as a primary key. + if was_primary + execute("ALTER TABLE #{table} ADD PRIMARY KEY USING INDEX " + + "index_#{table}_on_#{column}") + end + end + + # Cleans up a concurrent column name. + # + # This method takes care of removing previously installed triggers as well + # as removing the old column. + # + # table - The name of the database table. + # old - The name of the old column. + # new - The name of the new column. + def cleanup_concurrent_column_rename(table, old, new) + trigger_name = rename_trigger_name(table, old, new) + + check_trigger_permissions!(table) + + if MigrationHelpers.postgresql? + remove_rename_triggers_for_postgresql(table, trigger_name) + else + remove_rename_triggers_for_mysql(trigger_name) + end + + remove_column(table, old) + end + + # Performs a concurrent column rename when using PostgreSQL. + def install_rename_triggers_for_postgresql(trigger, table, old, new) + execute <<-EOF.strip_heredoc + CREATE OR REPLACE FUNCTION #{trigger}() + RETURNS trigger AS + $BODY$ + BEGIN + NEW.#{new} := NEW.#{old}; + RETURN NEW; + END; + $BODY$ + LANGUAGE 'plpgsql' + VOLATILE + EOF + + execute <<-EOF.strip_heredoc + CREATE TRIGGER #{trigger} + BEFORE INSERT OR UPDATE + ON #{table} + FOR EACH ROW + EXECUTE PROCEDURE #{trigger}() + EOF + end + + # Installs the triggers necessary to perform a concurrent column rename on + # MySQL. + def install_rename_triggers_for_mysql(trigger, table, old, new) + execute <<-EOF.strip_heredoc + CREATE TRIGGER #{trigger}_insert + BEFORE INSERT + ON #{table} + FOR EACH ROW + SET NEW.#{new} = NEW.#{old} + EOF + + execute <<-EOF.strip_heredoc + CREATE TRIGGER #{trigger}_update + BEFORE UPDATE + ON #{table} + FOR EACH ROW + SET NEW.#{new} = NEW.#{old} + EOF + end + + # Removes the triggers used for renaming a PostgreSQL column concurrently. + def remove_rename_triggers_for_postgresql(table, trigger) + execute("DROP TRIGGER IF EXISTS #{trigger} ON #{table}") + execute("DROP FUNCTION IF EXISTS #{trigger}()") + end + + # Removes the triggers used for renaming a MySQL column concurrently. + def remove_rename_triggers_for_mysql(trigger) + execute("DROP TRIGGER IF EXISTS #{trigger}_insert") + execute("DROP TRIGGER IF EXISTS #{trigger}_update") + end + + # Returns the (base) name to use for triggers when renaming columns. + def rename_trigger_name(table, old, new) + 'trigger_' + Digest::SHA256.hexdigest("#{table}_#{old}_#{new}").first(12) + end + + # Returns the name to use for temporary rename columns. + def rename_column_name(base) + base.to_s + '_cm' + end + + # Returns an Array containing the indexes for the given column + def indexes_for(table, column) + column = column.to_s + + indexes(table).select { |index| index.columns.include?(column) } + end + + # Returns an Array containing the foreign keys for the given column. + def foreign_keys_for(table, column) + column = column.to_s + + foreign_keys(table).select { |fk| fk.column == column } + end + + # Copies all indexes for the old column to a new column. + # + # table - The table containing the columns and indexes. + # old - The old column. + # new - The new column. + def copy_indexes(table, old, new) + old = old.to_s + new = new.to_s + + indexes_for(table, old).each do |index| + new_columns = index.columns.map do |column| + column == old ? new : column + end + + # This is necessary as we can't properly rename indexes such as + # "ci_taggings_idx". + name = index.name + '_cm' + + # If the order contained the old column, map it to the new one. + order = index.orders + if order.key?(old) + order[new] = order.delete(old) + end + + options = { + unique: index.unique, + name: name, + length: index.lengths, + order: order + } + + # These options are not supported by MySQL, so we only add them if + # they were previously set. + options[:using] = index.using if index.using + options[:where] = index.where if index.where + + add_concurrent_index(table, new_columns, options) + end + end + + # Copies all foreign keys for the old column to the new column. + # + # table - The table containing the columns and indexes. + # old - The old column. + # new - The new column. + def copy_foreign_keys(table, old, new) + foreign_keys_for(table, old).each do |fk| + add_concurrent_foreign_key(fk.from_table, + fk.to_table, + column: new, + on_delete: fk.on_delete) + end + end + + # Returns the column for the given table and column name. + def column_for(table, name) + name = name.to_s + + columns(table).find { |column| column.name == name } + end + + # This will replace the first occurance of a string in a column with + # the replacement + # On postgresql we can use `regexp_replace` for that. + # On mysql we find the location of the pattern, and overwrite it + # with the replacement + def replace_sql(column, pattern, replacement) + quoted_pattern = Arel::Nodes::Quoted.new(pattern.to_s) + quoted_replacement = Arel::Nodes::Quoted.new(replacement.to_s) + + if MigrationHelpers.mysql? + locate = Arel::Nodes::NamedFunction + .new('locate', [quoted_pattern, column]) + insert_in_place = Arel::Nodes::NamedFunction + .new('insert', [column, locate, pattern.size, quoted_replacement]) + + Arel::Nodes::SqlLiteral.new(insert_in_place.to_sql) + else + replace = Arel::Nodes::NamedFunction + .new("regexp_replace", [column, quoted_pattern, quoted_replacement]) + Arel::Nodes::SqlLiteral.new(replace.to_sql) + end + end + + def remove_foreign_key_without_error(*args) + remove_foreign_key(*args) + rescue ArgumentError + end + + def sidekiq_queue_migrate(queue_from, to:) + while sidekiq_queue_length(queue_from) > 0 + Sidekiq.redis do |conn| + conn.rpoplpush "queue:#{queue_from}", "queue:#{to}" + end + end + end + + def sidekiq_queue_length(queue_name) + Sidekiq.redis do |conn| + conn.llen("queue:#{queue_name}") + end + end + + def check_trigger_permissions!(table) + unless Grant.create_and_execute_trigger?(table) + dbname = ActiveRecord::Base.configurations[Rails.env]['database'] + user = ActiveRecord::Base.configurations[Rails.env]['username'] || ENV['USER'] + + raise <<-EOF +Your database user is not allowed to create, drop, or execute triggers on the +table #{table}. + +If you are using PostgreSQL you can solve this by logging in to the GitLab +database (#{dbname}) using a super user and running: + + ALTER #{user} WITH SUPERUSER + +For MySQL you instead need to run: + + GRANT ALL PRIVILEGES ON *.* TO #{user}@'%' + +Both queries will grant the user super user permissions, ensuring you don't run +into similar problems in the future (e.g. when new tables are created). + EOF + end + end + + # Bulk queues background migration jobs for an entire table, batched by ID range. + # "Bulk" meaning many jobs will be pushed at a time for efficiency. + # If you need a delay interval per job, then use `queue_background_migration_jobs_by_range_at_intervals`. + # + # model_class - The table being iterated over + # job_class_name - The background migration job class as a string + # batch_size - The maximum number of rows per job + # + # Example: + # + # class Route < ActiveRecord::Base + # include EachBatch + # self.table_name = 'routes' + # end + # + # bulk_queue_background_migration_jobs_by_range(Route, 'ProcessRoutes') + # + # Where the model_class includes EachBatch, and the background migration exists: + # + # class Gitlab::BackgroundMigration::ProcessRoutes + # def perform(start_id, end_id) + # # do something + # end + # end + def bulk_queue_background_migration_jobs_by_range(model_class, job_class_name, batch_size: BACKGROUND_MIGRATION_BATCH_SIZE) + raise "#{model_class} does not have an ID to use for batch ranges" unless model_class.column_names.include?('id') + + jobs = [] + + model_class.each_batch(of: batch_size) do |relation| + start_id, end_id = relation.pluck('MIN(id), MAX(id)').first + + if jobs.length >= BACKGROUND_MIGRATION_JOB_BUFFER_SIZE + # Note: This code path generally only helps with many millions of rows + # We push multiple jobs at a time to reduce the time spent in + # Sidekiq/Redis operations. We're using this buffer based approach so we + # don't need to run additional queries for every range. + BackgroundMigrationWorker.perform_bulk(jobs) + jobs.clear + end + + jobs << [job_class_name, [start_id, end_id]] + end + + BackgroundMigrationWorker.perform_bulk(jobs) unless jobs.empty? + end + + # Queues background migration jobs for an entire table, batched by ID range. + # Each job is scheduled with a `delay_interval` in between. + # If you use a small interval, then some jobs may run at the same time. + # + # model_class - The table being iterated over + # job_class_name - The background migration job class as a string + # delay_interval - The duration between each job's scheduled time (must respond to `to_f`) + # batch_size - The maximum number of rows per job + # + # Example: + # + # class Route < ActiveRecord::Base + # include EachBatch + # self.table_name = 'routes' + # end + # + # queue_background_migration_jobs_by_range_at_intervals(Route, 'ProcessRoutes', 1.minute) + # + # Where the model_class includes EachBatch, and the background migration exists: + # + # class Gitlab::BackgroundMigration::ProcessRoutes + # def perform(start_id, end_id) + # # do something + # end + # end + def queue_background_migration_jobs_by_range_at_intervals(model_class, job_class_name, delay_interval, batch_size: BACKGROUND_MIGRATION_BATCH_SIZE) + raise "#{model_class} does not have an ID to use for batch ranges" unless model_class.column_names.include?('id') + + model_class.each_batch(of: batch_size) do |relation, index| + start_id, end_id = relation.pluck('MIN(id), MAX(id)').first + + # `BackgroundMigrationWorker.bulk_perform_in` schedules all jobs for + # the same time, which is not helpful in most cases where we wish to + # spread the work over time. + BackgroundMigrationWorker.perform_in(delay_interval * index, job_class_name, [start_id, end_id]) + end + end + end +end + +# rubocop:enable all |