Database.php 26.5 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
61
62
63
    /**
     * Returns current data base handle from Store[System][SYSTEM_DBH].
     * If not exists: open database and store the new dbh in Store[System][SYSTEM_DBH]
     *
     * @throws CodeException
64
     * @throws UserFormException
65
     */
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
    public function __construct($mode = MODE_DB_REGULAR) {
        $dbInit = '';

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

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

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

94

95
    /**
96
97
98
     * Open mysqli database connection if not already done.
     *
     * @return \mysqli
99
     * @throws UserFormException
100
     */
101
    private function dbConnect($config) {
102
103
        $mysqli = null;

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

        if ($mysqli->connect_error) {
107
            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);
108
109
        }

110
111
112
113
114
        // 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);
        }

115
        return $mysqli;
116
117
118
    }

    /**
Carsten  Rose's avatar
Carsten Rose committed
119
120
     * Fires query $sql and fetches result as assoc array (all modes but ROW_KEYS) or as num array (mode: ROW_KEYS).
     * Throws exception.
121
     *
122
     * $mode
123
124
     *  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.
125
     *  ROW_EXPECT_0: Return empty string if there is no record row, Else an exception.
126
     *  ROW_EXPECT_1: Return 1-dimensional assoc array if there are exact one row. Else an exception.
Carsten  Rose's avatar
Carsten Rose committed
127
128
     *  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.
129
     *  ROW_KEYS: Return 2-dimensional num(!) array. Every query row is one array row. $keys are the column names.
130
     *
Carsten  Rose's avatar
Carsten Rose committed
131
     * @param        $sql
132
     * @param string $mode
Carsten  Rose's avatar
Carsten Rose committed
133
     * @param array  $parameterArray
134
     * @param string $specificMessage
Carsten  Rose's avatar
Carsten Rose committed
135
136
137
     * @param array  $keys
     * @param array  $stat DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
     *
138
     * @return array|int
139
     *      SELECT | SHOW | DESCRIBE | EXPLAIN: see $mode
140
141
     *      INSERT: last_insert_id
     *      UPDATE | DELETE | REPLACE: affected rows
142
143
     * @throws \qfq\CodeException
     * @throws \qfq\DbException
144
     */
145
    public function sql($sql, $mode = ROW_REGULAR, array $parameterArray = array(), $specificMessage = '', array &$keys = array(), array &$stat = array()) {
146
        $queryType = '';
147
148
        $result = array();
        $this->closeMysqliStmt();
149
150

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

155
        // for error reporting in exception
156
        if ($specificMessage) {
157
            $specificMessage .= " ";
158
        }
159

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

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

166
167
168
        if ($queryType === QUERY_TYPE_SELECT) {
            switch ($mode) {
                case ROW_IMPLODE_ALL:
169
                    $result = $this->fetchAll($mode);
170
171
172
173
174
175
176
177
178
                    break;
                case ROW_KEYS:
                case ROW_REGULAR:
                    $result = $this->fetchAll($mode, $keys);
                    break;
                case ROW_EXPECT_0:
                    if ($count === 0)
                        $result = array();
                    else
179
                        throw new DbException($specificMessage . "Expected none record, got $count rows: $sql", ERROR_DB_TOO_MANY_ROWS);
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
                    break;
                case ROW_EXPECT_1:
                    if ($count === 1)
                        $result = $this->fetchAll($mode)[0];
                    else
                        throw new DbException($specificMessage . "Expected one record, got $count: $sql", ERROR_DB_COUNT_DO_NOT_MATCH);
                    break;
                case ROW_EXPECT_0_1:
                    if ($count === 1)
                        $result = $this->fetchAll($mode)[0];
                    elseif ($count === 0)
                        $result = array();
                    else
                        throw new DbException($specificMessage . "Expected no record, got $count rows: $sql", ERROR_DB_TOO_MANY_ROWS);
                    break;
                case ROW_EXPECT_GE_1:
                    if ($count > 0)
                        $result = $this->fetchAll($mode);
                    else
                        throw new DbException($specificMessage . "Expected at least one record, got nothing: $sql", ERROR_DB_TOO_FEW_ROWS);
                    break;
201

202
203
204
                default:
                    throw new DbException($specificMessage . "Unknown mode: $mode", ERROR_UNKNOWN_MODE);
            }
205
206
        } elseif ($queryType === QUERY_TYPE_INSERT) {
            $result = $stat[DB_INSERT_ID];
207
208
        } else {
            $result = $count;
209
210
        }

211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
        $this->closeMysqliStmt();

        return $result;
    }

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

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

        if ($this->mysqli_stmt !== null && $this->mysqli_stmt !== false) {
            $this->mysqli_stmt->free_result();
            if (!$this->mysqli_stmt->close())
230
                throw new DbException('Error closing mysqli_stmt' . ERROR_DB_CLOSE_MYSQLI_STMT);
231
232
233
        }
        $this->mysqli_stmt = null;
        $this->mysqli_result = null;
234
235
236
    }

    /**
237
     * Execute a prepared SQL statement like SELECT, INSERT, UPDATE, DELETE, SHOW, ...
238
     *
Carsten  Rose's avatar
Carsten Rose committed
239
240
241
242
243
244
245
246
     * 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
247
     *
248
249
     * @return int|mixed
     * @throws \qfq\CodeException
250
     * @throws \qfq\DbException
251
     * @throws \qfq\UserFormException
252
     */
253
    private function prepareExecute($sql, array $parameterArray = array(), &$queryType, array &$stat) {
254

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

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

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

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

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

        if (false === $this->mysqli_stmt->execute()) {
280
281
            $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
            throw new DbException('[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error, ERROR_DB_EXECUTE);
282
283
284
285
286
287
288
289
290
291
292
293
294
        }

        $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);
                }
295
                $queryType = QUERY_TYPE_SELECT;
296
                $this->mysqli_result = $result;
297
298
299
                $stat[DB_NUM_ROWS] = $this->mysqli_result->num_rows;
                $count = $stat[DB_NUM_ROWS];
                $msg = 'Read rows: ' . $stat[DB_NUM_ROWS];
300
                break;
301
            case 'REPLACE':
302
            case 'INSERT':
303
                $queryType = QUERY_TYPE_INSERT;
304
305
306
                $stat[DB_INSERT_ID] = $this->mysqli->insert_id;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
307
                $msg = 'ID: ' . $this->mysqli->insert_id;
308
309
310
                break;
            case 'UPDATE':
            case 'DELETE':
311
            case 'TRUNCATE':
312
313
314
                $queryType = QUERY_TYPE_UPDATE;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
315
316
                $msg = 'Affected rows: ' . $count;
                break;
317

318
            case 'SET':
319
            case 'ALTER':
320
            case 'DROP':
321
            case 'CREATE':
322
323
324
325
326
327
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
                break;

328
            default:
329
                throw new DbException('Unknown comand: "' . $command . '"', ERROR_DB_UNKNOWN_COMMAND);
330
                break;
331
332
        }

333
334
335
        if ($this->store !== null) {
            $this->store->setVar(SYSTEM_SQL_COUNT, $count, STORE_SYSTEM);
        }
336

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

340
341
342
        return $count;
    }

343
344
345
346
    /**
     * Check if the given SQL Statement might modify data.
     *
     * @param $sql
Carsten  Rose's avatar
Carsten Rose committed
347
     *
348
349
350
     * @return bool  true is the statement might modify data, else: false
     */
    private function isSqlModify($sql) {
351

352
        $command = explode(' ', $sql, 2);
353

354
355
356
357
358
359
360
361
        switch (strtoupper($command[0])) {
            case 'INSERT':
            case 'UPDATE':
            case 'DELETE':
            case 'REPLACE':
            case 'TRUNCATE':
                return true;
        }
362

363
364
365
        return false;
    }

366
    /**
367
     * Decide if the SQL statement has to be logged. If yes, create a timestamp and do the log.
368
     *
Carsten  Rose's avatar
Carsten Rose committed
369
     * @param       $sql
370
     * @param array $parameterArray
Carsten  Rose's avatar
Carsten Rose committed
371
     *
372
     * @return string
373
     * @throws \qfq\UserFormException
374
     */
375
    private function dbLog($currentQueryMode = SQL_LOG_MODE_ALL, $sql = '', $parameterArray = array()) {
376

377
378
379
380
        if ($sql == '') {
            return;
        }

381
382
        $status = '';

383
384
385
386
387
        // 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;
        }
388

389
390
391
        // Check if string is known.
        foreach ([$sqlLogMode, $currentQueryMode] as $mode) {
            if (!isset($this->sqlLogModePrio[$mode])) {
392
                throw new UserFormException("Unknown SQL_LOG_MODE: $mode", ERROR_UNKNOWN_SQL_LOG_MODE);
393
394
395
396
397
398
            }
        }

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

401
        // Client IP Address
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
        $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);
421

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

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

428
429
        if ($currentQueryMode == SQL_LOG_MODE_ERROR) {
            $status = 'FAILED: ';
430
        }
431
        $msg .= '[' . $status . $sql . ']';
432

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

436
437
    /**
     * @param $sql
438
     * @param $parameterArray
Carsten  Rose's avatar
Carsten Rose committed
439
     *
440
     * @return string
441
     */
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
    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 = '?';
            }
457
        }
458
459
460
461
462
        if (isset($sqlArray[$ii])) {
            $msg .= $sqlArray[$ii];
        }

        return $msg;
463
464
    }

465
    /**
466
     * @param $arr
467
     */
468
    private function prepareBindParam($arr) {
469

470
471
472
473
        $bindParam = new BindParam();

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

478
479
480
    /**
     * Fetch all rows of the result as associative array.
     *
481
482
     * mode:
     *  ROW_IMPLODE_ALL: Return string. All cells of all rows imploded to one string.
483
     *  ROW_KEYS: Return num array with column names in $keys
484
485
486
     *  default: Return 2-dimensional assoc array
     *
     * @param string $mode
Carsten  Rose's avatar
Carsten Rose committed
487
488
     * @param array  $keys
     *
489
     * @return array|bool|mixed|string false in case of an error.
490
491
492
493
494
     *              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
     *
     */
495
496
    private function fetchAll($mode = '', &$keys = array()) {
        if ($this->mysqli_result == null || $this->mysqli_result == false) {
497
498
499
            return false;
        }

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

504
505
506
        switch ($mode) {
            case ROW_IMPLODE_ALL:
                $str = "";
507
                foreach ($this->mysqli_result->fetch_all(MYSQLI_NUM) as $row) {
508
509
                    $str .= implode($row);
                }
Carsten  Rose's avatar
Carsten Rose committed
510

511
512
513
514
515
516
517
518
519
                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
520

521
522
523
524
525
                return $this->mysqli_result->fetch_all(MYSQLI_NUM);
                break;

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

529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
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
    /**
     * 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
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
     * @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`");
    }

620
    /**
621
622
     * Wrapper for sql(), to simplyfy access.
     *
Carsten  Rose's avatar
Carsten Rose committed
623
     * @param              $sql
624
     * @param array $keys
625
     * @param array $stat
Carsten  Rose's avatar
Carsten Rose committed
626
     *
627
     * @return array|bool
628
     * @throws \qfq\CodeException
629
     * @throws \qfq\DbException
630
     */
631
    public function sqlKeys($sql, array &$keys, array &$stat = array()) {
632

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

636
637
638
639
640
641
    /**
     * Returns lastInsertId
     *
     * @return string
     */
    public function getLastInsertId() {
642
        // Do not try to use $this->mysqli->lastInsertId - this is not valid at any given time.
643
        return $this->mysqli->insert_id;
644
    }
Carsten  Rose's avatar
Carsten Rose committed
645

646
647
648
649
    /**
     * Searches for the table '$name'.
     *
     * @param $name
Carsten  Rose's avatar
Carsten Rose committed
650
     *
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
     * @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;
    }

670
671
672
    /**
     * Depending on $sql reads FormElements to a specific container or all. Preprocess all FormElements.
     *
Carsten  Rose's avatar
Carsten Rose committed
673
674
675
676
     * @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
     *
677
678
     * @return array|int
     * @throws \qfq\CodeException
Carsten  Rose's avatar
Carsten Rose committed
679
     * @throws \qfq\DbException
680
681
682
683
684
     */
    public function getNativeFormElements($sql, array $param, $formSpec) {

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

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

687
        // Explode and Do $FormElement.parameter
688
        HelperFormElement::explodeParameterInArrayElements($feSpecNative, FE_PARAMETER);
689
690
691
692
693

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

        // Copy Attributes to FormElements
694
        $feSpecNative = HelperFormElement::copyAttributesToFormElements($formSpec, $feSpecNative);
695
696
697
698

        return $feSpecNative;
    }

699
700
701
702
703
    /**
     * 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
704
     *
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
     * @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.
734
     * $arr might contain one or more columns.
Carsten  Rose's avatar
Carsten Rose committed
735
736
     * 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.
737
     *
Carsten  Rose's avatar
Carsten Rose committed
738
     * @param array                       $arr
739
740
741
742
     * @param string $srcColumn1
     * @param string $srcColumn2
     * @param string $destColumn1
     * @param string $destColumn2
Carsten  Rose's avatar
Carsten Rose committed
743
     *
744
745
     * @return array
     */
746
747
748
749
750
751
752
753
754
755
    public function makeArrayDict(array $arr, $srcColumn1, $srcColumn2, $destColumn1 = '', $destColumn2 = '') {

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

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

756
757
758
759
760
761
762
763
764
        $new = array();

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

        $row = $arr[0];
        $keys = array_keys($row);
        if (count($row) < 2) {
765
766
767
768
769
            $column1 = $keys[0];
            $column2 = $keys[0];
        } elseif (array_key_exists($srcColumn1, $row) && array_key_exists($srcColumn2, $row)) {
            $column1 = $srcColumn1;
            $column2 = $srcColumn2;
770
        } else {
771
772
            $column1 = $keys[0];
            $column2 = $keys[1];
773
774
775
776
        }

        $row = array_shift($arr);
        while (null !== $row) {
777
            $new[] = [$destColumn1 => $row[$column1], $destColumn2 => $row[$column2]];
778
779
780
781
782
            $row = array_shift($arr);
        }

        return $new;
    }
783
784
785
786
787

    /**
     * Proxy for mysqli::real_escape_string()
     *
     * @param string $value
Carsten  Rose's avatar
Carsten Rose committed
788
     *
789
790
791
792
793
     * @return string
     */
    public function realEscapeString($value) {
        return $this->mysqli->real_escape_string($value);
    }
Carsten  Rose's avatar
Carsten Rose committed
794
795
796

    /**
     * @param $filename
Carsten  Rose's avatar
Carsten Rose committed
797
     *
Carsten  Rose's avatar
Carsten Rose committed
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
     * @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());

    }
816
}