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:

• TruStudio
• Subclipse (SVN)
• JSEditor (Javascript)
• Clay Azzurri (DB design)

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.

Model creation

• 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!

Model transformation

Drop tables

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

i18n

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

• Enjoy

See also

• HowToApplyTransformationsToSchema -- Custom Pake task to apply xsl transformation with the symfony executable

Hope this will be useful to someone ... Ben