Symfony introspecting ERD generated SQL or Exporting ERD to YML directly?

We want to export Entity Relational Diagram (ERD) from MySQL Workbench to Symfony format in YAML or which is typically the same we want to end up building our Object Oriented Database Model.

Here is the ERD:

There are two approaches:

1. From ERD to YML conversion:

The plugin below is 2 years old now but it works well so far (2010):
http://trac.symfony-project.org/wiki/SymfonyYamlMyqlWorkbenchPlugin
The other problem I found with this old plugin is that is geared towards propel and not doctrine. It generated the yml from my ERD diagram which is what I wanted but for propel. It has a drawback, besides being old, it does not support something related to
i18. The path to save a file when generating the yml is relative to the user’s ~ directory. So DISCARDED.

Previous approach is different than this one here
http://github.com/johmue/mysql-workbench-schema-exporter
in that the latter is php code to generate the yml from the mwb file.
The same plugin has also a lua format that can be run from the Workbench program from the catalog plugin menu. However, the drawback of this method is that it is still experimental and not ready for production. It is better than the menu plugin in which is free to use php class code and that makes it neat to streamline into a work flow for symfony. I had to first fork the repository on github as it was read only. Then clone my copy of the repository with:
[code]
git clone git@github.com:cordoval/mysql-workbench-schema-exporter.git
[/code]
After that I run some tests and the only thing I needed to do is to:
[code]
php doctrine1.yaml.php > output.yml
pico output.yml (remove the html table tags that has at the beginning and at the end)
[/code]
The code seemed to be of pretty good quality.

Starting with the yml generated thus far, the commands that we need to issue to build our Object Oriented Database Model is:

[code]
symfony doctrine:build-model
[/code]

or

[code]
symfony doctrine:build-all
[/code]

2. From ERD to SQL and then from SQL to YML or direct Object Oriented Database Model conversion:

Workbench has a export function which generates what they call a SQL Forward Engineering Create Script for building database tables. Once we have generate this SQL script it seems that we can use symfony doctrine:build-schema using this SQL script. So starting from the ERD we can generate the SQL required to build this same Object Oriented Database Model. Symfony will introspect the database for us and do the job.

So after we have run the sql code into phpmyadmin we thereby create the database.
Once we have the database and no yml schema on symfony’s side we run the following command to get the yml file (config/doctrine/schema.yml):
[code]
./symfony doctrine:build-schema
>> doctrine generating yaml schema from database
>> doctrine Generate YAML schema successfully from database
[/code]

Now let’s turn to comparing the results between the plugin results and the symfony generate results from the SQL script.

This is a portion generated by introspection:
[code]
Affiliate:
connection: doctrine
tableName: Affiliate
columns:
id:
type: integer(4)
fixed: false
unsigned: false
primary: true
autoincrement: false
url:
type: string(45)
fixed: false
unsigned: false
primary: false
notnull: false
autoincrement: false
email:
type: string(45)
fixed: false
unsigned: false
primary: false
notnull: false
autoincrement: false
token:
type: string(45)
fixed: false
unsigned: false
primary: false
notnull: false
autoincrement: false
is_active:
type: string(45)
fixed: false
unsigned: false
primary: false
notnull: false
autoincrement: false
created_at:
type: string(45)
fixed: false
unsigned: false
primary: false
notnull: false
autoincrement: false
relations:
CategoryHas_Affiliate:
local: id
foreign: affiliate_id
type: many
[/code]

And this is a portion generated directly from the ERD:
[code]
Affiliate:
columns:
id:
type: integer(4)
primary: true
notnull: true
url:
type: string(45)
email:
type: string(45)
token:
type: string(45)
is_active:
type: string(45)
created_at:
type: string(45)
options:
charset: utf8
type: InnoDB
[/code]

And this is what the corresponding portion of yml by hand would look like:

[code]
JobeetAffiliate:
actAs: { Timestampable: ~ }
columns:
url: { type: string(255), notnull: true }
email: { type: string(255), notnull: true, unique: true }
token: { type: string(255), notnull: true }
is_active: { type: boolean, notnull: true, default: 0 }
relations:
JobeetCategories:
class: JobeetCategory
refClass: JobeetCategoryAffiliate
local: affiliate_id
foreign: category_id
foreignAlias: JobeetAffiliates
[/code]

My conclusion is to create the database from an ERD and then create the yml file.
Reason is that some of the relations are missing from the script generated as you can see by comparing the outputs above.

2 thoughts on “Symfony introspecting ERD generated SQL or Exporting ERD to YML directly?

  1. This is really cool. I am working on Ubuntu 12.04. i copied the SymfonyYmlExport.grt.lua file to workbench/modules/ folder. Hpw can i check plugin is enabled or not. how to export yml file. can you explain in detail.

Leave a Reply

Your email address will not be published. Required fields are marked *