BaseAdapter::generateSelectSQL()   A
last analyzed

Complexity

Conditions 3
Paths 1

Size

Total Lines 16
Code Lines 13

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
cc 3
eloc 13
nc 1
nop 11
dl 0
loc 16
rs 9.8333
c 0
b 0
f 0

How to fix   Many Parameters   

Many Parameters

Methods with many parameters are not only hard to understand, but their parameters also often become inconsistent when you need more, or different data.

There are several approaches to avoid long parameter lists:

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