add Foreign Key constraints, enforce foreign key coverage via a test

codebender · codebender · commit d7154cbde14f · 2016-02-20T12:11:56.000-07:00
diff --git a/db/migrate/20160220184719_add_foreign_key_badges_users.rb b/db/migrate/20160220184719_add_foreign_key_badges_users.rb
@@ -0,0 +1,5 @@
+class AddForeignKeyBadgesUsers < ActiveRecord::Migration
+  def change
+    add_foreign_key :badges, :users
+  end
+end
diff --git a/db/migrate/20160220184919_add_foreign_key_comments_users.rb b/db/migrate/20160220184919_add_foreign_key_comments_users.rb
@@ -0,0 +1,5 @@
+class AddForeignKeyCommentsUsers < ActiveRecord::Migration
+  def change
+    add_foreign_key :comments, :users
+  end
+end
diff --git a/db/migrate/20160220185013_add_foreign_key_comments_protips.rb b/db/migrate/20160220185013_add_foreign_key_comments_protips.rb
@@ -0,0 +1,5 @@
+class AddForeignKeyCommentsProtips < ActiveRecord::Migration
+  def change
+    add_foreign_key :comments, :protips
+  end
+end
diff --git a/db/migrate/20160220185141_add_foreign_key_likes_users.rb b/db/migrate/20160220185141_add_foreign_key_likes_users.rb
@@ -0,0 +1,5 @@
+class AddForeignKeyLikesUsers < ActiveRecord::Migration
+  def change
+    add_foreign_key :likes, :users
+  end
+end
diff --git a/db/migrate/20160220185329_add_foreign_key_protips_users.rb b/db/migrate/20160220185329_add_foreign_key_protips_users.rb
@@ -0,0 +1,5 @@
+class AddForeignKeyProtipsUsers < ActiveRecord::Migration
+  def change
+    add_foreign_key :protips, :users
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
@@ -11,7 +11,7 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema.define(version: 20160219190140) do
+ActiveRecord::Schema.define(version: 20160220185329) do
 
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"
@@ -111,4 +111,9 @@
   add_index "users", ["skills"], name: "index_users_on_skills", using: :gin
   add_index "users", ["username"], name: "index_users_on_username", unique: true, using: :btree
 
+  add_foreign_key "badges", "users"
+  add_foreign_key "comments", "protips"
+  add_foreign_key "comments", "users"
+  add_foreign_key "likes", "users"
+  add_foreign_key "protips", "users"
 end
diff --git a/spec/helpers/badges_helper_spec.rb b/spec/helpers/badges_helper_spec.rb
diff --git a/spec/helpers/comments_helper_spec.rb b/spec/helpers/comments_helper_spec.rb
diff --git a/spec/models/foreign_key_coverage_spec.rb b/spec/models/foreign_key_coverage_spec.rb
@@ -0,0 +1,70 @@
+require 'rails_helper'
+
+describe 'Foreign Key coverage' do
+  # https://viget.com/extend/identifying-foreign-key-dependencies-from-activerecordbase-classes
+  Association = Struct.new(:association) do
+    delegate :foreign_key, to: :association
+
+    def klass
+      association.klass unless polymorphic?
+    end
+
+    def name
+      association.options[:class_name] || association.name
+    end
+
+    def polymorphic?
+      !!association.options[:polymorphic]
+    end
+
+    def polymorphic_dependencies
+      return [] unless polymorphic?
+      @polymorphic_dependencies ||= ActiveRecord::Base.subclasses.select { |model| polymorphic_match? model }
+    end
+
+    def polymorphic_match?(model)
+      model.reflect_on_all_associations(:has_many).any? do |has_many_association|
+        has_many_association.options[:as] == association.name
+      end
+    end
+
+    def dependencies(include_polymorphic=true)
+      polymorphic? ? (include_polymorphic ? polymorphic_dependencies : []) : Array(klass)
+    end
+
+    def polymorphic_type
+      association.foreign_type if polymorphic?
+    end
+  end
+
+  it 'is complete' do
+    # to ignore an entire table, add the table name symbol as the key with value :all
+    # to ignore specific columns, add the table name symbol as the key with an array of column name symbols as the value
+    ignore = {
+      users_roles: %i( left_side_id ),
+      version_associations: :all # PaperTrail's models declare some relations to missing tables, so this is necessary even though we have no version_associations table.
+    }
+
+    abc = ActiveRecord::Base.connection
+
+    Rails.application.eager_load!
+
+    # enumerate active record models
+    ActiveRecord::Base.subclasses.each do |model|
+      ignore_columns = ignore.fetch(model.table_name.to_sym, [])
+      next if ignore_columns == :all
+
+      existing_foreign_keys = abc.foreign_keys(model.table_name).each_with_object([]) do |foreign_key, keys|
+        keys.push "#{ foreign_key.from_table }.#{ foreign_key.options[:column] } => #{ foreign_key.to_table }.#{ foreign_key.options[:primary_key] }"
+      end
+
+      model.reflect_on_all_associations(:belongs_to).each do |association|
+        Association.new(association).dependencies(false).each do |dependency|
+          next if ignore_columns.include?(association.foreign_key.to_sym)
+          key = "#{ model.table_name }.#{ association.foreign_key } => #{ dependency.table_name }.#{ dependency.primary_key }"
+          expect(existing_foreign_keys).to include(key), "Missing foreign key #{ key }"
+        end
+      end
+    end
+  end
+end
