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__ . '/../store/Config.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 = '';

    /**
     * @var array
     */
    private $sqlLogModePrio = [SQL_LOG_MODE_NONE => 1, SQL_LOG_MODE_ERROR => 2, SQL_LOG_MODE_MODIFY => 3, SQL_LOG_MODE_ALL => 4];

    /**
     * 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($mode = MODE_DB_REGULAR) {
        $dbInit = '';

        switch ($mode) {
            case MODE_DB_REGULAR:
                $this->store = Store::getInstance();
                $config = $this->store->getStore(STORE_SYSTEM);
                $this->sqlLog = $this->store->getVar(SYSTEM_SQL_LOG, STORE_SYSTEM);
                $dbInit = $this->store->getVar(SYSTEM_DB_INIT, STORE_SYSTEM);
                break;
            case MODE_DB_NO_LOG:
                $configClass = new Config();
                $config = $configClass->readConfig();
                break;
            default:
                throw new \qfq\CodeException('Unknown mode: ' . $mode, ERROR_UNKNOWN_MODE);
        }

        if ($this->mysqli === null) {
            $this->mysqli = $this->dbConnect($config);
        }

        // DB Init

        if ($dbInit !== false && $dbInit != '') {
            $this->sql($dbInit);
        }
    }


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

        $mysqli = new \mysqli($config[SYSTEM_DB_SERVER], $config[SYSTEM_DB_USER], $config[SYSTEM_DB_PASSWORD], $config[SYSTEM_DB_NAME]);

        if ($mysqli->connect_error) {
            throw new UserFormException ("Error open Database 'mysql:host=" . $config[SYSTEM_DB_SERVER] . ";dbname=" . $config[SYSTEM_DB_NAME] . ";dbuser=" . $config[SYSTEM_DB_USER] . "'': " . $mysqli->connect_errno . PHP_EOL . $mysqli->connect_error, ERROR_DB_OPEN);
        }

        // Necessary that mysqli::real_escape_string() functions properly.
        if (!$mysqli->set_charset('utf8')) {
            throw new UserFormException ("Error set_charset('utf8') Database: " . $mysqli->connect_errno . PHP_EOL . $mysqli->connect_error, ERROR_DB_SET_CHARSET);
        }

        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();

        if ($this->store !== null) {
            $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':
            case 'ALTER':
            case 'DROP':
                $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;
        }

        if ($this->store !== null) {
            $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($currentQueryMode = SQL_LOG_MODE_ALL, $sql = '', $parameterArray = array()) {

        if ($sql == '') {
            return;
        }

        $status = '';

        // If no sqlLogMode is defined/available, choose SQL_LOG_MODE_ERROR
        $sqlLogMode = $this->store->getVar(SYSTEM_SQL_LOG_MODE, STORE_SYSTEM);
        if ($sqlLogMode === false) {
            $sqlLogMode = SQL_LOG_MODE_ERROR;
        }

        // Check if string is known.
        foreach ([$sqlLogMode, $currentQueryMode] as $mode) {
            if (!isset($this->sqlLogModePrio[$mode])) {
                throw new UserFormException("Unknown SQL_LOG_MODE: $mode", ERROR_UNKNOWN_SQL_LOG_MODE);
            }
        }

        // Log?
        if ($this->sqlLogModePrio[$sqlLogMode] < ($this->sqlLogModePrio[$currentQueryMode])) {
            return;
        }

        // Client IP Address
        $remoteAddress = ($this->store === null) ? '0.0.0.0' : $this->store->getVar(CLIENT_REMOTE_ADDRESS, STORE_CLIENT);


        $logArr = [
            ['FE', TYPO3_FE_USER, STORE_TYPO3],
            ['Page', TYPO3_PAGE_ID, STORE_TYPO3],
            ['tt', TYPO3_TT_CONTENT_UID, STORE_TYPO3],
            ['level', SYSTEM_REPORT_FULL_LEVEL, STORE_SYSTEM],
            ['form', SIP_FORM, STORE_SIP],
        ];

        $t3msg = '';
        foreach ($logArr as $logItem) {
            $value = $this->store->getVar($logItem[1], $logItem[2]);
            if (!empty($value)) {
                $t3msg .= $logItem[0] . ":" . $value . ",";
            }
        }
        $t3msg = substr($t3msg, 0, strlen($t3msg) - 1);

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

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

        if ($currentQueryMode == 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;
    }

    /**
     * Depending on $sql reads FormElements to a specific container or all. Preprocess all FormElements.
     *
     * @param string $sql SQL_FORM_ELEMENT_SPECIFIC_CONTAINER | SQL_FORM_ELEMENT_ALL_CONTAINER
     * @param array $param Parameter which matches the prepared statement in $sql
     * @param array $formSpec Main FormSpec to copy generic parameter to FormElements
     * @return array|int
     * @throws \qfq\CodeException
     * @throws \qfq\DbException$this->feSpecNative
     */
    public function getNativeFormElements($sql, array $param, $formSpec) {

        $feSpecNative = $this->sql($sql, ROW_REGULAR, $param);

        $feSpecNative = HelperFormElement::formElementSetDefault($feSpecNative);

        // Explode and Do $FormElement.parameter
        HelperFormElement::explodeParameterInArrayElements($feSpecNative, FE_PARAMETER);

        // Check for retype FormElements which have to duplicated.
        $feSpecNative = HelperFormElement::duplicateRetypeElements($feSpecNative);

        // Copy Attributes to FormElements
        $feSpecNative = HelperFormElement::copyAttributesToFormElements($formSpec, $feSpecNative);

        return $feSpecNative;
    }

    /**
     * Checks if there is the SQL keyword 'limit' at the end of the SQL statement.
     * returns true for '... LIMIT', '.... LIMIT 1, ... LIMIT 1,2, ... LIMIT 1 , 2
     *
     * @param $sql
     * @return bool
     */
    public function hasLimit($sql) {

        $sql = trim(strtolower($sql));
        $arr = explode(' ', $sql);

        $ii = 3;
        array_pop($arr); // the last token can't be 'limit'

        while ($ii > 0) {
            $item = array_pop($arr);
            if ($item === null) {
                return false;
            }
            if ($item != '') {
                if ($item == 'limit') {
                    return true;
                } else {
                    $ii--;
                }
            }
        }

        return false;
    }

    /**
     * $arr will be converted to a two column array with keys $keyName1 and $keyName2.
     * $arr might contain one or more columns.
     * Only when $keyName1 and $keyName2 exist, those will be used. Else the first column becomes $keyName1 and the second becomes $keyName2.
     * If there is only one column, that column will be doubled.
     *
     * @param array $arr
     * @param string $srcColumn1
     * @param string $srcColumn2
     * @param string $destColumn1
     * @param string $destColumn2
     * @return array
     */
    public function makeArrayDict(array $arr, $srcColumn1, $srcColumn2, $destColumn1 = '', $destColumn2 = '') {

        if ($destColumn1 == '') {
            $destColumn1 = $srcColumn1;
        }

        if ($destColumn2 == '') {
            $destColumn2 = $srcColumn2;
        }

        $new = array();

        if ($arr == array() || $arr === null) {
            return array();
        }

        $row = $arr[0];
        $keys = array_keys($row);
        if (count($row) < 2) {
            $column1 = $keys[0];
            $column2 = $keys[0];
        } elseif (array_key_exists($srcColumn1, $row) && array_key_exists($srcColumn2, $row)) {
            $column1 = $srcColumn1;
            $column2 = $srcColumn2;
        } else {
            $column1 = $keys[0];
            $column2 = $keys[1];
        }

        $row = array_shift($arr);
        while (null !== $row) {
            $new[] = [$destColumn1 => $row[$column1], $destColumn2 => $row[$column2]];
            $row = array_shift($arr);
        }

        return $new;
    }

    /**
     * Proxy for mysqli::real_escape_string()
     *
     * @param string $value
     * @return string
     */
    public function realEscapeString($value) {
        return $this->mysqli->real_escape_string($value);
    }

    /**
     * @param $filename
     * @throws \qfq\CodeException
     */
    public function playSqlFile($filename) {

        $sqlStatements = file_get_contents($filename);

        if (false === $this->mysqli->multi_query($sqlStatements)) {
            throw new CodeException("Error playing $filename", ERROR_PLAY_SQL_FILE);
        }

        // discard all results: this is important - if missed, following calls on $mysqli will fail.
        do {
            if ($res = $this->mysqli->store_result()) {
                $res->free();
            }
        } while ($this->mysqli->more_results() && $this->mysqli->next_result());

    }
}