<?
/** -------------------------------------------------------------------------------------*
* Version: 1.0 *
* License: http://phpwebpad.hafij.com @copyright from 2010 *
* ---------------------------------------------------------------------------------------*
* DEVELOPED BY *
* Mohammad Hafijur Rahman (Badal) *
* hide@address.com, hide@address.com *
* ------------------------------------------------------------------------------------ **/
/**
* The heart of the orm.
* This is the innermost layer of the mysql database.
*/
class Database {
private static $instance = null;
private static $db = null;
private static $createTable = false;
private static $onlyOnce = true;
private static $queryLog = array();
private static $inTransaction = false;
/**
* Singleton pattern. We always take a single instance of the database.
* If we are already connected to the database we dont try to connect
* again.
* @param string $host
* @param string $db
* @param string $username
* @param string $pass
* @return Database $database
*/
public static function getInstance($host, $db, $username, $pass) {
if(empty($host) || empty($db) || empty($username))
throw new Exception('Missing information to connect to database.');
if(self::$instance == null) {
self::$instance = new Database($host, $db, $username, $pass);
}
self::$db = $db;
return self::$instance;
}
/**
* Private contstructor.
* @param string $host
* @param string $db
* @param string $username
* @param string $pass
*/
private function __construct($host, $db, $username, $pass){
try {
mysql_connect($host, $username, $pass);
mysql_select_db($db);
} catch(Exception $e) {
throw $e;
}
}
/**
* Responsible for creating table from a table definition.
* @param TableDefinition $table
* @exception if dont have permission to create table.
* @return void.
*/
public static function createTable(TableDefinition $table) {
if(!self::getCreateTableMode())
throw new Exception('Dont have enough privilage to create table.');
if(!self::isTableExist($table->getTableName())) {
$sql = sprintf('CREATE TABLE %s(%s INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(%s))',
self::wrapName($table->getTableName()),
self::wrapName($table->getPrimaryKeyName()),
self::wrapName($table->getPrimaryKeyName())
);
self::query($sql);
self::addColumns($table);
self::addUniqueKeys($table);
//self::addTriggers($table);
} else if(!self::$onlyOnce) {
self::addColumns($table);
self::addUniqueKeys($table);
//self::addTriggers($table);
}
}
/**
* private function. Can only be used by createTable
* Responsible for creating table's column from a table definition.
* @param TableDefinition $table
*/
private static function addColumns(TableDefinition $table) {
$table_name = $table->getTableName();
$columns = $table->getColumns();
foreach($columns as $column) {
if(!self::isColumnExist($table_name, $column['name'])){
// Now we will check if the column type is nonNull or can
// except null.
$nonNull = $column['non_null'];
if($nonNull && $column['default'] === null) {
// Yes the column is nonNull and has default value null.
// so we dont add default value for this column.
$sql = sprintf('ALTER TABLE %s ADD COLUMN %s %s',
self::wrapName($table_name),
self::wrapName($column['name']),
$column['type']
);
} else {
// Yes the column can be nonNull and can not except null.
// Or the column is not nonNull and in that case we dont
// care if the default value is null or not null.
//
// Now some time we dont want to wrap the value for default.
// like for TIMESTAMP colum type if we want the default value
// CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP then we should
// not wrap the value.
$wrap_default = $column['wrap_default'];
$default = $column['default'];
if($wrap_default) {
$default = self::wrapValue($default);
}
$sql = sprintf('ALTER TABLE %s ADD COLUMN %s %s DEFAULT %s',
self::wrapName($table_name),
self::wrapName($column['name']),
$column['type'],
$default
);
}
self::query($sql);
}
}
$columns[] = array('name' => $table->getPrimaryKeyName());
$cols = self::getAllColumnNames($table_name);
foreach($cols as $c) {
$flag = true;
foreach($columns as $column) {
if($c == $column['name']) {
$flag = false;
break;
}
}
if($flag) {
$sql = sprintf('ALTER TABLE %s DROP COLUMN %s',
self::wrapName($table_name),
self::wrapName($c)
);
self::query($sql);
}
}
}
/**
* private function. Can only be used by createTable
* Responsible for creating unique key constraints.
* @param TableDefinition $table
*/
private static function addUniqueKeys(TableDefinition $table) {
$table_name = $table->getTableName();
$uniques = $table->getUniqueKeys();
foreach($uniques as $u) {
$name = $u['name'];
$cols = $u['cols'];
if(!self::isConstraintExist($table_name, $name)) {
$columns = "";
foreach($cols as $c) {
$columns = $columns. self::wrapName($c) . ", ";
}
$columns = rtrim($columns, ", ");
if(!empty($columns)) {
$sql = sprintf('ALTER TABLE %s ADD CONSTRAINT %s UNIQUE (%s)',
self::wrapName($table_name),
self::wrapName($name),
$columns
);
self::query($sql);
}
}
}
$un = self::getAllConstraintNames($table_name);
foreach($un as $u) {
$flag = true;
foreach($uniques as $uniq){
if($u == $uniq['name']) {
$flag = false;
break;
}
}
if($flag && $u != 'PRIMARY') {
$sql = sprintf('ALTER TABLE %s DROP INDEX %s',
self::wrapName($table_name),
self::wrapName($u)
);
self::query($sql);
}
}
}
/**
* Not emplemented yet.
* @param TableDefinition $table
* @return void
*/
private function addTriggers(TableDefinition $table) {
return;
$table_name = $table->getTableName();
$triggers = $table->getTriggers();
foreach($triggers as $t) {
$name = $t['name'];
$time = $t['before'] ? 'BEFORE' : 'AFTER';
$event = $t['event'];
$sql = $t['sql'];
}
}
/**
* runs mysql_query and add the query to the query log.
* @param string $sql
* @exception if the given query is empty.
* @return resultset array
*/
public static function query($sql) {
if(empty($sql)) throw Exception('Query can not be empty');
self::$queryLog[] = $sql;
$result = mysql_query($sql);
if(!$result) throw new Exception(mysql_error());
return $result;
}
/**
* return all the table name from the database.
* @return array
*/
public static function getAllTableNames() {
$list = mysql_list_tables(self::getDbName());
$tables = array();
while($row = mysql_fetch_array($list)) {
$tables[] = $row[0];
}
return $tables;
}
/**
* Return all the column names of the table.
* @param string $table_name
* @exception if the table name does not exist.
* @return array
*/
public static function getAllColumnNames($table_name) {
if(!self::isTableExist($table_name))
throw new Exception('Table does not exist.');
$list = mysql_list_fields(self::getDbName(), $table_name);
$columns = array();
for ($i = 0; $i < mysql_num_fields($list); $i++) {
$columns[] = mysql_field_name($list, $i);
}
return $columns;
}
/**
* Return all the constraints name and type.
* @param string $table_name
* @exception if the table does not exist.
* @return array
*/
public static function getAllConstraintNames($table_name) {
if(!self::isTableExist($table_name))
throw new Exception('Table does not exist.');
$sql = sprintf('SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
TABLE_SCHEMA = schema() AND TABLE_NAME = %s',
self::wrapValue($table_name)
);
$rs = self::query($sql);
$constraint = array();
while($row = mysql_fetch_array($rs)) {
$constraint[] = $row[2];
}
return $constraint;
}
/**
* Return the database name.
* @return string
*/
public static function getDbName() {
return self::$db;
}
/**
* Check whether the table exist in the database or not.
* @param string $table_name
* @exception if the given table name is empty.
* @return bool
*/
public static function isTableExist($table_name) {
if(empty($table_name))
throw new Exception('Table name can not be empty');
$tables = self::getAllTableNames();
return in_array($table_name, $tables);
}
/**
* Check whether the column exist in the table or not.
* @param string $table_name
* @param string $column_name
* @exception if the given table name or column name is empty.
* @return bool
*/
public static function isColumnExist($table_name, $column_name) {
if(empty($table_name) || empty($column_name))
throw new Exception('Table or column name can not be empty');
$columns = self::getAllColumnNames($table_name);
return in_array($column_name, $columns);
}
/**
* Return whether the constraint exist or not.
* @param string $table_name
* @param string $constraint_name
* @exception if the table or constraint name is empty
* @return bool
*/
public static function isConstraintExist($table_name, $constraint_name) {
if(empty($table_name) || empty($constraint_name))
throw new Exception('Table or constraint name can not be empty');
$constraints = self::getAllConstraintNames($table_name);
return in_array($constraint_name, $constraints);
}
/**
* start a mysql transaction.
* @throws Exception if the transaction already been started.
*/
public static function startTransaction() {
if(self::$inTransaction)
throw new Exception('Can not start new transaction.');
self::query('START TRANSACTION');
self::$inTransaction = true;
}
/**
* commit a mysql transaction.
* @throws Exception if no transaction has been started.
*/
public static function commit() {
if(!self::$inTransaction)
throw new Exception('Can not commit without a transaction.');
self::query('COMMIT');
self::$inTransaction = false;
}
/**
* rollback a mysql transaction.
* @throws Exception if no transaction has been started.
*/
public static function rollback() {
if(!self::$inTransaction)
throw new Exception('Can not rollback without a transaction.');
self::query('ROLLBACK');
self::$inTransaction = false;
}
/**
* Return if we have any transaction open or not.
* @return bool
*/
public static function isInTransaction() {
return self::$inTransaction;
}
/**
* if $bool true then table can be created automatically.
* if $onlyOnce true then table will be created for the very
* first time. Otherwise the table will be altered every time when
* we make any changes to the tabledefinition.
* We set $onlyOnce to false when we are in developement mode.
* @param bool $bool
* @param bool $onlyOnce
*/
public static function setCreateTableMode($bool = false, $onlyOnce = true) {
self::$createTable = $bool;
self::$onlyOnce = $onlyOnce;
}
/**
* return the create table mode.
* @return bool
*/
public static function getCreateTableMode() {
return self::$createTable;
}
/**
* return a quoted name. We use the quoted name for
* the table or column name.
* @param string $name
* @return string
*/
public static function wrapName($name) {
return '`'.$name.'`';
}
/**
* Return a quated value string. We use this quoted string when
* we insert data to the database.
* @param string $value
* @return string
*/
public static function wrapValue($value) {
return "'".$value."'";
}
/**
* Escape special character in a given string
* @param string $str
* @return string
*/
public static function escapeString($str) {
return mysql_real_escape_string($str);
}
/**
* Escape html special characters.
* @param string $html
* @return string
*/
public static function escapeHTML($html) {
return htmlspecialchars($html);
}
/**
* List of $class_name objects
* @param string $class_name
* @param string | array $where
* @param string $orderBy
* @param int $page
* @param int $limit
* @return List of $class_name objects
*/
public static function loadAll($class_name, $where = null, $orderBy = null,
$page = 1, $limit = null) {
$obj = new $class_name();
$table_name = $obj->getTableDefinition()->getTableName();
$where_clause = "";
if(is_string($where)) {
$where_clause = "WHERE ".$where;
} else if(is_array($where)) {
foreach($where as $key => $value) {
$where_clause = $where_clause.self::wrapName($key)." = ".
self::wrapValue($value)." AND ";
}
if(!empty($where_clause))
$where_clause = "WHERE ".rtrim($where_clause, 'AND ');
} else if(empty($where)) {
// its ok.
} else {
throw new Exception('WHERE clause is not valid.');
}
$orderby_clause = "";
if(is_string($orderBy)) {
$orderby_clause = "ORDER BY ".$orderBy;
} else if(empty($orderBy)) {
// its ok.
} else {
throw new Exception('ORDER BY clause is not valid.');
}
$limit_clause = "";
if(!empty($limit)) {
$offset = ($page - 1) * $limit;
$limit_clause = "LIMIT ".$offset.", ".$limit;
}
$sql = sprintf('SELECT * FROM %s %s %s %s',self::wrapName($table_name),
$where_clause, $orderby_clause, $limit_clause);
$rs = self::query($sql);
$list = self::rsToObjects($class_name, $rs);
return $list;
}
/**
* Return a $class_name object
* @param string $class_name
* @param string | array $where
* @param string $orderBy
* @return $class_name objects || null if the object can not be found.
*/
public static function loadOnly($class_name, $where = null, $orderBy = null) {
$list = self::loadAll($class_name, $where, $orderBy, 1, 1);
if(empty($list)) return null;
return $list[0];
}
/**
* Return an object of type $class_name
* @param string $class_name
* @param int $id
* @return $class_name object
*/
public static function loadById($class_name, $id) {
if(empty($id))
throw new Exception('Id can not be empty');
$obj = new $class_name();
$pk = $obj->getTableDefinition()->getPrimaryKeyName();
$table_name = $obj->getTableDefinition()->getTableName();
$sql = sprintf('SELECT * FROM %s WHERE %s = %s', self::wrapName($table_name),
self::wrapName($pk), self::wrapValue($id)
);
$rs = self::query($sql);
$list = self::rsToObjects($class_name, $rs);
if(count($list) != 1)
throw new Exception('Object not found');
$obj = $list[0];
return $obj;
}
/**
* Return the total number of objects with the given where clause.
* @param string $class_name
* @param string | array $where
* @return int
*/
public static function countAll($class_name, $where) {
$obj = new $class_name();
$table_name = $obj->getTableDefinition()->getTableName();
$where_clause = "";
if(is_string($where)) {
$where_clause = "WHERE ".$where;
} else if(is_array($where)) {
foreach($where as $key => $value) {
$where_clause = $where_clause.self::wrapName($key)." = ".
self::wrapValue($value)." AND ";
}
if(!empty($where_clause))
$where_clause = "WHERE ".rtrim($where_clause, 'AND ');
} else if(empty($where)) {
// its ok.
} else {
throw new Exception('WHERE clause is not valid.');
}
$sql = sprintf('SELECT COUNT(*) c FROM %s %s',self::wrapName($table_name), $where_clause);
$rs = self::query($sql);
$row = mysql_fetch_array($rs);
return $row['c'];
}
/**
*
* @param string $class_name
* @param array $rs
* @return List of object type $class_name
*/
public static function rsToObjects($class_name, $rs) {
$list = array();
while($row = mysql_fetch_array($rs)) {
$obj = new $class_name();
$pk = $obj->getTableDefinition()->getPrimaryKeyName();
$obj->setPrimaryKey($row[$pk]);
$cols = $obj->getTableDefinition()->getColumns();
foreach($cols as $c) {
$obj->$c['name'] = $row[$c['name']];
}
$list[] = $obj;
}
return $list;
}
/**
* Insert the model to the database and return its id.
* @param Model $model
* @return int id of this new inserted model.
*/
public static function store(Model $model) {
$table_name = $model->getTableDefinition()->getTableName();
$pk = $model->getTableDefinition()->getPrimaryKeyName();
$properties = $model->getAllProperties();
$cal = "";
$val = "";
foreach($properties as $prop => $value) {
if($prop != $pk) {
$cal = $cal.self::wrapName($prop).", ";
$val = $val.self::wrapValue($value).", ";
}
}
$cal = rtrim($cal, ", ");
$val = rtrim($val, ", ");
$sql = sprintf('INSERT INTO %s (%s) VALUES(%s)', self::wrapName($table_name),$cal, $val);
self::query($sql);
$lastId = mysql_insert_id();
if(empty($lastId))
throw new Exception('Could not store object.');
return $lastId;
}
/**
* Update the model in database.
* @param Model $model
*/
public static function update(Model $model) {
$table_name = $model->getTableDefinition()->getTableName();
$pk = $model->getTableDefinition()->getPrimaryKeyName();
$pk_value = null;
$properties = $model->getAllProperties();
$update = "";
foreach($properties as $prop => $value) {
if($prop != $pk) {
$update = $update . self::wrapName($prop) . " = " . self::wrapValue($value) . ", ";
} else {
$pk_value = $value;
}
}
if(empty($pk_value)) throw new Exception('Primary key value not found.');
$update = rtrim($update, ", ");
if(!empty($update)) {
$sql = sprintf('UPDATE %s SET %s WHERE %s = %s',
self::wrapName($table_name),
$update,
self::wrapName($pk),
self::wrapValue($pk_value)
);
self::query($sql);
}
}
/**
* Delete all the rows with the given where condition.
* @param string $class_name
* @param array | string $where
*/
public static function deleteAll($class_name, $where = null) {
$obj = new $class_name();
$table_name = $obj->getTableDefinition()->getTableName();
$where_clause = "";
if(is_string($where)) {
$where_clause = "WHERE ".$where;
} else if(is_array($where)) {
foreach($where as $key => $value) {
$where_clause = $where_clause . self::wrapName($key) . " = " .
self::wrapValue($value) . " AND ";
}
if(!empty($where_clause))
$where_clause = "WHERE " . rtrim($where_clause, 'AND ');
} else if(empty($where)) {
// its ok.
} else {
throw new Exception('WHERE clause is not valid.');
}
$sql = sprintf('DELETE FROM %s %s',self::wrapName($table_name),
$where_clause
);
self::query($sql);
}
/**
* Return the query log.
* @return array
*/
public static function getQueryLog() {
return self::$queryLog;
}
}
?>