ci4

1
<?php
2

3
declare(strict_types=1);
4

5
/**
6
 * This file is part of CodeIgniter 4 framework.
7
 *
8
 * (c) CodeIgniter Foundation <admin@codeigniter.com>
9
 *
10
 * For the full copyright and license information, please view
11
 * the LICENSE file that was distributed with this source code.
12
 */
13

14
namespace CodeIgniter\Database\Postgre;
15

16
use CodeIgniter\Database\BaseBuilder;
17
use CodeIgniter\Database\Exceptions\DatabaseException;
18
use CodeIgniter\Database\RawSql;
19
use InvalidArgumentException;
20

21
/**
22
 * Builder for Postgre
23
 */
24
class Builder extends BaseBuilder
25
{
26
    /**
27
     * ORDER BY random keyword
28
     *
29
     * @var array
30
     */
31
    protected $randomKeyword = [
32
        'RANDOM()',
33
    ];
34

35
    /**
36
     * Specifies which sql statements
37
     * support the ignore option.
38
     *
39
     * @var array
40
     */
41
    protected $supportedIgnoreStatements = [
42
        'insert' => 'ON CONFLICT DO NOTHING',
43
    ];
44

45
    /**
46
     * Checks if the ignore option is supported by
47
     * the Database Driver for the specific statement.
48
     *
49
     * @return string
50
     */
51
    protected function compileIgnore(string $statement)
52
    {
53
        $sql = parent::compileIgnore($statement);
54

55
        if (! empty($sql)) {
56
            $sql = ' ' . trim($sql);
57
        }
58

59
        return $sql;
60
    }
61

62
    /**
63
     * ORDER BY
64
     *
65
     * @param string $direction ASC, DESC or RANDOM
66
     *
67
     * @return BaseBuilder
68
     */
69
    public function orderBy(string $orderBy, string $direction = '', ?bool $escape = null)
70
    {
71
        $direction = strtoupper(trim($direction));
72
        if ($direction === 'RANDOM') {
73
            if (ctype_digit($orderBy)) {
74
                $orderBy = (float) ($orderBy > 1 ? "0.{$orderBy}" : $orderBy);
75
            }
76

77
            if (is_float($orderBy)) {
78
                $this->db->simpleQuery("SET SEED {$orderBy}");
79
            }
80

81
            $orderBy   = $this->randomKeyword[0];
82
            $direction = '';
83
            $escape    = false;
84
        }
85

86
        return parent::orderBy($orderBy, $direction, $escape);
87
    }
88

89
    /**
90
     * Increments a numeric column by the specified value.
91
     *
92
     * @return mixed
93
     *
94
     * @throws DatabaseException
95
     */
96
    public function increment(string $column, int $value = 1)
97
    {
98
        $column = $this->db->protectIdentifiers($column);
99

100
        $sql = $this->_update($this->QBFrom[0], [$column => "to_number({$column}, '9999999') + {$value}"]);
101

102
        if (! $this->testMode) {
103
            $this->resetWrite();
104

105
            return $this->db->query($sql, $this->binds, false);
106
        }
107

108
        return true;
109
    }
110

111
    /**
112
     * Decrements a numeric column by the specified value.
113
     *
114
     * @return mixed
115
     *
116
     * @throws DatabaseException
117
     */
118
    public function decrement(string $column, int $value = 1)
119
    {
120
        $column = $this->db->protectIdentifiers($column);
121

122
        $sql = $this->_update($this->QBFrom[0], [$column => "to_number({$column}, '9999999') - {$value}"]);
123

124
        if (! $this->testMode) {
125
            $this->resetWrite();
126

127
            return $this->db->query($sql, $this->binds, false);
128
        }
129

130
        return true;
131
    }
132

133
    /**
134
     * Compiles an replace into string and runs the query.
135
     * Because PostgreSQL doesn't support the replace into command,
136
     * we simply do a DELETE and an INSERT on the first key/value
137
     * combo, assuming that it's either the primary key or a unique key.
138
     *
139
     * @param array|null $set An associative array of insert values
140
     *
141
     * @return mixed
142
     *
143
     * @throws DatabaseException
144
     */
145
    public function replace(?array $set = null)
146
    {
147
        if ($set !== null) {
148
            $this->set($set);
149
        }
150

151
        if ($this->QBSet === []) {
152
            if ($this->db->DBDebug) {
153
                throw new DatabaseException('You must use the "set" method to update an entry.');
154
            }
155

156
            return false; // @codeCoverageIgnore
157
        }
158

159
        $table = $this->QBFrom[0];
160
        $set   = $this->binds;
161

162
        array_walk($set, static function (array &$item): void {
163
            $item = $item[0];
164
        });
165

166
        $key   = array_key_first($set);
167
        $value = $set[$key];
168

169
        $builder = $this->db->table($table);
170
        $exists  = $builder->where($key, $value, true)->get()->getFirstRow();
171

172
        if (empty($exists) && $this->testMode) {
173
            $result = $this->getCompiledInsert();
174
        } elseif (empty($exists)) {
175
            $result = $builder->insert($set);
176
        } elseif ($this->testMode) {
177
            $result = $this->where($key, $value, true)->getCompiledUpdate();
178
        } else {
179
            array_shift($set);
180
            $result = $builder->where($key, $value, true)->update($set);
181
        }
182

183
        unset($builder);
184
        $this->resetWrite();
185
        $this->binds = [];
186

187
        return $result;
188
    }
189

190
    /**
191
     * Generates a platform-specific insert string from the supplied data
192
     */
193
    protected function _insert(string $table, array $keys, array $unescapedKeys): string
194
    {
195
        return trim(sprintf('INSERT INTO %s (%s) VALUES (%s) %s', $table, implode(', ', $keys), implode(', ', $unescapedKeys), $this->compileIgnore('insert')));
196
    }
197

198
    /**
199
     * Generates a platform-specific insert string from the supplied data.
200
     */
201
    protected function _insertBatch(string $table, array $keys, array $values): string
202
    {
203
        $sql = $this->QBOptions['sql'] ?? '';
204

205
        // if this is the first iteration of batch then we need to build skeleton sql
206
        if ($sql === '') {
207
            $sql = 'INSERT INTO ' . $table . '(' . implode(', ', $keys) . ")\n{:_table_:}\n";
208

209
            $sql .= $this->compileIgnore('insert');
210

211
            $this->QBOptions['sql'] = $sql;
212
        }
213

214
        if (isset($this->QBOptions['setQueryAsData'])) {
215
            $data = $this->QBOptions['setQueryAsData'];
216
        } else {
217
            $data = 'VALUES ' . implode(', ', $this->formatValues($values));
218
        }
219

220
        return str_replace('{:_table_:}', $data, $sql);
221
    }
222

223
    /**
224
     * Compiles a delete string and runs the query
225
     *
226
     * @param mixed $where
227
     *
228
     * @return mixed
229
     *
230
     * @throws DatabaseException
231
     */
232
    public function delete($where = '', ?int $limit = null, bool $resetData = true)
233
    {
234
        if ($limit !== null && $limit !== 0 || ! empty($this->QBLimit)) {
235
            throw new DatabaseException('PostgreSQL does not allow LIMITs on DELETE queries.');
236
        }
237

238
        return parent::delete($where, $limit, $resetData);
239
    }
240

241
    /**
242
     * Generates a platform-specific LIMIT clause.
243
     */
244
    protected function _limit(string $sql, bool $offsetIgnore = false): string
245
    {
246
        return $sql . ' LIMIT ' . $this->QBLimit . ($this->QBOffset ? " OFFSET {$this->QBOffset}" : '');
247
    }
248

249
    /**
250
     * Generates a platform-specific update string from the supplied data
251
     *
252
     * @throws DatabaseException
253
     */
254
    protected function _update(string $table, array $values): string
255
    {
256
        if (! empty($this->QBLimit)) {
257
            throw new DatabaseException('Postgres does not support LIMITs with UPDATE queries.');
258
        }
259

260
        $this->QBOrderBy = [];
261

262
        return parent::_update($table, $values);
263
    }
264

265
    /**
266
     * Generates a platform-specific delete string from the supplied data
267
     */
268
    protected function _delete(string $table): string
269
    {
270
        $this->QBLimit = false;
271

272
        return parent::_delete($table);
273
    }
274

275
    /**
276
     * Generates a platform-specific truncate string from the supplied data
277
     *
278
     * If the database does not support the truncate() command,
279
     * then this method maps to 'DELETE FROM table'
280
     */
281
    protected function _truncate(string $table): string
282
    {
283
        return 'TRUNCATE ' . $table . ' RESTART IDENTITY';
284
    }
285

286
    /**
287
     * Platform independent LIKE statement builder.
288
     *
289
     * In PostgreSQL, the ILIKE operator will perform case insensitive
290
     * searches according to the current locale.
291
     *
292
     * @see https://www.postgresql.org/docs/9.2/static/functions-matching.html
293
     */
294
    protected function _like_statement(?string $prefix, string $column, ?string $not, string $bind, bool $insensitiveSearch = false): string
295
    {
296
        $op = $insensitiveSearch === true ? 'ILIKE' : 'LIKE';
297

298
        return "{$prefix} {$column} {$not} {$op} :{$bind}:";
299
    }
300

301
    /**
302
     * Generates the JOIN portion of the query
303
     *
304
     * @param RawSql|string $cond
305
     *
306
     * @return BaseBuilder
307
     */
308
    public function join(string $table, $cond, string $type = '', ?bool $escape = null)
309
    {
310
        if (! in_array('FULL OUTER', $this->joinTypes, true)) {
311
            $this->joinTypes = array_merge($this->joinTypes, ['FULL OUTER']);
312
        }
313

314
        return parent::join($table, $cond, $type, $escape);
315
    }
316

317
    /**
318
     * Generates a platform-specific batch update string from the supplied data
319
     *
320
     * @used-by batchExecute()
321
     *
322
     * @param string                 $table  Protected table name
323
     * @param list<string>           $keys   QBKeys
324
     * @param list<list<int|string>> $values QBSet
325
     */
326
    protected function _updateBatch(string $table, array $keys, array $values): string
327
    {
328
        $sql = $this->QBOptions['sql'] ?? '';
329

330
        // if this is the first iteration of batch then we need to build skeleton sql
331
        if ($sql === '') {
332
            $constraints = $this->QBOptions['constraints'] ?? [];
333

334
            if ($constraints === []) {
335
                if ($this->db->DBDebug) {
336
                    throw new DatabaseException('You must specify a constraint to match on for batch updates.'); // @codeCoverageIgnore
337
                }
338

339
                return ''; // @codeCoverageIgnore
340
            }
341

342
            $updateFields = $this->QBOptions['updateFields'] ??
343
                $this->updateFields($keys, false, $constraints)->QBOptions['updateFields'] ??
344
                [];
345

346
            $alias = $this->QBOptions['alias'] ?? '_u';
347

348
            $sql = 'UPDATE ' . $this->compileIgnore('update') . $table . "\n";
349

350
            $sql .= "SET\n";
351

352
            $that = $this;
353
            $sql .= implode(
354
                ",\n",
355
                array_map(
356
                    static fn ($key, $value) => $key . ($value instanceof RawSql ?
357
                            ' = ' . $value :
358
                            ' = ' . $that->cast($alias . '.' . $value, $that->getFieldType($table, $key))),
359
                    array_keys($updateFields),
360
                    $updateFields
361
                )
362
            ) . "\n";
363

364
            $sql .= "FROM (\n{:_table_:}";
365

366
            $sql .= ') ' . $alias . "\n";
367

368
            $sql .= 'WHERE ' . implode(
369
                ' AND ',
370
                array_map(
371
                    static function ($key, $value) use ($table, $alias, $that) {
372
                        if ($value instanceof RawSql && is_string($key)) {
373
                            return $table . '.' . $key . ' = ' . $value;
374
                        }
375

376
                        if ($value instanceof RawSql) {
377
                            return $value;
378
                        }
379

380
                        return $table . '.' . $value . ' = '
381
                            . $that->cast($alias . '.' . $value, $that->getFieldType($table, $value));
382
                    },
383
                    array_keys($constraints),
384
                    $constraints
385
                )
386
            );
387

388
            $this->QBOptions['sql'] = $sql;
389
        }
390

391
        if (isset($this->QBOptions['setQueryAsData'])) {
392
            $data = $this->QBOptions['setQueryAsData'];
393
        } else {
394
            $data = implode(
395
                " UNION ALL\n",
396
                array_map(
397
                    static fn ($value) => 'SELECT ' . implode(', ', array_map(
398
                        static fn ($key, $index) => $index . ' ' . $key,
399
                        $keys,
400
                        $value
401
                    )),
402
                    $values
403
                )
404
            ) . "\n";
405
        }
406

407
        return str_replace('{:_table_:}', $data, $sql);
408
    }
409

410
    /**
411
     * Returns cast expression.
412
     *
413
     * @TODO move this to BaseBuilder in 4.5.0
414
     */
415
    private function cast(string $expression, ?string $type): string
416
    {
417
        return ($type === null) ? $expression : 'CAST(' . $expression . ' AS ' . strtoupper($type) . ')';
418
    }
419

420
    /**
421
     * Returns the filed type from database meta data.
422
     *
423
     * @param string $table     Protected table name.
424
     * @param string $fieldName Field name. May be protected.
425
     */
426
    private function getFieldType(string $table, string $fieldName): ?string
427
    {
428
        $fieldName = trim($fieldName, $this->db->escapeChar);
429

430
        if (! isset($this->QBOptions['fieldTypes'][$table])) {
431
            $this->QBOptions['fieldTypes'][$table] = [];
432

433
            foreach ($this->db->getFieldData($table) as $field) {
434
                $type = $field->type;
435

436
                // If `character` (or `char`) lacks a specifier, it is equivalent
437
                // to `character(1)`.
438
                // See https://www.postgresql.org/docs/current/datatype-character.html
439
                if ($field->type === 'character') {
440
                    $type = $field->type . '(' . $field->max_length . ')';
441
                }
442

443
                $this->QBOptions['fieldTypes'][$table][$field->name] = $type;
444
            }
445
        }
446

447
        return $this->QBOptions['fieldTypes'][$table][$fieldName] ?? null;
448
    }
449

450
    /**
451
     * Generates a platform-specific upsertBatch string from the supplied data
452
     *
453
     * @throws DatabaseException
454
     */
455
    protected function _upsertBatch(string $table, array $keys, array $values): string
456
    {
457
        $sql = $this->QBOptions['sql'] ?? '';
458

459
        // if this is the first iteration of batch then we need to build skeleton sql
460
        if ($sql === '') {
461
            $fieldNames = array_map(static fn ($columnName) => trim($columnName, '"'), $keys);
462

463
            $constraints = $this->QBOptions['constraints'] ?? [];
464

465
            if (empty($constraints)) {
466
                $allIndexes = array_filter($this->db->getIndexData($table), static function ($index) use ($fieldNames) {
467
                    $hasAllFields = count(array_intersect($index->fields, $fieldNames)) === count($index->fields);
468

469
                    return ($index->type === 'UNIQUE' || $index->type === 'PRIMARY') && $hasAllFields;
470
                });
471

472
                foreach (array_map(static fn ($index) => $index->fields, $allIndexes) as $index) {
473
                    $constraints[] = current($index);
474
                    // only one index can be used?
475
                    break;
476
                }
477

478
                $constraints = $this->onConstraint($constraints)->QBOptions['constraints'] ?? [];
479
            }
480

481
            if (empty($constraints)) {
482
                if ($this->db->DBDebug) {
483
                    throw new DatabaseException('No constraint found for upsert.');
484
                }
485

486
                return ''; // @codeCoverageIgnore
487
            }
488

489
            // in value set - replace null with DEFAULT where constraint is presumed not null
490
            // autoincrement identity field must use DEFAULT and not NULL
491
            // this could be removed in favour of leaving to developer but does make things easier and function like other DBMS
492
            foreach ($constraints as $constraint) {
493
                $key = array_search(trim((string) $constraint, '"'), $fieldNames, true);
494

495
                if ($key !== false) {
496
                    foreach ($values as $arrayKey => $value) {
497
                        if (strtoupper((string) $value[$key]) === 'NULL') {
498
                            $values[$arrayKey][$key] = 'DEFAULT';
499
                        }
500
                    }
501
                }
502
            }
503

504
            $alias = $this->QBOptions['alias'] ?? '"excluded"';
505

506
            if (strtolower($alias) !== '"excluded"') {
507
                throw new InvalidArgumentException('Postgres alias is always named "excluded". A custom alias cannot be used.');
508
            }
509

510
            $updateFields = $this->QBOptions['updateFields'] ?? $this->updateFields($keys, false, $constraints)->QBOptions['updateFields'] ?? [];
511

512
            $sql = 'INSERT INTO ' . $table . ' (';
513

514
            $sql .= implode(', ', $keys);
515

516
            $sql .= ")\n";
517

518
            $sql .= '{:_table_:}';
519

520
            $sql .= 'ON CONFLICT(' . implode(',', $constraints) . ")\n";
521

522
            $sql .= "DO UPDATE SET\n";
523

524
            $sql .= implode(
525
                ",\n",
526
                array_map(
527
                    static fn ($key, $value) => $key . ($value instanceof RawSql ?
528
                    " = {$value}" :
529
                    " = {$alias}.{$value}"),
530
                    array_keys($updateFields),
531
                    $updateFields
532
                )
533
            );
534

535
            $this->QBOptions['sql'] = $sql;
536
        }
537

538
        if (isset($this->QBOptions['setQueryAsData'])) {
539
            $data = $this->QBOptions['setQueryAsData'];
540
        } else {
541
            $data = 'VALUES ' . implode(', ', $this->formatValues($values)) . "\n";
542
        }
543

544
        return str_replace('{:_table_:}', $data, $sql);
545
    }
546

547
    /**
548
     * Generates a platform-specific batch update string from the supplied data
549
     */
550
    protected function _deleteBatch(string $table, array $keys, array $values): string
551
    {
552
        $sql = $this->QBOptions['sql'] ?? '';
553

554
        // if this is the first iteration of batch then we need to build skeleton sql
555
        if ($sql === '') {
556
            $constraints = $this->QBOptions['constraints'] ?? [];
557

558
            if ($constraints === []) {
559
                if ($this->db->DBDebug) {
560
                    throw new DatabaseException('You must specify a constraint to match on for batch deletes.'); // @codeCoverageIgnore
561
                }
562

563
                return ''; // @codeCoverageIgnore
564
            }
565

566
            $alias = $this->QBOptions['alias'] ?? '_u';
567

568
            $sql = 'DELETE FROM ' . $table . "\n";
569

570
            $sql .= "USING (\n{:_table_:}";
571

572
            $sql .= ') ' . $alias . "\n";
573

574
            $that = $this;
575
            $sql .= 'WHERE ' . implode(
576
                ' AND ',
577
                array_map(
578
                    static function ($key, $value) use ($table, $alias, $that) {
579
                        if ($value instanceof RawSql) {
580
                            return $value;
581
                        }
582

583
                        if (is_string($key)) {
584
                            return $table . '.' . $key . ' = '
585
                                . $that->cast(
586
                                    $alias . '.' . $value,
587
                                    $that->getFieldType($table, $key)
588
                                );
589
                        }
590

591
                        return $table . '.' . $value . ' = ' . $alias . '.' . $value;
592
                    },
593
                    array_keys($constraints),
594
                    $constraints
595
                )
596
            );
597

598
            // convert binds in where
599
            foreach ($this->QBWhere as $key => $where) {
600
                foreach ($this->binds as $field => $bind) {
601
                    $this->QBWhere[$key]['condition'] = str_replace(':' . $field . ':', $bind[0], $where['condition']);
602
                }
603
            }
604

605
            $sql .= ' ' . str_replace(
606
                'WHERE ',
607
                'AND ',
608
                $this->compileWhereHaving('QBWhere')
609
            );
610

611
            $this->QBOptions['sql'] = $sql;
612
        }
613

614
        if (isset($this->QBOptions['setQueryAsData'])) {
615
            $data = $this->QBOptions['setQueryAsData'];
616
        } else {
617
            $data = implode(
618
                " UNION ALL\n",
619
                array_map(
620
                    static fn ($value) => 'SELECT ' . implode(', ', array_map(
621
                        static fn ($key, $index) => $index . ' ' . $key,
622
                        $keys,
623
                        $value
624
                    )),
625
                    $values
626
                )
627
            ) . "\n";
628
        }
629

630
        return str_replace('{:_table_:}', $data, $sql);
631
    }
632
}
633