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

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

↳ Parent: DBAL

Complexity

Conditions	1
Paths	1

Size

Total Lines	3
Code Lines	1

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	2
CRAP Score	1

Importance

Changes

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

<?php

/** @noinspection PhpComposerExtensionStubsInspection */

declare(strict_types=1);

namespace EngineWorks\DBAL\Mssql;

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

/**
 * Mssql implementation
 * @package EngineWorks\DBAL\Mssql
 */
class DBAL extends BaseDBAL
{
    use MethodSqlConcatenate;
    use MethodSqlLimit;

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

    protected function getPDOConnectionString(): string
    {
        $vars = [];
        if ($this->settings->exists('freetds-version')) {
            $vars['version'] = $this->settings->get('freetds-version');
        }
        $vars['host'] = $this->settings->get('host');
        if ($this->settings->exists('port')) {
            $vars['host'] .= ':' . $this->settings->get('port');
        }

        if ($this->settings->exists('database')) {
            $vars['dbname'] = $this->settings->get('database');
        }
        if ($this->settings->exists('encoding')) {
            $vars['charset'] = $this->settings->get('encoding');
        }

        $return = 'dblib:';
        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::ATTR_TIMEOUT => $this->settings->get('connect-timeout'),
                ]
            );
        } catch (Throwable $ex) {
            $this->logger->info('-- Connection fail ' . $ex->getMessage());
            $this->logger->error('Cannot create PDO object for MS SQL ' . $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, it fails on multibyte strings
        //if ($this->isConnected()) {
        //    $quoted = $this->pdo()->quote($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
     *
     * @return PDOStatement|false
     */
    protected function queryDriver(string $query)
    {
        $this->logger->debug($query);
        try {
            if (false === $stmt = $this->pdo()->query($query)) {
                throw new RuntimeException("Unable to prepare statement $query");
            }
            return $stmt;
        } catch (Exception $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);
        if (false !== $stmt) {
            return new Result($stmt, $stmt->rowCount(), $overrideTypes);
        }
        return false;
    }

    protected function queryAffectedRows(string $query)
    {
        $stmt = $this->queryDriver($query);
        if (false !== $stmt) {
            return max(0, $stmt->rowCount());
        }
        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 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("-- COMMIT TRANSACTION $name");
    }

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

eclipxe13 / engineworks-dbal

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

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like

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