February 2, 2011 at 11:06Creating Doctrine YAML via MySQL Workbench
Recently I began to use the MySQL Workbench program. I was very pleased with the application; it is very convenient to design a database structure in it, which can then be easily exported to the Forward Engineering database server. You can also use reverse engineering (Reverse Engineering): the creation of ER diagrams on the existing structure of the Database. In addition, the application has synchronization of the model and the existing database, which is also very convenient during development.
But since we are developing our products using ORM Doctrine, in addition to the database structure, it was necessary to create models for accessing data. It turned out that there is an add-on for Workbench that creates Doctrine YAML right from the application. Having searched a bit on the internet, the plugin was successfully found. Developer page is here.
Using this plugin it is very convenient and quick to create circuits for future models.
Available stable versions can be found on this page . At the time of writing, the current version was 0.4.1 dated January 03, 2010. The latest development version is 0.4.2. I advise you to use it. Available at http://mysql-workbench-doctrine-plugin.googlecode.com/svn/trunk/DoctrineExport.grt.lua
After unpacking the archive you will find the file "DoctrineExport.grt.lua" - this is the necessary plug-in. Now you need to install it in MySQL Workbench. To do this, run the application and go to the “Scripting” menu - “Install Plugin / Module”, specify the file with the plugin. Do not forget to specify the type of the added plugin “lua files (* .lua)”. After that, the plugin will be successfully added.
We restart the Workbench application to activate the plugin.
In the next step, create a simple ER diagram (if you do not already have an existing connection to the Database server, create it in the “SQL Development” - “New Connection” section) To do this, after starting the application, in the “Data modeling” section, click “Create New EER Model. "
After opening a new window, you can either first create the necessary tables, and then go on to create an ER chart, or go straight to modeling and create tables in parallel. We will use the second method immediately. To do this, click on the “Add diagram" icon at the very top.
The design workspace opens.
Create tables and arrange the relationships between them. There is a table of users, groups and comments. Each user is included in a group (one-to-many relationship). Users can leave comments (one-to-many relationship). Do not forget that foreign keys are not possible when working with MyISAM. By default, MySQL Workbench works with InnoDB.
After designing, you can export the created structure to the database server using the “Database” - “Forward Engineer” menu.
To create a Doctrine YAML in the upper menu, open the section “Plugins” - “Catalog” - “Doctrine Export: Copy Generated Schema to Clipboard”. And the model circuit will go to the clipboard. Also, in this section of the menu, you can specify which file to upload data to.
As a result, we got such a data scheme (presented below). The plugin processed the tables correctly and created models without multiple endings. So if the table is called “users”, then the future model will be called “user” - this is very nice. In addition, when I created the model I needed, the plugin also correctly processed the name “categories” and created a scheme called “category”. And, as you can see, all the connections are arranged correctly. In the necessary classes, “foreignAlias” is immediately registered, Doctrine itself does not. She writes communications in each circuit separately.
The only thing that didn’t suit me was that the first letter of the models is lowercase, and we are working on the Zend notation, where the class name should start with a capital letter. But this problem was solved in version 0.4.2, which allows the use of various configuration variables. Now, by default, the first letter is capitalized.
To connect your configuration settings file, you must create the file "doctrinePluginConfig.lua", which must be placed in the directory "% PROGRAMFILES% / MySQL / Workbench / modules". The contents of the file may look like this. Details on configuration options can be found on this page. HowToUseConfigOptions
The official site in the Wiki describes some additional features of the plugin when working with Doctrine. For example, to add the Doctrine Email Validator, add Used Resources to the column name comment
YAML example for User model. UPD: Whoever gets an error with the getInfoFromTableComment function , then replace it with getCommentToken in the file "% APPDATA% \ MySQL \ Workbench \ modules \ DoctrineExport.grt.lua". Thanks worker4food
But since we are developing our products using ORM Doctrine, in addition to the database structure, it was necessary to create models for accessing data. It turned out that there is an add-on for Workbench that creates Doctrine YAML right from the application. Having searched a bit on the internet, the plugin was successfully found. Developer page is here.
Using this plugin it is very convenient and quick to create circuits for future models.
Available stable versions can be found on this page . At the time of writing, the current version was 0.4.1 dated January 03, 2010. The latest development version is 0.4.2. I advise you to use it. Available at http://mysql-workbench-doctrine-plugin.googlecode.com/svn/trunk/DoctrineExport.grt.lua
After unpacking the archive you will find the file "DoctrineExport.grt.lua" - this is the necessary plug-in. Now you need to install it in MySQL Workbench. To do this, run the application and go to the “Scripting” menu - “Install Plugin / Module”, specify the file with the plugin. Do not forget to specify the type of the added plugin “lua files (* .lua)”. After that, the plugin will be successfully added.
We restart the Workbench application to activate the plugin.
In the next step, create a simple ER diagram (if you do not already have an existing connection to the Database server, create it in the “SQL Development” - “New Connection” section) To do this, after starting the application, in the “Data modeling” section, click “Create New EER Model. "
After opening a new window, you can either first create the necessary tables, and then go on to create an ER chart, or go straight to modeling and create tables in parallel. We will use the second method immediately. To do this, click on the “Add diagram" icon at the very top.
The design workspace opens.
Create tables and arrange the relationships between them. There is a table of users, groups and comments. Each user is included in a group (one-to-many relationship). Users can leave comments (one-to-many relationship). Do not forget that foreign keys are not possible when working with MyISAM. By default, MySQL Workbench works with InnoDB.
After designing, you can export the created structure to the database server using the “Database” - “Forward Engineer” menu.
To create a Doctrine YAML in the upper menu, open the section “Plugins” - “Catalog” - “Doctrine Export: Copy Generated Schema to Clipboard”. And the model circuit will go to the clipboard. Also, in this section of the menu, you can specify which file to upload data to.
As a result, we got such a data scheme (presented below). The plugin processed the tables correctly and created models without multiple endings. So if the table is called “users”, then the future model will be called “user” - this is very nice. In addition, when I created the model I needed, the plugin also correctly processed the name “categories” and created a scheme called “category”. And, as you can see, all the connections are arranged correctly. In the necessary classes, “foreignAlias” is immediately registered, Doctrine itself does not. She writes communications in each circuit separately.
The only thing that didn’t suit me was that the first letter of the models is lowercase, and we are working on the Zend notation, where the class name should start with a capital letter. But this problem was solved in version 0.4.2, which allows the use of various configuration variables. Now, by default, the first letter is capitalized.
To connect your configuration settings file, you must create the file "doctrinePluginConfig.lua", which must be placed in the directory "% PROGRAMFILES% / MySQL / Workbench / modules". The contents of the file may look like this. Details on configuration options can be found on this page. HowToUseConfigOptions
local _G = _G
-- do not touch the three points, they are intended
module(...);
-- declare config and add it
-- to the global namespace
_G.extConfig = {
enableRecapitalizeTableNames = "none" –-Отключение первой заглавной буквы
}
The official site in the Wiki describes some additional features of the plugin when working with Doctrine. For example, to add the Doctrine Email Validator, add Used Resources to the column name comment
{doctrine:validators}
email: true
{/doctrine:validators}YAML example for User model. UPD: Whoever gets an error with the getInfoFromTableComment function , then replace it with getCommentToken in the file "% APPDATA% \ MySQL \ Workbench \ modules \ DoctrineExport.grt.lua". Thanks worker4food
---
detect_relations: true
options:
collate: utf8_bin
charset: utf8
type: InnoDB
User:
tableName: users
columns:
id:
type: integer(4)
primary: true
unsigned: true
notnull: true
autoincrement: true
name:
type: string(45)
login:
type: string(45)
email: true
password:
type: string(45)
group_id:
type: integer(4)
unsigned: true
relations:
group:
class: Group
local: group_id
foreign: id
foreignAlias: users
onDelete: cascade
foreignType: many
owningSide: true
indexes:
fk_users_groups:
fields: [group_id]