From: brabuhr@...
Date: 2010-07-29T03:59:39+09:00
Subject: Re: tool to compare DB schema against DDL file

On Wed, Jul 28, 2010 at 10:50 AM, Fabian Marin <fmg134s@yahoo.com> wrote:
>> On 28.07.2010 03:57, Fabian Marin wrote:
>>> must (eventually) match the schema proscribed by the DDL.
>>> I've done my share of research, looking for a similar tool to avoid
>>> reinventing the wheel.  However, I could not reach the author of
>>> db_discovery (http://rubyforge.org/projects/db-discovery/), and
>>> otherwise I found no other author.  It seems like db_discovery did not
>>> find a niche even though it seems to be a very good tool.
>>>
>>> Does anyone know if such a tool already exists and is actively
>>> supported??? As a newbie Rubyist I'm trying to exploit the power of this
>>> community by querying you guys to make sure such a tool would actually
>>> be novel.
>
> How about this option?
>
> Use RoR Active Record to create a model from a DDL file, and also a
> comparable model from a DB, then generate a module that can compare
> equivalent Entity/Table classes from both?

RoR ActiveRecord can dump a ruby representation of the database schema
(db:schema:dump) and another rake task (db:structure:dump) that dumps
the database schema as SQL CREATE TABLE statements.

I have a (really, extremely very ugly) script to "diff" ruby-schema files:

> ruby schemadiff.rb schema.production schema.development
schema.production       has 22 tables
schema.development      has 24 tables
Tables removed:
        resources
Tables added:
        faq
        page_translations
        surveys
Table answers:
        Columns added:
                created_at      datetime
                updated_at      datetime
Table inquiries:
        Columns added:
                spam    boolean
Table question_groups:
        Columns added:
                custom_class    string
                custom_renderer string
Table questions:
        Columns removed:
                is_mandatory    integer
        Columns added:
                is_mandatory    boolean
Table resources:
        Columns removed:
                created_at      datetime
                updated_at      datetime
                parent_id       integer
                size    integer
                content_type    string
                filename        string

> cat schemadiff.rb
def read_schema(filename)
  {}.tap{|h| File.read(filename).scan(/^\W*(create_table\W*"(.*?)".*?$.*?^\W*end)$/m).each{|s|
h[s[1]] = s[0].scan(/^\W*t.(\w+)\W*"(\w+)".*?$/m)}}
end

cur = read_schema(ARGV[0])
new = read_schema(ARGV[1])

puts ARGV[0] + "\thas #{cur.keys.size} tables"
puts ARGV[1] + "\thas #{new.keys.size} tables"

puts "Tables removed:\n\t" + (cur.keys.sort - new.keys.sort).join("\n\t")
puts "Tables added:\n\t"   + (new.keys.sort - cur.keys.sort).join("\n\t")

cur.keys.sort.each do |t|
  rem = (cur[t].sort - (new[t] || []).sort).map{|a| "#{a[1]}\t#{a[0]}"}
  add = ((new[t] || []).sort - cur[t].sort).map{|a| "#{a[1]}\t#{a[0]}"}
  next if rem == add
  puts "Table #{t}:"
  puts "\tColumns removed:\n\t\t" + rem.join("\n\t\t") unless rem == []
  puts "\tColumns added:\n\t\t"   + add.join("\n\t\t") unless add == []
end