1:   2:   3:   4:   5:   6:   7:   8:   9:  10:  11:  12:  13:  14:  15:  16:  17:  18:  19:  20:  21:  22:  23:  24:  25:  26:  27:  28:  29:  30:  31:  32:  33:  34:  35:  36:  37:  38:  39:  40:  41:  42:  43:  44:  45:  46:  47:  48:  49:  50:  51:  52:  53:  54:  55:  56:  57:  58:  59:  60:  61:  62:  63:  64:  65:  66:  67:  68:  69:  70:  71:  72:  73:  74:  75:  76:  77:  78:  79:  80:  81:  82:  83:  84:  85:  86:  87:  88:  89:  90:  91:  92:  93:  94:  95:  96:  97:  98:  99: 100: 101: 102: 103: 104: 105: 106: 107: 108: 109: 110: 111: 112: 113: 114: 115: 116: 117: 118: 119: 120: 121: 122: 123: 124: 125: 126: 127: 128: 129: 130: 131: 132: 133: 134: 135: 136: 137: 138: 139: 140: 141: 142: 143: 144: 145: 146: 147: 148: 149: 150: 151: 152: 153: 154: 155: 156: 157: 158: 159: 160: 161: 162: 163: 164: 165: 166: 167: 168: 169: 170: 171: 172: 173: 174: 175: 176: 177: 178: 179: 180: 181: 182: 183: 184: 185: 186: 187: 188: 189: 190: 191: 192: 193: 194: 195: 196: 197: 198: 199: 200: 201: 202: 203: 204: 205: 206: 207: 208: 209: 210: 211: 212: 213: 214: 215: 216: 217: 218: 219: 220: 221: 222: 223: 224: 225: 226: 227: 228: 229: 230: 231: 232: 233: 234: 235: 236: 237: 238: 239: 240: 241: 242: 243: 244: 245: 246: 247: 248: 249: 250: 251: 252: 253: 254: 255: 256: 257: 258: 259: 260: 261: 262: 263: 264: 265: 266: 267: 268: 269: 270: 271: 272: 273: 274: 275: 276: 277: 278: 279: 280: 281: 282: 283: 284: 285: 286: 287: 288: 289: 290: 291: 292: 293: 294: 295: 296: 297: 298: 299: 300: 301: 302: 303: 304: 305: 306: 307: 308: 309: 310: 311: 312: 313: 314: 315: 316: 317: 318: 319: 320: 321: 322: 323: 324: 325: 326: 327: 328: 329: 330: 331: 332: 333: 334: 335: 336: 337: 338: 339: 340: 341: 342: 343: 344: 345: 346: 347: 348: 349: 350: 351: 352: 353: 354: 355: 356: 357: 358: 359: 360: 361: 362: 363: 364: 365: 366: 367: 368: 369: 370: 371: 372: 373: 374: 375: 376: 377: 378: 379: 380: 381: 382: 383: 384: 385: 386: 387: 388: 389: 390: 391: 392: 393: 394: 395: 396: 397: 398: 399: 400: 401: 402: 403: 404: 405: 406: 407: 408: 409: 410: 411: 412: 413: 414: 415: 416: 417: 418: 419: 420: 421: 422: 423: 424: 425: 426: 427: 428: 429: 430: 431: 432: 433: 434: 435: 436: 437: 438: 439: 440: 441: 442: 443: 444: 445: 446: 447: 448: 449: 450: 451: 452: 453: 454: 455: 456: 457: 458: 459: 460: 461: 462: 463: 464: 465: 466: 467: 468: 469: 470: 471: 472: 473: 474: 475: 476: 477: 478: 479: 480: 481: 482: 483: 484: 485: 486: 487: 488: 489: 490: 491: 492: 493: 494: 495: 496: 497: 498: 499: 500: 501: 502: 503: 504: 505: 506: 507: 508: 509: 510: 511: 512: 513: 514: 515: 516: 517: 518: 519: 520: 521: 522: 523: 524: 525: 526: 527: 528: 529: 530: 531: 532: 533: 534: 535: 536: 537: 538: 539: 540: 541: 542: 543: 544: 545: 546: 547: 548: 549: 550: 551: 552: 553: 554: 

<?php

/**
 * MvcCore
 *
 * This source file is subject to the BSD 3 License
 * For the full copyright and license information, please view
 * the LICENSE.md file that are distributed with this source code.
 *
 * @copyright   Copyright (c) 2016 Tom Flidr (https://github.com/mvccore)
 * @license  https://mvccore.github.io/docs/mvccore/5.0.0/LICENCE.md
 */

namespace MvcCore\Ext\Models\Db;

class       Connection 
implements  \MvcCore\Model\IConstants,
            \MvcCore\Ext\Models\Db\IConnection {

    /**
     * `\PDO` connection provider instance.
     * @var \PDO
     */
    protected $provider = NULL;

    /**
     * PDO driver specific connection string (it may contains crendentials in come cases).
     * @var string
     */
    protected $dsn;

    /**
     * Connection user name, always not used in file databases without authentication.
     * @var string
     */
    protected $username;

    /**
     * Database password, it's stored in memory all the time for reconnection purposes.
     * @var string
     */
    protected $password;

    /**
     * `\PDO` constructor connection options.
     * @var array
     */
    protected $options;

    /**
     * `TRUE` for multi statements connection type.
     * @see https://stackoverflow.com/questions/38305108/disable-multiple-statements-in-php-pdo
     * @var bool
     */
    protected $multiStatements = FALSE;
    
    /**
     *  Database server version in "PHP-standardized" version number string.
     * @var string|NULL
     */
    protected $version = NULL;

    /**
     * Boolean about if current connection is inside transaction or not.
     * @var bool
     */
    protected $inTransaction = FALSE;
    
    /**
     * If current connection is inside transaction, this could contains 
     * current transaction name, else it's `NULL`.
     * @var string
     */
    protected $transactionName = NULL;

    /**
     * Retry attemps total count to reconnect by system configuration (if implemented).
     * @var int|NULL
     */
    protected $retryAttemptsTotal = NULL;

    /**
     * Retry attemps counter.
     * @var int
     */
    protected $retryAttempts = 0;

    /**
     * Delay to wait before next reconnect in seconds, it coulds contain
     * miliseconds after decimal point.
     * @var float
     */
    protected $retryDelay = 0;
    

    /**
     * @inheritDocs
     * @return array
     */
    public static function GetAvailableDrivers () {
        return \PDO::getAvailableDrivers();
    }

    /**
     * Creates a PDO instance representing a connection to a database.
     * @param string $dsn
     * @param string $username
     * @param string $password
     * @param array $options
     * @throws \Throwable
     */
    public function __construct ($dsn, $username = NULL, $password = NULL, array $options = []) {
        $this->dsn = $dsn;
        $this->username = $username;
        $this->password = $password;
        $this->options = $options;
        
        if ($this->retryAttemptsTotal === NULL) {
            $this->retryAttemptsTotal = 0;
            $sysCfg = \MvcCore\Config::GetSystem();
            if ($sysCfg !== NULL) {
                $sysCfgProps = \MvcCore\Model::GetSysConfigProperties();
                $sysCfgDbSection = $sysCfg->{$sysCfgProps->sectionName};
                if ($sysCfgDbSection !== NULL) {
                    if (isset($sysCfgDbSection->{$sysCfgProps->retryAttempts}))
                        $this->retryAttemptsTotal = $sysCfgDbSection->{$sysCfgProps->retryAttempts};
                    if (isset($sysCfgDbSection->{$sysCfgProps->retryDelay}))
                        $this->retryDelay = floatval($sysCfgDbSection->{$sysCfgProps->retryDelay});
                }
            }
        }
        
        try {
            $this->connect();
        } catch (\Exception $e) { // backward compatibility
            $this->reConnectIfNecessaryOrThrownError($e);
        } catch (\Throwable $e) {
            $this->reConnectIfNecessaryOrThrownError($e);
        }
    }
    
    /**
     * @inheritDocs
     * @param string|\string[] $statement
     * @param array $driverOptions
     * @throws \Throwable
     * @return \MvcCore\Ext\Models\Db\Statement
     */
    public function Prepare ($sql, $driverOptions = [\MvcCore\Ext\Models\Db\IStatement::AUTO_CLOSE]) {
        $sqlCode = is_array($sql) ? implode(" \n", $sql) : $sql;
        return $this->providerInvoke('prepare', [$sqlCode, $driverOptions], FALSE, FALSE);
    }

    /**
     * @inheritDocs
     * @param string|\string[] $sql
     * @throws \Throwable
     * @return \MvcCore\Ext\Models\Db\Statement
     */
    public function Query ($sql, $connectionIndexOrName = NULL) {
        $sqlCode = is_array($sql) ? implode(" \n", $sql) : $sql;
        return $this->providerInvoke('query', [$sqlCode, [\MvcCore\Ext\Models\Db\IStatement::AUTO_CLOSE]], TRUE, FALSE);
    }
    
    /**
     * @inheritDocs
     * @param string|\string[] $sql
     * @throws \Throwable
     * @return \MvcCore\Ext\Models\Db\Readers\Execution
     */
    public function Execute ($sql, $connectionIndexOrName = NULL) {
        $sqlCode = is_array($sql) ? implode(" \n", $sql) : $sql;
        return $this->providerInvoke('exec', [$sqlCode, [\MvcCore\Ext\Models\Db\IStatement::AUTO_CLOSE]], TRUE, TRUE);
    }



    /**
     * @inheritDocs
     * @param string|NULL $sequenceName
     * @param string|NULL $targetType
     * @return int|float|string|NULL
     */
    public function LastInsertId ($sequenceName = NULL, $targetType = NULL) {
        $result = $this->provider->lastInsertId($sequenceName);
        if ($result !== NULL && $targetType !== NULL)
            settype($result, $targetType);
        return $result;
    }
    
    /**
     * @inheritDocs
     * @param string $string
     * @param int $parameter_type
     * @return string
     */
    public function Quote ($string , $paramType = \PDO::PARAM_STR) {
        return $this->provider->quote($string, $paramType);
    }
    
    /**
     * @inheritDocs
     * @param string $identifierName
     * @return string
     */
    public function QuoteName ($identifierName) {
        return "'{$identifierName}'";
    }



    /**
     * @inheritDocs
     * @param int $attribute
     * @return mixed
     */
    public function GetAttribute ($attribute) {
        return $this->provider->getAttribute($attribute);
    }

    /**
     * @inheritDocs
     * @param int $attribute
     * @param mixed $value
     * @return bool
     */
    public function SetAttribute ($attribute, $value) {
        return $this->provider->setAttribute($attribute , $value);
    }

    /**
     * @inheritDocs
     * @return null|string
     */
    public function GetVersion () {
        return $this->version;
    }

    /**
     * @inheritDocs
     * @return bool|null
     */
    public function IsMutliStatements () {
        return $this->mutliStatements;
    }

    /**
     * @inheritDocs
     * @return \PDO
     */
    public function GetProvider () {
        return $this->provider;
    }

    /**
     * @inheritDocs
     * @return array
     */
    public function GetConfig () {
        return [
            'dsn'       => $this->dsn,
            'username'  => $this->username,
            'password'  => $this->password,
            'options'   => $this->options,
        ];
    }
    
    /**
     * @inheritDocs
     * @return bool
     */
    public function InTransaction () {
        return $this->inTransaction;
    }

    /**
     * @inheritDocs
     * @param int $flags
     * @param string $name
     * @return bool
     */
    public function BeginTransaction ($flags = 0, $name = NULL) {
        if ($name !== NULL) 
            $this->transactionName = $name;
        $this->inTransaction = TRUE;
        return $this->provider->beginTransaction();
    }

    /**
     * @inheritDocs
     * @param int $flags
     * @return bool
     */
    public function Commit ($flags = 0) {
        $result = $this->provider->commit();
        $this->inTransaction = FALSE;
        $this->transactionName = NULL;
        return $result;
    }

    /**
     * @inheritDocs
     * @param int $flags
     * @return bool
     */
    public function RollBack ($flags = 0) {
        $result = $this->provider->rollBack();
        $this->inTransaction = FALSE;
        $this->transactionName = NULL;
        return $result;
    }

    /**
     * Connect into database with `\PDO` provider with possibly configured retries.
     * @return \PDO
     * @return \PDO
     */
    protected function connect () {
        $this->provider = new \PDO(
            $this->dsn, $this->username, $this->password, $this->options
        );
        $this->setUpConnectionSpecifics();
        return $this->provider;
    }

    /**
     * Set up connection specific properties depends on this driver.
     * @return void
     */
    protected function setUpConnectionSpecifics () {
        $serverVersionConst = '\PDO::ATTR_SERVER_VERSION';
        $serverVersionConstVal = defined($serverVersionConst) 
            ? constant($serverVersionConst) 
            : 0;
        $this->version = $this->provider->getAttribute($serverVersionConstVal);
    }
    
    /**
     * Check if given exception is about connection lost.
     * @param \Throwable $e 
     * @return bool
     */
    protected function isConnectionLost (\Throwable $e) {
        return FALSE;
    }

    /**
     * Try to invoke methods `prepare()` or `query()` or `exec()` on internal `\PDO` 
     * provider instance and if there has been any exception or any error thrown with 
     * message like: `... server has gone away ...`, try to reconnect from PHP and try 
     * to process given method with arguments again by configured retry count.
     * @param string $method
     * @param array $args
     * @param bool $executeProvider
     * @param bool $returnReader
     * @throws \Throwable
     * @return \MvcCore\Ext\Models\Db\Statement|\MvcCore\Ext\Models\Db\Readers\Execution
     */
    protected function providerInvoke (
        $method, $args, $executeProvider = FALSE, $returnReader = FALSE
    ) {
        /**
         * @var $exception \Throwable|NULL
         * @var $dbErrorMsg string
         * @var $providerResult \PDOStatement|int|NULL
         */
        $exception = NULL;
        $dbErrorMsg = NULL;

        list($query, $driverOptions) = $args;
        
        if (($driverOptionsIndex = array_search(\MvcCore\Ext\Models\Db\IStatement::AUTO_CLOSE, $driverOptions)) !== FALSE) 
            unset($driverOptions[$driverOptionsIndex]);
        if (($driverOptionsIndex = array_search(\MvcCore\Ext\Models\Db\IStatement::DO_NOT_AUTO_CLOSE, $driverOptions)) !== FALSE) 
            unset($driverOptions[$driverOptionsIndex]);

        try {
            set_error_handler(function ($phpErrLevel, $errMessage) use (& $dbErrorMsg) {
                // $phpErrLevel is always with value `2` as warning
                $dbErrorMsg = $errMessage;
            });

            $providerResult = call_user_func_array(
                [$this->provider, $method], 
                [$query, $driverOptions]
            );

            restore_error_handler();
            if ($providerResult === FALSE) {
                $errInfo = $this->statement->errorInfo();
                throw new \Exception($errInfo[2] ?: $dbErrorMsg, intval($errInfo[0]));
            }
        } catch (\Exception $e) { // backward compatibility
            $exception = \MvcCore\Ext\Models\Db\Exception::Create($e)
                ->setQuery($query);
            $providerResult = NULL;
        } catch (\Throwable $e) {
            $exception = \MvcCore\Ext\Models\Db\Exception::Create($e)
                ->setQuery($query);
            $providerResult = NULL;
        }

        if ($providerResult === NULL) {
            $this->reConnectIfNecessaryOrThrownError($exception); // throws \Throwable
            return $this->providerInvoke($method, $args, $executeProvider, $returnReader);
        }
        
        $statement = new \MvcCore\Ext\Models\Db\Statement(
            $this, $providerResult, $args[1]
        );

        if ($executeProvider && $returnReader) 
            return $statement->Execute();
        if ($executeProvider) 
            $statement->Execute();
        return $statement;
    }

    /**
     * Log given exception and print query with params on development.
     * @param \Throwable $error 
     * @throws \Throwable 
     */
    protected function handleError (\Throwable $error) {
        $isDev = \MvcCore\Application::GetInstance()->GetEnvironment()->IsDevelopment();
        if ($isDev && $error instanceof \MvcCore\Ext\Models\Db\Exception) {
            $query = $error->getQuery();
            $params = array_merge([], $error->getParams() ?: []);
            if (count($params) === 0) {
                \MvcCore\Debug::BarDump($query, 'Query:', [
                    'truncate'  => mb_strlen($query)
                ]);
            } else {
                list(
                    $dumpSuccess, $queryWithValues
                ) = $this->devDumpQueryWithParams($query, $params);
                if ($dumpSuccess) {
                    \MvcCore\Debug::BarDump($queryWithValues, 'Query with params:', [
                        'truncate'  => mb_strlen($queryWithValues)
                    ]);
                } else {
                    \MvcCore\Debug::BarDump($query, 'Query:', [
                        'truncate'  => mb_strlen($query)
                    ]);
                    \MvcCore\Debug::BarDump($params, 'Params:');
                }
            }
            \MvcCore\Debug::BarDump($this, 'Connection:');
            throw $error->getPrevious();
        } else {
            throw $error;
        }
    }

    /**
     * Replace all params in query to dump query with values on development env.
     * Return array with success boolean and replaced query.
     * @param string $query 
     * @param array $params 
     * @return array
     */
    protected function devDumpQueryWithParams ($query, $params) {
        $paramsCnt = count($params);
        $assocParams = (
            array_keys($params) !== range(0, $paramsCnt - 1)
        );
        $prov = & $this->provider;
        array_walk($params, function (& $value, $key) use (& $prov) {
            if ($value === NULL) {
                $value = 'NULL';
            } else if (is_string($value)) {
                $value = $prov->quote($value, \PDO::PARAM_STR);
            }
        });
        if ($assocParams) {
            $index = 0;
            $resultItems = [];
            $matchesCount = 0;
            foreach ($params as $paramKey => $paramValue) {
                preg_match_all("#([^a-zA-Z0-9])({$paramKey})([^a-zA-Z0-9])#", $query, $matches, PREG_OFFSET_CAPTURE);
                if (count($matches) > 0 && count($matches[2]) === 1) {
                    $matchIndex = $matches[2][0][1];
                    $resultItems[] = mb_substr($query, $index, $matchIndex - $index);
                    $resultItems[] = $paramValue;
                    $index = $matchIndex + mb_strlen($paramKey);
                    $matchesCount += 1;
                } else {
                    break;
                }
            }
            if ($matchesCount === $paramsCnt) {
                if ($index < mb_strlen($query)) 
                    $resultItems[] = mb_substr($query, $index);
                $resultQuery = implode('', $resultItems);
                $dumpSuccess = TRUE;
            } else {
                $resultQuery = $query;
                $dumpSuccess = FALSE;
            }
        } else {
            $dumpSuccess = FALSE;
            $resultQuery = $query;
            preg_match_all("#([^a-zA-Z0-9])(\?)([^a-zA-Z0-9])#", $query, $matches, PREG_OFFSET_CAPTURE);
            if (count($matches)) {
                $matchesQm = $matches[2];
                $matchesCnt = count($matchesQm);
                if ($matchesCnt === $paramsCnt) {
                    $index = 0;
                    $resultItems = [];
                    foreach ($matchesQm as $key => $qmAndIndex) {
                        $matchIndex = $qmAndIndex[1];
                        $resultItems[] = mb_substr($query, $index, $matchIndex);
                        $resultItems[] = $params[$key];
                        $index = $matchIndex + 1;
                    }
                    if ($index < mb_strlen($query)) 
                        $resultItems[] = mb_substr($query, $index);
                    $resultQuery = implode('', $resultItems);
                    $dumpSuccess = TRUE;
                }
            }
        }
        return [$dumpSuccess, $resultQuery];
    }

    /**
     * Try to reconnect, if connection has been lost.
     * @param \Throwable $e
     * @throws \Throwable
     * @return \PDO|NULL
     */
    protected function reConnectIfNecessaryOrThrownError (\Throwable $e) {
        if (
            $this->isConnectionLost($e) &&
            $this->retryAttempts < $this->retryAttemptsTotal
        ) {
            $this->provider = NULL;
            $this->retryAttempts += 1;
            if ($this->retryDelay > 0.0) 
                usleep($this->retryDelay * 1000000);
            try {
                $this->connect();
            } catch (\Exception $e) { // backward compatibility
                $this->reConnectIfNecessaryOrThrownError($e);
            } catch (\Throwable $e) {
                $this->reConnectIfNecessaryOrThrownError($e);
            }
            return $this->provider;
        } else {
            $this->handleError($e); // throws \Throwable
            return NULL;
        }
    }
}