DBAL::commandTransactionBegin() - Code Metrics - eclipxe13/engineworks-dbal - Measure and Improve Code Quality continuously with Scrutinizer

DBAL::commandTransactionBegin() A
last analyzed 2024-04-29 15:58 UTC

↳ Parent: DBAL

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	3
CRAP Score	1

Importance

Changes

Metric	Value
eloc	2
dl	0
loc	4
ccs	3
cts	3
cp	1
rs	10
c	0
b	0
f	0
cc	1
nc	1
nop	0
crap	1

<?php

/** @noinspection PhpComposerExtensionStubsInspection */

declare(strict_types=1);

namespace EngineWorks\DBAL\Sqlsrv;

use EngineWorks\DBAL\Abstracts\BaseDBAL;
use EngineWorks\DBAL\Traits\MethodSqlConcatenate;
use EngineWorks\DBAL\Traits\MethodSqlLimit;
use InvalidArgumentException;
use PDO;
use PDOStatement;
use RuntimeException;
use Throwable;

/**
 * MS Sql Server implementation based on SqlSrv
 * @see https://docs.microsoft.com/en-us/sql/connect/php/microsoft-php-driver-for-sql-server
 * @package EngineWorks\DBAL\Sqlsrv
 *
 * @todo: encoding: $this->settings->get('encoding')
 */
class DBAL extends BaseDBAL
{
    use MethodSqlConcatenate;
    use MethodSqlLimit;

    /** @var PDO|null */
    protected $pdo = null;

    protected function getPDOConnectionString(): string
    {
        $vars = [];
        $vars['Server'] = $this->settings->get('host');
        if ($this->settings->exists('port')) {
            $vars['Server'] .= ',' . ((int) $this->settings->get('port'));
        }
        if ($this->settings->exists('database')) {
            $vars['Database'] = $this->settings->get('database');
        }
        if ($this->settings->exists('connect-timeout')) {
            $vars['LoginTimeout'] = max(0, (int) $this->settings->get('connect-timeout', '15'));
        }
        $return = 'sqlsrv:';
        foreach ($vars as $key => $value) {
            $return .= $key . '=' . $value . ';';
        }
        return $return;
    }

    public function connect(): bool
    {
        // disconnect
        $this->disconnect();
        // create the pdo object without error reporting
        $errorLevel = error_reporting(0);
        try {
            $this->pdo = new PDO(
                $this->getPDOConnectionString(),
                (string) $this->settings->get('user'),
                (string) $this->settings->get('password'),
                [
                    PDO::SQLSRV_ATTR_QUERY_TIMEOUT => max(0, (int) $this->settings->get('timeout')),
                ]
            );
        } catch (Throwable $ex) {
            $this->logger->info('-- Connection fail ' . $ex->getMessage());
            $this->logger->error('Cannot create PDO object for SqlSrv ' . $ex->getMessage());
            return false;
        } finally {
            error_reporting($errorLevel);
        }
        // OK, we are connected
        $this->logger->info('-- Connect and database select OK');
        return true;
    }

    public function disconnect(): void
    {
        if ($this->isConnected()) {
            $this->logger->info('-- Disconnection');
        }
        $this->transactionLevel = 0;
        $this->pdo = null;
    }

    public function isConnected(): bool
    {
        return ($this->pdo instanceof PDO);
    }

    public function lastInsertedID(): int
    {
        return (int) $this->pdo()->lastInsertId();
    }

    public function sqlString($variable): string
    {
        // there are no function to escape without a link
        if ($this->isConnected()) {
            $quoted = $this->pdo()->quote(strval($variable));
            return substr($quoted, 1, strlen($quoted) - 2);
        }
        return str_replace(["\0", "'"], ['', "''"], (string) $variable);
    }

    /**
     * Executes a query and return an object or resource native to the driver
     * This is the internal function to do the query according to the database functions
     * It's used by queryResult and queryAffectedRows methods
     *
     * @param string $query
     * @param bool $returnAffectedRows
     * @return PDOStatement|int|false
     */
    protected function queryDriver(string $query, bool $returnAffectedRows)
    {
        $this->logger->debug($query);
        try {
            if ($returnAffectedRows) {
                $affectedRows = $this->pdo()->exec($query);
                if (! is_int($affectedRows)) {

                    throw new RuntimeException("Unable to execute statement $query");
                }
                return $affectedRows;
            } else {
                $stmt = $this->pdo()->prepare($query, [
                    PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL,
                    PDO::SQLSRV_ATTR_DIRECT_QUERY => true,
                ]);
                if (! ($stmt instanceof PDOStatement)) {

                    throw new RuntimeException("Unable to prepare statement $query");
                }
                if (false === $stmt->execute()) {
                    throw new RuntimeException("Unable to execute statement $query");
                }
                return $stmt;
            }
        } catch (Throwable $ex) {
            $this->logger->info("-- Query fail with SQL: $query");
            $this->logger->error("FAIL: $query\nLast message:" . $this->getLastMessage());
            return false;
        }
    }

    /** @return Result|false */
    public function queryResult(string $query, array $overrideTypes = [])
    {
        $stmt = $this->queryDriver($query, false);
        if ($stmt instanceof PDOStatement) {
            return new Result($stmt, $overrideTypes);
        }
        return false;
    }

    protected function queryAffectedRows(string $query)
    {
        $affectedRows = $this->queryDriver($query, true);
        if (is_int($affectedRows)) {
            return max(0, $affectedRows);
        }
        return false;
    }

    protected function getLastErrorMessage(): string
    {
        $info = $this->pdo()->errorInfo();
        return '[' . $info[0] . '] ' . $info[2];
    }

    public function sqlTableEscape(string $tableName, string $asTable = ''): string
    {
        return '[' . $tableName . ']' . (('' !== $asTable) ? ' AS [' . $asTable . ']' : '');
    }

    public function sqlFieldEscape(string $fieldName, string $asTable = ''): string
    {
        return '[' . $fieldName . ']' . (('' !== $asTable) ? ' AS [' . $asTable . ']' : '');
    }

    public function sqlDatePart(string $part, string $expression): string
    {
        switch (strtoupper($part)) {
            case 'YEAR':
                return "DATEPART(yyyy, $expression)";
            case 'MONTH':
                return "RIGHT('0' + CAST(DATEPART(mm, $expression) AS VARCHAR(2)), 2)";
            case 'DAY':
                return "RIGHT('0' + CAST(DATEPART(dd, $expression) AS VARCHAR(2)), 2)";
            case 'HOUR':
                return "RIGHT('0' + CAST(DATEPART(hh, $expression) AS VARCHAR(2)), 2)";
            case 'MINUTE':
                return "RIGHT('0' + CAST(DATEPART(mi, $expression) AS VARCHAR(2)), 2)";
            case 'SECOND':
                return "RIGHT('0' + CAST(DATEPART(ss, $expression) AS VARCHAR(2)), 2)";
            case 'FDOM':
                return 'CONCAT'
                    . "(DATEPART(yyyy, $expression),"
                    . " '-',"
                    . " RIGHT('0' + CAST(DATEPART(mm, $expression) AS VARCHAR(2)), 2),"
                    . " '-01'"
                    . ')';
            case 'FYM':
                return 'CONCAT'
                    . "(DATEPART(yyyy, $expression),"
                    . " '-',"
                    . " RIGHT('0' + CAST(DATEPART(mm, $expression) AS VARCHAR(2)), 2)"
                    . ')';
            case 'FYMD':
                return 'CONCAT'
                    . "(DATEPART(yyyy, $expression),"
                    . " '-',"
                    . " RIGHT('0' + CAST(DATEPART(mm, $expression) AS VARCHAR(2)), 2),"
                    . " '-',"
                    . " RIGHT('0' + CAST(DATEPART(dd, $expression) AS VARCHAR(2)), 2)"
                    . ')';
            case 'FHMS':
                return 'CONCAT'
                    . "(RIGHT('0' + CAST(DATEPART(hh, $expression) AS VARCHAR(2)), 2),"
                    . " ':',"
                    . " RIGHT('0' + CAST(DATEPART(mi, $expression) AS VARCHAR(2)), 2),"
                    . " ':',"
                    . " RIGHT('0' + CAST(DATEPART(ss, $expression) AS VARCHAR(2)), 2)"
                    . ')';
        }
        throw new InvalidArgumentException("Date part $part is not valid");
    }

    public function sqlIf(string $condition, string $truePart, string $falsePart): string
    {
        return 'CASE WHEN (' . $condition . ') THEN ' . $truePart . ' ELSE ' . $falsePart . ' END';
    }

    public function sqlRandomFunc(): string
    {
        return 'RAND()';
    }

    public function sqlLimit(string $query, int $requestedPage, int $recordsPerPage = 20): string
    {
        return $this->sqlLimitOffsetFetchNext($query, $requestedPage, $recordsPerPage);
    }

    public function sqlLike(
        string $fieldName,
        string $searchString,
        bool $wildcardBegin = true,
        bool $wildcardEnd = true
    ): string {
        $searchString = str_replace(
            ['[', '_', '%'],
            ['[[]', '[_]', '[%]'],
            $searchString
        );
        return $fieldName . " LIKE '"
            . (($wildcardBegin) ? '%' : '') . $this->sqlString($searchString) . (($wildcardEnd) ? '%' : '') . "'";
    }

    /** @noinspection PhpMissingParentCallCommonInspection */
    protected function commandTransactionBegin(): void
    {
        $this->logger->debug('-- PDO TRANSACTION BEGIN'); // send message because it didn't run execute command
        $this->pdo()->beginTransaction();
    }

    /** @noinspection PhpMissingParentCallCommonInspection */
    protected function commandTransactionCommit(): void
    {
        $this->logger->debug('-- PDO TRANSACTION COMMIT'); // send message because it didn't run execute command
        $this->pdo()->commit();
    }

    /** @noinspection PhpMissingParentCallCommonInspection */
    protected function commandTransactionRollback(): void
    {
        $this->logger->debug('-- PDO TRANSACTION ROLLBACK'); // send message because it didn't run execute command
        $this->pdo()->rollBack();
    }

    /** @noinspection PhpMissingParentCallCommonInspection */
    protected function commandSavepoint(string $name): void
    {
        $this->execute(
            'SAVE TRANSACTION ' . $this->sqlFieldEscape($name) . ';',
            "Cannot begin nested transaction $name"
        );
    }

    /** @noinspection PhpMissingParentCallCommonInspection */
    protected function commandReleaseSavepoint(string $name): void
    {
        // do not execute, the command commit transaction does not work with save transaction
        $this->logger->debug("-- PDO TRANSACTION COMMIT $name"); // send message because it didn't run execute command
    }

    /** @noinspection PhpMissingParentCallCommonInspection */
    protected function commandRollbackToSavepoint(string $name): void
    {
        $this->execute(
            'ROLLBACK TRANSACTION ' . $this->sqlFieldEscape($name) . ';',
            "Cannot rollback nested transaction $name"
        );
    }

    private function pdo(): PDO
    {
        if (null === $this->pdo) {
            throw new RuntimeException('The current state of the connection is NULL');
        }
        return $this->pdo;
    }
}


1		<?php
2
3		/** @noinspection PhpComposerExtensionStubsInspection */
4
5		declare(strict_types=1);
6
7		namespace EngineWorks\DBAL\Sqlsrv;
8
9		use EngineWorks\DBAL\Abstracts\BaseDBAL;
10		use EngineWorks\DBAL\Traits\MethodSqlConcatenate;
11		use EngineWorks\DBAL\Traits\MethodSqlLimit;
12		use InvalidArgumentException;
13		use PDO;
14		use PDOStatement;
15		use RuntimeException;
16		use Throwable;
17
18		/**
19		* MS Sql Server implementation based on SqlSrv
20		* @see https://docs.microsoft.com/en-us/sql/connect/php/microsoft-php-driver-for-sql-server
21		* @package EngineWorks\DBAL\Sqlsrv
22		*
23		* @todo: encoding: $this->settings->get('encoding')
24		*/
25		class DBAL extends BaseDBAL
26		{
27		use MethodSqlConcatenate;
28		use MethodSqlLimit;
29
30		/** @var PDO\|null */
31		protected $pdo = null;
32
33	78	protected function getPDOConnectionString(): string
34		{
35	78	$vars = [];
36	78	$vars['Server'] = $this->settings->get('host');
37	78	if ($this->settings->exists('port')) {
38	78	$vars['Server'] .= ',' . ((int) $this->settings->get('port'));
39		}
40	78	if ($this->settings->exists('database')) {
41	78	$vars['Database'] = $this->settings->get('database');
42		}
43	78	if ($this->settings->exists('connect-timeout')) {
44	78	$vars['LoginTimeout'] = max(0, (int) $this->settings->get('connect-timeout', '15'));
45		}
46	78	$return = 'sqlsrv:';
47	78	foreach ($vars as $key => $value) {
48	78	$return .= $key . '=' . $value . ';';
49		}
50	78	return $return;
51		}
52
53	78	public function connect(): bool
54		{
55		// disconnect
56	78	$this->disconnect();
57		// create the pdo object without error reporting
58	78	$errorLevel = error_reporting(0);
59		try {
60	78	$this->pdo = new PDO(
61	78	$this->getPDOConnectionString(),
62	78	(string) $this->settings->get('user'),
63	78	(string) $this->settings->get('password'),
64	78	[
65	78	PDO::SQLSRV_ATTR_QUERY_TIMEOUT => max(0, (int) $this->settings->get('timeout')),
66	78	]
67	78	);
68	3	} catch (Throwable $ex) {
69	3	$this->logger->info('-- Connection fail ' . $ex->getMessage());
70	3	$this->logger->error('Cannot create PDO object for SqlSrv ' . $ex->getMessage());
71	3	return false;
72		} finally {
73	78	error_reporting($errorLevel);
74		}
75		// OK, we are connected
76	75	$this->logger->info('-- Connect and database select OK');
77	75	return true;
78		}
79
80	80	public function disconnect(): void
81		{
82	80	if ($this->isConnected()) {
83	75	$this->logger->info('-- Disconnection');
84		}
85	80	$this->transactionLevel = 0;
86	80	$this->pdo = null;
87		}
88
89	100	public function isConnected(): bool
90		{
91	100	return ($this->pdo instanceof PDO);
92		}
93
94		public function lastInsertedID(): int
95		{
96		return (int) $this->pdo()->lastInsertId();
97		}
98
99	80	public function sqlString($variable): string
100		{
101		// there are no function to escape without a link
102	80	if ($this->isConnected()) {
103	75	$quoted = $this->pdo()->quote(strval($variable));
104	75	return substr($quoted, 1, strlen($quoted) - 2);
105		}
106	5	return str_replace(["\0", "'"], ['', "''"], (string) $variable);
107		}
108
109		/**
110		* Executes a query and return an object or resource native to the driver
111		* This is the internal function to do the query according to the database functions
112		* It's used by queryResult and queryAffectedRows methods
113		*
114		* @param string $query
115		* @param bool $returnAffectedRows
116		* @return PDOStatement\|int\|false
117		*/
118	75	protected function queryDriver(string $query, bool $returnAffectedRows)
119		{
120	75	$this->logger->debug($query);
121		try {
122	75	if ($returnAffectedRows) {
123	75	$affectedRows = $this->pdo()->exec($query);
124	75	if (! is_int($affectedRows)) {
		0 ignored issues – show introduced 2018-10-19 19:54 UTC by Report Bug Copy Issue Report The condition `is_int($affectedRows)` is always `true`. Loading history...
125		throw new RuntimeException("Unable to execute statement $query");
126		}
127	75	return $affectedRows;
128		} else {
129	62	$stmt = $this->pdo()->prepare($query, [
130	62	PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL,
131	62	PDO::SQLSRV_ATTR_DIRECT_QUERY => true,
132	62	]);
133	62	if (! ($stmt instanceof PDOStatement)) {
		0 ignored issues – show introduced 2021-05-25 19:12 UTC by Report Bug Copy Issue Report `$stmt` is always a sub-type of `PDOStatement`. Loading history...
134		throw new RuntimeException("Unable to prepare statement $query");
135		}
136	62	if (false === $stmt->execute()) {
137		throw new RuntimeException("Unable to execute statement $query");
138		}
139	52	return $stmt;
140		}
141	11	} catch (Throwable $ex) {
142	11	$this->logger->info("-- Query fail with SQL: $query");
143	11	$this->logger->error("FAIL: $query\nLast message:" . $this->getLastMessage());
144	11	return false;
145		}
146		}
147
148		/** @return Result\|false */
149	62	public function queryResult(string $query, array $overrideTypes = [])
150		{
151	62	$stmt = $this->queryDriver($query, false);
152	62	if ($stmt instanceof PDOStatement) {
153	52	return new Result($stmt, $overrideTypes);
154		}
155	10	return false;
156		}
157
158	75	protected function queryAffectedRows(string $query)
159		{
160	75	$affectedRows = $this->queryDriver($query, true);
161	75	if (is_int($affectedRows)) {
162	75	return max(0, $affectedRows);
163		}
164	1	return false;
165		}
166
167	11	protected function getLastErrorMessage(): string
168		{
169	11	$info = $this->pdo()->errorInfo();
170	11	return '[' . $info[0] . '] ' . $info[2];
171		}
172
173	4	public function sqlTableEscape(string $tableName, string $asTable = ''): string
174		{
175	4	return '[' . $tableName . ']' . (('' !== $asTable) ? ' AS [' . $asTable . ']' : '');
176		}
177
178	5	public function sqlFieldEscape(string $fieldName, string $asTable = ''): string
179		{
180	5	return '[' . $fieldName . ']' . (('' !== $asTable) ? ' AS [' . $asTable . ']' : '');
181		}
182
183	10	public function sqlDatePart(string $part, string $expression): string
184		{
185	10	switch (strtoupper($part)) {
186	10	case 'YEAR':
187	1	return "DATEPART(yyyy, $expression)";
188	9	case 'MONTH':
189	1	return "RIGHT('0' + CAST(DATEPART(mm, $expression) AS VARCHAR(2)), 2)";
190	8	case 'DAY':
191	1	return "RIGHT('0' + CAST(DATEPART(dd, $expression) AS VARCHAR(2)), 2)";
192	7	case 'HOUR':
193	1	return "RIGHT('0' + CAST(DATEPART(hh, $expression) AS VARCHAR(2)), 2)";
194	6	case 'MINUTE':
195	1	return "RIGHT('0' + CAST(DATEPART(mi, $expression) AS VARCHAR(2)), 2)";
196	5	case 'SECOND':
197	1	return "RIGHT('0' + CAST(DATEPART(ss, $expression) AS VARCHAR(2)), 2)";
198	4	case 'FDOM':
199	1	return 'CONCAT'
200	1	. "(DATEPART(yyyy, $expression),"
201	1	. " '-',"
202	1	. " RIGHT('0' + CAST(DATEPART(mm, $expression) AS VARCHAR(2)), 2),"
203	1	. " '-01'"
204	1	. ')';
205	3	case 'FYM':
206	1	return 'CONCAT'
207	1	. "(DATEPART(yyyy, $expression),"
208	1	. " '-',"
209	1	. " RIGHT('0' + CAST(DATEPART(mm, $expression) AS VARCHAR(2)), 2)"
210	1	. ')';
211	2	case 'FYMD':
212	1	return 'CONCAT'
213	1	. "(DATEPART(yyyy, $expression),"
214	1	. " '-',"
215	1	. " RIGHT('0' + CAST(DATEPART(mm, $expression) AS VARCHAR(2)), 2),"
216	1	. " '-',"
217	1	. " RIGHT('0' + CAST(DATEPART(dd, $expression) AS VARCHAR(2)), 2)"
218	1	. ')';
219	1	case 'FHMS':
220	1	return 'CONCAT'
221	1	. "(RIGHT('0' + CAST(DATEPART(hh, $expression) AS VARCHAR(2)), 2),"
222	1	. " ':',"
223	1	. " RIGHT('0' + CAST(DATEPART(mi, $expression) AS VARCHAR(2)), 2),"
224	1	. " ':',"
225	1	. " RIGHT('0' + CAST(DATEPART(ss, $expression) AS VARCHAR(2)), 2)"
226	1	. ')';
227		}
228		throw new InvalidArgumentException("Date part $part is not valid");
229		}
230
231	1	public function sqlIf(string $condition, string $truePart, string $falsePart): string
232		{
233	1	return 'CASE WHEN (' . $condition . ') THEN ' . $truePart . ' ELSE ' . $falsePart . ' END';
234		}
235
236	1	public function sqlRandomFunc(): string
237		{
238	1	return 'RAND()';
239		}
240
241	1	public function sqlLimit(string $query, int $requestedPage, int $recordsPerPage = 20): string
242		{
243	1	return $this->sqlLimitOffsetFetchNext($query, $requestedPage, $recordsPerPage);
244		}
245
246	2	public function sqlLike(
247		string $fieldName,
248		string $searchString,
249		bool $wildcardBegin = true,
250		bool $wildcardEnd = true
251		): string {
252	2	$searchString = str_replace(
253	2	['[', '_', '%'],
254	2	['[[]', '[_]', '[%]'],
255	2	$searchString
256	2	);
257	2	return $fieldName . " LIKE '"
258	2	. (($wildcardBegin) ? '%' : '') . $this->sqlString($searchString) . (($wildcardEnd) ? '%' : '') . "'";
259		}
260
261		/** @noinspection PhpMissingParentCallCommonInspection */
262	75	protected function commandTransactionBegin(): void
263		{
264	75	$this->logger->debug('-- PDO TRANSACTION BEGIN'); // send message because it didn't run execute command
265	75	$this->pdo()->beginTransaction();
266		}
267
268		/** @noinspection PhpMissingParentCallCommonInspection */
269	75	protected function commandTransactionCommit(): void
270		{
271	75	$this->logger->debug('-- PDO TRANSACTION COMMIT'); // send message because it didn't run execute command
272	75	$this->pdo()->commit();
273		}
274
275		/** @noinspection PhpMissingParentCallCommonInspection */
276	1	protected function commandTransactionRollback(): void
277		{
278	1	$this->logger->debug('-- PDO TRANSACTION ROLLBACK'); // send message because it didn't run execute command
279	1	$this->pdo()->rollBack();
280		}
281
282		/** @noinspection PhpMissingParentCallCommonInspection */
283	2	protected function commandSavepoint(string $name): void
284		{
285	2	$this->execute(
286	2	'SAVE TRANSACTION ' . $this->sqlFieldEscape($name) . ';',
287	2	"Cannot begin nested transaction $name"
288	2	);
289		}
290
291		/** @noinspection PhpMissingParentCallCommonInspection */
292	2	protected function commandReleaseSavepoint(string $name): void
293		{
294		// do not execute, the command commit transaction does not work with save transaction
295	2	$this->logger->debug("-- PDO TRANSACTION COMMIT $name"); // send message because it didn't run execute command
296		}
297
298		/** @noinspection PhpMissingParentCallCommonInspection */
299	1	protected function commandRollbackToSavepoint(string $name): void
300		{
301	1	$this->execute(
302	1	'ROLLBACK TRANSACTION ' . $this->sqlFieldEscape($name) . ';',
303	1	"Cannot rollback nested transaction $name"
304	1	);
305		}
306
307	75	private function pdo(): PDO
308		{
309	75	if (null === $this->pdo) {
310		throw new RuntimeException('The current state of the connection is NULL');
311		}
312	75	return $this->pdo;
313		}
314		}
315

eclipxe13 / engineworks-dbal

DBAL::commandTransactionBegin() A last analyzed 2024-04-29 15:58 UTC

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like

DBAL::commandTransactionBegin() A
last analyzed 2024-04-29 15:58 UTC