File data/src/Persistence/Sql/Postgresql/PlatformTrait.php

1:	<?php
2:
3:	declare(strict_types=1);
4:
5:	namespace Atk4\Data\Persistence\Sql\Postgresql;
6:
7:	use Doctrine\DBAL\Schema\Column;
8:	use Doctrine\DBAL\Schema\Table;
9:
10:	trait PlatformTrait
11:	{
12:	// standard PostgreSQL character types are case sensitive, unify the behaviour with other databases
13:	// with custom case insensitive types
14:
15:	/**
16:	* @return array<int, string>
17:	*/
18:	private function getCreateCaseInsensitiveDomainsSql(): array
19:	{
20:	$sqls = [];
21:
22:	$sqls[] = 'DO' . "\n"
23:	. '$$' . "\n"
24:	. 'BEGIN' . "\n"
25:	. ' CREATE EXTENSION IF NOT EXISTS citext;' . "\n"
26:	. implode("\n", array_map(static function (string $domain): string {
27:	return ' IF to_regtype(\'' . $domain . '\') IS NULL THEN' . "\n"
28:	. ' CREATE DOMAIN ' . $domain . ' AS citext;' . "\n"
29:	. ' END IF;';
30:	}, ['atk4__cichar', 'atk4__civarchar'])) . "\n"
31:	. 'END' . "\n"
32:	. '$$';
33:
34:	return $sqls;
35:	}
36:
37:	#[\Override]
38:	protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed)
39:	{
40:	return $fixed ? 'ATK4__CICHAR' : 'ATK4__CIVARCHAR';
41:	}
42:
43:	#[\Override]
44:	public function getClobTypeDeclarationSQL(array $column)
45:	{
46:	return 'CITEXT';
47:	}
48:
49:	#[\Override]
50:	protected function initializeDoctrineTypeMappings(): void
51:	{
52:	parent::initializeDoctrineTypeMappings();
53:
54:	// https://github.com/doctrine/dbal/pull/5495
55:	$this->doctrineTypeMapping['citext'] = 'text';
56:	}
57:
58:	#[\Override]
59:	public function getCurrentDatabaseExpression(bool $includeSchema = false): string
60:	{
61:	if ($includeSchema) {
62:	return 'CONCAT(CURRENT_DATABASE(), \'.\', CURRENT_SCHEMA())';
63:	}
64:
65:	return parent::getCurrentDatabaseExpression();
66:	}
67:
68:	#[\Override]
69:	public function convertBooleansToDatabaseValue($item)
70:	{
71:	return $item;
72:	}
73:
74:	// PostgreSQL DBAL platform uses SERIAL column type for autoincrement which does not increment
75:	// when a row with a not-null PK is inserted like Sqlite or MySQL does, unify the behaviour
76:
77:	private function getPrimaryKeyColumn(Table $table): ?Column
78:	{
79:	if ($table->getPrimaryKey() === null) {
80:	return null;
81:	}
82:
83:	return $table->getColumn($table->getPrimaryKey()->getColumns()[0]);
84:	}
85:
86:	/**
87:	* @return array<int, string>
88:	*/
89:	protected function getCreateAutoincrementSql(Table $table, Column $pkColumn): array
90:	{
91:	$sqls = [];
92:
93:	$pkSeqName = $this->getIdentitySequenceName($table->getName(), $pkColumn->getName());
94:
95:	$sqls[] = (new Expression(
96:	// else branch should be maybe (because of concurrency) put into after update trigger
97:	// with pure nextval instead of setval with a loop like in Oracle trigger
98:	str_replace('[pk_seq]', '\'' . $pkSeqName . '\'', <<<'EOF'
99:	CREATE OR REPLACE FUNCTION {{trigger_func}}()
100:	RETURNS trigger AS $$
101:	DECLARE
102:	atk4__pk_seq_last__ {table}.{pk}%TYPE;
103:	BEGIN
104:	IF (NEW.{pk} IS NULL) THEN
105:	NEW.{pk} := nextval([pk_seq]);
106:	ELSE
107:	SELECT COALESCE(last_value, 0) INTO atk4__pk_seq_last__ FROM {{pk_seq}};
108:	IF (atk4__pk_seq_last__ <= NEW.{pk}) THEN
109:	atk4__pk_seq_last__ := setval([pk_seq], NEW.{pk}, true);
110:	END IF;
111:	END IF;
112:	RETURN NEW;
113:	END;
114:	$$ LANGUAGE plpgsql
115:	EOF),
116:	[
117:	'table' => $table->getShortestName($table->getNamespaceName()), // TODO should be probably name /w schema, but it is not supported, get variable type differently
118:	'pk' => $pkColumn->getName(),
119:	'pk_seq' => $pkSeqName,
120:	'trigger_func' => $table->getName() . '_AI_FUNC',
121:	]
122:	))->render()[0];
123:
124:	$sqls[] = (new Expression(
125:	<<<'EOF'
126:	CREATE TRIGGER {trigger}
127:	BEFORE INSERT OR UPDATE
128:	ON {{table}}
129:	FOR EACH ROW
130:	EXECUTE PROCEDURE {{trigger_func}}()
131:	EOF,
132:	[
133:	'table' => $table->getName(),
134:	'trigger' => $table->getShortestName($table->getNamespaceName()) . '_AI_PK',
135:	'trigger_func' => $table->getName() . '_AI_FUNC',
136:	]
137:	))->render()[0];
138:
139:	return $sqls;
140:	}
141:
142:	#[\Override]
143:	public function getCreateTableSQL(Table $table, $createFlags = self::CREATE_INDEXES)
144:	{
145:	$sqls = array_merge(
146:	$this->getCreateCaseInsensitiveDomainsSql(),
147:	parent::getCreateTableSQL($table, $createFlags)
148:	);
149:
150:	$pkColumn = $this->getPrimaryKeyColumn($table);
151:	if ($pkColumn !== null) {
152:	$sqls = array_merge($sqls, $this->getCreateAutoincrementSql($table, $pkColumn));
153:	}
154:
155:	return $sqls;
156:	}
157:	}
158:

Namespaces

Classes

Traits