/// <summary>
/// Generates a SQL fragment for a column definition for the given column metadata.
/// </summary>
/// <param name="schema"> The schema that contains the table, or <c>null</c> to use the default schema. </param>
/// <param name="table"> The table that contains the column. </param>
/// <param name="name"> The column name. </param>
/// <param name="operation"> The column metadata. </param>
/// <param name="model"> The target model which may be <c>null</c> if the operations exist without a model. </param>
/// <param name="builder"> The command builder to use to add the SQL fragment. </param>
protected override void ColumnDefinition(
string schema,
string table,
string name,
ColumnOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotEmpty(name, nameof(name));
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var matchType = operation.ColumnType ?? GetColumnType(schema, table, name, operation, model);
var matchLen = "";
var match = _typeRegex.Match(matchType ?? "-");
if (match.Success)
{
matchType = match.Groups[1].Value.ToLower();
if (!string.IsNullOrWhiteSpace(match.Groups[2].Value))
{
matchLen = match.Groups[2].Value;
}
}
var valueGenerationStrategy = MySQLValueGenerationStrategyCompatibility.GetValueGenerationStrategy(operation.GetAnnotations().OfType <IAnnotation>().ToArray());
var autoIncrement = false;
if (valueGenerationStrategy == MySQLValueGenerationStrategy.IdentityColumn &&
string.IsNullOrWhiteSpace(operation.DefaultValueSql) && operation.DefaultValue == null)
{
switch (matchType)
{
case "tinyint":
case "smallint":
case "mediumint":
case "int":
case "bigint":
autoIncrement = true;
break;
case "datetime":
case "timestamp":
operation.DefaultValueSql = $"CURRENT_TIMESTAMP({matchLen})";
break;
}
}
string onUpdateSql = null;
if (operation.IsRowVersion || valueGenerationStrategy == MySQLValueGenerationStrategy.ComputedColumn)
{
switch (matchType)
{
case "datetime":
case "timestamp":
if (string.IsNullOrWhiteSpace(operation.DefaultValueSql) && operation.DefaultValue == null)
{
operation.DefaultValueSql = $"CURRENT_TIMESTAMP({matchLen})";
}
onUpdateSql = $"CURRENT_TIMESTAMP({matchLen})";
break;
}
}
if (operation.ComputedColumnSql == null)
{
ColumnDefinitionWithCharSet(schema, table, name, operation, model, builder);
if (autoIncrement)
{
builder.Append(" AUTO_INCREMENT");
}
else
{
if (onUpdateSql != null)
{
builder
.Append(" ON UPDATE ")
.Append(onUpdateSql);
}
}
}
else
{
builder
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(name))
.Append(" ")
.Append(operation.ColumnType ?? GetColumnType(schema, table, name, operation, model));
builder
.Append(" AS ")
.Append($"({operation.ComputedColumnSql})");
if (operation.IsNullable)
{
builder.Append(" NULL");
}
}
}
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);
}
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(';');
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(';');
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(';');
madeChanges = true;
}
if (madeChanges)
{
EndStatement(builder);
}
}
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
if (operation[NpgsqlAnnotationNames.Comment] is string comment && comment.Length > 0)
{
builder.AppendLine(';');
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(';');
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(';');
EndStatement(builder);
}
}
protected virtual void Generate(EnableTemporalTableOperation operation, IModel model, MigrationCommandListBuilder builder)
{
string _SchemaQualifiedTableName =
Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema);
string _StartDate = operation.SysStartDate;
string _EndDate = operation.SysEndDate;
builder.AppendLine("IF NOT EXISTS(");
using (builder.Indent())
{
builder
.AppendLine("SELECT NULL FROM sys.[columns]")
.AppendLine($"WHERE OBJECT_ID('{_SchemaQualifiedTableName}') = [object_id] AND [name] = '{_StartDate}'");
}
builder.AppendLine(") AND NOT EXISTS(");
using (builder.Indent())
{
builder
.AppendLine("SELECT NULL FROM sys.[columns]")
.AppendLine($"WHERE OBJECT_ID('{_SchemaQualifiedTableName}') = [object_id] AND [name] = '{_EndDate}'");
}
builder
.AppendLine(")")
.AppendLine("BEGIN");
using (builder.Indent())
{
builder
.Append("ALTER TABLE ")
.AppendLine(_SchemaQualifiedTableName)
.AppendLine("ADD ");
using (builder.Indent())
{
builder
.Append($"{_StartDate} DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN")
.AppendLine();
using (builder.Indent())
{
builder
.Append("CONSTRAINT ")
.Append($"DF_{operation.Name}_{_StartDate}")
.Append(" DEFAULT (SYSUTCDATETIME())")
.AppendLine(",");
}
builder
.Append($"{_EndDate} DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN")
.AppendLine();
using (builder.Indent())
{
builder
.Append("CONSTRAINT ")
.Append($"DF_{operation.Name}_{_EndDate}")
.Append(" DEFAULT('9999-12-31 23:59:59.9999999')")
.AppendLine(",");
}
builder.Append($"PERIOD FOR SYSTEM_TIME([{_StartDate}], [{_EndDate}])");
}
}
builder
.AppendLine()
.AppendLine("END")
.EndCommand();
builder.AppendLine("IF NOT EXISTS(SELECT NULL FROM sys.[periods]");
using (builder.Indent())
{
builder.AppendLine($"WHERE [object_id] = OBJECT_ID('{_SchemaQualifiedTableName}'))");
}
builder.AppendLine("BEGIN");
using (builder.Indent())
{
builder.AppendLine($"EXEC('ALTER TABLE {_SchemaQualifiedTableName} ADD PERIOD FOR SYSTEM_TIME([{_StartDate}], [{_EndDate}])')");
}
builder
.AppendLine("END")
.EndCommand();
builder
.AppendLine($"ALTER TABLE {_SchemaQualifiedTableName} SET(");
using (builder.Indent())
{
builder.AppendLine("SYSTEM_VERSIONING = ON (");
using (builder.Indent())
{
builder
.Append($"HISTORY_TABLE = ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.HistoryTable, operation.HistorySchema ?? TemporalAnnotationNames.DefaultSchema));
if (operation.DataConsistencyCheck)
{
builder
.AppendLine(",")
.AppendLine("DATA_CONSISTENCY_CHECK = ON");
}
}
builder.AppendLine(")");
}
builder.AppendLine(")").EndCommand();
}
/// <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,
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();
}
public virtual void DropSequenceTriggerForColumn(string columnName, string tableName, string schemaName, MigrationsSqlGenerationOptions options, MigrationCommandListBuilder builder)
{
var triggerName = CreateSequenceTriggerName(columnName, tableName, schemaName);
if (options.HasFlag(MigrationsSqlGenerationOptions.Script))
{
builder.Append("SET TERM ");
builder.Append(((IFbSqlGenerationHelper)_sqlGenerationHelper).AlternativeStatementTerminator);
builder.AppendLine(_sqlGenerationHelper.StatementTerminator);
builder.EndCommand();
}
builder.AppendLine("EXECUTE BLOCK");
builder.AppendLine("AS");
builder.AppendLine("BEGIN");
builder.IncrementIndent();
builder.Append("if (exists(select 1 from rdb$triggers where rdb$trigger_name = '");
builder.Append(triggerName);
builder.Append("')) then");
builder.AppendLine();
builder.AppendLine("begin");
builder.IncrementIndent();
builder.Append("execute statement 'drop trigger ");
builder.Append(_sqlGenerationHelper.DelimitIdentifier(triggerName));
builder.Append("'");
builder.Append(_sqlGenerationHelper.StatementTerminator);
builder.AppendLine();
builder.DecrementIndent();
builder.AppendLine("end");
builder.DecrementIndent();
builder.Append("END");
builder.AppendLine();
if (options.HasFlag(MigrationsSqlGenerationOptions.Script))
{
builder.AppendLine(((IFbSqlGenerationHelper)_sqlGenerationHelper).AlternativeStatementTerminator);
}
else
{
builder.AppendLine(_sqlGenerationHelper.StatementTerminator);
}
builder.EndCommand();
if (options.HasFlag(MigrationsSqlGenerationOptions.Script))
{
builder.Append("SET TERM ");
builder.Append(_sqlGenerationHelper.StatementTerminator);
builder.AppendLine(((IFbSqlGenerationHelper)_sqlGenerationHelper).AlternativeStatementTerminator);
builder.EndCommand();
}
}
protected override void Generate(CreateTableOperation operation, IModel model, MigrationCommandListBuilder builder, bool terminate)
{
base.Generate(operation, model, builder, false);
if (terminate)
{
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
// If Firebird Version > = 3
if (_options.Settings.IsSupportIdentityIncrement)
{
return;
}
foreach (var column in operation.Columns.Where(p => !p.IsNullable))
{
var colAnnotation = (IAnnotation)column.FindAnnotation(FbAnnotationNames.ValueGenerationStrategy);
if (colAnnotation is null)
{
continue;
}
var valueGenerationStrategy = colAnnotation.Value as FbValueGenerationStrategy?;
if (valueGenerationStrategy is null)
{
continue;
}
if (valueGenerationStrategy == FbValueGenerationStrategy.IdentityColumn && string.IsNullOrWhiteSpace(column.DefaultValueSql) && column.DefaultValue == null)
{
var mergeColumnTable = string.Format("{0}_{1}", column.Table, column.Name).ToUpper();
var sequenceName = string.Format("GEN_IDENTITY_{0}", mergeColumnTable);
var triggerName = string.Format("ID_{0}", mergeColumnTable);
builder.AppendLine("EXECUTE BLOCK");
builder.AppendLine("AS");
builder.AppendLine("BEGIN");
builder.Append("if (not exists(select 1 from rdb$generators where rdb$generator_name = '");
builder.Append(sequenceName);
builder.Append("')) then");
builder.AppendLine();
builder.AppendLine("begin");
builder.Append("execute statement 'create sequence ");
builder.Append(sequenceName);
builder.Append("';");
builder.AppendLine();
builder.AppendLine("end");
builder.AppendLine("END");
EndStatement(builder);
builder.Append("CREATE OR ALTER TRIGGER ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(triggerName));
builder.Append(" ACTIVE BEFORE INSERT ON ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(column.Table));
builder.AppendLine();
builder.AppendLine("AS");
builder.AppendLine("BEGIN");
builder.AppendLine();
builder.Append("if (new.");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(column.Name));
builder.Append(" is null) then");
builder.AppendLine();
builder.AppendLine("begin");
builder.Append("new.");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(column.Name));
builder.Append(" = next value for ");
builder.Append(sequenceName);
builder.Append(";");
builder.AppendLine();
builder.AppendLine("end");
builder.Append("END");
EndStatement(builder);
}
}
}
protected override void Generate(AlterColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
{
var valueGenerationStrategy = operation[FbAnnotationNames.ValueGenerationStrategy] as FbValueGenerationStrategy?;
var oldValueGenerationStrategy = operation.OldColumn[FbAnnotationNames.ValueGenerationStrategy] as FbValueGenerationStrategy?;
if (oldValueGenerationStrategy == FbValueGenerationStrategy.IdentityColumn && valueGenerationStrategy != FbValueGenerationStrategy.IdentityColumn)
{
throw new InvalidOperationException("Cannot remove identity from column.");
// will be recreated, if needed, by next statement
// supported only on FB4
//builder.Append("ALTER TABLE ");
//builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema));
//builder.Append(" ALTER COLUMN ");
//builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name));
//builder.Append(" DROP IDENTITY");
//TerminateStatement(builder);
}
if (oldValueGenerationStrategy == FbValueGenerationStrategy.SequenceTrigger && valueGenerationStrategy != FbValueGenerationStrategy.SequenceTrigger)
{
_behavior.DropSequenceTriggerForColumn(operation.Name, operation.Table, operation.Schema, Options, builder);
}
// will be recreated, if needed, by next statement
builder.Append("ALTER TABLE ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema));
builder.Append(" ALTER COLUMN ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name));
builder.Append(" DROP NOT NULL");
TerminateStatement(builder);
builder.Append("ALTER TABLE ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema));
builder.Append(" ALTER COLUMN ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name));
builder.Append(" TYPE ");
if (operation.ColumnType != null)
{
builder.Append(operation.ColumnType);
}
else
{
var type = GetColumnType(operation.Schema, operation.Table, operation.Name, operation, model);
builder.Append(type);
}
if (valueGenerationStrategy == FbValueGenerationStrategy.IdentityColumn)
{
builder.Append(" GENERATED BY DEFAULT AS IDENTITY");
}
TerminateStatement(builder);
if (!operation.IsNullable)
{
builder.Append("ALTER TABLE ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema));
builder.Append(" ALTER COLUMN ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name));
builder.Append(" SET NOT NULL");
TerminateStatement(builder);
}
if (operation.DefaultValue != null || !string.IsNullOrWhiteSpace(operation.DefaultValueSql))
{
builder.Append("ALTER TABLE ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema));
builder.Append(" ALTER COLUMN ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name));
builder.Append(" DROP DEFAULT");
TerminateStatement(builder);
builder.Append("ALTER TABLE ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema));
builder.Append(" ALTER COLUMN ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name));
builder.Append(" SET");
DefaultValue(operation.DefaultValue, operation.DefaultValueSql, operation.ColumnType, builder);
TerminateStatement(builder);
}
if (valueGenerationStrategy == FbValueGenerationStrategy.SequenceTrigger)
{
_behavior.CreateSequenceTriggerForColumn(operation.Name, operation.Table, operation.Schema, Options, builder);
}
}
public override MigrationCommandListBuilder Append(string o)
{
_builder.Append(o);
return(this);
}
protected override void ForeignKeyConstraint(AddForeignKeyOperation operation, IModel model, MigrationCommandListBuilder builder)
{
if (operation.Name != null)
{
builder.Append("CONSTRAINT ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name));
builder.Append(" ");
}
builder.Append("FOREIGN KEY (");
builder.Append(ColumnList(operation.Columns));
builder.Append(") REFERENCES ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.PrincipalTable, operation.PrincipalSchema));
if (operation.PrincipalColumns != null)
{
builder.Append(" (");
builder.Append(ColumnList(operation.PrincipalColumns));
builder.Append(")");
}
if (operation.OnUpdate != ReferentialAction.Restrict)
{
builder.Append(" ON UPDATE ");
ForeignKeyAction(operation.OnUpdate, builder);
}
if (operation.OnDelete != ReferentialAction.Restrict)
{
builder.Append(" ON DELETE ");
ForeignKeyAction(operation.OnDelete, builder);
}
}
protected override void Generate(CreateIndexOperation operation, IModel model, MigrationCommandListBuilder builder, bool terminate = true)
{
builder.Append("CREATE ");
if (operation.IsUnique)
{
builder.Append("UNIQUE ");
}
IndexTraits(operation, model, builder);
builder.Append("INDEX ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name));
builder.Append(" ON ");
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema));
if (!string.IsNullOrEmpty(operation.Filter))
{
builder.Append(" COMPUTED BY (");
builder.Append(operation.Filter);
builder.Append(")");
}
else
{
builder.Append(" (");
builder.Append(ColumnList(operation.Columns));
builder.Append(")");
}
if (terminate)
{
TerminateStatement(builder);
}
}
protected override void Generate(CreateTableOperation operation, IModel model, MigrationCommandListBuilder builder)
{
var memoryOptimized = operation[SqlServerAnnotationNames.MemoryOptimized] as bool? == true;
var temporal = operation[SystemVersioningConstants.SqlServerSystemVersioning] as bool? == true;
builder
.Append("CREATE TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.AppendLine(" (");
var schema = operation.Schema ?? "dbo";
using (builder.Indent())
{
for (var i = 0; i < operation.Columns.Count; i++)
{
var column = operation.Columns[i];
ColumnDefinition(column, model, builder);
if (i != operation.Columns.Count - 1)
{
builder.AppendLine(",");
}
}
if (operation.PrimaryKey != null)
{
builder.AppendLine(",");
PrimaryKeyConstraint(operation.PrimaryKey, model, builder);
}
foreach (var uniqueConstraint in operation.UniqueConstraints)
{
builder.AppendLine(",");
UniqueConstraint(uniqueConstraint, model, builder);
}
foreach (var foreignKey in operation.ForeignKeys)
{
builder.AppendLine(",");
ForeignKeyConstraint(foreignKey, model, builder);
}
if (temporal)
{
builder.AppendLine(",");
builder.Append(
@"[SysStartTime] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
[SysEndTime] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME([SysStartTime], [SysEndTime])" );
}
builder.AppendLine();
}
builder.Append(")");
if (memoryOptimized || temporal)
{
builder.AppendLine();
using (builder.Indent())
{
builder.AppendLine("WITH (");
using (builder.Indent())
{
if (memoryOptimized)
{
builder.Append("MEMORY_OPTIMIZED = ON");
}
if (temporal)
{
builder.Append($"SYSTEM_VERSIONING = ON (HISTORY_TABLE = [{schema}].[{operation.Name}History])");
}
}
builder.AppendLine(")");
}
}
builder
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator)
.EndCommand(suppressTransaction: memoryOptimized);
}
protected override void ColumnDefinition(
string schema,
string table,
string name,
Type clrType,
string type,
bool?unicode,
int?maxLength,
bool rowVersion,
bool nullable,
object defaultValue,
string defaultValueSql,
string computedColumnSql,
IAnnotatable annotatable,
IModel model,
MigrationCommandListBuilder builder)
{
ThrowIf.Argument.IsEmpty(name, "name");
ThrowIf.Argument.IsNull(clrType, "clrType");
ThrowIf.Argument.IsNull(annotatable, "annotatable");
ThrowIf.Argument.IsNull(builder, "builder");
var property = FindProperty(model, schema, table, name);
if (type == null)
{
//Any property that maps to the column will work
type = property != null
? _typeMapper.GetMapping(property).StoreType
: _typeMapper.GetMapping(clrType).StoreType;
}
var charset = property?.FindAnnotation(MySQLAnnotationNames.Charset);
if (charset != null)
{
type += $" CHARACTER SET {charset.Value}";
}
var collation = property?.FindAnnotation(MySQLAnnotationNames.Collation);
if (collation != null)
{
type += $" COLLATE {collation.Value}";
}
if (computedColumnSql != null)
{
builder
.Append(_sqlGenerationHelper.DelimitIdentifier(name))
.Append(string.Format(" {0} AS ", type))
.Append(" (" + computedColumnSql + ")");
return;
}
var autoInc = annotatable[MySQLAnnotationNames.AutoIncrement];
base.ColumnDefinition(
schema, table, name, clrType, type, unicode, maxLength, rowVersion, nullable,
defaultValue, defaultValueSql, computedColumnSql, annotatable, model, builder);
if (autoInc != null && (bool)autoInc)
{
builder.Append(" AUTO_INCREMENT");
}
}
public virtual void CreateSequenceTriggerForColumn(string columnName, string tableName, string schemaName, MigrationsSqlGenerationOptions options, MigrationCommandListBuilder builder)
{
var identitySequenceName = CreateSequenceTriggerSequenceName(columnName, tableName, schemaName);
if (options.HasFlag(MigrationsSqlGenerationOptions.Script))
{
builder.Append("SET TERM ");
builder.Append(((IFbSqlGenerationHelper)_sqlGenerationHelper).AlternativeStatementTerminator);
builder.AppendLine(_sqlGenerationHelper.StatementTerminator);
builder.EndCommand();
}
builder.AppendLine("EXECUTE BLOCK");
builder.AppendLine("AS");
builder.AppendLine("BEGIN");
builder.IncrementIndent();
builder.Append("if (not exists(select 1 from rdb$generators where rdb$generator_name = '");
builder.Append(identitySequenceName);
builder.Append("')) then");
builder.AppendLine();
builder.AppendLine("begin");
builder.IncrementIndent();
builder.Append("execute statement 'create sequence ");
builder.Append(identitySequenceName);
builder.Append("'");
builder.Append(_sqlGenerationHelper.StatementTerminator);
builder.AppendLine();
builder.DecrementIndent();
builder.AppendLine("end");
builder.DecrementIndent();
builder.Append("END");
builder.AppendLine();
if (options.HasFlag(MigrationsSqlGenerationOptions.Script))
{
builder.AppendLine(((IFbSqlGenerationHelper)_sqlGenerationHelper).AlternativeStatementTerminator);
}
else
{
builder.AppendLine(_sqlGenerationHelper.StatementTerminator);
}
builder.EndCommand();
builder.Append("CREATE TRIGGER ");
builder.Append(_sqlGenerationHelper.DelimitIdentifier(CreateSequenceTriggerName(columnName, tableName, schemaName)));
builder.Append(" ACTIVE BEFORE INSERT ON ");
builder.Append(_sqlGenerationHelper.DelimitIdentifier(tableName, schemaName));
builder.AppendLine();
builder.AppendLine("AS");
builder.AppendLine("BEGIN");
builder.IncrementIndent();
builder.Append("if (new.");
builder.Append(_sqlGenerationHelper.DelimitIdentifier(columnName));
builder.Append(" is null) then");
builder.AppendLine();
builder.AppendLine("begin");
builder.IncrementIndent();
builder.Append("new.");
builder.Append(_sqlGenerationHelper.DelimitIdentifier(columnName));
builder.Append(" = next value for ");
builder.Append(identitySequenceName);
builder.Append(_sqlGenerationHelper.StatementTerminator);
builder.AppendLine();
builder.DecrementIndent();
builder.AppendLine("end");
builder.DecrementIndent();
builder.Append("END");
builder.AppendLine();
if (options.HasFlag(MigrationsSqlGenerationOptions.Script))
{
builder.AppendLine(((IFbSqlGenerationHelper)_sqlGenerationHelper).AlternativeStatementTerminator);
}
else
{
builder.AppendLine(_sqlGenerationHelper.StatementTerminator);
}
builder.EndCommand();
if (options.HasFlag(MigrationsSqlGenerationOptions.Script))
{
builder.Append("SET TERM ");
builder.Append(_sqlGenerationHelper.StatementTerminator);
builder.AppendLine(((IFbSqlGenerationHelper)_sqlGenerationHelper).AlternativeStatementTerminator);
builder.EndCommand();
}
}
public virtual void CreateIdentityForColumn(string columnName, string tableName, MigrationCommandListBuilder builder)
{
var identitySequenceName = CreateIdentitySequenceName(columnName, tableName);
builder.AppendLine("EXECUTE BLOCK");
builder.AppendLine("AS");
builder.AppendLine("BEGIN");
builder.IncrementIndent();
builder.Append("if (not exists(select 1 from rdb$generators where rdb$generator_name = '");
builder.Append(identitySequenceName);
builder.Append("')) then");
builder.AppendLine();
builder.AppendLine("begin");
builder.IncrementIndent();
builder.Append("execute statement 'create sequence ");
builder.Append(identitySequenceName);
builder.Append("'");
builder.Append(_sqlHelper.StatementTerminator);
builder.AppendLine();
builder.DecrementIndent();
builder.AppendLine("end");
builder.DecrementIndent();
builder.Append("END");
builder.EndCommand();
builder.Append("CREATE TRIGGER ");
builder.Append(_sqlHelper.DelimitIdentifier(CreateTriggerName(columnName, tableName)));
builder.Append(" ACTIVE BEFORE INSERT ON ");
builder.Append(_sqlHelper.DelimitIdentifier(tableName));
builder.AppendLine();
builder.AppendLine("AS");
builder.AppendLine("BEGIN");
builder.IncrementIndent();
builder.Append("if (new.");
builder.Append(_sqlHelper.DelimitIdentifier(columnName));
builder.Append(" is null) then");
builder.AppendLine();
builder.AppendLine("begin");
builder.IncrementIndent();
builder.Append("new.");
builder.Append(_sqlHelper.DelimitIdentifier(columnName));
builder.Append(" = next value for ");
builder.Append(identitySequenceName);
builder.Append(_sqlHelper.StatementTerminator);
builder.AppendLine();
builder.DecrementIndent();
builder.AppendLine("end");
builder.DecrementIndent();
builder.Append("END");
builder.EndCommand();
}
/// <summary>
/// 添加索引的相关定义。
/// </summary>
/// <param name="operation">操作实例。</param>
/// <param name="builder"><see cref="MigrationCommandListBuilder"/>实例。</param>
/// <param name="isPrimaryKey">是否为主键。</param>
protected override void IndexTraits(MigrationOperation operation, MigrationCommandListBuilder builder, bool isPrimaryKey = false)
{
var clustered = (operation[SqlServerAnnotationNames.Clustered] as bool?) ?? isPrimaryKey;
builder.Append(clustered ? "CLUSTERED " : "NONCLUSTERED ");
}
public override void SpecifyEngine(MigrationCommandListBuilder builder, IModel model)
{
builder.Append(" ENGINE = MergeTree()").AppendLine();
if (OrderBy != null)
{
builder.AppendLine($"ORDER BY ({OrderBy})");
}
if (PartitionBy != null)
{
builder.AppendLine($"$PARTITION BY ({PartitionBy})");
}
if (PrimaryKey != null)
{
builder.AppendLine($"PRIMARY KEY ({PrimaryKey})");
}
if (SampleBy != null)
{
builder.AppendLine($"SAMPLE BY ({SampleBy})");
}
if (Settings != null && !Settings.IsDefault)
{
builder.AppendLine("SETTINGS");
using (builder.Indent())
{
if (Settings.IndexGranularity != MergeTreeSettings.DefaultIndexGranularity)
{
builder.AppendLine("index_granularity = " + Settings.IndexGranularity);
}
if (Settings.IndexGranularityBytes != MergeTreeSettings.DefaultIndexGranularityBytes)
{
builder.AppendLine("index_granularity_bytes = " + Settings.IndexGranularityBytes);
}
if (Settings.MinIndexGranularityBytes != MergeTreeSettings.DefaultMinIndexGranularityBytes)
{
builder.AppendLine("min_index_granularity_bytes = " + Settings.MinIndexGranularityBytes);
}
if (Settings.EnableMixedGranularityParts != MergeTreeSettings.DefaultEnableMixedGranularityParts)
{
builder.AppendLine("enable_mixed_granularity_parts = " + Convert.ToInt32(Settings.EnableMixedGranularityParts));
}
if (Settings.UseMinimalisticPartHeaderInZookeeper != MergeTreeSettings.DefaultUseMinimalisticPartHeaderInZookeeper)
{
builder.AppendLine("use_minimalistic_part_header_in_zookeeper = " +
Convert.ToInt32(Settings.UseMinimalisticPartHeaderInZookeeper));
}
if (Settings.MinMergeBytesToUseDirectIo != MergeTreeSettings.DefaultMinMergeBytesToUseDirectIo)
{
builder.AppendLine("min_merge_bytes_to_use_direct_io = " + Settings.MinMergeBytesToUseDirectIo);
}
if (Settings.MergeWithTtlTimeout != MergeTreeSettings.DefaultMergeWithTtlTimeout)
{
builder.AppendLine("merge_with_ttl_timeout = " + (int)Settings.MergeWithTtlTimeout.TotalSeconds);
}
if (Settings.WriteFinalMark != MergeTreeSettings.DefaultWriteFinalMark)
{
builder.AppendLine("write_final_mark = " + Convert.ToInt32(Settings.WriteFinalMark));
}
if (Settings.MergeMaxBlockSize != MergeTreeSettings.DefaultMergeMaxBlockSize)
{
builder.AppendLine("merge_max_block_size = " + Settings.MergeMaxBlockSize);
}
if (!string.IsNullOrEmpty(Settings.StoragePolicy))
{
builder.AppendLine("storage_policy = " + Settings.StoragePolicy);
}
if (Settings.MinBytesForWidePart != null)
{
builder.AppendLine("min_bytes_for_wide_part = " + Settings.MinBytesForWidePart.Value);
}
if (Settings.MinRowsForWidePart != null)
{
builder.AppendLine("min_rows_for_wide_part = " + Settings.MinRowsForWidePart.Value);
}
if (Settings.MaxPartsInTotal != null)
{
builder.AppendLine("max_parts_in_total = " + Settings.MaxPartsInTotal.Value);
}
if (Settings.MaxCompressBlockSize != null)
{
builder.AppendLine("max_compress_block_size = " + Settings.MaxCompressBlockSize.Value);
}
if (Settings.MinCompressBlockSize != null)
{
builder.AppendLine("min_compress_block_size = " + Settings.MinCompressBlockSize.Value);
}
}
}
}
private void DropHistoryTable(MigrationCommandListBuilder builder)
{
builder.Append("DROP TABLE ")
.Append(Settings.HistoryTable)
.EndCommand(true);
}
/// <summary>
/// 添加索引的相关定义。
/// </summary>
/// <param name="clustered">是否聚合。</param>
/// <param name="builder"><see cref="MigrationCommandListBuilder"/>实例。</param>
protected override void IndexTraits(bool clustered, MigrationCommandListBuilder builder)
{
builder.Append(clustered ? "CLUSTERED " : "NONCLUSTERED ");
}
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));
}
protected override void Generate(
[NotNull] CreateTableOperation operation,
[CanBeNull] IModel model,
[NotNull] MigrationCommandListBuilder builder,
bool terminate = true)
{
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(")");
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);
if (terminate)
{
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
}
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(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);
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(';');
// NOT NULL
builder.Append(alterBase)
.Append(operation.IsNullable ? "DROP NOT NULL" : "SET NOT NULL")
.AppendLine(';');
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
{
Schema = operation.Schema,
Name = newSequenceName,
ClrType = operation.ClrType
}, model, builder);
builder.Append(alterBase).Append("SET");
DefaultValue(null, $@"nextval('{Dependencies.SqlGenerationHelper.DelimitIdentifier(newSequenceName, operation.Schema)}')", 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(';');
}
// 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, operation.Schema))
.Append(" OWNED BY ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append('.')
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
.AppendLine(';');
}
// 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(';');
}
EndStatement(builder);
}
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 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);
}
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(
AlterDatabaseOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
if (!IsMemoryOptimized(operation))
{
return;
}
builder.AppendLine("IF SERVERPROPERTY('IsXTPSupported') = 1 AND SERVERPROPERTY('EngineEdition') <> 5");
using (builder.Indent())
{
builder
.AppendLine("BEGIN")
.AppendLine("IF NOT EXISTS (");
using (builder.Indent())
{
builder
.Append("SELECT 1 FROM [sys].[filegroups] [FG] ")
.Append("JOIN [sys].[database_files] [F] ON [FG].[data_space_id] = [F].[data_space_id] ")
.AppendLine("WHERE [FG].[type] = N'FX' AND [F].[type] = 2)");
}
using (builder.Indent())
{
builder
.AppendLine("BEGIN")
.AppendLine("DECLARE @db_name NVARCHAR(MAX) = DB_NAME();")
.AppendLine("DECLARE @fg_name NVARCHAR(MAX);")
.AppendLine("SELECT TOP(1) @fg_name = [name] FROM [sys].[filegroups] WHERE [type] = N'FX';")
.AppendLine()
.AppendLine("IF @fg_name IS NULL");
using (builder.Indent())
{
builder
.AppendLine("BEGIN")
.AppendLine("SET @fg_name = @db_name + N'_MODFG';")
.AppendLine("EXEC(N'ALTER DATABASE CURRENT ADD FILEGROUP [' + @fg_name + '] CONTAINS MEMORY_OPTIMIZED_DATA;');")
.AppendLine("END");
}
builder
.AppendLine()
.AppendLine("DECLARE @path NVARCHAR(MAX);")
.Append("SELECT TOP(1) @path = [physical_name] FROM [sys].[database_files] ")
.AppendLine("WHERE charindex('\\', [physical_name]) > 0 ORDER BY [file_id];")
.AppendLine("IF (@path IS NULL)")
.IncrementIndent().AppendLine("SET @path = '\\' + @db_name;").DecrementIndent()
.AppendLine()
.AppendLine("DECLARE @filename NVARCHAR(MAX) = right(@path, charindex('\\', reverse(@path)) - 1);")
.AppendLine("SET @filename = REPLACE(left(@filename, len(@filename) - charindex('.', reverse(@filename))), '''', '''''') + N'_MOD';")
.AppendLine("DECLARE @new_path NVARCHAR(MAX) = REPLACE(CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(MAX)), '''', '''''') + @filename;")
.AppendLine()
.AppendLine("EXEC(N'");
using (builder.Indent())
{
builder
.AppendLine("ALTER DATABASE CURRENT")
.AppendLine("ADD FILE (NAME=''' + @filename + ''', filename=''' + @new_path + ''')")
.AppendLine("TO FILEGROUP [' + @fg_name + '];')");
}
builder.AppendLine("END");
}
builder.AppendLine("END");
}
builder.AppendLine()
.AppendLine("IF SERVERPROPERTY('IsXTPSupported') = 1")
.AppendLine("EXEC(N'");
using (builder.Indent())
{
builder
.AppendLine("ALTER DATABASE CURRENT")
.AppendLine("SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON;')");
}
builder.EndCommand(suppressTransaction: true);
}