From: Fabian Marin <fmg134s@...>
Date: 2010-07-29T10:48:00+09:00
Subject: Re: tool to compare DB schema against DDL file

unknown wrote:
> 
> 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:
> 
>> 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

Well I'll grant you one thing, it sure ain't pretty but it gets the job
done, at least with simple requirements.  It even seems surprising that
it is fairly short.  Then again, have you ever tried parsing DDL files
to generate a similar diff?  I would think even mixing (a ruby schema
against a DDL file) would be possible.

Did you code this for fun or did your job demand it?
-- 
Posted via http://www.ruby-forum.com/.