protected virtual void CreateIndexes(
[NotNull] IEnumerable <IIndex> indexes,
[NotNull] MigrationCommandListBuilder builder)
{
Check.NotNull(indexes, nameof(indexes));
Check.NotNull(builder, nameof(builder));
foreach (var index in indexes)
{
var operation = new CreateIndexOperation
{
IsUnique = index.IsUnique,
Name = Dependencies.Annotations.For(index).Name,
Schema = Dependencies.Annotations.For(index.DeclaringEntityType).Schema,
Table = Dependencies.Annotations.For(index.DeclaringEntityType).TableName,
Columns = index.Properties.Select(p => Dependencies.Annotations.For(p).ColumnName).ToArray(),
Filter = Dependencies.Annotations.For(index).Filter
};
operation.AddAnnotations(_migrationsAnnotations.For(index));
Generate(operation, index.DeclaringEntityType.Model, builder, terminate: false);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
}
}
public override void SpecifyEngine(MigrationCommandListBuilder builder, IModel model)
{
builder.AppendLine(" ENGINE = StripeLog;");
}
protected override void Generate(AlterColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
{
try
{
if (m_oracleLogger != null && m_oracleLogger.Logger != null && m_oracleLogger.Logger.IsEnabled(LogLevel.Trace))
{
Trace <DbLoggerCategory.Migrations> .Write(m_oracleLogger, LogLevel.Trace, OracleTraceTag.Entry, OracleTraceClassName.OracleMigrationsSqlGenerator, OracleTraceFuncName.Generate, "AlterColumnOperation");
}
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
FindProperty(model, operation.Schema, operation.Table, operation.Name);
if (operation.ComputedColumnSql != null)
{
DropColumnOperation operation2 = new DropColumnOperation
{
Schema = operation.Schema,
Table = operation.Table,
Name = operation.Name,
};
AddColumnOperation 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,
IsFixedLength = operation.IsFixedLength,
};
addColumnOperation.AddAnnotations(operation.GetAnnotations());
Generate(operation2, model, builder);
Generate(addColumnOperation, model, builder);
return;
}
bool flag = operation["Oracle:ValueGenerationStrategy"] as OracleValueGenerationStrategy? == OracleValueGenerationStrategy.IdentityColumn;
if (IsOldColumnSupported(model))
{
if (operation.OldColumn["Oracle:ValueGenerationStrategy"] as OracleValueGenerationStrategy? == OracleValueGenerationStrategy.IdentityColumn && !flag)
{
if (_oracleSQLCompatibility == "11")
{
DropIdentityForDB11(operation, builder);
}
else
{
DropIdentity(operation, builder);
}
}
if (operation.OldColumn.DefaultValue != null || (operation.OldColumn.DefaultValueSql != null && (operation.DefaultValue == null || operation.DefaultValueSql == null)))
{
DropDefaultConstraint(operation.Schema, operation.Table, operation.Name, builder);
}
}
else
{
if (!flag)
{
if (_oracleSQLCompatibility == "11")
{
DropIdentityForDB11(operation, builder);
}
else
{
DropIdentity(operation, builder);
}
}
if (operation.DefaultValue == null && operation.DefaultValueSql == null)
{
DropDefaultConstraint(operation.Schema, operation.Table, operation.Name, builder);
}
}
builder.AppendLine("declare");
if (operation.Schema != null)
{
builder.AppendLine(" l_nullable all_tab_columns.nullable % type" + Dependencies.SqlGenerationHelper.StatementTerminator);
}
else
{
builder.AppendLine(" l_nullable user_tab_columns.nullable % type" + Dependencies.SqlGenerationHelper.StatementTerminator);
}
builder.AppendLine("begin ").AppendLine(" select nullable into l_nullable ");
if (operation.Schema != null)
{
builder.AppendLine(" from all_tab_columns ");
}
else
{
builder.AppendLine(" from user_tab_columns ");
}
builder.AppendLine(" where table_name = '" + operation.Table + "' ").AppendLine(" and column_name = '" + operation.Name + "' ");
if (operation.Schema != null)
{
builder.AppendLine(" and owner = '" + operation.Schema + "'" + Dependencies.SqlGenerationHelper.StatementTerminator + " ");
}
else
{
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator ?? "");
}
builder.AppendLine(" if l_nullable = 'N' then ");
builder.Append(" EXECUTE IMMEDIATE 'ALTER TABLE ").Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema)).Append(" MODIFY ");
if (operation.IsNullable)
{
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, flag, operation, model, builder);
builder.AppendLine(" NULL'" + Dependencies.SqlGenerationHelper.StatementTerminator);
}
else
{
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, flag, operation, model, builder, addNotNullKeyword: false);
builder.AppendLine(" '" + Dependencies.SqlGenerationHelper.StatementTerminator);
}
builder.AppendLine(" else ");
builder.Append(" EXECUTE IMMEDIATE 'ALTER TABLE ").Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema)).Append(" MODIFY ");
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, flag, operation, model, builder);
builder.AppendLine("'" + Dependencies.SqlGenerationHelper.StatementTerminator).AppendLine(" end if" + Dependencies.SqlGenerationHelper.StatementTerminator).AppendLine("end" + Dependencies.SqlGenerationHelper.StatementTerminator);
builder.EndCommand();
}
catch (Exception ex)
{
if (m_oracleLogger != null && m_oracleLogger.Logger != null && m_oracleLogger.Logger.IsEnabled(LogLevel.Error))
{
Trace <DbLoggerCategory.Migrations> .Write(m_oracleLogger, LogLevel.Error, OracleTraceTag.Error, OracleTraceClassName.OracleMigrationsSqlGenerator, OracleTraceFuncName.Generate, ex.ToString());
}
throw;
}
finally
{
if (m_oracleLogger != null && m_oracleLogger.Logger != null && m_oracleLogger.Logger.IsEnabled(LogLevel.Trace))
{
Trace <DbLoggerCategory.Migrations> .Write(m_oracleLogger, LogLevel.Trace, OracleTraceTag.Exit, OracleTraceClassName.OracleMigrationsSqlGenerator, OracleTraceFuncName.Generate, "AlterColumnOperation");
}
}
}
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);
}
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));
}
var systemVersioned = !string.IsNullOrEmpty(operation[DotNetExtensionsAnnotationNames.SystemVersioned] as string);
var 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++)
{
var 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 (var uniqueConstraint in operation.UniqueConstraints)
{
builder.AppendLine(",");
UniqueConstraint(uniqueConstraint, model, builder);
}
foreach (var 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 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 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
{
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(';');
}
// 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(';');
}
// 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 virtual void TerminateStatement(MigrationCommandListBuilder builder)
{
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
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();
}
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);
}
}
}
}
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();
}
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);
}
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 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();
}
}
public override MigrationCommandListBuilder AppendLine()
{
_builder.AppendLine();
return(this);
}
protected override void Generate(
AlterColumnOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
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
};
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,
IsFixedLength = operation.IsFixedLength
};
addColumnOperation.AddAnnotations(operation.GetAnnotations());
Generate(dropColumnOperation, model, builder);
Generate(addColumnOperation, model, builder);
return;
}
var valueGenerationStrategy = operation[
OracleAnnotationNames.ValueGenerationStrategy] as OracleValueGenerationStrategy?;
var identity = valueGenerationStrategy == OracleValueGenerationStrategy.IdentityColumn;
if (IsOldColumnSupported(model))
{
var oldValueGenerationStrategy = operation.OldColumn[
OracleAnnotationNames.ValueGenerationStrategy] as OracleValueGenerationStrategy?;
var oldIdentity = oldValueGenerationStrategy == OracleValueGenerationStrategy.IdentityColumn;
if (oldIdentity &&
!identity)
{
DropIdentity(operation, builder);
}
if (operation.OldColumn.DefaultValue != null ||
operation.OldColumn.DefaultValueSql != null &&
(operation.DefaultValue == null ||
operation.DefaultValueSql == null))
{
DropDefaultConstraint(operation.Schema, operation.Table, operation.Name, builder);
}
}
else
{
if (!identity)
{
DropIdentity(operation, builder);
}
if (operation.DefaultValue == null &&
operation.DefaultValueSql == null)
{
DropDefaultConstraint(operation.Schema, operation.Table, operation.Name, builder);
}
}
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" MODIFY ");
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,
identity,
operation,
model,
builder);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
builder.EndCommand();
}
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);
if (operation.DefaultValue != null ||
operation.DefaultValueSql != null)
{
DefaultValue(operation.DefaultValue, operation.DefaultValueSql, builder);
}
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
if (narrowed)
{
CreateIndexes(indexesToRebuild, builder);
}
builder.EndCommand(suppressTransaction: IsMemoryOptimized(operation, model, operation.Schema, operation.Table));
}
/// <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();
}
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(
[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 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 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);
}
}
}
