Fix null values being included in some indexes (#17711)

* Fix null values being included in some indexes

* Update lib/mastodon/migration_helpers.rb

Co-authored-by: Claire <claire.github-309c@sitedethib.com>

* Add documentation link to corruption error message

Co-authored-by: Claire <claire.github-309c@sitedethib.com>

18 files changed, 306 insertions, 0 deletions

diff --git a/db/post_migrate/20220307083603_optimize_null_index_conversations_uri.rb b/db/post_migrate/20220307083603_optimize_null_index_conversations_uri.rb
new file mode 100644
index 000000000..9f45bec7e
--- /dev/null
+++ b/db/post_migrate/20220307083603_optimize_null_index_conversations_uri.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexConversationsUri < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :conversations, 'index_conversations_on_uri', :uri, unique: true, where: 'uri IS NOT NULL', opclass: :text_pattern_ops
+  end
+
+  def down
+    update_index :conversations, 'index_conversations_on_uri', :uri, unique: true
+  end
+end
diff --git a/db/post_migrate/20220310060545_optimize_null_index_statuses_in_reply_to_account_id.rb b/db/post_migrate/20220310060545_optimize_null_index_statuses_in_reply_to_account_id.rb
new file mode 100644
index 000000000..ddb07ab5d
--- /dev/null
+++ b/db/post_migrate/20220310060545_optimize_null_index_statuses_in_reply_to_account_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexStatusesInReplyToAccountId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :statuses, 'index_statuses_on_in_reply_to_account_id', :in_reply_to_account_id, where: 'in_reply_to_account_id IS NOT NULL'
+  end
+
+  def down
+    update_index :statuses, 'index_statuses_on_in_reply_to_account_id', :in_reply_to_account_id
+  end
+end
diff --git a/db/post_migrate/20220310060556_optimize_null_index_statuses_in_reply_to_id.rb b/db/post_migrate/20220310060556_optimize_null_index_statuses_in_reply_to_id.rb
new file mode 100644
index 000000000..411c86456
--- /dev/null
+++ b/db/post_migrate/20220310060556_optimize_null_index_statuses_in_reply_to_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexStatusesInReplyToId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :statuses, 'index_statuses_on_in_reply_to_id', :in_reply_to_id, where: 'in_reply_to_id IS NOT NULL'
+  end
+
+  def down
+    update_index :statuses, 'index_statuses_on_in_reply_to_id', :in_reply_to_id
+  end
+end
diff --git a/db/post_migrate/20220310060614_optimize_null_index_media_attachments_scheduled_status_id.rb b/db/post_migrate/20220310060614_optimize_null_index_media_attachments_scheduled_status_id.rb
new file mode 100644
index 000000000..9d86a0364
--- /dev/null
+++ b/db/post_migrate/20220310060614_optimize_null_index_media_attachments_scheduled_status_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexMediaAttachmentsScheduledStatusId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :media_attachments, 'index_media_attachments_on_scheduled_status_id', :scheduled_status_id, where: 'scheduled_status_id IS NOT NULL'
+  end
+
+  def down
+    update_index :media_attachments, 'index_media_attachments_on_scheduled_status_id', :scheduled_status_id
+  end
+end
diff --git a/db/post_migrate/20220310060626_optimize_null_index_media_attachments_shortcode.rb b/db/post_migrate/20220310060626_optimize_null_index_media_attachments_shortcode.rb
new file mode 100644
index 000000000..658f3e42a
--- /dev/null
+++ b/db/post_migrate/20220310060626_optimize_null_index_media_attachments_shortcode.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexMediaAttachmentsShortcode < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :media_attachments, 'index_media_attachments_on_shortcode', :shortcode, unique: true, where: 'shortcode IS NOT NULL', opclass: :text_pattern_ops
+  end
+
+  def down
+    update_index :media_attachments, 'index_media_attachments_on_shortcode', :shortcode, unique: true
+  end
+end
diff --git a/db/post_migrate/20220310060641_optimize_null_index_users_reset_password_token.rb b/db/post_migrate/20220310060641_optimize_null_index_users_reset_password_token.rb
new file mode 100644
index 000000000..cc5bdae96
--- /dev/null
+++ b/db/post_migrate/20220310060641_optimize_null_index_users_reset_password_token.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexUsersResetPasswordToken < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :users, 'index_users_on_reset_password_token', :reset_password_token, unique: true, where: 'reset_password_token IS NOT NULL', opclass: :text_pattern_ops
+  end
+
+  def down
+    update_index :users, 'index_users_on_reset_password_token', :reset_password_token, unique: true
+  end
+end
diff --git a/db/post_migrate/20220310060653_optimize_null_index_users_created_by_application_id.rb b/db/post_migrate/20220310060653_optimize_null_index_users_created_by_application_id.rb
new file mode 100644
index 000000000..364939d3a
--- /dev/null
+++ b/db/post_migrate/20220310060653_optimize_null_index_users_created_by_application_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexUsersCreatedByApplicationId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :users, 'index_users_on_created_by_application_id', :created_by_application_id, where: 'created_by_application_id IS NOT NULL'
+  end
+
+  def down
+    update_index :users, 'index_users_on_created_by_application_id', :created_by_application_id
+  end
+end
diff --git a/db/post_migrate/20220310060706_optimize_null_index_statuses_uri.rb b/db/post_migrate/20220310060706_optimize_null_index_statuses_uri.rb
new file mode 100644
index 000000000..0fb5f6365
--- /dev/null
+++ b/db/post_migrate/20220310060706_optimize_null_index_statuses_uri.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexStatusesUri < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :statuses, 'index_statuses_on_uri', :uri, unique: true, where: 'uri IS NOT NULL', opclass: :text_pattern_ops
+  end
+
+  def down
+    update_index :statuses, 'index_statuses_on_uri', :uri, unique: true
+  end
+end
diff --git a/db/post_migrate/20220310060722_optimize_null_index_accounts_moved_to_account_id.rb b/db/post_migrate/20220310060722_optimize_null_index_accounts_moved_to_account_id.rb
new file mode 100644
index 000000000..ec93a417a
--- /dev/null
+++ b/db/post_migrate/20220310060722_optimize_null_index_accounts_moved_to_account_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexAccountsMovedToAccountId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :accounts, 'index_accounts_on_moved_to_account_id', :moved_to_account_id, where: 'moved_to_account_id IS NOT NULL'
+  end
+
+  def down
+    update_index :accounts, 'index_accounts_on_moved_to_account_id', :moved_to_account_id
+  end
+end
diff --git a/db/post_migrate/20220310060740_optimize_null_index_oauth_access_tokens_refresh_token.rb b/db/post_migrate/20220310060740_optimize_null_index_oauth_access_tokens_refresh_token.rb
new file mode 100644
index 000000000..30ade8e3f
--- /dev/null
+++ b/db/post_migrate/20220310060740_optimize_null_index_oauth_access_tokens_refresh_token.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexOauthAccessTokensRefreshToken < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :oauth_access_tokens, 'index_oauth_access_tokens_on_refresh_token', :refresh_token, unique: true, where: 'refresh_token IS NOT NULL', opclass: :text_pattern_ops
+  end
+
+  def down
+    update_index :oauth_access_tokens, 'index_oauth_access_tokens_on_refresh_token', :refresh_token, unique: true
+  end
+end
diff --git a/db/post_migrate/20220310060750_optimize_null_index_accounts_url.rb b/db/post_migrate/20220310060750_optimize_null_index_accounts_url.rb
new file mode 100644
index 000000000..d8bce144f
--- /dev/null
+++ b/db/post_migrate/20220310060750_optimize_null_index_accounts_url.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexAccountsURL < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :accounts, 'index_accounts_on_url', :url, where: 'url IS NOT NULL', opclass: :text_pattern_ops
+  end
+
+  def down
+    update_index :accounts, 'index_accounts_on_url', :url
+  end
+end
diff --git a/db/post_migrate/20220310060809_optimize_null_index_oauth_access_tokens_resource_owner_id.rb b/db/post_migrate/20220310060809_optimize_null_index_oauth_access_tokens_resource_owner_id.rb
new file mode 100644
index 000000000..787263bf7
--- /dev/null
+++ b/db/post_migrate/20220310060809_optimize_null_index_oauth_access_tokens_resource_owner_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexOauthAccessTokensResourceOwnerId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :oauth_access_tokens, 'index_oauth_access_tokens_on_resource_owner_id', :resource_owner_id, where: 'resource_owner_id IS NOT NULL'
+  end
+
+  def down
+    update_index :oauth_access_tokens, 'index_oauth_access_tokens_on_resource_owner_id', :resource_owner_id
+  end
+end
diff --git a/db/post_migrate/20220310060833_optimize_null_index_announcement_reactions_custom_emoji_id.rb b/db/post_migrate/20220310060833_optimize_null_index_announcement_reactions_custom_emoji_id.rb
new file mode 100644
index 000000000..4afea291a
--- /dev/null
+++ b/db/post_migrate/20220310060833_optimize_null_index_announcement_reactions_custom_emoji_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexAnnouncementReactionsCustomEmojiId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :announcement_reactions, 'index_announcement_reactions_on_custom_emoji_id', :custom_emoji_id, where: 'custom_emoji_id IS NOT NULL'
+  end
+
+  def down
+    update_index :announcement_reactions, 'index_announcement_reactions_on_custom_emoji_id', :custom_emoji_id
+  end
+end
diff --git a/db/post_migrate/20220310060854_optimize_null_index_appeals_approved_by_account_id.rb b/db/post_migrate/20220310060854_optimize_null_index_appeals_approved_by_account_id.rb
new file mode 100644
index 000000000..244b89417
--- /dev/null
+++ b/db/post_migrate/20220310060854_optimize_null_index_appeals_approved_by_account_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexAppealsApprovedByAccountId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :appeals, 'index_appeals_on_approved_by_account_id', :approved_by_account_id, where: 'approved_by_account_id IS NOT NULL'
+  end
+
+  def down
+    update_index :appeals, 'index_appeals_on_approved_by_account_id', :approved_by_account_id
+  end
+end
diff --git a/db/post_migrate/20220310060913_optimize_null_index_account_migrations_target_account_id.rb b/db/post_migrate/20220310060913_optimize_null_index_account_migrations_target_account_id.rb
new file mode 100644
index 000000000..cb1906e9f
--- /dev/null
+++ b/db/post_migrate/20220310060913_optimize_null_index_account_migrations_target_account_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexAccountMigrationsTargetAccountId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :account_migrations, 'index_account_migrations_on_target_account_id', :target_account_id, where: 'target_account_id IS NOT NULL'
+  end
+
+  def down
+    update_index :account_migrations, 'index_account_migrations_on_target_account_id', :target_account_id
+  end
+end
diff --git a/db/post_migrate/20220310060926_optimize_null_index_appeals_rejected_by_account_id.rb b/db/post_migrate/20220310060926_optimize_null_index_appeals_rejected_by_account_id.rb
new file mode 100644
index 000000000..017333c26
--- /dev/null
+++ b/db/post_migrate/20220310060926_optimize_null_index_appeals_rejected_by_account_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexAppealsRejectedByAccountId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :appeals, 'index_appeals_on_rejected_by_account_id', :rejected_by_account_id, where: 'rejected_by_account_id IS NOT NULL'
+  end
+
+  def down
+    update_index :appeals, 'index_appeals_on_rejected_by_account_id', :rejected_by_account_id
+  end
+end
diff --git a/db/post_migrate/20220310060939_optimize_null_index_list_accounts_follow_id.rb b/db/post_migrate/20220310060939_optimize_null_index_list_accounts_follow_id.rb
new file mode 100644
index 000000000..fc0d42a25
--- /dev/null
+++ b/db/post_migrate/20220310060939_optimize_null_index_list_accounts_follow_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexListAccountsFollowId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :list_accounts, 'index_list_accounts_on_follow_id', :follow_id, where: 'follow_id IS NOT NULL'
+  end
+
+  def down
+    update_index :list_accounts, 'index_list_accounts_on_follow_id', :follow_id
+  end
+end
diff --git a/db/post_migrate/20220310060959_optimize_null_index_web_push_subscriptions_access_token_id.rb b/db/post_migrate/20220310060959_optimize_null_index_web_push_subscriptions_access_token_id.rb
new file mode 100644
index 000000000..fb49e33e9
--- /dev/null
+++ b/db/post_migrate/20220310060959_optimize_null_index_web_push_subscriptions_access_token_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexWebPushSubscriptionsAccessTokenId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :web_push_subscriptions, 'index_web_push_subscriptions_on_access_token_id', :access_token_id, where: 'access_token_id IS NOT NULL'
+  end
+
+  def down
+    update_index :web_push_subscriptions, 'index_web_push_subscriptions_on_access_token_id', :access_token_id
+  end
+end