BaseAdapter::sqlWhere()   A
last analyzed

Complexity

Conditions 2
Paths 2

Size

Total Lines 7
Code Lines 3

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
cc 2
eloc 3
nc 2
nop 1
dl 0
loc 7
rs 10
c 0
b 0
f 0
1
<?php
2
namespace Tsukasa\QueryBuilder;
3
4
use Doctrine\DBAL\Connection;
5
use Tsukasa\QueryBuilder\Aggregation\Aggregation;
6
use Tsukasa\QueryBuilder\Exception\QBException;
7
use Tsukasa\QueryBuilder\Expression\Expression;
8
use Tsukasa\QueryBuilder\Interfaces\ILookupCollection;
9
use Tsukasa\QueryBuilder\Interfaces\ISQLGenerator;
10
use Tsukasa\QueryBuilder\Interfaces\IToSql;
11
use Tsukasa\QueryBuilder\Interfaces\QueryBuilderInterface;
12
13
abstract class BaseAdapter implements ISQLGenerator
14
{
15
    /**
16
     * @var string
17
     */
18
    protected $tablePrefix;
19
    /**
20
     * @var null|Connection
21
     */
22
    protected $driver;
23
24
    public function __construct($driver = null)
25
    {
26
        $this->driver = $driver;
27
    }
28
29
    /**
30
     * @return string
31
     */
32
    public function getTablePrefix()
33
    {
34
        return $this->tablePrefix;
35
    }
36
37
    /**
38
     * @return BaseLookupCollection|ILookupCollection
39
     */
40
    abstract public function getLookupCollection();
41
42
    /**
43
     * Quotes a column name for use in a query.
44
     * If the column name contains prefix, the prefix will also be properly quoted.
45
     * If the column name is already quoted or contains '(', '[[' or '{{', then this method will do nothing.
46
     *
47
     * @param string $name column name
48
     * @return string the properly quoted column name
49
     * @see quoteSimpleColumnName()
50
     */
51
    public function quoteColumn($name)
52
    {
53
        if (strpos($name, '(') !== false || strpos($name, '[[') !== false || strpos($name, '{{') !== false) {
54
            return $name;
55
        }
56
        if (($pos = strrpos($name, '.')) !== false) {
57
            $prefix = $this->quoteTableName(substr($name, 0, $pos)) . '.';
58
            $name = substr($name, $pos + 1);
59
        }
60
        else {
61
            $prefix = '';
62
        }
63
        return $prefix . $this->quoteSimpleColumnName($name);
64
    }
65
66
    /**
67
     * Quotes a simple column name for use in a query.
68
     * A simple column name should contain the column name only without any prefix.
69
     * If the column name is already quoted or is the asterisk character '*', this method will do nothing.
70
     * @param string $name column name
71
     * @return string the properly quoted column name
72
     */
73
    public function quoteSimpleColumnName($name)
74
    {
75
        return strpos($name, '"') !== false || $name === '*' ? $name : '"' . $name . '"';
76
    }
77
78
    /**
79
     * Returns the actual name of a given table name.
80
     * This method will strip off curly brackets from the given table name
81
     * and replace the percentage character '%' with [[Connection::tablePrefix]].
82
     * @param string $name the table name to be converted
83
     * @return string the real name of the given table name
84
     */
85
    public function getRawTableName($name)
86
    {
87
        if (strpos($name, '{{') !== false) {
88
            $name = preg_replace('/\\{\\{(.*?)\\}\\}/', '\1', $name);
89
90
            if (strpos($name, '%') !== false) {
91
                return str_replace('%', $this->getTablePrefix() ?: '', $name);
92
            }
93
        }
94
95
        return $name;
96
    }
97
98
    /**
99
     * @return null|Connection
100
     */
101
    public function getDriver()
102
    {
103
        return $this->driver;
104
    }
105
106
    /**
107
     * @param Connection $driver
108
     * @return ISQLGenerator
109
     */
110
    public function setDriver(Connection $driver)
111
    {
112
        $this->driver = $driver;
113
        return $this;
114
    }
115
116
    /**
117
     * Quotes a string value for use in a query.
118
     * Note that if the parameter is not a string, it will be returned without change.
119
     *
120
     * Note sqlite3:
121
     * A string constant is formed by enclosing the string in single quotes (').
122
     * A single quote within the string can be encoded by putting two single
123
     * quotes in a row - as in Pascal. C-style escapes using the backslash
124
     * character are not supported because they are not standard SQL.
125
     *
126
     * @param string $value string to be quoted
127
     * @return string the properly quoted string
128
     * @see http://www.php.net/manual/en/function.PDO-quote.php
129
     */
130
    public function quoteValue($value)
131
    {
132
        if ($value instanceof IToSql) {
0 ignored issues
show
introduced by
$value is never a sub-type of Tsukasa\QueryBuilder\Interfaces\IToSql.
Loading history...
133
            return $value->toSql();
134
        }
135
        else if ($value === true || strtolower($value) === 'true') {
136
            return 'TRUE';
137
        }
138
        else if ($value === false || strtolower($value) === 'false') {
139
            return 'FALSE';
140
        }
141
        else if ($value === null || strtolower($value) === 'null') {
142
            return 'NULL';
143
        }
144
        else if (is_string($value) && $driver = $this->getDriver()) {
145
            return $driver->quote($value);
146
        }
147
148
        return $value;
149
    }
150
151
    /**
152
     * Quotes a table name for use in a query.
153
     * If the table name contains schema prefix, the prefix will also be properly quoted.
154
     * If the table name is already quoted or contains '(' or '{{',
155
     * then this method will do nothing.
156
     * @param string $name table name
157
     * @return string the properly quoted table name
158
     * @see quoteSimpleTableName()
159
     */
160
    public function quoteTableName($name)
161
    {
162
        if (strpos($name, '(') !== false || strpos($name, '{{') !== false) {
163
            return $name;
164
        }
165
        if (strpos($name, '.') === false) {
166
            return $this->quoteSimpleTableName($name);
167
        }
168
        $parts = explode('.', $name);
169
        foreach ($parts as $i => $part) {
170
            $parts[$i] = $this->quoteSimpleTableName($part);
171
        }
172
        return implode('.', $parts);
173
    }
174
175
    /**
176
     * Quotes a simple table name for use in a query.
177
     * A simple table name should contain the table name only without any schema prefix.
178
     * If the table name is already quoted, this method will do nothing.
179
     * @param string $name table name
180
     * @return string the properly quoted table name
181
     */
182
    public function quoteSimpleTableName($name)
183
    {
184
        return strpos($name, "'") !== false ? $name : "'" . $name . "'";
185
    }
186
187
    /**
188
     * @param $sql
189
     * @return mixed
190
     *
191
     */
192
    public function quoteSql($sql)
193
    {
194
//        $tablePrefix = $this->tablePrefix;
195
//
196
//        if (preg_match('/\\{\\{(%?[\w\-\. ]+%?)\\}\\}|\\[\\[([\w\-\. ]+)\\]\\]|\\[\\[([\w\-\. ]+)\\]\\][\s]*=[\s]*\\@([\w\-\. \/\%\:]+)\\@/', $sql))
197
//        {
198
//            return preg_replace_callback('/(\\{\\{(%?[\w\-\. ]+%?)\\}\\}|\\[\\[([\w\-\. ]+)\\]\\])|\\@([\w\-\. \/\%\:]+)\\@/',
199
//                function ($matches) use ($tablePrefix) {
200
//                    if (isset($matches[4])) {
201
//                        return $this->quoteValue($this->convertToDbValue($matches[4]));
202
//                    } else if (isset($matches[3])) {
203
//                        return $this->quoteColumn($matches[3]);
204
//                    } else {
205
//                        return str_replace('%', $tablePrefix, $this->quoteTableName($matches[2]));
206
//                    }
207
//                }, $sql);
208
//        }
209
210
        return $sql;
211
    }
212
213
    public function convertToDbValue($rawValue)
214
    {
215
        if ($rawValue === true || $rawValue === false || $rawValue === 'true' || $rawValue === 'false') {
216
            return $this->getBoolean($rawValue);
217
        }
218
219
        if ($rawValue === 'null' || $rawValue === null) {
220
            return 'NULL';
221
        }
222
223
        return $rawValue;
224
    }
225
226
    /**
227
     * Checks to see if the given limit is effective.
228
     * @param mixed $limit the given limit
229
     * @return boolean whether the limit is effective
230
     */
231
    public function hasLimit($limit)
232
    {
233
        return (int)$limit > 0;
234
    }
235
236
    /**
237
     * Checks to see if the given offset is effective.
238
     * @param mixed $offset the given offset
239
     * @return boolean whether the offset is effective
240
     */
241
    public function hasOffset($offset)
242
    {
243
        return (int)$offset > 0;
244
    }
245
246
    /**
247
     * @param integer $limit
248
     * @param integer $offset
249
     * @return string the LIMIT and OFFSET clauses
250
     */
251
    abstract public function sqlLimitOffset($limit = null, $offset = null);
252
253
    /**
254
     * @param $columns
255
     * @return string
256
     */
257
    public function buildColumns($columns)
258
    {
259
        if (!is_array($columns)) {
260
            if ($columns instanceof Aggregation) {
261
                $columns->setFieldSql($this->buildColumns($columns->getField()));
262
                return $columns->toSQL();
263
            }
264
265
            if (strpos($columns, '(') !== false) {
266
                return $columns;
267
            }
268
269
            $columns = preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY);
270
            if ($columns === false) {
271
                return '';
272
            }
273
        }
274
        foreach ($columns as $i => $column) {
275
            if ($column instanceof Expression) {
276
                $columns[$i] = $column->toSQL();
277
            }
278
            else if (strpos($column, '(') === false) {
279
                $columns[$i] = $this->quoteColumn($column);
280
            }
281
        }
282
283
        if (is_array($columns)) {
284
            return implode(', ', $columns);
285
        }
286
287
        return $columns;
288
    }
289
290
    /**
291
     * Builds a SQL statement for adding a primary key constraint to an existing table.
292
     * @param string $name the name of the primary key constraint.
293
     * @param string $tableName the table that the primary key constraint will be added to.
294
     * @param string|array $columns comma separated string or array of columns that the primary key will consist of.
295
     * @return string the SQL statement for adding a primary key constraint to an existing table.
296
     */
297
    public function sqlAddPrimaryKey($tableName, $name, $columns)
298
    {
299
        if (is_string($columns)) {
300
            $columns = [
301
                preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY)
302
            ];
303
        }
304
        foreach ($columns as $i => $col) {
305
            $columns[$i] = $this->quoteColumn($col);
306
        }
307
        return 'ALTER TABLE ' . $this->quoteTableName($tableName) . ' ADD CONSTRAINT '
308
        . $this->quoteColumn($name) . ' PRIMARY KEY (' . implode(', ', $columns) . ')';
309
    }
310
311
    /**
312
     * Builds a SQL statement for removing a primary key constraint to an existing table.
313
     * @param string $name the name of the primary key constraint to be removed.
314
     * @param string $tableName the table that the primary key constraint will be removed from.
315
     * @return string the SQL statement for removing a primary key constraint from an existing table.
316
     */
317
    public function sqlDropPrimaryKey($tableName, $name)
318
    {
319
        return 'ALTER TABLE ' . $this->quoteTableName($tableName) . ' DROP PRIMARY KEY ' . $this->quoteColumn($name);
320
    }
321
322
    public function sqlAlterColumn($tableName, $column, $type)
323
    {
324
        return 'ALTER TABLE ' . $this->quoteTableName($tableName) . ' CHANGE '
325
        . $this->quoteColumn($column) . ' '
326
        . $this->quoteColumn($column) . ' '
327
        . $type;
328
    }
329
330
    /**
331
     * @param $tableName
332
     * @param array $rows
333
     * @param string $options Sql Options
334
     * @return string
335
     */
336
    public function sqlInsert($tableName, array $rows, $options = '')
337
    {
338
        if (!is_string($options)) {
0 ignored issues
show
introduced by
The condition is_string($options) is always true.
Loading history...
339
            $options = '';
340
        }
341
342
        if ($options) {
343
            $options = " {$options} ";
344
        }
345
346
        if (is_array($rows) && isset($rows[0])) {
347
            $values = [];
348
            $columns = array_map([$this, 'quoteColumn'], array_keys($rows[0]));
349
350
            foreach ($rows as $row) {
351
                $record = [];
352
                foreach ($row as $value) {
353
                    $record[] = $value = $this->quoteValue($value);
0 ignored issues
show
Unused Code introduced by
The assignment to $value is dead and can be removed.
Loading history...
354
                }
355
                $values[] = '(' . implode(', ', $record) . ')';
356
            }
357
358
            $sql = 'INSERT' . $options . ' INTO ' . $this->quoteTableName($tableName) . ' (' . implode(', ', $columns) . ') VALUES ' . implode(', ', $values);
359
360
            return $this->quoteSql($sql);
361
        }
362
363
        $values = array_map([$this, 'quoteValue'], $rows);
364
        $columns = array_map([$this, 'quoteColumn'], array_keys($rows));
365
366
        $sql = 'INSERT' . $options . ' INTO ' . $this->quoteTableName($tableName) . ' (' . implode(', ', $columns) . ') VALUES (' . implode(', ', $values) . ')';
367
368
        return $this->quoteSql($sql);
369
    }
370
371
    public function sqlUpdate($tableName, array $columns, $options = '')
372
    {
373
        $tableName = $this->getRawTableName($tableName);
374
        $parts = [];
375
        foreach ($columns as $column => $value) {
376
            $parts[] = $this->quoteColumn($column) . '=' . $this->quoteValue($value);
377
        }
378
        if ($options) {
379
            $options = " {$options} ";
380
        }
381
382
        return 'UPDATE ' . $options . $this->quoteTableName($tableName) . ' SET ' . implode(', ', $parts);
383
    }
384
385
    /**
386
     * @param $select
387
     * @param $from
388
     * @param $where
389
     * @param $order
390
     * @param $group
391
     * @param $limit
392
     * @param $offset
393
     * @param $join
394
     * @param $having
395
     * @param $union
396
     * @param string $options
397
     * @return string
398
     * @throws \Exception
399
     */
400
    public function generateSelectSQL($select, $from, $where, $order, $group, $limit, $offset, $join, $having, $union, $options = '')
401
    {
402
        $where = $this->sqlWhere($where);
403
        $orderSql = $this->sqlOrderBy($order);
404
        $unionSql = $this->sqlUnion($union);
405
406
        return strtr('{select}{from}{join}{where}{group}{having}{order}{limit_offset}{union}', [
407
            '{select}' => $this->sqlSelect($select, $options),
408
            '{from}' => $this->sqlFrom($from),
409
            '{where}' => $where,
410
            '{group}' => $this->sqlGroupBy($group),
411
            '{order}' => empty($union) ? $orderSql : '',
412
            '{having}' => $this->sqlHaving($having),
413
            '{join}' => $join,
414
            '{limit_offset}' => $this->sqlLimitOffset($limit, $offset),
415
            '{union}' => empty($union) ? '' : $unionSql . $orderSql
416
        ]);
417
    }
418
419
    public function sqlCreateTable($tableName, $columns, $options = null, $ifNotExists = false)
420
    {
421
        $tableName = $this->getRawTableName($tableName);
422
        if (is_array($columns)) {
423
            $cols = [];
424
            foreach ($columns as $name => $type) {
425
                if (is_string($name)) {
426
                    $cols[] = "\t" . $this->quoteColumn($name) . ' ' . $type;
427
                }
428
                else {
429
                    $cols[] = "\t" . $type;
430
                }
431
            }
432
            $sql = ($ifNotExists ? "CREATE TABLE IF NOT EXISTS " : "CREATE TABLE ") . $this->quoteTableName($tableName) . " (\n" . implode(",\n", $cols) . "\n)";
433
        }
434
        else {
435
            $sql = ($ifNotExists ? "CREATE TABLE IF NOT EXISTS " : "CREATE TABLE ") . $this->quoteTableName($tableName) . " " . $this->quoteSql($columns);
436
        }
437
        return empty($options) ? $sql : $sql . ' ' . $options;
438
    }
439
440
    /**
441
     * @param $oldTableName
442
     * @param $newTableName
443
     * @return string
444
     */
445
    abstract public function sqlRenameTable($oldTableName, $newTableName);
446
447
    /**
448
     * @param $tableName
449
     * @param bool $ifExists
450
     * @param bool $cascade
451
     * @return string
452
     */
453
    public function sqlDropTable($tableName, $ifExists = false, $cascade = false)
454
    {
455
        $tableName = $this->getRawTableName($tableName);
456
        return ($ifExists ? "DROP TABLE IF EXISTS " : "DROP TABLE ") . $this->quoteTableName($tableName);
457
    }
458
459
    /**
460
     * @param $tableName
461
     * @param bool $cascade
462
     * @return string
463
     */
464
    public function sqlTruncateTable($tableName, $cascade = false)
465
    {
466
        return "TRUNCATE TABLE " . $this->quoteTableName($tableName);
467
    }
468
469
    /**
470
     * @param $tableName
471
     * @param $name
472
     * @return string
473
     */
474
    abstract public function sqlDropIndex($tableName, $name);
475
476
    /**
477
     * @param $value
478
     * @return string
479
     */
480
    public function getSqlType($value)
481
    {
482
        if ($value === 'true' || $value === true) {
483
            return 'TRUE';
484
        }
485
        else if ($value === null || $value === 'null') {
486
            return 'NULL';
487
        }
488
        else if ($value === false || $value === 'false') {
489
            return 'FALSE';
490
        }
491
        else {
492
            return $value;
493
        }
494
    }
495
496
    /**
497
     * @param $tableName
498
     * @param $column
499
     * @return string
500
     */
501
    public function sqlDropColumn($tableName, $column)
502
    {
503
        return 'ALTER TABLE ' . $this->quoteTableName($tableName) . ' DROP COLUMN ' . $this->quoteColumn($column);
504
    }
505
506
    /**
507
     * @param $tableName
508
     * @param $oldName
509
     * @param $newName
510
     * @return mixed
511
     */
512
    abstract public function sqlRenameColumn($tableName, $oldName, $newName);
513
514
    /**
515
     * @param $tableName
516
     * @param $name
517
     * @return mixed
518
     */
519
    abstract public function sqlDropForeignKey($tableName, $name);
520
521
    public function sqlAddForeignKey($tableName, $name, $columns, $refTable, $refColumns, $delete = null, $update = null)
522
    {
523
        $sql = 'ALTER TABLE ' . $this->quoteTableName($tableName)
524
            . ' ADD CONSTRAINT ' . $this->quoteColumn($name)
525
            . ' FOREIGN KEY (' . $this->buildColumns($columns) . ')'
526
            . ' REFERENCES ' . $this->quoteTableName($refTable)
527
            . ' (' . $this->buildColumns($refColumns) . ')';
528
        if ($delete !== null) {
529
            $sql .= ' ON DELETE ' . $delete;
530
        }
531
        if ($update !== null) {
532
            $sql .= ' ON UPDATE ' . $update;
533
        }
534
        return $sql;
535
    }
536
537
    /**
538
     * @return string
539
     */
540
    abstract public function getRandomOrder();
541
542
    /**
543
     * @param $value
544
     * @return string
545
     */
546
    abstract public function getBoolean($value = null);
547
548
    /**
549
     * @param null $value
0 ignored issues
show
Documentation Bug introduced by
Are you sure the doc-type for parameter $value is correct as it would always require null to be passed?
Loading history...
550
     * @return string
551
     */
552
    abstract public function getDateTime($value = null);
553
554
    /**
555
     * @param null $value
0 ignored issues
show
Documentation Bug introduced by
Are you sure the doc-type for parameter $value is correct as it would always require null to be passed?
Loading history...
556
     * @return string
557
     */
558
    abstract public function getDate($value = null);
559
560
    /**
561
     * @param null $value
0 ignored issues
show
Documentation Bug introduced by
Are you sure the doc-type for parameter $value is correct as it would always require null to be passed?
Loading history...
562
     * @return mixed
563
     */
564
    public function getTimestamp($value = null)
565
    {
566
        return $value instanceof \DateTime ? $value->getTimestamp() : strtotime($value);
567
    }
568
569
    /**
570
     * @param $tableName
571
     * @param $column
572
     * @param $type
573
     * @return string
574
     */
575
    abstract public function sqlAddColumn($tableName, $column, $type);
576
577
    /**
578
     * @param $tableName
579
     * @param $name
580
     * @param array $columns
581
     * @param bool $unique
582
     * @return string
583
     */
584
    public function sqlCreateIndex($tableName, $name, array $columns, $unique = false)
585
    {
586
        return ($unique ? 'CREATE UNIQUE INDEX ' : 'CREATE INDEX ')
587
        . $this->quoteTableName($name) . ' ON '
588
        . $this->quoteTableName($tableName)
589
        . ' (' . $this->buildColumns($columns) . ')';
590
    }
591
592
    /**
593
     * @param $tables
594
     * @return string
595
     */
596
    public function sqlFrom($tables)
597
    {
598
        if (empty($tables)) {
599
            return '';
600
        }
601
602
        if (!is_array($tables)) {
603
            $tables = (array)$tables;
604
        }
605
        $quotedTableNames = [];
606
        foreach ($tables as $tableAlias => $table) {
607
            if ($table instanceof QueryBuilder) {
608
                $tableRaw = $table->toSQL();
609
            }
610
            else {
611
                $tableRaw = $this->getRawTableName($table);
612
            }
613
            if (strpos($tableRaw, 'SELECT') !== false) {
614
                $quotedTableNames[] = '(' . $tableRaw . ')' . (is_numeric($tableAlias) ? '' : ' AS ' . $this->quoteTableName($tableAlias));
615
            }
616
            else {
617
                $quotedTableNames[] = $this->quoteTableName($tableRaw) . (is_numeric($tableAlias) ? '' : ' AS ' . $this->quoteTableName($tableAlias));
618
            }
619
        }
620
621
        return implode(', ', $quotedTableNames);
622
    }
623
624
    /**
625
     * @param $joinType string
626
     * @param $tableName string
627
     * @param $on string|array
628
     * @param $alias string
629
     * @return string
630
     */
631
    public function sqlJoin($joinType, $tableName, $on = [], $alias = null, $index = null)
632
    {
633
        $toSql = [$joinType];
634
        if (is_string($tableName) && $tableName = $this->getRawTableName($tableName)) {
635
            if (strpos($tableName, 'SELECT') !== false) {
636
                $toSql[] = '(' . $this->quoteSql($tableName) . ')';
637
            }
638
            else {
639
                $toSql[] = $this->quoteTableName($tableName);
640
            }
641
        }
642
        else if ($tableName instanceof QueryBuilder) {
643
            $toSql[] = '(' . $this->quoteSql($tableName->toSQL()) . ')';
644
        }
645
        else {
646
            throw new QBException('Incorrect table name');
647
        }
648
649
        if ($alias) {
650
            $toSql[] = 'AS ' . $this->quoteColumn($alias);
651
        }
652
653
        if ($on) {
654
            $onSQL = [];
655
            if (is_string($on)) {
656
                $onSQL[] = $this->quoteSql($on);
657
            }
658
            else {
659
                foreach ($on as $leftColumn => $rightColumn) {
660
                    if ($rightColumn instanceof Expression) {
661
                        $onSQL[] = $this->quoteColumn($leftColumn) . '=' . $this->quoteSql($rightColumn->toSQL());
662
                    }
663
                    else {
664
                        $onSQL[] = $this->quoteColumn($leftColumn) . '=' . $this->quoteColumn($rightColumn);
665
                    }
666
                }
667
            }
668
669
            $toSql[] = 'ON ' . implode(' and ', $onSQL);
670
        }
671
672
        return implode(' ', $toSql);
673
    }
674
675
    /**
676
     * @param $where string|array
677
     * @return string
678
     */
679
    public function sqlWhere($where)
680
    {
681
        if (empty($where)) {
682
            return '';
683
        }
684
685
        return ' WHERE ' . $this->quoteSql($where);
686
    }
687
688
    /**
689
     * @param $having
690
     * @return string
691
     */
692
    public function sqlHaving($having)
693
    {
694
        if (empty($having)) {
695
            return '';
696
        }
697
698
        if ($having instanceof IToSql) {
699
            $sql = $having
700
                ->toSql();
701
        }
702
        else {
703
            $sql = $this->quoteSql($having);
704
        }
705
706
        return empty($sql) ? '' : ' HAVING ' . $sql;
707
    }
708
709
    /**
710
     * @param QueryBuilderInterface|string $union
711
     * @param bool $all
712
     * @return string
713
     */
714
    public function sqlUnion($union, $all = false)
715
    {
716
        if (empty($union)) {
717
            return '';
718
        }
719
720
        if ($union instanceof QueryBuilderInterface) {
721
            $unionSQL = $union->setOrder(null)->toSQL();
722
        }
723
        else {
724
            $unionSQL = $this->quoteSql($union);
725
        }
726
727
        $sql = 'UNION ';
728
729
        if ($all) {
730
            $sql .= 'ALL ';
731
        }
732
733
        return $sql . '(' . $unionSQL . ')';
734
    }
735
736
    /**
737
     * @param $tableName
738
     * @param $sequenceName
739
     * @return string
740
     */
741
    abstract public function sqlResetSequence($tableName, $sequenceName);
742
743
    /**
744
     * @param bool $check
745
     * @param string $schema
746
     * @param string $table
747
     * @return string
748
     */
749
    abstract public function sqlCheckIntegrity($check = true, $schema = '', $table = '');
750
751
    /**
752
     * @param $columns
753
     * @return string
754
     */
755
    public function sqlGroupBy($columns)
756
    {
757
        if (empty($columns)) {
758
            return '';
759
        }
760
761
        if (is_string($columns)) {
762
            $columns = preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY);
763
764
            if ($columns) {
765
                $quotedColumns = array_map([$this, 'quoteColumn'], (array)$columns);
766
                return implode(', ', $quotedColumns);
767
            }
768
769
            return '';
770
        }
771
772
        $group = [];
773
        foreach ($columns as $column) {
774
            $group[] = $this->quoteColumn($column);
775
        }
776
777
        return implode(', ', $group);
778
    }
779
780
    /**
781
     * @param array $columns
782
     * @param null $options
0 ignored issues
show
Documentation Bug introduced by
Are you sure the doc-type for parameter $options is correct as it would always require null to be passed?
Loading history...
783
     * @return string
784
     */
785
    public function sqlOrderBy(array $columns, $options = null)
786
    {
787
        if (empty($columns)) {
788
            return '';
789
        }
790
791
        $order = [];
792
        foreach ($columns as $column => $direction) {
793
794
            $order[] = $this->quoteColumn($column) . ' ' . $direction;
795
        }
796
797
        return implode(', ', $order) . (empty($options) ? '' : ' ' . $options);
798
    }
799
800
    /**
801
     * @param array|null|string $columns
802
     * @param string $options
803
     *
804
     * @return string
805
     */
806
    public function sqlSelect($columns, $options = '')
807
    {
808
        $selectSql = 'SELECT ';
809
810
        if ($options) {
811
            $selectSql .= $options . ' ';
812
        }
813
814
        if (empty($columns)) {
815
            return $selectSql . '*';
816
        }
817
818
        if (is_array($columns) === false) {
819
            $columns = [$columns];
820
        }
821
822
        $select = [];
823
        foreach ($columns as $column => $expr) {
824
            if ($expr instanceof IToSql) {
825
                $value = $this->quoteColumn($expr->toSql());
826
827
                if (!is_numeric($column)) {
828
                    $value .= ' AS ' . $this->quoteColumn($column);
829
                }
830
            }
831
            else {
832
                $subQuery = (string)$this->quoteSql($expr);
833
834
                if (is_numeric($column)) {
835
                    $column = $subQuery;
836
                    $subQuery = '';
837
                }
838
839
                if (!empty($subQuery)) {
840
                    if (strpos($subQuery, 'SELECT') !== false) {
841
                        $value = '(' . $subQuery . ') AS ' . $this->quoteColumn($column);
842
                    }
843
                    else {
844
                        $value = $this->quoteColumn($subQuery) . ' AS ' . $this->quoteColumn($column);
845
                    }
846
                }
847
                else if (strpos($column, ',') === false && strpos($column, 'AS') !== false) {
848
849
                    list($rawColumn, $rawAlias) = explode('AS', $column);
850
                    $value = $this->quoteColumn(trim($rawColumn));
851
852
                    if (!empty($rawAlias)) {
853
                        $value .= ' AS ' . $this->quoteColumn(trim($rawAlias));
854
                    }
855
                }
856
                else if (strpos($column, ',') !== false) {
857
                    $newSelect = [];
858
859
                    foreach (explode(',', $column) as $item) {
860
                        $rawColumn = $item;
861
                        $rawAlias = '';
862
863
                        if (strpos($item, 'AS') !== false) {
864
                            list($rawColumn, $rawAlias) = explode('AS', $item);
865
                        }
866
867
                        $_v = $this->quoteColumn(trim($rawColumn));
868
869
                        if (!empty($rawAlias)) {
870
                            $_v .= ' AS ' . $this->quoteColumn(trim($rawAlias));
871
                        }
872
873
                        $newSelect[] = $_v;
874
                    }
875
                    $value = implode(', ', $newSelect);
876
877
                }
878
                else {
879
                    $value = $this->quoteColumn($column);
880
                }
881
            }
882
883
            $select[] = $value;
884
        }
885
886
        return $selectSql . implode(', ', $select);
887
    }
888
889
    /**
890
     * Prepare value for db
891
     * @param $value
892
     * @return int
893
     */
894
    public function prepareValue($value)
895
    {
896
        return $value;
897
    }
898
}
899