Ruby on Rails helper scripts for MySQL workbench!

I've been using DB Designer 4 for quite a while now and while it produced very pretty ER diagrams, it was a bit limited in functionality.

In particular it did not behave well with Rails style databases because of the following -

1. Table names are plural
2. Foreign keys are singular! E.g. a foreign key to table "Products" will be called "product_id"!
3. Primary keys are always "id", regardless of the table name.

These pecularities with the Rails db schemas made it impossible for DBDesigner to infer table relationships, and I simply made peace with that fact and used DBDesigner anyways (the lure of the pretty ER diagrams was too much!)

Well I recently discovered the "successor" to DBDesigner called MySQL Workbench. And amongst all the cool things it brings, the most important is its support for LUA scripting! Surely that can do some nifty things like add support for rails schemas! Alas a quick search on the net revealed no prior scripts I could use.

Now I'm knew zero percent Lua before today but a few minutes with the Lua cheat sheet got me up to speed! It is kind of like a mix between Python and C. I also found some handy prebuilt functions for Lua scripting here which really helped me understand the MySQL Workbench API (which is sadly enough mostly undocumented).

A few hours of hacking later I had the solution ready in the form of a script! Here are the steps to import a rails schema with all the relationships -

1. Download the script here.
2. Then run it by pressing Ctrl+Shift+R and selecting wb_library.lua from the location where you saved it.
3. Make sure there are no errors in the output window.
4. Reverse engineer your DB schema to get all the tables into an ER diagram. None of the tables will be related to each other die to the problems mentioned above. We'll fix that in a minute!
   
5. Select "view > advanced > GRT shell" to open the shell which will allow you to invoke functions from the script.
6. Now the semi-hard part. You need to get an object of your schema so that you can pass it to the script. Type the following in the shell - print(get_schema(1).name);. If this matches your database name then great! otherwise try print(get_schema(2).name); then print(get_schema(3).name); and so on till you find the index of your schema. (Remember that Lua is NOT zero indexed like usual programming languages. This means that there is no index 0). I'll assume that the correct index is 2.
   
7. Type the following in the shell - sc = get_schema(2). Where 2 is the number you got in the previous step. Now the variable sc points to the correct schema.
8. Now the final magic step! Type connect_all_rails_fk_relationships (sc); in the shell! Watch the magic happen!
   

It really does work! Maybe someday I will convert this to a plugin with a user friendly GUI!

PS: The script also includes a Lua port of the RoR inflectors (pluralise and singularise). Maybe those are useful independent of MySQL workbench. Check them out.