|
1
|
|
|
<?php |
|
2
|
|
|
/** |
|
3
|
|
|
* components |
|
4
|
|
|
* |
|
5
|
|
|
* @author Wolfy-J |
|
6
|
|
|
*/ |
|
7
|
|
|
namespace Spiral\Database\Drivers\Postgres\Schemas; |
|
8
|
|
|
|
|
9
|
|
|
use Spiral\Database\Schemas\Prototypes\AbstractColumn; |
|
10
|
|
|
use Spiral\Database\Schemas\Prototypes\AbstractIndex; |
|
11
|
|
|
use Spiral\Database\Schemas\Prototypes\AbstractReference; |
|
12
|
|
|
use Spiral\Database\Schemas\Prototypes\AbstractTable; |
|
13
|
|
|
|
|
14
|
|
|
class PostgresTable extends AbstractTable |
|
15
|
|
|
{ |
|
16
|
|
|
/** |
|
17
|
|
|
* Found table sequences. |
|
18
|
|
|
* |
|
19
|
|
|
* @var array |
|
20
|
|
|
*/ |
|
21
|
|
|
private $sequences = []; |
|
22
|
|
|
|
|
23
|
|
|
/** |
|
24
|
|
|
* Sequence object name usually defined only for primary keys and required by ORM to correctly |
|
25
|
|
|
* resolve inserted row id. |
|
26
|
|
|
* |
|
27
|
|
|
* @var string|null |
|
28
|
|
|
*/ |
|
29
|
|
|
private $primarySequence = null; |
|
30
|
|
|
|
|
31
|
|
|
/** |
|
32
|
|
|
* Sequence object name usually defined only for primary keys and required by ORM to correctly |
|
33
|
|
|
* resolve inserted row id. |
|
34
|
|
|
* |
|
35
|
|
|
* @return string|null |
|
36
|
|
|
*/ |
|
37
|
|
|
public function getSequence() |
|
38
|
|
|
{ |
|
39
|
|
|
return $this->primarySequence; |
|
40
|
|
|
} |
|
41
|
|
|
|
|
42
|
|
|
/** |
|
43
|
|
|
* {@inheritdoc} |
|
44
|
|
|
*/ |
|
45
|
|
|
protected function fetchColumns(): array |
|
46
|
|
|
{ |
|
47
|
|
|
//Required for constraints fetch |
|
48
|
|
|
$tableOID = $this->driver->query('SELECT oid FROM pg_class WHERE relname = ?', [ |
|
49
|
|
|
$this->getName(), |
|
50
|
|
|
])->fetchColumn(); |
|
51
|
|
|
|
|
52
|
|
|
$query = $this->driver->query( |
|
53
|
|
|
'SELECT * FROM information_schema.columns JOIN pg_type ON (pg_type.typname = columns.udt_name) WHERE table_name = ?', |
|
54
|
|
|
[$this->getName()] |
|
55
|
|
|
); |
|
56
|
|
|
|
|
57
|
|
|
$result = []; |
|
58
|
|
|
foreach ($query->bind('column_name', $name) as $schema) { |
|
59
|
|
|
if (preg_match( |
|
60
|
|
|
'/^nextval\([\'"]([a-z0-9_"]+)[\'"](?:::regclass)?\)$/i', |
|
61
|
|
|
$schema['column_default'], |
|
62
|
|
|
$matches |
|
63
|
|
|
)) { |
|
64
|
|
|
//Column is sequential |
|
65
|
|
|
$this->sequences[$name] = $matches[1]; |
|
66
|
|
|
} |
|
67
|
|
|
|
|
68
|
|
|
$result[] = PostgresColumn::createInstance( |
|
69
|
|
|
$this->getName(), |
|
70
|
|
|
$schema + ['tableOID' => $tableOID], |
|
71
|
|
|
$this->driver |
|
72
|
|
|
); |
|
73
|
|
|
} |
|
74
|
|
|
|
|
75
|
|
|
return $result; |
|
76
|
|
|
} |
|
77
|
|
|
|
|
78
|
|
|
/** |
|
79
|
|
|
* {@inheritdoc} |
|
80
|
|
|
*/ |
|
81
|
|
|
protected function fetchIndexes(bool $all = false): array |
|
82
|
|
|
{ |
|
83
|
|
|
$query = "SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = ?"; |
|
84
|
|
|
|
|
85
|
|
|
$result = []; |
|
86
|
|
|
foreach ($this->driver->query($query, [$this->getName()]) as $schema) { |
|
87
|
|
|
$conType = $this->driver->query( |
|
88
|
|
|
'SELECT contype FROM pg_constraint WHERE conname = ?', |
|
89
|
|
|
[$schema['indexname']] |
|
90
|
|
|
)->fetchColumn(); |
|
91
|
|
|
|
|
92
|
|
|
if ($conType == 'p') { |
|
93
|
|
|
//Skipping primary keys |
|
94
|
|
|
continue; |
|
95
|
|
|
} |
|
96
|
|
|
|
|
97
|
|
|
$result[] = PostgresIndex::createInstance($this->getName(), $schema); |
|
98
|
|
|
} |
|
99
|
|
|
|
|
100
|
|
|
return $result; |
|
101
|
|
|
} |
|
102
|
|
|
|
|
103
|
|
|
/** |
|
104
|
|
|
* {@inheritdoc} |
|
105
|
|
|
*/ |
|
106
|
|
|
protected function fetchReferences(): array |
|
107
|
|
|
{ |
|
108
|
|
|
//Mindblowing |
|
109
|
|
|
$query = 'SELECT tc.constraint_name, tc.table_name, kcu.column_name, rc.update_rule, ' |
|
110
|
|
|
. 'rc.delete_rule, ccu.table_name AS foreign_table_name, ' |
|
111
|
|
|
. "ccu.column_name AS foreign_column_name\n" |
|
112
|
|
|
. "FROM information_schema.table_constraints AS tc\n" |
|
113
|
|
|
. "JOIN information_schema.key_column_usage AS kcu\n" |
|
114
|
|
|
. " ON tc.constraint_name = kcu.constraint_name\n" |
|
115
|
|
|
. "JOIN information_schema.constraint_column_usage AS ccu\n" |
|
116
|
|
|
. " ON ccu.constraint_name = tc.constraint_name\n" |
|
117
|
|
|
. "JOIN information_schema.referential_constraints AS rc\n" |
|
118
|
|
|
. " ON rc.constraint_name = tc.constraint_name\n" |
|
119
|
|
|
. "WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name = ?"; |
|
120
|
|
|
|
|
121
|
|
|
$result = []; |
|
122
|
|
|
|
|
123
|
|
View Code Duplication |
foreach ($this->driver->query($query, [$this->getName()]) as $schema) { |
|
|
|
|
|
|
124
|
|
|
$result[] = PostgresReference::createInstance( |
|
125
|
|
|
$this->getName(), |
|
126
|
|
|
$this->getPrefix(), |
|
127
|
|
|
$schema |
|
128
|
|
|
); |
|
129
|
|
|
} |
|
130
|
|
|
|
|
131
|
|
|
return $result; |
|
132
|
|
|
} |
|
133
|
|
|
|
|
134
|
|
|
/** |
|
135
|
|
|
* {@inheritdoc} |
|
136
|
|
|
*/ |
|
137
|
|
|
protected function fetchPrimaryKeys(): array |
|
138
|
|
|
{ |
|
139
|
|
|
$query = "SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = ?"; |
|
140
|
|
|
|
|
141
|
|
|
foreach ($this->driver->query($query, [$this->getName()]) as $schema) { |
|
142
|
|
|
$conType = $this->driver->query( |
|
143
|
|
|
'SELECT contype FROM pg_constraint WHERE conname = ?', |
|
144
|
|
|
[$schema['indexname']] |
|
145
|
|
|
)->fetchColumn(); |
|
146
|
|
|
|
|
147
|
|
|
if ($conType != 'p') { |
|
148
|
|
|
//Skipping primary keys |
|
149
|
|
|
continue; |
|
150
|
|
|
} |
|
151
|
|
|
|
|
152
|
|
|
//To simplify definitions |
|
153
|
|
|
$index = PostgresIndex::createInstance($this->getName(), $schema); |
|
154
|
|
|
|
|
155
|
|
|
if (is_array($this->primarySequence) && count($index->getColumns()) === 1) { |
|
156
|
|
|
$column = $index->getColumns()[0]; |
|
157
|
|
|
|
|
158
|
|
|
if (isset($this->sequences[$column])) { |
|
159
|
|
|
//We found our primary sequence |
|
160
|
|
|
$this->primarySequence = $this->sequences[$column]; |
|
161
|
|
|
} |
|
162
|
|
|
} |
|
163
|
|
|
|
|
164
|
|
|
return $index->getColumns(); |
|
165
|
|
|
} |
|
166
|
|
|
|
|
167
|
|
|
return []; |
|
168
|
|
|
} |
|
169
|
|
|
|
|
170
|
|
|
/** |
|
171
|
|
|
* {@inheritdoc} |
|
172
|
|
|
*/ |
|
173
|
|
|
protected function createColumn(string $name): AbstractColumn |
|
174
|
|
|
{ |
|
175
|
|
|
return new PostgresColumn($this->getName(), $name); |
|
176
|
|
|
} |
|
177
|
|
|
|
|
178
|
|
|
/** |
|
179
|
|
|
* {@inheritdoc} |
|
180
|
|
|
*/ |
|
181
|
|
|
protected function createIndex(string $name): AbstractIndex |
|
182
|
|
|
{ |
|
183
|
|
|
return new PostgresIndex($this->getName(), $name); |
|
184
|
|
|
} |
|
185
|
|
|
|
|
186
|
|
|
/** |
|
187
|
|
|
* {@inheritdoc} |
|
188
|
|
|
*/ |
|
189
|
|
|
protected function createForeign(string $name): AbstractReference |
|
190
|
|
|
{ |
|
191
|
|
|
return new PostgresReference($this->getName(), $this->getPrefix(), $name); |
|
192
|
|
|
} |
|
193
|
|
|
} |
spiral /
components
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.