How to make customizable Db Table prefixes using ActiveRecord

Note: As of Yii 1.1.x this functionality is integrated into the framework. If you are using 1.1.x please disregard this article and use the built-in functionality.

Note: The method described on this page involves extending Core classes to create custom functionality. This method is best used when starting new projects, but can be implemented after a product has been developed. It's just more work to do it later on.

Yii's ActiveRecord classes are powerful, but they fall short when you want to have a configurable table prefix for your application.

A configurable table prefix comes in handy if you have an application that is being built around having many instances running on the same database server.

This tutorial describes a 2 step method, that requires very little code, and achieves the required effect with little extra overhead.

Step 1

Tip: Make sure that the new classes we create are in a directory that is imported by Yii, otherwise the application will crash.

Our first step is to add custom functionality to the active record to force all child classes to use our method of table prefixing.

To do this, we use the following logic:

  1. Add an application paramater where we are storing the prefix
  2. Create a new ActiveRecord class that all our Models will inherit
  3. Since the function tableName is used to find the table associated with the model we are going to force all child classes to use our customized tableName method
  4. Create a new method for the child classes to implement instead of tableName()

All this can be achieved in our new ActiveRecord class.

First add the new config item to config/main.php

'params' => array(
	'tablePrefix' => 'app_'

Next, create the new ActiveRecord class for our customized functionality.


class MyActiveRecord extends CActiveRecord
	// Cache for table prefix so we don't have excessive overhead constantly retrieving it
	protected $tbl_prefix = null;

	 * Force the child classes to use our table name prefixer
	final public function tableName()
		// If we haven't retrieved the table prefix yet
		if ($this->tbl_prefix === null)
			// Fetch prefix
			$this->tbl_prefix = Yii::app()->params['tablePrefix'];
		// Prepend prefix, call our new method
		return ($this->tbl_prefix . $this->_tableName());
	 * Function for child classes to implement to return the table name associated with it
	protected function _tableName()
		// Call the original method for our table name stuff
		return parent::tableName();

As you can see, the tableName function is now final, so no child classes can use it. However, they can now implement a protected function named _tableName(). Our new final tableName() function calls the _tableName() function to get the table name associated with our model and appends that to the system-wide table prefix. In this way, all existing code that interfaces with the ActiveRecord content continues to work without modifications, and the changes we just made are hidden from them.

Note: I chose to use a class variable to cache the table prefix, so that it is only fetched once from the application. This also gives you the ability to override this prefix in a child class to use a different prefix then the system-wide prefix.

If you want to have a new model that does not have a table prefix, all you need to do is override the variable in your child class with an empty string.

Step 2

Now that we have our ActiveRecord object set up, it's time to implement it with all of our models. This is fairly simple.

With an existing model (either already created or newly created with yii scaffolding) there are two simple find an replace statements to run.

<strong>Statement 1</strong>

Find: "extends CActiveRecord"
Replace: "extends MyActiveRecord"
<strong>Statement 2</strong>

Find: "public function tableName()"
Replace: "protected function _tableName()"

And now, without further ado (except maybe modifying your database tables to have the table prefix) your application is ready to go.

And, by all means, if you know of a better way of doing any of these steps, please say so.

And a big shout out to jonah for his ideas. They helped to simplify this and remove an unnecessary step.

0 0
1 follower
Viewed: 20 124 times
Version: 1.1
Category: Tutorials
Written by: killermonk
Last updated by: killermonk
Created on: Aug 9, 2009
Last updated: 10 years ago
Update Article


View all history