postgres_migration_utils.rb

postgres_migration_utils.rb

module PostgreSQLMigrationUtils

  # Changes the type of a string or text column to citext and recreates
  # indexes on that column that follow the rails naming convention, such
  # that the index is on the result of the lower(col) function.
  #
  # Note: The citext type changes comparisons on citext columns to be on
  # lower(col), but does nothing about ensuring that indexes created on
  # citext columns are automatically created on the result of lower(col).
  def change_column_to_citext(table_name, column_name)
    execute "ALTER TABLE #{table_name.to_s} ALTER COLUMN #{column_name.to_s} TYPE citext;"
    get_index_names(table_name, column_name).each do |index_name|
      execute "DROP INDEX #{index_name}";
      index_columns = index_name.split("_on_").last.split("_and_")
      index_expressions = index_columns.map do |column_name|
        is_citext_column(table_name, column_name) ? "LOWER(#{column_name})" : column_name
      end
      execute "CREATE INDEX #{index_name} ON #{table_name}( #{index_expressions.join(', ')} )"
    end
  end

  # Runs a postgres command via the psql command line client.
  #
  # This allows the command to be run by a user other than the
  # user specified in database.yml, which may be necessary to
  # create extensions or run other commands for which the app
  # user may not have sufficient privileges.
  #
  # Note: Relies on peer auth if no host found in database.yml,
  # password auth with password in ~/.pgpass file otherwise.
  def psql(cmd)
    db_host = ActiveRecord::Base.connection_config[:host]
    db_name = ActiveRecord::Base.connection.current_database
    psql_cmd = "psql -q -e -d #{db_name} -c \"#{cmd}\""
    psql_cmd << " -h #{db_host}" if db_host.present?
    raise "Psql command failed '#{psql_cmd}'" unless system(psql_cmd)
  end

  private
  # Returns an array of index names matching the table and column
  #
  # Note: only returns index names matching the standard
  # index_table_on_column naming convention applied by add_column
  def get_index_names(table_name, column_name)
    sql = %{
      SELECT c.relname AS index_name
      FROM pg_catalog.pg_class c
           LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
           LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
      WHERE c.relkind IN ('i','s','')
        AND n.nspname !~ '^pg_toast'
        AND c.relname LIKE 'index_#{table_name}_on_%#{column_name}%'
        AND pg_catalog.pg_table_is_visible(c.oid)
    }
    execute(sql).field_values("index_name")
  end

  def is_citext_column(table_name, column_name)
    sql = %{
      SELECT COUNT(*)
      FROM information_schema.columns
      WHERE table_name = '#{table_name}'
        AND column_name = '#{column_name}'
        AND udt_name = 'citext'
    }
    execute(sql).getvalue(0,0).to_i > 0
  end

end

class ActiveRecord::Migration
  include PostgreSQLMigrationUtils
end