13806563199

Committed 12 Mar 2025 08:10AM UTC coverage: 84.56% (+0.006%) from 84.554%

Build # 13806563199

Build Type

push

github

Committed by

web-flow

Commit Message

fix: `getVersion()` for OCI8 and SQLSRV drivers (#9471)

* fix: getVersion() for OCI8 driver when no connection is established

* phpstan - regenerate baseline

* fix: SQLSRV getVersion() when no connection is established

Run Details

5 of 7 new or added lines in 2 files covered. (71.43%)

1 existing line in 1 file now uncovered.

20845 of 24651 relevant lines covered (84.56%)

191.33 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

75.8

/system/Database/OCI8/Connection.php

<?php

declare(strict_types=1);

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

namespace CodeIgniter\Database\OCI8;

use CodeIgniter\Database\BaseConnection;
use CodeIgniter\Database\Exceptions\DatabaseException;
use CodeIgniter\Database\Query;
use CodeIgniter\Database\TableName;
use ErrorException;
use stdClass;

/**
 * Connection for OCI8
 *
 * @extends BaseConnection<resource, resource>
 */
class Connection extends BaseConnection
{
    /**
     * Database driver
     *
     * @var string
     */
    protected $DBDriver = 'OCI8';

    /**
     * Identifier escape character
     *
     * @var string
     */
    public $escapeChar = '"';

    /**
     * List of reserved identifiers
     *
     * Identifiers that must NOT be escaped.
     *
     * @var array
     */
    protected $reservedIdentifiers = [
        '*',
        'rownum',
    ];

    protected $validDSNs = [
        // TNS
        'tns' => '/^\(DESCRIPTION=(\(.+\)){2,}\)$/',
        // Easy Connect string (Oracle 10g+).
        // https://docs.oracle.com/en/database/oracle/oracle-database/23/netag/configuring-naming-methods.html#GUID-36F3A17D-843C-490A-8A23-FB0FE005F8E8
        // [//]host[:port][/[service_name][:server_type][/instance_name]]
        'ec' => '/^
            (\/\/)?
            (\[)?[a-z0-9.:_-]+(\])? # Host or IP address
            (:[1-9][0-9]{0,4})?     # Port
            (
                (\/)
                ([a-z0-9.$_]+)?     # Service name
                (:[a-z]+)?          # Server type
                (\/[a-z0-9$_]+)?    # Instance name
            )?
        $/ix',
        // Instance name (defined in tnsnames.ora)
        'in' => '/^[a-z0-9$_]+$/i',
    ];

    /**
     * Reset $stmtId flag
     *
     * Used by storedProcedure() to prevent execute() from
     * re-setting the statement ID.
     */
    protected $resetStmtId = true;

    /**
     * Statement ID
     *
     * @var resource
     */
    protected $stmtId;

    /**
     * Commit mode flag
     *
     * @used-by PreparedQuery::_execute()
     *
     * @var int
     */
    public $commitMode = OCI_COMMIT_ON_SUCCESS;

    /**
     * Cursor ID
     *
     * @var resource
     */
    protected $cursorId;

    /**
     * Latest inserted table name.
     *
     * @used-by PreparedQuery::_execute()
     *
     * @var string|null
     */
    public $lastInsertedTableName;

    /**
     * confirm DSN format.
     */
    private function isValidDSN(): bool
    {
        if ($this->DSN === null || $this->DSN === '') {
            return false;
        }

        foreach ($this->validDSNs as $regexp) {
            if (preg_match($regexp, $this->DSN)) {
                return true;
            }
        }

        return false;
    }

    /**
     * Connect to the database.
     *
     * @return false|resource
     */
    public function connect(bool $persistent = false)
    {
        if (! $this->isValidDSN()) {
            $this->buildDSN();
        }

        $func = $persistent ? 'oci_pconnect' : 'oci_connect';

        return ($this->charset === '')
            ? $func($this->username, $this->password, $this->DSN)
            : $func($this->username, $this->password, $this->DSN, $this->charset);
    }

    /**
     * Keep or establish the connection if no queries have been sent for
     * a length of time exceeding the server's idle timeout.
     *
     * @return void
     */
    public function reconnect()
    {
    }

    /**
     * Close the database connection.
     *
     * @return void
     */
    protected function _close()
    {
        if (is_resource($this->cursorId)) {
            oci_free_statement($this->cursorId);
        }
        if (is_resource($this->stmtId)) {
            oci_free_statement($this->stmtId);
        }
        oci_close($this->connID);
    }

    /**
     * Select a specific database table to use.
     */
    public function setDatabase(string $databaseName): bool
    {
        return false;
    }

    /**
     * Returns a string containing the version of the database being used.
     */
    public function getVersion(): string
    {
        if (isset($this->dataCache['version'])) {
            return $this->dataCache['version'];
        }

        if ($this->connID === false) {
            $this->initialize();
        }

        if (($versionString = oci_server_version($this->connID)) === false) {
            return '';
        }

        if (preg_match('#Release\s(\d+(?:\.\d+)+)#', $versionString, $match)) {
            return $this->dataCache['version'] = $match[1];
        }

        return '';
    }

    /**
     * Executes the query against the database.
     *
     * @return false|resource
     */
    protected function execute(string $sql)
    {
        try {
            if ($this->resetStmtId === true) {
                $this->stmtId = oci_parse($this->connID, $sql);
            }

            oci_set_prefetch($this->stmtId, 1000);

            $result          = oci_execute($this->stmtId, $this->commitMode) ? $this->stmtId : false;
            $insertTableName = $this->parseInsertTableName($sql);

            if ($result && $insertTableName !== '') {
                $this->lastInsertedTableName = $insertTableName;
            }

            return $result;
        } catch (ErrorException $e) {
            log_message('error', (string) $e);

            if ($this->DBDebug) {
                throw new DatabaseException($e->getMessage(), $e->getCode(), $e);
            }
        }

        return false;
    }

    /**
     * Get the table name for the insert statement from sql.
     */
    public function parseInsertTableName(string $sql): string
    {
        $commentStrippedSql = preg_replace(['/\/\*(.|\n)*?\*\//m', '/--.+/'], '', $sql);
        $isInsertQuery      = str_starts_with(strtoupper(ltrim($commentStrippedSql)), 'INSERT');

        if (! $isInsertQuery) {
            return '';
        }

        preg_match('/(?is)\b(?:into)\s+("?\w+"?)/', $commentStrippedSql, $match);
        $tableName = $match[1] ?? '';

        return str_starts_with($tableName, '"') ? trim($tableName, '"') : strtoupper($tableName);
    }

    /**
     * Returns the total number of rows affected by this query.
     */
    public function affectedRows(): int
    {
        return oci_num_rows($this->stmtId);
    }

    /**
     * Generates the SQL for listing tables in a platform-dependent manner.
     *
     * @param string|null $tableName If $tableName is provided will return only this table if exists.
     */
    protected function _listTables(bool $prefixLimit = false, ?string $tableName = null): string
    {
        $sql = 'SELECT "TABLE_NAME" FROM "USER_TABLES"';

        if ($tableName !== null) {
            return $sql . ' WHERE "TABLE_NAME" LIKE ' . $this->escape($tableName);
        }

        if ($prefixLimit && $this->DBPrefix !== '') {
            return $sql . ' WHERE "TABLE_NAME" LIKE \'' . $this->escapeLikeString($this->DBPrefix) . "%' "
                    . sprintf($this->likeEscapeStr, $this->likeEscapeChar);
        }

        return $sql;
    }

    /**
     * Generates a platform-specific query string so that the column names can be fetched.
     *
     * @param string|TableName $table
     */
    protected function _listColumns($table = ''): string
    {
        if ($table instanceof TableName) {
            $tableName = $this->escape(strtoupper($table->getActualTableName()));
            $owner     = $this->username;
        } elseif (str_contains($table, '.')) {
            sscanf($table, '%[^.].%s', $owner, $tableName);
            $tableName = $this->escape(strtoupper($this->DBPrefix . $tableName));
        } else {
            $owner     = $this->username;
            $tableName = $this->escape(strtoupper($this->DBPrefix . $table));
        }

        return 'SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS
                        WHERE UPPER(OWNER) = ' . $this->escape(strtoupper($owner)) . '
                                AND UPPER(TABLE_NAME) = ' . $tableName;
    }

    /**
     * Returns an array of objects with field data
     *
     * @return list<stdClass>
     *
     * @throws DatabaseException
     */
    protected function _fieldData(string $table): array
    {
        if (str_contains($table, '.')) {
            sscanf($table, '%[^.].%s', $owner, $table);
        } else {
            $owner = $this->username;
        }

        $sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHAR_LENGTH, DATA_PRECISION, DATA_LENGTH, DATA_DEFAULT, NULLABLE
                        FROM ALL_TAB_COLUMNS
                        WHERE UPPER(OWNER) = ' . $this->escape(strtoupper($owner)) . '
                                AND UPPER(TABLE_NAME) = ' . $this->escape(strtoupper($table));

        if (($query = $this->query($sql)) === false) {
            throw new DatabaseException(lang('Database.failGetFieldData'));
        }
        $query = $query->getResultObject();

        $retval = [];

        for ($i = 0, $c = count($query); $i < $c; $i++) {
            $retval[$i]       = new stdClass();
            $retval[$i]->name = $query[$i]->COLUMN_NAME;
            $retval[$i]->type = $query[$i]->DATA_TYPE;

            $length = $query[$i]->CHAR_LENGTH > 0 ? $query[$i]->CHAR_LENGTH : $query[$i]->DATA_PRECISION;
            $length ??= $query[$i]->DATA_LENGTH;

            $retval[$i]->max_length = $length;

            $retval[$i]->nullable = $query[$i]->NULLABLE === 'Y';
            $retval[$i]->default  = $query[$i]->DATA_DEFAULT;
        }

        return $retval;
    }

    /**
     * Returns an array of objects with index data
     *
     * @return array<string, stdClass>
     *
     * @throws DatabaseException
     */
    protected function _indexData(string $table): array
    {
        if (str_contains($table, '.')) {
            sscanf($table, '%[^.].%s', $owner, $table);
        } else {
            $owner = $this->username;
        }

        $sql = 'SELECT AIC.INDEX_NAME, UC.CONSTRAINT_TYPE, AIC.COLUMN_NAME '
            . ' FROM ALL_IND_COLUMNS AIC '
            . ' LEFT JOIN USER_CONSTRAINTS UC ON AIC.INDEX_NAME = UC.CONSTRAINT_NAME AND AIC.TABLE_NAME = UC.TABLE_NAME '
            . 'WHERE AIC.TABLE_NAME = ' . $this->escape(strtolower($table)) . ' '
            . 'AND AIC.TABLE_OWNER = ' . $this->escape(strtoupper($owner)) . ' '
            . ' ORDER BY UC.CONSTRAINT_TYPE, AIC.COLUMN_POSITION';

        if (($query = $this->query($sql)) === false) {
            throw new DatabaseException(lang('Database.failGetIndexData'));
        }
        $query = $query->getResultObject();

        $retVal          = [];
        $constraintTypes = [
            'P' => 'PRIMARY',
            'U' => 'UNIQUE',
        ];

        foreach ($query as $row) {
            if (isset($retVal[$row->INDEX_NAME])) {
                $retVal[$row->INDEX_NAME]->fields[] = $row->COLUMN_NAME;

                continue;
            }

            $retVal[$row->INDEX_NAME]         = new stdClass();
            $retVal[$row->INDEX_NAME]->name   = $row->INDEX_NAME;
            $retVal[$row->INDEX_NAME]->fields = [$row->COLUMN_NAME];
            $retVal[$row->INDEX_NAME]->type   = $constraintTypes[$row->CONSTRAINT_TYPE] ?? 'INDEX';
        }

        return $retVal;
    }

    /**
     * Returns an array of objects with Foreign key data
     *
     * @return array<string, stdClass>
     *
     * @throws DatabaseException
     */
    protected function _foreignKeyData(string $table): array
    {
        $sql = 'SELECT
                acc.constraint_name,
                acc.table_name,
                acc.column_name,
                ccu.table_name foreign_table_name,
                accu.column_name foreign_column_name,
                ac.delete_rule
                FROM all_cons_columns acc
                JOIN all_constraints ac ON acc.owner = ac.owner
                AND acc.constraint_name = ac.constraint_name
                JOIN all_constraints ccu ON ac.r_owner = ccu.owner
                AND ac.r_constraint_name = ccu.constraint_name
                JOIN all_cons_columns accu ON accu.constraint_name = ccu.constraint_name
                AND accu.position = acc.position
                AND accu.table_name = ccu.table_name
                WHERE ac.constraint_type = ' . $this->escape('R') . '
                AND acc.table_name = ' . $this->escape($table);

        $query = $this->query($sql);

        if ($query === false) {
            throw new DatabaseException(lang('Database.failGetForeignKeyData'));
        }

        $query   = $query->getResultObject();
        $indexes = [];

        foreach ($query as $row) {
            $indexes[$row->CONSTRAINT_NAME]['constraint_name']       = $row->CONSTRAINT_NAME;
            $indexes[$row->CONSTRAINT_NAME]['table_name']            = $row->TABLE_NAME;
            $indexes[$row->CONSTRAINT_NAME]['column_name'][]         = $row->COLUMN_NAME;
            $indexes[$row->CONSTRAINT_NAME]['foreign_table_name']    = $row->FOREIGN_TABLE_NAME;
            $indexes[$row->CONSTRAINT_NAME]['foreign_column_name'][] = $row->FOREIGN_COLUMN_NAME;
            $indexes[$row->CONSTRAINT_NAME]['on_delete']             = $row->DELETE_RULE;
            $indexes[$row->CONSTRAINT_NAME]['on_update']             = null;
            $indexes[$row->CONSTRAINT_NAME]['match']                 = null;
        }

        return $this->foreignKeyDataToObjects($indexes);
    }

    /**
     * Returns platform-specific SQL to disable foreign key checks.
     *
     * @return string
     */
    protected function _disableForeignKeyChecks()
    {
        return <<<'SQL'
            BEGIN
              FOR c IN
              (SELECT c.owner, c.table_name, c.constraint_name
               FROM user_constraints c, user_tables t
               WHERE c.table_name = t.table_name
               AND c.status = 'ENABLED'
               AND c.constraint_type = 'R'
               AND t.iot_type IS NULL
               ORDER BY c.constraint_type DESC)
              LOOP
                dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint "' || c.constraint_name || '"');
              END LOOP;
            END;
            SQL;
    }

    /**
     * Returns platform-specific SQL to enable foreign key checks.
     *
     * @return string
     */
    protected function _enableForeignKeyChecks()
    {
        return <<<'SQL'
            BEGIN
              FOR c IN
              (SELECT c.owner, c.table_name, c.constraint_name
               FROM user_constraints c, user_tables t
               WHERE c.table_name = t.table_name
               AND c.status = 'DISABLED'
               AND c.constraint_type = 'R'
               AND t.iot_type IS NULL
               ORDER BY c.constraint_type DESC)
              LOOP
                dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" enable constraint "' || c.constraint_name || '"');
              END LOOP;
            END;
            SQL;
    }

    /**
     * Get cursor. Returns a cursor from the database
     *
     * @return resource
     */
    public function getCursor()
    {
        return $this->cursorId = oci_new_cursor($this->connID);
    }

    /**
     * Executes a stored procedure
     *
     * @param string $procedureName procedure name to execute
     * @param array  $params        params array keys
     *                              KEY      OPTIONAL  NOTES
     *                              name     no        the name of the parameter should be in :<param_name> format
     *                              value    no        the value of the parameter.  If this is an OUT or IN OUT parameter,
     *                              this should be a reference to a variable
     *                              type     yes       the type of the parameter
     *                              length   yes       the max size of the parameter
     *
     * @return bool|Query|Result
     */
    public function storedProcedure(string $procedureName, array $params)
    {
        if ($procedureName === '') {
            throw new DatabaseException(lang('Database.invalidArgument', [$procedureName]));
        }

        // Build the query string
        $sql = sprintf(
            'BEGIN %s (' . substr(str_repeat(',%s', count($params)), 1) . '); END;',
            $procedureName,
            ...array_map(static fn ($row) => $row['name'], $params),
        );

        $this->resetStmtId = false;
        $this->stmtId      = oci_parse($this->connID, $sql);
        $this->bindParams($params);
        $result            = $this->query($sql);
        $this->resetStmtId = true;

        return $result;
    }

    /**
     * Bind parameters
     *
     * @param array $params
     *
     * @return void
     */
    protected function bindParams($params)
    {
        if (! is_array($params) || ! is_resource($this->stmtId)) {
            return;
        }

        foreach ($params as $param) {
            oci_bind_by_name(
                $this->stmtId,
                $param['name'],
                $param['value'],
                $param['length'] ?? -1,
                $param['type'] ?? SQLT_CHR,
            );
        }
    }

    /**
     * Returns the last error code and message.
     *
     * Must return an array with keys 'code' and 'message':
     *
     *  return ['code' => null, 'message' => null);
     */
    public function error(): array
    {
        // oci_error() returns an array that already contains
        // 'code' and 'message' keys, but it can return false
        // if there was no error ....
        $error     = oci_error();
        $resources = [$this->cursorId, $this->stmtId, $this->connID];

        foreach ($resources as $resource) {
            if (is_resource($resource)) {
                $error = oci_error($resource);
                break;
            }
        }

        return is_array($error)
            ? $error
            : [
                'code'    => '',
                'message' => '',
            ];
    }

    public function insertID(): int
    {
        if (empty($this->lastInsertedTableName)) {
            return 0;
        }

        $indexs     = $this->getIndexData($this->lastInsertedTableName);
        $fieldDatas = $this->getFieldData($this->lastInsertedTableName);

        if ($indexs === [] || $fieldDatas === []) {
            return 0;
        }

        $columnTypeList    = array_column($fieldDatas, 'type', 'name');
        $primaryColumnName = '';

        foreach ($indexs as $index) {
            if ($index->type !== 'PRIMARY' || count($index->fields) !== 1) {
                continue;
            }

            $primaryColumnName = $this->protectIdentifiers($index->fields[0], false, false);
            $primaryColumnType = $columnTypeList[$primaryColumnName];

            if ($primaryColumnType !== 'NUMBER') {
                $primaryColumnName = '';
            }
        }

        if ($primaryColumnName === '') {
            return 0;
        }

        $query           = $this->query('SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME = ?', [$this->lastInsertedTableName, $primaryColumnName])->getRow();
        $lastInsertValue = str_replace('nextval', 'currval', $query->DATA_DEFAULT ?? '0');
        $query           = $this->query(sprintf('SELECT %s SEQ FROM DUAL', $lastInsertValue))->getRow();

        return (int) ($query->SEQ ?? 0);
    }

    /**
     * Build a DSN from the provided parameters
     *
     * @return void
     */
    protected function buildDSN()
    {
        if ($this->DSN !== '') {
            $this->DSN = '';
        }

        // Legacy support for TNS in the hostname configuration field
        $this->hostname = str_replace(["\n", "\r", "\t", ' '], '', $this->hostname);

        if (preg_match($this->validDSNs['tns'], $this->hostname)) {
            $this->DSN = $this->hostname;

            return;
        }

        $isEasyConnectableHostName = $this->hostname !== '' && ! str_contains($this->hostname, '/') && ! str_contains($this->hostname, ':');
        $easyConnectablePort       = ($this->port !== '') && ctype_digit((string) $this->port) ? ':' . $this->port : '';
        $easyConnectableDatabase   = $this->database !== '' ? '/' . ltrim($this->database, '/') : '';

        if ($isEasyConnectableHostName && ($easyConnectablePort !== '' || $easyConnectableDatabase !== '')) {
            /* If the hostname field isn't empty, doesn't contain
             * ':' and/or '/' and if port and/or database aren't
             * empty, then the hostname field is most likely indeed
             * just a hostname. Therefore we'll try and build an
             * Easy Connect string from these 3 settings, assuming
             * that the database field is a service name.
             */
            $this->DSN = $this->hostname . $easyConnectablePort . $easyConnectableDatabase;

            if (preg_match($this->validDSNs['ec'], $this->DSN)) {
                return;
            }
        }

        /* At this point, we can only try and validate the hostname and
         * database fields separately as DSNs.
         */
        if (preg_match($this->validDSNs['ec'], $this->hostname) || preg_match($this->validDSNs['in'], $this->hostname)) {
            $this->DSN = $this->hostname;

            return;
        }

        $this->database = str_replace(["\n", "\r", "\t", ' '], '', $this->database);

        foreach ($this->validDSNs as $regexp) {
            if (preg_match($regexp, $this->database)) {
                return;
            }
        }

        /* Well - OK, an empty string should work as well.
         * PHP will try to use environment variables to
         * determine which Oracle instance to connect to.
         */
        $this->DSN = '';
    }

    /**
     * Begin Transaction
     */
    protected function _transBegin(): bool
    {
        $this->commitMode = OCI_NO_AUTO_COMMIT;

        return true;
    }

    /**
     * Commit Transaction
     */
    protected function _transCommit(): bool
    {
        $this->commitMode = OCI_COMMIT_ON_SUCCESS;

        return oci_commit($this->connID);
    }

    /**
     * Rollback Transaction
     */
    protected function _transRollback(): bool
    {
        $this->commitMode = OCI_COMMIT_ON_SUCCESS;

        return oci_rollback($this->connID);
    }

    /**
     * Returns the name of the current database being used.
     */
    public function getDatabase(): string
    {
        if (! empty($this->database)) {
            return $this->database;
        }

        return $this->query('SELECT DEFAULT_TABLESPACE FROM USER_USERS')->getRow()->DEFAULT_TABLESPACE ?? '';
    }

    /**
     * Get the prefix of the function to access the DB.
     */
    protected function getDriverFunctionPrefix(): string
    {
        return 'oci_';
    }
}

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\BaseConnection;
17	use CodeIgniter\Database\Exceptions\DatabaseException;
18	use CodeIgniter\Database\Query;
19	use CodeIgniter\Database\TableName;
20	use ErrorException;
21	use stdClass;
22
23	/**
24	* Connection for OCI8
25	*
26	* @extends BaseConnection<resource, resource>
27	*/
28	class Connection extends BaseConnection
29	{
30	/**
31	* Database driver
32	*
33	* @var string
34	*/
35	protected $DBDriver = 'OCI8';
36
37	/**
38	* Identifier escape character
39	*
40	* @var string
41	*/
42	public $escapeChar = '"';
43
44	/**
45	* List of reserved identifiers
46	*
47	* Identifiers that must NOT be escaped.
48	*
49	* @var array
50	*/
51	protected $reservedIdentifiers = [
52	'*',
53	'rownum',
54	];
55
56	protected $validDSNs = [
57	// TNS
58	'tns' => '/^\(DESCRIPTION=(\(.+\)){2,}\)$/',
59	// Easy Connect string (Oracle 10g+).
60	// https://docs.oracle.com/en/database/oracle/oracle-database/23/netag/configuring-naming-methods.html#GUID-36F3A17D-843C-490A-8A23-FB0FE005F8E8
61	// [//]host[:port][/[service_name][:server_type][/instance_name]]
62	'ec' => '/^
63	(\/\/)?
64	(\[)?[a-z0-9.:_-]+(\])? # Host or IP address
65	(:[1-9][0-9]{0,4})? # Port
66	(
67	(\/)
68	([a-z0-9.$_]+)? # Service name
69	(:[a-z]+)? # Server type
70	(\/[a-z0-9$_]+)? # Instance name
71	)?
72	$/ix',
73	// Instance name (defined in tnsnames.ora)
74	'in' => '/^[a-z0-9$_]+$/i',
75	];
76
77	/**
78	* Reset $stmtId flag
79	*
80	* Used by storedProcedure() to prevent execute() from
81	* re-setting the statement ID.
82	*/
83	protected $resetStmtId = true;
84
85	/**
86	* Statement ID
87	*
88	* @var resource
89	*/
90	protected $stmtId;
91
92	/**
93	* Commit mode flag
94	*
95	* @used-by PreparedQuery::_execute()
96	*
97	* @var int
98	*/
99	public $commitMode = OCI_COMMIT_ON_SUCCESS;
100
101	/**
102	* Cursor ID
103	*
104	* @var resource
105	*/
106	protected $cursorId;
107
108	/**
109	* Latest inserted table name.
110	*
111	* @used-by PreparedQuery::_execute()
112	*
113	* @var string\|null
114	*/
115	public $lastInsertedTableName;
116
117	/**
118	* confirm DSN format.
119	*/
120	private function isValidDSN(): bool
121	{
122	if ($this->DSN === null \|\| $this->DSN === '') {	29✔
123	return false;	×
124	}
125
126	foreach ($this->validDSNs as $regexp) {	29✔
127	if (preg_match($regexp, $this->DSN)) {	29✔
128	return true;	29✔
129	}
130	}
131
132	return false;	×
133	}
134
135	/**
136	* Connect to the database.
137	*
138	* @return false\|resource
139	*/
140	public function connect(bool $persistent = false)
141	{
142	if (! $this->isValidDSN()) {	20✔
143	$this->buildDSN();	×
144	}
145
146	$func = $persistent ? 'oci_pconnect' : 'oci_connect';	20✔
147
148	return ($this->charset === '')	20✔
149	? $func($this->username, $this->password, $this->DSN)	×
150	: $func($this->username, $this->password, $this->DSN, $this->charset);	20✔
151	}
152
153	/**
154	* Keep or establish the connection if no queries have been sent for
155	* a length of time exceeding the server's idle timeout.
156	*
157	* @return void
158	*/
159	public function reconnect()
160	{
161	}	×
162
163	/**
164	* Close the database connection.
165	*
166	* @return void
167	*/
168	protected function _close()
169	{
170	if (is_resource($this->cursorId)) {	×
171	oci_free_statement($this->cursorId);	×
172	}
173	if (is_resource($this->stmtId)) {	×
174	oci_free_statement($this->stmtId);	×
175	}
176	oci_close($this->connID);	×
177	}
178
179	/**
180	* Select a specific database table to use.
181	*/
182	public function setDatabase(string $databaseName): bool
183	{
184	return false;	×
185	}
186
187	/**
188	* Returns a string containing the version of the database being used.
189	*/
190	public function getVersion(): string
191	{
192	if (isset($this->dataCache['version'])) {	603✔
193	return $this->dataCache['version'];	603✔
194	}
195
196	if ($this->connID === false) {	29✔
NEW 197	$this->initialize();	×
198	}
199
200	if (($versionString = oci_server_version($this->connID)) === false) {	29✔
UNCOV 201	return '';	×
202	}
203
204	if (preg_match('#Release\s(\d+(?:\.\d+)+)#', $versionString, $match)) {	29✔
205	return $this->dataCache['version'] = $match[1];	29✔
206	}
207
208	return '';	×
209	}
210
211	/**
212	* Executes the query against the database.
213	*
214	* @return false\|resource
215	*/
216	protected function execute(string $sql)
217	{
218	try {
219	if ($this->resetStmtId === true) {	668✔
220	$this->stmtId = oci_parse($this->connID, $sql);	668✔
221	}
222
223	oci_set_prefetch($this->stmtId, 1000);	668✔
224
225	$result = oci_execute($this->stmtId, $this->commitMode) ? $this->stmtId : false;	668✔
226	$insertTableName = $this->parseInsertTableName($sql);	668✔
227
228	if ($result && $insertTableName !== '') {	668✔
229	$this->lastInsertedTableName = $insertTableName;	633✔
230	}
231
232	return $result;	668✔
233	} catch (ErrorException $e) {	29✔
234	log_message('error', (string) $e);	29✔
235
236	if ($this->DBDebug) {	29✔
237	throw new DatabaseException($e->getMessage(), $e->getCode(), $e);	12✔
238	}
239	}
240
241	return false;	17✔
242	}
243
244	/**
245	* Get the table name for the insert statement from sql.
246	*/
247	public function parseInsertTableName(string $sql): string
248	{
249	$commentStrippedSql = preg_replace(['/\/\(.\|\n)?\*\//m', '/--.+/'], '', $sql);	668✔
250	$isInsertQuery = str_starts_with(strtoupper(ltrim($commentStrippedSql)), 'INSERT');	668✔
251
252	if (! $isInsertQuery) {	668✔
253	return '';	668✔
254	}
255
256	preg_match('/(?is)\b(?:into)\s+("?\w+"?)/', $commentStrippedSql, $match);	633✔
257	$tableName = $match[1] ?? '';	633✔
258
259	return str_starts_with($tableName, '"') ? trim($tableName, '"') : strtoupper($tableName);	633✔
260	}
261
262	/**
263	* Returns the total number of rows affected by this query.
264	*/
265	public function affectedRows(): int
266	{
267	return oci_num_rows($this->stmtId);	49✔
268	}
269
270	/**
271	* Generates the SQL for listing tables in a platform-dependent manner.
272	*
273	* @param string\|null $tableName If $tableName is provided will return only this table if exists.
274	*/
275	protected function _listTables(bool $prefixLimit = false, ?string $tableName = null): string
276	{
277	$sql = 'SELECT "TABLE_NAME" FROM "USER_TABLES"';	591✔
278
279	if ($tableName !== null) {	591✔
280	return $sql . ' WHERE "TABLE_NAME" LIKE ' . $this->escape($tableName);	591✔
281	}
282
283	if ($prefixLimit && $this->DBPrefix !== '') {	30✔
284	return $sql . ' WHERE "TABLE_NAME" LIKE \'' . $this->escapeLikeString($this->DBPrefix) . "%' "	×
285	. sprintf($this->likeEscapeStr, $this->likeEscapeChar);	×
286	}
287
288	return $sql;	30✔
289	}
290
291	/**
292	* Generates a platform-specific query string so that the column names can be fetched.
293	*
294	* @param string\|TableName $table
295	*/
296	protected function _listColumns($table = ''): string
297	{
298	if ($table instanceof TableName) {	8✔
299	$tableName = $this->escape(strtoupper($table->getActualTableName()));	2✔
300	$owner = $this->username;	2✔
301	} elseif (str_contains($table, '.')) {	6✔
302	sscanf($table, '%[^.].%s', $owner, $tableName);	×
303	$tableName = $this->escape(strtoupper($this->DBPrefix . $tableName));	×
304	} else {
305	$owner = $this->username;	6✔
306	$tableName = $this->escape(strtoupper($this->DBPrefix . $table));	6✔
307	}
308
309	return 'SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS	8✔
310	WHERE UPPER(OWNER) = ' . $this->escape(strtoupper($owner)) . '	8✔
311	AND UPPER(TABLE_NAME) = ' . $tableName;	8✔
312	}
313
314	/**
315	* Returns an array of objects with field data
316	*
317	* @return list<stdClass>
318	*
319	* @throws DatabaseException
320	*/
321	protected function _fieldData(string $table): array
322	{
323	if (str_contains($table, '.')) {	108✔
324	sscanf($table, '%[^.].%s', $owner, $table);	×
325	} else {
326	$owner = $this->username;	108✔
327	}
328
329	$sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHAR_LENGTH, DATA_PRECISION, DATA_LENGTH, DATA_DEFAULT, NULLABLE	108✔
330	FROM ALL_TAB_COLUMNS
331	WHERE UPPER(OWNER) = ' . $this->escape(strtoupper($owner)) . '	108✔
332	AND UPPER(TABLE_NAME) = ' . $this->escape(strtoupper($table));	108✔
333
334	if (($query = $this->query($sql)) === false) {	108✔
335	throw new DatabaseException(lang('Database.failGetFieldData'));	×
336	}
337	$query = $query->getResultObject();	108✔
338
339	$retval = [];	108✔
340
341	for ($i = 0, $c = count($query); $i < $c; $i++) {	108✔
342	$retval[$i] = new stdClass();	108✔
343	$retval[$i]->name = $query[$i]->COLUMN_NAME;	108✔
344	$retval[$i]->type = $query[$i]->DATA_TYPE;	108✔
345
346	$length = $query[$i]->CHAR_LENGTH > 0 ? $query[$i]->CHAR_LENGTH : $query[$i]->DATA_PRECISION;	108✔
347	$length ??= $query[$i]->DATA_LENGTH;	108✔
348
349	$retval[$i]->max_length = $length;	108✔
350
351	$retval[$i]->nullable = $query[$i]->NULLABLE === 'Y';	108✔
352	$retval[$i]->default = $query[$i]->DATA_DEFAULT;	108✔
353	}
354
355	return $retval;	108✔
356	}
357
358	/**
359	* Returns an array of objects with index data
360	*
361	* @return array<string, stdClass>
362	*
363	* @throws DatabaseException
364	*/
365	protected function _indexData(string $table): array
366	{
367	if (str_contains($table, '.')) {	127✔
368	sscanf($table, '%[^.].%s', $owner, $table);	×
369	} else {
370	$owner = $this->username;	127✔
371	}
372
373	$sql = 'SELECT AIC.INDEX_NAME, UC.CONSTRAINT_TYPE, AIC.COLUMN_NAME '	127✔
374	. ' FROM ALL_IND_COLUMNS AIC '	127✔
375	. ' LEFT JOIN USER_CONSTRAINTS UC ON AIC.INDEX_NAME = UC.CONSTRAINT_NAME AND AIC.TABLE_NAME = UC.TABLE_NAME '	127✔
376	. 'WHERE AIC.TABLE_NAME = ' . $this->escape(strtolower($table)) . ' '	127✔
377	. 'AND AIC.TABLE_OWNER = ' . $this->escape(strtoupper($owner)) . ' '	127✔
378	. ' ORDER BY UC.CONSTRAINT_TYPE, AIC.COLUMN_POSITION';	127✔
379
380	if (($query = $this->query($sql)) === false) {	127✔
381	throw new DatabaseException(lang('Database.failGetIndexData'));	×
382	}
383	$query = $query->getResultObject();	127✔
384
385	$retVal = [];	127✔
386	$constraintTypes = [	127✔
387	'P' => 'PRIMARY',	127✔
388	'U' => 'UNIQUE',	127✔
389	];	127✔
390
391	foreach ($query as $row) {	127✔
392	if (isset($retVal[$row->INDEX_NAME])) {	125✔
393	$retVal[$row->INDEX_NAME]->fields[] = $row->COLUMN_NAME;	5✔
394
395	continue;	5✔
396	}
397
398	$retVal[$row->INDEX_NAME] = new stdClass();	125✔
399	$retVal[$row->INDEX_NAME]->name = $row->INDEX_NAME;	125✔
400	$retVal[$row->INDEX_NAME]->fields = [$row->COLUMN_NAME];	125✔
401	$retVal[$row->INDEX_NAME]->type = $constraintTypes[$row->CONSTRAINT_TYPE] ?? 'INDEX';	125✔
402	}
403
404	return $retVal;	127✔
405	}
406
407	/**
408	* Returns an array of objects with Foreign key data
409	*
410	* @return array<string, stdClass>
411	*
412	* @throws DatabaseException
413	*/
414	protected function _foreignKeyData(string $table): array
415	{
416	$sql = 'SELECT	5✔
417	acc.constraint_name,
418	acc.table_name,
419	acc.column_name,
420	ccu.table_name foreign_table_name,
421	accu.column_name foreign_column_name,
422	ac.delete_rule
423	FROM all_cons_columns acc
424	JOIN all_constraints ac ON acc.owner = ac.owner
425	AND acc.constraint_name = ac.constraint_name
426	JOIN all_constraints ccu ON ac.r_owner = ccu.owner
427	AND ac.r_constraint_name = ccu.constraint_name
428	JOIN all_cons_columns accu ON accu.constraint_name = ccu.constraint_name
429	AND accu.position = acc.position
430	AND accu.table_name = ccu.table_name
431	WHERE ac.constraint_type = ' . $this->escape('R') . '	5✔
432	AND acc.table_name = ' . $this->escape($table);	5✔
433
434	$query = $this->query($sql);	5✔
435
436	if ($query === false) {	5✔
437	throw new DatabaseException(lang('Database.failGetForeignKeyData'));	×
438	}
439
440	$query = $query->getResultObject();	5✔
441	$indexes = [];	5✔
442
443	foreach ($query as $row) {	5✔
444	$indexes[$row->CONSTRAINT_NAME]['constraint_name'] = $row->CONSTRAINT_NAME;	4✔
445	$indexes[$row->CONSTRAINT_NAME]['table_name'] = $row->TABLE_NAME;	4✔
446	$indexes[$row->CONSTRAINT_NAME]['column_name'][] = $row->COLUMN_NAME;	4✔
447	$indexes[$row->CONSTRAINT_NAME]['foreign_table_name'] = $row->FOREIGN_TABLE_NAME;	4✔
448	$indexes[$row->CONSTRAINT_NAME]['foreign_column_name'][] = $row->FOREIGN_COLUMN_NAME;	4✔
449	$indexes[$row->CONSTRAINT_NAME]['on_delete'] = $row->DELETE_RULE;	4✔
450	$indexes[$row->CONSTRAINT_NAME]['on_update'] = null;	4✔
451	$indexes[$row->CONSTRAINT_NAME]['match'] = null;	4✔
452	}
453
454	return $this->foreignKeyDataToObjects($indexes);	5✔
455	}
456
457	/**
458	* Returns platform-specific SQL to disable foreign key checks.
459	*
460	* @return string
461	*/
462	protected function _disableForeignKeyChecks()
463	{
464	return <<<'SQL'
465	BEGIN
466	FOR c IN
467	(SELECT c.owner, c.table_name, c.constraint_name
468	FROM user_constraints c, user_tables t
469	WHERE c.table_name = t.table_name
470	AND c.status = 'ENABLED'
471	AND c.constraint_type = 'R'
472	AND t.iot_type IS NULL
473	ORDER BY c.constraint_type DESC)
474	LOOP
475	dbms_utility.exec_ddl_statement('alter table "' \|\| c.owner \|\| '"."' \|\| c.table_name \|\| '" disable constraint "' \|\| c.constraint_name \|\| '"');
476	END LOOP;
477	END;
478	SQL;
479	}
480
481	/**
482	* Returns platform-specific SQL to enable foreign key checks.
483	*
484	* @return string
485	*/
486	protected function _enableForeignKeyChecks()
487	{
488	return <<<'SQL'
489	BEGIN
490	FOR c IN
491	(SELECT c.owner, c.table_name, c.constraint_name
492	FROM user_constraints c, user_tables t
493	WHERE c.table_name = t.table_name
494	AND c.status = 'DISABLED'
495	AND c.constraint_type = 'R'
496	AND t.iot_type IS NULL
497	ORDER BY c.constraint_type DESC)
498	LOOP
499	dbms_utility.exec_ddl_statement('alter table "' \|\| c.owner \|\| '"."' \|\| c.table_name \|\| '" enable constraint "' \|\| c.constraint_name \|\| '"');
500	END LOOP;
501	END;
502	SQL;
503	}
504
505	/**
506	* Get cursor. Returns a cursor from the database
507	*
508	* @return resource
509	*/
510	public function getCursor()
511	{
512	return $this->cursorId = oci_new_cursor($this->connID);	1✔
513	}
514
515	/**
516	* Executes a stored procedure
517	*
518	* @param string $procedureName procedure name to execute
519	* @param array $params params array keys
520	* KEY OPTIONAL NOTES
521	* name no the name of the parameter should be in :<param_name> format
522	* value no the value of the parameter. If this is an OUT or IN OUT parameter,
523	* this should be a reference to a variable
524	* type yes the type of the parameter
525	* length yes the max size of the parameter
526	*
527	* @return bool\|Query\|Result
528	*/
529	public function storedProcedure(string $procedureName, array $params)
530	{
531	if ($procedureName === '') {	3✔
532	throw new DatabaseException(lang('Database.invalidArgument', [$procedureName]));	×
533	}
534
535	// Build the query string
536	$sql = sprintf(	3✔
537	'BEGIN %s (' . substr(str_repeat(',%s', count($params)), 1) . '); END;',	3✔
538	$procedureName,	3✔
539	...array_map(static fn ($row) => $row['name'], $params),	3✔
540	);	3✔
541
542	$this->resetStmtId = false;	3✔
543	$this->stmtId = oci_parse($this->connID, $sql);	3✔
544	$this->bindParams($params);	3✔
545	$result = $this->query($sql);	3✔
546	$this->resetStmtId = true;	3✔
547
548	return $result;	3✔
549	}
550
551	/**
552	* Bind parameters
553	*
554	* @param array $params
555	*
556	* @return void
557	*/
558	protected function bindParams($params)
559	{
560	if (! is_array($params) \|\| ! is_resource($this->stmtId)) {	3✔
561	return;	×
562	}
563
564	foreach ($params as $param) {	3✔
565	oci_bind_by_name(	3✔
566	$this->stmtId,	3✔
567	$param['name'],	3✔
568	$param['value'],	3✔
569	$param['length'] ?? -1,	3✔
570	$param['type'] ?? SQLT_CHR,	3✔
571	);	3✔
572	}
573	}
574
575	/**
576	* Returns the last error code and message.
577	*
578	* Must return an array with keys 'code' and 'message':
579	*
580	* return ['code' => null, 'message' => null);
581	*/
582	public function error(): array
583	{
584	// oci_error() returns an array that already contains
585	// 'code' and 'message' keys, but it can return false
586	// if there was no error ....
587	$error = oci_error();	2✔
588	$resources = [$this->cursorId, $this->stmtId, $this->connID];	2✔
589
590	foreach ($resources as $resource) {	2✔
591	if (is_resource($resource)) {	2✔
592	$error = oci_error($resource);	2✔
593	break;	2✔
594	}
595	}
596
597	return is_array($error)	2✔
598	? $error	×
599	: [	2✔
600	'code' => '',	2✔
601	'message' => '',	2✔
602	];	2✔
603	}
604
605	public function insertID(): int
606	{
607	if (empty($this->lastInsertedTableName)) {	83✔
608	return 0;	×
609	}
610
611	$indexs = $this->getIndexData($this->lastInsertedTableName);	83✔
612	$fieldDatas = $this->getFieldData($this->lastInsertedTableName);	83✔
613
614	if ($indexs === [] \|\| $fieldDatas === []) {	83✔
615	return 0;	×
616	}
617
618	$columnTypeList = array_column($fieldDatas, 'type', 'name');	83✔
619	$primaryColumnName = '';	83✔
620
621	foreach ($indexs as $index) {	83✔
622	if ($index->type !== 'PRIMARY' \|\| count($index->fields) !== 1) {	83✔
623	continue;	57✔
624	}
625
626	$primaryColumnName = $this->protectIdentifiers($index->fields[0], false, false);	83✔
627	$primaryColumnType = $columnTypeList[$primaryColumnName];	83✔
628
629	if ($primaryColumnType !== 'NUMBER') {	83✔
630	$primaryColumnName = '';	×
631	}
632	}
633
634	if ($primaryColumnName === '') {	83✔
635	return 0;	×
636	}
637
638	$query = $this->query('SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME = ?', [$this->lastInsertedTableName, $primaryColumnName])->getRow();	83✔
639	$lastInsertValue = str_replace('nextval', 'currval', $query->DATA_DEFAULT ?? '0');	83✔
640	$query = $this->query(sprintf('SELECT %s SEQ FROM DUAL', $lastInsertValue))->getRow();	83✔
641
642	return (int) ($query->SEQ ?? 0);	83✔
643	}
644
645	/**
646	* Build a DSN from the provided parameters
647	*
648	* @return void
649	*/
650	protected function buildDSN()
651	{
652	if ($this->DSN !== '') {	×
653	$this->DSN = '';	×
654	}
655
656	// Legacy support for TNS in the hostname configuration field
657	$this->hostname = str_replace(["\n", "\r", "\t", ' '], '', $this->hostname);	×
658
659	if (preg_match($this->validDSNs['tns'], $this->hostname)) {	×
660	$this->DSN = $this->hostname;	×
661
662	return;	×
663	}
664
665	$isEasyConnectableHostName = $this->hostname !== '' && ! str_contains($this->hostname, '/') && ! str_contains($this->hostname, ':');	×
666	$easyConnectablePort = ($this->port !== '') && ctype_digit((string) $this->port) ? ':' . $this->port : '';	×
667	$easyConnectableDatabase = $this->database !== '' ? '/' . ltrim($this->database, '/') : '';	×
668
669	if ($isEasyConnectableHostName && ($easyConnectablePort !== '' \|\| $easyConnectableDatabase !== '')) {	×
670	/* If the hostname field isn't empty, doesn't contain
671	* ':' and/or '/' and if port and/or database aren't
672	* empty, then the hostname field is most likely indeed
673	* just a hostname. Therefore we'll try and build an
674	* Easy Connect string from these 3 settings, assuming
675	* that the database field is a service name.
676	*/
677	$this->DSN = $this->hostname . $easyConnectablePort . $easyConnectableDatabase;	×
678
679	if (preg_match($this->validDSNs['ec'], $this->DSN)) {	×
680	return;	×
681	}
682	}
683
684	/* At this point, we can only try and validate the hostname and
685	* database fields separately as DSNs.
686	*/
687	if (preg_match($this->validDSNs['ec'], $this->hostname) \|\| preg_match($this->validDSNs['in'], $this->hostname)) {	×
688	$this->DSN = $this->hostname;	×
689
690	return;	×
691	}
692
693	$this->database = str_replace(["\n", "\r", "\t", ' '], '', $this->database);	×
694
695	foreach ($this->validDSNs as $regexp) {	×
696	if (preg_match($regexp, $this->database)) {	×
697	return;	×
698	}
699	}
700
701	/* Well - OK, an empty string should work as well.
702	* PHP will try to use environment variables to
703	* determine which Oracle instance to connect to.
704	*/
705	$this->DSN = '';	×
706	}
707
708	/**
709	* Begin Transaction
710	*/
711	protected function _transBegin(): bool
712	{
713	$this->commitMode = OCI_NO_AUTO_COMMIT;	17✔
714
715	return true;	17✔
716	}
717
718	/**
719	* Commit Transaction
720	*/
721	protected function _transCommit(): bool
722	{
723	$this->commitMode = OCI_COMMIT_ON_SUCCESS;	5✔
724
725	return oci_commit($this->connID);	5✔
726	}
727
728	/**
729	* Rollback Transaction
730	*/
731	protected function _transRollback(): bool
732	{
733	$this->commitMode = OCI_COMMIT_ON_SUCCESS;	17✔
734
735	return oci_rollback($this->connID);	17✔
736	}
737
738	/**
739	* Returns the name of the current database being used.
740	*/
741	public function getDatabase(): string
742	{
743	if (! empty($this->database)) {	11✔
744	return $this->database;	×
745	}
746
747	return $this->query('SELECT DEFAULT_TABLESPACE FROM USER_USERS')->getRow()->DEFAULT_TABLESPACE ?? '';	11✔
748	}
749
750	/**
751	* Get the prefix of the function to access the DB.
752	*/
753	protected function getDriverFunctionPrefix(): string
754	{
755	return 'oci_';	×
756	}
757	}

codeigniter4 / CodeIgniter4 / 13806563199

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous