Helper class for finding all ActiveRecord relation mappings in a database (mostly for use outside of the context of ActiveRecord)

active_record_mappings.rb

# Non-Rails version that pulls mappings without using ActiveRecord. Externally pulls data and stores on filesystem.
class ActiveRecordMappings

  attr_accessor :database, :command

  # Initialize with a database name and a mysql commandline string
  # e.g. arm = ActiveRecordMappings.new(command: "mysql -u john -p", database: "my_stuff")
  def initialize(opts={})
    opts.assert_valid_keys :database, :command
    @database = opts[:database]
    @command = opts[:command]
  end

  # Hash of all tables (as keys) which have references to other tables (value as array)
  # e.g. arm.all_mappings
  # { :activities=>[:user_id, :item_id, :item_type, :enterprise_id],
  #   :activity_items=>[:actor_id, :actor_type, :object_id, :object_type, :indirect_object_id, :indirect_object_type, :enterprise_id],
  #   :applications=>[:user_id, :enterprise_id, :application_id],
  #   :authorizations=>[:user_id],
  #   :comments=>[:commentable_id, :commentable_type, :user_id, :recipient_id, :enterprise_id], :completion_records=>[:user_id, :enrollment_id]
  # }
  def all_mappings
    File.open(File.join('id_names.sql'), 'w') do |f|
      f << %{SELECT concat('SELECT ', column_name, ' FROM ', table_name)
          FROM information_schema.columns
          WHERE (column_name LIKE '%_id%' OR column_name LIKE '%_type') and table_schema = '#{@database}'}
    end
    %x{ #{@command} < id_names.sql > id_names2.sql}
    arr = File.open( File.join('id_names2.sql') ).readlines
    ['id_names.sql', 'id_names2.sql'].each{|f| File.delete(File.join(f))}
    arr.shift
    hash = {}
    arr.select{|v| v.match /_id/ or v.match /_type/}.map{|v| v.gsub('SELECT ','').gsub(' FROM','').split}.each do |v,k|
      hash[k.to_sym] = [] unless hash[k.to_sym].present?
      hash[k.to_sym] << v.to_sym
    end
    hash
  end

  # Subset of all_mappings as a hash of all tables showing only non-polymorphic mappings
  # e.g. arm.id_mappings
  # { :activities=>[:user_id, :enterprise_id],
  #   :activity_items=>[:enterprise_id],
  #   :applications=>[:user_id, :enterprise_id, :application_id],
  #   :authorizations=>[:user_id],
  #   :comments=>[:user_id, :recipient_id, :enterprise_id]
  # }
  # Using "for: Class" subsets only those mappings that relate to that class. For instance 'for: :enterprise' returns
  # $ arm.id_mappings for: :enterprise
  # { :activities=>[:user_id, :enterprise_id],
  #   :activity_items=>[:enterprise_id],
  #   :applications=>[:user_id, :enterprise_id, :application_id],
  #   :comments=>[:user_id, :recipient_id, :enterprise_id]
  # }
  # Note: Authorizations is not present, because it does not link to enterprise
  def id_mappings(opts={})
    opts.assert_valid_keys :for # can send in multiple ways. e.g. :enterprises, :enterprise, or :enterprise_id
    hash = {}
    all_mappings.each do |k,v|
      hash[k] = [] unless hash[k].present?
      v.each do |value|
        hash[k] << value if value.to_s.match /_id/ and not v.include?(value.to_s.gsub('_id', '_type').to_sym)
      end
    end
    hash.reject!{|k,v| v.none?}
    opts[:for].nil? ? hash : hash.select{|k,v| v.include?("#{opts[:for].to_s.gsub('_id','').singularize}_id".to_sym)}
  end

  # Subset of all_mappings as a hash of all tables showing ONLY polymorphic mappings
  # e.g. arm.polymorphic_mappings
  # { :activities=>[:item_id],
  #   :activity_items=>[:actor_id, :object_id, :indirect_object_id],
  #   :comments=>[:commentable_id]
  # }
  def polymorphic_mappings
    hash = {}
    all_mappings.each do |k,v|
      hash[k] = [] unless hash[k].present?
      v.each do |value|
        hash[k] << value if value.to_s.match /_id/ and v.include?(value.to_s.gsub('_id', '_type').to_sym)
      end
    end
    hash.reject{|k,v| v.none?}
  end
end

active_record_mappings_memory.rb

# Version with Rails-dependency, utilizing ActiveRecord to store things in memory, rather than avoiding ActiveRecord and writing to the filesystem.
class ActiveRecordMappings

  attr_accessor :database, :command

  # Initialize with a database name and a mysql commandline string
  # e.g. arm = ActiveRecordMappings.new(command: "mysql -u john -p", database: "my_stuff")
  def initialize(hash)
    @database = hash[:database]
    @connection_hash = hash
  end

  def conn
    ActiveRecord::Base.establish_connection @connection_hash
    ActiveRecord::Base.connection
  end

  # Hash of all tables (as keys) which have references to other tables (value as array)
  # e.g. arm.all_mappings
  # { :activities=>[:user_id, :item_id, :item_type, :enterprise_id],
  #   :activity_items=>[:actor_id, :actor_type, :object_id, :object_type, :indirect_object_id, :indirect_object_type, :enterprise_id],
  #   :applications=>[:user_id, :enterprise_id, :application_id],
  #   :authorizations=>[:user_id],
  #   :comments=>[:commentable_id, :commentable_type, :user_id, :recipient_id, :enterprise_id], :completion_records=>[:user_id, :enrollment_id]
  # }
  def all_mappings
    res = conn.execute %{SELECT concat('SELECT ', column_name, ' FROM ', table_name)
          FROM information_schema.columns
          WHERE (column_name LIKE '%_id%' OR column_name LIKE '%_type') and table_schema = '#{@database}'}
    res = res.map(&:first)
    hash = {}
    res.select{|v| v.match /_id/ or v.match /_type/}.map{|v| v.gsub('SELECT ','').gsub(' FROM','').split}.each do |v,k|
      hash[k.to_sym] = [] unless hash[k.to_sym].present?
      hash[k.to_sym] << v.to_sym
    end
    hash
  end

  # Subset of all_mappings as a hash of all tables showing only non-polymorphic mappings
  # e.g. arm.id_mappings
  # { :activities=>[:user_id, :enterprise_id],
  #   :activity_items=>[:enterprise_id],
  #   :applications=>[:user_id, :enterprise_id, :application_id],
  #   :authorizations=>[:user_id],
  #   :comments=>[:user_id, :recipient_id, :enterprise_id]
  # }
  # Using "for: Class" subsets only those mappings that relate to that class. For instance 'for: :enterprise' returns
  # $ arm.id_mappings for: :enterprise
  # { :activities=>[:user_id, :enterprise_id],
  #   :activity_items=>[:enterprise_id],
  #   :applications=>[:user_id, :enterprise_id, :application_id],
  #   :comments=>[:user_id, :recipient_id, :enterprise_id]
  # }
  # Note: Authorizations is not present, because it does not link to enterprise
  def id_mappings(opts={})
    opts.assert_valid_keys :for # can send in multiple ways. e.g. :enterprises, :enterprise, or :enterprise_id
    hash = {}
    all_mappings.each do |k,v|
      hash[k] = [] unless hash[k].present?
      v.each do |value|
        hash[k] << value if value.to_s.match /_id/ and not v.include?(value.to_s.gsub('_id', '_type').to_sym)
      end
    end
    hash.reject!{|k,v| v.none?}
    opts[:for].nil? ? hash : hash.select{|k,v| v.include?("#{opts[:for].to_s.gsub('_id','').singularize}_id".to_sym)}
  end

  # Subset of all_mappings as a hash of all tables showing ONLY polymorphic mappings
  # e.g. arm.polymorphic_mappings
  # { :activities=>[:item_id],
  #   :activity_items=>[:actor_id, :object_id, :indirect_object_id],
  #   :comments=>[:commentable_id]
  # }
  def polymorphic_mappings(opts={})
    hash = {}
    all_mappings.each do |k,v|
      hash[k] = [] unless hash[k].present?
      v.each do |value|
        hash[k] << value if value.to_s.match /_id/ and v.include?(value.to_s.gsub('_id', '_type').to_sym)
      end
    end
    hash.reject{|k,v| v.none?}
  end
end