Changing dbdeploy

I first learned about dbdeploy last year while watching Harrie Verveer talking at PfCongres about database version control. More recently he also wrote a blog post about it. At first I thought dbdeploy wasn’t interesting enough because of two limitations:

1. Dbdeploy could only handle numbering for patch numbers and would only apply patches based on the last applied patch number. Because at work we are using several branches in Subversion, patchnumbers would easily conflict with each other or patches wouldn’t be applied since the numbering would be too low.
2. Dbdeploy cannot handle PHP patches, it will only handle SQL patches.

At the Dutch PHP Conference (DPC) last May I attended a talk by Michiel Rook about Phing and dbdeploy and learned that it is all written in PHP. Afterwards I talked to Michiel about changing dbdeploy to fit my needs and he was very interested. That’s how it all got started and about two weeks after DPC I was rewriting dbdeploy to fit my needs. I only got to fixing the first limitation I mentioned, since PHP patches are less used and not as easy to add in dbdeploy.

To install and setup dbdeploy, I used this tutorial by Dave Marshall. It’s a very good article and following the instructions it’s very easy to get dbdeploy to work with patch numbering. To fix the problem I mentioned before, we are not using patch numbers, but timestamps instead. So instead of naming the patchfile something like 1-my-first-patchfile.sql, you should use something like 20110822221223-my-first-patchfile.sql.

To get this to work with dbdeploy, you need to change two things:

1. Use VARCHAR(20) for the change_number field in the changelog table in stead of BIGINT
2. Set the checkall attribute in the build.xml file to make sure that patchfiles are always executed (in order of timestamp ascending) even if the last patchfile that was applied has a newer timestamp than the patchfile that wasn’t applied already. Note: this attribute can also be used with patch numbers instead of timestamps.

An example of the build.xml file with the checkall attribute:

<?xml version="1.0" ?>
<project name="PurpleMonkey" basedir="." default="build">
 
    <!-- Sets the DSTAMP, TSTAMP and TODAY properties -->
    <tstamp/>
 
    <!-- Load our configuration -->
    <property file="./build.properties" />
 
    <!-- create our migration task -->
    <target name="migrate" description="Database Migrations">
        <!-- load the dbdeploy task -->
        <taskdef name="dbdeploy" classname="phing.tasks.ext.dbdeploy.DbDeployTask"/>
 
        <!-- these two filenames will contain the generated SQL to do the deploy and roll it back-->
        <property name="build.dbdeploy.deployfile" value="deploy/scripts/deploy-${DSTAMP}${TSTAMP}.sql" />
        <property name="build.dbdeploy.undofile" value="deploy/scripts/undo-${DSTAMP}${TSTAMP}.sql" />
 
        <!-- generate the deployment scripts -->
        <dbdeploy
            url="mysql:host=${db.host};dbname=${db.name}"
            userid="${db.user}"
            password="${db.pass}"
            checkall="true"
            dir="${build.dir}/db/deltas"
            outputfile="${build.dir}/${build.dbdeploy.deployfile}"
            undooutputfile="${build.dir}/${build.dbdeploy.undofile}" />
 
        <!-- execute the SQL - Use mysql command line to avoid trouble with large files or many statements and PDO -->
        <exec
            command="${progs.mysql} -h${db.host} -u${db.user} -p${db.pass} ${db.name} < ${build.dbdeploy.deployfile}"
            dir="${build.dir}"
            checkreturn="true" />
 
    </target>
</project>

Note: These changes are implemented in version 2.4.6 of Phing.

This entry was posted in PHP, Quality Assurance and tagged , , , . Bookmark the permalink.

17 Responses to Changing dbdeploy

  1. Thanks Sandy!

    Didn’t know that exists. I had a quick look at it, but I will look into it further.

  2. Sandy says:

    well, if you have question you know where to find me :)

    We currently use an ATK version of DbPatch for all our products, and this is a rewrite that doesn’t need ATK anymore :)

  3. joeni says:

    Thanks, worked for me.

    Had a “small problem” on 32bit systems, because dbdeploy uses the intval function. On 32bit systems this function is not compatibel with the datetimestamp used for the delta scripts: max is 2147483647.

  4. great share.
    btw i had the same problem coz i m on 32bit system

  5. Elisha says:

    Garcinia cambogia supplements, in many cases, contain hardly any Garcinia cambogia.

  6. Moshe says:

    When we first learned about this particular weight loss combo, our diet fad adnger zone went
    off right away.

  7. Elliot says:

    Most fluctuate between the 20-30% purity levels – so we monitored down the original ‘Garcinia
    Cambogia’ to comprehend the difference.

  8. According to the most famous bodybuilding community, it works via the amino acid solution 5-hydroxytryptophan, which is a direct precursor towards the
    so-called happy hormone” serotonin.

  9. Some recommend eating tons of protein while others stress restricting carbohydrates.

  10. Most Garcinia items are fortified with potassium
    to assist enhance some of the body’s functions.

  11. If you get a free 14 days of cancel it right after so that they cancel you and they can’t charge you another do it right they are a scam..

    go to gnc ok good luck.

  12. It’s hard to find experienced people about this subject, however, you sound like you
    know what you’re talking about! Thanks

  13. Hattie says:

    The faster that we convert our ZCD negative, the more likely it really is that LabDoor is around forever.

  14. I believe I’ll pick up a bottle or even two depending on the price, soon and am would like to know the price.

  15. cutebridal says:

    At least he has headphones A true jerk would have just pointed that jam box at you on full

Leave a Reply

Your email address will not be published. Required fields are marked *

*