historical-db Yii Components for managing historical database records.


  1. Introduction
  2. Requirements
  3. Installation
  4. Features
  5. Usage
  6. Known issues and other comments
  7. Code
  8. License

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

5 0
Yii Version: 1.1
License: BSD-2-Clause
Category: Database
Developed by: yiier
Created on: Aug 5, 2012
Last updated: 11 years ago

Related Extensions