Pure SQL, Yii Active Record, or Mixed

Trying to sort through the advantages or disadvantag开发者_运维技巧es of the different approaches. Really not looking for opinions, rather what these different approaches provide or limit.

Yii claims that using their AR simplifies the DB programming but I am more concerned with "meshing" the DB with my code too much. I obviously know they work together, but the mapping is a bit of a concern. I want as many controls and constraints to exist withing the DB and wonder if getting away from pure SQL might limit performance in anyway.

ActiveRecord is good for any system with pretty straight-forward ORM where Model == Table, with a 1-to-1 relationship. In other words, if all of your data objects fit in single tables (perhaps with a few extra tables relating them) ActiveRecord is for you. And you can usually design your software so this is the case!

But you are correct, it does "mesh" the DB implementation and code a bit. It is assumed that Model == Table, meaning that the database implementation is NOT separate from your code. ActiveRecord breaks down a bit if you have complex Models stored in multiple tables. But that is a rather involved argument I won't get in to. Other patterns like Data Mapper give you more flexibility, but they require more work to code initially.

ActiveRecords is set-it-and-forget-it. SO easy and fast. No tedious coding of getters and setters, just nice PHP objects built from your tables. I built a pretty large app with Yii recently and I love the speed and simplicity of ActiveRecord. I am working on an app in Zend now, and even though I can see how their way gives you more control I miss the ease of AR. ;)

Yii ActiveRecord definitely simplifies programming against the database, as of course any decent ActiveRecord implementation does.

Mapping to the db

Regarding the mapping between database and your models, there is no issue that your code will "take over" your database. The only thing you are forced to maintain always up-to-date is this:

/**
 * @return string the associated database table name
 */
public function tableName()
{
    return 'my_table';
}

Seeing as table names won't change that often, it's not a practical concern.

There are other parts of your ActiveRecord models that should stay in sync with your schema:

• The phpdoc comments describing the properties of the model class
• Other public functions that implement various bits of functionality, such as validation rules [public function rules()], foreign model relations [public function relations()], attribute labels [public function attributeLabels()] etc.

However, you can still use the models and the database itself even if these do not reflect your current database schema -- it's simply that the corresponding bits of functionality will no longer work.

These pieces of data do not enforce your database constraints, they merely help you by allowing you to access functionality in a more intuitive manner. The actual constraints are still implemented at the database.

Performance

Yii implements a query builder which you can use to generate SQL from fluent expressions (your models use the same mechanism when queried directly). The builder generates SQL from the fluent expressions and the result will not be any slower than if you executed the equivalent SQL directly.

Of course there will be some overhead from using the query builder itself, but that is a tradeoff which pretty much all modern programming languages have chosen to make (LINQ would be the most massive example here) because it's totally worth it.

In any case, you can write SQL queries yourself by using CDbCommand. I don't see a reason for doing that, but the option is always there if you need it. Therefore there is no chance that you will be left hanging by deficiencies in ActiveRecord no matter what you need to do.