ChWsbDb.php

Go to the documentation of this file.

<?php
 
define('CH_WSB_TABLE_PROFILES', '`Profiles`');
 
require_once(CH_DIRECTORY_PATH_CLASSES . 'ChWsbParams.php');
 
class ChWsbDb
{
    protected $host, $port, $socket, $dbname, $user, $password;
 
    protected $link;
 
    protected static $instance;
 
    protected $oCurrentStmt;
 
    protected $iCurrentFetchStyle;
 
    public $oParams = null;
 
    public $oDbCacheObject = null;
 
    /*
    * set database parameters and connect to it
    * don't want anyone to initate this class
    */
    protected function __construct()
    {
        $this->host               = DATABASE_HOST;
        $this->port               = DATABASE_PORT;
        $this->socket             = DATABASE_SOCK;
        $this->dbname             = DATABASE_NAME;
        $this->user               = DATABASE_USER;
        $this->password           = DATABASE_PASS;
        $this->iCurrentFetchStyle = PDO::FETCH_ASSOC;
 
        //  connect to db automatically
        if (!$this->link) {
            $this->connect();
        }
 
        $GLOBALS['gl_db_cache'] = [];
        $GLOBALS['ch_db_param'] = [];
 
        if (empty($GLOBALS['ch_db_param'])) {
            $GLOBALS['ch_db_param'] = new ChWsbParams($this);
        }
 
        $this->oParams = $GLOBALS['ch_db_param'];
    }
 
    public static function getInstance()
    {
        if (!isset(self::$instance)) {
            self::$instance = new static();
        }
 
        return self::$instance;
    }
 
    protected function connect()
    {
        $sSocketOrHost = ($this->socket) ? "unix_socket={$this->socket}" : "host={$this->host};port={$this->port}";
 
        try {
            $this->link = new PDO(
                "mysql:{$sSocketOrHost};dbname={$this->dbname};charset=utf8",
                $this->user,
                $this->password,
                [
                    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET sql_mode=""',
                    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES   => false,
                    PDO::ATTR_PERSISTENT         => defined('DATABASE_PERSISTENT') && DATABASE_PERSISTENT ? true : false,
                ]
            );
        }
        catch (PDOException $o) {
            throw new Exception ("DB connect failed: " . $o->getMessage());
        }
    }
 
    protected function disconnect()
    {
        $this->link = null;
    }
 
    public function setTimezone($sTimezone)
    {
        $oTimeZone = new DateTimeZone($sTimezone);
        $oDate     = new DateTime('now', $oTimeZone);
        $this->link->query('SET time_zone = "' . $oDate->format('P') . '"');
    }
 
    public function getLink()
    {
        return $this->link;
    }
 
    public function res($sQuery, $aBindings = [], $bReplaying = false)
    {
        if (strlen(trim($sQuery)) < 1) {
            throw new InvalidArgumentException('Please provide a valid sql query');
        }
 
        if ($this->link === null) {
            $this->connect();
        }
 
        if (isset($GLOBALS['ch_profiler'])) {
            $GLOBALS['ch_profiler']->beginQuery($sQuery);
        }
 
        try {
            if ($aBindings) {
                $oStmt = $this->link->prepare($sQuery);
                $oStmt->execute($aBindings);
            } else {
                $oStmt = $this->link->query($sQuery);
            }
        } catch (PDOException $e) {
            // check if this is not a replay call already, if it is, than we will skip this block
            // and also check if the error is about mysql server going away/disconnecting
            if (!$bReplaying && (stripos($e->getMessage(), 'gone away') !== false)) {
                // reconnect to db
                $this->disconnect();
                $this->connect();
 
                // lets retry after reconnecting by replaying
                // the call with the replay arg set to true
                return $this->res($sQuery, $aBindings, true);
            }
 
            // this was a replay call and it failed again OR
            // the error was not about mysql disconnecting.
            // We will throw the exception and let
            // the system handle it like a boss
            throw $e;
        }
 
        if (isset($GLOBALS['ch_profiler'])) {
            $GLOBALS['ch_profiler']->endQuery($oStmt);
        }
 
        return $oStmt;
    }
 
    public function getAll($sQuery, $aBindings = [], $iFetchType = PDO::FETCH_ASSOC)
    {
        if ($iFetchType != PDO::FETCH_ASSOC && $iFetchType != PDO::FETCH_NUM && $iFetchType != PDO::FETCH_BOTH) {
            $iFetchType = PDO::FETCH_ASSOC;
        }
 
        $oStmt = $this->res($sQuery, $aBindings);
 
        return $oStmt->fetchAll($iFetchType);
    }
 
    public function getRow($sQuery, $aBindings = [], $iFetchStyle = PDO::FETCH_ASSOC)
    {
        if ($iFetchStyle != PDO::FETCH_ASSOC && $iFetchStyle != PDO::FETCH_NUM && $iFetchStyle != PDO::FETCH_BOTH) {
            $iFetchStyle = PDO::FETCH_ASSOC;
        }
 
        $oStmt = $this->res($sQuery, $aBindings);
 
        return $oStmt->fetch($iFetchStyle);
    }
 
    public function getColumn($sQuery, $aBindings = [])
    {
        $oStmt = $this->res($sQuery, $aBindings);
 
        $aResultRows = [];
        while ($row = $oStmt->fetchColumn()) {
            $aResultRows[] = $row;
        }
 
        return $aResultRows;
    }
 
    public function getOne($sQuery, $aBindings = [], $iIndex = 0)
    {
        $oStmt = $this->res($sQuery, $aBindings);
 
        $result = $oStmt->fetch(PDO::FETCH_BOTH);
        if ($result) {
            return $result[$iIndex];
        }
 
        return null;
    }
 
    public function getFirstRow($sQuery, $aBindings = [], $iFetchStyle = PDO::FETCH_ASSOC)
    {
        if ($iFetchStyle != PDO::FETCH_ASSOC && $iFetchStyle != PDO::FETCH_NUM) {
            $this->iCurrentFetchStyle = PDO::FETCH_ASSOC;
        } else {
            $this->iCurrentFetchStyle = $iFetchStyle;
        }
 
        $oStmt = $this->res($sQuery, $aBindings);
 
        $this->oCurrentStmt = $oStmt;
 
        $result = $this->oCurrentStmt->fetch($this->iCurrentFetchStyle);
        if ($result) {
            return $result;
        }
 
        return [];
    }
 
    public function getNextRow()
    {
        $aResult = [];
 
        if (!$this->oCurrentStmt) {
            return $aResult;
        }
 
        $aResult = $this->oCurrentStmt->fetch($this->iCurrentFetchStyle);
 
        if ($aResult === false) {
            $this->oCurrentStmt       = null;
            $this->iCurrentFetchStyle = PDO::FETCH_ASSOC;
 
            return [];
        }
 
        return $aResult;
    }
 
    public function getAllWithKey($sQuery, $sFieldKey, $aBindings = [], $iFetchType = PDO::FETCH_ASSOC)
    {
        $oStmt = $this->res($sQuery, $aBindings);
 
        $aResult = [];
        if ($oStmt) {
            while ($row = $oStmt->fetch($iFetchType)) {
                $aResult[$row[$sFieldKey]] = $row;
            }
 
            $oStmt = null;
        }
 
        return $aResult;
    }
 
    public function getPairs($sQuery, $sFieldKey, $sFieldValue, $aBindings = [])
    {
        $oStmt = $this->res($sQuery, $aBindings);
 
        $aResult = [];
        if ($oStmt) {
            while ($row = $oStmt->fetch()) {
                $aResult[$row[$sFieldKey]] = $row[$sFieldValue];
            }
 
            $oStmt = null;
        }
 
        return $aResult;
    }
 
    public function query($sQuery, $aBindings = [])
    {
        return $this->res($sQuery, $aBindings)->rowCount();
    }
 
    public function getNumRows($oStmt = null)
    {
        return $this->getAffectedRows($oStmt);
    }
 
    public function getAffectedRows($oStmt = null)
    {
        if ($oStmt) {
            return $oStmt->rowCount();
        }
 
        if ($this->oCurrentStmt) {
            return $this->oCurrentStmt->rowCount();
        }
 
        return 0;
    }
 
    public function fillArray($oStmt, $iFetchType = PDO::FETCH_ASSOC)
    {
        if ($iFetchType != PDO::FETCH_ASSOC && $iFetchType != PDO::FETCH_NUM && $iFetchType != PDO::FETCH_BOTH) {
            $iFetchType = PDO::FETCH_ASSOC;
        }
 
        $aResult = [];
        while ($row = $oStmt->fetch($iFetchType)) {
            $aResult[] = $row;
        }
 
        return $aResult;
    }
 
    public function lastId()
    {
        return $this->link->lastInsertId();
    }
 
    public function getParam($sName, $bCache = true)
    {
        return $this->oParams->get($sName, $bCache);
    }
 
    public function setParam($sName, $sValue)
    {
        $this->oParams->set($sName, $sValue);
 
        return true;
    }
 
    public function listTables()
    {
        $aResult = [];
 
        $oStmt = $this->link->query("SHOW TABLES FROM `{$this->dbname}`");
        while ($row = $oStmt->fetch(PDO::FETCH_NUM)) {
            $aResult[] = $row[0];
        }
 
        return $aResult;
    }
 
    public function getFields($sTable)
    {
        $oFieldsStmt = $this->link->query("SHOW COLUMNS FROM `{$sTable}`");
 
        $aResult = ['original' => [], 'uppercase' => []];
        while ($row = $oFieldsStmt->fetch()) {
            $sName                  = $row['Field'];
            $aResult['original'][]  = $sName;
            $aResult['uppercase'][] = strtoupper($sName);
        }
 
        return $aResult;
    }
 
    public function isFieldExists($sTable, $sFieldName)
    {
        $aFields = $this->getFields($sTable);
 
        return in_array(strtoupper($sFieldName), $aFields['uppercase']);
    }
 
    public function fetchField($mixedQuery, $iField, $aBindings = [])
    {
        if(is_string($mixedQuery))
            $mixedQuery = $this->res($mixedQuery, $aBindings);
 
        return $mixedQuery->getColumnMeta($iField);
    }
 
    public function getDbCacheObject()
    {
        if ($this->oDbCacheObject != null) {
            return $this->oDbCacheObject;
        } else {
            $sEngine              = getParam('sys_db_cache_engine');
            $this->oDbCacheObject = ch_instance('ChWsbCache' . $sEngine);
            if (!$this->oDbCacheObject->isAvailable()) {
                $this->oDbCacheObject = ch_instance('ChWsbCacheFile');
            }
 
            return $this->oDbCacheObject;
        }
    }
 
    public function genDbCacheKey($sName)
    {
        global $site;
 
        return 'db_' . $sName . '_' . md5($site['ver'] . $site['build'] . $site['url']) . '.php';
    }
 
    public function fromCache($sName, $sFunc)
    {
        $aArgs = func_get_args();
        array_shift($aArgs); // shift $sName
        array_shift($aArgs); // shift $sFunc
 
        if (!getParam('sys_db_cache_enable')) {
            return call_user_func_array(array($this, $sFunc), $aArgs);
        } // pass other function parameters as database function parameters
 
        $oCache = $this->getDbCacheObject();
 
        $sKey = $this->genDbCacheKey($sName);
 
        $mixedRet = $oCache->getData($sKey);
 
        if ($mixedRet !== null) {
 
            return $mixedRet;
 
        } else {
 
            $mixedRet = call_user_func_array(array($this, $sFunc),
                $aArgs); // pass other function parameters as database function parameters
 
            $oCache->setData($sKey, $mixedRet);
        }
 
        return $mixedRet;
    }
 
    public function cleanCache($sName)
    {
        $oCache = $this->getDbCacheObject();
 
        $sKey = $this->genDbCacheKey($sName);
 
        return $oCache->delData($sKey);
    }
 
    public function & fromMemory($sName, $sFunc)
    {
        if (array_key_exists($sName, $GLOBALS['gl_db_cache'])) {
            return $GLOBALS['gl_db_cache'][$sName];
        }
 
        $aArgs = func_get_args();
        array_shift($aArgs); // shift $sName
        array_shift($aArgs); // shift $sFunc
 
        // pass other function parameters as database function parameters
        $GLOBALS['gl_db_cache'][$sName] = call_user_func_array(array($this, $sFunc), $aArgs);
 
        return $GLOBALS['gl_db_cache'][$sName];
 
    }
 
    public function cleanMemory($sName)
    {
        if (isset($GLOBALS['gl_db_cache'][$sName])) {
            unset($GLOBALS['gl_db_cache'][$sName]);
 
            return true;
        }
 
        return false;
    }
 
    public function arrayToSQL($a, $sDiv = ',')
    {
        $s = '';
        foreach($a as $k => $v)
            $s .= "`{$k}` = " . $this->escape($v) . $sDiv;
 
        return trim($s, $sDiv);
    }
 
    public function escape($sText, $bReal = true)
    {
        $pdoEscapted = $this->link->quote($sText);
 
        if ($bReal) {
            return $pdoEscapted;
        }
 
        // don't need the actual quotes pdo adds, so it
        // behaves kinda like mysql_real_escape_string
        // p.s. things we do for legacy code
        return 0 === mb_strpos($pdoEscapted, "'") ? mb_substr($pdoEscapted, 1, -1) : $pdoEscapted;
    }
 
    public function implode_escape ($mixed)
    {
        if (is_array($mixed)) {
            $s = '';
            foreach ($mixed as $v)
                $s .= (is_numeric($v) ? $v : $this->escape($v)) . ',';
            if ($s)
                return substr($s, 0, -1);
            else
                return 'NULL';
        }
 
        return is_numeric($mixed) ? $mixed : ($mixed ? $this->escape($mixed) : 'NULL');
    }
 
    public function unescape($mixed)
    {
        if (is_array($mixed)) {
            foreach ($mixed as $k => $v) {
                $mixed[$k] = $this->getOne("SELECT '$v'");
            }
 
            return $mixed;
        } else {
            return $this->getOne("SELECT '$mixed'");
        }
    }
}