Hard code db schema

This is not an essential feature as you can implement it yourself, fairly easily, but as with all things, wouldn’t it be nice if I could switch it on and Yii magically handle it for me. Like the many other things it does.

The implementation we created feels a little bit tacked on at the moment which is why I thought I would mention a possible feature request.

So the feature: It would be nice to have the addition of an option to hard code the table schema. This could be an option, similar to doctrine, where you can define the schema, perhaps in the active record class.

Perhaps a function named schema, that returns an array of the database columns etc. This shifts the focus of defining the schema to be controlled by the application, rather than interpreted from the database. Then it would be very easy to write an install script that simply installs the tables that do not exist, the active record classes themselves could take care of this.

This is great for applications that are moved around and installed a lot.

I have had a quick play by creating a Class that extends CActiveRecord.




    public static function install($className){

        $t = new $className(null);

	$exists = Yii::app()->getMyDb()->getSchema()->getTable($t->tableName());

	$realTable = $t->getRealTableName();

	if(!$exists){ 

            Yii::app()->getMyDb()->createCommand()->createTable(

                $realTable,

                $t->schema()

            );

        }else{

            // adds columns that dont exist in the database

            $schema = $t->schema();

            $missingCols = array_diff(array_keys($schema), array_keys($exists->columns));

            foreach($missingCols as $col){

                Yii::app()->getMyDb()->createCommand()->addColumn($realTable, $col, $schema[$col]);

            }

        }

        $t->onAfterInstall(new CEvent($t));

    }



This also adds in any columns that are not in the database, and ignores columns that have been deleted (it won’t actively delete columns from the database that have not been defined in the schema function).

This is ok, but now it would be good if the DB component used this definition to populate its schema also, with this set up the DB component will still query the database to build the schema information. Obviously it’s not a problem as when caching is on it does not affect performance, it does feel a little messy having it defined in two places as is the case with my rough implementation… just an idea…