OpenBiz & Cubi

Hi,

a very nice feature of the PHP framework symfony is the automatic generation of all the files from a MySQL Workbench Model (mwb-file). This way you can model your database graphically and later export it into your database and even into your framework.

When I was using the OpenBiz eclipse plugin I got annoyed of doing all these clicks for generating the DataObjects and the Forms. So I thought about doing these steps automatically and here is the first version of my approach:

Since the .mwb is a zipped xml file, I guess XSLT is the right choice for this task (which unfortunately I had never used before).

So far I have only done the BizDataObj-s. What's left to do (and even way more):

• primary key recognition, at the moment the BizField for the first column of a table will always be renamed to "Id"
• data type recognition (will this be necessary?)
• foreign key propagation (to Joins/Refrences?)
• form generation
• generation of Sub/Ref-DOs for driven forms etc.

Here is a very small tutorial for Windows XP in case you want to try:

1.) Install the MySQL workbench 5 (http://dev.mysql.com/downloads/workbench/5.1.html) and model your database, save as mwb and forward engineer SQL CREATE script, if needed for database setup. Be careful to set the first column of each table as primary key.

2.) Rename a copy of the mwb-file to filename.zip and unzip it. You will find a document.mwb.xml file.

3.) Install a current saxon release (see http://saxon.sourceforge.net/#F9.1B), I chose the java implementation because I have a JRE installed.

4.) Put the mwb-obiz.xsl (coming at the end) together with the document.mwb.xml from step 2.) in the saxon installation directory (you can put them elsewhere but this way running saxon is easier to describe...).

5.) Open the mwb-obiz.xsl with notepad and replace the term "yourpackage" with your desired package name (as often as it appears).

6.) Open a command window and change the current directory to the saxon installation dir. Start the transformation with the following command line:

java -cp "C:\Program Files\saxon\saxon9.jar" net.sf.saxon.Transform -o output.html document.mwb.xml mwb-obiz.xsl

Hopefully this will generate a number of BO_ files in a newly created subdirectory named "yourpackage" under the saxon installation directory. Afterwards you can copy the files into your OB project directory and hopefully work with them.

This is very basic so far. So if anybody wants to improve - go on! Especially you - the XSLT expert amongst us, you should make the XSLT code conform, performant and readable - I'm sorry, but I can't do that! And of course the primary key recognition needs to be improved. Oh, and we should discuss the name prefixes/postfixes for the objects.

- Veit.

Here comes the mwb-obiz.xsl, please ignore the <CODE> tags:

<CODE>

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="demo.xsl"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes" method="text" omit-xml-declaration="no" name="text"/>
    <xsl:output method="html" indent="yes" name="html"/>
    <xsl:template match="/">

<xsl:for-each select="//value[@key='tables']/value[@struct-name='db.mysql.Table']">
    <xsl:variable name="filename" select="concat('yourpackage/BO_',value[@key='name'],'.xml')"/>
    <xsl:value-of select="$filename"/>
    <!-- Creating  -->
    <xsl:result-document href="{$filename}" format="text">
    <xsl:text>&lt;?xml version="1.0" encoding="UTF-8" standalone="no" ?&gt;
</xsl:text>
        <xsl:for-each select="value[@key='name']">
        <xsl:text>&lt;BizDataObj Class="BizDataObj" DBName="</xsl:text>
        <xsl:value-of select="//value[@key='schemata']/value[@struct-name='db.mysql.Schema']/value[@key='name']"/>
        <xsl:text>" Description="Add description of BizDataObj" IdGeneration="Identity" InheritFrom="" Name="BO_</xsl:text>
        <xsl:value-of select="."/>
        <xsl:text>" OtherSQLRule="" Package="yourpackage" SearchRule="" SortRule="" Table="</xsl:text>
        <xsl:value-of select="."/>
        <xsl:text>"&gt;
&lt;BizFieldList&gt;
</xsl:text>
        </xsl:for-each>
    <xsl:for-each select="value[@key='columns']/value[@struct-name='db.mysql.Column']">
        <xsl:text>&lt;BizField Column="</xsl:text>
        <xsl:value-of select="./value[@key='name']"/>
        <xsl:text>" Name="</xsl:text>
        <!-- Primary Index determination ??) <xsl:value-of select="if ../../value[@struct-name='db.mysql.Index'/isprimary=1] >  -->
        <xsl:if test="position() = 1" >
        <xsl:text>Id</xsl:text>
        </xsl:if>
        <xsl:if test="position() > 1" >
        <xsl:value-of select="./value[@key='name']"/>
        </xsl:if>
        <xsl:text>" Type="</xsl:text>
        <!-- <xsl:value-of select="./link[@key='simpleType']"/> -->
        <xsl:text>"/&gt;
</xsl:text>
    </xsl:for-each>
        <xsl:text>&lt;/BizFieldList&gt;
</xsl:text>
<xsl:text>&lt;TableJoins /&gt;
&lt;ObjReferences /&gt;
&lt;/BizDataObj>
</xsl:text>
</xsl:result-document>
</xsl:for-each>

</xsl:template>
</xsl:stylesheet>
 

</CODE>