Database.php 24.8 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
require_once(__DIR__ . '/exceptions/UserFormException.php');
19
20
require_once(__DIR__ . '/exceptions/CodeException.php');
require_once(__DIR__ . '/exceptions/DbException.php');
21

22
require_once(__DIR__ . '/store/Store.php');
23
require_once(__DIR__ . '/store/Config.php');
24
require_once(__DIR__ . '/helper/Support.php');
25
require_once(__DIR__ . '/helper/Logger.php');
26
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

    /**
     * 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
        return $mysqli;
111
112
113
    }

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

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

150
        // for error reporting in exception
151
        if ($specificMessage) {
152
            $specificMessage .= " ";
153
        }
154

155
156
        $count = $this->prepareExecute($sql, $parameterArray, $queryType, $stat);

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

161
162
163
        if ($queryType === QUERY_TYPE_SELECT) {
            switch ($mode) {
                case ROW_IMPLODE_ALL:
164
                    $result = $this->fetchAll($mode);
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
                    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;
196

197
198
199
                default:
                    throw new DbException($specificMessage . "Unknown mode: $mode", ERROR_UNKNOWN_MODE);
            }
200
201
        } elseif ($queryType === QUERY_TYPE_INSERT) {
            $result = $stat[DB_INSERT_ID];
202
203
        } else {
            $result = $count;
204
205
        }

206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
        $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())
225
                throw new DbException('Error closing mysqli_stmt' . ERROR_DB_CLOSE_MYSQLI_STMT);
226
227
228
        }
        $this->mysqli_stmt = null;
        $this->mysqli_result = null;
229
230
231
    }

    /**
232
     * Execute a prepared SQL statement like SELECT, INSERT, UPDATE, DELETE, SHOW, ...
233
     *
234
     * Returns the number of selected rows (SELECT, SHOW, ..) or the affected rows (UPDATE, INSERT). $stat contains appropriate num_rows, insert_id or rows_affected.
235
236
237
     *
     * @param string $sql SQL statement with prepared statement variable.
     * @param array $parameterArray parameter array for prepared statement execution.
238
     * @param string $queryType returns QUERY_TYPE_SELECT | QUERY_TYPE_UPDATE | QUERY_TYPE_INSERT, depending on the query.
239
     * @param array $stat DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
240
241
     * @return int|mixed
     * @throws \qfq\CodeException
242
     * @throws \qfq\DbException
243
     * @throws \qfq\UserFormException
244
     */
245
    private function prepareExecute($sql, array $parameterArray = array(), &$queryType, array &$stat) {
246
247

        $sqlLogMode = $this->isSqlModify($sql) ? SQL_LOG_MODE_MODIFY : SQL_LOG_MODE_ALL;;
248
        $result = 0;
249
        $stat = array();
250

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

256
        // Logfile
257
        $this->dbLog($sqlLogMode, $sql, $parameterArray);
258

259
        if (false === ($this->mysqli_stmt = $this->mysqli->prepare($sql))) {
260
261
            $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
            throw new DbException('[ mysqli: ' . $this->mysqli->errno . ' ] ' . $this->mysqli->error, ERROR_DB_PREPARE);
262
263
264
        }

        if (count($parameterArray) > 0) {
265
            if (false === $this->prepareBindParam($parameterArray)) {
266
267
                $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
                throw new DbException('[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error, ERROR_DB_BIND);
268
269
270
271
            }
        }

        if (false === $this->mysqli_stmt->execute()) {
272
273
            $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
            throw new DbException('[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error, ERROR_DB_EXECUTE);
274
275
276
277
278
279
280
281
282
283
284
285
286
        }

        $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);
                }
287
                $queryType = QUERY_TYPE_SELECT;
288
                $this->mysqli_result = $result;
289
290
291
                $stat[DB_NUM_ROWS] = $this->mysqli_result->num_rows;
                $count = $stat[DB_NUM_ROWS];
                $msg = 'Read rows: ' . $stat[DB_NUM_ROWS];
292
                break;
293
            case 'REPLACE':
294
            case 'INSERT':
295
                $queryType = QUERY_TYPE_INSERT;
296
297
298
                $stat[DB_INSERT_ID] = $this->mysqli->insert_id;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
299
            $msg = 'ID: ' . $this->mysqli->insert_id;
300
301
302
                break;
            case 'UPDATE':
            case 'DELETE':
303
            case 'TRUNCATE':
304
305
306
                $queryType = QUERY_TYPE_UPDATE;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
307
308
                $msg = 'Affected rows: ' . $count;
                break;
309

310
311
312
313
314
315
316
            case 'SET':
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
                break;

317
            default:
318
                throw new DbException('Unknown comand: "' . $command . '"', ERROR_DB_UNKNOWN_COMMAND);
319
                break;
320
321
        }

322
323
324
        if ($this->store !== null) {
            $this->store->setVar(SYSTEM_SQL_COUNT, $count, STORE_SYSTEM);
        }
325

326
        // Logfile
327
        $this->dbLog($sqlLogMode, $msg);
328

329
330
331
        return $count;
    }

332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
    /**
     * Check if the given SQL Statement might modify data.
     *
     * @param $sql
     * @return bool  true is the statement might modify data, else: false
     */
    private function isSqlModify($sql) {
        $command = explode(' ', $sql, 2);
        switch (strtoupper($command[0])) {
            case 'INSERT':
            case 'UPDATE':
            case 'DELETE':
            case 'REPLACE':
            case 'TRUNCATE':
                return true;
        }
        return false;
    }

351
    /**
352
     * Decide if the SQL statement has to be logged. If yes, create a timestamp and do the log.
353
     *
354
     * @param $sql
355
356
     * @param array $parameterArray
     * @return string
357
     * @throws \qfq\UserFormException
358
     */
359
360
    private function dbLog($mode = SQL_LOG_MODE_ALL, $sql = '', $parameterArray = array()) {

361
362
        $status = '';

363
        $sqlLogMode = ($this->store === null) ? $this->store->getVar(SYSTEM_SQL_LOG_MODE, STORE_SYSTEM) : SQL_LOG_MODE_ERROR;
364
365
366

        switch ($mode) {
            case SQL_LOG_MODE_ALL:
367
368
369
                if ($sqlLogMode != SQL_LOG_MODE_ALL) {
                    return;
                }
370
371
372
                break;

            case SQL_LOG_MODE_MODIFY:
373
374
                break;

375
376
377
            case SQL_LOG_MODE_ERROR:
                break;

378
            default:
379
                throw new UserFormException("Unknown SQL_LOG_MODE: $mode", ERROR_UNKNOWN_SQL_LOG_MODE);
380
381
        }

382
        // Client IP Address
383
        $remoteAddress = ($this->store === null) ? $this->store->getVar(CLIENT_REMOTE_ADDRESS, STORE_CLIENT) : '0.0.0.0';
384

385
        $msg = '[' . date('Y.m.d H:i:s O') . '][' . $remoteAddress . ']';
386
387
388
389
390

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

392
393
        if (count($parameterArray) > 0) {
            $sql = $this->preparedStatementInsertParameter($sql, $parameterArray);
394
395
        }

396
        if ($sql !== '') {
397
398
399
400
            if ($mode == SQL_LOG_MODE_ERROR) {
                $status = 'FAILED: ';
            }
            $msg .= '[' . $status . $sql . ']';
401
        }
402

403
        Logger::logMessage($msg, $this->sqlLog);
404
405
    }

406
407
    /**
     * @param $sql
408
409
     * @param $parameterArray
     * @return string
410
     */
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
    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 = '?';
            }
426
        }
427
428
429
430
431
        if (isset($sqlArray[$ii])) {
            $msg .= $sqlArray[$ii];
        }

        return $msg;
432
433
    }

434
    /**
435
     * @param $arr
436
     */
437
    private function prepareBindParam($arr) {
438

439
440
441
442
        $bindParam = new BindParam();

        for ($ii = 0; $ii < count($arr); $ii++) {
            $bindParam->add($arr[$ii]);
443
        }
444
        call_user_func_array([$this->mysqli_stmt, 'bind_param'], $bindParam->get());
445
446
    }

447
448
449
    /**
     * Fetch all rows of the result as associative array.
     *
450
451
     * mode:
     *  ROW_IMPLODE_ALL: Return string. All cells of all rows imploded to one string.
452
     *  ROW_KEYS: Return num array with column names in $keys
453
454
455
456
     *  default: Return 2-dimensional assoc array
     *
     * @param string $mode
     * @param array $keys
457
     * @return array|bool|mixed|string false in case of an error.
458
459
460
461
462
     *              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
     *
     */
463
464
    private function fetchAll($mode = '', &$keys = array()) {
        if ($this->mysqli_result == null || $this->mysqli_result == false) {
465
466
467
            return false;
        }

468
        if ($this->mysqli_result->num_rows === 0) {
469
470
471
            return ($mode === ROW_IMPLODE_ALL) ? "" : array();
        }

472
473
474
        switch ($mode) {
            case ROW_IMPLODE_ALL:
                $str = "";
475
                foreach ($this->mysqli_result->fetch_all(MYSQLI_NUM) as $row) {
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
                    $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);
492
493
494
        }
    }

495
496
497
498
499
500
501
502
503
504
505
506
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
    /**
     * 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`");
    }

585
    /**
586
587
     * Wrapper for sql(), to simplyfy access.
     *
588
589
     * @param $sql
     * @param array $keys
590
     * @param array $stat
591
     * @return array|bool
592
     * @throws \qfq\CodeException
593
     * @throws \qfq\DbException
594
     */
595
    public function sqlKeys($sql, array &$keys, array &$stat = array()) {
596

597
        return $this->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
598
    }
599

600
601
602
603
604
605
    /**
     * Returns lastInsertId
     *
     * @return string
     */
    public function getLastInsertId() {
606
        // Do not try to use $this->mysqli->lastInsertId - this is not valid at any given time.
607
        return $this->mysqli->insert_id;
608
    }
Carsten  Rose's avatar
Carsten Rose committed
609

610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
    /**
     * 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;
    }

633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
    /**
     * 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
648
        HelperFormElement::explodeParameterInArrayElements($feSpecNative, FE_PARAMETER);
649
650
651
652

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

653
654
655
        // Check for templateGroup Elements to explode them
        $feSpecNative = HelperFormElement::explodeTemplateGroupElements($feSpecNative);

656
        // Copy Attributes to FormElements
657
        $feSpecNative = HelperFormElement::copyAttributesToFormElements($formSpec, $feSpecNative);
658
659
660
661

        return $feSpecNative;
    }

662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
    /**
     * 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.
696
     * $arr might contain one or more columns.
697
698
699
700
     * 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
701
702
703
704
     * @param string $srcColumn1
     * @param string $srcColumn2
     * @param string $destColumn1
     * @param string $destColumn2
705
706
     * @return array
     */
707
708
709
710
711
712
713
714
715
716
    public function makeArrayDict(array $arr, $srcColumn1, $srcColumn2, $destColumn1 = '', $destColumn2 = '') {

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

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

717
718
719
720
721
722
723
724
725
        $new = array();

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

        $row = $arr[0];
        $keys = array_keys($row);
        if (count($row) < 2) {
726
727
728
729
730
            $column1 = $keys[0];
            $column2 = $keys[0];
        } elseif (array_key_exists($srcColumn1, $row) && array_key_exists($srcColumn2, $row)) {
            $column1 = $srcColumn1;
            $column2 = $srcColumn2;
731
        } else {
732
733
            $column1 = $keys[0];
            $column2 = $keys[1];
734
735
736
737
        }

        $row = array_shift($arr);
        while (null !== $row) {
738
            $new[] = [$destColumn1 => $row[$column1], $destColumn2 => $row[$column2]];
739
740
741
742
743
            $row = array_shift($arr);
        }

        return $new;
    }
744
}