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))
{
builder
.Append("CREATE EXTENSION IF NOT EXISTS ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(extension.Name));
if (extension.Schema != null)
{
builder
.Append(" SCHEMA ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(extension.Schema));
}
if (extension.Version != null)
{
builder
.Append(" VERSION ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(extension.Version));
}
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder, suppressTransaction: true);
}
}
protected virtual void GenerateCreateExtension(
PostgresExtension extension,
IModel model,
MigrationCommandListBuilder builder)
{
builder
.Append("CREATE EXTENSION IF NOT EXISTS ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(extension.Name));
if (extension.Schema != null)
{
builder
.Append(" SCHEMA ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(extension.Schema));
}
if (extension.Version != null)
{
builder
.Append(" VERSION ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(extension.Version));
}
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);
}
void GetExtensions(NpgsqlConnection connection, DatabaseModel databaseModel)
{
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT name,default_version,installed_version FROM pg_available_extensions";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var name = reader.GetString(reader.GetOrdinal("name"));
var defaultVersion = reader.GetValueOrDefault <string>("default_version");
var installedVersion = reader.GetValueOrDefault <string>("installed_version");
if (installedVersion == null)
{
continue;
}
if (name == "plpgsql") // Implicitly installed in all PG databases
{
continue;
}
PostgresExtension.GetOrAddPostgresExtension(databaseModel, name);
}
}
}
}
public override async Task CreateTablesAsync(CancellationToken cancellationToken = default)
{
var operations = Dependencies.ModelDiffer.GetDifferences(null, Dependencies.Model);
var commands = Dependencies.MigrationsSqlGenerator.Generate(operations, Dependencies.Model);
// Adding a PostgreSQL extension might define new types (e.g. hstore), which we
// Npgsql to reload
var reloadTypes = operations.Any(o => o is AlterDatabaseOperation && PostgresExtension.GetPostgresExtensions(o).Any());
try
{
await Dependencies.MigrationCommandExecutor.ExecuteNonQueryAsync(commands, _connection,
cancellationToken);
}
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.
}
// TODO: Not async
if (reloadTypes)
{
var npgsqlConn = (NpgsqlConnection)_connection.DbConnection;
if (npgsqlConn.FullState == ConnectionState.Open)
{
npgsqlConn.ReloadTypes();
}
}
}
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 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();
}
/// <summary>
/// Returns a value indicating whether the given PostgreSQL extension can be registered in the model.
/// </summary>
/// <remarks>
/// See <see href="https://aka.ms/efcore-docs-modeling">Modeling entity types and relationships</see>, and
/// <see href="https://aka.ms/efcore-docs-sqlserver">Accessing SQL Server and SQL Azure databases with EF Core</see>
/// for more information and examples.
/// </remarks>
/// <param name="modelBuilder">The model builder.</param>
/// <param name="schema">The schema in which to create the extension.</param>
/// <param name="name">The name of the extension to create.</param>
/// <param name="version">The version of the extension.</param>
/// <param name="fromDataAnnotation">Indicates whether the configuration was specified using a data annotation.</param>
/// <returns><see langword="true" /> if the given value can be set as the default increment for SQL Server IDENTITY.</returns>
public static bool CanSetPostgresExtension(
this IConventionModelBuilder modelBuilder,
string?schema,
string name,
string?version = null,
bool fromDataAnnotation = false)
{
var annotationName = PostgresExtension.BuildAnnotationName(schema, name);
return(modelBuilder.CanSetAnnotation(annotationName, $"{schema},{name},{version}", fromDataAnnotation));
}
public void EnsurePostgresExtension()
{
var op = new AlterDatabaseOperation();
PostgresExtension.GetOrAddPostgresExtension(op, "hstore");
Generate(op);
Assert.Equal(
@"CREATE EXTENSION IF NOT EXISTS hstore;" + EOL,
Sql);
}
public void EnsurePostgresExtension_with_schema()
{
var op = new AlterDatabaseOperation();
var extension = PostgresExtension.GetOrAddPostgresExtension(op, "hstore");
extension.Schema = "myschema";
Generate(op);
Assert.Equal(
@"CREATE EXTENSION IF NOT EXISTS hstore SCHEMA myschema;" + 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);
}
public override string GenerateFluentApi(IModel model, IAnnotation annotation, string language)
{
Check.NotNull(model, nameof(model));
Check.NotNull(annotation, nameof(annotation));
Check.NotNull(language, nameof(language));
if (language != "CSharp")
{
return(null);
}
if (annotation.Name.StartsWith(NpgsqlAnnotationNames.PostgresExtensionPrefix))
{
var extension = new PostgresExtension(model, annotation.Name);
return($".{nameof(NpgsqlModelBuilderExtensions.HasPostgresExtension)}(\"{extension.Name}\")");
}
return(null);
}
public static MigrationBuilder EnsurePostgresExtension(
this MigrationBuilder builder,
[NotNull] string name,
string schema = null,
string version = null
)
{
Check.NotEmpty(name, nameof(name));
Check.NullButNotEmpty(schema, nameof(schema));
Check.NullButNotEmpty(version, nameof(schema));
var op = new AlterDatabaseOperation();
var extension = PostgresExtension.GetOrAddPostgresExtension(op, name);
extension.Schema = schema;
extension.Version = version;
builder.Operations.Add(op);
return(builder);
}
public override void CreateTables()
{
var operations = _modelDiffer.GetDifferences(null, Model);
var commands = _migrationsSqlGenerator.Generate(operations, Model);
// Adding a PostgreSQL extension might define new types (e.g. hstore), which we
// Npgsql to reload
var reloadTypes = operations.Any(o => o is AlterDatabaseOperation && PostgresExtension.GetPostgresExtensions(o).Any());
MigrationCommandExecutor.ExecuteNonQuery(commands, Connection);
if (reloadTypes)
{
var npgsqlConn = (NpgsqlConnection)Connection.DbConnection;
if (npgsqlConn.FullState == ConnectionState.Open)
{
npgsqlConn.ReloadTypes();
}
}
}
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 <PostgresExtension> GetPostgresExtensions([NotNull] this DatabaseModel model) => PostgresExtension.GetPostgresExtensions(model).ToArray();
public static IReadOnlyList <PostgresExtension> GetPostgresExtensions(this IReadOnlyModel model) => PostgresExtension.GetPostgresExtensions(model).ToArray();
public static PostgresExtension GetOrAddPostgresExtension(
this IMutableModel model,
string?schema,
string name,
string?version)
=> PostgresExtension.GetOrAddPostgresExtension(model, schema, name, version);
public static IReadOnlyList <PostgresExtension> GetPostgresExtensions(this AlterDatabaseOperation operation) => PostgresExtension.GetPostgresExtensions(operation).ToArray();
public static PostgresExtension GetOrAddPostgresExtension(
[NotNull] this AlterDatabaseOperation operation,
[CanBeNull] string schema,
[NotNull] string name,
[CanBeNull] string version)
=> PostgresExtension.GetOrAddPostgresExtension(operation, schema, name, version);
public static IReadOnlyList <PostgresExtension> GetOldPostgresExtensions([NotNull] this AlterDatabaseOperation operation) => PostgresExtension.GetPostgresExtensions(operation.OldDatabase).ToArray();
public static PostgresExtension GetOrAddPostgresExtension(
this AlterDatabaseOperation operation,
string?schema,
string name,
string?version)
=> PostgresExtension.GetOrAddPostgresExtension(operation, schema, name, version);
public static PostgresExtension GetOrAddPostgresExtension(
[NotNull] this DatabaseModel model,
[CanBeNull] string schema,
[NotNull] string name,
[CanBeNull] string version)
=> PostgresExtension.GetOrAddPostgresExtension(model, schema, name, version);
