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

namespace qfq;

use qfq;
12
use qfq\CodeException;
13
use qfq\DbException;
14
use qfq\UserFormException;
15
use qfq\Support;
16
use qfq\Store;
17

18
19
20
require_once(__DIR__ . '/../exceptions/UserFormException.php');
require_once(__DIR__ . '/../exceptions/CodeException.php');
require_once(__DIR__ . '/../exceptions/DbException.php');
21

22
23
24
25
26
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');
27

Carsten  Rose's avatar
Carsten Rose committed
28
29
30
31
/**
 * Class Database
 * @package qfq
 */
32
33
34
class Database {

    /**
35
     * @var Store
36
37
     */
    private $store = null;
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

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

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

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

54
55
56
    /**
     * @var string
     */
57
    private $sqlLog = '';
58

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

64
65
66
67
68
    /**
     * 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
69
     * @throws UserFormException
70
     */
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
    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);
        }
88

89
        if ($this->mysqli === null) {
90
            $this->mysqli = $this->dbConnect($config);
91
        }
92
93

        // DB Init
94

95
96
97
        if ($dbInit !== false && $dbInit != '') {
            $this->sql($dbInit);
        }
98
99
    }

100

101
    /**
102
103
104
     * Open mysqli database connection if not already done.
     *
     * @return \mysqli
105
     * @throws UserFormException
106
     */
107
    private function dbConnect($config) {
108
109
        $mysqli = null;

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

        if ($mysqli->connect_error) {
113
            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);
114
115
        }

116
117
118
119
120
        // 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);
        }

121
        return $mysqli;
122
123
124
    }

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

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

161
        // for error reporting in exception
162
        if ($specificMessage) {
163
            $specificMessage .= " ";
164
        }
165

166
167
        $count = $this->prepareExecute($sql, $parameterArray, $queryType, $stat);

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

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

208
209
210
                default:
                    throw new DbException($specificMessage . "Unknown mode: $mode", ERROR_UNKNOWN_MODE);
            }
211
212
        } elseif ($queryType === QUERY_TYPE_INSERT) {
            $result = $stat[DB_INSERT_ID];
213
214
        } else {
            $result = $count;
215
216
        }

217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
        $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())
236
                throw new DbException('Error closing mysqli_stmt' . ERROR_DB_CLOSE_MYSQLI_STMT);
237
238
239
        }
        $this->mysqli_stmt = null;
        $this->mysqli_result = null;
240
241
242
    }

    /**
243
     * Execute a prepared SQL statement like SELECT, INSERT, UPDATE, DELETE, SHOW, ...
244
     *
245
     * Returns the number of selected rows (SELECT, SHOW, ..) or the affected rows (UPDATE, INSERT). $stat contains appropriate num_rows, insert_id or rows_affected.
246
247
248
     *
     * @param string $sql SQL statement with prepared statement variable.
     * @param array $parameterArray parameter array for prepared statement execution.
249
     * @param string $queryType returns QUERY_TYPE_SELECT | QUERY_TYPE_UPDATE | QUERY_TYPE_INSERT, depending on the query.
250
     * @param array $stat DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
251
252
     * @return int|mixed
     * @throws \qfq\CodeException
253
     * @throws \qfq\DbException
254
     * @throws \qfq\UserFormException
255
     */
256
    private function prepareExecute($sql, array $parameterArray = array(), &$queryType, array &$stat) {
257

258
        $sqlLogMode = $this->isSqlModify($sql) ? SQL_LOG_MODE_MODIFY : SQL_LOG_MODE_ALL;
259
        $result = 0;
260
        $stat = array();
261

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

267
        // Logfile
268
        $this->dbLog($sqlLogMode, $sql, $parameterArray);
269

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

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

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

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

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

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

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

339
        // Logfile
340
        $this->dbLog($sqlLogMode, $msg);
341

342
343
344
        return $count;
    }

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

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

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

364
365
366
        return false;
    }

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

377
378
        $status = '';

379
380
381
382
383
        // 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;
        }
384

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

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

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

400
        $msg = '[' . date('Y.m.d H:i:s O') . '][' . $remoteAddress . ']';
401
402
403
404
405

//        // FE User
//        $feUser = $this->sqlLog = $this->store->getVar(TYPO3_FE_USER, STORE_TYPO3);
//        $pageId = $this->sqlLog = $this->store->getVar(TYPO3_PAGE_ID, STORE_TYPO3);
//        $ttcontentId = $this->sqlLog = $this->store->getVar(TYPO3_TT_CONTENT_UID, STORE_TYPO3);
406

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

411
        if ($sql !== '') {
412
            if ($currentQueryMode == SQL_LOG_MODE_ERROR) {
413
414
415
                $status = 'FAILED: ';
            }
            $msg .= '[' . $status . $sql . ']';
416
        }
417

418
        Logger::logMessage($msg, $this->sqlLog);
419
420
    }

421
422
    /**
     * @param $sql
423
424
     * @param $parameterArray
     * @return string
425
     */
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
    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 = '?';
            }
441
        }
442
443
444
445
446
        if (isset($sqlArray[$ii])) {
            $msg .= $sqlArray[$ii];
        }

        return $msg;
447
448
    }

449
    /**
450
     * @param $arr
451
     */
452
    private function prepareBindParam($arr) {
453

454
455
456
457
        $bindParam = new BindParam();

        for ($ii = 0; $ii < count($arr); $ii++) {
            $bindParam->add($arr[$ii]);
458
        }
459
        call_user_func_array([$this->mysqli_stmt, 'bind_param'], $bindParam->get());
460
461
    }

462
463
464
    /**
     * Fetch all rows of the result as associative array.
     *
465
466
     * mode:
     *  ROW_IMPLODE_ALL: Return string. All cells of all rows imploded to one string.
467
     *  ROW_KEYS: Return num array with column names in $keys
468
469
470
471
     *  default: Return 2-dimensional assoc array
     *
     * @param string $mode
     * @param array $keys
472
     * @return array|bool|mixed|string false in case of an error.
473
474
475
476
477
     *              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
     *
     */
478
479
    private function fetchAll($mode = '', &$keys = array()) {
        if ($this->mysqli_result == null || $this->mysqli_result == false) {
480
481
482
            return false;
        }

483
        if ($this->mysqli_result->num_rows === 0) {
484
485
486
            return ($mode === ROW_IMPLODE_ALL) ? "" : array();
        }

487
488
489
        switch ($mode) {
            case ROW_IMPLODE_ALL:
                $str = "";
490
                foreach ($this->mysqli_result->fetch_all(MYSQLI_NUM) as $row) {
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
                    $str .= implode($row);
                }
                return $str;
                break;

            case ROW_KEYS:
                $keys = array();

                for ($ii = 0; $ii < $this->mysqli_result->field_count; $ii++) {
                    $keys[$ii] = $this->mysqli_result->fetch_field_direct($ii)->name;
                }
                return $this->mysqli_result->fetch_all(MYSQLI_NUM);
                break;

            default:
                return $this->mysqli_result->fetch_all(MYSQLI_ASSOC);
507
508
509
        }
    }

510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
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
586
587
588
589
590
591
592
593
594
595
596
597
598
599
    /**
     * Return the number of rows returned by the last call to execute().
     *
     * If execute() has never been called, returns FALSE.
     *
     * @return mixed Number of rows returned by last call to execute(). If Database::execute()
     *     has never been called prior a call to this method, false is returned.
     */
    public function getRowCount() {
        if ($this->mysqli_result == null) {
            return false;
        }

        return $this->mysqli_result->num_rows;
    }

    /**
     * Get the values for a given ENUM or SET column
     *
     * @param string $table name of the table
     * @param string $columnName name of the column
     *
     * @throws UserFormException if the table or column does not exist, or is not of type ENUM or SET
     * @return array
     */
    public function getEnumSetValueList($table, $columnName) {

        $columnDefinition = $this->getFieldDefinitionFromTable($table, $columnName);
        $setEnumDefinition = $columnDefinition["Type"];

        // $setEnumDefinition holds now a string like
        // String:  enum('','red','blue','green')
        $len = mb_strlen($setEnumDefinition);

        # "enum('" = 6, "set('" = 5
        $tokenLength = strpos($setEnumDefinition, "'") + 1;

        // count("enum('") == 6, count("')") == 2
        $enumSetString = mb_substr($setEnumDefinition, $tokenLength, $len - (2 + $tokenLength));

        // String: ','red','blue','green

        if (($setEnumValueList = explode("','", $enumSetString)) === false) {
            return array();
        }

        return $setEnumValueList;
    }

    /**
     * Get database column definition.
     *
     * If the column is not found in the table, an exception is thrown.
     *
     * @param string $table name of the table
     *
     * @param string $columnName name of the column
     * @return array the definition of the column as retrieved by Database::getTableDefinition().
     *
     * @throws \qfq\DbException
     */
    private function getFieldDefinitionFromTable($table, $columnName) {
        $tableDefinition = $this->getTableDefinition($table);
        foreach ($tableDefinition AS $row) {
            if ($row["Field"] == $columnName) {
                return $row;
            }
        }
        throw new DbException("Column name '$columnName' not found in table '$table'.", ERROR_DB_COLUMN_NOT_FOUND_IN_TABLE);
    }

    /**
     * Get all column definitions for a table. Return Assoc Array:
     *
     * Field      Type                      Null    Key    Default    Extra
     * --------------------------------------------------------------------------
     * id         bigint(20)                 NO     PRI    NULL    auto_increment
     * name       varchar(128)               YES           NULL
     * firstname  varchar(128)               YES           NULL
     * gender     enum('','male','female')   NO            male
     * groups     set('','a','b','c')        NO            a
     *
     * @param string $table table to retrieve column definition from
     *
     * @return array column definition of table as returned by SHOW FIELDS FROM as associative array.
     */
    public function getTableDefinition($table) {
        return $this->sql("SHOW FIELDS FROM `$table`");
    }

600
    /**
601
602
     * Wrapper for sql(), to simplyfy access.
     *
603
604
     * @param $sql
     * @param array $keys
605
     * @param array $stat
606
     * @return array|bool
607
     * @throws \qfq\CodeException
608
     * @throws \qfq\DbException
609
     */
610
    public function sqlKeys($sql, array &$keys, array &$stat = array()) {
611

612
        return $this->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
613
    }
614

615
616
617
618
619
620
    /**
     * Returns lastInsertId
     *
     * @return string
     */
    public function getLastInsertId() {
621
        // Do not try to use $this->mysqli->lastInsertId - this is not valid at any given time.
622
        return $this->mysqli->insert_id;
623
    }
Carsten  Rose's avatar
Carsten Rose committed
624

625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
    /**
     * Searches for the table '$name'.
     *
     * @param $name
     * @return bool  true if found, else false
     */
    public function existTable($name) {
        $found = false;

        $tables = $this->sql("SHOW tables");

        foreach ($tables as $t) {
            foreach ($t as $key => $value) {
                if ($value === $name) {
                    $found = true;
                    break 2;
                }
            }
        }

        return $found;
    }

648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
    /**
     * Depending on $sql reads FormElements to a specific container or all. Preprocess all FormElements.
     *
     * @param string $sql SQL_FORM_ELEMENT_SPECIFIC_CONTAINER | SQL_FORM_ELEMENT_ALL_CONTAINER
     * @param array $param Parameter which matches the prepared statement in $sql
     * @param array $formSpec Main FormSpec to copy generic parameter to FormElements
     * @return array|int
     * @throws \qfq\CodeException
     * @throws \qfq\DbException
     */
    public function getNativeFormElements($sql, array $param, $formSpec) {

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

        // Explode and Do $FormElement.parameter
663
        HelperFormElement::explodeParameterInArrayElements($feSpecNative, FE_PARAMETER);
664
665
666
667
668

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

        // Copy Attributes to FormElements
669
        $feSpecNative = HelperFormElement::copyAttributesToFormElements($formSpec, $feSpecNative);
670
671
672
673

        return $feSpecNative;
    }

674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
    /**
     * Checks if there is the SQL keyword 'limit' at the end of the SQL statement.
     * returns true for '... LIMIT', '.... LIMIT 1, ... LIMIT 1,2, ... LIMIT 1 , 2
     *
     * @param $sql
     * @return bool
     */
    public function hasLimit($sql) {

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

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

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

        return false;
    }

    /**
     * $arr will be converted to a two column array with keys $keyName1 and $keyName2.
708
     * $arr might contain one or more columns.
709
710
711
712
     * Only when $keyName1 and $keyName2 exist, those will be used. Else the first column becomes $keyName1 and the second becomes $keyName2.
     * If there is only one column, that column will be doubled.
     *
     * @param array $arr
713
714
715
716
     * @param string $srcColumn1
     * @param string $srcColumn2
     * @param string $destColumn1
     * @param string $destColumn2
717
718
     * @return array
     */
719
720
721
722
723
724
725
726
727
728
    public function makeArrayDict(array $arr, $srcColumn1, $srcColumn2, $destColumn1 = '', $destColumn2 = '') {

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

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

729
730
731
732
733
734
735
736
737
        $new = array();

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

        $row = $arr[0];
        $keys = array_keys($row);
        if (count($row) < 2) {
738
739
740
741
742
            $column1 = $keys[0];
            $column2 = $keys[0];
        } elseif (array_key_exists($srcColumn1, $row) && array_key_exists($srcColumn2, $row)) {
            $column1 = $srcColumn1;
            $column2 = $srcColumn2;
743
        } else {
744
745
            $column1 = $keys[0];
            $column2 = $keys[1];
746
747
748
749
        }

        $row = array_shift($arr);
        while (null !== $row) {
750
            $new[] = [$destColumn1 => $row[$column1], $destColumn2 => $row[$column2]];
751
752
753
754
755
            $row = array_shift($arr);
        }

        return $new;
    }
756
757
758
759
760
761
762
763
764
765

    /**
     * Proxy for mysqli::real_escape_string()
     *
     * @param string $value
     * @return string
     */
    public function realEscapeString($value) {
        return $this->mysqli->real_escape_string($value);
    }
Carsten  Rose's avatar
Carsten Rose committed
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786

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

        $sqlStatements = file_get_contents($filename);

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

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

    }
787
}