Skip to content

Commit 57ae89a

Browse files
hafezdivandarihafezdivandari
authored
[10.x] Get user-defined types on PostgreSQL (#49303)
* get user defined types * wip * fix tests * fix tests * wip * fix tests
1 parent 2f65457 commit 57ae89a

File tree

7 files changed

+156
-8
lines changed

7 files changed

+156
-8
lines changed

‎src/Illuminate/Database/Query/Processors/PostgresProcessor.php‎

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -45,6 +45,54 @@ public function processColumnListing($results)
4545
}, $results);
4646
}
4747

48+
/**
49+
* Process the results of a types query.
50+
*
51+
* @param array $results
52+
* @return array
53+
*/
54+
public function processTypes($results)
55+
{
56+
return array_map(function ($result) {
57+
$result = (object) $result;
58+
59+
return [
60+
'name' => $result->name,
61+
'schema' => $result->schema,
62+
'implicit' => (bool) $result->implicit,
63+
'type' => match (strtolower($result->type)) {
64+
'b' => 'base',
65+
'c' => 'composite',
66+
'd' => 'domain',
67+
'e' => 'enum',
68+
'p' => 'pseudo',
69+
'r' => 'range',
70+
'm' => 'multirange',
71+
default => null,
72+
},
73+
'category' => match (strtolower($result->category)) {
74+
'a' => 'array',
75+
'b' => 'boolean',
76+
'c' => 'composite',
77+
'd' => 'date_time',
78+
'e' => 'enum',
79+
'g' => 'geometric',
80+
'i' => 'network_address',
81+
'n' => 'numeric',
82+
'p' => 'pseudo',
83+
'r' => 'range',
84+
's' => 'string',
85+
't' => 'timespan',
86+
'u' => 'user_defined',
87+
'v' => 'bit_string',
88+
'x' => 'unknown',
89+
'z' => 'internal_use',
90+
default => null,
91+
},
92+
];
93+
}, $results);
94+
}
95+
4896
/**
4997
* Process the results of a columns query.
5098
*

‎src/Illuminate/Database/Query/Processors/Processor.php‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -77,6 +77,17 @@ public function processViews($results)
7777
}, $results);
7878
}
7979

80+
/**
81+
* Process the results of a types query.
82+
*
83+
* @param array $results
84+
* @return array
85+
*/
86+
public function processTypes($results)
87+
{
88+
return $results;
89+
}
90+
8091
/**
8192
* Process the results of a columns query.
8293
*

‎src/Illuminate/Database/Schema/Builder.php‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -211,6 +211,16 @@ public function getViews()
211211
);
212212
}
213213

214+
/**
215+
* Get the user-defined types that belong to the database.
216+
*
217+
* @return array
218+
*/
219+
public function getTypes()
220+
{
221+
throw new LogicException('This database driver does not support user-defined types.');
222+
}
223+
214224
/**
215225
* Get all of the table names for the database.
216226
*

‎src/Illuminate/Database/Schema/Grammars/PostgresGrammar.php‎

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -101,6 +101,23 @@ public function compileViews()
101101
return "select viewname as name, schemaname as schema, definition from pg_views where schemaname not in ('pg_catalog', 'information_schema') order by viewname";
102102
}
103103

104+
/**
105+
* Compile the query to determine the user-defined types.
106+
*
107+
* @return string
108+
*/
109+
public function compileTypes()
110+
{
111+
return 'select t.typname as name, n.nspname as schema, t.typtype as type, t.typcategory as category, '
112+
."((t.typinput = 'array_in'::regproc and t.typoutput = 'array_out'::regproc) or t.typtype = 'm') as implicit "
113+
.'from pg_type t join pg_namespace n on n.oid = t.typnamespace '
114+
.'left join pg_class c on c.oid = t.typrelid '
115+
.'left join pg_type el on el.oid = t.typelem '
116+
.'left join pg_class ce on ce.oid = el.typrelid '
117+
."where ((t.typrelid = 0 and (ce.relkind = 'c' or ce.relkind is null)) or c.relkind = 'c') "
118+
."and n.nspname not in ('pg_catalog', 'information_schema')";
119+
}
120+
104121
/**
105122
* Compile the SQL needed to retrieve all table names.
106123
*
@@ -503,9 +520,22 @@ public function compileDropAllTypes($types)
503520
return 'drop type '.implode(',', $this->escapeNames($types)).' cascade';
504521
}
505522

523+
/**
524+
* Compile the SQL needed to drop all domains.
525+
*
526+
* @param array $domains
527+
* @return string
528+
*/
529+
public function compileDropAllDomains($domains)
530+
{
531+
return 'drop domain '.implode(',', $this->escapeNames($domains)).' cascade';
532+
}
533+
506534
/**
507535
* Compile the SQL needed to retrieve all type names.
508536
*
537+
* @deprecated Will be removed in a future Laravel version.
538+
*
509539
* @return string
510540
*/
511541
public function compileGetAllTypes()

‎src/Illuminate/Database/Schema/PostgresBuilder.php‎

Lines changed: 29 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,18 @@ public function hasTable($table)
5353
)) > 0;
5454
}
5555

56+
/**
57+
* Get the user-defined types that belong to the database.
58+
*
59+
* @return array
60+
*/
61+
public function getTypes()
62+
{
63+
return $this->connection->getPostProcessor()->processTypes(
64+
$this->connection->selectFromWriteConnection($this->grammar->compileTypes())
65+
);
66+
}
67+
5668
/**
5769
* Get all of the table names for the database.
5870
*
@@ -151,6 +163,8 @@ public function dropAllViews()
151163
/**
152164
* Get all of the type names for the database.
153165
*
166+
* @deprecated Will be removed in a future Laravel version.
167+
*
154168
* @return array
155169
*/
156170
public function getAllTypes()
@@ -168,20 +182,27 @@ public function getAllTypes()
168182
public function dropAllTypes()
169183
{
170184
$types = [];
185+
$domains = [];
171186

172-
foreach ($this->getAllTypes() as $row) {
173-
$row = (array) $row;
187+
$schemas = $this->grammar->escapeNames($this->getSchemas());
174188

175-
$types[] = reset($row);
189+
foreach ($this->getTypes() as $type) {
190+
if (! $type['implicit'] && in_array($this->grammar->escapeNames([$type['schema']])[0], $schemas)) {
191+
if ($type['type'] === 'domain') {
192+
$domains[] = $type['schema'].'.'.$type['name'];
193+
} else {
194+
$types[] = $type['schema'].'.'.$type['name'];
195+
}
196+
}
176197
}
177198

178-
if (empty($types)) {
179-
return;
199+
if (! empty($types)) {
200+
$this->connection->statement($this->grammar->compileDropAllTypes($types));
180201
}
181202

182-
$this->connection->statement(
183-
$this->grammar->compileDropAllTypes($types)
184-
);
203+
if (! empty($domains)) {
204+
$this->connection->statement($this->grammar->compileDropAllDomains($domains));
205+
}
185206
}
186207

187208
/**

‎src/Illuminate/Support/Facades/Schema.php‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,7 @@
1414
* @method static bool hasView(string $view)
1515
* @method static array getTables()
1616
* @method static array getViews()
17+
* @method static array getTypes()
1718
* @method static bool hasColumn(string $table, string $column)
1819
* @method static bool hasColumns(string $table, array $columns)
1920
* @method static void whenTableHasColumn(string $table, string $column, \Closure $callback)

‎tests/Integration/Database/SchemaBuilderTest.php‎

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -182,6 +182,33 @@ public function testGetViews()
182182
$this->assertEmpty(array_diff(['foo', 'bar', 'baz'], array_column($views, 'name')));
183183
}
184184

185+
public function testGetAndDropTypes()
186+
{
187+
if ($this->driver !== 'pgsql') {
188+
$this->markTestSkipped('Test requires a PostgreSQL connection.');
189+
}
190+
191+
DB::statement('create type pseudo_foo');
192+
DB::statement('create type comp_foo as (f1 int, f2 text)');
193+
DB::statement("create type enum_foo as enum ('new', 'open', 'closed')");
194+
DB::statement('create type range_foo as range (subtype = float8)');
195+
DB::statement('create domain domain_foo as text');
196+
197+
$types = Schema::getTypes();
198+
199+
$this->assertCount(11, $types);
200+
$this->assertTrue(collect($types)->contains(fn ($type) => $type['name'] === 'pseudo_foo' && $type['type'] === 'pseudo' && ! $type['implicit']));
201+
$this->assertTrue(collect($types)->contains(fn ($type) => $type['name'] === 'comp_foo' && $type['type'] === 'composite' && ! $type['implicit']));
202+
$this->assertTrue(collect($types)->contains(fn ($type) => $type['name'] === 'enum_foo' && $type['type'] === 'enum' && ! $type['implicit']));
203+
$this->assertTrue(collect($types)->contains(fn ($type) => $type['name'] === 'range_foo' && $type['type'] === 'range' && ! $type['implicit']));
204+
$this->assertTrue(collect($types)->contains(fn ($type) => $type['name'] === 'domain_foo' && $type['type'] === 'domain' && ! $type['implicit']));
205+
206+
Schema::dropAllTypes();
207+
$types = Schema::getTypes();
208+
209+
$this->assertEmpty($types);
210+
}
211+
185212
public function testGetIndexes()
186213
{
187214
Schema::create('foo', function (Blueprint $table) {

0 commit comments

Comments
 (0)