13619972298

Committed 02 Mar 2025 10:33PM UTC coverage: 86.649% (-0.1%) from 86.756%

Build # 13619972298

Build Type

push

github

Committed by

web-flow

Commit Message

implement LocalDate %ld modifier (#283)

Coverage Stats

7 of 12 new or added lines in 5 files covered. (58.33%)

1947 of 2247 relevant lines covered (86.65%)

3.45 hits per line

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

96.67

/src/Platforms/SqlServerPlatform.php

<?php declare(strict_types = 1);

namespace Nextras\Dbal\Platforms;


use DateInterval;
use DateTimeInterface;
use Nextras\Dbal\Drivers\IDriver;
use Nextras\Dbal\Exception\NotSupportedException;
use Nextras\Dbal\IConnection;
use Nextras\Dbal\Platforms\Data\Column;
use Nextras\Dbal\Platforms\Data\ForeignKey;
use Nextras\Dbal\Platforms\Data\Fqn;
use Nextras\Dbal\Platforms\Data\Table;
use Nextras\Dbal\Utils\JsonHelper;
use Nextras\Dbal\Utils\StrictObjectTrait;
use Nextras\MultiQueryParser\IMultiQueryParser;
use Nextras\MultiQueryParser\SqlServerMultiQueryParser;


class SqlServerPlatform implements IPlatform
{
        use StrictObjectTrait;


        final public const NAME = 'mssql';

        private readonly IDriver $driver;


        public function __construct(private readonly IConnection $connection)
        {
                $this->driver = $connection->getDriver();
        }


        public function getName(): string
        {
                return static::NAME;
        }


        /** @inheritDoc */
        public function getTables(?string $schema = null): array
        {
                $result = $this->connection->query(/** @lang GenericSQL */ "
                        SELECT TABLE_NAME, TABLE_TYPE, TABLE_SCHEMA
                         FROM [INFORMATION_SCHEMA].[TABLES]
                        WHERE TABLE_SCHEMA = COALESCE(%?s, SCHEMA_NAME())
                         ORDER BY TABLE_NAME
                ", $schema);

                $tables = [];
                foreach ($result as $row) {
                        $table = new Table(
                                fqnName: new Fqn((string) $row->TABLE_SCHEMA, (string) $row->TABLE_NAME),
                                isView: $row->TABLE_TYPE === 'VIEW',
                        );
                        $tables[$table->fqnName->getUnescaped()] = $table;
                }
                return $tables;
        }


        /** @inheritDoc */
        public function getColumns(string $table, ?string $schema = null): array
        {
                $result = $this->connection->query(/** @lang GenericSQL */ "
                        SELECT
                                [a].[COLUMN_NAME] AS [name],
                                UPPER([a].[DATA_TYPE]) AS [type],
                                CASE
                                        WHEN [a].[CHARACTER_MAXIMUM_LENGTH] IS NOT NULL THEN [a].[CHARACTER_MAXIMUM_LENGTH]
                                        WHEN  [a].[NUMERIC_PRECISION] IS NOT NULL THEN [a].[NUMERIC_PRECISION]
                                        ELSE NULL
                                END AS [size],
                                [a].[COLUMN_DEFAULT] AS [default],
                                CASE
                                        WHEN [b].[CONSTRAINT_TYPE] = 'PRIMARY KEY'
                                        THEN CONVERT(BIT, 1)
                                        ELSE CONVERT(BIT, 0)
                                END AS [is_primary],
                                CONVERT(
                                        BIT, COLUMNPROPERTY(object_id(CONCAT([a].[TABLE_SCHEMA], '.', [a].[TABLE_NAME])), [a].[COLUMN_NAME], 'IsIdentity')
                                ) AS [is_autoincrement],
                                CASE
                                        WHEN [a].[IS_NULLABLE] = 'YES'
                                        THEN CONVERT(BIT, 1)
                                        ELSE CONVERT(BIT, 0)
                                END AS [is_nullable]
                        FROM [INFORMATION_SCHEMA].[COLUMNS] AS [a]
                        LEFT JOIN (
                                SELECT [c].[TABLE_SCHEMA], [c].[TABLE_CATALOG], [c].[TABLE_NAME], [c].[COLUMN_NAME], [d].[CONSTRAINT_TYPE]
                                FROM [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] AS [c]
                                INNER JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] AS [d]
                                ON ([d].[CONSTRAINT_NAME] = [c].[CONSTRAINT_NAME] AND [d].[CONSTRAINT_TYPE] = 'PRIMARY KEY')
                        ) AS [b] ON (
                                [b].[TABLE_CATALOG] = [a].[TABLE_CATALOG] AND
                                [b].[TABLE_SCHEMA] = [a].[TABLE_SCHEMA] AND
                                [b].[TABLE_NAME] = [a].[TABLE_NAME] AND
                                [b].[COLUMN_NAME] = [a].[COLUMN_NAME]
                        )
                        WHERE [a].[TABLE_NAME] = %s
                                AND [a].[TABLE_SCHEMA] = COALESCE(%?s, SCHEMA_NAME())
                        ORDER BY [a].[ORDINAL_POSITION]
                ", $table, $schema);

                $columns = [];
                foreach ($result as $row) {
                        $column = new Column(
                                name: (string) $row->name,
                                type: (string) $row->type,
                                size: $row->size !== null ? (int) $row->size : null,
                                default: $row->default !== null ? (string) $row->default : null,
                                isPrimary: (bool) $row->is_primary,
                                isAutoincrement: (bool) $row->is_autoincrement,
                                isUnsigned: false, // not available in SqlServer
                                isNullable: (bool) $row->is_nullable,
                                meta: [],
                        );
                        $columns[$column->name] = $column;
                }

                return $columns;
        }


        /** @inheritDoc */
        public function getForeignKeys(string $table, ?string $schema = null): array
        {
                $result = $this->connection->query(/** @lang GenericSQL */ "
                        SELECT
                                [a].[CONSTRAINT_NAME] AS [name],
                                [d].[COLUMN_NAME] AS [column],
                                [d].[TABLE_SCHEMA] AS [schema],
                                [c].[TABLE_NAME] AS [ref_table],
                                [c].[TABLE_SCHEMA] AS [ref_table_schema],
                                [e].[COLUMN_NAME] AS [ref_column]
                        FROM [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] AS [a]
                        INNER JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] AS [b]
                                ON [a].[CONSTRAINT_NAME] = [b].[CONSTRAINT_NAME]
                        INNER JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] AS [c]
                                ON [a].[UNIQUE_CONSTRAINT_NAME] = [c].[CONSTRAINT_NAME]
                        INNER JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [d]
                                ON [a].[CONSTRAINT_NAME] = [d].[CONSTRAINT_NAME]
                        INNER JOIN (
                                SELECT [i1].[TABLE_NAME], [i2].[COLUMN_NAME]
                                FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] AS [i1]
                                INNER JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [i2]
                                        ON [i1].[CONSTRAINT_NAME] = [i2].[CONSTRAINT_NAME]
                                WHERE [i1].[CONSTRAINT_TYPE] = 'PRIMARY KEY'
                        ) AS [e] ON [e].[TABLE_NAME] = [c].[TABLE_NAME]
                        WHERE [b].[TABLE_NAME] = %s AND [d].[TABLE_SCHEMA] = COALESCE(%?s, SCHEMA_NAME())
                        ORDER BY [d].[COLUMN_NAME]
                ", $table, $schema);

                $keys = [];
                foreach ($result as $row) {
                        $foreignKey = new ForeignKey(
                                fqnName: new Fqn((string) $row->schema, (string) $row->name),
                                column: (string) $row->column,
                                refTable: new Fqn((string) $row->ref_table_schema, (string) $row->ref_table),
                                refColumn: (string) $row->ref_column,
                        );
                        $keys[$foreignKey->column] = $foreignKey;
                }
                return $keys;
        }


        public function getPrimarySequenceName(string $table, ?string $schema = null): ?string
        {
                return null;
        }


        public function formatString(string $value): string
        {
                return $this->driver->convertStringToSql($value);
        }


        public function formatStringLike(string $value, int $mode)
        {
                // https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql
                $value = strtr($value, [
                        "'" => "''",
                        '%' => '[%]',
                        '_' => '[_]',
                        '[' => '[[]',
                ]);
                return ($mode <= 0 ? "'%" : "'") . $value . ($mode >= 0 ? "%'" : "'");
        }


        public function formatJson(mixed $value): string
        {
                $encoded = JsonHelper::safeEncode($value);
                return $this->driver->convertStringToSql($encoded);
        }


        public function formatBool(bool $value): string
        {
                return $value ? '1' : '0';
        }


        public function formatIdentifier(string $value): string
        {
                return '[' . str_replace([']', '.'], [']]', '].['], $value) . ']';
        }


        public function formatDateTime(DateTimeInterface $value): string
        {
                return "CAST('" . $value->format('Y-m-d H:i:s.u P') . "' AS DATETIMEOFFSET)";
        }


        public function formatLocalDateTime(DateTimeInterface $value): string
        {
                return "CAST('" . $value->format('Y-m-d H:i:s.u') . "' AS DATETIME2)";
        }


        public function formatLocalDate(DateTimeInterface $value): string
        {
                return "CAST('" . $value->format('Y-m-d H:i:s.u') . "' AS DATE)";
        }


        public function formatDateInterval(DateInterval $value): string
        {
                throw new NotSupportedException();
        }


        public function formatBlob(string $value): string
        {
                return '0x' . bin2hex($value);
        }


        public function formatLimitOffset(?int $limit, ?int $offset): string
        {
                $clause = 'OFFSET ' . ($offset ?? 0) . ' ROWS';
                if ($limit !== null) {
                        $clause .= ' FETCH NEXT ' . $limit . ' ROWS ONLY';
                }
                return $clause;
        }


        public function createMultiQueryParser(): IMultiQueryParser
        {
                if (!class_exists(SqlServerMultiQueryParser::class)) {
                        throw new \RuntimeException("Missing nextras/multi-query-parser dependency. Install it first to use IPlatform::createMultiQueryParser().");
                }
                return new SqlServerMultiQueryParser();
        }


        public function isSupported(int $feature): bool
        {
                static $supported = [
                ];
                return isset($supported[$feature]);
        }
}

1	<?php declare(strict_types = 1);
2
3	namespace Nextras\Dbal\Platforms;
4
5
6	use DateInterval;
7	use DateTimeInterface;
8	use Nextras\Dbal\Drivers\IDriver;
9	use Nextras\Dbal\Exception\NotSupportedException;
10	use Nextras\Dbal\IConnection;
11	use Nextras\Dbal\Platforms\Data\Column;
12	use Nextras\Dbal\Platforms\Data\ForeignKey;
13	use Nextras\Dbal\Platforms\Data\Fqn;
14	use Nextras\Dbal\Platforms\Data\Table;
15	use Nextras\Dbal\Utils\JsonHelper;
16	use Nextras\Dbal\Utils\StrictObjectTrait;
17	use Nextras\MultiQueryParser\IMultiQueryParser;
18	use Nextras\MultiQueryParser\SqlServerMultiQueryParser;
19
20
21	class SqlServerPlatform implements IPlatform
22	{
23	use StrictObjectTrait;
24
25
26	final public const NAME = 'mssql';
27
28	private readonly IDriver $driver;
29
30
31	public function __construct(private readonly IConnection $connection)	4✔
32	{
33	$this->driver = $connection->getDriver();	4✔
34	}	4✔
35
36
37	public function getName(): string
38	{
39	return static::NAME;	4✔
40	}
41
42
43	/** @inheritDoc */
44	public function getTables(?string $schema = null): array
45	{
46	$result = $this->connection->query(/** @lang GenericSQL */ "	4✔
47	SELECT TABLE_NAME, TABLE_TYPE, TABLE_SCHEMA
48	FROM [INFORMATION_SCHEMA].[TABLES]
49	WHERE TABLE_SCHEMA = COALESCE(%?s, SCHEMA_NAME())
50	ORDER BY TABLE_NAME
51	", $schema);
52
53	$tables = [];	4✔
54	foreach ($result as $row) {	4✔
55	$table = new Table(	4✔
56	fqnName: new Fqn((string) $row->TABLE_SCHEMA, (string) $row->TABLE_NAME),	4✔
57	isView: $row->TABLE_TYPE === 'VIEW',	4✔
58	);
59	$tables[$table->fqnName->getUnescaped()] = $table;	4✔
60	}
61	return $tables;	4✔
62	}
63
64
65	/** @inheritDoc */
66	public function getColumns(string $table, ?string $schema = null): array
67	{
68	$result = $this->connection->query(/** @lang GenericSQL */ "	4✔
69	SELECT
70	[a].[COLUMN_NAME] AS [name],
71	UPPER([a].[DATA_TYPE]) AS [type],
72	CASE
73	WHEN [a].[CHARACTER_MAXIMUM_LENGTH] IS NOT NULL THEN [a].[CHARACTER_MAXIMUM_LENGTH]
74	WHEN [a].[NUMERIC_PRECISION] IS NOT NULL THEN [a].[NUMERIC_PRECISION]
75	ELSE NULL
76	END AS [size],
77	[a].[COLUMN_DEFAULT] AS [default],
78	CASE
79	WHEN [b].[CONSTRAINT_TYPE] = 'PRIMARY KEY'
80	THEN CONVERT(BIT, 1)
81	ELSE CONVERT(BIT, 0)
82	END AS [is_primary],
83	CONVERT(
84	BIT, COLUMNPROPERTY(object_id(CONCAT([a].[TABLE_SCHEMA], '.', [a].[TABLE_NAME])), [a].[COLUMN_NAME], 'IsIdentity')
85	) AS [is_autoincrement],
86	CASE
87	WHEN [a].[IS_NULLABLE] = 'YES'
88	THEN CONVERT(BIT, 1)
89	ELSE CONVERT(BIT, 0)
90	END AS [is_nullable]
91	FROM [INFORMATION_SCHEMA].[COLUMNS] AS [a]
92	LEFT JOIN (
93	SELECT [c].[TABLE_SCHEMA], [c].[TABLE_CATALOG], [c].[TABLE_NAME], [c].[COLUMN_NAME], [d].[CONSTRAINT_TYPE]
94	FROM [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] AS [c]
95	INNER JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] AS [d]
96	ON ([d].[CONSTRAINT_NAME] = [c].[CONSTRAINT_NAME] AND [d].[CONSTRAINT_TYPE] = 'PRIMARY KEY')
97	) AS [b] ON (
98	[b].[TABLE_CATALOG] = [a].[TABLE_CATALOG] AND
99	[b].[TABLE_SCHEMA] = [a].[TABLE_SCHEMA] AND
100	[b].[TABLE_NAME] = [a].[TABLE_NAME] AND
101	[b].[COLUMN_NAME] = [a].[COLUMN_NAME]
102	)
103	WHERE [a].[TABLE_NAME] = %s
104	AND [a].[TABLE_SCHEMA] = COALESCE(%?s, SCHEMA_NAME())
105	ORDER BY [a].[ORDINAL_POSITION]
106	", $table, $schema);
107
108	$columns = [];	4✔
109	foreach ($result as $row) {	4✔
110	$column = new Column(	4✔
111	name: (string) $row->name,	4✔
112	type: (string) $row->type,	4✔
113	size: $row->size !== null ? (int) $row->size : null,	4✔
114	default: $row->default !== null ? (string) $row->default : null,	4✔
115	isPrimary: (bool) $row->is_primary,	4✔
116	isAutoincrement: (bool) $row->is_autoincrement,	4✔
117	isUnsigned: false, // not available in SqlServer	4✔
118	isNullable: (bool) $row->is_nullable,	4✔
119	meta: [],	4✔
120	);
121	$columns[$column->name] = $column;	4✔
122	}
123
124	return $columns;	4✔
125	}
126
127
128	/** @inheritDoc */
129	public function getForeignKeys(string $table, ?string $schema = null): array
130	{
131	$result = $this->connection->query(/** @lang GenericSQL */ "	4✔
132	SELECT
133	[a].[CONSTRAINT_NAME] AS [name],
134	[d].[COLUMN_NAME] AS [column],
135	[d].[TABLE_SCHEMA] AS [schema],
136	[c].[TABLE_NAME] AS [ref_table],
137	[c].[TABLE_SCHEMA] AS [ref_table_schema],
138	[e].[COLUMN_NAME] AS [ref_column]
139	FROM [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] AS [a]
140	INNER JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] AS [b]
141	ON [a].[CONSTRAINT_NAME] = [b].[CONSTRAINT_NAME]
142	INNER JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] AS [c]
143	ON [a].[UNIQUE_CONSTRAINT_NAME] = [c].[CONSTRAINT_NAME]
144	INNER JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [d]
145	ON [a].[CONSTRAINT_NAME] = [d].[CONSTRAINT_NAME]
146	INNER JOIN (
147	SELECT [i1].[TABLE_NAME], [i2].[COLUMN_NAME]
148	FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] AS [i1]
149	INNER JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [i2]
150	ON [i1].[CONSTRAINT_NAME] = [i2].[CONSTRAINT_NAME]
151	WHERE [i1].[CONSTRAINT_TYPE] = 'PRIMARY KEY'
152	) AS [e] ON [e].[TABLE_NAME] = [c].[TABLE_NAME]
153	WHERE [b].[TABLE_NAME] = %s AND [d].[TABLE_SCHEMA] = COALESCE(%?s, SCHEMA_NAME())
154	ORDER BY [d].[COLUMN_NAME]
155	", $table, $schema);
156
157	$keys = [];	4✔
158	foreach ($result as $row) {	4✔
159	$foreignKey = new ForeignKey(	4✔
160	fqnName: new Fqn((string) $row->schema, (string) $row->name),	4✔
161	column: (string) $row->column,	4✔
162	refTable: new Fqn((string) $row->ref_table_schema, (string) $row->ref_table),	4✔
163	refColumn: (string) $row->ref_column,	4✔
164	);
165	$keys[$foreignKey->column] = $foreignKey;	4✔
166	}
167	return $keys;	4✔
168	}
169
170
171	public function getPrimarySequenceName(string $table, ?string $schema = null): ?string
172	{
173	return null;	4✔
174	}
175
176
177	public function formatString(string $value): string
178	{
179	return $this->driver->convertStringToSql($value);	4✔
180	}
181
182
183	public function formatStringLike(string $value, int $mode)
184	{
185	// https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql
186	$value = strtr($value, [	4✔
187	"'" => "''",	3✔
188	'%' => '[%]',
189	'_' => '[_]',
190	'[' => '[[]',
191	]);
192	return ($mode <= 0 ? "'%" : "'") . $value . ($mode >= 0 ? "%'" : "'");	4✔
193	}
194
195
196	public function formatJson(mixed $value): string
197	{
198	$encoded = JsonHelper::safeEncode($value);	4✔
199	return $this->driver->convertStringToSql($encoded);	4✔
200	}
201
202
203	public function formatBool(bool $value): string
204	{
205	return $value ? '1' : '0';	4✔
206	}
207
208
209	public function formatIdentifier(string $value): string
210	{
211	return '[' . str_replace([']', '.'], [']]', '].['], $value) . ']';	4✔
212	}
213
214
215	public function formatDateTime(DateTimeInterface $value): string
216	{
217	return "CAST('" . $value->format('Y-m-d H:i:s.u P') . "' AS DATETIMEOFFSET)";	4✔
218	}
219
220
221	public function formatLocalDateTime(DateTimeInterface $value): string
222	{
223	return "CAST('" . $value->format('Y-m-d H:i:s.u') . "' AS DATETIME2)";	4✔
224	}
225
226
227	public function formatLocalDate(DateTimeInterface $value): string
228	{
NEW 229	return "CAST('" . $value->format('Y-m-d H:i:s.u') . "' AS DATE)";	×
230	}
231
232
233	public function formatDateInterval(DateInterval $value): string
234	{
235	throw new NotSupportedException();	4✔
236	}
237
238
239	public function formatBlob(string $value): string
240	{
241	return '0x' . bin2hex($value);	4✔
242	}
243
244
245	public function formatLimitOffset(?int $limit, ?int $offset): string
246	{
247	$clause = 'OFFSET ' . ($offset ?? 0) . ' ROWS';	4✔
248	if ($limit !== null) {	4✔
249	$clause .= ' FETCH NEXT ' . $limit . ' ROWS ONLY';	4✔
250	}
251	return $clause;	4✔
252	}
253
254
255	public function createMultiQueryParser(): IMultiQueryParser
256	{
257	if (!class_exists(SqlServerMultiQueryParser::class)) {	4✔
258	throw new \RuntimeException("Missing nextras/multi-query-parser dependency. Install it first to use IPlatform::createMultiQueryParser().");	×
259	}
260	return new SqlServerMultiQueryParser();	4✔
261	}
262
263
264	public function isSupported(int $feature): bool
265	{
266	static $supported = [	4✔
267	];
268	return isset($supported[$feature]);	4✔
269	}
270	}

nextras / dbal / 13619972298

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