DbManager.php

<?php
/*
Copyright (C) 2014, Siemens AG
Authors: Steffen Weber, Andreas Würl

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
version 2 as published by the Free Software Foundation.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License along
with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
*/

namespace Fossology\Lib\Db;

use Fossology\Lib\Exception;
use Monolog\Logger;

abstract class DbManager
{
  protected $dbDriver;
  protected $preparedStatements;
  protected $logger;
  protected $cumulatedTime = array();
  protected $queryCount = array();
  private $transactionDepth = 0;

  function __construct(Logger $logger)
  {
    $this->preparedStatements = array();
    $this->logger = $logger;
  }

  public function setDriver(Driver &$dbDriver)
  {
    $this->dbDriver = $dbDriver;
  }

  public function getDriver()
  {
    return $this->dbDriver;
  }

  public function begin()
  {
    if ($this->transactionDepth==0) {
      $this->dbDriver->begin();
    }
    $this->transactionDepth++;
  }

  public function commit()
  {
    $this->transactionDepth--;
    if ($this->transactionDepth==0) {
      $this->dbDriver->commit();
    } else if ($this->transactionDepth < 0) {
      throw new \Exception('too much transaction commits');
    }
  }

  public function rollback()
  {
    if ($this->transactionDepth > 0) {
      $this->transactionDepth--;
      $this->dbDriver->rollback();
    } else if ($this->transactionDepth == 0) {
      throw new \Exception('too much transaction rollbacks');
    }
  }

  abstract public function prepare($statementName, $sqlStatement);

  public function insertPreparedAndReturn($statementName, $sqlStatement, $params, $returning)
  {
    $sqlStatement .= " RETURNING $returning";
    $statementName .= ".returning:$returning";
    $this->prepare($statementName,$sqlStatement);
    $res = $this->execute($statementName,$params);
    $return = $this->fetchArray($res);
    $this->freeResult($res);
    return $return[$returning];
  }

  abstract public function execute($statementName, $params = array());

  protected function checkResult($result, $sqlStatement = "")
  {
    if ($result !== false) {
      return;
    }
    $lastError = "";
    if ($this->dbDriver->isConnected()) {
      $lastError = $this->dbDriver->getLastError();
      $this->logger->addCritical($lastError);
      if ($this->transactionDepth>0) {
        $this->dbDriver->rollback();
      }
    } else {
      $this->logger->addCritical("DB connection lost.");
    }

    $message = "error executing: $sqlStatement\n\n$lastError";
    throw new Exception($message);
  }

  public function getSingleRow($sqlStatement, $params = array(), $statementName = "")
  {
    if (empty($statementName)) {
      $backtrace = debug_backtrace();
      $caller = $backtrace[1];
      $statementName = (array_key_exists('class', $caller) ? "$caller[class]::" : '') . "$caller[function]";
    }
    if (!array_key_exists($statementName, $this->preparedStatements)) {
      $this->prepare($statementName, $sqlStatement);
    }
    $res = $this->execute($statementName, $params);
    $row = $this->dbDriver->fetchArray($res);
    $this->dbDriver->freeResult($res);
    return $row;
  }

  public function getRows($sqlStatement, $params = array(), $statementName = "")
  {
    if (empty($statementName)) {
      $backtrace = debug_backtrace();
      $caller = $backtrace[1];
      $statementName = (array_key_exists('class', $caller) ? "$caller[class]::" : '') . "$caller[function]";
    }
    if (!array_key_exists($statementName, $this->preparedStatements)) {
      $this->prepare($statementName, $sqlStatement);
    }
    $res = $this->execute($statementName, $params);
    $rows = $this->dbDriver->fetchAll($res);
    $this->dbDriver->freeResult($res);
    return $rows;
  }

  public function queryOnce($sqlStatement, $sqlLog = '')
  {
    if (empty($sqlLog)) {
      $sqlLog = $sqlStatement;
    }
    $startTime = microtime($get_as_float = true);
    $res = $this->dbDriver->query($sqlStatement);
    $this->checkResult($res, $sqlStatement);
    $this->freeResult($res);
    $execTime = microtime($get_as_float = true) - $startTime;
    $this->logger->addDebug("query '$sqlLog' took " . $this->formatMilliseconds($execTime));
  }

  public function freeResult($res)
  {
    return $this->dbDriver->freeResult($res);
  }

  public function fetchArray($res)
  {
    return $this->dbDriver->fetchArray($res);
  }

  public function fetchAll($res)
  {
    return $this->dbDriver->fetchAll($res);
  }

  public function createMap($tableName,$keyColumn,$valueColumn,$sqlLog='')
  {
    if (empty($sqlLog)) {
      $sqlLog = __METHOD__ . ".$tableName.$keyColumn,$valueColumn";
    }
    $this->prepare($sqlLog, "select $keyColumn,$valueColumn from $tableName");
    $res = $this->execute($sqlLog);
    $map = array();
    while ($row = $this->fetchArray($res)) {
      $map[$row[$keyColumn]] = $row[$valueColumn];
    }
    $this->freeResult($res);
    return $map;
  }

  public function flushStats()
  {
    foreach ($this->cumulatedTime as $statementName => $seconds) {
      $queryCount = $this->queryCount[$statementName];
      $this->logger->addDebug("executing '$statementName' took "
          . $this->formatMilliseconds($seconds)
          . " ($queryCount queries" . ($queryCount > 0 ? ", avg " . $this->formatMilliseconds($seconds / $queryCount) : "") . ")");
    }

    if ($this->transactionDepth != 0) {
      throw new \Fossology\Lib\Exception("you have not committed enough");
    }
  }

  protected function formatMilliseconds($seconds)
  {
    return sprintf("%0.3fms", 1000 * $seconds);
  }

  protected function collectStatistics($statementName, $execTime)
  {
    $this->cumulatedTime[$statementName] += $execTime;
    $this->queryCount[$statementName]++;
  }

  public function booleanFromDb($booleanValue)
  {
    return $this->dbDriver->booleanFromDb($booleanValue);
  }

  public function booleanToDb($booleanValue)
  {
    return $this->dbDriver->booleanToDb($booleanValue);
  }

  private function cleanupParamsArray($params)
  {
    $nParams = sizeof($params);
    for ($i = 0; $i<$nParams; $i++) {
      if (is_bool($params[$i])) {
        $params[$i] = $this->dbDriver->booleanToDb($params[$i]);
      }
    }
    return $params;
  }

  public function insertInto($tableName, $keys, $params, $sqlLog='', $returning='')
  {
    if (empty($sqlLog)) {
      $sqlLog = __METHOD__ . ".$tableName.$keys" . (empty($returning) ? "" : md5($returning));
    }
    $sql = "INSERT INTO $tableName ($keys) VALUES (";
    $nKeys = substr_count($keys,',')+1;
    for ($i = 1; $i < $nKeys; $i++) {
      $sql .= '$'.$i.',';
    }
    $sql .= '$'.$nKeys.')';
    $params = $this->cleanupParamsArray($params);
    if (!empty($returning)) {
      return $this->insertPreparedAndReturn($sqlLog, $sql, $params, $returning);
    }
    $this->prepare($sqlLog,$sql);
    $res = $this->execute($sqlLog,$params);
    $this->freeResult($res);
  }

  public function insertTableRow($tableName,$assocParams,$sqlLog='',$returning='')
  {
    $params = array_values($assocParams);
    $keys = implode(',',array_keys($assocParams));
    if (empty($sqlLog)) {
      $sqlLog = __METHOD__ . ".$tableName.$keys" . (empty($returning) ? "" : md5($returning));
    }
    return $this->insertInto($tableName, $keys, $params, $sqlLog, $returning);
  }

  public function updateTableRow($tableName, $assocParams, $idColName, $id, $sqlLog='')
  {
    $params = array_values($assocParams);
    $keys = array_keys($assocParams);
    $nKeys = sizeof($keys);

    if (empty($sqlLog)) {
      $sqlLog = __METHOD__ . ".$tableName.$keys";
    }

    $sql = "UPDATE $tableName SET";
    for ($i = 1; $i < $nKeys; $i++) {
      $sql .= " ".$keys[$i - 1].' = $'.$i.",";
    }
    $sql .= " ".$keys[$nKeys - 1].' = $'.$nKeys;
    $sql .= " WHERE $idColName = \$".($nKeys + 1);

    $params[] = $id;
    $params = $this->cleanupParamsArray($params);

    $this->prepare($sqlLog,$sql);
    $res = $this->execute($sqlLog,$params);
    $this->freeResult($res);
  }

  public function existsTable($tableName)
  {
    if (! preg_match('/^[a-z0-9_]+$/i',$tableName)) {
      throw new \Exception("invalid table name '$tableName'");
    }
    return $this->dbDriver->existsTable($tableName);
  }

  public function existsColumn($tableName, $columnName)
  {
    if (! preg_match('/^[a-z0-9_]+$/i',$columnName)) {
      throw new \Exception("invalid column name '$columnName'");
    }
    return $this->existsTable($tableName) && $this->dbDriver->existsColumn($tableName, $columnName);
  }
}