tool to compare DB schema against DDL file
in [Ruby]
|
Prev: Chat client
Next: multidimensional array insert syntax
From: Fabian Marin on 28 Jul 2010 21:43 Robert Klemme wrote: > The key question is: can an AR model represent a DB schema entirely? > What does it tell you about constraints and triggers? Does it cover all > sorts of special indexes as Oracle's FBI's and special types found only > in one RDBMS? > > Kind regards > > robert The answer might be that it cannot, it's a matter of where you put your logic. RoR relies on logic in the AR model even for testing constraints like uniqueness, as far as I know. They themselves admit RoR is opinionated in many respects. I guess such a tool won't be useful for developers that have opted for an architecture strongly reliant on the Database (i.e. using triggers, stored procedures, sequences and so forth). And I do realize I did not state I was looking for open source alternatives. I've looked in Rubyforge, and the ruby gems repository, and have not found much. -- Posted via http://www.ruby-forum.com/.
From: Fabian Marin on 28 Jul 2010 21:48 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/.
From: brabuhr on 28 Jul 2010 23:37 On Wed, Jul 28, 2010 at 9:48 PM, Fabian Marin <fmg134s(a)yahoo.com> wrote: > 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. I've not tried it; it should work, but I'm more comfortable reading Ruby than SQL :-) > Did you code this for fun or did your job demand it? Mostly fun :) one recent case where it's handy is with an internal Rails app I have that hits an Oracle database for an external PHP app; for development, I originally did a schema:dump from Oracle and a schema:load into H2. The script provides a quick way to get an idea of what they've changed in the database. At present, I'm just reading the existing data, so losing database-side integrity in the rails schema.rb doesn't cause much trouble.
From: Robert Klemme on 29 Jul 2010 01:14 On 07/29/2010 03:43 AM, Fabian Marin wrote: > Robert Klemme wrote: > >> The key question is: can an AR model represent a DB schema entirely? >> What does it tell you about constraints and triggers? Does it cover all >> sorts of special indexes as Oracle's FBI's and special types found only >> in one RDBMS? > > The answer might be that it cannot, it's a matter of where you put your > logic. RoR relies on logic in the AR model even for testing constraints > like uniqueness, as far as I know. They themselves admit RoR is > opinionated in many respects. I guess such a tool won't be useful for > developers that have opted for an architecture strongly reliant on the > Database (i.e. using triggers, stored procedures, sequences and so > forth). But what are you looking for? The subject says you want to compare a schema and a DDL file now it seems you need to compare a schema and an AR model. These are two quite different sets of requirements. Cheers robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
From: Fabian Marin on 29 Jul 2010 16:11
Robert Klemme wrote: > But what are you looking for? The subject says you want to compare a > schema and a DDL file now it seems you need to compare a schema and an > AR model. These are two quite different sets of requirements. > > Cheers > > robert I ultimately need to compare a DB (extracting data to a DB custom tool or from the system catalogs) and a DDL file. However it might be easier if one or both of those forms are translated to an intermediate, more easily comparable form such as ... an AR model! The idea is basically to find an intermediate form that allows easy comparison of the two: a DB's current schema, and a DDL file. Translating information to this intermediate form would also allow maximum flexibility (e.g. how about comparing two DDL files, two databases, a ruby schema and a DDL file). -- Posted via http://www.ruby-forum.com/. |