Difference between #12 and #11 of Application-driven database with CDbMigration and an extended CActiveRecord

unchanged
Title
Application-driven database with CDbMigration and an extended CActiveRecord
unchanged
Category
Others
unchanged
Tags
database, model
changed
Content
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]
<?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]
<?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,finally, an actual application model:
------------------


~~~
[php]
<?php

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

}
~~~
With this code in place, calling


~~~
[php]
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.**