Count distinct values in all columns of a table

The following rake task will show the number of different values of each column of a given table in the database. I wrote it to see statistics on which columns are really used.

namespace :db do
 desc "Count distinct values for each non empty column of a table (TABLE=xxx) "+
      "optionally using a condition (WHERE=\"yyy\")"
 task :count_distincts => :environment do
   raise "Specify option TABLE=<table_name>" unless ENV["TABLE"]
   puts "Distinct values in table #{ENV['TABLE']}:"
   puts "(condition: where #{ENV['WHERE']})" if ENV['WHERE']
   c = ActiveRecord::Base.connection
   columns = c.select_all("describe #{ENV['TABLE']};").map{|a|a["Field"]}
   columns.each do |col|
     sql = "select count(distinct #{col}) as c from #{ENV['TABLE']}"
     sql << " where #{ENV['WHERE']}" if ENV['WHERE']
     n = c.select_one(sql)['c']
     puts "#{col}: #{n}" unless n == "0"
     STDOUT.flush
   end
 end
end

Fake migrations for rails < 2.0.2

The following rake task can be used to update the migration pointer without actually migrate in rails databases for rails under 2.0.2. This is sometimes useful if you applied something manually or want to skip some migration anyway.

Usage examples:

rake db:pretend:migrate

rake db:pretend:migrate VERSION=20080730181045

rake db:pretend:rollback

namespace(:db) do
  namespace(:pretend) do
    desc "Pretend the database migrated 1 step or to VERSION=<nn>"
    task :migrate => :environment do
      c = ActiveRecord::Base.connection
      if ENV['VERSION']
        version = ENV['VERSION'].to_i
      else
        version = c.select_one("select version from schema_info")['version'].to_i + 1
      end
      c.execute("update schema_info set version = #{version}")
      puts "Current version: #{version}"
    end
    desc "Pretend that the last migration did not happen"
    task :rollback => :environment do
      c = ActiveRecord::Base.connection
      version = c.select_one("select version from schema_info")['version'].to_i - 1
      c.execute("update schema_info set version = #{version}")
      puts "Current version: #{version}"
    end
  end
end

oracle adapter and rails migrations

The Oracle adapter for active record has a bug that does not allow to create a dump of the schema using rake. The following code is from http://dev.rubyonrails.org/ticket/10415 and corrects this problem.

require 'active_record/connection_adapters/oracle_adapter'
module ActiveRecord
  module ConnectionAdapters
   class OracleAdapter
     # Returns an array of arrays containing the field values.
     # Order is the same as that returned by #columns.
     def select_rows(sql, name = nil)
       result = select(sql, name)
       result.map{ |v| v.values}
     end
   end
 end
end