18675330340

Committed 21 Oct 2025 06:46AM UTC coverage: 86.655% (+0.006%) from 86.649%

Build # 18675330340

Build Type

Pull #289

github

Committed by

web-flow

Commit Message

Merge 886b0ebf0 into a89576c0a

Pull Request Pull Request #289: Add "sslrootcert" to knownKeys in PgsqlDriver

Run Details

1948 of 2248 relevant lines covered (86.65%)

4.31 hits per line

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

96.47

/src/Platforms/PostgreSqlPlatform.php

<?php declare(strict_types = 1);

namespace Nextras\Dbal\Platforms;


use DateInterval;
use DateTimeInterface;
use Nextras\Dbal\Drivers\IDriver;
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\DateTimeHelper;
use Nextras\Dbal\Utils\JsonHelper;
use Nextras\Dbal\Utils\StrictObjectTrait;
use Nextras\MultiQueryParser\IMultiQueryParser;
use Nextras\MultiQueryParser\PostgreSqlMultiQueryParser;
use function bin2hex;
use function str_replace;
use function strtr;
use function trim;


class PostgreSqlPlatform implements IPlatform
{
        use StrictObjectTrait;


        final public const NAME = 'pgsql';

        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
        {
                // relkind options:
                // r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table, I = partitioned index
                $result = $this->connection->query(/** @lang GenericSQL */ "
                        SELECT
                                DISTINCT ON (c.relname)
                                c.relname::varchar AS name,
                                n.nspname::varchar AS schema,
                                c.relkind = 'v' AS is_view
                        FROM
                                pg_catalog.pg_class AS c
                                JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
                        WHERE
                                c.relkind IN ('r', 'v', 'm', 'f', 'p')
                                AND n.nspname = ANY (
                                        CASE %?s IS NULL WHEN true THEN pg_catalog.current_schemas(FALSE) ELSE ARRAY[[%?s]] END
                                )
                        ORDER BY
                                c.relname
                ", $schema, $schema);

                $tables = [];
                foreach ($result as $row) {
                        $table = new Table(
                                fqnName: new Fqn((string) $row->schema, (string) $row->name),
                                isView: (bool) $row->is_view,
                        );
                        $tables[$table->fqnName->getUnescaped()] = $table;
                }
                return $tables;
        }


        /** @inheritDoc */
        public function getColumns(string $table, ?string $schema = null): array
        {
                $tableArgs = $schema !== null ? [$schema, $table] : [$table];
                $result = $this->connection->query((/** @lang GenericSQL */ "
                        SELECT
                                a.attname::varchar AS name,
                                UPPER(t.typname) AS type,
                                CASE WHEN a.atttypmod = -1 THEN NULL ELSE a.atttypmod -4 END AS size,
                                pg_catalog.pg_get_expr(ad.adbin, ad.adrelid)::varchar AS default,
                                COALESCE(co.contype = 'p', FALSE) AS is_primary,
                                COALESCE(co.contype = 'p' AND (strpos(pg_get_expr(ad.adbin, ad.adrelid), 'nextval') = 1 OR a.attidentity != ''), FALSE) AS is_autoincrement,
                                NOT (a.attnotnull OR t.typtype = 'd' AND t.typnotnull) AS is_nullable,
                                COALESCE(
                        ") . (
                                count($tableArgs) > 1
                                        ? "pg_get_serial_sequence('%table.%table', a.attname),"
                                        : "pg_get_serial_sequence('%table', a.attname),"
                        )
                        . (/** @lang GenericSQL */ "
                                        SUBSTRING(pg_catalog.pg_get_expr(ad.adbin, ad.adrelid) FROM %s)
                                ) AS sequence
                        FROM
                                pg_catalog.pg_attribute AS a
                                JOIN pg_catalog.pg_class AS c ON a.attrelid = c.oid
                                JOIN pg_catalog.pg_type AS t ON a.atttypid = t.oid
                                LEFT JOIN pg_catalog.pg_attrdef AS ad ON ad.adrelid = c.oid AND ad.adnum = a.attnum
                                LEFT JOIN pg_catalog.pg_constraint AS co ON co.connamespace = c.relnamespace AND contype = 'p' AND co.conrelid = c.oid AND a.attnum = ANY(co.conkey)
                        WHERE
                                c.relkind IN ('r', 'v', 'm', 'f', 'p')
                                ") . (
                        count($tableArgs) > 1
                                ? "AND c.oid = '%table.%table'::regclass"
                                : "AND c.oid = '%table'::regclass"
                        ) . (/** @lang GenericSQL */ "
                                AND a.attnum > 0
                                AND NOT a.attisdropped
                        ORDER BY
                                a.attnum
                "), ...$tableArgs, ...["nextval[(]'\"?([^'\"]+)"], ...$tableArgs);

                $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,
                                isNullable: (bool) $row->is_nullable,
                                meta: isset($row->sequence) ? ['sequence' => $row->sequence] : [],
                        );
                        $columns[$column->name] = $column;
                }
                return $columns;
        }


        /** @inheritDoc */
        public function getForeignKeys(string $table, ?string $schema = null): array
        {
                $tableArgs = $schema !== null ? [$schema, $table] : [$table];
                $result = $this->connection->query((/** @lang PostgreSQL */ "
                        SELECT
                                co.conname::varchar AS name,
                                ns.nspname::varchar AS schema,
                                at.attname::varchar AS column,
                                clf.relname::varchar AS ref_table,
                                nsf.nspname::varchar AS ref_table_schema,
                                atf.attname::varchar AS ref_column
                        FROM
                                pg_catalog.pg_constraint AS co
                                JOIN pg_catalog.pg_class AS cl ON co.conrelid = cl.oid
                                JOIN pg_catalog.pg_class AS clf ON co.confrelid = clf.oid
                                JOIN pg_catalog.pg_namespace AS ns ON ns.oid = cl.relnamespace
                                JOIN pg_catalog.pg_namespace AS nsf ON nsf.oid = clf.relnamespace
                                JOIN pg_catalog.pg_attribute AS at ON at.attrelid = cl.oid AND at.attnum = co.conkey[[1]]
                                JOIN pg_catalog.pg_attribute AS atf ON atf.attrelid = clf.oid AND atf.attnum = co.confkey[[1]]
                        WHERE
                                co.contype = 'f'
                                ") . (
                        count($tableArgs) > 1
                                ? "AND cl.oid = '%table.%table'::regclass"
                                : "AND cl.oid = '%table'::regclass"
                        ) . '
                        ORDER BY at.attnum
                        ', ...$tableArgs);

                $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
        {
                foreach ($this->getColumns($table, $schema) as $column) {
                        if ($column->isPrimary) {
                                return $column->meta['sequence'] ?? null;
                        }
                }
                return null;
        }


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


        public function formatStringLike(string $value, int $mode)
        {
                $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 ? 'TRUE' : 'FALSE';
        }


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


        public function formatDateTime(DateTimeInterface $value): string
        {
                $value = DateTimeHelper::convertToTimezone($value, $this->driver->getConnectionTimeZone());
                return "'" . $value->format('Y-m-d H:i:s.u') . "'::timestamptz";
        }


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


        public function formatLocalDate(DateTimeInterface $value): string
        {
                return "'" . $value->format('Y-m-d') . "'::date";
        }


        public function formatDateInterval(DateInterval $value): string
        {
                return $value->format('P%yY%mM%dDT%hH%iM%sS');
        }


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


        public function formatLimitOffset(?int $limit, ?int $offset): string
        {
                $clause = '';
                if ($limit !== null) {
                        $clause = 'LIMIT ' . $limit;
                }
                if ($offset !== null) {
                        $clause = trim("$clause OFFSET $offset");
                }
                return $clause;
        }


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


        public function isSupported(int $feature): bool
        {
                static $supported = [
                        self::SUPPORT_MULTI_COLUMN_IN => true,
                        self::SUPPORT_QUERY_EXPLAIN => true,
                        self::SUPPORT_WHITESPACE_EXPLAIN => true,
                ];
                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\IConnection;
10	use Nextras\Dbal\Platforms\Data\Column;
11	use Nextras\Dbal\Platforms\Data\ForeignKey;
12	use Nextras\Dbal\Platforms\Data\Fqn;
13	use Nextras\Dbal\Platforms\Data\Table;
14	use Nextras\Dbal\Utils\DateTimeHelper;
15	use Nextras\Dbal\Utils\JsonHelper;
16	use Nextras\Dbal\Utils\StrictObjectTrait;
17	use Nextras\MultiQueryParser\IMultiQueryParser;
18	use Nextras\MultiQueryParser\PostgreSqlMultiQueryParser;
19	use function bin2hex;
20	use function str_replace;
21	use function strtr;
22	use function trim;
23
24
25	class PostgreSqlPlatform implements IPlatform
26	{
27	use StrictObjectTrait;
28
29
30	final public const NAME = 'pgsql';
31
32	private readonly IDriver $driver;
33
34
35	public function __construct(private readonly IConnection $connection)	5✔
36	{
37	$this->driver = $connection->getDriver();	5✔
38	}	5✔
39
40
41	public function getName(): string
42	{
43	return static::NAME;	5✔
44	}
45
46
47	/** @inheritDoc */
48	public function getTables(?string $schema = null): array
49	{
50	// relkind options:
51	// r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table, I = partitioned index
52	$result = $this->connection->query(/** @lang GenericSQL */ "	5✔
53	SELECT
54	DISTINCT ON (c.relname)
55	c.relname::varchar AS name,
56	n.nspname::varchar AS schema,
57	c.relkind = 'v' AS is_view
58	FROM
59	pg_catalog.pg_class AS c
60	JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
61	WHERE
62	c.relkind IN ('r', 'v', 'm', 'f', 'p')
63	AND n.nspname = ANY (
64	CASE %?s IS NULL WHEN true THEN pg_catalog.current_schemas(FALSE) ELSE ARRAY[[%?s]] END
65	)
66	ORDER BY
67	c.relname
68	", $schema, $schema);
69
70	$tables = [];	5✔
71	foreach ($result as $row) {	5✔
72	$table = new Table(	5✔
73	fqnName: new Fqn((string) $row->schema, (string) $row->name),	5✔
74	isView: (bool) $row->is_view,	5✔
75	);
76	$tables[$table->fqnName->getUnescaped()] = $table;	5✔
77	}
78	return $tables;	5✔
79	}
80
81
82	/** @inheritDoc */
83	public function getColumns(string $table, ?string $schema = null): array
84	{
85	$tableArgs = $schema !== null ? [$schema, $table] : [$table];	5✔
86	$result = $this->connection->query((/** @lang GenericSQL */ "	5✔
87	SELECT
88	a.attname::varchar AS name,
89	UPPER(t.typname) AS type,
90	CASE WHEN a.atttypmod = -1 THEN NULL ELSE a.atttypmod -4 END AS size,
91	pg_catalog.pg_get_expr(ad.adbin, ad.adrelid)::varchar AS default,
92	COALESCE(co.contype = 'p', FALSE) AS is_primary,
93	COALESCE(co.contype = 'p' AND (strpos(pg_get_expr(ad.adbin, ad.adrelid), 'nextval') = 1 OR a.attidentity != ''), FALSE) AS is_autoincrement,
94	NOT (a.attnotnull OR t.typtype = 'd' AND t.typnotnull) AS is_nullable,
95	COALESCE(
96	") . (
97	count($tableArgs) > 1	5✔
98	? "pg_get_serial_sequence('%table.%table', a.attname),"	5✔
99	: "pg_get_serial_sequence('%table', a.attname),"	5✔
100	)
101	. (/** @lang GenericSQL */ "	5✔
102	SUBSTRING(pg_catalog.pg_get_expr(ad.adbin, ad.adrelid) FROM %s)
103	) AS sequence
104	FROM
105	pg_catalog.pg_attribute AS a
106	JOIN pg_catalog.pg_class AS c ON a.attrelid = c.oid
107	JOIN pg_catalog.pg_type AS t ON a.atttypid = t.oid
108	LEFT JOIN pg_catalog.pg_attrdef AS ad ON ad.adrelid = c.oid AND ad.adnum = a.attnum
109	LEFT JOIN pg_catalog.pg_constraint AS co ON co.connamespace = c.relnamespace AND contype = 'p' AND co.conrelid = c.oid AND a.attnum = ANY(co.conkey)
110	WHERE
111	c.relkind IN ('r', 'v', 'm', 'f', 'p')
112	") . (
113	count($tableArgs) > 1	5✔
114	? "AND c.oid = '%table.%table'::regclass"	5✔
115	: "AND c.oid = '%table'::regclass"	5✔
116	) . (/** @lang GenericSQL */ "	5✔
117	AND a.attnum > 0
118	AND NOT a.attisdropped
119	ORDER BY
120	a.attnum
121	"), ...$tableArgs, ...["nextval[(]'\"?([^'\"]+)"], ...$tableArgs);	5✔
122
123	$columns = [];	5✔
124	foreach ($result as $row) {	5✔
125	$column = new Column(	5✔
126	name: (string) $row->name,	5✔
127	type: (string) $row->type,	5✔
128	size: $row->size !== null ? (int) $row->size : null,	5✔
129	default: $row->default !== null ? (string) $row->default : null,	5✔
130	isPrimary: (bool) $row->is_primary,	5✔
131	isAutoincrement: (bool) $row->is_autoincrement,	5✔
132	isUnsigned: false,	5✔
133	isNullable: (bool) $row->is_nullable,	5✔
134	meta: isset($row->sequence) ? ['sequence' => $row->sequence] : [],	5✔
135	);
136	$columns[$column->name] = $column;	5✔
137	}
138	return $columns;	5✔
139	}
140
141
142	/** @inheritDoc */
143	public function getForeignKeys(string $table, ?string $schema = null): array
144	{
145	$tableArgs = $schema !== null ? [$schema, $table] : [$table];	5✔
146	$result = $this->connection->query((/** @lang PostgreSQL */ "	5✔
147	SELECT
148	co.conname::varchar AS name,
149	ns.nspname::varchar AS schema,
150	at.attname::varchar AS column,
151	clf.relname::varchar AS ref_table,
152	nsf.nspname::varchar AS ref_table_schema,
153	atf.attname::varchar AS ref_column
154	FROM
155	pg_catalog.pg_constraint AS co
156	JOIN pg_catalog.pg_class AS cl ON co.conrelid = cl.oid
157	JOIN pg_catalog.pg_class AS clf ON co.confrelid = clf.oid
158	JOIN pg_catalog.pg_namespace AS ns ON ns.oid = cl.relnamespace
159	JOIN pg_catalog.pg_namespace AS nsf ON nsf.oid = clf.relnamespace
160	JOIN pg_catalog.pg_attribute AS at ON at.attrelid = cl.oid AND at.attnum = co.conkey[[1]]
161	JOIN pg_catalog.pg_attribute AS atf ON atf.attrelid = clf.oid AND atf.attnum = co.confkey[[1]]
162	WHERE
163	co.contype = 'f'
164	") . (
165	count($tableArgs) > 1	5✔
166	? "AND cl.oid = '%table.%table'::regclass"	5✔
167	: "AND cl.oid = '%table'::regclass"	5✔
168	) . '	5✔
169	ORDER BY at.attnum
170	', ...$tableArgs);	5✔
171
172	$keys = [];	5✔
173	foreach ($result as $row) {	5✔
174	$foreignKey = new ForeignKey(	5✔
175	fqnName: new Fqn((string) $row->schema, (string) $row->name),	5✔
176	column: (string) $row->column,	5✔
177	refTable: new Fqn((string) $row->ref_table_schema, (string) $row->ref_table),	5✔
178	refColumn: (string) $row->ref_column,	5✔
179	);
180	$keys[$foreignKey->column] = $foreignKey;	5✔
181	}
182	return $keys;	5✔
183	}
184
185
186	public function getPrimarySequenceName(string $table, ?string $schema = null): ?string
187	{
188	foreach ($this->getColumns($table, $schema) as $column) {	5✔
189	if ($column->isPrimary) {	5✔
190	return $column->meta['sequence'] ?? null;	5✔
191	}
192	}
193	return null;	×
194	}
195
196
197	public function formatString(string $value): string
198	{
199	return $this->driver->convertStringToSql($value);	5✔
200	}
201
202
203	public function formatStringLike(string $value, int $mode)
204	{
205	$value = strtr($value, [	5✔
206	"'" => "''",	2✔
207	'\\' => '\\\\',
208	'%' => '\\%',
209	'_' => '\\_',
210	]);
211	return ($mode <= 0 ? "'%" : "'") . $value . ($mode >= 0 ? "%'" : "'");	5✔
212	}
213
214
215	public function formatJson(mixed $value): string
216	{
217	$encoded = JsonHelper::safeEncode($value);	5✔
218	return $this->driver->convertStringToSql($encoded);	5✔
219	}
220
221
222	public function formatBool(bool $value): string
223	{
224	return $value ? 'TRUE' : 'FALSE';	5✔
225	}
226
227
228	public function formatIdentifier(string $value): string
229	{
230	return '"' . str_replace(['"', '.'], ['""', '"."'], $value) . '"';	5✔
231	}
232
233
234	public function formatDateTime(DateTimeInterface $value): string
235	{
236	$value = DateTimeHelper::convertToTimezone($value, $this->driver->getConnectionTimeZone());	5✔
237	return "'" . $value->format('Y-m-d H:i:s.u') . "'::timestamptz";	5✔
238	}
239
240
241	public function formatLocalDateTime(DateTimeInterface $value): string
242	{
243	return "'" . $value->format('Y-m-d H:i:s.u') . "'::timestamp";	5✔
244	}
245
246
247	public function formatLocalDate(DateTimeInterface $value): string
248	{
249	return "'" . $value->format('Y-m-d') . "'::date";	×
250	}
251
252
253	public function formatDateInterval(DateInterval $value): string
254	{
255	return $value->format('P%yY%mM%dDT%hH%iM%sS');	5✔
256	}
257
258
259	public function formatBlob(string $value): string
260	{
261	return "E'\\\\x" . bin2hex($value) . "'";	5✔
262	}
263
264
265	public function formatLimitOffset(?int $limit, ?int $offset): string
266	{
267	$clause = '';	5✔
268	if ($limit !== null) {	5✔
269	$clause = 'LIMIT ' . $limit;	5✔
270	}
271	if ($offset !== null) {	5✔
272	$clause = trim("$clause OFFSET $offset");	5✔
273	}
274	return $clause;	5✔
275	}
276
277
278	public function createMultiQueryParser(): IMultiQueryParser
279	{
280	if (!class_exists(PostgreSqlMultiQueryParser::class)) {	5✔
281	throw new \RuntimeException("Missing nextras/multi-query-parser dependency. Install it first to use IPlatform::createMultiQueryParser().");	×
282	}
283	return new PostgreSqlMultiQueryParser();	5✔
284	}
285
286
287	public function isSupported(int $feature): bool
288	{
289	static $supported = [	5✔
290	self::SUPPORT_MULTI_COLUMN_IN => true,	2✔
291	self::SUPPORT_QUERY_EXPLAIN => true,	2✔
292	self::SUPPORT_WHITESPACE_EXPLAIN => true,	2✔
293	];
294	return isset($supported[$feature]);	5✔
295	}
296	}

nextras / dbal / 18675330340

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