void GetEnums(NpgsqlConnection connection, DatabaseModel databaseModel)
{
_enums.Clear();
using (var command = connection.CreateCommand())
{
command.CommandText = @"
SELECT nspname, typname, array_agg(enumlabel ORDER BY enumsortorder) AS labels
FROM pg_enum
JOIN pg_type ON pg_type.oid=enumtypid
JOIN pg_namespace ON pg_namespace.oid=pg_type.typnamespace
GROUP BY nspname, typname";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var schema = reader.GetValueOrDefault <string>("nspname");
var name = reader.GetValueOrDefault <string>("typname");
var labels = reader.GetValueOrDefault <string[]>("labels");
if (schema == "public")
{
schema = null;
}
PostgresEnum.GetOrAddPostgresEnum(databaseModel, schema, name, labels);
_enums.Add(name);
}
}
}
}
protected virtual void GenerateDropEnum(PostgresEnum @enum, MigrationCommandListBuilder builder)
{
builder
.Append("DROP TYPE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(@enum.Name, @enum.Schema))
.AppendLine(";");
}
public override void CreateTables()
{
var operations = Dependencies.ModelDiffer.GetDifferences(null, Dependencies.Model);
var commands = Dependencies.MigrationsSqlGenerator.Generate(operations, Dependencies.Model);
// If a PostgreSQL extension or enum was added, we want Npgsql to reload all types at the ADO.NET level.
var reloadTypes = operations.Any(o => o is AlterDatabaseOperation &&
(PostgresExtension.GetPostgresExtensions(o).Any() ||
PostgresEnum.GetPostgresEnums(o).Any()));
try
{
Dependencies.MigrationCommandExecutor.ExecuteNonQuery(commands, _connection);
}
catch (PostgresException e) when(
e.SqlState == "23505" && e.ConstraintName == "pg_type_typname_nsp_index"
)
{
// This occurs when two connections are trying to create the same database concurrently
// (happens in the tests). Simply ignore the error.
}
if (reloadTypes)
{
_connection.Open();
try
{
((NpgsqlConnection)_connection.DbConnection).ReloadTypes();
}
catch
{
_connection.Close();
}
}
}
protected virtual void GenerateCreateEnum(
PostgresEnum enumType,
IModel model,
MigrationCommandListBuilder builder)
{
// Schemas are normally created (or rather ensured) by the model differ, which scans all tables, sequences
// and other database objects. However, it isn't aware of enums, so we always ensure schema on enum creation.
if (enumType.Schema != null)
{
Generate(new EnsureSchemaOperation {
Name = enumType.Schema
}, model, builder);
}
builder
.Append("CREATE TYPE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(enumType.Name, enumType.Schema))
.Append(" AS ENUM (");
var stringTypeMapping = Dependencies.TypeMappingSource.GetMapping(typeof(string));
var labels = enumType.Labels;
for (var i = 0; i < labels.Length; i++)
{
builder.Append(stringTypeMapping.GenerateSqlLiteral(labels[i]));
if (i < labels.Length - 1)
{
builder.Append(", ");
}
}
builder.AppendLine(");");
}
public override MethodCallCodeFragment GenerateFluentApi(IModel model, IAnnotation annotation)
{
Check.NotNull(model, nameof(model));
Check.NotNull(annotation, nameof(annotation));
if (annotation.Name.StartsWith(NpgsqlAnnotationNames.PostgresExtensionPrefix))
{
var extension = new PostgresExtension(model, annotation.Name);
return(new MethodCallCodeFragment(nameof(NpgsqlModelBuilderExtensions.HasPostgresExtension),
extension.Name));
}
if (annotation.Name.StartsWith(NpgsqlAnnotationNames.EnumPrefix))
{
var enumTypeDef = new PostgresEnum(model, annotation.Name);
return(enumTypeDef.Schema == "public"
? new MethodCallCodeFragment(nameof(NpgsqlModelBuilderExtensions.ForNpgsqlHasEnum),
enumTypeDef.Name, enumTypeDef.Labels)
: new MethodCallCodeFragment(nameof(NpgsqlModelBuilderExtensions.ForNpgsqlHasEnum),
enumTypeDef.Schema, enumTypeDef.Name, enumTypeDef.Labels));
}
return(null);
}
protected override void Generate(
AlterDatabaseOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
foreach (var extension in PostgresExtension.GetPostgresExtensions(operation))
{
GenerateCreateExtension(extension, model, builder);
}
foreach (var enumTypeToCreate in PostgresEnum.GetPostgresEnums(operation)
.Where(ne => PostgresEnum.GetPostgresEnums(operation.OldDatabase).All(oe => oe.Name != ne.Name)))
{
GenerateCreateEnum(enumTypeToCreate, model, builder);
}
foreach (var enumTypeToDrop in PostgresEnum.GetPostgresEnums(operation.OldDatabase)
.Where(oe => PostgresEnum.GetPostgresEnums(operation).All(ne => ne.Name != oe.Name)))
{
GenerateDropEnum(enumTypeToDrop, builder);
}
foreach (var enumTypeToAlter in from newEnum in PostgresEnum.GetPostgresEnums(operation)
join oldEnum in PostgresEnum.GetPostgresEnums(operation.OldDatabase) on newEnum.Name equals oldEnum.Name
select new { newEnum.Name, OldLabels = oldEnum.Labels, newLabels = newEnum.Labels })
{
// TODO: Some forms of enum alterations are actually supported... At least log...
}
builder.EndCommand();
}
public void DropPostgresEnum()
{
var op = new AlterDatabaseOperation();
PostgresEnum.GetOrAddPostgresEnum(op.OldDatabase, "public", "my_enum", new[] { "value1", "value2" });
Generate(op);
Assert.Equal(@"DROP TYPE public.my_enum;" + EOL, Sql);
}
public void CreatePostgresEnum()
{
var op = new AlterDatabaseOperation();
PostgresEnum.GetOrAddPostgresEnum(op, "public", "my_enum", new[] { "value1", "value2" });
Generate(op);
Assert.Equal(@"CREATE TYPE public.my_enum AS ENUM ('value1', 'value2');" + EOL, Sql);
}
protected virtual void GenerateDropEnum(
[NotNull] PostgresEnum enumType,
[NotNull] IModel model,
[NotNull] MigrationCommandListBuilder builder)
{
var schema = enumType.Schema ?? model.Relational().DefaultSchema;
builder
.Append("DROP TYPE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(enumType.Name, schema))
.AppendLine(";");
}
public void CreatePostgresEnumWithSchema()
{
var op = new AlterDatabaseOperation();
PostgresEnum.GetOrAddPostgresEnum(op, "some_schema", "my_enum", new[] { "value1", "value2" });
Generate(op);
Assert.Equal(
@"CREATE SCHEMA IF NOT EXISTS some_schema;" + EOL +
@"GO" + EOL + EOL +
@"CREATE TYPE some_schema.my_enum AS ENUM ('value1', 'value2');" + EOL,
Sql);
}
public override MethodCallCodeFragment GenerateFluentApi(IModel model, IAnnotation annotation)
{
Check.NotNull(model, nameof(model));
Check.NotNull(annotation, nameof(annotation));
if (annotation.Name.StartsWith(NpgsqlAnnotationNames.PostgresExtensionPrefix, StringComparison.Ordinal))
{
var extension = new PostgresExtension(model, annotation.Name);
return(new MethodCallCodeFragment(nameof(NpgsqlModelBuilderExtensions.HasPostgresExtension),
extension.Name));
}
if (annotation.Name.StartsWith(NpgsqlAnnotationNames.EnumPrefix, StringComparison.Ordinal))
{
var enumTypeDef = new PostgresEnum(model, annotation.Name);
return(enumTypeDef.Schema == "public"
? new MethodCallCodeFragment(nameof(NpgsqlModelBuilderExtensions.ForNpgsqlHasEnum),
enumTypeDef.Name, enumTypeDef.Labels)
: new MethodCallCodeFragment(nameof(NpgsqlModelBuilderExtensions.ForNpgsqlHasEnum),
enumTypeDef.Schema, enumTypeDef.Name, enumTypeDef.Labels));
}
if (annotation.Name.StartsWith(NpgsqlAnnotationNames.RangePrefix, StringComparison.Ordinal))
{
var rangeTypeDef = new PostgresRange(model, annotation.Name);
if (rangeTypeDef.CanonicalFunction == null &&
rangeTypeDef.SubtypeOpClass == null &&
rangeTypeDef.Collation == null &&
rangeTypeDef.SubtypeDiff == null)
{
return(new MethodCallCodeFragment(nameof(NpgsqlModelBuilderExtensions.ForNpgsqlHasRange),
rangeTypeDef.Schema == "public" ? null : rangeTypeDef.Schema,
rangeTypeDef.Name,
rangeTypeDef.Subtype));
}
return(new MethodCallCodeFragment(nameof(NpgsqlModelBuilderExtensions.ForNpgsqlHasRange),
rangeTypeDef.Schema == "public" ? null : rangeTypeDef.Schema,
rangeTypeDef.Name,
rangeTypeDef.Subtype,
rangeTypeDef.CanonicalFunction,
rangeTypeDef.SubtypeOpClass,
rangeTypeDef.Collation,
rangeTypeDef.SubtypeDiff));
}
return(null);
}
protected override void Generate(
AlterDatabaseOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
foreach (var extension in PostgresExtension.GetPostgresExtensions(operation))
{
GenerateCreateExtension(extension, model, builder);
}
foreach (var enumType in PostgresEnum.GetPostgresEnums(operation))
{
GenerateCreateEnum(enumType, model, builder);
}
// TODO: Some forms of enum alterations are actually supported...
foreach (var enumType in PostgresEnum.GetPostgresEnums(operation.OldDatabase))
{
GenerateDropEnum(enumType, builder);
}
builder.EndCommand();
}
public static IReadOnlyList <PostgresEnum> GetPostgresEnums(this IReadOnlyModel model) => PostgresEnum.GetPostgresEnums(model).ToArray();
public static PostgresEnum GetOrAddPostgresEnum(
this IMutableModel model,
string?schema,
string name,
string[] labels)
=> PostgresEnum.GetOrAddPostgresEnum(model, schema, name, labels);
public static IReadOnlyList <PostgresEnum> GetOldPostgresEnums([NotNull] this AlterDatabaseOperation operation) => PostgresEnum.GetPostgresEnums(operation.OldDatabase).ToArray();
public static PostgresEnum GetOrAddPostgresEnum(
[NotNull] this IMutableModel model,
[CanBeNull] string schema,
[NotNull] string name,
[NotNull] string[] labels)
=> PostgresEnum.GetOrAddPostgresEnum(model, schema, name, labels);
public static IReadOnlyList <PostgresEnum> GetPostgresEnums([NotNull] this DatabaseModel model) => PostgresEnum.GetPostgresEnums(model).ToArray();
public static IReadOnlyList <PostgresEnum> GetPostgresEnums(this AlterDatabaseOperation operation) => PostgresEnum.GetPostgresEnums(operation).ToArray();
