Please add an example for a migration task

[steveh80]

Hi,

I just stumbled over your very nice project. I am very interested in it, particularly in the migration tasks. We are using simple sql files for migrations in combination with capistrano at the moment (just the up way). Your solution is maybe a better approach to that, but I actually cannot imagine how a implemented migration task would look like. Is it db-schema and data changes in php code? And how do you test it in development? Maybe you could add an example for that in your readme file?

Thanks Stephan

[ellisv]

In some cases you need not just plain SQL scripts for keeping your DB schema up-to-date, especially when dealing with multi-shops . I actually had to learn it in hard way. Before oxid console was open-sourced original migration queries (they were called upgrade queries back then) were plain SQL scripts and we had bad times writing them because we needed conditional queries or inject some variables. Well you might think that SQL smarty template would work too, but to make the most out of this we have introduced migration query as PHP object.

Here is a quick example of migration query which adds a column to oxuser table

<?php
// FILE: 20140414085723_adddemoculumntooxuser.php

class AddDemoCulumnToOxUserMigration extends oxMigrationQuery
{

    /**
     * {@inheritdoc}
     */
    public function up()
    {
        if ( $this->_columnExists( 'oxuser', 'OXDEMO' ) ) {
            return;
        }

        $sSql = "ALTER TABLE  `oxuser`
                 ADD  `OXDEMO`
                    CHAR( 32 )
                    CHARACTER SET utf8
                    COLLATE utf8_general_ci
                    NULL
                    DEFAULT NULL
                    COMMENT  'Demo field for migration'";

        oxDb::getDb()->execute( $sSql );
    }

    /**
     * {@inheritdoc}
     */
    public function down()
    {
        if ( !$this->_columnExists( 'oxuser', 'OXDEMO' ) ) {
            return;
        }

        oxDb::getDb()->execute( 'ALTER TABLE `oxuser` DROP `OXDEMO`' );
    }

    /**
     * Does column exist in specific table?
     *
     * @param string $sTable Table name
     * @param string $sColumn Column name
     *
     * @return bool
     */
    protected function _columnExists( $sTable, $sColumn )
    {
        $oConfig = oxRegistry::getConfig();
        $sDbName = $oConfig->getConfigParam( 'dbName' );
        $sSql    = 'SELECT 1
                    FROM information_schema.COLUMNS
                    WHERE
                        TABLE_SCHEMA = ?
                    AND TABLE_NAME = ?
                    AND COLUMN_NAME = ?';

        $oDb = oxDb::getDb();

        return (bool) $oDb->getOne( $sSql, array($sDbName, $sTable, $sColumn) );
    }
}

Maybe it is a good idea to have _columnExists() method in oxMigrationQuery class.

[ellisv]

For release v1.0.1 or later you do not need to define _columnExists() method as it is in oxMigrationQuery

[steveh80]

Great! Thanks! :+1: