PostgreSQLSchemaManager.php — MailPoet – Newsletters, Email Marketing, and Automation 5.36.0

mailpoet / vendor-prefixed / doctrine / dbal / src / Schema / PostgreSQLSchemaManager.php

mailpoet / vendor-prefixed / doctrine / dbal / src / Schema Last commit date

Exception 2 years ago Visitor 9 months ago AbstractAsset.php 2 years ago AbstractSchemaManager.php 9 months ago Column.php 2 years ago ColumnDiff.php 2 years ago Comparator.php 2 years ago Constraint.php 2 years ago DB2SchemaManager.php 2 years ago DefaultSchemaManagerFactory.php 2 years ago ForeignKeyConstraint.php 9 months ago Identifier.php 2 years ago Index.php 2 years ago LegacySchemaManagerFactory.php 2 years ago MySQLSchemaManager.php 9 months ago OracleSchemaManager.php 2 years ago PostgreSQLSchemaManager.php 9 months ago SQLServerSchemaManager.php 9 months ago Schema.php 2 years ago SchemaConfig.php 2 years ago SchemaDiff.php 2 years ago SchemaException.php 2 years ago SchemaManagerFactory.php 2 years ago Sequence.php 2 years ago SqliteSchemaManager.php 8 months ago Table.php 2 years ago TableDiff.php 2 years ago UniqueConstraint.php 2 years ago View.php 2 years ago

PostgreSQLSchemaManager.php

466 lines

1	<?php
2	namespace MailPoetVendor\Doctrine\DBAL\Schema;
3	if (!defined('ABSPATH')) exit;
4	use MailPoetVendor\Doctrine\DBAL\Exception;
5	use MailPoetVendor\Doctrine\DBAL\Platforms\PostgreSQLPlatform;
6	use MailPoetVendor\Doctrine\DBAL\Result;
7	use MailPoetVendor\Doctrine\DBAL\Types\JsonType;
8	use MailPoetVendor\Doctrine\DBAL\Types\Type;
9	use MailPoetVendor\Doctrine\DBAL\Types\Types;
10	use MailPoetVendor\Doctrine\Deprecations\Deprecation;
11	use function array_change_key_case;
12	use function array_filter;
13	use function array_map;
14	use function array_merge;
15	use function array_shift;
16	use function assert;
17	use function explode;
18	use function get_class;
19	use function implode;
20	use function in_array;
21	use function preg_match;
22	use function preg_replace;
23	use function sprintf;
24	use function str_replace;
25	use function strpos;
26	use function strtolower;
27	use function trim;
28	use const CASE_LOWER;
29	class PostgreSQLSchemaManager extends AbstractSchemaManager
30	{
31	private ?array $existingSchemaPaths = null;
32	public function listTableNames()
33	{
34	return $this->doListTableNames();
35	}
36	public function listTables()
37	{
38	return $this->doListTables();
39	}
40	public function listTableDetails($name)
41	{
42	Deprecation::triggerIfCalledFromOutside('doctrine/dbal', 'https://github.com/doctrine/dbal/pull/5595', '%s is deprecated. Use introspectTable() instead.', __METHOD__);
43	return $this->doListTableDetails($name);
44	}
45	public function listTableColumns($table, $database = null)
46	{
47	return $this->doListTableColumns($table, $database);
48	}
49	public function listTableIndexes($table)
50	{
51	return $this->doListTableIndexes($table);
52	}
53	public function listTableForeignKeys($table, $database = null)
54	{
55	return $this->doListTableForeignKeys($table, $database);
56	}
57	public function getSchemaNames()
58	{
59	Deprecation::trigger('doctrine/dbal', 'https://github.com/doctrine/dbal/issues/4503', 'PostgreSQLSchemaManager::getSchemaNames() is deprecated,' . ' use PostgreSQLSchemaManager::listSchemaNames() instead.');
60	return $this->listNamespaceNames();
61	}
62	public function listSchemaNames() : array
63	{
64	return $this->_conn->fetchFirstColumn(<<<'SQL'
65	SELECT schema_name
66	FROM information_schema.schemata
67	WHERE schema_name NOT LIKE 'pg\_%'
68	AND schema_name != 'information_schema'
69	SQL
70	);
71	}
72	public function getSchemaSearchPaths()
73	{
74	Deprecation::triggerIfCalledFromOutside('doctrine/dbal', 'https://github.com/doctrine/dbal/pull/4821', 'PostgreSQLSchemaManager::getSchemaSearchPaths() is deprecated.');
75	$params = $this->_conn->getParams();
76	$searchPaths = $this->_conn->fetchOne('SHOW search_path');
77	assert($searchPaths !== \false);
78	$schema = explode(',', $searchPaths);
79	if (isset($params['user'])) {
80	$schema = str_replace('"$user"', $params['user'], $schema);
81	}
82	return array_map('trim', $schema);
83	}
84	public function getExistingSchemaSearchPaths()
85	{
86	if ($this->existingSchemaPaths === null) {
87	$this->determineExistingSchemaSearchPaths();
88	}
89	assert($this->existingSchemaPaths !== null);
90	return $this->existingSchemaPaths;
91	}
92	protected function getCurrentSchema()
93	{
94	$schemas = $this->getExistingSchemaSearchPaths();
95	return array_shift($schemas);
96	}
97	public function determineExistingSchemaSearchPaths()
98	{
99	$names = $this->listSchemaNames();
100	$paths = $this->getSchemaSearchPaths();
101	$this->existingSchemaPaths = array_filter($paths, static function ($v) use($names) : bool {
102	return in_array($v, $names, \true);
103	});
104	}
105	protected function _getPortableTableForeignKeyDefinition($tableForeignKey)
106	{
107	$onUpdate = null;
108	$onDelete = null;
109	if (preg_match('(ON UPDATE ([a-zA-Z0-9]+( (NULL\|ACTION\|DEFAULT))?))', $tableForeignKey['condef'], $match) === 1) {
110	$onUpdate = $match[1];
111	}
112	if (preg_match('(ON DELETE ([a-zA-Z0-9]+( (NULL\|ACTION\|DEFAULT))?))', $tableForeignKey['condef'], $match) === 1) {
113	$onDelete = $match[1];
114	}
115	$result = preg_match('/FOREIGN KEY \$(.+)\$ REFERENCES (.+)\$(.+)\$/', $tableForeignKey['condef'], $values);
116	assert($result === 1);
117	// PostgreSQL returns identifiers that are keywords with quotes, we need them later, don't get
118	// the idea to trim them here.
119	$localColumns = array_map('trim', explode(',', $values[1]));
120	$foreignColumns = array_map('trim', explode(',', $values[3]));
121	$foreignTable = $values[2];
122	return new ForeignKeyConstraint($localColumns, $foreignTable, $foreignColumns, $tableForeignKey['conname'], ['onUpdate' => $onUpdate, 'onDelete' => $onDelete]);
123	}
124	protected function _getPortableViewDefinition($view)
125	{
126	return new View($view['schemaname'] . '.' . $view['viewname'], $view['definition']);
127	}
128	protected function _getPortableTableDefinition($table)
129	{
130	$currentSchema = $this->getCurrentSchema();
131	if ($table['schema_name'] === $currentSchema) {
132	return $table['table_name'];
133	}
134	return $table['schema_name'] . '.' . $table['table_name'];
135	}
136	protected function _getPortableTableIndexesList($tableIndexes, $tableName = null)
137	{
138	$buffer = [];
139	foreach ($tableIndexes as $row) {
140	$colNumbers = array_map('intval', explode(' ', $row['indkey']));
141	$columnNameSql = sprintf(<<<'SQL'
142	SELECT attnum,
143	quote_ident(attname) AS attname
144	FROM pg_attribute
145	WHERE attrelid = %d
146	AND attnum IN (%s)
147	ORDER BY attnum
148	SQL
149	, $row['indrelid'], implode(', ', $colNumbers));
150	$indexColumns = $this->_conn->fetchAllAssociative($columnNameSql);
151	// required for getting the order of the columns right.
152	foreach ($colNumbers as $colNum) {
153	foreach ($indexColumns as $colRow) {
154	if ($colNum !== $colRow['attnum']) {
155	continue;
156	}
157	$buffer[] = ['key_name' => $row['relname'], 'column_name' => trim($colRow['attname']), 'non_unique' => !$row['indisunique'], 'primary' => $row['indisprimary'], 'where' => $row['where']];
158	}
159	}
160	}
161	return parent::_getPortableTableIndexesList($buffer, $tableName);
162	}
163	protected function _getPortableDatabaseDefinition($database)
164	{
165	return $database['datname'];
166	}
167	protected function getPortableNamespaceDefinition(array $namespace)
168	{
169	Deprecation::triggerIfCalledFromOutside('doctrine/dbal', 'https://github.com/doctrine/dbal/issues/4503', 'PostgreSQLSchemaManager::getPortableNamespaceDefinition() is deprecated,' . ' use PostgreSQLSchemaManager::listSchemaNames() instead.');
170	return $namespace['nspname'];
171	}
172	protected function _getPortableSequenceDefinition($sequence)
173	{
174	if ($sequence['schemaname'] !== 'public') {
175	$sequenceName = $sequence['schemaname'] . '.' . $sequence['relname'];
176	} else {
177	$sequenceName = $sequence['relname'];
178	}
179	return new Sequence($sequenceName, (int) $sequence['increment_by'], (int) $sequence['min_value']);
180	}
181	protected function _getPortableTableColumnDefinition($tableColumn)
182	{
183	$tableColumn = array_change_key_case($tableColumn, CASE_LOWER);
184	if (strtolower($tableColumn['type']) === 'varchar' \|\| strtolower($tableColumn['type']) === 'bpchar') {
185	// get length from varchar definition
186	$length = preg_replace('~.\$([0-9])\$.*~', '$1', $tableColumn['complete_type']);
187	$tableColumn['length'] = $length;
188	}
189	$matches = [];
190	$autoincrement = \false;
191	if ($tableColumn['default'] !== null && preg_match("/^nextval\$'(.)'(::.)?\$\$/", $tableColumn['default'], $matches) === 1) {
192	$tableColumn['sequence'] = $matches[1];
193	$tableColumn['default'] = null;
194	$autoincrement = \true;
195	}
196	if ($tableColumn['default'] !== null) {
197	if (preg_match("/^['(](.*)[')]::/", $tableColumn['default'], $matches) === 1) {
198	$tableColumn['default'] = $matches[1];
199	} elseif (preg_match('/^NULL::/', $tableColumn['default']) === 1) {
200	$tableColumn['default'] = null;
201	}
202	}
203	$length = $tableColumn['length'] ?? null;
204	if ($length === '-1' && isset($tableColumn['atttypmod'])) {
205	$length = $tableColumn['atttypmod'] - 4;
206	}
207	if ((int) $length <= 0) {
208	$length = null;
209	}
210	$fixed = null;
211	if (!isset($tableColumn['name'])) {
212	$tableColumn['name'] = '';
213	}
214	$precision = null;
215	$scale = null;
216	$jsonb = null;
217	$dbType = strtolower($tableColumn['type']);
218	if ($tableColumn['domain_type'] !== null && $tableColumn['domain_type'] !== '' && !$this->_platform->hasDoctrineTypeMappingFor($tableColumn['type'])) {
219	$dbType = strtolower($tableColumn['domain_type']);
220	$tableColumn['complete_type'] = $tableColumn['domain_complete_type'];
221	}
222	$type = $this->_platform->getDoctrineTypeMapping($dbType);
223	$type = $this->extractDoctrineTypeFromComment($tableColumn['comment'], $type);
224	$tableColumn['comment'] = $this->removeDoctrineTypeFromComment($tableColumn['comment'], $type);
225	switch ($dbType) {
226	case 'smallint':
227	case 'int2':
228	$tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']);
229	$length = null;
230	break;
231	case 'int':
232	case 'int4':
233	case 'integer':
234	$tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']);
235	$length = null;
236	break;
237	case 'bigint':
238	case 'int8':
239	$tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']);
240	$length = null;
241	break;
242	case 'bool':
243	case 'boolean':
244	if ($tableColumn['default'] === 'true') {
245	$tableColumn['default'] = \true;
246	}
247	if ($tableColumn['default'] === 'false') {
248	$tableColumn['default'] = \false;
249	}
250	$length = null;
251	break;
252	case 'json':
253	case 'text':
254	case '_varchar':
255	case 'varchar':
256	$tableColumn['default'] = $this->parseDefaultExpression($tableColumn['default']);
257	$fixed = \false;
258	break;
259	case 'interval':
260	$fixed = \false;
261	break;
262	case 'char':
263	case 'bpchar':
264	$fixed = \true;
265	break;
266	case 'float':
267	case 'float4':
268	case 'float8':
269	case 'double':
270	case 'double precision':
271	case 'real':
272	case 'decimal':
273	case 'money':
274	case 'numeric':
275	$tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']);
276	if (preg_match('([A-Za-z]+\$([0-9]+),([0-9]+)\$)', $tableColumn['complete_type'], $match) === 1) {
277	$precision = $match[1];
278	$scale = $match[2];
279	$length = null;
280	}
281	break;
282	case 'year':
283	$length = null;
284	break;
285	// PostgreSQL 9.4+ only
286	case 'jsonb':
287	$jsonb = \true;
288	break;
289	}
290	if ($tableColumn['default'] !== null && preg_match("('([^']+)'::)", $tableColumn['default'], $match) === 1) {
291	$tableColumn['default'] = $match[1];
292	}
293	$options = ['length' => $length, 'notnull' => (bool) $tableColumn['isnotnull'], 'default' => $tableColumn['default'], 'precision' => $precision, 'scale' => $scale, 'fixed' => $fixed, 'autoincrement' => $autoincrement, 'comment' => isset($tableColumn['comment']) && $tableColumn['comment'] !== '' ? $tableColumn['comment'] : null];
294	$column = new Column($tableColumn['field'], Type::getType($type), $options);
295	if (!empty($tableColumn['collation'])) {
296	$column->setPlatformOption('collation', $tableColumn['collation']);
297	}
298	if ($column->getType()->getName() === Types::JSON) {
299	if (!$column->getType() instanceof JsonType) {
300	Deprecation::trigger('doctrine/dbal', 'https://github.com/doctrine/dbal/pull/5049', <<<'DEPRECATION'
301	%s not extending %s while being named %s is deprecated,
302	and will lead to jsonb never to being used in 4.0.,
303	DEPRECATION
304	, get_class($column->getType()), JsonType::class, Types::JSON);
305	}
306	$column->setPlatformOption('jsonb', $jsonb);
307	}
308	return $column;
309	}
310	private function fixVersion94NegativeNumericDefaultValue($defaultValue)
311	{
312	if ($defaultValue !== null && strpos($defaultValue, '(') === 0) {
313	return trim($defaultValue, '()');
314	}
315	return $defaultValue;
316	}
317	private function parseDefaultExpression(?string $default) : ?string
318	{
319	if ($default === null) {
320	return $default;
321	}
322	return str_replace("''", "'", $default);
323	}
324	protected function selectTableNames(string $databaseName) : Result
325	{
326	$sql = <<<'SQL'
327	SELECT quote_ident(table_name) AS table_name,
328	table_schema AS schema_name
329	FROM information_schema.tables
330	WHERE table_catalog = ?
331	AND table_schema NOT LIKE 'pg\_%'
332	AND table_schema != 'information_schema'
333	AND table_name != 'geometry_columns'
334	AND table_name != 'spatial_ref_sys'
335	AND table_type = 'BASE TABLE'
336	ORDER BY
337	quote_ident(table_name)
338	SQL;
339	return $this->_conn->executeQuery($sql, [$databaseName]);
340	}
341	protected function selectTableColumns(string $databaseName, ?string $tableName = null) : Result
342	{
343	$sql = 'SELECT';
344	if ($tableName === null) {
345	$sql .= ' quote_ident(c.relname) AS table_name, quote_ident(n.nspname) AS schema_name,';
346	}
347	$sql .= sprintf(<<<'SQL'
348	a.attnum,
349	quote_ident(a.attname) AS field,
350	t.typname AS type,
351	format_type(a.atttypid, a.atttypmod) AS complete_type,
352	(SELECT tc.collcollate FROM pg_catalog.pg_collation tc WHERE tc.oid = a.attcollation) AS collation,
353	(SELECT t1.typname FROM pg_catalog.pg_type t1 WHERE t1.oid = t.typbasetype) AS domain_type,
354	(SELECT format_type(t2.typbasetype, t2.typtypmod) FROM
355	pg_catalog.pg_type t2 WHERE t2.typtype = 'd' AND t2.oid = a.atttypid) AS domain_complete_type,
356	a.attnotnull AS isnotnull,
357	(SELECT 't'
358	FROM pg_index
359	WHERE c.oid = pg_index.indrelid
360	AND pg_index.indkey[0] = a.attnum
361	AND pg_index.indisprimary = 't'
362	) AS pri,
363	(%s) AS default,
364	(SELECT pg_description.description
365	FROM pg_description WHERE pg_description.objoid = c.oid AND a.attnum = pg_description.objsubid
366	) AS comment
367	FROM pg_attribute a
368	INNER JOIN pg_class c
369	ON c.oid = a.attrelid
370	INNER JOIN pg_type t
371	ON t.oid = a.atttypid
372	INNER JOIN pg_namespace n
373	ON n.oid = c.relnamespace
374	LEFT JOIN pg_depend d
375	ON d.objid = c.oid
376	AND d.deptype = 'e'
377	AND d.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_class')
378	SQL
379	, $this->_platform->getDefaultColumnValueSQLSnippet());
380	$conditions = array_merge(['a.attnum > 0', "c.relkind = 'r'", 'd.refobjid IS NULL'], $this->buildQueryConditions($tableName));
381	$sql .= ' WHERE ' . implode(' AND ', $conditions) . ' ORDER BY a.attnum';
382	return $this->_conn->executeQuery($sql);
383	}
384	protected function selectIndexColumns(string $databaseName, ?string $tableName = null) : Result
385	{
386	$sql = 'SELECT';
387	if ($tableName === null) {
388	$sql .= ' quote_ident(tc.relname) AS table_name, quote_ident(tn.nspname) AS schema_name,';
389	}
390	$sql .= <<<'SQL'
391	quote_ident(ic.relname) AS relname,
392	i.indisunique,
393	i.indisprimary,
394	i.indkey,
395	i.indrelid,
396	pg_get_expr(indpred, indrelid) AS "where"
397	FROM pg_index i
398	JOIN pg_class AS tc ON tc.oid = i.indrelid
399	JOIN pg_namespace tn ON tn.oid = tc.relnamespace
400	JOIN pg_class AS ic ON ic.oid = i.indexrelid
401	WHERE ic.oid IN (
402	SELECT indexrelid
403	FROM pg_index i, pg_class c, pg_namespace n
404	SQL;
405	$conditions = array_merge(['c.oid = i.indrelid', 'c.relnamespace = n.oid'], $this->buildQueryConditions($tableName));
406	$sql .= ' WHERE ' . implode(' AND ', $conditions) . ') ORDER BY quote_ident(ic.relname)';
407	return $this->_conn->executeQuery($sql);
408	}
409	protected function selectForeignKeyColumns(string $databaseName, ?string $tableName = null) : Result
410	{
411	$sql = 'SELECT';
412	if ($tableName === null) {
413	$sql .= ' quote_ident(tc.relname) AS table_name, quote_ident(tn.nspname) AS schema_name,';
414	}
415	$sql .= <<<'SQL'
416	quote_ident(r.conname) as conname,
417	pg_get_constraintdef(r.oid, true) as condef
418	FROM pg_constraint r
419	JOIN pg_class AS tc ON tc.oid = r.conrelid
420	JOIN pg_namespace tn ON tn.oid = tc.relnamespace
421	WHERE r.conrelid IN
422	(
423	SELECT c.oid
424	FROM pg_class c, pg_namespace n
425	SQL;
426	$conditions = array_merge(['n.oid = c.relnamespace'], $this->buildQueryConditions($tableName));
427	$sql .= ' WHERE ' . implode(' AND ', $conditions) . ") AND r.contype = 'f' ORDER BY quote_ident(r.conname)";
428	return $this->_conn->executeQuery($sql);
429	}
430	protected function fetchTableOptionsByTable(string $databaseName, ?string $tableName = null) : array
431	{
432	$sql = <<<'SQL'
433	SELECT n.nspname AS schema_name,
434	c.relname AS table_name,
435	CASE c.relpersistence WHEN 'u' THEN true ELSE false END as unlogged,
436	obj_description(c.oid, 'pg_class') AS comment
437	FROM pg_class c
438	INNER JOIN pg_namespace n
439	ON n.oid = c.relnamespace
440	SQL;
441	$conditions = array_merge(["c.relkind = 'r'"], $this->buildQueryConditions($tableName));
442	$sql .= ' WHERE ' . implode(' AND ', $conditions);
443	$tableOptions = [];
444	foreach ($this->_conn->iterateAssociative($sql) as $row) {
445	$tableOptions[$this->_getPortableTableDefinition($row)] = $row;
446	}
447	return $tableOptions;
448	}
449	private function buildQueryConditions($tableName) : array
450	{
451	$conditions = [];
452	if ($tableName !== null) {
453	if (strpos($tableName, '.') !== \false) {
454	[$schemaName, $tableName] = explode('.', $tableName);
455	$conditions[] = 'n.nspname = ' . $this->_platform->quoteStringLiteral($schemaName);
456	} else {
457	$conditions[] = 'n.nspname = ANY(current_schemas(false))';
458	}
459	$identifier = new Identifier($tableName);
460	$conditions[] = 'c.relname = ' . $this->_platform->quoteStringLiteral($identifier->getName());
461	}
462	$conditions[] = "n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')";
463	return $conditions;
464	}
465	}
466