Wolfmans Howlings

A programmers Blog about Programming solutions and a few other issues

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/

Posted in Rails,Ruby  |  Tags rails,ddl,activerecord  |  5 comments

Comments

  1. Chris said on Tue Oct 17 08:39:56 -0700 2006
    Hey Wolfman,
    I have been looking for a way to automatically create the belongs_to relationships in the model, and have thankfully googled this post.

    I am having problems getting the model to update. I have done exactly what I think I need to do,

    >rake db:structure:dump

    which creates \db\development_structure.sql

    then I am running

    >ruby db\dbmodel.rb db\development_structure.sql
    =>Reading the DDL (db\development_structure.sql)

    then I look at the model, but nothing is changed.

    Am I missing something?
  2. wolfman said on Tue Oct 17 13:13:13 -0700 2006
    What database are you using? Right now it support postgresql, but I made a small update that will work with mysql, except for the validations.
    If you send me the development_structure.sql I can make sure I can parse it and send you any changes to ddl.rb. It sounds like ddl.rb is not reading the DDL properly you can check this by running ddl.rb directly on the .sql file, it should print out a list of tables it finds.
  3. hank said on Wed Oct 18 05:45:18 -0700 2006
    "wolfman said 145 days later:"

    This new "so many days later" craze is useful in the first few days following an original post, but as time goes on it becomes less and less useful. "time_from_now_in_words" is cool and all, but in the end it would be nice to tell at a glance whether or not a comment is 'stale'. As it stands, I need to scroll back up to the top, check the original post date, and add 145 days to that to figure out how long ago the comments were posted. This could be fixed by simply adding the boring old pre-Web2.0 date to the comment header.
  4. hank said on Wed Oct 18 05:48:36 -0700 2006
    also: with so many things in Rails being delineated by underscores, it seems ridiculous to be using blogging software that converts a word surrounded by underscores into italics.

    Usability is king.

    foo_on_you
    _foo_on_you
    what_the_hell_is_going_on_here?
  5. wolfman said on Wed Oct 18 09:40:27 -0700 2006
    Probably best to file a change request on the Typo blog, I agree having the date show up after say 30 days is probably a good idea, as for the _ italic problem that is just the textile markup, not much I can do about that other than disallow markup in comments, simply putting the variable with underscrores between backtics solves the problem...
    `this_is_not_italized`

(leave email »)