Using DbUpdater with MySql

DbUpdater can be used with mysql.

Here are the files you need to jumpstart the integration – DbUpdater-MySql.zip

1. You might need to change the path to mysql.exe in mysql-exec.bat.

2. Modify values in mysql-sample-command-line.bat.

Make sure mysql.data.dll is placed in GAC or in the same directory as DbUpdater.exe. Connector binaries can be downloaded from here –
http://dev.mysql.com/downloads/connector/net/6.1.html

Database Schema Version Control Tool

It is essential to treat database schema as source code and apply all the norms and best practices applicable to it. From what I hear and read, database schema version control is non-existent in too many projects.

Various techniques to accomplish version control of database schemas have been discussed and published. Martin Fowler and Pramod Sadagale has written a comprehensive article on Evolutionary Database Design. It is a must-read for everyone involved with database development.

K. Scott Allen writes in detail about an excellent system here – Versioning Databases – Change Scripts. This approach is sufficiently light-weight (for my taste) and provides complete control over the whole process.

I am providing a free tool here that you can use to implement this system. It will even create the SchemaVersionsLog table for your database, if it doesn’t exist. If you are using this tool with MSSQL Server, you can get started with very little effort. If you are using a different DBMS, you can still use this tool. It uses NHibernate to access the SchemaVersionsLog table. So, any DBMS supported by NHibernate is acceptable, as long as it comes with a command line tool to execute the sql scripts.

Download DbUpdater here.

Leveraging XPATH Axes

XPATH axes come in very handy when nodes need to be selected based on (attribute’s or child element’s) values that must match those of other nodes elsewhere in the xml tree. Consider the following log4net configuration file :


<?xml version="1.0"?>

<configuration>
    <configSections>
        <section name="nhibernate"
            type="System.Configuration.NameValueSectionHandler, System,
            Version=2.0.0.0,Culture=neutral, PublicKeyToken=b77a5c561934e089" />
        <section name="log4net"
            type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />
    </configSections>
    <nhibernate>
        <add key="hibernate.connection.provider"
            value="NHibernate.Connection.DriverConnectionProvider" />
        <add key="hibernate.connection.driver_class"
            value="NHibernate.Driver.SqlClientDriver" />
        <add key="hibernate.connection.connection_string"
            value="Server=servername; Database=dbname;
            User=username; Password=secret;" />
        <add key="hibernate.connection.isolation" value="ReadCommitted" />
        <add key="hibernate.dialect"
            value="NHibernate.Dialect.MsSql2000Dialect" />
    </nhibernate>

    <log4net>
        <appender name="NHibernateFileLog"
        type="log4net.Appender.RollingFileAppender">
            <file value="Logs/nhibernate.txt" />
            <appendToFile value="true" />
            <rollingStyle value="Size" />
            <maxSizeRollBackups value="10" />
            <maximumFileSize value="100KB" />
            <staticLogFileName value="true" />
            <layout type="log4net.Layout.PatternLayout">
                <conversionPattern
                value="%d{HH:mm:ss.fff} [%t] %-5p %c - %m%n"  />
            </layout>
        </appender>

        <appender name="GeneralLog"
            type="log4net.Appender.RollingFileAppender">
            <file value="Logs/general.txt" />
            <appendToFile value="true" />
            <maximumFileSize value="100KB" />
            <rollingStyle value="Size" />
            <maxSizeRollBackups value="5" />
            <layout type="log4net.Layout.PatternLayout">
                <conversionPattern
                value="%d{HH:mm:ss.fff} [%t] %-5p %c - %m%n"  />
            </layout>
        </appender>
        <appender name="DataLog"
            type="log4net.Appender.RollingFileAppender">
            <file value="Logs/data.txt" />
            <appendToFile value="true" />
            <maximumFileSize value="100KB" />
            <rollingStyle value="Size" />
            <maxSizeRollBackups value="5" />
            <layout type="log4net.Layout.PatternLayout">
                <conversionPattern
                value="%d{HH:mm:ss.fff} [%t] %-5p %c - %m%n"  />
            </layout>
        </appender>

        <!-- levels: DEBUG, INFO, WARN, ERROR, FATAL -->

        <root>
            <level value="DEBUG"/>
            <appender-ref ref="GeneralLog" />
        </root>

        <logger name="NHibernate" additivity="false">
            <level value="DEBUG"/>
            <appender-ref ref="NHibernateFileLog"/>
        </logger>
        <logger name="Pushable.Data" additivity="false">
            <level value="DEBUG"/>
            <appender-ref ref="DataLog"/>
        </logger>
    </log4net>
</configuration>


The <appender>s are referenced by <logger>s using ‘ref’ attribute of their ‘appender-ref’ child elements, using the same value as the ‘name’ attribute of the <appender>. This type of linking is quite common in xml documents. Now, The first question is : How to list files that are referenced by <logger>s ?
In order to solve this problem you will need to reach the <appender>s that are referenced by each <logger> and then extract the ‘value’ of their <file>s.


Solution:

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                version="1.0">

    <xsl:output method="text"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="log4net">
        <xsl:apply-templates
            select="appender[@name=parent::log4net/logger/appender-ref/@ref]"/>
    </xsl:template>

    <xsl:template match="appender">
        <xsl:value-of
            select="file/@value"/> <xsl:text>&#10;</xsl:text>
    </xsl:template>

</xsl:stylesheet>


The second question is : How to write an xslt script to transform this xml document into an html document with a table that looks like this –

NHibernate Logs/nhibernate.txt
Pushable.Data Logs/data.txt

Solution:

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                version="1.0">

    <xsl:output method="html"/>

    <xsl:template match="log4net">
        <HTML>
            <HEAD>
                <TITLE>www.tewari.info : using xpath axes</TITLE>
            </HEAD>
            <BODY>
                <TABLE border="1" width="25%">
                    <TR><TH>Logger</TH><TH>File</TH></TR>
                    <xsl:apply-templates select="logger"/>
                </TABLE>
            </BODY>
        </HTML>
    </xsl:template>

    <xsl:template match="logger">
            <xsl:variable name="aName" select="appender-ref/@ref"/>
            <TR>
            <TD><xsl:value-of select="@name"/></TD>
            <TD><xsl:value-of
                select="parent::log4net/appender[@name=$aName]/file/@value"/></TD>
            </TR>
    </xsl:template>

</xsl:stylesheet>


Credits :

  • SketchPath is a brilliant tool to play with xpath. I love the “New Context” button : Click on a node and hit “New Context” to change the context for the xpath query processor !