find-invalid-keys.rb

require 'mysql2'
require 'yaml'


class ForeignKeys

    def initialize
      @config = YAML::load(File.open(File.dirname(File.expand_path(__FILE__)) + '/db.yml'))
      @config['database'] = ARGV[0] ? ARGV[0] : @config['database']

      @client = Mysql2::Client.new(
        :host => @config['host'],
        :port => @config['port'],
        :username => @config['username'],
        :password => @config['password'],
        :database => @config['database'])

      get_foreign_keys_from_database
    end

    def get_foreign_keys_from_database
      @foreign_keys = @client.query("SELECT `TABLE_NAME`, `COLUMN_NAME`, `REFERENCED_TABLE_NAME`, `REFERENCED_COLUMN_NAME`  FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='#{@config['database']}' AND REFERENCED_TABLE_SCHEMA IS NOT NULL;")
    end


    def check_all_forgein_keys
      @foreign_keys.each do |fk|
        check_foreign_key(fk)
      end
    end


    def check_foreign_key(foreign_key)

      puts "Checking #{foreign_key['TABLE_NAME']}.#{foreign_key['COLUMN_NAME']}"

      if(foreign_key['TABLE_NAME'] == foreign_key['REFERENCED_TABLE_NAME'])
        sql = "SELECT t1.* FROM `#{@config['database']}`.`#{foreign_key['TABLE_NAME']}` t1 LEFT JOIN `#{@config['database']}`.`#{foreign_key['REFERENCED_TABLE_NAME']}` t2 ON (t1.#{foreign_key['COLUMN_NAME']}=t2.#{foreign_key['REFERENCED_COLUMN_NAME']}) WHERE t1.`#{foreign_key['COLUMN_NAME']}` IS NOT NULL AND t2.`#{foreign_key['REFERENCED_COLUMN_NAME']}` IS NULL"
      else
        sql = "SELECT `#{foreign_key['TABLE_NAME']}`.* FROM `#{@config['database']}`.`#{foreign_key['TABLE_NAME']}` LEFT JOIN `#{@config['database']}`.`#{foreign_key['REFERENCED_TABLE_NAME']}` ON (#{foreign_key['TABLE_NAME']}.#{foreign_key['COLUMN_NAME']}=#{foreign_key['REFERENCED_TABLE_NAME']}.#{foreign_key['REFERENCED_COLUMN_NAME']}) WHERE #{foreign_key['TABLE_NAME']}.#{foreign_key['COLUMN_NAME']} IS NOT NULL AND `#{foreign_key['REFERENCED_TABLE_NAME']}`.`#{foreign_key['REFERENCED_COLUMN_NAME']}` IS NULL"
      end

        results = @client.query(sql)
        if(results.count > 0)
          STDERR.puts "Found #{results.count} Invalid Foreign Keys"
          STDERR.puts sql
        end
    end


end


fk = ForeignKeys.new
fk.check_all_forgein_keys