Database.php

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

namespace IMATHUZH\Qfq\Core\Database;

 
use IMATHUZH\Qfq\Core\Store\Store;
use IMATHUZH\Qfq\Core\Helper\Logger;
use IMATHUZH\Qfq\Core\Helper\BindParam;
use IMATHUZH\Qfq\Core\Helper\HelperFormElement;
use IMATHUZH\Qfq\Core\Helper\OnArray;
use IMATHUZH\Qfq\Core\Helper\HelperFile;

/**
 * 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];

    private $dbName = '';

    /**
     * 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]
     *
     * @param string $dbIndex Typically '1' for Data, optional 2 for external Form/FormElement
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function __construct($dbIndex = DB_INDEX_DEFAULT) {

        if (empty($dbIndex)) {
            $dbIndex = DB_INDEX_DEFAULT;
        }

        $this->store = Store::getInstance();
        $storeSystem = $this->store->getStore(STORE_SYSTEM);

        $this->sqlLog = $storeSystem[SYSTEM_SQL_LOG];
        $dbInit = $storeSystem[SYSTEM_DB_INIT];

        $config = $this->getConnectionDetails($dbIndex, $storeSystem);
        $this->dbName = $config[SYSTEM_DB_NAME];

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

        // DB Init
        if ($dbInit !== false && $dbInit != '') {
            $arr = explode(';', $dbInit);
            foreach ($arr AS $sql) {
                $sql = trim($sql);
                if ('' != $sql) {
                    $this->sql($sql);
                }
            }
        }
    }

    /**
     * @return mixed|string
     */
    public function getDbName() {
        return $this->dbName;
    }

    /**
     * Depending on $dbIndex, read DB_?_SERVER ... crendentials.
     * If $dbIndex==1 but no DB_1_xxx specified, take the default DB_xxxx - old config.qfq.ini style
     *
     * @param $dbIndex 1,2,...
     *
     * @param array $config
     * @return array
     * @throws \UserFormException
     */
    private function getConnectionDetails($dbIndex, array $config) {

        if (isset($config["DB_" . $dbIndex . "_SERVER"])) {
            $config[SYSTEM_DB_SERVER] = $config["DB_" . $dbIndex . "_SERVER"];
            $config[SYSTEM_DB_USER] = $config["DB_" . $dbIndex . "_USER"];
            $config[SYSTEM_DB_PASSWORD] = $config["DB_" . $dbIndex . "_PASSWORD"];
            $config[SYSTEM_DB_NAME] = $config["DB_" . $dbIndex . "_NAME"];
        } elseif ($dbIndex != 1) {
            // Backward compatibility: old configs use SYSTEM_DB_SERVER (without index) and index=1 might mean 'legacy config'.
            throw new \UserFormException("DB Handle not found in config: $dbIndex", ERROR_INVALID_VALUE);
        }

        return $config;
    }

    /**
     * Open mysqli database connection if not already done.
     *
     * @param $config
     * @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 (
                json_encode([ERROR_MESSAGE_TO_USER => 'Error open Database',
                    ERROR_MESSAGE_TO_DEVELOPER => "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 (
                json_encode([ERROR_MESSAGE_TO_USER => "Error set_charset('utf8')",
                    ERROR_MESSAGE_TO_DEVELOPER => "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 array 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: see $mode
     *      INSERT: last_insert_id
     *      UPDATE | DELETE | REPLACE: affected rows
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public function sql($sql, $mode = ROW_REGULAR, array $parameterArray = array(), $specificMessage = '', array &$keys = array(), array &$stat = array()) {
        $queryType = '';
        $result = array();
        $this->closeMysqliStmt();

        // CR (& EV) 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, $specificMessage);

        if ($count === false) {
            throw new \DbException($specificMessage . "No idea why this error happens - please take some time and check the problem.", 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 none row, got $count rows", 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 row, got $count rows", 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 zero or one rows, got $count rows", 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 row, got none", 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();

        $this->store->setVar(SYSTEM_SQL_RAW, '', STORE_SYSTEM);
        $this->store->setVar(SYSTEM_SQL_FINAL, '', STORE_SYSTEM);
        $this->store->setVar(SYSTEM_SQL_PARAM_ARRAY, '', STORE_SYSTEM);

        return $result;
    }

    /**
     * Close an optional open MySQLi Statement.
     *
     * @throws \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;
    }

    /**
     * Checks the problematic $sql if there is a common mistake.
     * If something is found, give a hint.
     *
     * @param string $sql
     * @param string $errorMsg
     * @return string
     */
    private function getSqlHint($sql, $errorMsg) {
        $msg = '';

        // Check if there is a comma before FROM: 'SELECT ... , FROM ...'
        $pos = stripos($sql, ' FROM ');
        if ($pos !== false && $pos > 0 && $sql[$pos - 1] == ',') {
            $msg .= "HINT: Remove extra ',' before FROM\n";
        }

        // Look for QFQ variables which haven't been replaced
        $matches = array();
        preg_match_all("/{{[^}}]*}}/", $sql, $matches);
        // '.line.count' might be replaced later and should not shown.
        foreach ($matches[0] as $key => $value) {
            if (false !== stripos($value, '.line.count')) {
                unset($matches[0][$key]);
            }
        }
        if (count($matches[0]) > 0) {
            $msg .= "HINT: The following variables couldn't be replaced: " . implode(', ', $matches[0]) . "\n";
        }

        // Look for missing '()' after FROM in case LEFT JOIN is used.
        $pos = stripos($sql, ' LEFT JOIN ');
        if (stripos($errorMsg, 'Unknown column') !== false && $pos !== false && ($sql[$pos - 1] ?? '') != ')') {
            $msg .= "HINT: Maybe the tables after 'FROM' should be enclosed by '()' \n";
        }

        // Check for double comma
        if (stripos($errorMsg, 'the right syntax to use near') && preg_match('/,[ ]*,/', $sql)) {
            $msg .= "HINT: There seems to be a double comma in your query.\n";
        }

        return $msg;
    }

    /**
     * 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
     *
     * @param string $specificMessage
     * @return int|mixed
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function prepareExecute($sql, array $parameterArray, &$queryType, array &$stat, $specificMessage = '') {

        $sqlLogMode = $this->isSqlModify($sql) ? SQL_LOG_MODE_MODIFY : SQL_LOG_MODE_ALL;
        $result = 0;
        $stat = array();
        $errorMsg[ERROR_MESSAGE_TO_USER] = empty($specificMessage) ? 'SQL error' : $specificMessage;

        if ($this->store !== null) {
            $this->store->setVar(SYSTEM_SQL_FINAL, $sql, STORE_SYSTEM);
            $this->store->setVar(SYSTEM_SQL_PARAM_ARRAY, $parameterArray, STORE_SYSTEM);
        }

//        if ($specificMessage !== '') {
//            $specificMessage = ' - ' . $specificMessage;
//        }
        // Logfile
        $this->dbLog($sqlLogMode, $sql, $parameterArray);

        if (false === ($this->mysqli_stmt = $this->mysqli->prepare($sql))) {
            $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $this->getSqlHint($sql, $this->mysqli->error);
            $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $this->mysqli->errno . ' ] ' . $this->mysqli->error;

            throw new \DbException(json_encode($errorMsg), ERROR_DB_PREPARE);
        }

        if (count($parameterArray) > 0) {
            if (false === $this->prepareBindParam($parameterArray)) {
                $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
                $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $this->getSqlHint($sql, $this->mysqli->error);
                $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error;

                throw new \DbException(json_encode($errorMsg), ERROR_DB_BIND);
            }
        }

        if (false === $this->mysqli_stmt->execute()) {
            $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $this->getSqlHint($sql, $this->mysqli->error);
            $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error;

            throw new \DbException(json_encode($errorMsg), 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(
                        json_encode([ERROR_MESSAGE_TO_USER => 'Error DB execute', ERROR_MESSAGE_TO_DEVELOPER => '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error . $specificMessage]),
                        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':
            case 'CREATE':
            case 'CALL':
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
                break;

            default:
                // Unknown command: treat it as a control command
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
                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 string $currentQueryMode
     * @param string $sql
     * @param array $parameterArray
     *
     * @throws \CodeException
     * @throws \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],
            ['FESU', TYPO3_FE_USER, STORE_USER],
            ['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
     *
     * @return array
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    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 \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function getFieldDefinitionFromTable($table, $columnName) {

        $tableDefinition = $this->getTableDefinition($table);
        foreach ($tableDefinition AS $row) {
            if ($row["Field"] == $columnName) {
                return $row;
            }
        }

        throw new \DbException(
            json_encode([ERROR_MESSAGE_TO_USER => 'Column name not found', ERROR_MESSAGE_TO_DEVELOPER => "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.
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    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 \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    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
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    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;
    }

    /**
     * @param $table
     * @param $columnDefinition
     * @param $mode
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public function createTable($table, $columnDefinition, $mode) {

        $cols = array();

        if (!$this->existTable($table)) {
            $sql = "CREATE TABLE $table (";
            foreach ($columnDefinition AS $key => $value) {
                $cols[] = "`" . $key . "` " . $value . " NOT NULL,";
            }
            $sql .= implode(',', $cols);
            $sql .= ") ENGINE=InnoDB DEFAULT CHARSET=utf8_general_ci";

            $this->sql($sql);
        }

        if ($mode == IMPORT_MODE_REPLACE) {
            $this->sql("TRUNCATE $table");
        }
    }

    /**
     * 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 \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function getNativeFormElements($sql, array $param, $formSpec) {

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

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

        // 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 $sqlStatements
     *
     * @throws \CodeException
     */
    public function playMultiQuery($sqlStatements) {

        $executed = $this->mysqli->multi_query($sqlStatements);
        if (false === $executed) {
            $errorMsg[ERROR_MESSAGE_TO_USER] = 'SQL Error.';
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = "Error playing multi query: " . $sqlStatements;
            throw new \CodeException(json_encode($errorMsg), ERROR_PLAY_SQL_MULTIQUERY);
        }

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

    }

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

        $query = file_get_contents($filename);

        try {
            $this->playMultiQuery($query);
        }
        catch (\CodeException $e) {
            throw new \CodeException("Error playing $filename", ERROR_PLAY_SQL_FILE);