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\OCI8;
15

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

20
/**
21
 * Builder for OCI8
22
 */
23
class Builder extends BaseBuilder
24
{
25
    /**
26
     * Identifier escape character
27
     *
28
     * @var string
29
     */
30
    protected $escapeChar = '"';
31

32
    /**
33
     * ORDER BY random keyword
34
     *
35
     * @var array
36
     */
37
    protected $randomKeyword = [
38
        '"DBMS_RANDOM"."RANDOM"',
39
    ];
40

41
    /**
42
     * COUNT string
43
     *
44
     * @used-by CI_DB_driver::count_all()
45
     * @used-by BaseBuilder::count_all_results()
46
     *
47
     * @var string
48
     */
49
    protected $countString = 'SELECT COUNT(1) ';
50

51
    /**
52
     * Limit used flag
53
     *
54
     * If we use LIMIT, we'll add a field that will
55
     * throw off num_fields later.
56
     *
57
     * @var bool
58
     */
59
    protected $limitUsed = false;
60

61
    /**
62
     * A reference to the database connection.
63
     *
64
     * @var Connection
65
     */
66
    protected $db;
67

68
    /**
69
     * Generates a platform-specific insert string from the supplied data.
70
     */
71
    protected function _insertBatch(string $table, array $keys, array $values): string
72
    {
73
        $sql = $this->QBOptions['sql'] ?? '';
74

75
        // if this is the first iteration of batch then we need to build skeleton sql
76
        if ($sql === '') {
77
            $insertKeys    = implode(', ', $keys);
78
            $hasPrimaryKey = in_array('PRIMARY', array_column($this->db->getIndexData($table), 'type'), true);
79

80
            // ORA-00001 measures
81
            $sql = 'INSERT' . ($hasPrimaryKey ? '' : ' ALL') . ' INTO ' . $table . ' (' . $insertKeys . ")\n{:_table_:}";
82

83
            $this->QBOptions['sql'] = $sql;
84
        }
85

86
        if (isset($this->QBOptions['setQueryAsData'])) {
87
            $data = $this->QBOptions['setQueryAsData'];
88
        } else {
89
            $data = implode(
90
                " FROM DUAL UNION ALL\n",
91
                array_map(
92
                    static fn ($value) => 'SELECT ' . implode(', ', array_map(
93
                        static fn ($key, $index) => $index . ' ' . $key,
94
                        $keys,
95
                        $value
96
                    )),
97
                    $values
98
                )
99
            ) . " FROM DUAL\n";
100
        }
101

102
        return str_replace('{:_table_:}', $data, $sql);
103
    }
104

105
    /**
106
     * Generates a platform-specific replace string from the supplied data
107
     */
108
    protected function _replace(string $table, array $keys, array $values): string
109
    {
110
        $fieldNames = array_map(static fn ($columnName) => trim($columnName, '"'), $keys);
111

112
        $uniqueIndexes = array_filter($this->db->getIndexData($table), static function ($index) use ($fieldNames) {
113
            $hasAllFields = count(array_intersect($index->fields, $fieldNames)) === count($index->fields);
114

115
            return ($index->type === 'PRIMARY') && $hasAllFields;
116
        });
117
        $replaceableFields = array_filter($keys, static function ($columnName) use ($uniqueIndexes) {
118
            foreach ($uniqueIndexes as $index) {
119
                if (in_array(trim($columnName, '"'), $index->fields, true)) {
120
                    return false;
121
                }
122
            }
123

124
            return true;
125
        });
126

127
        $sql = 'MERGE INTO ' . $table . "\n USING (SELECT ";
128

129
        $sql .= implode(', ', array_map(static fn ($columnName, $value) => $value . ' ' . $columnName, $keys, $values));
130

131
        $sql .= ' FROM DUAL) "_replace" ON ( ';
132

133
        $onList   = [];
134
        $onList[] = '1 != 1';
135

136
        foreach ($uniqueIndexes as $index) {
137
            $onList[] = '(' . implode(' AND ', array_map(static fn ($columnName) => $table . '."' . $columnName . '" = "_replace"."' . $columnName . '"', $index->fields)) . ')';
138
        }
139

140
        $sql .= implode(' OR ', $onList) . ') WHEN MATCHED THEN UPDATE SET ';
141

142
        $sql .= implode(', ', array_map(static fn ($columnName) => $columnName . ' = "_replace".' . $columnName, $replaceableFields));
143

144
        $sql .= ' WHEN NOT MATCHED THEN INSERT (' . implode(', ', $replaceableFields) . ') VALUES ';
145

146
        return $sql . (' (' . implode(', ', array_map(static fn ($columnName) => '"_replace".' . $columnName, $replaceableFields)) . ')');
147
    }
148

149
    /**
150
     * Generates a platform-specific truncate string from the supplied data
151
     *
152
     * If the database does not support the truncate() command,
153
     * then this method maps to 'DELETE FROM table'
154
     */
155
    protected function _truncate(string $table): string
156
    {
157
        return 'TRUNCATE TABLE ' . $table;
158
    }
159

160
    /**
161
     * Compiles a delete string and runs the query
162
     *
163
     * @param mixed $where
164
     *
165
     * @return mixed
166
     *
167
     * @throws DatabaseException
168
     */
169
    public function delete($where = '', ?int $limit = null, bool $resetData = true)
170
    {
171
        if ($limit !== null && $limit !== 0) {
172
            $this->QBLimit = $limit;
173
        }
174

175
        return parent::delete($where, null, $resetData);
176
    }
177

178
    /**
179
     * Generates a platform-specific delete string from the supplied data
180
     */
181
    protected function _delete(string $table): string
182
    {
183
        if ($this->QBLimit) {
184
            $this->where('rownum <= ', $this->QBLimit, false);
185
            $this->QBLimit = false;
186
        }
187

188
        return parent::_delete($table);
189
    }
190

191
    /**
192
     * Generates a platform-specific update string from the supplied data
193
     */
194
    protected function _update(string $table, array $values): string
195
    {
196
        $valStr = [];
197

198
        foreach ($values as $key => $val) {
199
            $valStr[] = $key . ' = ' . $val;
200
        }
201

202
        if ($this->QBLimit) {
203
            $this->where('rownum <= ', $this->QBLimit, false);
204
        }
205

206
        return 'UPDATE ' . $this->compileIgnore('update') . $table . ' SET ' . implode(', ', $valStr)
207
            . $this->compileWhereHaving('QBWhere')
208
            . $this->compileOrderBy();
209
    }
210

211
    /**
212
     * Generates a platform-specific LIMIT clause.
213
     */
214
    protected function _limit(string $sql, bool $offsetIgnore = false): string
215
    {
216
        $offset = (int) ($offsetIgnore === false ? $this->QBOffset : 0);
217
        if (version_compare($this->db->getVersion(), '12.1', '>=')) {
218
            // OFFSET-FETCH can be used only with the ORDER BY clause
219
            if (empty($this->QBOrderBy)) {
220
                $sql .= ' ORDER BY 1';
221
            }
222

223
            return $sql . ' OFFSET ' . $offset . ' ROWS FETCH NEXT ' . $this->QBLimit . ' ROWS ONLY';
224
        }
225

226
        $this->limitUsed    = true;
227
        $limitTemplateQuery = 'SELECT * FROM (SELECT INNER_QUERY.*, ROWNUM RNUM FROM (%s) INNER_QUERY WHERE ROWNUM < %d)' . ($offset !== 0 ? ' WHERE RNUM >= %d' : '');
228

229
        return sprintf($limitTemplateQuery, $sql, $offset + $this->QBLimit + 1, $offset);
230
    }
231

232
    /**
233
     * Resets the query builder values.  Called by the get() function
234
     */
235
    protected function resetSelect()
236
    {
237
        $this->limitUsed = false;
238
        parent::resetSelect();
239
    }
240

241
    /**
242
     * Generates a platform-specific batch update string from the supplied data
243
     */
244
    protected function _updateBatch(string $table, array $keys, array $values): string
245
    {
246
        $sql = $this->QBOptions['sql'] ?? '';
247

248
        // if this is the first iteration of batch then we need to build skeleton sql
249
        if ($sql === '') {
250
            $constraints = $this->QBOptions['constraints'] ?? [];
251

252
            if ($constraints === []) {
253
                if ($this->db->DBDebug) {
254
                    throw new DatabaseException('You must specify a constraint to match on for batch updates.');
255
                }
256

257
                return ''; // @codeCoverageIgnore
258
            }
259

260
            $updateFields = $this->QBOptions['updateFields'] ??
261
                $this->updateFields($keys, false, $constraints)->QBOptions['updateFields'] ??
262
                [];
263

264
            $alias = $this->QBOptions['alias'] ?? '"_u"';
265

266
            // Oracle doesn't support ignore on updates so we will use MERGE
267
            $sql = 'MERGE INTO ' . $table . "\n";
268

269
            $sql .= "USING (\n{:_table_:}";
270

271
            $sql .= ') ' . $alias . "\n";
272

273
            $sql .= 'ON (' . implode(
274
                ' AND ',
275
                array_map(
276
                    static fn ($key, $value) => (
277
                        ($value instanceof RawSql && is_string($key))
278
                        ?
279
                        $table . '.' . $key . ' = ' . $value
280
                        :
281
                        (
282
                            $value instanceof RawSql
283
                            ?
284
                            $value
285
                            :
286
                            $table . '.' . $value . ' = ' . $alias . '.' . $value
287
                        )
288
                    ),
289
                    array_keys($constraints),
290
                    $constraints
291
                )
292
            ) . ")\n";
293

294
            $sql .= "WHEN MATCHED THEN UPDATE\n";
295

296
            $sql .= "SET\n";
297

298
            $sql .= implode(
299
                ",\n",
300
                array_map(
301
                    static fn ($key, $value) => $table . '.' . $key . ($value instanceof RawSql ?
302
                    ' = ' . $value :
303
                    ' = ' . $alias . '.' . $value),
304
                    array_keys($updateFields),
305
                    $updateFields
306
                )
307
            );
308

309
            $this->QBOptions['sql'] = $sql;
310
        }
311

312
        if (isset($this->QBOptions['setQueryAsData'])) {
313
            $data = $this->QBOptions['setQueryAsData'];
314
        } else {
315
            $data = implode(
316
                " UNION ALL\n",
317
                array_map(
318
                    static fn ($value) => 'SELECT ' . implode(', ', array_map(
319
                        static fn ($key, $index) => $index . ' ' . $key,
320
                        $keys,
321
                        $value
322
                    )) . ' FROM DUAL',
323
                    $values
324
                )
325
            ) . "\n";
326
        }
327

328
        return str_replace('{:_table_:}', $data, $sql);
329
    }
330

331
    /**
332
     * Generates a platform-specific upsertBatch string from the supplied data
333
     *
334
     * @throws DatabaseException
335
     */
336
    protected function _upsertBatch(string $table, array $keys, array $values): string
337
    {
338
        $sql = $this->QBOptions['sql'] ?? '';
339

340
        // if this is the first iteration of batch then we need to build skeleton sql
341
        if ($sql === '') {
342
            $constraints = $this->QBOptions['constraints'] ?? [];
343

344
            if (empty($constraints)) {
345
                $fieldNames = array_map(static fn ($columnName) => trim($columnName, '"'), $keys);
346

347
                $uniqueIndexes = array_filter($this->db->getIndexData($table), static function ($index) use ($fieldNames) {
348
                    $hasAllFields = count(array_intersect($index->fields, $fieldNames)) === count($index->fields);
349

350
                    return ($index->type === 'PRIMARY' || $index->type === 'UNIQUE') && $hasAllFields;
351
                });
352

353
                // only take first index
354
                foreach ($uniqueIndexes as $index) {
355
                    $constraints = $index->fields;
356
                    break;
357
                }
358

359
                $constraints = $this->onConstraint($constraints)->QBOptions['constraints'] ?? [];
360
            }
361

362
            if (empty($constraints)) {
363
                if ($this->db->DBDebug) {
364
                    throw new DatabaseException('No constraint found for upsert.');
365
                }
366

367
                return ''; // @codeCoverageIgnore
368
            }
369

370
            $alias = $this->QBOptions['alias'] ?? '"_upsert"';
371

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

374
            $sql = 'MERGE INTO ' . $table . "\nUSING (\n{:_table_:}";
375

376
            $sql .= ") {$alias}\nON (";
377

378
            $sql .= implode(
379
                ' AND ',
380
                array_map(
381
                    static fn ($key, $value) => (
382
                        ($value instanceof RawSql && is_string($key))
383
                        ?
384
                        $table . '.' . $key . ' = ' . $value
385
                        :
386
                        (
387
                            $value instanceof RawSql
388
                            ?
389
                            $value
390
                            :
391
                            $table . '.' . $value . ' = ' . $alias . '.' . $value
392
                        )
393
                    ),
394
                    array_keys($constraints),
395
                    $constraints
396
                )
397
            ) . ")\n";
398

399
            $sql .= "WHEN MATCHED THEN UPDATE SET\n";
400

401
            $sql .= implode(
402
                ",\n",
403
                array_map(
404
                    static fn ($key, $value) => $key . ($value instanceof RawSql ?
405
                    " = {$value}" :
406
                    " = {$alias}.{$value}"),
407
                    array_keys($updateFields),
408
                    $updateFields
409
                )
410
            );
411

412
            $sql .= "\nWHEN NOT MATCHED THEN INSERT (" . implode(', ', $keys) . ")\nVALUES ";
413

414
            $sql .= (' ('
415
                . implode(', ', array_map(static fn ($columnName) => "{$alias}.{$columnName}", $keys))
416
                . ')');
417

418
            $this->QBOptions['sql'] = $sql;
419
        }
420

421
        if (isset($this->QBOptions['setQueryAsData'])) {
422
            $data = $this->QBOptions['setQueryAsData'];
423
        } else {
424
            $data = implode(
425
                " FROM DUAL UNION ALL\n",
426
                array_map(
427
                    static fn ($value) => 'SELECT ' . implode(', ', array_map(
428
                        static fn ($key, $index) => $index . ' ' . $key,
429
                        $keys,
430
                        $value
431
                    )),
432
                    $values
433
                )
434
            ) . " FROM DUAL\n";
435
        }
436

437
        return str_replace('{:_table_:}', $data, $sql);
438
    }
439

440
    /**
441
     * Generates a platform-specific batch update string from the supplied data
442
     */
443
    protected function _deleteBatch(string $table, array $keys, array $values): string
444
    {
445
        $sql = $this->QBOptions['sql'] ?? '';
446

447
        // if this is the first iteration of batch then we need to build skeleton sql
448
        if ($sql === '') {
449
            $constraints = $this->QBOptions['constraints'] ?? [];
450

451
            if ($constraints === []) {
452
                if ($this->db->DBDebug) {
453
                    throw new DatabaseException('You must specify a constraint to match on for batch deletes.'); // @codeCoverageIgnore
454
                }
455

456
                return ''; // @codeCoverageIgnore
457
            }
458

459
            $alias = $this->QBOptions['alias'] ?? '_u';
460

461
            $sql = 'DELETE ' . $table . "\n";
462

463
            $sql .= "WHERE EXISTS (SELECT * FROM (\n{:_table_:}";
464

465
            $sql .= ') ' . $alias . "\n";
466

467
            $sql .= 'WHERE ' . implode(
468
                ' AND ',
469
                array_map(
470
                    static fn ($key, $value) => (
471
                        $value instanceof RawSql ?
472
                        $value :
473
                        (
474
                            is_string($key) ?
475
                            $table . '.' . $key . ' = ' . $alias . '.' . $value :
476
                            $table . '.' . $value . ' = ' . $alias . '.' . $value
477
                        )
478
                    ),
479
                    array_keys($constraints),
480
                    $constraints
481
                )
482
            );
483

484
            // convert binds in where
485
            foreach ($this->QBWhere as $key => $where) {
486
                foreach ($this->binds as $field => $bind) {
487
                    $this->QBWhere[$key]['condition'] = str_replace(':' . $field . ':', $bind[0], $where['condition']);
488
                }
489
            }
490

491
            $sql .= ' ' . str_replace(
492
                'WHERE ',
493
                'AND ',
494
                $this->compileWhereHaving('QBWhere')
495
            ) . ')';
496

497
            $this->QBOptions['sql'] = $sql;
498
        }
499

500
        if (isset($this->QBOptions['setQueryAsData'])) {
501
            $data = $this->QBOptions['setQueryAsData'];
502
        } else {
503
            $data = implode(
504
                " FROM DUAL UNION ALL\n",
505
                array_map(
506
                    static fn ($value) => 'SELECT ' . implode(', ', array_map(
507
                        static fn ($key, $index) => $index . ' ' . $key,
508
                        $keys,
509
                        $value
510
                    )),
511
                    $values
512
                )
513
            ) . " FROM DUAL\n";
514
        }
515

516
        return str_replace('{:_table_:}', $data, $sql);
517
    }
518

519
    /**
520
     * Gets column names from a select query
521
     */
522
    protected function fieldsFromQuery(string $sql): array
523
    {
524
        return $this->db->query('SELECT * FROM (' . $sql . ') "_u_" WHERE ROWNUM = 1')->getFieldNames();
525
    }
526
}
527