historical-db Yii Components for managing historical database records.

historical-db

  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.

Introduction

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.

Requirements

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

Installation

  • 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':
'components'=>array(
		// ...
		'db'=>array(
			'pdoClass'=>'HistoricalDbPDO',
			'class'=>'HistoricalDbConnection',
			'logHistorical'=>true,
			'connectionString'=>'mysql:host=localhost;dbname=database_name',  // Edit
			'emulatePrepare'=>true,
			'username'=>'database_username',  // Edit
			'password'=>'database_password',  // Edit
		),
		'dbHistorical'=>array(
			'pdoClass'=>'HistoricalDbPDO',
			'class'=>'HistoricalDbConnection',
			'logHistorical'=>false,
			'connectionString'=>'mysql:host=localhost;dbname=historical_database_name',  // Edit
			'emulatePrepare'=>true,
			'username'=>'historical_database_username',  // Edit
			'password'=>'historical_database_password',  // Edit
		),
	),
  • Also in main.php, in 'params':
'params'=>array(
		// ...
		'historicalDbPrefix' => 'z',
	),

Features

  • 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.

Usage

HistoricalDbMigration
  • Create a historical table from an existing table: HistoricalDbMigration::createHistoricalTable()
  • TODO: Finish docs for HistoricalDbMigration
HistoricalActiveRecord
  • HistoricalActiveRecord has deactivated a few methods for now, rest will work seamlessly
  • TODO: Finish docs for HistoricalActiveRecord
HistoricalDbCommand
  • 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.

Code

Get the code at GitHub

License

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

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

Related Extensions