Yii 1.1: historical-db

Yii Components for managing historical database records.


Yii Components for managing historical database records.


historical-db is a collection of components for Yii that allow for automatic creation of historical database records at the application layer. ActiveRecord and DAO are both supported.


  • Yii 1.1 or above
  • PHP 5.2+
  • MySQL 5+


  • Create a new separate historical DB to correspond to the dbHistorical component below.
  • Then, in main.php, you will need two main DB connections inside 'components':
        // ...
            'connectionString'=>'mysql:host=localhost;dbname=database_name',  // Edit
            'username'=>'database_username',  // Edit
            'password'=>'database_password',  // Edit
            'connectionString'=>'mysql:host=localhost;dbname=historical_database_name',  // Edit
            'username'=>'historical_database_username',  // Edit
            'password'=>'historical_database_password',  // Edit
  • Also in main.php, in 'params':
        // ...
        'historicalDbPrefix' => 'z',


  • historical-db provides most of what can be described as Type 4 Slowly Changing Dimensions.
  • Each record stores the user_id that performed the action, action (INSERT, UPDATE, DELETE), and time of creation.
  • Historical tables can be automatically created from your existing schema via subclass of CDbMigration.
  • Mechanism for creating and maintaining your historical tables are provided in CDbMigration subclass.
  • CActiveRecord has been subclassed with mechanisms to seamlessly create historical records.
  • CDbCommand has been subclassed with new methods to handle INSERT, UPDATE, and DELETE with automatic historical record creation.
  • All historical record creation occurs at the application layer. historical-db assumes that you do not have any mechanisms at the database layer (such as triggers) to create historical database records.
  • You are responsible for ensuring that the mechanisms in historical-db are not bypassed. Doing so will break the integrity of your slowly changing dimensions.
  • Subclass provided to support nested transactions in PDO.
  • MySQL 5+ only at this point, hopefully generic in a future release.
  • MySQL InnoDB and UTF-8 is assumed for all historical tables.



  • Create a historical table from an existing table: HistoricalDbMigration::createHistoricalTable()
  • TODO: Finish docs for HistoricalDbMigration


  • HistoricalActiveRecord has deactivated a few methods for now, rest will work seamlessly
  • TODO: Finish docs for HistoricalActiveRecord


  • HistoricalDbCommand now expects you to not use execute() for INSERT, UPDATE, DELETE.
  • Please use the normal 'db' connection for creating historical records. dbHistorical is primarly used internally for migrations and actually creating the historical records.
  • Please instead use HistoricalDbCommand::executeHistoricalInsert(), HistoricalDbCommand::executeHistoricalUpdate(), HistoricalDbCommand::executeHistoricalDelete() instead of execute().
  • HistoricalDbCommand::insert(), HistoricalDbCommand::update(), HistoricalDbCommand::delete() all support historical record creation.
  • TODO: Finish docs for HistoricalDbCommand

Known issues and other comments

  • historical-db is very much a work in progress, and probably not yet fully functional/reliable in the current form. USE AT YOUR OWN RISK!
  • At this time, historical-db has many design warts and pitfalls for generic use; it was originally (quickly) designed and implemented for a specific project.
  • Certain methods are quite slow; HistoricalActiveRecord:updateAll() for instance. Certain methods contain unnecessary redundancies at this time.
  • Goal is to refactor the code, clean it up, and make it reusable.
  • Your contributions and/or comments/suggestions are greatly appreciated.


Get the code at GitHub


Modified BSD License https://github.com/gtcode/historical-db

Total 2 comments

#9740 report it
yiier at 2012/09/07 03:42pm
re: ??????


Thanks for your comment.

Per the introduction and features list, the use is to create Type 4 Slowly Changing Dimensions for your database. Please let me know if you have any specific questions.

#9739 report it
Sephiroth at 2012/09/07 03:34pm

should be more specific to the use of this tool

Leave a comment

Please to leave your comment.

Create extension
No downloadable files yet