/home/edulekha/studygroup.edulekha.com/ow_core/database.php
<?php
/**
* EXHIBIT A. Common Public Attribution License Version 1.0
* The contents of this file are subject to the Common Public Attribution License Version 1.0 (the “License”);
* you may not use this file except in compliance with the License. You may obtain a copy of the License at
* http://www.oxwall.org/license. The License is based on the Mozilla Public License Version 1.1
* but Sections 14 and 15 have been added to cover use of software over a computer network and provide for
* limited attribution for the Original Developer. In addition, Exhibit A has been modified to be consistent
* with Exhibit B. Software distributed under the License is distributed on an “AS IS” basis,
* WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for the specific language
* governing rights and limitations under the License. The Original Code is Oxwall software.
* The Initial Developer of the Original Code is Oxwall Foundation (http://www.oxwall.org/foundation).
* All portions of the code written by Oxwall Foundation are Copyright (c) 2011. All Rights Reserved.
* EXHIBIT B. Attribution Information
* Attribution Copyright Notice: Copyright 2011 Oxwall Foundation. All rights reserved.
* Attribution Phrase (not exceeding 10 words): Powered by Oxwall community software
* Attribution URL: http://www.oxwall.org/
* Graphic Image as provided in the Covered Code.
* Display of Attribution Information is required in Larger Works which are defined in the CPAL as a work
* which combines Covered Code or portions thereof with code not governed by the terms of the CPAL.
*/
/**
* Core database connection class.
*
* @author Sardar Madumarov <madumarov@gmail.com>, Nurlan Dzhumakaliev <nurlanj@live.com>
* @package ow_core
* @since 1.0
*/
final class OW_Database
{
//const DEFAULT_CACHE_LIFETIME = false;
const NO_CACHE_ENTRY = "ow_db_no_cache_entry";
/**
* @var array
*/
private static $classInstances;
/**
* Mysql connection object
*
* @var PDO
*/
private $connection;
/**
* Number of rows affected by the last SQL statement
*
* @var int
*/
private $affectedRows;
/**
* Logger data
*
* @var array
*/
private $queryLog;
/**
* Debug option
*
* @var boolean
*/
private $debugMode;
/**
* Enter description here...
*
* @var boolean
*/
private $isProfilerEnabled;
/**
* Enter description here...
*
* @var UTIL_Profiler
*/
private $profiler;
/**
* Last executed query
*
* @var int
*/
private $queryExecTime;
/**
* Enter description here...
*
* @var int
*/
private $totalQueryExecTime;
/**
*
* @var int
*/
private $queryCount;
/**
* @var boolean
*/
private $useCashe;
/**
* Getter for $log property
*
* @return array
*/
public function getQueryLog()
{
return $this->queryLog;
}
/**
* Getter for $queryExecTime property
*
* @return int
*/
public function getQueryExecTime()
{
return $this->queryExecTime;
}
/**
* @return int
*/
public function getTotalQueryExecTime()
{
return $this->totalQueryExecTime;
}
/**
* @return int
*/
public function getQueryCount()
{
return $this->queryCount;
}
/**
* @return string
*/
public function getQueryString()
{
return $this->queryString;
}
/**
* @return bool
*/
public function getUseCashe()
{
return $this->useCashe;
}
/**
* @param bool $useCashe
*/
public function setUseCashe( $useCashe )
{
$this->useCashe = (bool) $useCashe;
}
/**
* Constructor.
*
* @param array $params
*/
private function __construct( $params )
{
$port = isset($params['port']) ? (int) $params['port'] : null;
$socket = isset($params['socket']) ? $params['socket'] : null;
try
{
if ( $socket === null )
{
$dsn = "mysql:host={$params['host']};";
if ( $port !== null )
{
$dsn .= "port={$params['port']};";
}
}
else
{
$dsn = "mysql:unix_socket={$socket};";
}
$dsn .= "dbname={$params['dbname']}";
$this->connection = new PDO($dsn, $params['username'], $params['password'],
array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8;',
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
if ( !$this->isMysqlValidVersion() )
{
throw new InvalidArgumentException("Cant connect to database. Connection needs MySQL version 5.0 + !");
}
$this->prepareMysql();
if ( !empty($params['profilerEnable']) )
{
$this->isProfilerEnabled = true;
$this->profiler = UTIL_Profiler::getInstance('db');
$this->queryCount = 0;
$this->queryExecTime = 0;
$this->totalQueryExecTime = 0;
$this->queryLog = array();
}
if ( !empty($params['debugMode']) )
{
$this->debugMode = true;
}
$this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->useCashe = false;
}
catch ( PDOException $e )
{
throw new InvalidArgumentException($e->getMessage());
}
}
/**
* Returns the instance of class for $params
*
* @param array $params ( host, username, password, dbname, [socket], [port] )
*
* @return OW_Database
*
*/
public static function getInstance( $params )
{
if ( !isset(self::$classInstances) )
{
self::$classInstances = array();
}
ksort($params);
$connectionKey = serialize($params);
if ( empty(self::$classInstances[$connectionKey]) )
{
if ( !isset($params['host']) || !isset($params['username']) || !isset($params['password']) || !isset($params['dbname']) )
{
throw new InvalidArgumentException("Can't connect to database. Please provide valid connection attributes.");
}
self::$classInstances[$connectionKey] = new self($params);
}
return self::$classInstances[$connectionKey];
}
/**
* @param string $sql
* @param array $params
* @param int $cacheLifeTime
* @param array $tags
* @return mixed
*/
public function queryForColumn( $sql, array $params = null, $cacheLifeTime = 0, $tags = array() )
{
$dataFromCache = $this->getFromCache($sql, $params, $cacheLifeTime);
if ( $dataFromCache !== self::NO_CACHE_ENTRY )
{
return $dataFromCache;
}
$stmt = $this->execute($sql, $params);
$result = $stmt->fetchColumn(); // (PDO::FETCH_COLUMN);
$stmt->closeCursor();
if ( $result === false )
{
$result = null;
}
$this->saveToCache($result, $sql, $params, $cacheLifeTime, $tags);
return $result;
}
/**
* Enter description here...
*
* @param string $sql
* @param unknown_type $className
* @param array $params
* @return mixed
*/
public function queryForObject( $sql, $className, array $params = null, $cacheLifeTime = 0, $tags = array() )
{
$dataFromCache = $this->getFromCache($sql, $params, $cacheLifeTime);
if ( $dataFromCache !== self::NO_CACHE_ENTRY )
{
return $dataFromCache;
}
$stmt = $this->execute($sql, $params);
$stmt->setFetchMode(PDO::FETCH_CLASS, $className);
$result = $stmt->fetch();
$stmt->closeCursor();
if ( $result === false )
{
$result = null;
}
else
{
$result->generateFieldsHash();
}
$this->saveToCache($result, $sql, $params, $cacheLifeTime, $tags);
return $result;
}
/**
*
* @param string $sql
* @param string $className
* @param array $params
* @param int $cacheLifeTime
* @param array $tags
* @return array
*/
public function queryForObjectList( $sql, $className, array $params = null, $cacheLifeTime = 0, $tags = array() )
{
$dataFromCache = $this->getFromCache($sql, $params, $cacheLifeTime);
if ( $dataFromCache !== self::NO_CACHE_ENTRY )
{
return $dataFromCache;
}
$stmt = $this->execute($sql, $params);
$result = $stmt->fetchAll(PDO::FETCH_CLASS, $className);
foreach ( $result as $item )
{
$item->generateFieldsHash();
}
$this->saveToCache($result, $sql, $params, $cacheLifeTime, $tags);
return $result;
}
/**
* Set time zone
*
* @return void
*/
public function setTimezone()
{
$date = new DateTime;
$this->query('SET TIME_ZONE = ?', array(
$date->format('P')
));
}
/**
* @param string $sql
* @param array $params
* @param int $cacheLifeTime
* @param array $tags
* @return array
*/
public function queryForRow( $sql, array $params = null, $cacheLifeTime = 0, $tags = array() )
{
$dataFromCache = $this->getFromCache($sql, $params, $cacheLifeTime);
if ( $dataFromCache !== self::NO_CACHE_ENTRY )
{
return $dataFromCache;
}
$stmt = $this->execute($sql, $params);
$result = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt->closeCursor();
if ( $result === false )
{
$result = array();
}
$this->saveToCache($result, $sql, $params, $cacheLifeTime, $tags);
return $result;
}
/**
* Enter description here...
*
* @param string $sql
* @param array $params
* @return array
*/
public function queryForList( $sql, array $params = null, $cacheLifeTime = 0, $tags = array() )
{
$dataFromCache = $this->getFromCache($sql, $params, $cacheLifeTime);
if ( $dataFromCache !== self::NO_CACHE_ENTRY )
{
return $dataFromCache;
}
$stmt = $this->execute($sql, $params);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$this->saveToCache($result, $sql, $params, $cacheLifeTime, $tags);
return $result;
}
/**
* @param string $sql
* @param array $params
* @param int $cacheLifeTime
* @param array $tags
* @return type
*/
public function queryForColumnList( $sql, array $params = null, $cacheLifeTime = 0, $tags = array() )
{
$dataFromCache = $this->getFromCache($sql, $params, $cacheLifeTime);
if ( $dataFromCache !== self::NO_CACHE_ENTRY )
{
return $dataFromCache;
}
$stmt = $this->execute($sql, $params);
$result = $stmt->fetchAll(PDO::FETCH_COLUMN);
$this->saveToCache($result, $sql, $params, $cacheLifeTime, $tags);
return $result;
}
/**
* Enter description here...
*
* @param string $sql
* @param array $params
* @return int
*/
public function query( $sql, array $params = null )
{
$stmt = $this->execute($sql, $params);
$rowCount = $stmt->rowCount();
$stmt->closeCursor();
return $rowCount;
}
/**
* @param type $sql
* @param array $params
* @return int
*/
public function delete( $sql, array $params = null )
{
return $this->query($sql, $params);
}
/**
* insert data and return last insert id
*
* @param string $sql
* @param array $params
* @return int last_insert_id
*/
public function insert( $sql, array $params = null )
{
$stmt = $this->execute($sql, $params);
$lastInsertId = $this->connection->lastInsertId();
$stmt->closeCursor();
return $lastInsertId;
}
/**
* Enter description here...
*
* @param unknown_type $sql
* @param array $params
* @return unknown
*/
public function update( $sql, array $params = null )
{
return $this->query($sql, $params);
}
/**
* Insert object $obj to table $tableName. Returns last_insert_id
* throws InvalidArgumentException
*
* @param string $tableName
* @param object $obj
* @return int
*/
public function insertObject( $tableName, $obj, $delayed = false )
{
if ( $obj != null && is_object($obj) )
{
$params = get_object_vars($obj);
$paramNames = array_keys($params);
$columns = UTIL_String::arrayToDelimitedString($paramNames, ',', '`', '`');
$values = UTIL_String::arrayToDelimitedString($paramNames, ',', ':');
$sql = "INSERT" . ($delayed ? " DELAYED" : "") . " INTO `{$tableName}` ({$columns}) VALUES ({$values})";
return $this->insert($sql, $params);
}
else
{
throw new InvalidArgumentException('object expected');
}
}
/**
* @param string $tableName
* @param OW_Entity $obj
* @param string $primaryKeyName
* @return type
* @throws InvalidArgumentException
*/
public function updateObject( $tableName, $obj, $primaryKeyName = 'id', $lowPriority = false )
{
if ( $obj != null && is_object($obj) )
{
$params = get_object_vars($obj);
if ( !array_key_exists($primaryKeyName, $params) )
{
throw new InvalidArgumentException('object property not found');
}
$fieldsToUpdate = $obj->getEntinyUpdatedFields();
if ( empty($fieldsToUpdate) )
{
return true;
}
$updateArray = array();
foreach ( $params as $key => $value )
{
if ( $key !== $primaryKeyName )
{
if ( in_array($key, $fieldsToUpdate) )
{
$updateArray[] = '`' . $key . '`=:' . $key;
}
else
{
unset($params[$key]);
}
}
}
$updateStmt = UTIL_String::arrayToDelimitedString($updateArray);
$sql = "UPDATE" . ($lowPriority ? " LOW_PRIORITY" : "") . " `{$tableName}` SET {$updateStmt} WHERE {$primaryKeyName}=:{$primaryKeyName}";
return $this->update($sql, $params);
}
else
{
throw new InvalidArgumentException('object expected');
}
}
public function mergeInClause( array $valueList )
{
if ( $valueList === null )
{
return '';
}
$result = '';
foreach ( $valueList as $value )
{
$result .= ( '\'' . $this->escapeString($value) . '\',' ); //"'$value',"
}
$result = mb_substr($result, 0, mb_strlen($result) - 1);
return $result;
}
public function batchInsertOrUpdateObjectList( $tableName, $objects, $batchSize = 50 )
{
if ( $objects != null && is_array($objects) )
{
if ( count($objects) > 0 )
{
$columns = '';
$paramNames = array();
if ( is_object($objects[0]) )
{
$params = get_object_vars($objects[0]);
$paramNames = array_keys($params);
$columns = UTIL_String::arrayToDelimitedString($paramNames, ',', '`', '`');
}
else
{
throw new InvalidArgumentException('Array of objects expected');
}
$i = 0;
$totalInsertsCount = 0;
$objectsCount = count($objects);
$batchSize = (int) $batchSize;
$inserts = array();
foreach ( $objects as $obj )
{
$values = '(';
foreach ( $paramNames as $property )
{
if ( $obj->$property !== null )
{
$values .= ( '\'' . $this->escapeString($obj->$property) . '\',' );
}
else
{
$values .= 'NULL,';
}
}
$values = mb_substr($values, 0, mb_strlen($values) - 1);
$values .= ')';
$inserts[] = $values;
$i++;
$totalInsertsCount++;
if ( $i === $batchSize || $totalInsertsCount === $objectsCount )
{
$sql = "REPLACE INTO `{$tableName}` ({$columns}) VALUES" . implode(',', $inserts);
$inserts = array();
$i = 0;
$this->execute($sql)->closeCursor();
//$this->connection->query($sql)->closeCursor();
}
}
}
}
else
{
throw new InvalidArgumentException('Array expected');
}
}
/**
* Escapes SQL string
*
* @param string $string
* @return string
*/
public function escapeString( $string )
{
$quotedString = $this->connection->quote($string); // real_escape_string( $string );
return mb_substr($quotedString, 1, mb_strlen($quotedString) - 2); //dirty hack to delete quotes
}
/* * 206.123.0
* Returns affected rows
*
* @return integer
*/
public function getAffectedRows()
{
return $this->affectedRows;
}
/**
* Returns last insert id
*
* @return integer
*/
public function getInsertId( $seqname = null )
{
return $this->connection->lastInsertId($seqname);
}
/**
* Class destruct actions
*/
public function __destruct()
{
if ( isset($this->connection) )
{
$this->connection = null;
}
}
/**
* Returns current PDOStatement
*
* @return PDOStatement
*/
private function execute( $sql, array $params = null )
{
if ( $this->isProfilerEnabled )
{
$this->profiler->reset();
}
/* @var $stmt PDOStatement */
$stmt = $this->connection->prepare($sql);
if ( $params !== null )
{
foreach ( $params as $key => $value )
{
$paramType = PDO::PARAM_STR;
if ( is_int($value) )
$paramType = PDO::PARAM_INT;
elseif ( is_bool($value) )
$paramType = PDO::PARAM_BOOL;
$stmt->bindValue(is_int($key) ? $key + 1 : $key, $value, $paramType);
}
}
OW::getEventManager()->trigger(new OW_Event("core.sql.exec_query", array("sql" => $sql, "params" => $params)));
$stmt->execute(); //TODO setup profiler
$this->affectedRows = $stmt->rowCount();
if ( $this->isProfilerEnabled )
{
$this->queryExecTime = $this->profiler->getTotalTime();
$this->totalQueryExecTime += $this->queryExecTime;
$this->queryCount++;
$this->queryLog[] = array('query' => $sql, 'execTime' => $this->queryExecTime, 'params' => $params);
}
return $stmt;
}
/**
* Check if MySQL version is 5+
*
* @return boolean
*/
private function isMysqlValidVersion()
{
$verArray = explode('.', $this->connection->getAttribute(PDO::ATTR_SERVER_VERSION));
return intval($verArray[0]) >= 5;
}
/**
* Set additional MySQL server settings
*/
private function prepareMysql()
{
if ( $this->connection->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql' )
{
$verArray = explode('.', $this->connection->getAttribute(PDO::ATTR_SERVER_VERSION));
if ( intval($verArray[0]) == 5 && intval($verArray[1]) >=7 && intval($verArray[2]) >= 9 )
{
$this->connection->exec(' SET SESSION sql_mode = ""; ');
}
}
}
private function getCacheKeyForQuery( $query, $params )
{
return 'core.sql.' . md5(trim($query) . serialize($params));
}
private function cacheEnabled( $expTime )
{
return !OW_DEV_MODE && $this->useCashe && ( $expTime === false || $expTime > 0 );
}
/**
* @return OW_CacheManager
*/
private function getCacheManager()
{
return OW::getCacheManager();
}
private function getFromCache( $sql, $params, $cacheLifeTime )
{
if ( $this->cacheEnabled($cacheLifeTime) )
{
$cacheKey = $this->getCacheKeyForQuery($sql, $params ? $params : array());
$cacheData = $this->getCacheManager()->load($cacheKey);
if ( $cacheData !== null )
{
return unserialize($cacheData);
}
}
$data = OW::getEventManager()->call("core.sql.get_query_result", array("sql" => $sql, "params" => $params));
if ( is_array($data) && isset($data["result"]) && $data["result"] === true )
{
return $data["value"];
}
return self::NO_CACHE_ENTRY;
}
private function saveToCache( $result, $sql, $params, $cacheLifeTime, $tags )
{
if ( $this->cacheEnabled($cacheLifeTime) )
{
$cacheKey = $this->getCacheKeyForQuery($sql, $params ? $params : array());
$this->getCacheManager()->save(serialize($result), $cacheKey, $tags, $cacheLifeTime);
}
OW::getEventManager()->trigger(new OW_Event("core.sql.set_query_result",
array("sql" => $sql, "params" => $params, "result" => $result)));
}
}