File: /var/www/limestate-api/lib/dblib/DbPgsqlDriver.php
<?php
class Db_Pgsql_Driver implements iDb_Driver
{
/** @var PDO[] */
private static $_connections = array();
/** @var string|null последний сервер к которому происходил запрос */
private static $_last_server = null;
private static $_config = array();
private static $_log = array();
private static $_write_log = false;
private static $_active_transaction = 0;
/**
* Подключение к базе данных
*
* @param string $server сервер БД read|write
* @return PDO подключение к базе
* @throws PDOException в случае ошибки подключения
*/
private static function connect($server = null)
{
if ($server === null) {
return null;
}
try {
if (isset(self::$_connections[$server]) && self::$_connections[$server] instanceof PDO) {
self::$_last_server = $server;
return self::$_connections[$server];
}
$server_config = isset(self::$_config[$server]) ? self::$_config[$server] : self::$_config;
/** @var self::$_connections PDO */
self::$_connections[$server] = new PDO(
'pgsql:host=' . $server_config['host']
. (isset($server_config['port']) ? ';port=' . $server_config['port'] : '')
. ';dbname=' . $server_config['database'],
$server_config['username'],
$server_config['password'],
array(PDO::ATTR_EMULATE_PREPARES => true)
);
/** @var $sth PDOStatement */
//$sth = self::$_connections[$server]->query('SET NAMES ' . self::$_config[$server]['charset']);
//$sth->execute();
} catch (PDOException $e) {
throw new PDOException($e->getMessage());
}
self::$_last_server = $server;
return self::$_connections[$server];
}
/**
* Выполняет запрос в БД и возвращает результат
*
* @param string $function название ф-ции из которой происходит вызов
* @param string $query строка запроса
* @param string $_method метод, из которого происходит вызов запроса
* @param string $server сервер БД read|write
* @return mixed
* @throws Exception в случае не выполнения запроса
*/
private function querySelect($function, $query, $_method = '', $server = null)
{
$query = trim((string) $query);
$isSelect = (mb_stripos($query, 'select') === 0);
$isProcedure = ($isSelect && mb_stripos($query, 'from') === false);
$returning = (mb_stripos($query, 'returning') !== false);
$innerTransaction = (!$isSelect && !self::$_active_transaction);
if (self::$_active_transaction || $isProcedure) {
$server = Db_Driver::SERVER_WRITE;
} else if ($server === null) {
$server = $isSelect ? Db_Driver::SERVER_READ : Db_Driver::SERVER_WRITE;
}
if (!($dbh = $this->connect($server))) {
throw new Exception();
}
try {
$logId = self::writeLog($server, $query, $_method);
if ($innerTransaction) {
$dbh->beginTransaction();
}
$timeStart = microtime(true);
$sth = $dbh->query($query);
if (!$sth) {
$err = $dbh->errorInfo();
throw new Exception($_method . ' <br>' . $err[2] . '<br><br>' . $query, $err[1]);
}
$result = null;
if ($isSelect || $returning) {
if ($function === 'fetchCol') {
$result = $sth->fetchAll(PDO::FETCH_COLUMN, 0);
} elseif ($function === 'fetchAll') {
$result = $sth->fetchAll(PDO::FETCH_OBJ);
} elseif ($function === 'fetchOne') {
$result = $sth->fetchColumn();
} elseif ($function === 'fetchRow') {
$result = $sth->fetchObject();
} elseif ($function === 'fetchRow') {
$result = $sth->fetchAll(PDO::FETCH_ASSOC);
} elseif ($function === 'fetchAssoc') {
$temp = $sth->fetchAll(PDO::FETCH_ASSOC);
$result = array();
foreach ($temp as $row) {
$result[reset($row)] = (object) $row;
}
} else if ($function === 'insert') {
$result = $sth->fetchAll(PDO::FETCH_COLUMN, 0);
} else {
$result = $sth->fetchAll(PDO::FETCH_ASSOC);
}
}
self::changeLog($logId, 'time', number_format((microtime(true) - $timeStart),3,',',''));
if ($innerTransaction) {
$dbh->commit();
}
} catch (PDOException $e) {
if ($innerTransaction) {
$dbh->rollBack();
}
throw new Exception($e->getMessage() . $query);
}
return $isSelect || $returning ? $result : $sth->rowCount();
}
/**
* Запись запроса в лог
*
* @param string $server тип сервера
* @param string $query строка запроса
* @param string $_method метод, из которого происходит вызов запроса
* @return int индекс записи в логе
*/
private static function writeLog($server, $query, $_method = '')
{
if (!self::$_write_log) {
return false;
}
array_push(self::$_log, array('type' => $server, 'query' => $query, 'method' => $_method, 'time' => 0));
return count(self::$_log) - 1;
}
/**
* Изменение записи в логе
*
* @param int $logId индекс записи
* @param string $param параметр
* @param mixed $value значение
*/
private static function changeLog($logId, $param, $value)
{
if (!self::$_write_log) {
return;
}
if (isset(self::$_log[$logId])) {
self::$_log[$logId][$param] = $value;
}
}
public function getLog()
{
return self::$_log;
}
public function select()
{
return new Db_Pgsql_Select(func_get_args());
}
public function init($config = array())
{
self::$_config = $config;
$this->connect(Db_Driver::SERVER_READ);
return $this;
}
public function begin($server = Db_Driver::SERVER_WRITE)
{
if (!self::$_active_transaction) {
self::$_last_server = $server;
$this->connect($server)->beginTransaction();
}
self::$_active_transaction++;
}
public function commit()
{
self::$_active_transaction--;
if (self::$_active_transaction <= 0 && self::$_last_server !== null) {
$this->connect(self::$_last_server)->commit();
}
}
public function rollback()
{
if (self::$_active_transaction > 0 && self::$_last_server !== null) {
self::$_active_transaction = 0;
$this->connect(self::$_last_server)->rollBack();
}
}
public function query($query, $server = null)
{
if (self::$_last_server !== null && $this->connect(self::$_last_server)->inTransaction()) {
$server = self::$_last_server;
}
return $this->querySelect(__FUNCTION__, $query, $this->getCalledMethod(), $server);
}
public function insert($tableName, $fields, $aData, $returning = true)
{
$dataArray = array();
$fieldsCount = count($fields);
$prepareData = array();
foreach ($aData as $val) {
$val = (array) $val;
$dataArray[] = '(?' . str_repeat(',?', $fieldsCount-1) . ')';
$prepareData = array_merge($prepareData, $val);
}
$query = 'INSERT INTO ' . $tableName . ' ("'. implode('", "', $fields) . '") VALUES' . implode(',', $dataArray) . ($returning ? ' RETURNING id' : '');
if (is_array($aData)) {
$this->prepareData($query, $prepareData);
}
$result = $this->querySelect(__FUNCTION__, $query, $this->getCalledMethod());
/*return $result ? self::$_connections[Db_Driver::SERVER_WRITE]->lastInsertId($tableName . '_id_seq') : false;*/
return is_array($result) && count($result) == 1 ? $result[0] : $result;
}
public function update($tableName, $aData, $where = null)
{
$query = 'UPDATE ' . $tableName . ' SET "' . implode('" = ?, "', array_keys($aData)) . '" = ?';
if (!empty($where)) {
if (is_array($where)) {
$query .= ' WHERE ' . $this->prepareWhere($where);
} else if (is_string($where)) {
$query .= ' WHERE ' . $where;
}
}
$aData = array_values($aData);
if (is_array($aData) && !empty($aData)) {
$this->prepareData($query, $aData);
}
return $this->querySelect(__FUNCTION__, $query, $this->getCalledMethod());
}
public function delete($tableName, $where = null, $returning = false)
{
$query = 'DELETE FROM ' . $tableName . ' ';
if (!empty($where)) {
if (is_array($where)) {
$query .= ' WHERE ' . $this->prepareWhere($where);
} else if (is_string($where)) {
$query .= ' WHERE ' . $where;
}
}
return $this->querySelect(__FUNCTION__, $query . ($returning ? ' RETURNING *' : ''), $this->getCalledMethod());
}
public function fetchCol($query, $server = null)
{
return $this->querySelect(__FUNCTION__, $query, $this->getCalledMethod(), $server);
}
public function fetchAll($query, $server = null)
{
return $this->querySelect(__FUNCTION__, $query, $this->getCalledMethod(), $server);
}
public function fetchArray($query, $server = null)
{
return $this->querySelect(__FUNCTION__, $query, $this->getCalledMethod(), $server);
}
public function fetchAssoc($query, $server = null)
{
return $this->querySelect(__FUNCTION__, $query, $this->getCalledMethod(), $server);
}
public function fetchOne($query, $server = null)
{
return $this->querySelect(__FUNCTION__, $query, $this->getCalledMethod(), $server);
}
public function fetchRow($query, $server = null)
{
return $this->querySelect(__FUNCTION__, $query, $this->getCalledMethod(), $server);
}
public function quote($value)
{
//return pg_escape_string((string) $value);
return $this->connect(self::$_last_server)->quote(pg_escape_string((string) $value));
}
public function log($value = true)
{
if (is_bool($value)) {
self::$_write_log = $value;
}
return $this;
}
public function prepareData(&$query, $dataArray)
{
$query = (string) $query;
$dataArray = (array) $dataArray;
foreach ($dataArray as &$data) {
if ($data === null) {
$data = 'DEFAULT';
} else if (is_array($data)) {
$data = implode(',', $data);
} else if (is_float($data) || is_int($data)) {
continue;
} else if (is_string($data)) {
$data = $this->quote($data);
}
}
$ret = '';
$quotes = 0;
$dataCount = count($dataArray);
for ($i=0,$n=strlen($query); $i<$n; ++$i) {
$ch = $query[$i];
if ($ch == '?') {
if ($quotes % 2 == 0) {
if ($dataCount <= 0) {
\Nette\Diagnostics\Debugger::log('Ошибка запроса (' . $query . ') с параметрами ' . print_r($dataArray, true));
throw new Exception('replace parameter not exists!');
}
$replaceData = array_shift($dataArray);
if ($replaceData === null) {
$replaceData = 'DEFAULT';
}
$dataCount--;
$ch = $replaceData;
}
} elseif ($ch == '\'' && $i > 0 && $query[$i-1] != '\\') {
++$quotes;
}
$ret .= $ch;
}
$query = $ret;
}
public function prepareWhere($whereArray)
{
$aWhere = array();
foreach ($whereArray as $field => $value) {
$field = trim(mb_strtolower($field));
preg_match('/^([a-z]+[a-z0-9_]*)(\s*)?(.*)?$/', $field, $parseKey);
if ( !($field = $parseKey[1]) ) continue;
$operand = $parseKey[3];
if (is_float($value) || is_int($value)) {
$aWhere[] = ' "' . $field . '" ' . ($operand ? : '=') . ' ' . $value;
} else if (is_array($value)) {
if ($operand === 'between' && isset($value[0]) && isset($value[1])) {
$aWhere[] = ' "' . $field . ' BETWEEN ' . $value[0] . ' AND ' . $value[1];
} else {
$aWhere[] = ' "' . $field . '" ' . ($operand ? : 'IN') . ' (' . implode(', ', $value) . ')';
}
} else if ($value === null) {
$aWhere[] = ' "' . $field . '" ' . ($operand ? : 'IS') . ' NULL';
} else {
$aWhere[] = ' "' . $field . '" ' . ($operand ? : '=') . ' ' . $this->quote((string) $value);
}
}
return implode(' AND ', $aWhere);
}
/**
* Возвращает метод откуда был вызван конструктор
*/
private function getCalledMethod()
{
if (defined('DEBUG') && DEBUG) {
$backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
$index = isset($backtrace[3]['class']) ? ($backtrace[3]['class'] === 'Db_Model' ? 4 : 3) : 2;
return $backtrace[$index]['class'] . $backtrace[$index]['type'] . $backtrace[$index]['function'] . '[' . $backtrace[$index-1]['line'] . ']';
} else {
return '';
}
}
}