Wolfmans Howlings

Create Rails ActiveRecord Models from DDL

Posted by Jim Morris on Wed May 24 23:20:00 -0700 2006

I had a database with about 60 tables in it, most where simple lookup tables with simple has_many and belongs_to relationships, and I didn't want to manually create all the models, with the associations by hand. I googled around and came across Bill Katz's dbmodel, which takes the output of dbdesigner and creates the models with the relationships. However I already had the Databases and schema setup, and I didn't have (and couldn't find) a copy of dbdesigner to use. So I hacked Bills dbmodel to read a DDL file that was created from the command rake db:structure:dump, as I was using Postgresql this file had all the relevant relationship info in it plus a bit extra.

I added the ability to create the relationships that had non standard table names and foreign keys, and also added some validations to the created models.

By default all relationships are created as has_many (and belongs_to), and tables can have multiple belongs_to referencing the same table so long as the foreign keys are different. Also models are created for every table found in the DDL.

I also added an yaml file as an override so you can specify habtm and has_one relationships too.

There are two files to this modification, a hacked version of the original dbmodel.rb and a new file which encapsulates the parsing of the DDL file. The main changes to the original dbmodel.rb consists of removing the parsing of the original xml file and reading the changes from a modified hash of the tables and associations.

The new ddl.rb file handles the parsing of the DDL file, and building a hash of data for the tables, with their associations and validations. It also reads the YAML file that overides the association types.

You can get a zip of the two files from this link ddl2model.zip.

Both files need to be in the same directory and it is run from the command line, the yaml override being in the same directory as the dbmodel.rb script. The ddl file to be processed is given on the command line.

The YAML file assocs.yml is used to tell the ddl.rb script about relations that are anything other than has_many, so:

extable1:
   - {:assoc: has_one, :ref: extable2, :column: extable1_id}
   - {:assoc: has_one, :ref: extable3, :column: extable1_id}
   - {:assoc: habtm, :ref: extable1_extable2, :column: extable1_id}
extable2:
   - {:assoc: habtm, :ref: extable1_extable2, :column: extable2_id}

Tells the parser that the model for a table named extable1 should create a has_one relation for extable2 using the foreign key extable1_id. Ditto for extable3.

It also specifies that the model for extable2 has a habtm relationship using the join table extable1_extable2 using foreign key extable1_id. Note that the habtm override needs to be specified for both tables, in this case extable2 also has a habtm override.

You can also ignore a table with this:

table3: []

will ignore the table called table3.

Lastly the generator will add validate_presence_of based on any NOT NULL constraints found on the column DDL.

Update

A better solution to this problem has been provided here http://db-discovery.rubyforge.org/