Database.php

<?php
/**
 * Created by PhpStorm.
 * User: crose
 * Date: 1/4/16
 * Time: 7:14 PM
 */

namespace qfq;

use qfq;
use qfq\CodeException;
use qfq\DbException;
use qfq\UserFormException;
use qfq\Support;
use qfq\Store;

require_once(__DIR__ . '/exceptions/UserFormException.php');
require_once(__DIR__ . '/exceptions/CodeException.php');
require_once(__DIR__ . '/exceptions/DbException.php');

require_once(__DIR__ . '/store/Store.php');
require_once(__DIR__ . '/helper/Support.php');
require_once(__DIR__ . '/helper/Logger.php');
require_once(__DIR__ . '/helper/BindParam.php');

/**
 * Class Database
 * @package qfq
 */
class Database {

    /**
     * @var Store
     */
    private $store = null;

    /**
     * @var \mysqli
     */
    private $mysqli = null;

    /**
     * @var \mysqli_stmt
     */
    private $mysqli_stmt = null;

    /**
     * @var \mysqli_result
     */
    private $mysqli_result = null;

    /**
     * @var string
     */
    private $sqlLog = '';

    /**
     * Returns current data base handle from Store[System][SYSTEM_DBH].
     * If not exists: open database and store the new dbh in Store[System][SYSTEM_DBH]
     *
     * @throws CodeException
     * @throws UserFormException
     */
    public function __construct() {
        $this->store = Store::getInstance();

        if ($this->mysqli === null) {
            $this->mysqli = $this->dbConnect();
        }
        $this->sqlLog = $this->store->getVar(SYSTEM_SQL_LOG, STORE_SYSTEM);

        // DB Init
        $dbInit = $this->store->getVar(SYSTEM_DB_INIT, STORE_SYSTEM);
        if ($dbInit !== false && $dbInit != '') {
            $this->sql($dbInit);
        }
    }


    /**
     * Open mysqli database connection if not already done.
     *
     * @return \mysqli
     * @throws UserFormException
     */
    private function dbConnect() {
        $mysqli = null;

        $dbuser = $this->store->getVar(SYSTEM_DB_USER, STORE_SYSTEM);
        $dbserver = $this->store->getVar(SYSTEM_DB_SERVER, STORE_SYSTEM);
        $dbpw = $this->store->getVar(SYSTEM_DB_PASSWORD, STORE_SYSTEM);
        $db = $this->store->getVar(SYSTEM_DB_NAME, STORE_SYSTEM);

        $mysqli = new \mysqli($dbserver, $dbuser, $dbpw, $db);

        if ($mysqli->connect_error) {
            throw new UserFormException ("Error open Database 'mysql:host=" . $dbserver . ";dbname=" . $db . ";dbuser=" . $dbuser . "'': " . $mysqli->connect_errno . PHP_EOL . $mysqli->connect_error, ERROR_DB_OPEN);
        }

        return $mysqli;
    }

    /**
     * Fires query $sql and fetches result as assoc array (all modes but ROW_KEYS) or as num array (mode: ROW_KEYS). Throws exception.
     *
     * $mode
     *  ROW_REGULAR: Return 2-dimensional assoc array. Every query row is one array row.
     *  ROW_IMPLODE_ALL: Return string. All cells of all rows imploded to one string.
     *  ROW_EXPECT_0: Return empty string if there is no record row, Else an exception.
     *  ROW_EXPECT_1: Return 1-dimensional assoc array if there are exact one row. Else an exception.
     *  ROW_EXPECT_0_1: Return empty string if there is no row. Return 1- dimensional assoc array if there is one row. Else an exception.
     *  ROW_EXPECT_GE_1: Like 'ROW_REGULAR'. Throws an exception if there is an empty resultset.
     *  ROW_KEYS: Return 2-dimensional num(!) array. Every query row is one array row. $keys are the column names.
     *
     * @param $sql
     * @param string $mode
     * @param array $parameterArray
     * @param string $specificMessage
     * @param array $keys
     * @param array $stat DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
     * @return array|int
     *      SELECT | SHOW | DESCRIBE | EXPLAIN:
     *          If no record found: a) ROW_EXPECT_0_1, ROW_EXPECT_1: empty string, b) All other modes: empty array
     *          If record(s) found: a) ROW_EXPECT_0_1, ROW_EXPECT_1: one dimensional array, b) All other modes: 2 dimensional array
     *      INSERT: last_insert_id
     *      UPDATE | DELETE | REPLACE: affected rows
     * @throws \qfq\CodeException
     * @throws \qfq\DbException
     */
    public function sql($sql, $mode = ROW_REGULAR, array $parameterArray = array(), $specificMessage = '', array &$keys = array(), array &$stat = array()) {
        $queryType = '';
        $result = array();
        $this->closeMysqliStmt();

        // CR often forgets to specify the $mode and use prepared statement with parameter instead.
        if (is_array($mode)) {
            throw new CodeException("Probably a parameter forgotten: \$mode ?");
        }

        // for error reporting in exception
        if ($specificMessage) {
            $specificMessage .= " ";
        }

        $count = $this->prepareExecute($sql, $parameterArray, $queryType, $stat);

        if ($count === false) {
            throw new DbException($specificMessage . "No idea why this error happens - please take some time and check this: $sql", ERROR_DB_GENERIC_CHECK);
        }

        if ($queryType === QUERY_TYPE_SELECT) {
            switch ($mode) {
                case ROW_IMPLODE_ALL:
                    $result = $this->fetchAll($mode);
                    break;
                case ROW_KEYS:
                case ROW_REGULAR:
                    $result = $this->fetchAll($mode, $keys);
                    break;
                case ROW_EXPECT_0:
                    if ($count === 0)
                        $result = array();
                    else
                        throw new DbException($specificMessage . "Expected no record, got $count rows: $sql", ERROR_DB_TOO_MANY_ROWS);
                    break;
                case ROW_EXPECT_1:
                    if ($count === 1)
                        $result = $this->fetchAll($mode)[0];
                    else
                        throw new DbException($specificMessage . "Expected one record, got $count: $sql", ERROR_DB_COUNT_DO_NOT_MATCH);
                    break;
                case ROW_EXPECT_0_1:
                    if ($count === 1)
                        $result = $this->fetchAll($mode)[0];
                    elseif ($count === 0)
                        $result = array();
                    else
                        throw new DbException($specificMessage . "Expected no record, got $count rows: $sql", ERROR_DB_TOO_MANY_ROWS);
                    break;
                case ROW_EXPECT_GE_1:
                    if ($count > 0)
                        $result = $this->fetchAll($mode);
                    else
                        throw new DbException($specificMessage . "Expected at least one record, got nothing: $sql", ERROR_DB_TOO_FEW_ROWS);
                    break;

                default:
                    throw new DbException($specificMessage . "Unknown mode: $mode", ERROR_UNKNOWN_MODE);
            }
        } elseif ($queryType === QUERY_TYPE_INSERT) {
            $result = $stat[DB_INSERT_ID];
        } else {
            $result = $count;
        }

        $this->closeMysqliStmt();

        return $result;
    }

    /**
     * Close an optional open MySQLi Statement.
     *
     * @throws \qfq\DbException
     */
    private function closeMysqliStmt() {

        if ($this->mysqli_result !== null && $this->mysqli_result !== false) {
            $this->mysqli_result->free_result();
        }

        if ($this->mysqli_stmt !== null && $this->mysqli_stmt !== false) {
            $this->mysqli_stmt->free_result();
            if (!$this->mysqli_stmt->close())
                throw new DbException('Error closing mysqli_stmt' . ERROR_DB_CLOSE_MYSQLI_STMT);
        }
        $this->mysqli_stmt = null;
        $this->mysqli_result = null;
    }

    /**
     * Execute a prepared SQL statement like SELECT, INSERT, UPDATE, DELETE, SHOW, ...
     *
     * Returns the number of selected rows (SELECT, SHOW, ..) or the affected rows (UPDATE, INSERT). $stat contains appropriate num_rows, insert_id or rows_affected.
     *
     * @param string $sql SQL statement with prepared statement variable.
     * @param array $parameterArray parameter array for prepared statement execution.
     * @param string $queryType returns QUERY_TYPE_SELECT | QUERY_TYPE_UPDATE | QUERY_TYPE_INSERT, depending on the query.
     * @param array $stat DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
     * @return int|mixed
     * @throws \qfq\CodeException
     * @throws \qfq\DbException
     * @throws \qfq\UserFormException
     */
    private function prepareExecute($sql, array $parameterArray = array(), &$queryType, array &$stat) {

        $sqlLogMode = $this->isSqlModify($sql) ? SQL_LOG_MODE_MODIFY : SQL_LOG_MODE_ALL;;
        $result = 0;
        $stat = array();

        $this->store->setVar(SYSTEM_SQL_FINAL, $sql, STORE_SYSTEM);
        $this->store->setVar(SYSTEM_SQL_PARAM_ARRAY, $parameterArray, STORE_SYSTEM);

        // Logfile
        $this->dbLog($sqlLogMode, $sql, $parameterArray);

        if (false === ($this->mysqli_stmt = $this->mysqli->prepare($sql))) {
            $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
            throw new DbException('[ mysqli: ' . $this->mysqli->errno . ' ] ' . $this->mysqli->error, ERROR_DB_PREPARE);
        }

        if (count($parameterArray) > 0) {
            if (false === $this->prepareBindParam($parameterArray)) {
                $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
                throw new DbException('[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error, ERROR_DB_BIND);
            }
        }

        if (false === $this->mysqli_stmt->execute()) {
            $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
            throw new DbException('[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error, ERROR_DB_EXECUTE);
        }

        $msg = '';
        $count = 0;
        $command = strtoupper(explode(' ', $sql, 2)[0]);
        switch ($command) {
            case 'SELECT':
            case 'SHOW':
            case 'DESCRIBE':
            case 'EXPLAIN':
                if (false === ($result = $this->mysqli_stmt->get_result())) {
                    throw new DbException('[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error, ERROR_DB_EXECUTE);
                }
                $queryType = QUERY_TYPE_SELECT;
                $this->mysqli_result = $result;
                $stat[DB_NUM_ROWS] = $this->mysqli_result->num_rows;
                $count = $stat[DB_NUM_ROWS];
                $msg = 'Read rows: ' . $stat[DB_NUM_ROWS];
                break;
            case 'REPLACE':
            case 'INSERT':
                $queryType = QUERY_TYPE_INSERT;
                $stat[DB_INSERT_ID] = $this->mysqli->insert_id;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
            $msg = 'ID: ' . $this->mysqli->insert_id;
                break;
            case 'UPDATE':
            case 'DELETE':
            case 'TRUNCATE':
                $queryType = QUERY_TYPE_UPDATE;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = 'Affected rows: ' . $count;
                break;

            case 'SET':
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
                break;

            default:
                throw new DbException('Unknown comand: "' . $command . '"', ERROR_DB_UNKNOWN_COMMAND);
                break;
        }

        $this->store->setVar(SYSTEM_SQL_COUNT, $count, STORE_SYSTEM);

        // Logfile
        $this->dbLog($sqlLogMode, $msg);

        return $count;
    }

    /**
     * Check if the given SQL Statement might modify data.
     *
     * @param $sql
     * @return bool  true is the statement might modify data, else: false
     */
    private function isSqlModify($sql) {
        $command = explode(' ', $sql, 2);
        switch (strtoupper($command[0])) {
            case 'INSERT':
            case 'UPDATE':
            case 'DELETE':
            case 'REPLACE':
            case 'TRUNCATE':
                return true;
        }
        return false;
    }

    /**
     * Decide if the SQL statement has to be logged. If yes, create a timestamp and do the log.
     *
     * @param $sql
     * @param array $parameterArray
     * @return string
     * @throws \qfq\UserFormException
     */
    private function dbLog($mode = SQL_LOG_MODE_ALL, $sql = '', $parameterArray = array()) {

        $status = '';

        $sqlLogMode = $this->store->getVar(SYSTEM_SQL_LOG_MODE, STORE_SYSTEM);

        switch ($mode) {
            case SQL_LOG_MODE_ALL:
                if ($sqlLogMode != SQL_LOG_MODE_ALL) {
                    return;
                }
                break;

            case SQL_LOG_MODE_MODIFY:
                break;

            case SQL_LOG_MODE_ERROR:
                break;

            default:
                throw new UserFormException("Unknown SQL_LOG_MODE: $mode", ERROR_UNKNOWN_SQL_LOG_MODE);
        }

        // Client IP Address
        $remoteAddress = $this->store->getVar(CLIENT_REMOTE_ADDRESS, STORE_CLIENT);

        $msg = '[' . date('Y.m.d H:i:s O') . '][' . $remoteAddress . ']';

//        // FE User
//        $feUser = $this->sqlLog = $this->store->getVar(TYPO3_FE_USER, STORE_TYPO3);
//        $pageId = $this->sqlLog = $this->store->getVar(TYPO3_PAGE_ID, STORE_TYPO3);
//        $ttcontentId = $this->sqlLog = $this->store->getVar(TYPO3_TT_CONTENT_UID, STORE_TYPO3);

        if (count($parameterArray) > 0) {
            $sql = $this->preparedStatementInsertParameter($sql, $parameterArray);
        }

        if ($sql !== '') {
            if ($mode == SQL_LOG_MODE_ERROR) {
                $status = 'FAILED: ';
            }
            $msg .= '[' . $status . $sql . ']';
        }

        Logger::logMessage($msg, $this->sqlLog);
    }

    /**
     * @param $sql
     * @param $parameterArray
     * @return string
     */
    private function preparedStatementInsertParameter($sql, $parameterArray) {
        $msg = '';

        $sqlArray = explode('?', $sql);
        $ii = 0;
        foreach ($parameterArray as $value) {
            if (isset($sqlArray[$ii])) {
                if (is_array($value)) {
                    $value = OnArray::toString($value);
                }

                $msg .= $sqlArray[$ii++] . "'" . $value . "'";
            } else {
                $msg = '?';
            }
        }
        if (isset($sqlArray[$ii])) {
            $msg .= $sqlArray[$ii];
        }

        return $msg;
    }

    /**
     * @param $arr
     */
    private function prepareBindParam($arr) {

        $bindParam = new BindParam();

        for ($ii = 0; $ii < count($arr); $ii++) {
            $bindParam->add($arr[$ii]);
        }
        call_user_func_array([$this->mysqli_stmt, 'bind_param'], $bindParam->get());
    }

    /**
     * Fetch all rows of the result as associative array.
     *
     * mode:
     *  ROW_IMPLODE_ALL: Return string. All cells of all rows imploded to one string.
     *  ROW_KEYS: Return num array with column names in $keys
     *  default: Return 2-dimensional assoc array
     *
     * @param string $mode
     * @param array $keys
     * @return array|bool|mixed|string false in case of an error.
     *              Empty string is returned if the query didn't yield any rows.
     *              All rows as Multi Assoc array if $mode!=IMPLODE_ALL.
     *              All rows and all columns imploded to one string if $mode=IMPLODE_ALL
     *
     */
    private function fetchAll($mode = '', &$keys = array()) {
        if ($this->mysqli_result == null || $this->mysqli_result == false) {
            return false;
        }

        if ($this->mysqli_result->num_rows === 0) {
            return ($mode === ROW_IMPLODE_ALL) ? "" : array();
        }

        switch ($mode) {
            case ROW_IMPLODE_ALL:
                $str = "";
                foreach ($this->mysqli_result->fetch_all(MYSQLI_NUM) as $row) {
                    $str .= implode($row);
                }
                return $str;
                break;

            case ROW_KEYS:
                $keys = array();

                for ($ii = 0; $ii < $this->mysqli_result->field_count; $ii++) {
                    $keys[$ii] = $this->mysqli_result->fetch_field_direct($ii)->name;
                }
                return $this->mysqli_result->fetch_all(MYSQLI_NUM);
                break;

            default:
                return $this->mysqli_result->fetch_all(MYSQLI_ASSOC);
        }
    }

    /**
     * Return the number of rows returned by the last call to execute().
     *
     * If execute() has never been called, returns FALSE.
     *
     * @return mixed Number of rows returned by last call to execute(). If Database::execute()
     *     has never been called prior a call to this method, false is returned.
     */
    public function getRowCount() {
        if ($this->mysqli_result == null) {
            return false;
        }

        return $this->mysqli_result->num_rows;
    }

    /**
     * Get the values for a given ENUM or SET column
     *
     * @param string $table name of the table
     * @param string $columnName name of the column
     *
     * @throws UserFormException if the table or column does not exist, or is not of type ENUM or SET
     * @return array
     */
    public function getEnumSetValueList($table, $columnName) {

        $columnDefinition = $this->getFieldDefinitionFromTable($table, $columnName);
        $setEnumDefinition = $columnDefinition["Type"];

        // $setEnumDefinition holds now a string like
        // String:  enum('','red','blue','green')
        $len = mb_strlen($setEnumDefinition);

        # "enum('" = 6, "set('" = 5
        $tokenLength = strpos($setEnumDefinition, "'") + 1;

        // count("enum('") == 6, count("')") == 2
        $enumSetString = mb_substr($setEnumDefinition, $tokenLength, $len - (2 + $tokenLength));

        // String: ','red','blue','green

        if (($setEnumValueList = explode("','", $enumSetString)) === false) {
            return array();
        }

        return $setEnumValueList;
    }

    /**
     * Get database column definition.
     *
     * If the column is not found in the table, an exception is thrown.
     *
     * @param string $table name of the table
     *
     * @param string $columnName name of the column
     * @return array the definition of the column as retrieved by Database::getTableDefinition().
     *
     * @throws \qfq\DbException
     */
    private function getFieldDefinitionFromTable($table, $columnName) {
        $tableDefinition = $this->getTableDefinition($table);
        foreach ($tableDefinition AS $row) {
            if ($row["Field"] == $columnName) {
                return $row;
            }
        }
        throw new DbException("Column name '$columnName' not found in table '$table'.", ERROR_DB_COLUMN_NOT_FOUND_IN_TABLE);
    }

    /**
     * Get all column definitions for a table. Return Assoc Array:
     *
     * Field      Type                      Null    Key    Default    Extra
     * --------------------------------------------------------------------------
     * id         bigint(20)                 NO     PRI    NULL    auto_increment
     * name       varchar(128)               YES           NULL
     * firstname  varchar(128)               YES           NULL
     * gender     enum('','male','female')   NO            male
     * groups     set('','a','b','c')        NO            a
     *
     * @param string $table table to retrieve column definition from
     *
     * @return array column definition of table as returned by SHOW FIELDS FROM as associative array.
     */
    public function getTableDefinition($table) {
        return $this->sql("SHOW FIELDS FROM `$table`");
    }

    /**
     * Wrapper for sql(), to simplyfy access.
     *
     * @param $sql
     * @param array $keys
     * @param array $stat
     * @return array|bool
     * @throws \qfq\CodeException
     * @throws \qfq\DbException
     */
    public function sqlKeys($sql, array &$keys, array &$stat = array()) {

        return $this->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
    }

    /**
     * Returns lastInsertId
     *
     * @return string
     */
    public function getLastInsertId() {
        // Do not try to use $this->mysqli->lastInsertId - this is not valid at any given time.
        return $this->mysqli->insert_id;
    }

    /**
     * Searches for the table '$name'.
     *
     * @param $name
     * @return bool  true if found, else false
     */
    public function existTable($name) {
        $found = false;

        $tables = $this->sql("SHOW tables");

        foreach ($tables as $t) {
            foreach ($t as $key => $value) {
                if ($value === $name) {
                    $found = true;
                    break 2;
                }
            }
        }

        return $found;
    }

}