Completed
Pull Request — master (#10)
by Todd
02:06
created

MySqlDb::buildWhere()   D

Complexity

Conditions 27
Paths 79

Size

Total Lines 94

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 61
CRAP Score 27.0232

Importance

Changes 0
Metric Value
dl 0
loc 94
ccs 61
cts 63
cp 0.9683
rs 4.1666
c 0
b 0
f 0
cc 27
nc 79
nop 2
crap 27.0232

How to fix   Long Method    Complexity   

Long Method

Small methods make your code easier to understand, in particular if combined with a good name. Besides, if your method is small, finding a good name is usually much easier.

For example, if you find yourself adding comments to a method's body, this is usually a good sign to extract the commented part to a new method, and use the comment as a starting point when coming up with a good name for this new method.

Commonly applied refactorings include:

1
<?php
2
/**
3
 * @author Todd Burry <[email protected]>
4
 * @copyright 2009-2014 Vanilla Forums Inc.
5
 * @license MIT
6
 */
7
8
namespace Garden\Db\Drivers;
9
10
use Garden\Db\Db;
11
use Garden\Db\Identifier;
12
use Garden\Db\Literal;
13
use PDO;
14
15
/**
16
 * A {@link Db} class for connecting to MySQL.
17
 */
18
class MySqlDb extends Db {
19
    const MYSQL_DATE_FORMAT = 'Y-m-d H:i:s';
20
21
    /**
22
     * @var string
23
     */
24
    protected $dbname;
25
26
    protected static $map = [
27
        Db::OP_GT => '>',
28
        Db::OP_GTE => '>=',
29
        Db::OP_LT => '<',
30
        Db::OP_LTE => '<=',
31
        Db::OP_LIKE => 'like',
32
        Db::OP_AND => 'and',
33
        Db::OP_OR => 'or',
34
    ];
35
36
    /**
37
     * {@inheritdoc}
38
     */
39 8
    protected function dropTableDb(string $table, array $options = []) {
40
        $sql = 'drop table '.
41 8
            ($options[Db::OPTION_IGNORE] ? 'if exists ' : '').
42 8
            $this->prefixTable($table);
43
44 8
        $this->queryDefine($sql);
45 8
    }
46
47
    /**
48
     * {@inheritdoc}
49
     */
50 12
    protected function fetchTableDefDb(string $table) {
51 12
        $columns = $this->fetchColumnDefsDb($table);
52
53 12
        if (empty($columns)) {
54
            // A table with no columns does not exist.
55 8
            return null;
56
        }
57
58 10
        $indexes = $this->fetchIndexesDb($table);
59
60
        $tableDef = [
61 10
            'name' => $table,
62 10
            'columns' => $columns,
63 10
            'indexes' => $indexes
64
        ];
65
66 10
        return $tableDef;
67
    }
68
69
    /**
70
     * {@inheritdoc}
71
     */
72 7
    protected function fetchColumnDefsDb(string $table) {
73 7
        $rows = $this->get(
74 7
            new Identifier('information_schema', 'COLUMNS'),
75
            [
76 7
                'TABLE_SCHEMA' => $this->getDbName(),
77 7
                'TABLE_NAME' => $this->prefixTable($table, false)
78
            ],
79
            [
80 7
                Db::OPTION_FETCH_MODE => PDO::FETCH_ASSOC,
81
                'order' => ['TABLE_NAME', 'ORDINAL_POSITION']
82
            ]
83
        );
84
85 7
        $columns = [];
86 7
        foreach ($rows as $row) {
87 6
            $columnType = $row['COLUMN_TYPE'];
88 6
            if ($columnType === 'tinyint(1)') {
89 1
                $columnType = 'bool';
90
            }
91 6
            $column = Db::typeDef($columnType);
92 6
            if ($column === null) {
93
                throw new \Exception("Unknown type '$columnType'.", 500);
94
            }
95
96 6
            $column['allowNull'] = strcasecmp($row['IS_NULLABLE'], 'YES') === 0;
97
98 6
            if (($default = $row['COLUMN_DEFAULT']) !== null) {
99 4
                $column['default'] = $this->forceType($default, $column['type']);
100
            }
101
102 6
            if ($row['EXTRA'] === 'auto_increment') {
103 1
                $column['autoIncrement'] = true;
104
            }
105
106 6
            if ($row['COLUMN_KEY'] === 'PRI') {
107 3
                $column['primary'] = true;
108
            }
109
110 6
            $columns[$row['COLUMN_NAME']] = $column;
111
        }
112
113 7
        return $columns;
114
    }
115
116
    /**
117
     * {@inheritdoc}
118
     */
119 79
    public function get($table, array $where, array $options = []): \PDOStatement {
120 79
        $sql = $this->buildSelect($table, $where, $options);
121 79
        $result = $this->query($sql, [], $options);
122 79
        return $result;
123
    }
124
125
    /**
126
     * Build a sql select statement.
127
     *
128
     * @param string|Identifier $table The name of the main table.
129
     * @param array $where The where filter.
130
     * @param array $options An array of additional query options.
131
     * @return string Returns the select statement as a string.
132
     * @see Db::get()
133
     */
134 84
    protected function buildSelect($table, array $where, array $options = []) {
135 84
        $options += ['limit' => 0];
136
137 84
        $sql = '';
138
139
        // Build the select clause.
140 84
        if (!empty($options['columns'])) {
141 16
            $columns = array();
142 16
            foreach ($options['columns'] as $value) {
143 16
                $columns[] = $this->escape($value);
144
            }
145 16
            $sql .= 'select '.implode(', ', $columns);
146
        } else {
147 78
            $sql .= "select *";
148
        }
149
150
        // Build the from clause.
151 84
        if ($table instanceof Literal) {
152
            $table = $table->getValue($this);
153
        } else {
154 84
            $table = $this->prefixTable($table);
155
        }
156 84
        $sql .= "\nfrom $table";
157
158
        // Build the where clause.
159 84
        $whereString = $this->buildWhere($where, Db::OP_AND);
160 84
        if ($whereString) {
161 54
            $sql .= "\nwhere ".$whereString;
162
        }
163
164
        // Build the order.
165 84
        if (!empty($options['order'])) {
166 42
            $orders = [];
167 42
            foreach ($options['order'] as $column) {
168 42
                if ($column[0] === '-') {
169 2
                    $order = $this->escape(substr($column, 1)).' desc';
170
                } else {
171 42
                    $order = $this->escape($column);
172
                }
173 42
                $orders[] = $order;
174
            }
175 42
            $sql .= "\norder by ".implode(', ', $orders);
176
        }
177
178
        // Build the limit, offset.
179 84
        if (!empty($options['limit'])) {
180 16
            $limit = (int)$options['limit'];
181 16
            $sql .= "\nlimit $limit";
182
        }
183
184 84
        if (!empty($options['offset'])) {
185 1
            $sql .= ' offset '.((int)$options['offset']);
186 84
        } elseif (isset($options['page'])) {
187
            $offset = $options['limit'] * ($options['page'] - 1);
188
            $sql .= ' offset '.$offset;
189
        }
190
191 84
        return $sql;
192
    }
193
194
    /**
195
     * Build a where clause from a where array.
196
     *
197
     * @param array $where There where string.
198
     * This is an array in the form `['column' => 'value']` with more advanced options for non-equality comparisons.
199
     * @param string $op The logical operator to join multiple field comparisons.
200
     * @return string The where string.
201
     */
202 88
    protected function buildWhere($where, $op = Db::OP_AND) {
203 88
        $map = static::$map;
204 88
        $strop = $map[$op];
205
206 88
        $result = '';
207 88
        foreach ($where as $column => $value) {
208 58
            $btcolumn = $this->escape($column);
209
210 58
            if (is_array($value)) {
211 32
                if (is_numeric($column)) {
212
                    // This is a bracketed expression.
213 4
                    $result .= (empty($result) ? '' : "\n  $strop ").
214 4
                        "(\n  ".
215 4
                        $this->buildWhere($value, $op).
216 4
                        "\n  )";
217 32
                } elseif (in_array($column, [Db::OP_AND, Db::OP_OR])) {
218
                    // This is an AND/OR expression.
219 4
                    $result .= (empty($result) ? '' : "\n  $strop ").
220 4
                        "(\n  ".
221 4
                        $this->buildWhere($value, $column).
222 4
                        "\n  )";
223
                } else {
224 28
                    if (isset($value[0])) {
225
                        // This is a short in syntax.
226 2
                        $value = [Db::OP_IN => $value];
227
                    }
228
229 28
                    foreach ($value as $vop => $rval) {
230 28
                        if ($result) {
231 6
                            $result .= "\n  $strop ";
232
                        }
233
234
                        switch ($vop) {
235 28
                            case Db::OP_AND:
236 28
                            case Db::OP_OR:
237 4
                                if (is_numeric($column)) {
238
                                    $innerWhere = $rval;
239
                                } else {
240 4
                                    $innerWhere = [$column => $rval];
241
                                }
242
                                $result .= "(\n  ".
243 4
                                    $this->buildWhere($innerWhere, $vop).
0 ignored issues
show
Bug introduced by
It seems like $innerWhere defined by $rval on line 238 can also be of type null; however, Garden\Db\Drivers\MySqlDb::buildWhere() does only seem to accept array, maybe add an additional type check?

If a method or function can return multiple different values and unless you are sure that you only can receive a single value in this context, we recommend to add an additional type check:

/**
 * @return array|string
 */
function returnsDifferentValues($x) {
    if ($x) {
        return 'foo';
    }

    return array();
}

$x = returnsDifferentValues($y);
if (is_array($x)) {
    // $x is an array.
}

If this a common case that PHP Analyzer should handle natively, please let us know by opening an issue.

Loading history...
244 4
                                    "\n  )";
245 4
                                break;
246 28
                            case Db::OP_EQ:
247 6
                                if ($rval === null) {
248
                                    $result .= "$btcolumn is null";
249 6
                                } elseif (is_array($rval)) {
250 2
                                    $result .= "$btcolumn in ".$this->bracketList($rval);
251
                                } else {
252 4
                                    $result .= "$btcolumn = ".$this->quote($rval);
253
                                }
254 6
                                break;
255 24
                            case Db::OP_GT:
256 22
                            case Db::OP_GTE:
257 20
                            case Db::OP_LT:
258 14
                            case Db::OP_LTE:
259 12
                                $result .= "$btcolumn {$map[$vop]} ".$this->quote($rval);
260 12
                                break;
261 12
                            case Db::OP_LIKE:
262 2
                                $result .= $this->buildLike($btcolumn, $rval);
263 2
                                break;
264 10
                            case Db::OP_IN:
265
                                // Quote the in values.
266 4
                                $rval = array_map([$this, 'quote'], (array)$rval);
267 4
                                $result .= "$btcolumn in (".implode(', ', $rval).')';
268 4
                                break;
269 6
                            case Db::OP_NEQ:
270 6
                                if ($rval === null) {
271 2
                                    $result .= "$btcolumn is not null";
272 4
                                } elseif (is_array($rval)) {
273 2
                                    $result .= "$btcolumn not in ".$this->bracketList($rval);
274
                                } else {
275 2
                                    $result .= "$btcolumn <> ".$this->quote($rval);
276
                                }
277 32
                                break;
278
                        }
279
                    }
280
                }
281
            } else {
282 32
                if ($result) {
283 11
                    $result .= "\n  $strop ";
284
                }
285
286
                // This is just an equality operator.
287 32
                if ($value === null) {
288 2
                    $result .= "$btcolumn is null";
289
                } else {
290 58
                    $result .= "$btcolumn = ".$this->quote($value);
291
                }
292
            }
293
        }
294 88
        return $result;
295
    }
296
297
    /**
298
     * Build a like expression.
299
     *
300
     * @param string $column The column name.
301
     * @param mixed $value The right-hand value.
302
     * @return string Returns the like expression.
303
     */
304 1
    protected function buildLike(string $column, $value): string {
305 1
        return "$column like ".$this->quote($value);
306
    }
307
308
    /**
309
     * Convert an array into a bracketed list suitable for MySQL clauses.
310
     *
311
     * @param array $row The row to expand.
312
     * @param string $quote The quotes to surroud the items with. There are two special cases.
313
     * ' (single quote)
314
     * : The row will be passed through {@link PDO::quote()}.
315
     * ` (backticks)
316
     * : The row will be passed through {@link MySqlDb::backtick()}.
317
     * @return string Returns the bracket list.
318
     */
319 81
    public function bracketList($row, $quote = "'") {
320
        switch ($quote) {
321 81
            case "'":
322 67
                $row = array_map([$this, 'quote'], $row);
323 67
                $quote = '';
324 67
                break;
325 81
            case '`':
326 81
                $row = array_map([$this, 'escape'], $row);
327 81
                $quote = '';
328 81
                break;
329
        }
330
331 81
        return "($quote".implode("$quote, $quote", $row)."$quote)";
332
    }
333
334
335
    /**
336
     * Get the current database name.
337
     *
338
     * @return mixed
339
     */
340 7
    private function getDbName() {
341 7
        if (!isset($this->dbname)) {
342
            $this->dbname = $this->getPDO()->query('select database()')->fetchColumn();
343
        }
344 7
        return $this->dbname;
345
    }
346
347
    /**
348
     * {@inheritdoc}
349
     */
350 21
    protected function nativeDbType(array $type) {
351 21
        static $translations = ['bool' => 'tinyint(1)', 'byte' => 'tinyint', 'short' => 'smallint', 'long' => 'bigint'];
352
353
        // Translate the dbtype to a MySQL native type.
354 21
        if (isset($translations[$type['dbtype']])) {
355 1
            $type['dbtype'] = $translations[$type['dbtype']];
356
        }
357
358
        // Unsigned is represented differently in MySQL.
359 21
        $unsigned = !empty($type['unsigned']);
360 21
        unset ($type['unsigned']);
361
362 21
        $dbType = static::dbType($type).($unsigned ? ' unsigned' : '');
363
364 21
        return $dbType;
365
    }
366
367
    /**
368
     * Parse a column type string and return it in a way that is suitable for a create/alter table statement.
369
     *
370
     * @param string $typeString The string to parse.
371
     * @return string Returns a canonical string.
0 ignored issues
show
Documentation introduced by
Should the return type not be string[]|null|string?

This check compares the return type specified in the @return annotation of a function or method doc comment with the types returned by the function and raises an issue if they mismatch.

Loading history...
372
     */
373
    protected function columnTypeString($typeString) {
374
        $type = null;
375
376
        if (substr($typeString, 0, 4) === 'enum') {
377
            // This is an enum which will come in as an array.
378
            if (preg_match_all("`'([^']+)'`", $typeString, $matches)) {
379
                $type = $matches[1];
380
            }
381
        } else {
382
            if (preg_match('`([a-z]+)\s*(?:\((\d+(?:\s*,\s*\d+)*)\))?\s*(unsigned)?`', $typeString, $matches)) {
383
                //         var_dump($matches);
384
                $str = $matches[1];
385
                $length = self::val(2, $matches);
386
                $unsigned = self::val(3, $matches);
387
388
                if (substr($str, 0, 1) == 'u') {
389
                    $unsigned = true;
390
                    $str = substr($str, 1);
391
                }
392
393
                // Remove the length from types without real lengths.
394
                if (in_array($str, array('tinyint', 'smallint', 'mediumint', 'int', 'bigint', 'float', 'double'))) {
395
                    $length = null;
396
                }
397
398
                $type = $str;
399
                if ($length) {
400
                    $length = str_replace(' ', '', $length);
401
                    $type .= "($length)";
402
                }
403
                if ($unsigned) {
404
                    $type .= ' unsigned';
405
                }
406
            }
407
        }
408
409
        if (!$type) {
410
            debug_print_backtrace();
411
            trigger_error("Couldn't parse type $typeString", E_USER_ERROR);
412
        }
413
414
        return $type;
415
    }
416
417
    /**
418
     * Get the indexes from the database.
419
     *
420
     * @param string $table The name of the table to get the indexes for.
421
     * @return array|null
422
     */
423 5
    protected function fetchIndexesDb($table = '') {
424 5
        $stm = $this->get(
425 5
            new Identifier('information_schema', 'STATISTICS'),
426
            [
427 5
                'TABLE_SCHEMA' => $this->getDbName(),
428 5
                'TABLE_NAME' => $this->prefixTable($table, false)
429
            ],
430
            [
431 5
                'columns' => [
432
                    'INDEX_NAME',
433
                    'COLUMN_NAME',
434
                    'NON_UNIQUE'
435
                ],
436
                'order' => ['INDEX_NAME', 'SEQ_IN_INDEX']
437
            ]
438
        );
439 5
        $indexRows = $stm->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP);
440
441 5
        $indexes = [];
442 5
        foreach ($indexRows as $indexName => $columns) {
443
            $index = [
444 4
                'type' => null,
445 4
                'columns' => array_column($columns, 'COLUMN_NAME'),
446 4
                'name' => $indexName
447
            ];
448
449 4
            if ($indexName === 'PRIMARY') {
450 2
                $index['type'] = Db::INDEX_PK;
451
            } else {
452 2
                $index['type'] = $columns[0]['NON_UNIQUE'] ? Db::INDEX_IX : Db::INDEX_UNIQUE;
453
            }
454 4
            $indexes[] = $index;
455
        }
456
457 5
        return $indexes;
458
    }
459
460
    /**
461
     * {@inheritdoc}
462
     */
463 1
    protected function fetchTableNamesDb() {
464
        // Get the table names.
465 1
        $tables = $this->get(
466 1
            new Identifier('information_schema', 'TABLES'),
467
            [
468 1
                'TABLE_SCHEMA' => $this->getDbName(),
469 1
                'TABLE_NAME' => [Db::OP_LIKE => $this->escapeLike($this->getPx()).'%']
470
            ],
471
            [
472 1
                'columns' => ['TABLE_NAME'],
473
                'fetchMode' => PDO::FETCH_ASSOC
474
            ]
475
        );
476
477 1
        return $tables->fetchAll(PDO::FETCH_COLUMN);
478
    }
479
480
    /**
481
     * {@inheritdoc}
482
     */
483 23
    public function insert(string $table, array $row, array $options = []) {
484 23
        $sql = $this->buildInsert($table, $row, $options);
485 23
        $id = $this->queryID($sql, [], $options);
486 23
        if (is_numeric($id)) {
487 23
            return (int)$id;
488
        } else {
489
            return $id;
490
        }
491
    }
492
493
    /**
494
     * Build an insert statement.
495
     *
496
     * @param string|Identifier $table The name of the table to insert to.
497
     * @param array $row The row to insert.
498
     * @param array $options An array of options for the insert. See {@link Db::insert} for the options.
499
     * @return string Returns the the sql string of the insert statement.
500
     */
501 36
    protected function buildInsert($table, array $row, $options = []) {
502 36
        if (self::val(Db::OPTION_UPSERT, $options)) {
503 2
            return $this->buildUpsert($table, $row, $options);
504 36
        } elseif (self::val(Db::OPTION_IGNORE, $options)) {
505 2
            $sql = 'insert ignore ';
506 35
        } elseif (self::val(Db::OPTION_REPLACE, $options)) {
507 2
            $sql = 'replace ';
508
        } else {
509 34
            $sql = 'insert ';
510
        }
511 36
        $sql .= $this->prefixTable($table);
512
513
        // Add the list of values.
514
        $sql .=
515 36
            "\n".$this->bracketList(array_keys($row), '`').
516 36
            "\nvalues".$this->bracketList($row, "'");
517
518 36
        return $sql;
519
    }
520
521
    /**
522
     * Build an upsert statement.
523
     *
524
     * An upsert statement is an insert on duplicate key statement in MySQL.
525
     *
526
     * @param string|Identifier $table The name of the table to update.
527
     * @param array $row The row to insert or update.
528
     * @param array $options An array of additional query options.
529
     * @return string Returns the upsert statement as a string.
530
     */
531 2
    protected function buildUpsert($table, array $row, $options = []) {
532
        // Build the initial insert statement first.
533 2
        unset($options[Db::OPTION_UPSERT]);
534 2
        $sql = $this->buildInsert($table, $row, $options);
535
536
        // Add the duplicate key stuff.
537 2
        $updates = [];
538 2
        foreach ($row as $key => $value) {
539 2
            $updates[] = $this->escape($key).' = values('.$this->escape($key).')';
540
        }
541 2
        $sql .= "\non duplicate key update ".implode(', ', $updates);
542
543 2
        return $sql;
544
    }
545
546
    /**
547
     * {@inheritdoc}
548
     */
549 54
    public function load(string $table, $rows, array $options = []) {
550 54
        $count = 0;
551 54
        $first = true;
552 54
        $spec = [];
553 54
        $stmt = null;
554
555
        // Loop over the rows and insert them with the statement.
556 54
        foreach ($rows as $row) {
557 44
            if ($first) {
558
                // Build the insert statement from the first row.
559 44
                foreach ($row as $key => $value) {
560 44
                    $spec[$key] = new Literal($this->paramName($key));
561
                }
562
563 44
                $sql = $this->buildInsert($table, $spec, $options);
564 44
                $stmt = $this->getPDO()->prepare($sql);
565 44
                $first = false;
566
            }
567
568 44
            $args = array_map([$this, 'argValue'], $row);
569 44
            $stmt->execute($args);
570 44
            $count += $stmt->rowCount();
571
        }
572
573 54
        return $count;
574
    }
575
576
    /**
577
     * Make a valid PDO parameter name from a string.
578
     *
579
     * This method replaces invalid placeholder characters with underscores.
580
     *
581
     * @param string $name The name to replace.
582
     * @return string
583
     */
584 44
    protected function paramName($name) {
585 44
        $result = ':'.preg_replace('`[^a-zA-Z0-9_]`', '_', $name);
586 44
        return $result;
587
    }
588
589
    /**
590
     * {@inheritdoc}
591
     */
592 10
    public function update(string $table, array $set, array $where, array $options = []): int {
593 10
        $sql = $this->buildUpdate($table, $set, $where, $options);
594 10
        $result = $this->queryModify($sql, [], $options);
595
596 10
        return $result;
597
    }
598
599
    /**
600
     * Build a sql update statement.
601
     *
602
     * @param string|Identifier $table The name of the table to update.
603
     * @param array $set An array of columns to set.
604
     * @param array $where The where filter.
605
     * @param array $options Additional options for the query.
606
     * @return string Returns the update statement as a string.
607
     */
608 4
    protected function buildUpdate($table, array $set, array $where, array $options = []): string {
609
        $sql = 'update '.
610 4
            (self::val(Db::OPTION_IGNORE, $options) ? 'ignore ' : '').
611 4
            $this->prefixTable($table).
612 4
            "\nset\n  ";
613
614 4
        $parts = [];
615 4
        foreach ($set as $key => $value) {
616 4
            $escapedKey = $this->escape($key);
617
618 4
            $parts[] = "$escapedKey = ".$this->quote($value, $escapedKey);
619
        }
620 4
        $sql .= implode(",\n  ", $parts);
621
622 4
        if (!empty($where)) {
623 4
            $sql .= "\nwhere ".$this->buildWhere($where, Db::OP_AND);
624
        }
625
626 4
        return $sql;
627
    }
628
629
    /**
630
     * {@inheritdoc}
631
     */
632 30
    public function delete(string $table, array $where, array $options = []): int {
633 30
        if (self::val(Db::OPTION_TRUNCATE, $options)) {
634
            if (!empty($where)) {
635
                throw new \InvalidArgumentException("You cannot truncate $table with a where filter.", 500);
636
            }
637
            $sql = 'truncate table '.$this->prefixTable($table);
638
        } else {
639 30
            $sql = 'delete from '.$this->prefixTable($table);
640
641 30
            if (!empty($where)) {
642
                $sql .= "\nwhere ".$this->buildWhere($where);
643
            }
644
        }
645 30
        return $this->queryModify($sql, [], $options);
646
    }
647
648
    /**
649
     * {@inheritdoc}
650
     */
651 21
    protected function createTableDb(array $tableDef, array $options = []) {
652 21
        $table = $tableDef['name'];
653
654
        // The table doesn't exist so this is a create table.
655 21
        $parts = array();
656 21
        foreach ($tableDef['columns'] as $name => $cdef) {
657 21
            $parts[] = $this->columnDefString($name, $cdef);
658
        }
659
660 21
        foreach (self::val('indexes', $tableDef, []) as $index) {
661 18
            $indexDef = $this->indexDefString($table, $index);
662 18
            if (!empty($indexDef)) {
663 18
                $parts[] = $indexDef;
664
            }
665
        }
666
667 21
        $tableName = $this->prefixTable($table);
668 21
        $sql = "create table $tableName (\n  ".
669 21
            implode(",\n  ", $parts).
670 21
            "\n)";
671
672 21
        if (self::val('collate', $options)) {
673
            $sql .= "\n collate {$options['collate']}";
674
        }
675
676 21
        $this->queryDefine($sql, $options);
677 21
    }
678
679
    /**
680
     * Construct a column definition string from an array defintion.
681
     *
682
     * @param string $name The name of the column.
683
     * @param array $cdef The column definition.
684
     * @return string Returns a string representing the column definition.
685
     */
686 21
    protected function columnDefString($name, array $cdef) {
687 21
        $result = $this->escape($name).' '.$this->nativeDbType($cdef);
688
689 21
        if (!self::val('allowNull', $cdef)) {
690 19
            $result .= ' not null';
691
        }
692
693 21
        if (isset($cdef['default'])) {
694 14
            $result .= ' default '.$this->quote($cdef['default']);
695
        }
696
697 21
        if (self::val('autoIncrement', $cdef)) {
698 10
            $result .= ' auto_increment';
699
        }
700
701 21
        return $result;
702
    }
703
704
    /**
705
     * Return the SDL string that defines an index.
706
     *
707
     * @param string $table The name of the table that the index is on.
708
     * @param array $def The index definition. This definition should have the following keys.
709
     *
710
     * columns
711
     * : An array of columns in the index.
712
     * type
713
     * : One of "index", "unique", or "primary".
714
     * @return null|string Returns the index string or null if the index is not correct.
715
     */
716 18
    protected function indexDefString($table, array $def) {
717 18
        $indexName = $this->escape($this->buildIndexName($table, $def));
718 18
        if (empty($def['columns'])) {
719
            throw new \DomainException("The `$table`.$indexName index has no columns.", 500);
720
        }
721 18
        switch (self::val('type', $def, Db::INDEX_IX)) {
722 18
            case Db::INDEX_IX:
723 11
                return "index $indexName ".$this->bracketList($def['columns'], '`');
724 16
            case Db::INDEX_UNIQUE:
725 1
                return "unique $indexName ".$this->bracketList($def['columns'], '`');
726 16
            case Db::INDEX_PK:
727 15
                return "primary key ".$this->bracketList($def['columns'], '`');
728
        }
729 2
        return null;
730
    }
731
732
    /**
733
     * {@inheritdoc}
734
     */
735 6
    protected function alterTableDb(array $alterDef, array $options = []) {
736 6
        $table = $alterDef['name'];
737 6
        $columnOrders = $this->getColumnOrders($alterDef['def']['columns']);
738 6
        $parts = [];
739
740
        // Add the columns and indexes.
741 6
        foreach ($alterDef['add']['columns'] as $cname => $cdef) {
742
            // Figure out the order of the column.
743 3
            $pos = self::val($cname, $columnOrders, '');
744 3
            $parts[] = 'add '.$this->columnDefString($cname, $cdef).$pos;
745
        }
746 6
        foreach ($alterDef['add']['indexes'] as $ixdef) {
747 3
            $parts[] = 'add '.$this->indexDefString($table, $ixdef);
748
        }
749
750
        // Alter the columns.
751 6
        foreach ($alterDef['alter']['columns'] as $cname => $cdef) {
752 3
            $parts[] = 'modify '.$this->columnDefString($cname, $cdef);
753
        }
754
755
        // Drop the columns and indexes.
756 6
        foreach ($alterDef['drop']['columns'] as $cname => $_) {
757
            $parts[] = 'drop '.$this->escape($cname);
758
        }
759 6
        foreach ($alterDef['drop']['indexes'] as $ixdef) {
760 2
            $parts[] = 'drop index '.$this->escape($ixdef['name']);
761
        }
762
763 6
        if (empty($parts)) {
764
            return false;
765
        }
766
767
        $sql = 'alter '.
768 6
            (self::val(Db::OPTION_IGNORE, $options) ? 'ignore ' : '').
769 6
            'table '.$this->prefixTable($table)."\n  ".
770 6
            implode(",\n  ", $parts);
771
772 6
        $this->queryDefine($sql, $options);
773 6
    }
774
775
    /**
776
     * Get an array of column orders so that added columns can be slotted into their correct spot.
777
     *
778
     * @param array $cdefs An array of column definitions.
779
     * @return array Returns an array of column orders suitable for an `alter table` statement.
780
     */
781 6
    private function getColumnOrders($cdefs) {
782 6
        $orders = array_flip(array_keys($cdefs));
783
784 6
        $prev = ' first';
785 6
        foreach ($orders as $cname => &$value) {
786 6
            $value = $prev;
787 6
            $prev = ' after '.$this->escape($cname);
788
        }
789 6
        return $orders;
790
    }
791
792
    /**
793
     * Force a value into the appropriate php type based on its SQL type.
794
     *
795
     * @param mixed $value The value to force.
796
     * @param string $type The sqlite type name.
797
     * @return mixed Returns $value cast to the appropriate type.
798
     */
799 4
    protected function forceType($value, $type) {
800 4
        $type = strtolower($type);
801
802 4
        if ($type === 'null') {
803
            return null;
804 4
        } elseif ($type === 'boolean') {
805 1
            return filter_var($value, FILTER_VALIDATE_BOOLEAN);
806 3
        } elseif (in_array($type, ['int', 'integer', 'tinyint', 'smallint',
807
            'mediumint', 'bigint', 'unsigned big int', 'int2', 'int8', 'boolean'])) {
808 3
            return filter_var($value, FILTER_VALIDATE_INT);
809
        } elseif (in_array($type, ['real', 'double', 'double precision', 'float',
810
            'numeric', 'number', 'decimal(10,5)'])) {
811
            return filter_var($value, FILTER_VALIDATE_FLOAT);
812
        } else {
813
            return (string)$value;
814
        }
815
    }
816
817 45
    public function quote($value, string $column = ''): string {
818 45
        if (is_bool($value)) {
819 1
            return (string)(int)$value;
820 45
        } elseif ($value instanceof \DateTimeInterface) {
821 2
            $value = $value->format(self::MYSQL_DATE_FORMAT);
822 45
        } elseif ($value === null) {
823 2
            return 'null';
824
        }
825
826 44
        return parent::quote($value, $column);
827
    }
828
829
    /**
830
     * Convert a value into something usable as a PDO parameter.
831
     *
832
     * @param mixed $value The value to convert.
833
     * @return mixed Returns the converted value or the value itself if it's fine.
834
     */
835 44
    private function argValue($value) {
836 44
        if (is_bool($value)) {
837
            return (int)$value;
838 44
        } elseif ($value instanceof \DateTimeInterface) {
839
            return $value->format(self::MYSQL_DATE_FORMAT);
840
        } else {
841 44
            return $value;
842
        }
843
    }
844
}
845