1. The XCoreModel: an extended CActiveRecord
2. The XMigrator class
3. And, finnally, an actual application model:

This is an incipient implementation of application-driven database with Yii.

This only works on MySQL RDBMS

The aim of this tutorial is to show you how to write an extended CActiveRecord that will self-generate the corresponding database tables and columns.

This is especially useful when writing an app from scratch and having a lot of models. Your only concern should be writing model classes that extend the XCoreModel, and the app will generate the schema(I'm using 'X' as a class prefix, instead of the proposed 'E').

I'm using a class derived from CDbMigration to create and alter the database tables(based on model class name).

During development, you may add columns to the model definition, change column types(e.g. from integer to string), or change column ordering.

The system will reflect these changes into the database schema.

What it does ¶

• creates tables
• adds fields if tables exists and the model-defined fields are not present in the database schema
• changes field type if model-defined field type differs from database field type
• reorders fields to reflect the order in the XMigrator

  What it doesn't do ¶

• it does not rename the database table if you rename the model class
• it does not drop fields which are present in the schema, but are not defined in the model
• it does not handle indexes. there is logic in place to specify column definition with indexes, but it's not yet fully implemented (note the field definition 'target_class_id' => 'integer,index' in XMigrator)

  The XCoreModel: an extended CActiveRecord ¶

One thing to note here is the PHP 5.3+ late static binding, which allows us to get rid of the annoying requirement of writing a model() method for each of our model classes. This is achieved by determining the calling class with get_called_class(). As you'll see below, the XPostModel(our example model) does not have to implement a model() method.

<?php

class XCoreModel extends CActiveRecord {

    public static function model($className=__CLASS__) {
        $className = get_called_class();
        return parent::model($className);
    }

    function __construct($scenario='insert') {
        if (!$scenario)
            $this->migrate();
        return parent::__construct($scenario);
    }

    public function getColumnDefs($index=0) {
        return XMigrator::abstractColumns($this->getColumnGroups(), $index);
    }

    public function migrate() {
        return XMigrator::migrate($this);
    }

    public static function load($pk, $class='') {
        if (!$class)
            $class = get_called_class();
        $model = call_user_func(array($class, 'model'));
        return call_user_func_array(array($model, 'findByPk'), array($pk));
    }

}

The XMigrator class ¶

This handles the schema, as well as column groups definitions, which we can (re-)use in models.

<?php

class XMigrator {

    public static $migration = null;
    public static $schema_columns = null;
    public static $columns = array(
        'base' => array(
            'id' => 'pk',
            'target_class_id' => 'integer,index',
            'target_id' => 'integer,index',
            'creator_id' => 'integer,index',
            'owner_id' => 'integer,index',
            'editor_id' => 'integer,index',
            'status' => 'integer,index',
            'access' => 'integer,index',
            'score' => 'float',
            'ip_address' => 'string',
            'date_created' => 'datetime,index',
            'date_modified' => 'datetime,index',
            'notes' => 'text',
        ),
        'text' => array(
            'title' => 'string',
            'slug' => 'string',
            'content' => 'text',
            'raw_content' => 'text',
        ),
        'expiration' => array(
            'date_expires' => 'datetime,index',
            'date_renewed' => 'datetime,index',
        ),
        'timed_publishing' => array(
            'date_publish' => 'datetime,index',
            'date_unpublish' => 'datetime,index',
        ),
        'upload' => array(
            'client_name' => 'string',
            'server_name' => 'string',
            'extension' => 'string',
            'mime_type' => 'string',
            'size' => 'integer',
            'width' => 'integer',
            'height' => 'integer',
        ),
    );

    public static function migrationInstance() {
        return (self::$migration) ? self::$migration : (self::$migration = new XBaseMigration_1());
    }

    public static function migrate($model) {
        $m = XMigrator::migrationInstance();
//        echo $m->dbConnection->serverInfo;
        $table_names = $m->dbConnection->Schema->TableNames;
        //    XXX warning: any ActiveRecord magic __get will trigger a metadata call and form an infinite loop
        $column_defs = $model->getColumnDefs();
        $table_name = $model->tableName();
        if (!in_array($table_name, $table_names)) {
            $m->createTable($table_name, $column_defs, " DEFAULT CHARACTER SET = utf8");
        } else {
//            table exists, so check the columns
            $table = $m->dbConnection->Schema->getTable($table_name);
            $alters = 0;
            $additions = 0;
            foreach ($column_defs as $col_name => $col_type) {

                $schema_type = XMigrator::getColumnTypeFromSchema($table_name, $col_name);
//                if column does not exist, create it
                if ($schema_type == false) {
                    $m->addColumn($table_name, $col_name, $col_type);
                    $additions++;
                } else {
                    if ($schema_type != $col_type) {
//                column exists and is different, alter it        
                        $m->alterColumn($table_name, $col_name, $col_type);
                        $alters++;
                    }
                }
            }
            if ($alters || $additions) {
                //reorder columns
                XMigrator::reorderColumns($table_name, $column_defs);
                $model->refreshMetaData();
            }
        }
    }

    public static function abstractColumns($group_names, $index=0) {
        foreach ($group_names as $group_name) {
            foreach (self::$columns[$group_name] as $k => $f) {
                $foo = explode(',', $f);
                $ret[$k] = $foo[$index];
            }
        }
//        var_dump($ret);
//        die();
        return $ret;
    }

    public static function getSchemaColumns($table, $refresh=false) {
        if (!$refresh && self::$schema_columns !== null)
            return self::$schema_columns;
        $sql = 'SHOW COLUMNS FROM ' . $table;
        try {
            $table_columns = Yii::app()->db->createCommand($sql)->queryAll();
        } catch (Exception $e) {
            return false;
        }
        $ret = array();
        foreach ($table_columns as $col)
            $ret[$col['Field']] = $col;
        self::$schema_columns = $ret;
        return $ret;
    }

    public static function getColumnTypeFromSchema($table, $column_name) {
        $schema_columns = self::getSchemaColumns($table);
        if (!isset($schema_columns[$column_name]))
            return false;
        $schema_column = $schema_columns[$column_name];
        if ($schema_column['Key'] == 'PRI')
            return 'pk';
        $types = array_flip(Yii::app()->db->Schema->columnTypes);
//        var_dump($types);
        if (isset($types[$schema_column['Type']]))
            return $types[$schema_column['Type']];
        //unknown type
        return $schema_column['Type'];
    }

    public static function reorderColumns($table_name, $column_defs) {
        $schema_columns = self::getSchemaColumns($table_name, true);

//        var_dump(array_keys($schema_columns));

        $columns = array_keys($schema_columns);
        $ordering = array_flip($columns);
        $desired_columns = array_keys($column_defs);
        $desired_ordering = array_flip($desired_columns);
//        var_dump($ordering);
//        var_dump($desired_ordering);
        $k = 0;
        $count = count($column_defs);

        foreach ($desired_ordering as $name => $index) {
            if ($index !== $ordering[$name]) {
                $desired_prev_col = ($index > 0 ) ? $desired_columns[$index - 1] : null;
                $prev_col = ($ordering[$name] > 0) ? $columns[$ordering[$name] - 1] : null;
                echo "<br /> $name $index: " . $ordering[$name] . " $desired_prev_col $prev_col ";
                if ($prev_col !== $desired_prev_col) {
                    $pos = ($desired_prev_col) ? "AFTER `$desired_prev_col`" : 'FIRST';
                    $m = self::migrationInstance();
                    $schema_type = XMigrator::getColumnTypeFromSchema($table_name, $name);
                    $sql = "ALTER table `$table_name`
                            MODIFY COLUMN `$name` "
                            . $m->dbConnection->Schema->columnTypes[$column_defs[$name]]
                            . " $pos ";
                    $m->execute($sql);
                    self::reorderColumns($table_name, $column_defs);
                }
            }
        }
    }

}

class XBaseMigration_1 extends CDbMigration {
    
}

And, finnally, an actual application model: ¶

<?php

class XPostModel extends XBaseModel {
   
    public function getColumnGroups() {
        return array('base', 'text');
    }

}

With this code in place, calling

XPostModel::model();

will create a (case sensitive) XPostModel table in the database(Yii::app()->db), having the fields described in XMigrator::$columns['base'] and XMigrator::$columns['text'];

Thoughts for the future ¶

This is experimental code. It has, of course, room for improvement.

• maybe when refactoring a column is renamed. The system should be extended to handle this.
• the system should drop the columns which are not present in model definition
• the system should be extended to work with database systems other than MySQL
• ... and many more

Thank you for reading. Please let me know your opinions and improvement suggestions.