Database.php 27.7 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
<?php
/**
 * Created by PhpStorm.
 * User: crose
 * Date: 1/4/16
 * Time: 7:14 PM
 */

namespace qfq;

use qfq;

13
14
15
require_once(__DIR__ . '/../exceptions/UserFormException.php');
require_once(__DIR__ . '/../exceptions/CodeException.php');
require_once(__DIR__ . '/../exceptions/DbException.php');
16

17
18
19
20
21
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');
22

Carsten  Rose's avatar
Carsten Rose committed
23
24
25
26
/**
 * Class Database
 * @package qfq
 */
27
28
29
class Database {

    /**
30
     * @var Store
31
32
     */
    private $store = null;
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

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

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

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

49
50
51
    /**
     * @var string
     */
52
    private $sqlLog = '';
53

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

59
60
    private $dbName = '';

61
62
63
64
    /**
     * 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]
     *
65
66
     * @param int $dbIndex Typically '1' for Data, optional 2 for external Form/FormElement
     *
67
     * @throws CodeException
68
     * @throws DbException
69
     * @throws UserFormException
70
     */
71
72
73
74
    public function __construct($dbIndex = DB_INDEX_DEFAULT) {
        if (empty($dbIndex)) {
            $dbIndex = DB_INDEX_DEFAULT;
        }
75

76
        $this->store = Store::getInstance();
77
78
79
80
        $storeSystem = $this->store->getStore(STORE_SYSTEM);

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

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

85
        if ($this->mysqli === null) {
86
            $this->mysqli = $this->dbConnect($config);
87
        }
88
89
90
91
92

        // DB Init
        if ($dbInit !== false && $dbInit != '') {
            $this->sql($dbInit);
        }
93
94
    }

95
96
97
98
    public function getDbName() {
        return $this->dbName;
    }

99
100
101
102
103
104
105
106
107
108
    /**
     * 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,...
     *
     * @return array
     * @throws CodeException
     * @throws UserFormException
     */
109
    private function getConnectionDetails($dbIndex, array $config) {
110
111
112
113
114
115
116

        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) {
117
            // Backward compatibility: old configs use SYSTEM_DB_SERVER (without index) and index=1 might mean 'legacy config'.
118
119
120
121
122
            throw new UserFormException("DB Handle not found in config: $dbIndex", ERROR_INVALID_VALUE);
        }

        return $config;
    }
123

124
    /**
125
126
127
     * Open mysqli database connection if not already done.
     *
     * @return \mysqli
128
     * @throws UserFormException
129
     */
130
    private function dbConnect($config) {
131
132
        $mysqli = null;

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

        if ($mysqli->connect_error) {
136
            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);
137
138
        }

139
140
141
142
143
        // 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);
        }

144
        return $mysqli;
145
146
147
    }

    /**
Carsten  Rose's avatar
Carsten Rose committed
148
149
     * Fires query $sql and fetches result as assoc array (all modes but ROW_KEYS) or as num array (mode: ROW_KEYS).
     * Throws exception.
150
     *
151
     * $mode
152
153
     *  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.
154
     *  ROW_EXPECT_0: Return empty string if there is no record row, Else an exception.
155
     *  ROW_EXPECT_1: Return 1-dimensional assoc array if there are exact one row. Else an exception.
Carsten  Rose's avatar
Carsten Rose committed
156
157
     *  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.
158
     *  ROW_KEYS: Return 2-dimensional num(!) array. Every query row is one array row. $keys are the column names.
159
     *
Carsten  Rose's avatar
Carsten Rose committed
160
     * @param        $sql
161
     * @param string $mode
Carsten  Rose's avatar
Carsten Rose committed
162
     * @param array $parameterArray
163
     * @param string $specificMessage
Carsten  Rose's avatar
Carsten Rose committed
164
165
     * @param array $keys
     * @param array $stat DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
Carsten  Rose's avatar
Carsten Rose committed
166
     *
167
     * @return array|int
168
     *      SELECT | SHOW | DESCRIBE | EXPLAIN: see $mode
169
170
     *      INSERT: last_insert_id
     *      UPDATE | DELETE | REPLACE: affected rows
171
172
     * @throws \qfq\CodeException
     * @throws \qfq\DbException
173
     */
174
    public function sql($sql, $mode = ROW_REGULAR, array $parameterArray = array(), $specificMessage = '', array &$keys = array(), array &$stat = array()) {
175
        $queryType = '';
176
177
        $result = array();
        $this->closeMysqliStmt();
178
179

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

184
        // for error reporting in exception
185
        if ($specificMessage) {
186
            $specificMessage .= " ";
187
        }
188

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

191
        if ($count === false) {
192
            throw new DbException($specificMessage . "No idea why this error happens - please take some time and check the problem.", ERROR_DB_GENERIC_CHECK);
193
194
        }

195
196
197
        if ($queryType === QUERY_TYPE_SELECT) {
            switch ($mode) {
                case ROW_IMPLODE_ALL:
198
                    $result = $this->fetchAll($mode);
199
200
201
202
203
204
                    break;
                case ROW_KEYS:
                case ROW_REGULAR:
                    $result = $this->fetchAll($mode, $keys);
                    break;
                case ROW_EXPECT_0:
205
                    if ($count === 0) {
206
                        $result = array();
207
                    } else {
208
                        throw new DbException($specificMessage . "Expected none row, got $count rows", ERROR_DB_TOO_MANY_ROWS);
209
                    }
210
211
                    break;
                case ROW_EXPECT_1:
212
                    if ($count === 1) {
213
                        $result = $this->fetchAll($mode)[0];
214
                    } else {
215
                        throw new DbException($specificMessage . "Expected one row, got $count rows", ERROR_DB_COUNT_DO_NOT_MATCH);
216
                    }
217
218
                    break;
                case ROW_EXPECT_0_1:
219
                    if ($count === 1) {
220
                        $result = $this->fetchAll($mode)[0];
221
                    } elseif ($count === 0) {
222
                        $result = array();
223
                    } else
224
                        throw new DbException($specificMessage . "Expected zero or one rows, got $count rows", ERROR_DB_TOO_MANY_ROWS);
225
226
                    break;
                case ROW_EXPECT_GE_1:
227
                    if ($count > 0) {
228
                        $result = $this->fetchAll($mode);
229
                    } else {
230
                        throw new DbException($specificMessage . "Expected at least one row, got none", ERROR_DB_TOO_FEW_ROWS);
231
                    }
232
                    break;
233

234
235
236
                default:
                    throw new DbException($specificMessage . "Unknown mode: $mode", ERROR_UNKNOWN_MODE);
            }
237
238
        } elseif ($queryType === QUERY_TYPE_INSERT) {
            $result = $stat[DB_INSERT_ID];
239
240
        } else {
            $result = $count;
241
242
        }

243
244
        $this->closeMysqliStmt();

245
246
247
248
        $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);

249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
        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();
265
            if (!$this->mysqli_stmt->close()) {
266
                throw new DbException('Error closing mysqli_stmt' . ERROR_DB_CLOSE_MYSQLI_STMT);
267
            }
268
269
270
        }
        $this->mysqli_stmt = null;
        $this->mysqli_result = null;
271
272
273
    }

    /**
274
     * Execute a prepared SQL statement like SELECT, INSERT, UPDATE, DELETE, SHOW, ...
275
     *
Carsten  Rose's avatar
Carsten Rose committed
276
277
278
     * Returns the number of selected rows (SELECT, SHOW, ..) or the affected rows (UPDATE, INSERT). $stat contains
     * appropriate num_rows, insert_id or rows_affected.
     *
Carsten  Rose's avatar
Carsten Rose committed
279
280
281
     * @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
Carsten  Rose's avatar
Carsten Rose committed
282
     *                               the query.
Carsten  Rose's avatar
Carsten Rose committed
283
     * @param array $stat DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
284
     *
285
286
     * @return int|mixed
     * @throws \qfq\CodeException
287
     * @throws \qfq\DbException
288
     * @throws \qfq\UserFormException
289
     */
290
    private function prepareExecute($sql, array $parameterArray = array(), &$queryType, array &$stat) {
291

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

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

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

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

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

        if (false === $this->mysqli_stmt->execute()) {
317
318
            $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
            throw new DbException('[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error, ERROR_DB_EXECUTE);
319
320
321
322
323
324
325
326
327
328
329
330
331
        }

        $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);
                }
332
                $queryType = QUERY_TYPE_SELECT;
333
                $this->mysqli_result = $result;
334
335
336
                $stat[DB_NUM_ROWS] = $this->mysqli_result->num_rows;
                $count = $stat[DB_NUM_ROWS];
                $msg = 'Read rows: ' . $stat[DB_NUM_ROWS];
337
                break;
338
            case 'REPLACE':
339
            case 'INSERT':
340
                $queryType = QUERY_TYPE_INSERT;
341
342
343
                $stat[DB_INSERT_ID] = $this->mysqli->insert_id;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
344
                $msg = 'ID: ' . $this->mysqli->insert_id;
345
346
347
                break;
            case 'UPDATE':
            case 'DELETE':
348
            case 'TRUNCATE':
349
350
351
                $queryType = QUERY_TYPE_UPDATE;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
352
353
                $msg = 'Affected rows: ' . $count;
                break;
354

355
            case 'SET':
356
            case 'ALTER':
357
            case 'DROP':
358
            case 'CREATE':
359
360
361
362
363
364
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
                break;

365
            default:
366
                throw new DbException('Unknown comand: "' . $command . '"', ERROR_DB_UNKNOWN_COMMAND);
367
                break;
368
369
        }

370
371
372
        if ($this->store !== null) {
            $this->store->setVar(SYSTEM_SQL_COUNT, $count, STORE_SYSTEM);
        }
373

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

377
378
379
        return $count;
    }

380
381
382
383
    /**
     * Check if the given SQL Statement might modify data.
     *
     * @param $sql
Carsten  Rose's avatar
Carsten Rose committed
384
     *
385
386
387
     * @return bool  true is the statement might modify data, else: false
     */
    private function isSqlModify($sql) {
388

389
        $command = explode(' ', $sql, 2);
390

391
392
393
394
395
396
397
398
        switch (strtoupper($command[0])) {
            case 'INSERT':
            case 'UPDATE':
            case 'DELETE':
            case 'REPLACE':
            case 'TRUNCATE':
                return true;
        }
399

400
401
402
        return false;
    }

403
    /**
404
     * Decide if the SQL statement has to be logged. If yes, create a timestamp and do the log.
405
     *
Carsten  Rose's avatar
Carsten Rose committed
406
     * @param       $sql
407
     * @param array $parameterArray
Carsten  Rose's avatar
Carsten Rose committed
408
     *
409
     * @return string
410
     * @throws \qfq\UserFormException
411
     */
412
    private function dbLog($currentQueryMode = SQL_LOG_MODE_ALL, $sql = '', $parameterArray = array()) {
413

414
415
416
417
        if ($sql == '') {
            return;
        }

418
419
        $status = '';

420
421
422
423
424
        // 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;
        }
425

426
427
428
        // Check if string is known.
        foreach ([$sqlLogMode, $currentQueryMode] as $mode) {
            if (!isset($this->sqlLogModePrio[$mode])) {
429
                throw new UserFormException("Unknown SQL_LOG_MODE: $mode", ERROR_UNKNOWN_SQL_LOG_MODE);
430
431
432
433
434
435
            }
        }

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

438
        // Client IP Address
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
        $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);
458

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

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

465
466
        if ($currentQueryMode == SQL_LOG_MODE_ERROR) {
            $status = 'FAILED: ';
467
        }
468
        $msg .= '[' . $status . $sql . ']';
469

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

473
474
    /**
     * @param $sql
475
     * @param $parameterArray
Carsten  Rose's avatar
Carsten Rose committed
476
     *
477
     * @return string
478
     */
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
    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 = '?';
            }
494
        }
495
496
497
498
499
        if (isset($sqlArray[$ii])) {
            $msg .= $sqlArray[$ii];
        }

        return $msg;
500
501
    }

502
    /**
503
     * @param $arr
504
     */
505
    private function prepareBindParam($arr) {
506

507
508
509
510
        $bindParam = new BindParam();

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

515
516
517
    /**
     * Fetch all rows of the result as associative array.
     *
518
519
     * mode:
     *  ROW_IMPLODE_ALL: Return string. All cells of all rows imploded to one string.
520
     *  ROW_KEYS: Return num array with column names in $keys
521
522
523
     *  default: Return 2-dimensional assoc array
     *
     * @param string $mode
Carsten  Rose's avatar
Carsten Rose committed
524
     * @param array $keys
Carsten  Rose's avatar
Carsten Rose committed
525
     *
526
     * @return array|bool|mixed|string false in case of an error.
527
528
529
530
531
     *              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
     *
     */
532
533
    private function fetchAll($mode = '', &$keys = array()) {
        if ($this->mysqli_result == null || $this->mysqli_result == false) {
534
535
536
            return false;
        }

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

541
542
543
        switch ($mode) {
            case ROW_IMPLODE_ALL:
                $str = "";
544
                foreach ($this->mysqli_result->fetch_all(MYSQLI_NUM) as $row) {
545
546
                    $str .= implode($row);
                }
Carsten  Rose's avatar
Carsten Rose committed
547

548
549
550
551
552
553
554
555
556
                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;
                }
Carsten  Rose's avatar
Carsten Rose committed
557

558
559
560
561
562
                return $this->mysqli_result->fetch_all(MYSQLI_NUM);
                break;

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

566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
    /**
     * 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
Carsten  Rose's avatar
Carsten Rose committed
623
     *
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
     * @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`");
    }

657
    /**
658
659
     * Wrapper for sql(), to simplyfy access.
     *
Carsten  Rose's avatar
Carsten Rose committed
660
     * @param              $sql
Carsten  Rose's avatar
Carsten Rose committed
661
662
     * @param array $keys
     * @param array $stat
Carsten  Rose's avatar
Carsten Rose committed
663
     *
664
     * @return array|bool
665
     * @throws \qfq\CodeException
666
     * @throws \qfq\DbException
667
     */
668
    public function sqlKeys($sql, array &$keys, array &$stat = array()) {
669

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

673
674
675
676
677
678
    /**
     * Returns lastInsertId
     *
     * @return string
     */
    public function getLastInsertId() {
679
        // Do not try to use $this->mysqli->lastInsertId - this is not valid at any given time.
680
        return $this->mysqli->insert_id;
681
    }
Carsten  Rose's avatar
Carsten Rose committed
682

683
684
685
686
    /**
     * Searches for the table '$name'.
     *
     * @param $name
Carsten  Rose's avatar
Carsten Rose committed
687
     *
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
     * @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;
    }

707
708
709
    /**
     * Depending on $sql reads FormElements to a specific container or all. Preprocess all FormElements.
     *
Carsten  Rose's avatar
Carsten Rose committed
710
711
712
     * @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
Carsten  Rose's avatar
Carsten Rose committed
713
     *
714
715
     * @return array|int
     * @throws \qfq\CodeException
Carsten  Rose's avatar
Carsten Rose committed
716
     * @throws \qfq\DbException
717
718
719
720
721
     */
    public function getNativeFormElements($sql, array $param, $formSpec) {

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

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

724
        // Explode and Do $FormElement.parameter
725
        HelperFormElement::explodeParameterInArrayElements($feSpecNative, FE_PARAMETER);
726
727
728
729
730

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

        // Copy Attributes to FormElements
731
        $feSpecNative = HelperFormElement::copyAttributesToFormElements($formSpec, $feSpecNative);
732
733
734
735

        return $feSpecNative;
    }

736
737
738
739
740
    /**
     * 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
Carsten  Rose's avatar
Carsten Rose committed
741
     *
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
     * @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.
771
     * $arr might contain one or more columns.
Carsten  Rose's avatar
Carsten Rose committed
772
773
     * 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.
774
     *
Carsten  Rose's avatar
Carsten Rose committed
775
     * @param array $arr
776
777
778
779
     * @param string $srcColumn1
     * @param string $srcColumn2
     * @param string $destColumn1
     * @param string $destColumn2
Carsten  Rose's avatar
Carsten Rose committed
780
     *
781
782
     * @return array
     */
783
784
785
786
787
788
789
790
791
792
    public function makeArrayDict(array $arr, $srcColumn1, $srcColumn2, $destColumn1 = '', $destColumn2 = '') {

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

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

793
794
795
796
797
798
799
800
801
        $new = array();

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

        $row = $arr[0];
        $keys = array_keys($row);
        if (count($row) < 2) {
802
803
804
805
806
            $column1 = $keys[0];
            $column2 = $keys[0];
        } elseif (array_key_exists($srcColumn1, $row) && array_key_exists($srcColumn2, $row)) {
            $column1 = $srcColumn1;
            $column2 = $srcColumn2;
807
        } else {
808
809
            $column1 = $keys[0];
            $column2 = $keys[1];
810
811
812
813
        }

        $row = array_shift($arr);
        while (null !== $row) {
814
            $new[] = [$destColumn1 => $row[$column1], $destColumn2 => $row[$column2]];
815
816
817
818
819
            $row = array_shift($arr);
        }

        return $new;
    }
820
821
822
823
824

    /**
     * Proxy for mysqli::real_escape_string()
     *
     * @param string $value
Carsten  Rose's avatar
Carsten Rose committed
825
     *
826
827
828
829
830
     * @return string
     */
    public function realEscapeString($value) {
        return $this->mysqli->real_escape_string($value);
    }
Carsten  Rose's avatar
Carsten Rose committed
831
832
833

    /**
     * @param $filename
Carsten  Rose's avatar
Carsten Rose committed
834
     *
Carsten  Rose's avatar
Carsten Rose committed
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
     * @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());

    }
853
}