3
declare(strict_types=1);
14
namespace CodeIgniter\Database\OCI8;
16
use CodeIgniter\Database\BaseConnection;
17
use CodeIgniter\Database\Exceptions\DatabaseException;
18
use CodeIgniter\Database\Query;
27
class Connection extends BaseConnection
34
protected $DBDriver = 'OCI8';
41
public $escapeChar = '"';
50
protected $reservedIdentifiers = [
55
protected $validDSNs = [
57
'tns' => '/^\(DESCRIPTION=(\(.+\)){2,}\)$/',
63
(\[)?[a-z0-9.:_-]+(\])? # Host or IP address
64
(:[1-9][0-9]{0,4})? # Port
67
([a-z0-9.$_]+)? # Service name
68
(:[a-z]+)? # Server type
69
(\/[a-z0-9$_]+)? # Instance name
73
'in' => '/^[a-z0-9$_]+$/i',
82
protected $resetStmtId = true;
98
public $commitMode = OCI_COMMIT_ON_SUCCESS;
114
public $lastInsertedTableName;
119
private function isValidDSN(): bool
121
if ($this->DSN === null || $this->DSN === '') {
125
foreach ($this->validDSNs as $regexp) {
126
if (preg_match($regexp, $this->DSN)) {
139
public function connect(bool $persistent = false)
141
if (! $this->isValidDSN()) {
145
$func = $persistent ? 'oci_pconnect' : 'oci_connect';
147
return ($this->charset === '')
148
? $func($this->username, $this->password, $this->DSN)
149
: $func($this->username, $this->password, $this->DSN, $this->charset);
158
public function reconnect()
167
protected function _close()
169
if (is_resource($this->cursorId)) {
170
oci_free_statement($this->cursorId);
172
if (is_resource($this->stmtId)) {
173
oci_free_statement($this->stmtId);
175
oci_close($this->connID);
181
public function setDatabase(string $databaseName): bool
189
public function getVersion(): string
191
if (isset($this->dataCache['version'])) {
192
return $this->dataCache['version'];
195
if (! $this->connID || ($versionString = oci_server_version($this->connID)) === false) {
198
if (preg_match('#Release\s(\d+(?:\.\d+)+)#', $versionString, $match)) {
199
return $this->dataCache['version'] = $match[1];
210
protected function execute(string $sql)
213
if ($this->resetStmtId === true) {
214
$this->stmtId = oci_parse($this->connID, $sql);
217
oci_set_prefetch($this->stmtId, 1000);
219
$result = oci_execute($this->stmtId, $this->commitMode) ? $this->stmtId : false;
220
$insertTableName = $this->parseInsertTableName($sql);
222
if ($result && $insertTableName !== '') {
223
$this->lastInsertedTableName = $insertTableName;
227
} catch (ErrorException $e) {
228
log_message('error', (string) $e);
230
if ($this->DBDebug) {
231
throw new DatabaseException($e->getMessage(), $e->getCode(), $e);
241
public function parseInsertTableName(string $sql): string
243
$commentStrippedSql = preg_replace(['/\/\*(.|\n)*?\*\//m', '/--.+/'], '', $sql);
244
$isInsertQuery = str_starts_with(strtoupper(ltrim($commentStrippedSql)), 'INSERT');
246
if (! $isInsertQuery) {
250
preg_match('/(?is)\b(?:into)\s+("?\w+"?)/', $commentStrippedSql, $match);
251
$tableName = $match[1] ?? '';
253
return str_starts_with($tableName, '"') ? trim($tableName, '"') : strtoupper($tableName);
259
public function affectedRows(): int
261
return oci_num_rows($this->stmtId);
269
protected function _listTables(bool $prefixLimit = false, ?string $tableName = null): string
271
$sql = 'SELECT "TABLE_NAME" FROM "USER_TABLES"';
273
if ($tableName !== null) {
274
return $sql . ' WHERE "TABLE_NAME" LIKE ' . $this->escape($tableName);
277
if ($prefixLimit !== false && $this->DBPrefix !== '') {
278
return $sql . ' WHERE "TABLE_NAME" LIKE \'' . $this->escapeLikeString($this->DBPrefix) . "%' "
279
. sprintf($this->likeEscapeStr, $this->likeEscapeChar);
288
protected function _listColumns(string $table = ''): string
290
if (str_contains($table, '.')) {
291
sscanf($table, '%[^.].%s', $owner, $table);
293
$owner = $this->username;
296
return 'SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS
297
WHERE UPPER(OWNER) = ' . $this->escape(strtoupper($owner)) . '
298
AND UPPER(TABLE_NAME) = ' . $this->escape(strtoupper($this->DBPrefix . $table));
308
protected function _fieldData(string $table): array
310
if (str_contains($table, '.')) {
311
sscanf($table, '%[^.].%s', $owner, $table);
313
$owner = $this->username;
316
$sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHAR_LENGTH, DATA_PRECISION, DATA_LENGTH, DATA_DEFAULT, NULLABLE
318
WHERE UPPER(OWNER) = ' . $this->escape(strtoupper($owner)) . '
319
AND UPPER(TABLE_NAME) = ' . $this->escape(strtoupper($table));
321
if (($query = $this->query($sql)) === false) {
322
throw new DatabaseException(lang('Database.failGetFieldData'));
324
$query = $query->getResultObject();
328
for ($i = 0, $c = count($query); $i < $c; $i++) {
329
$retval[$i] = new stdClass();
330
$retval[$i]->name = $query[$i]->COLUMN_NAME;
331
$retval[$i]->type = $query[$i]->DATA_TYPE;
333
$length = $query[$i]->CHAR_LENGTH > 0 ? $query[$i]->CHAR_LENGTH : $query[$i]->DATA_PRECISION;
334
$length ??= $query[$i]->DATA_LENGTH;
336
$retval[$i]->max_length = $length;
338
$retval[$i]->nullable = $query[$i]->NULLABLE === 'Y';
339
$retval[$i]->default = $query[$i]->DATA_DEFAULT;
352
protected function _indexData(string $table): array
354
if (str_contains($table, '.')) {
355
sscanf($table, '%[^.].%s', $owner, $table);
357
$owner = $this->username;
360
$sql = 'SELECT AIC.INDEX_NAME, UC.CONSTRAINT_TYPE, AIC.COLUMN_NAME '
361
. ' FROM ALL_IND_COLUMNS AIC '
362
. ' LEFT JOIN USER_CONSTRAINTS UC ON AIC.INDEX_NAME = UC.CONSTRAINT_NAME AND AIC.TABLE_NAME = UC.TABLE_NAME '
363
. 'WHERE AIC.TABLE_NAME = ' . $this->escape(strtolower($table)) . ' '
364
. 'AND AIC.TABLE_OWNER = ' . $this->escape(strtoupper($owner)) . ' '
365
. ' ORDER BY UC.CONSTRAINT_TYPE, AIC.COLUMN_POSITION';
367
if (($query = $this->query($sql)) === false) {
368
throw new DatabaseException(lang('Database.failGetIndexData'));
370
$query = $query->getResultObject();
378
foreach ($query as $row) {
379
if (isset($retVal[$row->INDEX_NAME])) {
380
$retVal[$row->INDEX_NAME]->fields[] = $row->COLUMN_NAME;
385
$retVal[$row->INDEX_NAME] = new stdClass();
386
$retVal[$row->INDEX_NAME]->name = $row->INDEX_NAME;
387
$retVal[$row->INDEX_NAME]->fields = [$row->COLUMN_NAME];
388
$retVal[$row->INDEX_NAME]->type = $constraintTypes[$row->CONSTRAINT_TYPE] ?? 'INDEX';
401
protected function _foreignKeyData(string $table): array
407
ccu.table_name foreign_table_name,
408
accu.column_name foreign_column_name,
410
FROM all_cons_columns acc
411
JOIN all_constraints ac ON acc.owner = ac.owner
412
AND acc.constraint_name = ac.constraint_name
413
JOIN all_constraints ccu ON ac.r_owner = ccu.owner
414
AND ac.r_constraint_name = ccu.constraint_name
415
JOIN all_cons_columns accu ON accu.constraint_name = ccu.constraint_name
416
AND accu.position = acc.position
417
AND accu.table_name = ccu.table_name
418
WHERE ac.constraint_type = ' . $this->escape('R') . '
419
AND acc.table_name = ' . $this->escape($table);
421
$query = $this->query($sql);
423
if ($query === false) {
424
throw new DatabaseException(lang('Database.failGetForeignKeyData'));
427
$query = $query->getResultObject();
430
foreach ($query as $row) {
431
$indexes[$row->CONSTRAINT_NAME]['constraint_name'] = $row->CONSTRAINT_NAME;
432
$indexes[$row->CONSTRAINT_NAME]['table_name'] = $row->TABLE_NAME;
433
$indexes[$row->CONSTRAINT_NAME]['column_name'][] = $row->COLUMN_NAME;
434
$indexes[$row->CONSTRAINT_NAME]['foreign_table_name'] = $row->FOREIGN_TABLE_NAME;
435
$indexes[$row->CONSTRAINT_NAME]['foreign_column_name'][] = $row->FOREIGN_COLUMN_NAME;
436
$indexes[$row->CONSTRAINT_NAME]['on_delete'] = $row->DELETE_RULE;
437
$indexes[$row->CONSTRAINT_NAME]['on_update'] = null;
438
$indexes[$row->CONSTRAINT_NAME]['match'] = null;
441
return $this->foreignKeyDataToObjects($indexes);
449
protected function _disableForeignKeyChecks()
454
(SELECT c.owner, c.table_name, c.constraint_name
455
FROM user_constraints c, user_tables t
456
WHERE c.table_name = t.table_name
457
AND c.status = 'ENABLED'
458
AND c.constraint_type = 'R'
459
AND t.iot_type IS NULL
460
ORDER BY c.constraint_type DESC)
462
dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint "' || c.constraint_name || '"');
473
protected function _enableForeignKeyChecks()
478
(SELECT c.owner, c.table_name, c.constraint_name
479
FROM user_constraints c, user_tables t
480
WHERE c.table_name = t.table_name
481
AND c.status = 'DISABLED'
482
AND c.constraint_type = 'R'
483
AND t.iot_type IS NULL
484
ORDER BY c.constraint_type DESC)
486
dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" enable constraint "' || c.constraint_name || '"');
497
public function getCursor()
499
return $this->cursorId = oci_new_cursor($this->connID);
516
public function storedProcedure(string $procedureName, array $params)
518
if ($procedureName === '') {
519
throw new DatabaseException(lang('Database.invalidArgument', [$procedureName]));
524
'BEGIN %s (' . substr(str_repeat(',%s', count($params)), 1) . '); END;',
526
...array_map(static fn ($row) => $row['name'], $params)
529
$this->resetStmtId = false;
530
$this->stmtId = oci_parse($this->connID, $sql);
531
$this->bindParams($params);
532
$result = $this->query($sql);
533
$this->resetStmtId = true;
545
protected function bindParams($params)
547
if (! is_array($params) || ! is_resource($this->stmtId)) {
551
foreach ($params as $param) {
556
$param['length'] ?? -1,
557
$param['type'] ?? SQLT_CHR
563
* Returns the last error code and message.
565
* Must return an array with keys 'code' and 'message':
567
* return ['code' => null, 'message' => null);
569
public function error(): array
572
' and 'message' keys, but it can return false
573
// if there was no error ....
574
$error = oci_error();
575
$resources = [$this->cursorId, $this->stmtId, $this->connID];
577
foreach ($resources as $resource) {
578
if (is_resource($resource)) {
579
$error = oci_error($resource);
584
return is_array($error)
592
public function insertID(): int
594
if (empty($this->lastInsertedTableName)) {
598
$indexs = $this->getIndexData($this->lastInsertedTableName);
599
$fieldDatas = $this->getFieldData($this->lastInsertedTableName);
601
if ($indexs === [] || $fieldDatas === []) {
605
$columnTypeList = array_column($fieldDatas, 'type', 'name');
606
$primaryColumnName = '';
608
foreach ($indexs as $index) {
609
if ($index->type !== 'PRIMARY' || count($index->fields) !== 1) {
613
$primaryColumnName = $this->protectIdentifiers($index->fields[0], false, false);
614
$primaryColumnType = $columnTypeList[$primaryColumnName];
616
if ($primaryColumnType !== 'NUMBER') {
617
$primaryColumnName = '';
621
if ($primaryColumnName === '') {
625
$query = $this->query('SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME = ?', [$this->lastInsertedTableName, $primaryColumnName])->getRow();
626
$lastInsertValue = str_replace('nextval', 'currval', $query->DATA_DEFAULT ?? '0');
627
$query = $this->query(sprintf('SELECT %s SEQ FROM DUAL', $lastInsertValue))->getRow();
629
return (int) ($query->SEQ ?? 0);
633
* Build a DSN from the provided parameters
637
protected function buildDSN()
639
if ($this->DSN !== '') {
643
// Legacy support for TNS in the hostname configuration field
644
$this->hostname = str_replace(["\n", "\r", "\t", ' '], '', $this->hostname);
646
if (preg_match($this->validDSNs['tns'], $this->hostname)) {
647
$this->DSN = $this->hostname;
652
$isEasyConnectableHostName = $this->hostname !== '' && ! str_contains($this->hostname, '/') && ! str_contains($this->hostname, ':');
653
$easyConnectablePort = ($this->port !== '') && ctype_digit((string) $this->port) ? ':' . $this->port : '';
654
$easyConnectableDatabase = $this->database !== '' ? '/' . ltrim($this->database, '/') : '';
656
if ($isEasyConnectableHostName && ($easyConnectablePort !== '' || $easyConnectableDatabase !== '')) {
657
/* If the hostname field isn't empty, doesn't contain
658
* ':' and/or '/' and if port and/or database aren't
659
* empty, then the hostname field is most likely indeed
660
* just a hostname. Therefore we'll try and build an
661
* Easy Connect string from these 3 settings, assuming
662
* that the database field is a service name.
664
$this->DSN = $this->hostname . $easyConnectablePort . $easyConnectableDatabase;
666
if (preg_match($this->validDSNs['ec'], $this->DSN)) {
671
/* At this point, we can only try and validate the hostname and
672
* database fields separately as DSNs.
674
if (preg_match($this->validDSNs['ec'], $this->hostname) || preg_match($this->validDSNs['in'], $this->hostname)) {
675
$this->DSN = $this->hostname;
680
$this->database = str_replace(["\n", "\r", "\t", ' '], '', $this->database);
682
foreach ($this->validDSNs as $regexp) {
683
if (preg_match($regexp, $this->database)) {
688
/* Well - OK, an empty string should work as well.
689
* PHP will try to use environment variables to
690
* determine which Oracle instance to connect to.
698
protected function _transBegin(): bool
700
$this->commitMode = OCI_NO_AUTO_COMMIT;
708
protected function _transCommit(): bool
710
$this->commitMode = OCI_COMMIT_ON_SUCCESS;
712
return oci_commit($this->connID);
716
* Rollback Transaction
718
protected function _transRollback(): bool
720
$this->commitMode = OCI_COMMIT_ON_SUCCESS;
722
return oci_rollback($this->connID);
726
* Returns the name of the current database being used.
728
public function getDatabase(): string
730
if (! empty($this->database)) {
731
return $this->database;
734
return $this->query('SELECT DEFAULT_TABLESPACE FROM USER_USERS')->getRow()->DEFAULT_TABLESPACE ?? '';
738
* Get the prefix of the function to access the DB.
740
protected function getDriverFunctionPrefix(): string