Skip to content

Handle UDTs in non-public schemas in GetSchema #4394

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
roji merged 1 commit into from
Mar 30, 2022
Merged

Handle UDTs in non-public schemas in GetSchema #4394

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
40 changes: 25 additions & 15 deletions src/Npgsql/NpgsqlSchema.cs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -232,24 +232,34 @@ static async Task<DataTable> GetColumns(NpgsqlConnection conn, string?[]? restri
{
var columns = new DataTable("Columns") { Locale = CultureInfo.InvariantCulture };

columns.Columns.AddRange(new[] {
new DataColumn("table_catalog"), new DataColumn("table_schema"), new DataColumn("table_name"),
new DataColumn("column_name"), new DataColumn("ordinal_position", typeof(int)), new DataColumn("column_default"),
new DataColumn("is_nullable"), new DataColumn("data_type"),
new DataColumn("character_maximum_length", typeof(int)), new DataColumn("character_octet_length", typeof(int)),
new DataColumn("numeric_precision", typeof(int)), new DataColumn("numeric_precision_radix", typeof(int)),
new DataColumn("numeric_scale", typeof(int)), new DataColumn("datetime_precision", typeof(int)),
new DataColumn("character_set_catalog"), new DataColumn("character_set_schema"),
new DataColumn("character_set_name"), new DataColumn("collation_catalog")
columns.Columns.AddRange(new DataColumn[] {
new("table_catalog"), new("table_schema"), new("table_name"), new("column_name"),
new("ordinal_position", typeof(int)),
new("column_default"),
new("is_nullable"),
new("data_type"),
new("character_maximum_length", typeof(int)), new("character_octet_length", typeof(int)),
new("numeric_precision", typeof(int)), new("numeric_precision_radix", typeof(int)), new("numeric_scale", typeof(int)),
new("datetime_precision", typeof(int)),
new("character_set_catalog"), new("character_set_schema"), new("character_set_name"),
new("collation_catalog")
});

var getColumns = new StringBuilder(@"
SELECT
table_catalog, table_schema, table_name, column_name, ordinal_position, column_default, is_nullable,
udt_name::regtype::text AS data_type, character_maximum_length, character_octet_length, numeric_precision,
numeric_precision_radix, numeric_scale, datetime_precision, character_set_catalog, character_set_schema,
character_set_name, collation_catalog
FROM information_schema.columns");
table_catalog, table_schema, table_name, column_name,
ordinal_position,
column_default,
is_nullable,
CASE WHEN udt_schema is NULL THEN udt_name ELSE format_type(typ.oid, NULL) END AS data_type,
character_maximum_length, character_octet_length,
numeric_precision, numeric_precision_radix, numeric_scale,
datetime_precision,
character_set_catalog, character_set_schema, character_set_name,
collation_catalog
FROM information_schema.columns
JOIN pg_namespace AS ns ON ns.nspname = udt_schema
JOIN pg_type AS typ ON typnamespace = ns.oid AND typname = udt_name");

using var command = BuildCommand(conn, getColumns, restrictions, "table_catalog", "table_schema", "table_name", "column_name");
using var adapter = new NpgsqlDataAdapter(command);
Expand Down Expand Up @@ -858,4 +868,4 @@ static DataTable GetReservedWords()
};

#endregion Reserved Keywords
}
}
50 changes: 50 additions & 0 deletions test/Npgsql.Tests/SchemaTests.cs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -541,6 +541,56 @@ CONSTRAINT types_table_pkey PRIMARY KEY(p0)
}
}

[Test, IssueLink("https://github.com/npgsql/npgsql/issues/4392")]
public async Task Enum_in_public_schema()
{
using var conn = OpenConnection();
try
{
conn.ExecuteNonQuery(@"
CREATE TYPE colors AS ENUM ('red', 'yellow', 'blue');

DROP TABLE IF EXISTS data;
CREATE TABLE data (color colors);");

var dataTable = await GetSchema(conn, "Columns", new[] { null, null, "data" });
var row = dataTable.Rows.Cast<DataRow>().Single();
Assert.That(row["data_type"], Is.EqualTo("colors"));
}
finally
{
conn.ExecuteNonQuery(@"
DROP TABLE IF EXISTS data;
DROP TYPE IF EXISTS colors;");
}
}

[Test, IssueLink("https://github.com/npgsql/npgsql/issues/4392")]
public async Task Enum_in_non_public_schema()
{
using var conn = OpenConnection();
try
{
conn.ExecuteNonQuery(@"
DROP SCHEMA IF EXISTS enum_schema CASCADE;
CREATE SCHEMA enum_schema;
CREATE TYPE enum_schema.colors AS ENUM ('red', 'yellow', 'blue');

DROP TABLE IF EXISTS data;
CREATE TABLE data (color enum_schema.colors);");

var dataTable = await GetSchema(conn, "Columns", new[] { null, null, "data" });
var row = dataTable.Rows.Cast<DataRow>().Single();
Assert.That(row["data_type"], Is.EqualTo("enum_schema.colors"));
}
finally
{
conn.ExecuteNonQuery(@"
DROP TABLE IF EXISTS data;
DROP SCHEMA IF EXISTS enum_schema CASCADE;");
}
}

public SchemaTests(SyncOrAsync syncOrAsync) : base(syncOrAsync) { }

async Task<DataTable> GetSchema(NpgsqlConnection conn)
Expand Down