When I first started to build a symfony project the first thing I tried to do was to gather all the right tools for my development to be as easy as in the Java/Eclipse world. Therefore I've come up with the following IDE:
One of the great feature in Propel is it's ability to create the schema.xml file from a creole/db connection. With this feature, designing a db with a graphical tool (like clay) and then generating the SQL and revert it with Propel was easy but... Clay doesn't generate DROP TABLE IF EXISTS (it only generates DROP TABLE, which is an annoying when refactoring) and neither Clay nor Propel knows about I18N, which is a Symfony addon to Propel. I came up with a little script for a whole refactoring cycle from Clay to Symfony model.
- Install Eclipse. Eclipse can be found at http://www.eclipse.org (or you should prefer the PHP packaged version of trustudio http://www.xored.com/trustudio). There is a very good wiki page about PHP/Eclipse with symfony : SymfonyWithEclipseBasedEditors
- Add the Clay plugin from : http://www.azzurri.jp/en/software/clay/index.jsp. The best way to install it, is through the install/update of Eclipse explained here : http://www.azzurri.jp/eclipse/plugins/
- You are ready to build your first model ''' Create a file in your Eclipse project : Right click on the folder where you would like your file to be created, then New -> Other -> Database Modeling and Azzurri Clay Database Design Diagram. A model is created and you can build your model. Once done, the goal is to generate the SQL from your model. Use right click on the background then Generate SQL, choose the destination file and Next, and check the following items :
- Once generated and if you have no need for I18N-tables or onUpdate/onDelete then just dump the SQL in the database and call "symfony propel-build-schema" from your Symfony project.
- But for those who are more interested in a complete cycle :
- onUpdate/onDelete : when you create a foreign key relation in Clay, just doubleclick on the relation and you can see two fields named ON DELETE and ON UPDATE which you can change to "NO ACTION" and "CASCADE" - those will be translated by Propel!
The DROP IF EXIST thing : When SQL is generated just use the sed unix-tool to replace DROP TABLE with DROP TABLE IF EXISTS. This is done with:
sed -e 's/DROP TABLE/DROP TABLE IF EXISTS/' YourClayFile.sql > YourModifiedClayFile.sql
Note: Today 22/05/2008, i use clay and his tools for generate SQL , and it give me a : DROP TABLE IF EXISTS scheme.mytable; .. just that.
Then what about the I18N? - To generate the correct I18N tags we are going to use one simple assertion : any tableA which is used as foreign key in another tableB which has a CULTURE column is I18N. Then the best way to transform an XML file into a new modified XML-file is with XSLT and guess what, PHP has libxslt! So we came up with this little 'culture_i18n.xsl' xsl-stylesheet :
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="@*|node()"> <xsl:copy> <xsl:apply-templates select="@*|node()"/> </xsl:copy> </xsl:template> <xsl:template match='database'> <xsl:copy> <xsl:attribute name='noxsd'>true</xsl:attribute> <xsl:apply-templates select="@*|node()"/> </xsl:copy> </xsl:template> <xsl:template match="table"> <xsl:variable name="tableName" select='@name'/> <xsl:copy> <xsl:for-each select="//table[(column/@name='culture' or column/@name='CULTURE') and foreign-key/@foreignTable=$tableName]"> <xsl:attribute name="isI18N">true</xsl:attribute> <xsl:attribute name="i18nTable"><xsl:value-of select="@name"/></xsl:attribute> </xsl:for-each> <xsl:apply-templates select="@*|node()"/> </xsl:copy> </xsl:template> <xsl:template match="column[translate(@name,'culture','CULTURE')='CULTURE']"> <xsl:copy> <xsl:attribute name="isCulture">true</xsl:attribute> <xsl:apply-templates select="@*|node()"/> </xsl:copy> </xsl:template> </xsl:stylesheet>
which can be used with a 'culture_i18n.php' file :
<?php $xslt = new xsltProcessor; $xslt->importStyleSheet(DomDocument::load('culture_i18n.xsl')); $out = $xslt->transformToXML(DomDocument::loadXML(file_get_contents('../config/schema.xml'))); file_put_contents('../config/schema.xml',$out); ?>
Putting it all together
At last a little 'refactoring.sh' script in your project "batch" folder to put all this together :
sed -e 's/DROP TABLE/DROP TABLE IF EXISTS/' ../data/sql/model.sql > ../data/sql/model_drop.sql mysql -uroot your_database < ../data/sql/model_drop.sql cd .. symfony build-schema cd batch php culture_i18n.php cd .. symfony build-model symfony build-sql
- HowToApplyTransformationsToSchema -- Custom Pake task to apply xsl transformation with the symfony executable
Hope this will be useful to someone ... Ben