Development

ClayToSchema

You must first sign up to be able to contribute.

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.

Model creation

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

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

Hope this will be useful to someone ... Ben

Attachments