Orite Blog

In the past few weeks we have been exploring with Yii Framework, and two of us have started porting some of our project to Yii Framework. One of the first few things that caught our attention is the CActiveRecord model classes, which require relational rules to describe relationships among database tables. We are glad that Yiic can help to create project scaffolds and create controller, model and CRUD, however it's not very practical to manually add all the relationship if your project has over 10 tables.

Our normal practice prefers to design database with relational diagram, so it not only sets out the table schemas but also visually demonstrates the relationship of database tables. However in this fast changing world, without the right tool most the relational diagram tends to out of sync with real production database. For most the of our project, we create ERD and generate database from the diagram in the design phase and later on in the development phase, most the developer choose to update the schema on the table directly, since that's quick, and most of them don't have the habit to update the ERD with relavant changes. You will feel lucky if your database design tool comes with reverse engineering, which can help you syn with your database at a later point still, otherwise you'll be purely rely on your bare eye and hand to syn among database table your model classes and the ERD.

So I believe when it comes to database design it should always be diagram driven, therefore if there's any changes need to be made, always update the digram first and carry on to your physical table and model class. Although we are still looking for good database migration tools to manage schema diffs. We have written a little script to help Yii Framework to work with MySQL Workbench (which we use to model MySQL databases), the purpose of the script is to batch create model classes for Yii Framework (although Yiic can generate models one by one, we want it happen at once and for all), and with the help of mwb file it can create the relational rules for the models, so there's no manual translation needed while you creating the models. Since it's a Yii shell command, we have named it 'mwbModelCommand'.

It's available for download at this Yii Extensions page, or Yii Framework mwbModelCommand (direct link)

Hope this script will save your time on relational model creation, if anyone have issues or suggestions mwbModelCommand please feel free to post back.

Wish List (Todo),
A tool (set) to stream line the database migration, so it would not only creates the models, but may also update (revert) models as well as database table schema at once, and data safe if possible. Please advice if you know something good out there.

When we were building a WordPress website for a Chinese community we found even the configuration in WordPress defined default charset as 'UTF-8', becase the datbase tables/fields are by default not in 'utf8_unicode_ci', it won't allow users to post in Chinese the characters all come up as ? marks.

So we decide to alter all the tables and fields for WordPress to UTF-8, which would fix the problem. However it's not easy to change all the fields one by one from one charset and collation to another. Then we have written the following php script to help converting the tables and all text fields to UTF-8.

To set the MySQL default charset and collation to UTF-8, so the tables created later without any charset or collation specification would be default to UTF-8, please add these two lines to your my.cnf file under [mysqld],
character-set-server=utf8
default-collation=utf8_unicode_ci
        

We recently updated our XAMPP on developers work stations to 1.7.0, since it incorporates the latest MySQL 5.1 and PHP 5.2.8 releases. However we dicovered an issue with the mysql libraries which prevents PDO from running properly. Come with the package there are two versions of mysql libraries, if you check xampp/php and xampp/apache/bin you will find one is named libmysql_5.0.51a on the files, the other is just libmysql.dll, all we did to fix the problem is replacing the library files with the 5.051a files in the directories mentioned before. Also in xampp/php/ext there're php_mysqli.dll and php_mysql.dll needs replacing too. Please make sure you stop Apache before replacing file under xampp/apache/bin This should fix the problem with PDO.