protected override void Generate(NpgsqlCreateDatabaseOperation operation, IModel model, MigrationCommandListBuilder builder)
{
builder
.Append("CREATE DATABASE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name));
// POSTGRES gotcha: Indexed Text column (even if PK) are not used if we are not using C locale
builder
.Append(" TEMPLATE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier("template0"));
builder
.Append(" LC_CTYPE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier("C"));
builder
.Append(" LC_COLLATE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier("C"));
builder
.Append(" ENCODING ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier("UTF8"));
if (operation.Tablespace != null)
{
builder
.Append(" TABLESPACE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Tablespace));
}
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder, suppressTransaction: true);
}
protected override void ColumnDefinition(
string schema,
string table,
string name,
Type clrType,
string type,
bool?unicode,
int?maxLength,
bool?fixedLength,
bool rowVersion,
bool nullable,
object defaultValue,
string defaultValueSql,
string computedColumnSql,
IAnnotatable annotatable,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotEmpty(name, nameof(name));
Check.NotNull(annotatable, nameof(annotatable));
Check.NotNull(clrType, nameof(clrType));
Check.NotNull(builder, nameof(builder));
if (type == null)
{
type = GetColumnType(schema, table, name, clrType, unicode, maxLength, fixedLength, rowVersion, model);
}
// User-defined type names are quoted if they contain uppercase letters. Other types are never quoted
// since users sometimes prefer to write TEXT instead of text.
if (_typeMappingSource.IsUserDefinedType(type))
{
type = _sqlGenerationHelper.DelimitIdentifier(type);
}
CheckForOldValueGenerationAnnotation(annotatable);
var valueGenerationStrategy = annotatable[NpgsqlAnnotationNames.ValueGenerationStrategy] as NpgsqlValueGenerationStrategy?;
if (valueGenerationStrategy == NpgsqlValueGenerationStrategy.SerialColumn)
{
switch (type)
{
case "int":
case "int4":
case "integer":
type = "serial";
break;
case "bigint":
case "int8":
type = "bigserial";
break;
case "smallint":
case "int2":
type = "smallserial";
break;
}
}
base.ColumnDefinition(
schema,
table,
name,
clrType,
type,
unicode,
maxLength,
fixedLength,
rowVersion,
nullable,
defaultValue,
defaultValueSql,
computedColumnSql,
annotatable,
model,
builder);
switch (valueGenerationStrategy)
{
case NpgsqlValueGenerationStrategy.IdentityAlwaysColumn:
builder.Append(" GENERATED ALWAYS AS IDENTITY");
break;
case NpgsqlValueGenerationStrategy.IdentityByDefaultColumn:
builder.Append(" GENERATED BY DEFAULT AS IDENTITY");
break;
}
}
protected override void Generate(
[NotNull] CreateTableOperation operation,
[CanBeNull] IModel model,
[NotNull] MigrationCommandListBuilder builder,
bool terminate)
{
if (operation == null)
{
throw new ArgumentNullException(nameof(operation));
}
if (builder == null)
{
throw new ArgumentNullException(nameof(builder));
}
bool systemVersioned =
!string.IsNullOrEmpty(operation[DotNetExtensionsAnnotationNames.SystemVersioned] as string);
bool historyTable = operation[DotNetExtensionsAnnotationNames.HistoryTable] as bool? == true;
builder.Append("CREATE TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.AppendLine(" (");
using (builder.Indent())
{
for (var i = 0; i < operation.Columns.Count; i++)
{
AddColumnOperation column = operation.Columns[i];
ColumnDefinition(column, model, builder);
if (i != operation.Columns.Count - 1)
{
builder.AppendLine(",");
}
}
if (systemVersioned)
{
builder.AppendLine(",");
builder.Append("PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)");
}
if (operation.PrimaryKey != null && !historyTable)
{
builder.AppendLine(",");
PrimaryKeyConstraint(operation.PrimaryKey, model, builder);
}
foreach (AddUniqueConstraintOperation uniqueConstraint in operation.UniqueConstraints)
{
builder.AppendLine(",");
UniqueConstraint(uniqueConstraint, model, builder);
}
foreach (AddForeignKeyOperation foreignKey in operation.ForeignKeys)
{
builder.AppendLine(",");
ForeignKeyConstraint(foreignKey, model, builder);
}
builder.AppendLine();
}
builder.Append(")");
if (terminate)
{
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
}
protected virtual void Generate(NpgsqlCreateDatabaseOperation operation, [CanBeNull] IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("CREATE DATABASE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name));
if (operation.Template != null)
{
builder
.Append(" TEMPLATE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Template));
}
if (operation.Tablespace != null)
{
builder
.Append(" TABLESPACE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Tablespace));
}
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder, suppressTransaction: true);
}
protected override void Generate(
CreateTableOperation operation,
IModel model,
MigrationCommandListBuilder builder,
bool terminate)
{
// Filter out any system columns
if (operation.Columns.Any(c => IsSystemColumn(c.Name)))
{
var filteredOperation = new CreateTableOperation
{
Name = operation.Name,
Schema = operation.Schema,
PrimaryKey = operation.PrimaryKey,
};
filteredOperation.Columns.AddRange(operation.Columns.Where(c => !_systemColumnNames.Contains(c.Name)));
filteredOperation.ForeignKeys.AddRange(operation.ForeignKeys);
filteredOperation.UniqueConstraints.AddRange(operation.UniqueConstraints);
operation = filteredOperation;
}
base.Generate(operation, model, builder, false);
// CockroachDB "interleave in parent" (https://www.cockroachlabs.com/docs/stable/interleave-in-parent.html)
if (operation[CockroachDbAnnotationNames.InterleaveInParent] is string)
{
var interleaveInParent = new CockroachDbInterleaveInParent(operation);
var parentTableSchema = interleaveInParent.ParentTableSchema;
var parentTableName = interleaveInParent.ParentTableName;
var interleavePrefix = interleaveInParent.InterleavePrefix;
builder
.AppendLine()
.Append("INTERLEAVE IN PARENT ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(parentTableName, parentTableSchema))
.Append(" (")
.Append(string.Join(", ", interleavePrefix.Select(c => Dependencies.SqlGenerationHelper.DelimitIdentifier(c))))
.Append(')');
}
var storageParameters = GetStorageParameters(operation);
if (storageParameters.Count > 0)
{
builder
.AppendLine()
.Append("WITH (")
.Append(string.Join(", ", storageParameters.Select(p => $"{p.Key}={p.Value}")))
.Append(')');
}
// Comment on the table
var comment = operation[NpgsqlAnnotationNames.Comment] as string;
if (comment != null)
{
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
var stringTypeMapping = Dependencies.TypeMappingSource.GetMapping(typeof(string));
builder
.Append("COMMENT ON TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.Append(" IS ")
.Append(stringTypeMapping.GenerateSqlLiteral(comment));
}
// Comments on the columns
foreach (var columnOp in operation.Columns.Where(c => c[NpgsqlAnnotationNames.Comment] != null))
{
var columnComment = columnOp[NpgsqlAnnotationNames.Comment];
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
var stringTypeMapping = Dependencies.TypeMappingSource.GetMapping(typeof(string));
builder
.Append("COMMENT ON COLUMN ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.Append('.')
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(columnOp.Name))
.Append(" IS ")
.Append(stringTypeMapping.GenerateSqlLiteral(columnComment));
}
if (terminate)
{
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
}
protected override void Generate(RenameIndexOperation operation, [CanBeNull] IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var qualifiedName = new StringBuilder();
if (operation.Schema != null)
{
qualifiedName
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Schema))
.Append(".");
}
qualifiedName.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name));
// TODO: Rename across schema will break, see #44
Rename(qualifiedName.ToString(), Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.NewName), "INDEX", builder);
EndStatement(builder);
}
protected override void Generate(MigrationOperation operation, [CanBeNull] IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
if (operation is NpgsqlCreateDatabaseOperation createDatabaseOperation)
{
Generate(createDatabaseOperation, model, builder);
return;
}
if (operation is NpgsqlDropDatabaseOperation dropDatabaseOperation)
{
Generate(dropDatabaseOperation, model, builder);
return;
}
base.Generate(operation, model, builder);
}
protected override void IndexTraits(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var clustered = operation[JetAnnotationNames.Clustered] as bool?;
if (clustered.HasValue)
{
builder.Append(clustered.Value ? "CLUSTERED " : "NONCLUSTERED ");
}
}
protected override void ForeignKeyAction(ReferentialAction referentialAction, MigrationCommandListBuilder builder)
{
Check.NotNull(builder, nameof(builder));
if (referentialAction == ReferentialAction.Restrict)
{
builder.Append("NO ACTION");
}
else
{
base.ForeignKeyAction(referentialAction, builder);
}
}
protected override void Generate(DropForeignKeyOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" DROP CONSTRAINT ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(TruncateName(operation.Name)));
builder
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator)
.EndCommand(suppressTransaction: IsMemoryOptimized(operation, model, operation.Schema, operation.Table));
}
protected override void Generate(
DropColumnOperation operation,
IModel model,
MigrationCommandListBuilder builder)
=> Generate(operation, model, builder, terminate: true);
protected override void Generate(AlterTableOperation operation, IModel model, MigrationCommandListBuilder builder)
{
if (IsMemoryOptimized(operation)
^ IsMemoryOptimized(operation.OldTable))
{
throw new InvalidOperationException(JetStrings.AlterMemoryOptimizedTable);
}
base.Generate(operation, model, builder);
}
protected override void Generate(
AlterColumnOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
List <IIndex> indexesToRebuild = null;
var property = FindProperty(model, operation.Schema, operation.Table, operation.Name);
if (operation.ComputedColumnSql != null)
{
var dropColumnOperation = new DropColumnOperation
{
Schema = operation.Schema,
Table = operation.Table,
Name = operation.Name
};
if (property != null)
{
dropColumnOperation.AddAnnotations(_migrationsAnnotations.ForRemove(property));
}
var addColumnOperation = new AddColumnOperation
{
Schema = operation.Schema,
Table = operation.Table,
Name = operation.Name,
ClrType = operation.ClrType,
ColumnType = operation.ColumnType,
IsUnicode = operation.IsUnicode,
MaxLength = operation.MaxLength,
IsRowVersion = operation.IsRowVersion,
IsNullable = operation.IsNullable,
DefaultValue = operation.DefaultValue,
DefaultValueSql = operation.DefaultValueSql,
ComputedColumnSql = operation.ComputedColumnSql
};
addColumnOperation.AddAnnotations(operation.GetAnnotations());
// TODO: Use a column rebuild instead
indexesToRebuild = GetIndexesToRebuild(property, operation).ToList();
DropIndexes(indexesToRebuild, builder);
Generate(dropColumnOperation, model, builder, terminate: false);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
Generate(addColumnOperation, model, builder, terminate: false);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
CreateIndexes(indexesToRebuild, builder);
builder.EndCommand(suppressTransaction: IsMemoryOptimized(operation, model, operation.Schema, operation.Table));
return;
}
var narrowed = false;
if (IsOldColumnSupported(model))
{
var valueGenerationStrategy = operation[
JetAnnotationNames.ValueGenerationStrategy] as JetValueGenerationStrategy?;
var identity = valueGenerationStrategy == JetValueGenerationStrategy.IdentityColumn;
var oldValueGenerationStrategy = operation.OldColumn[
JetAnnotationNames.ValueGenerationStrategy] as JetValueGenerationStrategy?;
var oldIdentity = oldValueGenerationStrategy == JetValueGenerationStrategy.IdentityColumn;
if (identity != oldIdentity)
{
throw new InvalidOperationException(JetStrings.AlterIdentityColumn);
}
var type = operation.ColumnType
?? GetColumnType(
operation.Schema,
operation.Table,
operation.Name,
operation.ClrType,
operation.IsUnicode,
operation.MaxLength,
operation.IsRowVersion,
model);
var oldType = operation.OldColumn.ColumnType
?? GetColumnType(
operation.Schema,
operation.Table,
operation.Name,
operation.OldColumn.ClrType,
operation.OldColumn.IsUnicode,
operation.OldColumn.MaxLength,
operation.OldColumn.IsRowVersion,
model);
narrowed = type != oldType || !operation.IsNullable && operation.OldColumn.IsNullable;
}
if (narrowed)
{
indexesToRebuild = GetIndexesToRebuild(property, operation).ToList();
DropIndexes(indexesToRebuild, builder);
}
DropDefaultConstraint(operation.Schema, operation.Table, operation.Name, builder);
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" ALTER COLUMN ");
ColumnDefinition(
operation.Schema,
operation.Table,
operation.Name,
operation.ClrType,
operation.ColumnType,
operation.IsUnicode,
operation.MaxLength,
operation.IsRowVersion,
operation.IsNullable,
/*defaultValue:*/ null,
/*defaultValueSql:*/ null,
operation.ComputedColumnSql,
/*identity:*/ false,
operation,
model,
builder);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
if (operation.DefaultValue != null ||
operation.DefaultValueSql != null)
{
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" ADD");
DefaultValue(operation.DefaultValue, operation.DefaultValueSql, builder);
builder
.Append(" FOR ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
}
if (narrowed)
{
CreateIndexes(indexesToRebuild, builder);
}
builder.EndCommand(suppressTransaction: IsMemoryOptimized(operation, model, operation.Schema, operation.Table));
}
private void DropHistoryTable(MigrationCommandListBuilder builder)
{
builder.Append("DROP TABLE ")
.Append(Settings.HistoryTable)
.EndCommand(true);
}
protected override void Generate(AlterTableOperation operation, IModel model, MigrationCommandListBuilder builder)
{
var madeChanges = false;
// Storage parameters
var oldStorageParameters = GetStorageParameters(operation.OldTable);
var newStorageParameters = GetStorageParameters(operation);
var newOrChanged = newStorageParameters.Where(p =>
!oldStorageParameters.ContainsKey(p.Key) ||
oldStorageParameters[p.Key] != p.Value
).ToList();
if (newOrChanged.Count > 0)
{
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema));
builder
.Append(" SET (")
.Append(string.Join(", ", newOrChanged.Select(p => $"{p.Key}={p.Value}")))
.Append(")");
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
madeChanges = true;
}
var removed = oldStorageParameters
.Select(p => p.Key)
.Where(pn => !newStorageParameters.ContainsKey(pn))
.ToList();
if (removed.Count > 0)
{
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema));
builder
.Append(" RESET (")
.Append(string.Join(", ", removed))
.Append(")");
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
madeChanges = true;
}
// Comment
var oldComment = operation.OldTable[NpgsqlAnnotationNames.Comment] as string;
var newComment = operation[NpgsqlAnnotationNames.Comment] as string;
if (oldComment != newComment)
{
var stringTypeMapping = Dependencies.TypeMappingSource.GetMapping(typeof(string));
builder
.Append("COMMENT ON TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.Append(" IS ")
.Append(stringTypeMapping.GenerateSqlLiteral(newComment));
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
madeChanges = true;
}
if (madeChanges)
{
EndStatement(builder);
}
}
protected override void Generate(DropSchemaOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
}
protected override void Generate(AlterColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
// Never touch system columns
if (IsSystemColumn(operation.Name))
{
return;
}
var type = operation.ColumnType ?? GetColumnType(operation.Schema, operation.Table, operation.Name, operation.ClrType, null, operation.MaxLength, false, model);
// User-defined type names are quoted if they contain uppercase letters. Other types are never quoted
// since users sometimes prefer to write TEXT instead of text.
if (_typeMappingSource.IsUserDefinedType(type))
{
type = _sqlGenerationHelper.DelimitIdentifier(type);
}
string newSequenceName = null;
var defaultValueSql = operation.DefaultValueSql;
var table = Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema);
var column = Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name);
var alterBase = $"ALTER TABLE {table} ALTER COLUMN {column} ";
// TYPE
builder.Append(alterBase)
.Append("TYPE ")
.Append(type)
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
// NOT NULL
builder.Append(alterBase)
.Append(operation.IsNullable ? "DROP NOT NULL" : "SET NOT NULL")
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
CheckForOldValueGenerationAnnotation(operation);
var oldStrategy = operation.OldColumn[NpgsqlAnnotationNames.ValueGenerationStrategy] as NpgsqlValueGenerationStrategy?;
var newStrategy = operation[NpgsqlAnnotationNames.ValueGenerationStrategy] as NpgsqlValueGenerationStrategy?;
if (oldStrategy != newStrategy)
{
// We have a value generation strategy change
if (oldStrategy == NpgsqlValueGenerationStrategy.SerialColumn)
{
// TODO: It would be better to actually select for the owned sequence.
// This would require plpgsql.
var sequenceName = Dependencies.SqlGenerationHelper.DelimitIdentifier($"{operation.Table}_{operation.Name}_seq");
switch (newStrategy)
{
case null:
// Drop the serial, converting the column to a regular int
builder.AppendLine($"DROP SEQUENCE {sequenceName} CASCADE;");
break;
case NpgsqlValueGenerationStrategy.IdentityAlwaysColumn:
case NpgsqlValueGenerationStrategy.IdentityByDefaultColumn:
// Convert serial column to identity, maintaining the current sequence value
var identityTypeClause = newStrategy == NpgsqlValueGenerationStrategy.IdentityAlwaysColumn
? "ALWAYS"
: "BY DEFAULT";
var oldSequenceName = Dependencies.SqlGenerationHelper.DelimitIdentifier($"{operation.Table}_{operation.Name}_old_seq");
builder
.AppendLine($"ALTER SEQUENCE {sequenceName} RENAME TO {oldSequenceName};")
.AppendLine($"ALTER TABLE {table} ALTER COLUMN {column} DROP DEFAULT;")
.AppendLine($"ALTER TABLE {table} ALTER COLUMN {column} ADD GENERATED {identityTypeClause} AS IDENTITY;")
.AppendLine($"SELECT * FROM setval('{sequenceName}', nextval('{oldSequenceName}'), false);")
.AppendLine($"DROP SEQUENCE {oldSequenceName};");
break;
default:
throw new NotSupportedException($"Don't know how to migrate serial column to {newStrategy}");
}
}
else if (oldStrategy == NpgsqlValueGenerationStrategy.IdentityAlwaysColumn ||
oldStrategy == NpgsqlValueGenerationStrategy.IdentityByDefaultColumn)
{
switch (newStrategy)
{
case null:
// Drop the identity, converting the column to a regular int
builder.AppendLine(alterBase).AppendLine("DROP IDENTITY;");
break;
case NpgsqlValueGenerationStrategy.IdentityAlwaysColumn:
builder.Append(alterBase).AppendLine("SET GENERATED ALWAYS;");
break;
case NpgsqlValueGenerationStrategy.IdentityByDefaultColumn:
builder.Append(alterBase).AppendLine("SET GENERATED BY DEFAULT;");
break;
case NpgsqlValueGenerationStrategy.SerialColumn:
throw new NotSupportedException("Migrating from identity to serial isn't currently supported (and is a bad idea)");
default:
throw new NotSupportedException($"Don't know how to migrate identity column to {newStrategy}");
}
}
else if (oldStrategy == null)
{
switch (newStrategy)
{
case NpgsqlValueGenerationStrategy.IdentityAlwaysColumn:
builder.Append(alterBase).AppendLine("ADD GENERATED ALWAYS AS IDENTITY;");
break;
case NpgsqlValueGenerationStrategy.IdentityByDefaultColumn:
builder.Append(alterBase).AppendLine("ADD GENERATED BY DEFAULT AS IDENTITY;");
break;
case NpgsqlValueGenerationStrategy.SerialColumn:
switch (type)
{
case "integer":
case "int":
case "int4":
case "bigint":
case "int8":
case "smallint":
case "int2":
newSequenceName = $"{operation.Table}_{operation.Name}_seq";
Generate(new CreateSequenceOperation
{
Name = newSequenceName,
ClrType = operation.ClrType
}, model, builder);
builder.Append(alterBase).Append("SET");
DefaultValue(null, $@"nextval('{Dependencies.SqlGenerationHelper.DelimitIdentifier(newSequenceName)}')", builder);
builder.AppendLine(';');
// Note: we also need to set the sequence ownership, this is done below after the ALTER COLUMN
break;
}
break;
default:
throw new NotSupportedException($"Don't know how to apply value generation strategy {newStrategy}");
}
}
}
// DEFAULT.
// Note that defaults values for value-generated columns (identity, serial) are managed above. This is
// only for regular columns with user-specified default settings.
if (newStrategy == null)
{
builder.Append(alterBase);
if (operation.DefaultValue != null || defaultValueSql != null)
{
builder.Append("SET");
DefaultValue(operation.DefaultValue, defaultValueSql, builder);
}
else
{
builder.Append("DROP DEFAULT");
}
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
}
// A sequence has been created because this column was altered to be a serial.
// Change the sequence's ownership.
if (newSequenceName != null)
{
builder
.Append("ALTER SEQUENCE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(newSequenceName))
.Append(" OWNED BY ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table))
.Append('.')
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
}
// Comment
var oldComment = operation.OldColumn[NpgsqlAnnotationNames.Comment] as string;
var newComment = operation[NpgsqlAnnotationNames.Comment] as string;
if (oldComment != newComment)
{
var stringTypeMapping = Dependencies.TypeMappingSource.GetMapping(typeof(string));
builder
.Append("COMMENT ON COLUMN ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append('.')
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" IS ")
.Append(stringTypeMapping.GenerateSqlLiteral(newComment))
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
}
EndStatement(builder);
}
protected override void Generate(DropSequenceOperation operation, IModel model, MigrationCommandListBuilder builder)
{
throw new NotSupportedException("JET does not support sequences");
}
protected override void Generate(RenameTableOperation operation, [CanBeNull] IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var name = operation.Name;
if (operation.NewName != null)
{
var qualifiedName = new StringBuilder();
if (operation.Schema != null)
{
qualifiedName
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Schema))
.Append(".");
}
qualifiedName.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name));
Rename(qualifiedName.ToString(), Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.NewName), "TABLE", builder);
name = operation.NewName;
}
if (operation.NewSchema != null)
{
Transfer(operation.NewSchema, operation.Schema, name, "TABLE", builder);
}
EndStatement(builder);
}
protected override void Generate(AlterColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
{
throw new NotSupportedException(SqliteStrings.InvalidMigrationOperation(operation.GetType().ShortDisplayName()));
}
protected override void Generate(EnsureSchemaOperation operation, [CanBeNull] IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
// PostgreSQL 9.2 and below unfortunately doesn't have CREATE SCHEMA IF NOT EXISTS.
// An attempted workaround by creating a function which checks and creates the schema, and then invoking it, failed because
// of #641 (pg_temp doesn't exist yet).
// So the only workaround for pre-9.3 PostgreSQL, at least for now, is to define all tables in the public schema.
// TODO: Since Npgsql 3.1 we can now ensure schema with a function in pg_temp
// NOTE: Technically the public schema can be dropped so we should also be ensuring it, but this is a rare case and
// we want to allow pre-9.3
if (operation.Name == "public")
{
return;
}
builder
.Append("CREATE SCHEMA IF NOT EXISTS ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
protected override void Generate(DropSchemaOperation operation, IModel model, MigrationCommandListBuilder builder)
{
}
public virtual void Generate(NpgsqlDropDatabaseOperation operation, [CanBeNull] IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var dbName = Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name);
builder
// TODO: The following revokes connection only for the public role, what about other connecting roles?
.Append("REVOKE CONNECT ON DATABASE ")
.Append(dbName)
.Append(" FROM PUBLIC")
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator)
// TODO: For PG <= 9.1, the column name is prodpic, not pid (see http://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it)
.Append(
"SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname = '")
.Append(operation.Name)
.Append("'")
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator)
.EndCommand(suppressTransaction: true)
.Append("DROP DATABASE ")
.Append(dbName)
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder, suppressTransaction: true);
}
protected override void Generate(DropSequenceOperation operation, IModel model, MigrationCommandListBuilder builder)
{
throw new NotSupportedException(SqliteStrings.SequencesNotSupported);
}
protected override void Generate(DropIndexOperation operation, [CanBeNull] IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("DROP INDEX ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
protected override void Generate(RenameTableOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
if (operation.NewName != null)
{
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" RENAME TO ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.NewName))
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator)
.EndCommand();
}
}
// Required to "activate" IsFixedLength
protected override void ColumnDefinition(AddColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
=> ColumnDefinition(
operation.Schema,
operation.Table,
operation.Name,
operation.ClrType,
operation.ColumnType,
operation.IsUnicode,
operation.MaxLength,
operation.IsFixedLength,
operation.IsRowVersion,
operation.IsNullable,
operation.DefaultValue,
operation.DefaultValueSql,
operation.ComputedColumnSql,
operation,
model,
builder);
protected override void Generate(CreateTableOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
// Lifts a primary key definition into the typename.
// This handles the quirks of creating integer primary keys using autoincrement, not default rowid behavior.
if (operation.PrimaryKey?.Columns.Length == 1)
{
var columnOp = operation.Columns.FirstOrDefault(o => o.Name == operation.PrimaryKey.Columns[0]);
if (columnOp != null)
{
columnOp.AddAnnotation(SqliteFullAnnotationNames.Instance.InlinePrimaryKey, true);
if (!string.IsNullOrEmpty(operation.PrimaryKey.Name))
{
columnOp.AddAnnotation(SqliteFullAnnotationNames.Instance.InlinePrimaryKeyName, operation.PrimaryKey.Name);
}
operation.PrimaryKey = null;
}
}
base.Generate(operation, model, builder);
}
protected override void Generate(
CreateTableOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Generate(operation, model, builder, false);
bool memoryOptimized = operation[SqlServerAnnotationNames.MemoryOptimized] as bool? == true;
var historyEntityTypeName = operation[DotNetExtensionsAnnotationNames.SystemVersioned] as string;
var tableOptions = new List <string>();
if (memoryOptimized)
{
tableOptions.Add("MEMORY_OPTIMIZED = ON");
}
if (historyEntityTypeName != null)
{
IEntityType historyEntityType = model.FindEntityType(historyEntityTypeName);
var versioningOptions = new List <string>
{
$"HISTORY_TABLE = {Dependencies.SqlGenerationHelper.DelimitIdentifier(historyEntityType[RelationalAnnotationNames.TableName] as string, operation.Schema ?? "dbo")}"
};
var retentionPeriod = operation[DotNetExtensionsAnnotationNames.RetentionPeriod] as string;
if (retentionPeriod != null)
{
versioningOptions.Add($"HISTORY_RETENTION_PERIOD = {retentionPeriod}");
}
tableOptions.Add($"SYSTEM_VERSIONING = ON ({string.Join(", ", versioningOptions)})");
}
if (tableOptions.Any())
{
builder.AppendLine();
using (builder.Indent())
{
builder.AppendLine("WITH (");
using (builder.Indent())
{
for (var i = 0; i < tableOptions.Count; i++)
{
string option = tableOptions[i];
builder.Append(option);
if (i + 1 != tableOptions.Count)
{
builder.AppendLine(",");
}
else
{
builder.AppendLine();
}
}
}
builder.Append(")");
}
}
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator).EndCommand(memoryOptimized);
}
/// <summary>
/// 修改列。
/// </summary>
/// <param name="operation">操作实例。</param>
/// <param name="builder"><see cref="MigrationCommandListBuilder"/>实例对象。</param>
protected override void Generate(
AlterColumnOperation operation,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
if (operation.ComputedColumnSql != null)
{
var dropColumnOperation = new DropColumnOperation
{
Table = operation.Table,
Name = operation.Name
};
var addColumnOperation = new AddColumnOperation
{
Table = operation.Table,
Name = operation.Name,
ClrType = operation.ClrType,
ColumnType = operation.ColumnType,
IsUnicode = operation.IsUnicode,
MaxLength = operation.MaxLength,
IsRowVersion = operation.IsRowVersion,
IsNullable = operation.IsNullable,
DefaultValue = operation.DefaultValue,
DefaultValueSql = operation.DefaultValueSql,
ComputedColumnSql = operation.ComputedColumnSql
};
Generate(dropColumnOperation, builder, false);
builder.AppendLine(SqlHelper.StatementTerminator);
Generate(addColumnOperation, builder, false);
builder.AppendLine(SqlHelper.StatementTerminator);
builder.EndCommand();
return;
}
DropDefaultConstraint(operation.Table, operation.Name, builder);
builder
.Append("ALTER TABLE ")
.Append(operation.Table)
.Append(" ALTER COLUMN ");
ColumnDefinition(
operation.Table,
operation.Name,
operation.ClrType,
operation.ColumnType,
operation.IsUnicode,
operation.MaxLength,
operation.IsRowVersion,
operation.IsIdentity,
operation.IsNullable,
/*defaultValue:*/ null,
/*defaultValueSql:*/ null,
operation.ComputedColumnSql,
operation,
builder);
builder.AppendLine(SqlHelper.StatementTerminator);
if ((operation.DefaultValue != null) ||
(operation.DefaultValueSql != null))
{
builder
.Append("ALTER TABLE ")
.Append(operation.Table)
.Append(" ADD");
DefaultValue(operation.DefaultValue, operation.DefaultValueSql, builder);
builder
.Append(" FOR ")
.Append(SqlHelper.DelimitIdentifier(operation.Name))
.AppendLine(SqlHelper.StatementTerminator);
}
builder.EndCommand();
}
