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

app helper in the rails console

The app helper is cool. You can use it to simulate requests to your app: 

e.g.

app.get "/" 

Hide attributes in Rails / ActiveRecord

Let's assume you have a table "entities" that was created by this Rails migration code:

create_table :entities do |t|
  t.string :type
  t.string :title
  t.string :first_name
  t.string :last_name
  t.binary :logo
end

Person and Organisation will be subclasses of Entity (which of course is a subclass of ActiveRecord::Base), using single table inheritance.

If people have no logo and organisations have no title, first name and last name, maybe we would like to hide this columns from their respective classes. Of course, you may say that in this case you shouldn't use STI. But if you really want to... ActiveRecord does not provide hiding functionality. All Person and Organisation instances will see all attributes of Entity. This behaviour can be changed as follows.

When a new object is created (e.g. Person.new), this is a real instance of Person. The list of attributes comes in this case from the public class method columns of ActiveRecord::Base. When records are fetched using a finder method, however, (e.g. Person.first), ActiveRecord goes another way, and calls the private class method instantiate of ActiveRecord::Base, which takes an hash of attributes/values as argument, which conversely derive from the results of the sql query. So to hide attributes you have to hide them both in columns and in instantiate and the trick is done.

ActiveScaffold and LOB sorting in Oracle

If you are using ActiveScaffold and an Oracle DB, you will notice that you can't natively sort on LOB columns. If you click on the table header for a LOB column, you will get an error message. A way to fix this is to sort using the substr function and pass it to config.columns[].sort_by :sql.

To make it simpler, I solved the matter including in ActiveScaffold::DataStructures::Column a
method:

def sort_as_lob(lenght = 50, offset = 1)
  sort_by :sql => "dbms_lob.substr(#{name},"+
                                  "#{lenght},"+
                                  "#{offset})"
end

which I use on LOBs column declaring in the active scaffold config block:

active_scaffold do |config|
config.columns[:my_lob].sort_as_lob
end

sort_by => :sql in and polymorphic associations

Assume you have a polymorphic association in Rails.

class MyObject < ActiveRecord::Base 
  belongs_to :another_object, :polymorphic => true
end

Well, "another object" can now be in any other table of the DB, and in the my_objects table there are two columns (assuming you followed the conventions) named another_object_id and another_object_type, the first one containing the ID of the object, the second the model name.

Now let's say I have a table with all "my objects" and I want to sort it according to a specific column in "another object" (for simplicity let us assume any possible "other object" have a column called "label").

There is two ways to do this. The first one is loading all "my objects" instances, then for each one make a query, according to the association type, to find the other objects, than let Ruby sort by label. This is of course not optimized. Through the
'eager loading' ActiveRecord feature, which I think is in the meantime also available for polymorphic associations, it is probably possible to find a better way. However I wanted a single query, so I did it using SQL; case was introduced in the standard, if I am not wrong, in the SQL-92 version. I think that most SQL-DBs comply, SQL-Lite probably excluded (I tested only on Oracle).

So in my case another_objects can only be of a few types, so I did it this way, in the controller code:

#
# e.g. types = %w[Cat Dog Mouse]
#
def sort_by_sql(types)
  sql = '(case (another_object_type)'
  sql << types.map do |type|
           'when #{type} 
            then (select label 
                 from #{type.tableize} t
                 where t.id = another_object_id) '
         end.join('')
  sql << 'end)'
end
private :sort_by_sql

You can use this sort_by_sql() method in the :order_by => sort_by_sql(...) key of the find method to sort by the label method of the polymorphic association with only one query. This was sensibly faster in my case.

Actually the need for this came because I wanted to sort by a polymorphic association in an ActiveScaffold based controller, and in this case I am not sure I could have specified an eager loading without too much effort (probably overriding the finder method of the list). So I just wrote the sort_by_sql() as a class method and used it in the config block of my active scaffold:

AllowedTypes = %w[Cat Dog Mouse]
def self.sort_by_sql(types)
  #...the code up here...#
end
active_scaffold :my_objects do |config|
  # ...
  config.columns[:another_object].sort_by :sql => 
                           sort_by_sql(*AllowedTypes)
  # ...
end

That worked fine for me.

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