Development

ClayToSchema

You must first sign up to be able to contribute.

Version 4 (modified by anonymous, 12 years ago)
--

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.

  1. first the Eclipse/Clay couple. 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
  1. 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/
  1. 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 :

  1. 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 build-schema" from your Symfony project.
  1. 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
    
  • 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);
?>
  1. 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 pdv < ../data/sql/model_drop.sql
    cd ..
    symfony build-schema
    cd batch
    php culture_i18n.php
    cd ..
    symfony build-model
    symfony build-sql
    
  1. enjoy

Hope this will be useful to someone ... Ben

Attachments