Database.php 16.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\UserException;
15
use qfq\Support;
16
use qfq\Store;
17

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

22
23
require_once(__DIR__ . '/store/Store.php');
require_once(__DIR__ . '/helper/Support.php');
24
require_once(__DIR__ . '/helper/Logger.php');
25
require_once(__DIR__ . '/helper/BindParam.php');
26

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

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

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

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

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

53
54
55
    /**
     * @var string
     */
56
    private $sqlLog = '';
57
58
59
60
61
62
63
64
65

    /**
     * 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
     * @throws UserException
     */
    public function __construct() {
66
        $this->store = Store::getInstance();
67

68
69
        if ($this->mysqli === null) {
            $this->mysqli = $this->dbConnect();
70
        }
71
        $this->sqlLog = $this->store->getVar(SYSTEM_SQL_LOG, STORE_SYSTEM);
72
73
    }

74

75
    /**
76
77
78
     * Open mysqli database connection if not already done.
     *
     * @return \mysqli
79
80
81
     * @throws UserException
     */
    private function dbConnect() {
82
83
        $mysqli = null;

84
85
86
87
        $dbuser = $this->store->getVar(SYSTEM_DB_USER, STORE_SYSTEM);
        $dbserver = $this->store->getVar(SYSTEM_DB_SERVER, STORE_SYSTEM);
        $dbpw = $this->store->getVar(SYSTEM_DB_PASSWORD, STORE_SYSTEM);
        $db = $this->store->getVar(SYSTEM_DB_NAME, STORE_SYSTEM);
88

89
90
91
        $mysqli = new \mysqli($dbserver, $dbuser, $dbpw, $db);

        if ($mysqli->connect_error) {
92
            throw new UserException ("Error open Database 'mysql:host=" . $dbserver . ";dbname=" . $db . ";dbuser=" . $dbuser . "'': " . $mysqli->connect_errno . PHP_EOL . $mysqli->connect_error, ERROR_DB_OPEN);
93
94
        }

95
        return $mysqli;
96
97
98
99
100
101
102
103
104
105
    }

    /**
     * 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.
     */
106
    public function getRowCount() {
107
        if ($this->mysqli_result == null) {
108
109
110
            return false;
        }

111
        return $this->mysqli_result->num_rows;
112
113
114
115
116
117
118
119
    }

    /**
     * Get the values for a given ENUM or SET column
     *
     * @param string $table name of the table
     * @param string $columnName name of the column
     *
120
     * @throws UserException if the table or column does not exist, or is not of type ENUM or SET
121
122
     * @return array
     */
123
    public function getEnumSetValueList($table, $columnName) {
124
125
126
127
128
129
130
131
132
133
134
135

        $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
136
        $enumSetString = mb_substr($setEnumDefinition, $tokenLength, $len - (2 + $tokenLength));
137
138
139

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

140
        if (($setEnumValueList = explode("','", $enumSetString)) === false) {
141
142
143
144
145
146
147
148
149
150
151
152
153
154
            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
155
     * @return array the definition of the column as retrieved by Database::getTableDefinition().
156
     *
157
     * @throws \qfq\DbException
158
159
     */
    private function getFieldDefinitionFromTable($table, $columnName) {
160
        $tableDefinition = $this->getTableDefinition($table);
161
162
163
164
165
        foreach ($tableDefinition AS $row) {
            if ($row["Field"] == $columnName) {
                return $row;
            }
        }
166
        throw new DbException("Column name '$columnName' not found in table '$table'.", ERROR_DB_COLUMN_NOT_FOUND_IN_TABLE);
167
168
169
    }

    /**
170
171
172
173
174
175
176
177
178
     * 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
179
180
181
182
183
     *
     * @param string $table table to retrieve column definition from
     *
     * @return array column definition of table as returned by SHOW FIELDS FROM as associative array.
     */
184
    public function getTableDefinition($table) {
185
        return $this->sql("SHOW FIELDS FROM `$table`");
186
187
188
    }

    /**
189
     * Fires query $sql and fetches result als assoc array (all modes but ROW_KEYS) or as num array (mode: ROW_EKYS). Throws exception.
190
     *
191
     * $mode
192
193
194
195
196
197
198
     *  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.
     *  ROW_EXPECT_0: Return empty string if there is now record row, Else an exception.
     *  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.
     *  ROW_KEYS: Return 2-dimensional num(!) array. Every query row is one array row. In $keys are the column names.
199
200
     *
     * @param $sql
201
202
203
     * @param string $mode
     * @param array $parameterArray
     * @param string $specificMessage
204
     * @return mixed|null                  If no record found, empty string ( ROW_EXPECT_0_1, ROW_EXPECT_1) or empty array (all other modes)
205
206
     * @throws \qfq\CodeException
     * @throws \qfq\DbException
207
     */
208
209
210
    public function sql($sql, $mode = ROW_REGULAR, array $parameterArray = array(), $specificMessage = '', array &$keys = array()) {
        $result = array();
        $this->closeMysqliStmt();
211
212
213
214
215

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

216
        // for error reporting in exception
217
        if ($specificMessage)
218
            $specificMessage .= " ";
219

Carsten  Rose's avatar
Carsten Rose committed
220
        $count = $this->prepareExecute($sql, $parameterArray);
221
        if ($count === false) {
222
            throw new DbException($specificMessage . "No idea why this error happens - please take some time and check this: $sql", ERROR_DB_GENERIC_CHECK);
223
224
        }

225
226
        switch ($mode) {
            case ROW_IMPLODE_ALL:
227
                $result = $this->fetchAll($mode);
228
                break;
229
            case ROW_KEYS:
230
            case ROW_REGULAR:
231
                $result = $this->fetchAll($mode, $keys);
232
233
234
                break;
            case ROW_EXPECT_0:
                if ($count === 0)
235
236
                    $result = array();
                else
237
                    throw new DbException($specificMessage . "Expected no record, got $count rows: $sql", ERROR_DB_TOO_MANY_ROWS);
238
239
240
                break;
            case ROW_EXPECT_1:
                if ($count === 1)
241
242
                    $result = $this->fetchAll($mode)[0];
                else
243
                    throw new DbException($specificMessage . "Expected one record, got $count: $sql", ERROR_DB_COUNT_DO_NOT_MATCH);
244
245
246
                break;
            case ROW_EXPECT_0_1:
                if ($count === 1)
247
248
249
250
                    $result = $this->fetchAll($mode)[0];
                elseif ($count === 0)
                    $result = array();
                else
251
                    throw new DbException($specificMessage . "Expected no record, got $count rows: $sql", ERROR_DB_TOO_MANY_ROWS);
252
253
254
                break;
            case ROW_EXPECT_GE_1:
                if ($count > 0)
255
256
                    $result = $this->fetchAll($mode);
                else
257
                    throw new DbException($specificMessage . "Expected at least one record, got nothing: $sql", ERROR_DB_TOO_FEW_ROWS);
258
                break;
259

260
261
            default:
                throw new DbException($specificMessage . "Unknown mode: $mode", ERROR_UNKNOWN_MODE);
262
263
        }

264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
        $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())
283
                throw new DbException('Error closing mysqli_stmt' . ERROR_DB_CLOSE_MYSQLI_STMT);
284
285
286
        }
        $this->mysqli_stmt = null;
        $this->mysqli_result = null;
287
288
289
    }

    /**
290
     * Execute a prepared SQL statement like SELECT, INSERT, UPDATE, DELETE, SHOW, ...
291
     *
292
     * Returns the number of selected rows (SELECT, SHOW, ..) or the affected rows (UPDATE) or the last insert id (INSERT)
293
294
295
     *
     * @param string $sql SQL statement with prepared statement variable.
     * @param array $parameterArray parameter array for prepared statement execution.
296
297
     * @return int|mixed
     * @throws \qfq\CodeException
298
299
     * @throws \qfq\DbException
     * @throws \qfq\UserException
300
     */
301
    private function prepareExecute($sql, array $parameterArray = array()) {
302
        $result = 0;
303
        $this->store->setVar(SYSTEM_SQL_FINAL, $sql, STORE_SYSTEM);
Carsten  Rose's avatar
Carsten Rose committed
304
        $this->store->setVar(SYSTEM_SQL_PARAM_ARRAY, $parameterArray, STORE_SYSTEM);
305

306
307
308
        // Logfile
        $this->dbLog($sql, $parameterArray);

309
310
311
312
        if (false === ($this->mysqli_stmt = $this->mysqli->prepare($sql))) {
            throw new DbException('[ mysqli: ' . $this->mysqli->errno . ' ] ' . $this->mysqli->error, ERROR_DB_PREPARE);
        }

313

314
        if (count($parameterArray) > 0) {
315
            if (false === $this->prepareBindParam($parameterArray)) {
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
                throw new DbException('[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error, ERROR_DB_BIND);
            }
        }

        if (false === $this->mysqli_stmt->execute()) {
            throw new DbException('[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error, ERROR_DB_EXECUTE);
        }

        $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);
                }
                $this->mysqli_result = $result;
                $count = $this->mysqli_result->num_rows;
337
            $msg = 'Read rows: ' . $count;
338
339
340
341
342
343
344
345
346
347
348
349
350
                break;
            case 'INSERT':
                $count = $this->mysqli->insert_id;
                $msg = 'ID: ' . $count;
                break;
            case 'UPDATE':
            case 'REPLACE':
            case 'DELETE':
                $count = $this->mysqli->affected_rows;
                $msg = 'Affected rows: ' . $count;
                break;
            default:
                break;
351
352
353
354
        }

        $this->store->setVar(SYSTEM_SQL_COUNT, $count, STORE_SYSTEM);

355
356
357
        // Logfile
        $this->dbLog($msg);

358
359
360
        return $count;
    }

361
    /**
362
363
     * Decide if the SQL statement has to be logged.If yes, create a timestamp and do the log.
     *
364
     * @param $sql
365
366
367
     * @param array $parameterArray
     * @return string
     * @throws \qfq\UserException
368
369
     */
    private function dbLog($sql, $parameterArray = array()) {
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386

        $mode = $this->store->getVar(SYSTEM_SQL_LOG_MODE, STORE_SYSTEM);
        switch ($mode) {
            case SQL_LOG_MODE_ALL:
                break;

            case SQL_LOG_MODE_MODIFY:
                if ($this->isSqlModify($sql)) {
                    break;
                }
                // nothing to log.
                return;
            default:
                throw new UserException("Unknown SQL_LOG_MODE: $mode", ERROR_UNKNOWN_SQL_LOG_MODE);
        }

        $msg = '[' . date('Y.m.d H:i:s O') . '][';
387
388
389
390
391
392
393
394
395

        if (count($parameterArray) === 0) {
            $msg .= $sql;
        } else {

            $sqlArray = explode('?', $sql);
            $ii = 0;
            foreach ($parameterArray as $value) {
                if (isset($sqlArray[$ii])) {
396
397
398
399
                    if (is_array($value)) {
                        $value = OnArray::toString($value);
                    }

400
401
402
403
404
405
406
407
408
409
410
                    $msg .= $sqlArray[$ii++] . "'" . $value . "'";
                } else {
                    $msg = '?';
                }
            }
            if (isset($sqlArray[$ii]))
                $msg .= $sqlArray[$ii];
        }

        $msg .= ']';

411
        Logger::logMessage($msg, $this->sqlLog);
412
413
    }

414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
    /**
     * 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':
                return true;
        }
        return false;
    }

432
    /**
433
     * @param $arr
434
     */
435
    private function prepareBindParam($arr) {
436

437
438
439
440
        $bindParam = new BindParam();

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

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

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

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

    /**
494
495
     * Wrapper for sql(), to simplyfy access.
     *
496
497
498
499
     * @param $sql
     * @param array $keys
     * @return array|bool
     * @throws \qfq\DbException
500
     */
501
    public function sqlKeys($sql, array &$keys) {
502

503
504
        return $this->sql($sql, ROW_KEYS, array(), '', $keys);
    }
505

506
507
508
509
510
511
    /**
     * Returns lastInsertId
     *
     * @return string
     */
    public function getLastInsertId() {
512
        // Do not try to use $this->mysqli->lastInsertId - this is not valid at any given time.
513
        return $this->mysqli->insert_id;
514
    }
Carsten  Rose's avatar
Carsten Rose committed
515

516
}