db

1
<?php
2

3
namespace Upside\Db\SQL;
4

5
class Compiler
6
{
7
    /** Date format. */
8
    protected string $date_format = 'Y-m-d H:i:s';
9

10
    /** Wrapper used to escape table and column names. */
11
    protected string $wrapper = '"%s"';
12

13
    /** Query params */
14
    protected array $params = [];
15

16
    /**
17
     * Returns the SQL for a select statement
18
     */
19
    public function select(SQLStatement $select): string
20
    {
21
        $sql = $select->get_distinct() ? 'SELECT DISTINCT ' : 'SELECT ';
22
        $sql .= $this->handle_columns($select->get_columns());
23
        $sql .= $this->handle_into($select->get_into_table(), $select->get_into_database());
24
        $sql .= ' FROM ';
25
        $sql .= $this->handle_tables($select->get_tables());
26
        $sql .= $this->handle_joins($select->get_joins());
27
        $sql .= $this->handle_wheres($select->get_wheres());
28
        $sql .= $this->handle_groupings($select->get_group_by());
29
        $sql .= $this->handle_orderings($select->get_order());
30
        $sql .= $this->handle_havings($select->get_having());
31
        $sql .= $this->handle_limit($select->get_limit());
32
        $sql .= $this->handle_offset($select->get_offset());
33

34
        return $sql;
35
    }
36

37
    /**
38
     * Returns the SQL for an insert statement
39
     */
40
    public function insert(SQLStatement $insert): string
41
    {
42
        $columns = $this->handle_columns($insert->get_columns());
43

44
        $sql = 'INSERT INTO ';
45
        $sql .= $this->handle_tables($insert->get_tables());
46
        $sql .= ($columns === '*') ? '' : ' (' . $columns . ')';
47
        $sql .= $this->handle_insert_values($insert->get_values());
48

49
        return $sql;
50
    }
51

52

53
    /**
54
     * Returns the SQL for an update statement
55
     */
56
    public function update(SQLStatement $update): string
57
    {
58
        $sql = 'UPDATE ';
59
        $sql .= $this->handle_tables($update->get_tables());
60
        $sql .= $this->handle_joins($update->get_joins());
61
        $sql .= $this->handle_set_columns($update->get_columns());
62
        $sql .= $this->handle_wheres($update->get_wheres());
63

64
        return $sql;
65
    }
66

67
    /**
68
     * Returns the SQL for a delete statement
69
     */
70
    public function delete(SQLStatement $delete): string
71
    {
72
        $sql = 'DELETE ' . $this->handle_tables($delete->get_tables());
73
        $sql .= $sql === 'DELETE ' ? 'FROM ' : ' FROM ';
74
        $sql .= $this->handle_tables($delete->get_from());
75
        $sql .= $this->handle_joins($delete->get_joins());
76
        $sql .= $this->handle_wheres($delete->get_wheres());
77

78
        return $sql;
79
    }
80

81
    /**
82
     * Returns the data format used
83
     */
84
    public function get_date_format(): string
85
    {
86
        return $this->date_format;
87
    }
88

89
    /**
90
     * Sets compiler options
91
     */
92
    public function set_options(array $options): void
93
    {
94
        foreach ($options as $name => $value) {
95
            $this->{$name} = $value;
96
        }
97
    }
98

99
    /**
100
     * Stores an array of params
101
     */
102
    public function params(array $params): string
103
    {
104
        return \implode(', ', \array_map([$this, 'param'], $params));
105
    }
106

107
    /**
108
     * Add an array of columns
109
     */
110
    public function columns(array $columns): string
111
    {
112
        return \implode(', ', \array_map([$this, 'wrap'], $columns));
113
    }
114

115
    public function quote(string $value): string
116
    {
117
        return "'" . \str_replace("'", "''", $value) . "'";
118
    }
119

120
    /**
121
     * Return the stored params
122
     */
123
    public function get_params(): array
124
    {
125
        $params = $this->params;
126
        $this->params = [];
127

128
        return $params;
129
    }
130

131
    /**
132
     * Wrap a value
133
     */
134
    protected function wrap(mixed $value): string
135
    {
136
        if ($value instanceof Expression) {
137
            return $this->handle_expressions($value->get_expressions());
138
        }
139

140
        $wrapped = [];
141

142
        foreach (\explode('.', $value) as $segment) {
143
            if ($segment === '*') {
144
                $wrapped[] = $segment;
145
            } else {
146
                $wrapped[] = \sprintf($this->wrapper, $segment);
147
            }
148
        }
149

150
        return \implode('.', $wrapped);
151
    }
152

153
    /**
154
     * Stores a query param
155
     */
156
    protected function param(mixed $value): string
157
    {
158
        if ($value instanceof Expression) {
159
            return $this->handle_expressions($value->get_expressions());
160
        }
161

162
        if ($value instanceof \DateTimeInterface) {
163
            $this->params[] = $value->format($this->date_format);
164
        } else {
165
            $this->params[] = $value;
166
        }
167

168
        return '?';
169
    }
170

171
    /**
172
     * Handle all expressions
173
     */
174
    protected function handle_expressions(array $expressions): string
175
    {
176
        $sql = [];
177

178
        foreach ($expressions as $expr) {
179
            switch ($expr['type']) {
180
                case 'column':
181
                    $sql[] = $this->wrap($expr['value']);
182
                    break;
183
                case 'op':
184
                    $sql[] = $expr['value'];
185
                    break;
186
                case 'value':
187
                    $sql[] = $this->param($expr['value']);
188
                    break;
189
                case 'group':
190
                    /** @var Expression $expression */
191
                    $expression = $expr['value'];
192
                    $sql[] = '(' . $this->handle_expressions($expression->get_expressions()) . ')';
193
                    break;
194
                case 'function':
195
                    $sql[] = $this->handle_sql_function($expr['value']);
196
                    break;
197
                case 'subquery':
198
                    /** @var Subquery $subquery */
199
                    $subquery = $expr['value'];
200
                    $sql[] = '(' . $this->select($subquery->get_sql_statement()) . ')';
201
                    break;
202
            }
203
        }
204

205
        return \implode(' ', $sql);
206
    }
207

208
    /**
209
     * Handle SQL functions
210
     */
211
    protected function handle_sql_function(array $func): string
212
    {
213
        $method = $func['type'] . $func['name'];
214

215
        return $this->{$method}($func);
216
    }
217

218
    /**
219
     * Handle tables
220
     */
221
    protected function handle_tables(array $tables): string
222
    {
223
        if (empty($tables)) {
224
            return '';
225
        }
226

227
        $sql = [];
228

229
        foreach ($tables as $name => $alias) {
230
            if (\is_string($name)) {
231
                $sql[] = $this->wrap($name) . ' AS ' . $this->wrap($alias);
232
            } else {
233
                $sql[] = $this->wrap($alias);
234
            }
235
        }
236
        return \implode(', ', $sql);
237
    }
238

239
    /**
240
     * Handle columns
241
     */
242
    protected function handle_columns(array $columns): string
243
    {
244
        if (empty($columns)) {
245
            return '*';
246
        }
247

248
        $sql = [];
249

250
        foreach ($columns as $column) {
251
            if ($column['alias'] !== null) {
252
                $sql[] = $this->wrap($column['name']) . ' AS ' . $this->wrap($column['alias']);
253
            } else {
254
                $sql[] = $this->wrap($column['name']);
255
            }
256
        }
257

258
        return \implode(', ', $sql);
259
    }
260

261
    /**
262
     * Handle INTO
263
     */
264
    protected function handle_into($table, $database): string
265
    {
266
        if ($table === null) {
267
            return '';
268
        }
269
        return ' INTO ' . $this->wrap($table) . ($database === null ? '' : ' IN ' . $this->wrap($database));
270
    }
271

272
    /**
273
     * Handle WHERE conditions
274
     */
275
    protected function handle_wheres(array $wheres, bool $prefix = true): string
276
    {
277
        if (empty($wheres)) {
278
            return '';
279
        }
280

281
        $sql[] = $this->{$wheres[0]['type']}($wheres[0]);
282

283
        $count = \count($wheres);
284

285
        for ($i = 1; $i < $count; $i++) {
286
            $sql[] = $wheres[$i]['separator'] . ' ' . $this->{$wheres[$i]['type']}($wheres[$i]);
287
        }
288

289
        return ($prefix ? ' WHERE ' : '') . \implode(' ', $sql);
290
    }
291

292
    /**
293
     * Handle groups
294
     */
295
    protected function handle_groupings(array $grouping): string
296
    {
297
        return empty($grouping) ? '' : ' GROUP BY ' . $this->columns($grouping);
298
    }
299

300
    /**
301
     * Handle JOIN clauses
302
     */
303
    protected function handle_joins(array $joins): string
304
    {
305
        if (empty($joins)) {
306
            return '';
307
        }
308
        $sql = [];
309
        foreach ($joins as $join) {
310
            /** @var Join $joinObject */
311
            $joinObject = $join['join'];
312

313
            $on = '';
314
            if ($joinObject) {
315
                $on = $this->handle_join_conditions($joinObject->get_join_conditions());
316
            }
317
            if ($on !== '') {
318
                $on = ' ON ' . $on;
319
            }
320

321
            $sql[] = $join['type'] . ' JOIN ' . $this->handle_tables($join['table']) . $on;
322
        }
323

324
        return ' ' . \implode(' ', $sql);
325
    }
326

327
    /**
328
     * Handle JOIN conditions
329
     */
330
    protected function handle_join_conditions(array $conditions): string
331
    {
332
        if (empty($conditions)) {
333
            return '';
334
        }
335
        $sql[] = $this->{$conditions[0]['type']}($conditions[0]);
336
        $count = \count($conditions);
337
        for ($i = 1; $i < $count; $i++) {
338
            $sql[] = $conditions[$i]['separator'] . ' ' . $this->{$conditions[$i]['type']}($conditions[$i]);
339
        }
340

341
        return \implode(' ', $sql);
342
    }
343

344
    /**
345
     * Handle HAVING clause
346
     */
347
    protected function handle_havings(array $havings, bool $prefix = true): string
348
    {
349
        if (empty($havings)) {
350
            return '';
351
        }
352

353
        $sql[] = $this->{$havings[0]['type']}($havings[0]);
354

355
        $count = \count($havings);
356

357
        for ($i = 1; $i < $count; $i++) {
358
            $sql[] = $havings[$i]['separator'] . ' ' . $this->{$havings[$i]['type']}($havings[$i]);
359
        }
360

361
        return ($prefix ? ' HAVING ' : '') . \implode(' ', $sql);
362
    }
363

364
    /**
365
     * Handle ORDER BY
366
     */
367
    protected function handle_orderings(array $ordering): string
368
    {
369
        if (empty($ordering)) {
370
            return '';
371
        }
372

373
        $sql = [];
374

375
        foreach ($ordering as $order) {
376
            if ($order['nulls'] !== null) {
377
                foreach ($order['columns'] as $column) {
378
                    $column = $this->columns([$column]);
379

380
                    if ($order['nulls'] === 'NULLS FIRST') {
381
                        $sql[] = '(CASE WHEN ' . $column . ' IS NULL THEN 0 ELSE 1 END)';
382
                    } else {
383
                        $sql[] = '(CASE WHEN ' . $column . ' IS NULL THEN 1 ELSE 0 END)';
384
                    }
385
                }
386
            }
387

388
            $sql[] = $this->columns($order['columns']) . ' ' . $order['order'];
389
        }
390

391
        return ' ORDER BY ' . \implode(', ', $sql);
392
    }
393

394
    /**
395
     * Handle SET
396
     */
397
    protected function handle_set_columns(array $columns): string
398
    {
399
        if (empty($columns)) {
400
            return '';
401
        }
402

403
        $sql = [];
404

405
        foreach ($columns as $column) {
406
            $sql[] = $this->wrap($column['column']) . ' = ' . $this->param($column['value']);
407
        }
408

409
        return ' SET ' . \implode(', ', $sql);
410
    }
411

412
    /**
413
     * Handle insert values
414
     */
415
    protected function handle_insert_values(array $values): string
416
    {
417
        return ' VALUES (' . $this->params($values) . ')';
418
    }
419

420
    /**
421
     * Handle limits
422
     */
423
    protected function handle_limit(int $limit = 0): string
424
    {
425
        return ($limit === 0) ? '' : ' LIMIT ' . $this->param($limit);
426
    }
427

428
    /**
429
     * Handle offsets
430
     */
431
    protected function handle_offset(int $offset = -1): string
432
    {
433
        return ($offset === -1) ? '' : ' OFFSET ' . $this->param($offset);
434
    }
435

436
    protected function join_column(array $join): string
437
    {
438
        return $this->wrap($join['column1']) . ' ' . $join['operator'] . ' ' . $this->wrap($join['column2']);
439
    }
440

441
    protected function join_nested(array $join): string
442
    {
443
        return '(' . $this->handle_join_conditions($join['join']->get_join_conditions()) . ')';
444
    }
445

446
    protected function join_expression(array $join): string
447
    {
448
        return $this->wrap($join['expression']);
449
    }
450

451
    protected function where_column(array $where): string
452
    {
453
        return $this->wrap($where['column']) . ' ' . $where['operator'] . ' ' . $this->param($where['value']);
454
    }
455

456
    protected function where_in(array $where): string
457
    {
458
        return $this->wrap($where['column']) . ' ' . ($where['not'] ? 'NOT IN ' : 'IN ') . '(' . $this->params($where['value']) . ')';
459
    }
460

461
    protected function where_in_select(array $where): string
462
    {
463
        return $this->wrap($where['column']) . ' ' . ($where['not'] ? 'NOT IN ' : 'IN ') . '(' . $this->select($where['subquery']->get_sql_Statement()) . ')';
464
    }
465

466
    protected function where_nested(array $where): string
467
    {
468
        return '(' . $this->handle_wheres($where['clause'], false) . ')';
469
    }
470

471
    protected function where_exists(array $where): string
472
    {
473
        return ($where['not'] ? 'NOT EXISTS ' : 'EXISTS ') . '(' . $this->select($where['subquery']->get_sql_statement()) . ')';
474
    }
475

476
    protected function where_null(array $where): string
477
    {
478
        return $this->wrap($where['column']) . ' ' . ($where['not'] ? 'IS NOT NULL' : 'IS NULL');
479
    }
480

481
    protected function where_between(array $where): string
482
    {
483
        return $this->wrap($where['column']) . ' ' . ($where['not'] ? 'NOT BETWEEN' : 'BETWEEN') . ' ' . $this->param($where['value1']) . ' AND ' . $this->param($where['value2']);
484
    }
485

486
    protected function where_like(array $where): string
487
    {
488
        return $this->wrap($where['column']) . ' ' . ($where['not'] ? 'NOT LIKE' : 'LIKE') . ' ' . $this->param($where['pattern']);
489
    }
490

491
    protected function where_subquery(array $where): string
492
    {
493
        return $this->wrap($where['column']) . ' ' . $where['operator'] . ' (' . $this->select($where['subquery']->get_sql_statement()) . ')';
494
    }
495

496
    protected function where_nop(array $where): string
497
    {
498
        return $this->wrap($where['column']);
499
    }
500

501
    protected function having_condition(array $having): string
502
    {
503
        return $this->wrap($having['aggregate']) . ' ' . $having['operator'] . ' ' . $this->param($having['value']);
504
    }
505

506
    protected function having_nested(array $having): string
507
    {
508
        return '(' . $this->handle_havings($having['conditions'], false) . ')';
509
    }
510

511
    protected function having_between(array $having): string
512
    {
513
        return $this->wrap($having['aggregate']) . ($having['not'] ? ' NOT BETWEEN ' : ' BETWEEN ') . $this->param($having['value1']) . ' AND ' . $this->param($having['value2']);
514
    }
515

516
    protected function having_in_select(array $having): string
517
    {
518
        return $this->wrap($having['aggregate']) . ($having['not'] ? ' NOT IN ' : ' IN ') . '(' . $this->select($having['subquery']->get_sql_statement()) . ')';
519
    }
520

521
    protected function having_in(array $having): string
522
    {
523
        return $this->wrap($having['aggregate']) . ($having['not'] ? ' NOT IN ' : ' IN ') . '(' . $this->params($having['value']) . ')';
524
    }
525

526
    protected function aggregate_function_COUNT(array $func): string
527
    {
528
        return 'COUNT(' . ($func['distinct'] ? 'DISTINCT ' : '') . $this->columns($func['column']) . ')';
529
    }
530

531
    protected function aggregate_function_AVG(array $func): string
532
    {
533
        return 'AVG(' . ($func['distinct'] ? 'DISTINCT ' : '') . $this->wrap($func['column']) . ')';
534
    }
535

536
    protected function aggregate_function_SUM(array $func): string
537
    {
538
        return 'SUM(' . ($func['distinct'] ? 'DISTINCT ' : '') . $this->wrap($func['column']) . ')';
539
    }
540

541
    protected function aggregate_function_MIN(array $func): string
542
    {
543
        return 'MIN(' . ($func['distinct'] ? 'DISTINCT ' : '') . $this->wrap($func['column']) . ')';
544
    }
545

546
    protected function aggregate_function_MAX(array $func): string
547
    {
548
        return 'MAX(' . ($func['distinct'] ? 'DISTINCT ' : '') . $this->wrap($func['column']) . ')';
549
    }
550

551
    protected function sql_function_UCASE(array $func): string
552
    {
553
        return 'UCASE(' . $this->wrap($func['column']) . ')';
554
    }
555

556
    protected function sql_function_LCASE(array $func): string
557
    {
558
        return 'LCASE(' . $this->wrap($func['column']) . ')';
559
    }
560

561
    protected function sql_function_MID(array $func): string
562
    {
563
        return 'MID(' . $this->wrap($func['column']) . ', ' . $this->param($func['start']) . ($func['length'] > 0 ? ', ' . $this->param($func['length']) . ')' : ')');
564
    }
565

566
    protected function sql_function_LEN(array $func): string
567
    {
568
        return 'LEN(' . $this->wrap($func['column']) . ')';
569
    }
570

571
    protected function sql_function_ROUND(array $func): string
572
    {
573
        return 'ROUND(' . $this->wrap($func['column']) . ', ' . $this->param($func['decimals']) . ')';
574
    }
575

576
    protected function sql_function_NOW(array $func): string
577
    {
578
        return 'NOW()';
579
    }
580

581
    protected function sql_function_FORMAT(array $func): string
582
    {
583
        return 'FORMAT(' . $this->wrap($func['column']) . ', ' . $this->param($func['format']) . ')';
584
    }
585
}
586