Database.php 27.6 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
    public function __construct($dbIndex = 1) {
72
73
        $dbInit = '';

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

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

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

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

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

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

97
98
99
100
101
102
103
104
105
106
    /**
     * 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
     */
107
    private function getConnectionDetails($dbIndex, array $config) {
108
109
110
111
112
113
114

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

        return $config;
    }
121

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

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

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

137
138
139
140
141
        // 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);
        }

142
        return $mysqli;
143
144
145
    }

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

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

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

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

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

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

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

241
242
        $this->closeMysqliStmt();

243
244
245
246
        $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);

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

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

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

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

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

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

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

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

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

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

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

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

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

375
376
377
        return $count;
    }

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

387
        $command = explode(' ', $sql, 2);
388

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

398
399
400
        return false;
    }

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

412
413
414
415
        if ($sql == '') {
            return;
        }

416
417
        $status = '';

418
419
420
421
422
        // 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;
        }
423

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

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

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

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

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

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

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

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

        return $msg;
498
499
    }

500
    /**
501
     * @param $arr
502
     */
503
    private function prepareBindParam($arr) {
504

505
506
507
508
        $bindParam = new BindParam();

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

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

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

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

546
547
548
549
550
551
552
553
554
                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
555

556
557
558
559
560
                return $this->mysqli_result->fetch_all(MYSQLI_NUM);
                break;

            default:
                return $this->mysqli_result->fetch_all(MYSQLI_ASSOC);
561
562
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
    /**
     * 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
621
     *
622
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
     * @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`");
    }

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

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

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

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

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

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

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

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

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

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

        return $feSpecNative;
    }

734
735
736
737
738
    /**
     * 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
739
     *
740
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
     * @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.
769
     * $arr might contain one or more columns.
Carsten  Rose's avatar
Carsten Rose committed
770
771
     * 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.
772
     *
Carsten  Rose's avatar
Carsten Rose committed
773
     * @param array $arr
774
775
776
777
     * @param string $srcColumn1
     * @param string $srcColumn2
     * @param string $destColumn1
     * @param string $destColumn2
Carsten  Rose's avatar
Carsten Rose committed
778
     *
779
780
     * @return array
     */
781
782
783
784
785
786
787
788
789
790
    public function makeArrayDict(array $arr, $srcColumn1, $srcColumn2, $destColumn1 = '', $destColumn2 = '') {

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

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

791
792
793
794
795
796
797
798
799
        $new = array();

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

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

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

        return $new;
    }
818
819
820
821
822

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

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

    }
851
}