using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Npgsql.Internal;
using Npgsql.PostgresTypes;
using NpgsqlTypes;
namespace Npgsql;
///
/// Provides the underlying mechanism for reading schema information.
///
static class NpgsqlSchema
{
public static Task GetSchema(bool async, NpgsqlConnection conn, string? collectionName, string?[]? restrictions, CancellationToken cancellationToken = default)
{
if (collectionName is null)
throw new ArgumentNullException(nameof(collectionName));
if (collectionName.Length == 0)
throw new ArgumentException("Collection name cannot be empty.", nameof(collectionName));
return collectionName.ToUpperInvariant() switch
{
"METADATACOLLECTIONS" => Task.FromResult(GetMetaDataCollections()),
"RESTRICTIONS" => Task.FromResult(GetRestrictions()),
"DATASOURCEINFORMATION" => Task.FromResult(GetDataSourceInformation(conn)),
"DATATYPES" => Task.FromResult(GetDataTypes(conn)),
"RESERVEDWORDS" => Task.FromResult(GetReservedWords()),
// custom collections for npgsql
"DATABASES" => GetDatabases(conn, restrictions, async, cancellationToken),
"SCHEMATA" => GetSchemata(conn, restrictions, async, cancellationToken),
"TABLES" => GetTables(conn, restrictions, async, cancellationToken),
"COLUMNS" => GetColumns(conn, restrictions, async, cancellationToken),
"VIEWS" => GetViews(conn, restrictions, async, cancellationToken),
"MATERIALIZEDVIEWS" => GetMaterializedViews(conn, restrictions, async, cancellationToken),
"USERS" => GetUsers(conn, restrictions, async, cancellationToken),
"INDEXES" => GetIndexes(conn, restrictions, async, cancellationToken),
"INDEXCOLUMNS" => GetIndexColumns(conn, restrictions, async, cancellationToken),
"CONSTRAINTS" => GetConstraints(conn, restrictions, collectionName, async, cancellationToken),
"PRIMARYKEY" => GetConstraints(conn, restrictions, collectionName, async, cancellationToken),
"UNIQUEKEYS" => GetConstraints(conn, restrictions, collectionName, async, cancellationToken),
"FOREIGNKEYS" => GetConstraints(conn, restrictions, collectionName, async, cancellationToken),
"CONSTRAINTCOLUMNS" => GetConstraintColumns(conn, restrictions, async, cancellationToken),
_ => throw new ArgumentOutOfRangeException(nameof(collectionName), collectionName, "Invalid collection name.")
};
}
///
/// Returns the MetaDataCollections that lists all possible collections.
///
/// The MetaDataCollections
static DataTable GetMetaDataCollections()
{
var table = new DataTable("MetaDataCollections");
table.Columns.Add("CollectionName", typeof(string));
table.Columns.Add("NumberOfRestrictions", typeof(int));
table.Columns.Add("NumberOfIdentifierParts", typeof(int));
table.Rows.Add("MetaDataCollections", 0, 0);
table.Rows.Add("DataSourceInformation", 0, 0);
table.Rows.Add("Restrictions", 0, 0);
table.Rows.Add("DataTypes", 0, 0); // TODO: Support type name restriction
table.Rows.Add("Databases", 1, 1);
table.Rows.Add("Tables", 4, 3);
table.Rows.Add("Columns", 4, 4);
table.Rows.Add("Views", 3, 3);
table.Rows.Add("Users", 1, 1);
table.Rows.Add("Indexes", 4, 4);
table.Rows.Add("IndexColumns", 5, 5);
return table;
}
///
/// Returns the Restrictions that contains the meaning and position of the values in the restrictions array.
///
/// The Restrictions
static DataTable GetRestrictions()
{
var table = new DataTable("Restrictions");
table.Columns.Add("CollectionName", typeof(string));
table.Columns.Add("RestrictionName", typeof(string));
table.Columns.Add("RestrictionDefault", typeof(string));
table.Columns.Add("RestrictionNumber", typeof(int));
table.Rows.Add("Database", "Name", "Name", 1);
table.Rows.Add("Tables", "Catalog", "table_catalog", 1);
table.Rows.Add("Tables", "Schema", "schema_catalog", 2);
table.Rows.Add("Tables", "Table", "table_name", 3);
table.Rows.Add("Tables", "TableType", "table_type", 4);
table.Rows.Add("Columns", "Catalog", "table_catalog", 1);
table.Rows.Add("Columns", "Schema", "table_schema", 2);
table.Rows.Add("Columns", "TableName", "table_name", 3);
table.Rows.Add("Columns", "Column", "column_name", 4);
table.Rows.Add("Views", "Catalog", "table_catalog", 1);
table.Rows.Add("Views", "Schema", "table_schema", 2);
table.Rows.Add("Views", "Table", "table_name", 3);
table.Rows.Add("Users", "User", "user_name", 1);
table.Rows.Add("Indexes", "Catalog", "table_catalog", 1);
table.Rows.Add("Indexes", "Schema", "table_schema", 2);
table.Rows.Add("Indexes", "Table", "table_name", 3);
table.Rows.Add("Indexes", "Index", "index_name", 4);
table.Rows.Add("IndexColumns", "Catalog", "table_catalog", 1);
table.Rows.Add("IndexColumns", "Schema", "table_schema", 2);
table.Rows.Add("IndexColumns", "Table", "table_name", 3);
table.Rows.Add("IndexColumns", "Index", "index_name", 4);
table.Rows.Add("IndexColumns", "Column", "column_name", 5);
return table;
}
static NpgsqlCommand BuildCommand(NpgsqlConnection conn, StringBuilder query, string?[]? restrictions, params string[]? names)
=> BuildCommand(conn, query, restrictions, true, names);
static NpgsqlCommand BuildCommand(NpgsqlConnection conn, StringBuilder query, string?[]? restrictions, bool addWhere, params string[]? names)
{
var command = new NpgsqlCommand();
if (restrictions != null && names != null)
{
for (var i = 0; i < restrictions.Length && i < names.Length; ++i)
{
if (restrictions[i] is { Length: > 0 } restriction)
{
if (addWhere)
{
query.Append(" WHERE ");
addWhere = false;
}
else
{
query.Append(" AND ");
}
var paramName = RemoveSpecialChars(names[i]);
query.AppendFormat("{0} = :{1}", names[i], paramName);
command.Parameters.Add(new NpgsqlParameter(paramName, restriction));
}
}
}
command.CommandText = query.ToString();
command.Connection = conn;
return command;
}
static string RemoveSpecialChars(string paramName)
=> paramName.Replace("(", "").Replace(")", "").Replace(".", "");
static Task GetDatabases(NpgsqlConnection conn, string?[]? restrictions, bool async, CancellationToken cancellationToken = default)
{
var dataTable = new DataTable("Databases")
{
Locale = CultureInfo.InvariantCulture,
Columns =
{
new DataColumn("database_name"),
new DataColumn("owner"),
new DataColumn("encoding")
}
};
var sql = new StringBuilder();
sql.Append(
"""
SELECT d.datname, u.usename, pg_catalog.pg_encoding_to_char(d.encoding)
FROM pg_catalog.pg_database d
LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid
""");
return ParseResults(
async,
BuildCommand(conn, sql, restrictions, "datname"),
dataTable,
(reader, row) =>
{
row["database_name"] = GetFieldValueOrDBNull(reader, 0);
row["owner"] = GetFieldValueOrDBNull(reader, 1);
row["encoding"] = GetFieldValueOrDBNull(reader, 2);
}, cancellationToken);
}
static Task GetSchemata(NpgsqlConnection conn, string?[]? restrictions, bool async, CancellationToken cancellationToken = default)
{
var dataTable = new DataTable("Schemata")
{
Locale = CultureInfo.InvariantCulture,
Columns =
{
new DataColumn("catalog_name"),
new DataColumn("schema_name"),
new DataColumn("schema_owner")
}
};
var sql = new StringBuilder(
"""
SELECT * FROM (
SELECT current_database(), nspname, r.rolname
FROM pg_catalog.pg_namespace
LEFT JOIN pg_catalog.pg_roles r ON r.oid = nspowner
) tmp
""");
return ParseResults(
async,
BuildCommand(conn, sql, restrictions, "catalog_name", "schema_name", "schema_owner"),
dataTable,
(reader, row) =>
{
row["catalog_name"] = GetFieldValueOrDBNull(reader, 0);
row["schema_name"] = GetFieldValueOrDBNull(reader, 1);
row["schema_owner"] = GetFieldValueOrDBNull(reader, 2);
}, cancellationToken);
}
static Task GetTables(NpgsqlConnection conn, string?[]? restrictions, bool async, CancellationToken cancellationToken = default)
{
var dataTable = new DataTable("Tables")
{
Locale = CultureInfo.InvariantCulture,
Columns =
{
new DataColumn("table_catalog"),
new DataColumn("table_schema"),
new DataColumn("table_name"),
new DataColumn("table_type")
}
};
var sql = new StringBuilder();
sql.Append(
"""
SELECT table_catalog, table_schema, table_name, table_type
FROM information_schema.tables
WHERE
table_type IN ('BASE TABLE', 'FOREIGN', 'FOREIGN TABLE') AND
table_schema NOT IN ('pg_catalog', 'information_schema')
""");
return ParseResults(
async,
BuildCommand(conn, sql, restrictions, false, "table_catalog", "table_schema", "table_name", "table_type"),
dataTable,
(reader, row) =>
{
row["table_catalog"] = GetFieldValueOrDBNull(reader, 0);
row["table_schema"] = GetFieldValueOrDBNull(reader, 1);
row["table_name"] = GetFieldValueOrDBNull(reader, 2);
row["table_type"] = GetFieldValueOrDBNull(reader, 3);
}, cancellationToken);
}
static Task GetColumns(NpgsqlConnection conn, string?[]? restrictions, bool async, CancellationToken cancellationToken = default)
{
var dataTable = new DataTable("Columns")
{
Locale = CultureInfo.InvariantCulture,
Columns =
{
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")
}
};
var sql = new StringBuilder(
"""
SELECT
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,
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
""");
return ParseResults(
async,
BuildCommand(conn, sql, restrictions, "table_catalog", "table_schema", "table_name", "column_name"),
dataTable,
(reader, row) =>
{
row["table_catalog"] = GetFieldValueOrDBNull(reader, 0);
row["table_schema"] = GetFieldValueOrDBNull(reader, 1);
row["table_name"] = GetFieldValueOrDBNull(reader, 2);
row["column_name"] = GetFieldValueOrDBNull(reader, 3);
row["ordinal_position"] = GetFieldValueOrDBNull(reader, 4);
row["column_default"] = GetFieldValueOrDBNull(reader, 5);
row["is_nullable"] = GetFieldValueOrDBNull(reader, 6);
row["data_type"] = GetFieldValueOrDBNull(reader, 7);
row["character_maximum_length"] = GetFieldValueOrDBNull(reader, 8);
row["character_octet_length"] = GetFieldValueOrDBNull(reader, 9);
row["numeric_precision"] = GetFieldValueOrDBNull(reader, 10);
row["numeric_precision_radix"] = GetFieldValueOrDBNull(reader, 11);
row["numeric_scale"] = GetFieldValueOrDBNull(reader, 12);
row["datetime_precision"] = GetFieldValueOrDBNull(reader, 13);
row["character_set_catalog"] = GetFieldValueOrDBNull(reader, 14);
row["character_set_schema"] = GetFieldValueOrDBNull(reader, 15);
row["character_set_name"] = GetFieldValueOrDBNull(reader, 16);
row["collation_catalog"] = GetFieldValueOrDBNull(reader, 17);
}, cancellationToken);
}
static Task GetViews(NpgsqlConnection conn, string?[]? restrictions, bool async, CancellationToken cancellationToken = default)
{
var dataTable = new DataTable("Views")
{
Locale = CultureInfo.InvariantCulture,
Columns =
{
new DataColumn("table_catalog"),
new DataColumn("table_schema"),
new DataColumn("table_name"),
new DataColumn("check_option"),
new DataColumn("is_updatable")
}
};
var sql = new StringBuilder(
"""
SELECT table_catalog, table_schema, table_name, check_option, is_updatable
FROM information_schema.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
""");
return ParseResults(
async,
BuildCommand(conn, sql, restrictions, false, "table_catalog", "table_schema", "table_name"),
dataTable,
(reader, row) =>
{
row["table_catalog"] = GetFieldValueOrDBNull(reader, 0);
row["table_schema"] = GetFieldValueOrDBNull(reader, 1);
row["table_name"] = GetFieldValueOrDBNull(reader, 2);
row["check_option"] = GetFieldValueOrDBNull(reader, 3);
row["is_updatable"] = GetFieldValueOrDBNull(reader, 3);
}, cancellationToken);
}
static Task GetMaterializedViews(NpgsqlConnection conn, string?[]? restrictions, bool async, CancellationToken cancellationToken = default)
{
var dataTable = new DataTable("MaterializedViews")
{
Locale = CultureInfo.InvariantCulture,
Columns =
{
new DataColumn("table_catalog"),
new DataColumn("table_schema"),
new DataColumn("table_name"),
new DataColumn("table_owner"),
new DataColumn("has_indexes", typeof(bool)),
new DataColumn("is_populated", typeof(bool))
}
};
var sql = new StringBuilder();
sql.Append("""SELECT current_database(), schemaname, matviewname, matviewowner, hasindexes, ispopulated FROM pg_catalog.pg_matviews""");
return ParseResults(
async,
BuildCommand(conn, sql, restrictions, "current_database()", "schemaname", "matviewname", "matviewowner"),
dataTable,
(reader, row) =>
{
row["table_catalog"] = GetFieldValueOrDBNull(reader, 0);
row["table_schema"] = GetFieldValueOrDBNull(reader, 1);
row["table_name"] = GetFieldValueOrDBNull(reader, 2);
row["table_owner"] = GetFieldValueOrDBNull(reader, 3);
row["has_indexes"] = GetFieldValueOrDBNull(reader, 4);
row["is_populated"] = GetFieldValueOrDBNull(reader, 5);
}, cancellationToken);
}
static Task GetUsers(NpgsqlConnection conn, string?[]? restrictions, bool async, CancellationToken cancellationToken = default)
{
var dataTable = new DataTable("Users")
{
Locale = CultureInfo.InvariantCulture,
Columns =
{
new DataColumn("user_name"),
new DataColumn("user_sysid", typeof(uint))
}
};
var sql = new StringBuilder();
sql.Append("SELECT usename, usesysid FROM pg_catalog.pg_user");
return ParseResults(
async,
BuildCommand(conn, sql, restrictions, "usename"),
dataTable,
(reader, row) =>
{
row["user_name"] = GetFieldValueOrDBNull(reader, 0);
row["user_sysid"] = GetFieldValueOrDBNull(reader, 1);
}, cancellationToken);
}
static Task GetIndexes(NpgsqlConnection conn, string?[]? restrictions, bool async, CancellationToken cancellationToken = default)
{
var dataTable = new DataTable("Indexes")
{
Locale = CultureInfo.InvariantCulture,
Columns =
{
new DataColumn("table_catalog"),
new DataColumn("table_schema"),
new DataColumn("table_name"),
new DataColumn("index_name"),
new DataColumn("type_desc")
}
};
var sql = new StringBuilder(
"""
SELECT current_database(),
n.nspname,
t.relname,
i.relname,
''
FROM
pg_catalog.pg_class i
JOIN pg_catalog.pg_index ix ON ix.indexrelid = i.oid
JOIN pg_catalog.pg_class t ON ix.indrelid = t.oid
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = i.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = i.relnamespace
WHERE
i.relkind = 'i' AND
n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
t.relkind = 'r'
""");
return ParseResults(
async,
BuildCommand(conn, sql, restrictions, false, "current_database()", "n.nspname", "t.relname", "i.relname"),
dataTable,
(reader, row) =>
{
row["table_catalog"] = GetFieldValueOrDBNull(reader, 0);
row["table_schema"] = GetFieldValueOrDBNull(reader, 1);
row["table_name"] = GetFieldValueOrDBNull(reader, 2);
row["index_name"] = GetFieldValueOrDBNull(reader, 3);
row["type_desc"] = GetFieldValueOrDBNull(reader, 4);
}, cancellationToken);
}
static Task GetIndexColumns(NpgsqlConnection conn, string?[]? restrictions, bool async, CancellationToken cancellationToken = default)
{
var dataTable = new DataTable("IndexColumns")
{
Locale = CultureInfo.InvariantCulture,
Columns =
{
new DataColumn("constraint_catalog"),
new DataColumn("constraint_schema"),
new DataColumn("constraint_name"),
new DataColumn("table_catalog"),
new DataColumn("table_schema"),
new DataColumn("table_name"),
new DataColumn("column_name"),
new DataColumn("index_name")
}
};
var sql = new StringBuilder(
"""
SELECT
current_database(),
t_ns.nspname,
ix_cls.relname,
current_database(),
ix_ns.nspname,
t.relname,
a.attname,
ix_cls.relname
FROM
pg_class t
JOIN pg_index ix ON t.oid = ix.indrelid
JOIN pg_class ix_cls ON ix.indexrelid = ix_cls.oid
JOIN pg_attribute a ON t.oid = a.attrelid
LEFT JOIN pg_namespace t_ns ON t.relnamespace = t_ns.oid
LEFT JOIN pg_namespace ix_ns ON ix_cls.relnamespace = ix_ns.oid
WHERE
ix_cls.relkind = 'i' AND
t_ns.nspname NOT IN ('pg_catalog', 'pg_toast') AND
a.attnum = ANY(ix.indkey) AND
t.relkind = 'r'
""");
return ParseResults(
async,
BuildCommand(conn, sql, restrictions, false, "current_database()", "t_ns.nspname", "t.relname", "ix_cls.relname", "a.attname"),
dataTable,
(reader, row) =>
{
row["constraint_catalog"] = GetFieldValueOrDBNull(reader, 0);
row["constraint_schema"] = GetFieldValueOrDBNull(reader, 1);
row["constraint_name"] = GetFieldValueOrDBNull(reader, 2);
row["table_catalog"] = GetFieldValueOrDBNull(reader, 3);
row["table_schema"] = GetFieldValueOrDBNull(reader, 4);
row["table_name"] = GetFieldValueOrDBNull(reader, 5);
row["column_name"] = GetFieldValueOrDBNull(reader, 6);
row["index_name"] = GetFieldValueOrDBNull(reader, 7);
}, cancellationToken);
}
static Task GetConstraints(NpgsqlConnection conn, string?[]? restrictions, string? constraintType, bool async, CancellationToken cancellationToken = default)
{
var sql = new StringBuilder(
"""
SELECT
current_database(),
pgn.nspname,
pgc.conname,
current_database(),
pgtn.nspname,
pgt.relname,
constraint_type,
pgc.condeferrable,
pgc.condeferred
FROM
pg_catalog.pg_constraint pgc
JOIN pg_catalog.pg_namespace pgn ON pgc.connamespace = pgn.oid
JOIN pg_catalog.pg_class pgt ON pgc.conrelid = pgt.oid
JOIN pg_catalog.pg_namespace pgtn ON pgt.relnamespace = pgtn.oid
JOIN (
SELECT 'PRIMARY KEY' AS constraint_type, 'p' AS contype
UNION ALL
SELECT 'FOREIGN KEY' AS constraint_type, 'f' AS contype
UNION ALL
SELECT 'UNIQUE KEY' AS constraint_type, 'u' AS contype
) mapping_table ON mapping_table.contype = pgc.contype
""");
switch (constraintType)
{
case "ForeignKeys":
sql.Append(" and pgc.contype='f'");
break;
case "PrimaryKey":
sql.Append(" and pgc.contype='p'");
break;
case "UniqueKeys":
sql.Append(" and pgc.contype='u'");
break;
default:
constraintType = "Constraints";
break;
}
var dataTable = new DataTable(constraintType)
{
Locale = CultureInfo.InvariantCulture,
Columns =
{
new DataColumn("CONSTRAINT_CATALOG"),
new DataColumn("CONSTRAINT_SCHEMA"),
new DataColumn("CONSTRAINT_NAME"),
new DataColumn("TABLE_CATALOG"),
new DataColumn("TABLE_SCHEMA"),
new DataColumn("TABLE_NAME"),
new DataColumn("CONSTRAINT_TYPE"),
new DataColumn("IS_DEFERRABLE", typeof(bool)),
new DataColumn("INITIALLY_DEFERRED", typeof(bool))
}
};
return ParseResults(
async,
BuildCommand(conn, sql, restrictions, false, "current_database()", "pgtn.nspname", "pgt.relname", "pgc.conname"),
dataTable,
(reader, row) =>
{
row["CONSTRAINT_CATALOG"] = GetFieldValueOrDBNull(reader, 0);
row["CONSTRAINT_SCHEMA"] = GetFieldValueOrDBNull(reader, 1);
row["CONSTRAINT_NAME"] = GetFieldValueOrDBNull(reader, 2);
row["TABLE_CATALOG"] = GetFieldValueOrDBNull(reader, 3);
row["TABLE_SCHEMA"] = GetFieldValueOrDBNull(reader, 4);
row["TABLE_NAME"] = GetFieldValueOrDBNull(reader, 5);
row["CONSTRAINT_TYPE"] = GetFieldValueOrDBNull(reader, 6);
row["IS_DEFERRABLE"] = GetFieldValueOrDBNull(reader, 7);
row["INITIALLY_DEFERRED"] = GetFieldValueOrDBNull(reader, 8);
}, cancellationToken);
}
static Task GetConstraintColumns(NpgsqlConnection conn, string?[]? restrictions, bool async, CancellationToken cancellationToken = default)
{
var sql = new StringBuilder(
"""
SELECT current_database(),
n.nspname,
c.conname,
current_database(),
n.nspname,
t.relname,
a.attname,
a.attnum,
mapping_table.constraint_type
FROM pg_constraint c
JOIN pg_namespace n on n.oid = c.connamespace
JOIN pg_class t on t.oid = c.conrelid AND t.relkind = 'r'
JOIN pg_attribute a on t.oid = a.attrelid AND a.attnum = ANY(c.conkey)
JOIN (
SELECT 'PRIMARY KEY' AS constraint_type, 'p' AS contype
UNION ALL
SELECT 'FOREIGN KEY' AS constraint_type, 'f' AS contype
UNION ALL
SELECT 'UNIQUE KEY' AS constraint_type, 'u' AS contype
) mapping_table ON
mapping_table.contype = c.contype
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
""");
var dataTable = new DataTable("ConstraintColumns")
{
Locale = CultureInfo.InvariantCulture,
Columns =
{
new DataColumn("constraint_catalog"),
new DataColumn("constraint_schema"),
new DataColumn("constraint_name"),
new DataColumn("table_catalog"),
new DataColumn("table_schema"),
new DataColumn("table_name"),
new DataColumn("column_name"),
new DataColumn("ordinal_number", typeof(int)),
new DataColumn("constraint_type")
}
};
return ParseResults(
async,
BuildCommand(conn, sql, restrictions, false, "current_database()", "n.nspname", "t.relname", "c.conname", "a.attname"),
dataTable,
(reader, row) =>
{
row["constraint_catalog"] = GetFieldValueOrDBNull(reader, 0);
row["constraint_schema"] = GetFieldValueOrDBNull(reader, 1);
row["constraint_name"] = GetFieldValueOrDBNull(reader, 2);
row["table_catalog"] = GetFieldValueOrDBNull(reader, 3);
row["table_schema"] = GetFieldValueOrDBNull(reader, 4);
row["table_name"] = GetFieldValueOrDBNull(reader, 5);
row["column_name"] = GetFieldValueOrDBNull(reader, 6);
row["ordinal_number"] = GetFieldValueOrDBNull(reader, 7);
row["constraint_type"] = GetFieldValueOrDBNull(reader, 8);
}, cancellationToken);
}
static DataTable GetDataSourceInformation(NpgsqlConnection conn)
{
var table = new DataTable("DataSourceInformation");
var row = table.Rows.Add();
table.Columns.Add("CompositeIdentifierSeparatorPattern", typeof(string));
// TODO: DefaultCatalog? Was in XML (unfilled) but isn't in docs
table.Columns.Add("DataSourceProductName", typeof(string));
table.Columns.Add("DataSourceProductVersion", typeof(string));
table.Columns.Add("DataSourceProductVersionNormalized", typeof(string));
table.Columns.Add("GroupByBehavior", typeof(GroupByBehavior));
table.Columns.Add("IdentifierPattern", typeof(string));
table.Columns.Add("IdentifierCase", typeof(IdentifierCase));
table.Columns.Add("OrderByColumnsInSelect", typeof(bool));
table.Columns.Add("ParameterMarkerFormat", typeof(string));
table.Columns.Add("ParameterMarkerPattern", typeof(string));
table.Columns.Add("ParameterNameMaxLength", typeof(int));
table.Columns.Add("QuotedIdentifierPattern", typeof(string));
table.Columns.Add("QuotedIdentifierCase", typeof(IdentifierCase));
table.Columns.Add("ParameterNamePattern", typeof(string));
table.Columns.Add("StatementSeparatorPattern", typeof(string));
table.Columns.Add("StringLiteralPattern", typeof(string));
table.Columns.Add("SupportedJoinOperators", typeof(SupportedJoinOperators));
var version = conn.PostgreSqlVersion;
var normalizedVersion = $"{version.Major:00}.{version.Minor:00}";
if (version.Build >= 0)
normalizedVersion += $".{version.Build:00}";
row["CompositeIdentifierSeparatorPattern"] = @"\.";
row["DataSourceProductName"] = "Npgsql";
row["DataSourceProductVersion"] = version.ToString();
row["DataSourceProductVersionNormalized"] = normalizedVersion;
row["GroupByBehavior"] = GroupByBehavior.Unrelated;
row["IdentifierPattern"] = @"(^\[\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\[[^\]\0]|\]\]+\]$)|(^\""[^\""\0]|\""\""+\""$)";
row["IdentifierCase"] = IdentifierCase.Insensitive;
row["OrderByColumnsInSelect"] = false;
row["QuotedIdentifierPattern"] = @"""(([^\""]|\""\"")*)""";
row["QuotedIdentifierCase"] = IdentifierCase.Sensitive;
row["StatementSeparatorPattern"] = ";";
row["StringLiteralPattern"] = @"'(([^']|'')*)'";
row["SupportedJoinOperators"] =
SupportedJoinOperators.FullOuter |
SupportedJoinOperators.Inner |
SupportedJoinOperators.LeftOuter |
SupportedJoinOperators.RightOuter;
row["ParameterNameMaxLength"] = 63; // For function out parameters
row["ParameterMarkerFormat"] = @"{0}"; // TODO: Not sure
if (NpgsqlCommand.EnableSqlRewriting)
{
row["ParameterMarkerPattern"] = @"@[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)";
row["ParameterNamePattern"] = @"^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)";
}
else
{
row["ParameterMarkerPattern"] = @"$\d+";
row["ParameterNamePattern"] = @"\d+";
}
return table;
}
#region DataTypes
static DataTable GetDataTypes(NpgsqlConnection conn)
{
using var _ = conn.StartTemporaryBindingScope(out var connector);
var table = new DataTable("DataTypes");
table.Columns.Add("TypeName", typeof(string));
table.Columns.Add("ColumnSize", typeof(long));
table.Columns.Add("CreateFormat", typeof(string));
table.Columns.Add("CreateParameters", typeof(string));
table.Columns.Add("DataType", typeof(string));
table.Columns.Add("IsAutoIncrementable", typeof(bool));
table.Columns.Add("IsBestMatch", typeof(bool));
table.Columns.Add("IsCaseSensitive", typeof(bool));
table.Columns.Add("IsConcurrencyType", typeof(bool));
table.Columns.Add("IsFixedLength", typeof(bool));
table.Columns.Add("IsFixedPrecisionAndScale", typeof(bool));
table.Columns.Add("IsLiteralSupported", typeof(bool));
table.Columns.Add("IsLong", typeof(bool));
table.Columns.Add("IsNullable", typeof(bool));
table.Columns.Add("IsSearchable", typeof(bool));
table.Columns.Add("IsSearchableWithLike", typeof(bool));
table.Columns.Add("IsUnsigned", typeof(bool));
table.Columns.Add("LiteralPrefix", typeof(string));
table.Columns.Add("LiteralSuffix", typeof(string));
table.Columns.Add("MaximumScale", typeof(short));
table.Columns.Add("MinimumScale", typeof(short));
table.Columns.Add("NativeDataType", typeof(string));
table.Columns.Add("ProviderDbType", typeof(int));
// Npgsql-specific
table.Columns.Add("OID", typeof(uint));
// TODO: Support type name restriction
try
{
PgSerializerOptions.IntrospectionCaller = true;
var types = new List();
types.AddRange(connector.DatabaseInfo.BaseTypes);
types.AddRange(connector.DatabaseInfo.EnumTypes);
types.AddRange(connector.DatabaseInfo.CompositeTypes);
foreach (var baseType in types)
{
if (connector.SerializerOptions.GetDefaultTypeInfo(baseType) is not { } info)
continue;
var row = table.Rows.Add();
PopulateDefaultDataTypeInfo(row, baseType);
PopulateHardcodedDataTypeInfo(row, baseType);
row["DataType"] = info.Type.FullName;
if (baseType.DataTypeName.ToNpgsqlDbType() is { } npgsqlDbType)
row["ProviderDbType"] = (int)npgsqlDbType;
}
foreach (var arrayType in connector.DatabaseInfo.ArrayTypes)
{
if (connector.SerializerOptions.GetDefaultTypeInfo(arrayType) is not { } info)
continue;
var row = table.Rows.Add();
PopulateDefaultDataTypeInfo(row, arrayType.Element);
// Populate hardcoded values based on the element type (e.g. citext[] is case-insensitive).
PopulateHardcodedDataTypeInfo(row, arrayType.Element);
row["TypeName"] = arrayType.DisplayName;
row["OID"] = arrayType.OID;
row["CreateFormat"] += "[]";
row["DataType"] = info.Type.FullName;
if (arrayType.DataTypeName.ToNpgsqlDbType() is { } npgsqlDbType)
row["ProviderDbType"] = (int)npgsqlDbType;
}
foreach (var rangeType in connector.DatabaseInfo.RangeTypes)
{
if (connector.SerializerOptions.GetDefaultTypeInfo(rangeType) is not { } info)
continue;
var row = table.Rows.Add();
PopulateDefaultDataTypeInfo(row, rangeType.Subtype);
// Populate hardcoded values based on the subtype type (e.g. citext[] is case-insensitive).
PopulateHardcodedDataTypeInfo(row, rangeType.Subtype);
row["TypeName"] = rangeType.DisplayName;
row["OID"] = rangeType.OID;
row["CreateFormat"] = rangeType.DisplayName.ToUpperInvariant();
row["DataType"] = info.Type.FullName;
if (rangeType.DataTypeName.ToNpgsqlDbType() is { } npgsqlDbType)
row["ProviderDbType"] = (int)npgsqlDbType;
}
foreach (var multirangeType in connector.DatabaseInfo.MultirangeTypes)
{
var subtypeType = multirangeType.Subrange.Subtype;
if (connector.SerializerOptions.GetDefaultTypeInfo(multirangeType) is not { } info)
continue;
var row = table.Rows.Add();
PopulateDefaultDataTypeInfo(row, subtypeType);
// Populate hardcoded values based on the subtype type (e.g. citext[] is case-insensitive).
PopulateHardcodedDataTypeInfo(row, subtypeType);
row["TypeName"] = multirangeType.DisplayName;
row["OID"] = multirangeType.OID;
row["CreateFormat"] = multirangeType.DisplayName.ToUpperInvariant();
row["DataType"] = info.Type.FullName;
if (multirangeType.DataTypeName.ToNpgsqlDbType() is { } npgsqlDbType)
row["ProviderDbType"] = (int)npgsqlDbType;
}
foreach (var domainType in connector.DatabaseInfo.DomainTypes)
{
var representationalType = domainType.GetRepresentationalType();
if (connector.SerializerOptions.GetDefaultTypeInfo(representationalType) is not { } info)
continue;
var row = table.Rows.Add();
PopulateDefaultDataTypeInfo(row, representationalType);
// Populate hardcoded values based on the element type (e.g. citext[] is case-insensitive).
PopulateHardcodedDataTypeInfo(row, representationalType);
row["TypeName"] = domainType.DisplayName;
row["OID"] = domainType.OID;
// A domain is never the best match, since its underlying base type is
row["IsBestMatch"] = false;
row["DataType"] = info.Type.FullName;
if (representationalType.DataTypeName.ToNpgsqlDbType() is { } npgsqlDbType)
row["ProviderDbType"] = (int)npgsqlDbType;
}
}
finally
{
PgSerializerOptions.IntrospectionCaller = false;
}
return table;
}
///
/// Populates some generic type information that is common for base types, arrays, enums, etc. Some will
/// be overridden later.
///
static void PopulateDefaultDataTypeInfo(DataRow row, PostgresType type)
{
row["TypeName"] = type.DisplayName;
// Skipping ColumnSize at least for now, not very meaningful
row["CreateFormat"] = type.DisplayName.ToUpperInvariant();
row["CreateParameters"] = "";
row["IsAutoIncrementable"] = false;
// We populate the DataType above from mapping.ClrTypes, which means we take the .NET type from
// which we *infer* the PostgreSQL type. Since only a single PostgreSQL type gets inferred from a given
// .NET type, we never have the same DataType in more than one row - so the mapping is always the
// best match. See the hardcoding override below for some exceptions.
row["IsBestMatch"] = true;
row["IsCaseSensitive"] = true;
row["IsConcurrencyType"] = false;
row["IsFixedLength"] = false;
row["IsFixedPrecisionAndScale"] = false;
row["IsLiteralSupported"] = false; // See hardcoding override below
row["IsLong"] = false;
row["IsNullable"] = true;
row["IsSearchable"] = true;
row["IsSearchableWithLike"] = false;
row["IsUnsigned"] = DBNull.Value; // See hardcoding override below
// LiteralPrefix/Suffix: no literal for now except for strings, see hardcoding override below
row["MaximumScale"] = DBNull.Value;
row["MinimumScale"] = DBNull.Value;
// NativeDataType is unset
row["OID"] = type.OID;
}
///
/// Sets some custom, hardcoded info on a DataType row that cannot be loaded/inferred from PostgreSQL
///
static void PopulateHardcodedDataTypeInfo(DataRow row, PostgresType type)
{
switch (type.Name)
{
case "varchar":
case "char":
row["DataType"] = "String";
row["IsBestMatch"] = false;
goto case "text";
case "text":
row["CreateFormat"] += "({0})";
row["CreateParameters"] = "size";
row["IsSearchableWithLike"] = true;
row["IsLiteralSupported"] = true;
row["LiteralPrefix"] = "'";
row["LiteralSuffix"] = "'";
return;
case "numeric":
row["CreateFormat"] += "({0},{1})";
row["CreateParameters"] = "precision, scale";
row["MaximumScale"] = 16383;
row["MinimumScale"] = 16383;
row["IsUnsigned"] = false;
return;
case "bytea":
row["IsLong"] = true;
return;
case "citext":
row["IsCaseSensitive"] = false;
return;
case "integer":
case "smallint":
case "bigint":
case "double precision":
case "real":
case "money":
row["IsUnsigned"] = false;
return;
case "oid":
case "cid":
case "regtype":
case "regconfig":
row["IsUnsigned"] = true;
return;
case "xid":
row["IsUnsigned"] = true;
row["IsConcurrencyType"] = true;
return;
}
}
#endregion DataTypes
#region Reserved Keywords
static DataTable GetReservedWords()
{
var table = new DataTable("ReservedWords") { Locale = CultureInfo.InvariantCulture };
table.Columns.Add("ReservedWord", typeof(string));
foreach (var keyword in ReservedKeywords)
table.Rows.Add(keyword);
return table;
}
///
/// List of keywords taken from PostgreSQL 9.0 reserved words documentation.
///
static readonly string[] ReservedKeywords =
{
"ALL",
"ANALYSE",
"ANALYZE",
"AND",
"ANY",
"ARRAY",
"AS",
"ASC",
"ASYMMETRIC",
"AUTHORIZATION",
"BINARY",
"BOTH",
"CASE",
"CAST",
"CHECK",
"COLLATE",
"COLUMN",
"CONCURRENTLY",
"CONSTRAINT",
"CREATE",
"CROSS",
"CURRENT_CATALOG",
"CURRENT_DATE",
"CURRENT_ROLE",
"CURRENT_SCHEMA",
"CURRENT_TIME",
"CURRENT_TIMESTAMP",
"CURRENT_USER",
"DEFAULT",
"DEFERRABLE",
"DESC",
"DISTINCT",
"DO",
"ELSE",
"END",
"EXCEPT",
"FALSE",
"FETCH",
"FOR",
"FOREIGN",
"FREEZE",
"FROM",
"FULL",
"GRANT",
"GROUP",
"HAVING",
"ILIKE",
"IN",
"INITIALLY",
"INNER",
"INTERSECT",
"INTO",
"IS",
"ISNULL",
"JOIN",
"LATERAL",
"LEADING",
"LEFT",
"LIKE",
"LIMIT",
"LOCALTIME",
"LOCALTIMESTAMP",
"NATURAL",
"NOT",
"NOTNULL",
"NULL",
"OFFSET",
"ON",
"ONLY",
"OR",
"ORDER",
"OUTER",
"OVER",
"OVERLAPS",
"PLACING",
"PRIMARY",
"REFERENCES",
"RETURNING",
"RIGHT",
"SELECT",
"SESSION_USER",
"SIMILAR",
"SOME",
"SYMMETRIC",
"TABLE",
"THEN",
"TO",
"TRAILING",
"TRUE",
"UNION",
"UNIQUE",
"USER",
"USING",
"VARIADIC",
"VERBOSE",
"WHEN",
"WHERE",
"WINDOW",
"WITH"
};
#endregion Reserved Keywords
static async Task ParseResults(bool async, NpgsqlCommand command, DataTable dataTable, Action populateRow, CancellationToken cancellationToken)
{
NpgsqlDataReader? reader = null;
try
{
reader = async
? await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false)
: command.ExecuteReader();
dataTable.BeginLoadData();
while (async ? await reader.ReadAsync(cancellationToken).ConfigureAwait(false) : reader.Read())
populateRow(reader, dataTable.Rows.Add());
return dataTable;
}
finally
{
dataTable.EndLoadData();
if (async)
{
if (reader is not null)
await reader.DisposeAsync().ConfigureAwait(false);
await command.DisposeAsync().ConfigureAwait(false);
}
else
{
reader?.Dispose();
command.Dispose();
}
}
}
static object GetFieldValueOrDBNull(NpgsqlDataReader reader, int ordinal)
=> reader.IsDBNull(ordinal) ? DBNull.Value : reader.GetFieldValue(ordinal)!;
}