Completed
Push — master ( 10f953...ab5b42 )
by Todd
9s
created

MySqlDb::indexDefString()   B

Complexity

Conditions 5
Paths 5

Size

Total Lines 15
Code Lines 12

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 8
CRAP Score 5.0342

Importance

Changes 0
Metric Value
dl 0
loc 15
ccs 8
cts 9
cp 0.8889
rs 8.8571
c 0
b 0
f 0
cc 5
eloc 12
nc 5
nop 2
crap 5.0342
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 28
                        switch ($vop) {
235
                            case Db::OP_AND:
236
                            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
                            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
                            case Db::OP_GT:
256
                            case Db::OP_GTE:
257
                            case Db::OP_LT:
258
                            case Db::OP_LTE:
259 12
                                $result .= "$btcolumn {$map[$vop]} ".$this->quote($rval);
260 12
                                break;
261
                            case Db::OP_LIKE:
262 2
                                $result .= $this->buildLike($btcolumn, $rval);
263 2
                                break;
264
                            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
                            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 81
        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);
0 ignored issues
show
Unused Code Comprehensibility introduced by
67% of this comment could be valid code. Did you maybe forget this after debugging?

Sometimes obsolete code just ends up commented out instead of removed. In this case it is better to remove the code once you have checked you do not need it.

The code might also have been commented out for debugging purposes. In this case it is vital that someone uncomments it again or your project may behave in very unexpected ways in production.

This check looks for comments that seem to be mostly valid code and reports them.

Loading history...
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
            case Db::INDEX_IX:
723 11
                return "index $indexName ".$this->bracketList($def['columns'], '`');
724
            case Db::INDEX_UNIQUE:
725 1
                return "unique $indexName ".$this->bracketList($def['columns'], '`');
726
            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