pg_check_referential_integrity

#!/usr/bin/env ruby

# If  your  tables  follow   default  Rails  naming  conventions  (i.e.
# pluralized table  names and #{model_name}_id foreign  key names) then
# this script can run  a quick report on your data and  tell you if any
# of your tables contain references to non-existent rows in foreign key
# tables.
#
# If you  had used  referential integrity from  the start,  this script
# wouldn't be that useful. But for  some people migrating from MySQL to
# Postgres, this script is a  useful automated sanity check. Note: this
# script only  understands really simple foreign  key relationships and
# not  polymorphic  relationships  or any  other  'funky'  ActiveRecord
# associations.
#
# If you pass '--attempt-fix' to this script, it will attempt to create
# an enforced foreign key constraint for you.
#
# This software is 'use-at-your-own-risk-ware'. No warranties, expressed
# or implied, yada yada.
#

ALL_TABLES_QUERY = "
  select
    c.relname
  FROM
    pg_catalog.pg_class c
  LEFT JOIN
    pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE
    c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
  AND pg_catalog.pg_table_is_visible(c.oid);
"

COLUMNS_FOR_TABLE_QUERY = "
  select
    column_name
  from
    information_schema.columns
  where
  table_name = 'TABLE_NAME';
"

CHECK_CONSTRAINT_EXISTS_QUERY = "
  select
    constraint_name
  from
    information_schema.table_constraints
  where
    constraint_type = 'FOREIGN KEY' and table_name = 'TABLE_NAME';
"

COUNT_ORPHANS_QUERY = "
  select
    count(*) as orphan_count
  from
    LEFT_TABLE_NAME as lTable
  left join
    RIGHT_TABLE_NAME as rTable on lTable.LEFT_COLUMN = rTable.id
  where
    lTable.LEFT_COLUMN is not null
    and rTable.id is null;
"

def execute(query)
  ActiveRecord::Base.connection.execute(query)
end

def all_tables
  execute(ALL_TABLES_QUERY).rows.map {|row| row[0] }
end

def columns_for_table(table)
  execute(COLUMNS_FOR_TABLE_QUERY.gsub(/TABLE_NAME/, table)).rows.map {|row| row[0] }
end

def column_fkey(table, column)
  constraints = execute(CHECK_CONSTRAINT_EXISTS_QUERY.gsub(/TABLE_NAME/, table)).rows.map {|row| row[0] }
  if constraints.include? "#{table}_#{column}_fkey"
    "#{table}_#{column}_fkey"
  else
    nil
  end
end

def fkey_table(column)
  column.gsub(/_id$/, "").pluralize
end

def make_column_fkey(table, column)
  execute("alter table #{table} add foreign key (#{column}) references #{fkey_table(column)} (id);")
end

def report_integrity_violations(table, column)
  result = execute(COUNT_ORPHANS_QUERY.
                   gsub(/LEFT_TABLE_NAME/, table).
                   gsub(/LEFT_COLUMN/, column).
                   gsub(/RIGHT_TABLE_NAME/, fkey_table(column)))
  orphan_count = result.rows[0][0].to_i
  if orphan_count > 0
    puts "'#{table}.#{column}' contains #{orphan_count} entries that have no matching entry in '#{fkey_table(column)}.id'"
  end
end

if $PROGRAM_NAME == __FILE__
  require File.dirname(__FILE__) + '/../config/environment'
  attempt_fix = ARGV[0] == "--attempt-fix"
  tables = all_tables
  tables.each do |table|
    columns_for_table(table).each do |column|
      if column =~ /_id$/
        if !column_fkey(table, column)
          if attempt_fix
            make_column_fkey(table, column) if tables.include?(fkey_table(column))
          else
            report_integrity_violations(table, column) if tables.include?(fkey_table(column))
          end
        end
      end
    end
  end
end