Merge branch 'ab-49789-fks-want-indexes' into 'master'

Documentation + rubocop for checking that foreign key constraints require an index

Closes #49789

See merge request gitlab-org/gitlab-ce!20964

5 files changed, 133 insertions, 1 deletions

diff --git a/db/migrate/20160317092222_add_moved_to_to_issue.rb b/db/migrate/20160317092222_add_moved_to_to_issue.rb
index 461e7fb3a9b..2bf549d7ecd 100644
--- a/db/migrate/20160317092222_add_moved_to_to_issue.rb
+++ b/db/migrate/20160317092222_add_moved_to_to_issue.rb
@@ -1,5 +1,5 @@
 class AddMovedToToIssue < ActiveRecord::Migration
   def change
-    add_reference :issues, :moved_to, references: :issues
+    add_reference :issues, :moved_to, references: :issues # rubocop:disable Migration/AddReference
   end
 end
diff --git a/doc/development/migration_style_guide.md b/doc/development/migration_style_guide.md
index a211effdfa7..6f31e5b82e5 100644
--- a/doc/development/migration_style_guide.md
+++ b/doc/development/migration_style_guide.md
@@ -182,6 +182,34 @@ class MyMigration < ActiveRecord::Migration
 end
 ```
 
+## Adding foreign-key constraints
+
+When adding a foreign-key constraint to either an existing or new
+column remember to also add a index on the column.
+
+This is _required_ if the foreign-key constraint specifies
+`ON DELETE CASCADE` or `ON DELETE SET NULL` behavior. On a cascading
+delete, the [corresponding record needs to be retrieved using an
+index](https://www.cybertec-postgresql.com/en/postgresql-indexes-and-foreign-keys/)
+(otherwise, we'd need to scan the whole table) for subsequent update or
+deletion.
+
+Here's an example where we add a new column with a foreign key
+constraint. Note it includes `index: true` to create an index for it.
+
+```ruby
+class Migration < ActiveRecord::Migration
+
+  def change
+    add_reference :model, :other_model, index: true, foreign_key: { on_delete: :cascade }
+  end
+end
+```
+
+When adding a foreign-key constraint to an existing column, we
+have to employ `add_concurrent_foreign_key` and `add_concurrent_index`
+instead of `add_reference`.
+
 ## Adding Columns With Default Values
 
 When adding columns with default values you must use the method
diff --git a/rubocop/cop/migration/add_reference.rb b/rubocop/cop/migration/add_reference.rb
new file mode 100644
index 00000000000..4b67270c97a
--- /dev/null
+++ b/rubocop/cop/migration/add_reference.rb
@@ -0,0 +1,49 @@
+# frozen_string_literal: true
+require_relative '../../migration_helpers'
+
+module RuboCop
+  module Cop
+    module Migration
+      # Cop that checks if a foreign key constraint is added and require a index for it
+      class AddReference < RuboCop::Cop::Cop
+        include MigrationHelpers
+
+        MSG = '`add_reference` requires `index: true`'
+
+        def on_send(node)
+          return unless in_migration?(node)
+
+          name = node.children[1]
+
+          return unless name == :add_reference
+
+          opts = node.children.last
+
+          add_offense(node, location: :selector) unless opts && opts.type == :hash
+
+          index_present = false
+
+          opts.each_node(:pair) do |pair|
+            index_present ||= index_enabled?(pair)
+          end
+
+          add_offense(node, location: :selector) unless index_present
+        end
+
+        private
+
+        def index_enabled?(pair)
+          hash_key_type(pair) == :sym && hash_key_name(pair) == :index && pair.children[1].true_type?
+        end
+
+        def hash_key_type(pair)
+          pair.children[0].type
+        end
+
+        def hash_key_name(pair)
+          pair.children[0].children[0]
+        end
+      end
+    end
+  end
+end
diff --git a/rubocop/rubocop.rb b/rubocop/rubocop.rb
index aa7ae601f75..a427208cdab 100644
--- a/rubocop/rubocop.rb
+++ b/rubocop/rubocop.rb
@@ -11,6 +11,7 @@ require_relative 'cop/migration/add_column'
 require_relative 'cop/migration/add_concurrent_foreign_key'
 require_relative 'cop/migration/add_concurrent_index'
 require_relative 'cop/migration/add_index'
+require_relative 'cop/migration/add_reference'
 require_relative 'cop/migration/add_timestamps'
 require_relative 'cop/migration/datetime'
 require_relative 'cop/migration/hash_index'
diff --git a/spec/rubocop/cop/migration/add_reference_spec.rb b/spec/rubocop/cop/migration/add_reference_spec.rb
new file mode 100644
index 00000000000..8f795bb561e
--- /dev/null
+++ b/spec/rubocop/cop/migration/add_reference_spec.rb
@@ -0,0 +1,54 @@
+require 'spec_helper'
+
+require 'rubocop'
+require 'rubocop/rspec/support'
+
+require_relative '../../../../rubocop/cop/migration/add_reference'
+
+describe RuboCop::Cop::Migration::AddReference do
+  include CopHelper
+
+  let(:cop) { described_class.new }
+
+  context 'outside of a migration' do
+    it 'does not register any offenses' do
+      expect_no_offenses(<<~RUBY)
+        def up
+          add_reference(:projects, :users)
+        end
+      RUBY
+    end
+  end
+
+  context 'in a migration' do
+    before do
+      allow(cop).to receive(:in_migration?).and_return(true)
+    end
+
+    it 'registers an offense when using add_reference without index' do
+      expect_offense(<<~RUBY)
+        call do
+          add_reference(:projects, :users)
+          ^^^^^^^^^^^^^ `add_reference` requires `index: true`
+        end
+      RUBY
+    end
+
+    it 'registers an offense when using add_reference index disabled' do
+      expect_offense(<<~RUBY)
+        def up
+          add_reference(:projects, :users, index: false)
+          ^^^^^^^^^^^^^ `add_reference` requires `index: true`
+        end
+      RUBY
+    end
+
+    it 'does not register an offense when using add_reference with index enabled' do
+      expect_no_offenses(<<~RUBY)
+        def up
+          add_reference(:projects, :users, index: true)
+        end
+      RUBY
+    end
+  end
+end