tool to compare DB schema against DDL file
in [Ruby]
|
Prev: Chat client
Next: multidimensional array insert syntax
From: Fabian Marin on 27 Jul 2010 21:57 First of all any feedback from you guys will be of tremendous help. I'm looking for a good topic for my thesis (to major in Computer Engineering) and at the same time I'm encountering a problem in my current job. Simply stated: I have a DDL file that describes the entire Data Model of an application. We use 6 different environments (including production) with a DB that must (eventually) match the schema proscribed by the DDL. We delegate the execution of the DDL to a DBA; I work in a highly bureaucratic environment so I can't just go ahead and run DDL myself. Additionally the app itself is currently running Java, though I want to use Ruby to generate the tool I need right now. Such a tool would parse the DDL as an external DSL and then compare it with the current DB schema, to then generate a series of DDL statements that would synchronize the two, a sort of DDL diff, if you will. 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. As for its implementation I have a couple of ideas (e.g. parse DDL as an external DSL using a multiple dispatch library, use ActiveRecord to reflect on a database's schema). However, I want to make sure that such a tool would actually benefit the community. -- Posted via http://www.ruby-forum.com/.
From: Robert Klemme on 28 Jul 2010 04:11 On 28.07.2010 03:57, Fabian Marin wrote: > First of all any feedback from you guys will be of tremendous help. > > I'm looking for a good topic for my thesis (to major in Computer > Engineering) and at the same time I'm encountering a problem in my > current job. Simply stated: > > I have a DDL file that describes the entire Data Model of an > application. > > We use 6 different environments (including production) with a DB that > must (eventually) match the schema proscribed by the DDL. > > We delegate the execution of the DDL to a DBA; I work in a highly > bureaucratic environment so I can't just go ahead and run DDL myself. > Additionally the app itself is currently running Java, though I want to > use Ruby to generate the tool I need right now. > > Such a tool would parse the DDL as an external DSL and then compare it > with the current DB schema, to then generate a series of DDL statements > that would synchronize the two, a sort of DDL diff, if you will. > > 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. It wouldn't. This is a solved problem already, for example Sybase has an excellent product which has sophisticated schema comparison functionality with a good graphical UI: http://www.sybase.com/products/modelingdevelopment/powerdesigner > As for its implementation I have a couple of ideas (e.g. parse DDL as an > external DSL using a multiple dispatch library, use ActiveRecord to > reflect on a database's schema). However, I want to make sure that such > a tool would actually benefit the community. I would create these tools: 1. schema extractor into a standard format which you define (could be an XML representation or just marshalled object model). 2. diff for the standard format schema (two way, maybe also three way) 3. maybe also a converter from a given DDL to the standard format so you do not have to go through the DB although this might be fragile since the actual DB's settings may influence how the schema eventually looks. Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
From: Fabian Marin on 28 Jul 2010 10:50 Robert Klemme 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. > > It wouldn't. This is a solved problem already, for example Sybase has > an excellent product which has sophisticated schema comparison > functionality with a good graphical UI: > http://www.sybase.com/products/modelingdevelopment/powerdesigner > Is Sybase open source? I'm disregarding proprietary, non open source solutions. >> As for its implementation I have a couple of ideas (e.g. parse DDL as an >> external DSL using a multiple dispatch library, use ActiveRecord to >> reflect on a database's schema). However, I want to make sure that such >> a tool would actually benefit the community. > > I would create these tools: > > 1. schema extractor into a standard format which you define (could be an > XML representation or just marshalled object model). > > 2. diff for the standard format schema (two way, maybe also three way) > > 3. maybe also a converter from a given DDL to the standard format so you > do not have to go through the DB although this might be fragile since > the actual DB's settings may influence how the schema eventually looks. > > Kind regards > > robert 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? -- Posted via http://www.ruby-forum.com/.
From: brabuhr on 28 Jul 2010 14:59 On Wed, Jul 28, 2010 at 10:50 AM, Fabian Marin <fmg134s(a)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
From: Robert Klemme on 28 Jul 2010 16:14
On 28.07.2010 16:50, Fabian Marin wrote: > Robert Klemme 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. >> >> It wouldn't. This is a solved problem already, for example Sybase has >> an excellent product which has sophisticated schema comparison >> functionality with a good graphical UI: >> http://www.sybase.com/products/modelingdevelopment/powerdesigner > > Is Sybase open source? No. > I'm disregarding proprietary, non open source > solutions. Well, you didn't say so initially if I'm not mistaken. >>> As for its implementation I have a couple of ideas (e.g. parse DDL as an >>> external DSL using a multiple dispatch library, use ActiveRecord to >>> reflect on a database's schema). However, I want to make sure that such >>> a tool would actually benefit the community. >> >> I would create these tools: >> >> 1. schema extractor into a standard format which you define (could be an >> XML representation or just marshalled object model). >> >> 2. diff for the standard format schema (two way, maybe also three way) >> >> 3. maybe also a converter from a given DDL to the standard format so you >> do not have to go through the DB although this might be fragile since >> the actual DB's settings may influence how the schema eventually looks. > > 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? 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 -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/ |