protected override void Generate(
AlterColumnOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
DropDefaultConstraint(operation.Schema, operation.Table, operation.Name, builder);
builder
.Append("ALTER TABLE ")
.Append(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(SqlGenerationHelper.StatementTerminator);
if ((operation.DefaultValue != null)
|| (operation.DefaultValueSql != null))
{
builder
.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" ADD");
DefaultValue(operation.DefaultValue, operation.DefaultValueSql, builder);
builder
.Append(" FOR ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.AppendLine(SqlGenerationHelper.StatementTerminator);
}
EndStatement(builder);
}
/// <summary>
/// Builds commands for the given <see cref="AlterColumnOperation" />
/// by making calls on the given <see cref="MigrationCommandListBuilder" />.
/// </summary>
/// <param name="operation"> The operation. </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 build the commands. </param>
protected override void Generate(
AlterColumnOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" MODIFY COLUMN ");
ColumnDefinition(
operation.Schema,
operation.Table,
operation.Name,
operation,
model,
builder);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
builder.EndCommand();
}
private void DropIdentity(
AlterColumnOperation operation,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var commandText = $@"
DECLARE
v_Count INTEGER;
BEGIN
SELECT COUNT(*) INTO v_Count
FROM ALL_TAB_IDENTITY_COLS T
WHERE T.TABLE_NAME = N'{operation.Table}'
AND T.COLUMN_NAME = '{operation.Name}';
IF v_Count > 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ""{operation.Table}"" MODIFY ""{operation.Name}"" DROP IDENTITY';
END IF;
END;";
builder
.AppendLine(commandText)
.EndCommand();
}
protected override void Generate(
AlterColumnOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
IEnumerable <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();
return;
}
var narrowed = false;
if (IsOldColumnSupported(model))
{
var valueGenerationStrategy = operation[
OracleAnnotationNames.ValueGenerationStrategy] as OracleValueGenerationStrategy?;
var identity = valueGenerationStrategy == OracleValueGenerationStrategy.IdentityColumn;
var oldValueGenerationStrategy = operation.OldColumn[
OracleAnnotationNames.ValueGenerationStrategy] as OracleValueGenerationStrategy?;
var oldIdentity = oldValueGenerationStrategy == OracleValueGenerationStrategy.IdentityColumn;
if (identity != oldIdentity)
{
throw new InvalidOperationException(OracleStrings.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();
}
protected virtual void Generate(
[NotNull] UpdateDataOperation operation,
[CanBeNull] IModel model,
[NotNull] MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
if (operation.Values.Length == 0)
{
return;
}
var rowCount = operation.Values.GetLength(0);
var valueCount = operation.Values.GetLength(1);
var keyValueCount = operation.KeyValues.GetLength(1);
for (var i = 0; i < rowCount; i++)
{
builder
.Append("UPDATE ")
.AppendLine(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append("SET ");
for (var j = 0; j < valueCount; j++)
{
if (j != 0)
{
builder
.AppendLine(",")
.Append(" ");
}
var value = operation.Values[i, j];
var typeMapping = Dependencies.TypeMapper.GetMappingForValue(value);
builder.Append(
Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Columns[j]) +
" = " +
typeMapping.GenerateSqlLiteral(value));
}
builder
.AppendLine()
.Append("WHERE (");
for (var j = 0; j < keyValueCount; j++)
{
if (j != 0)
{
builder.Append(" AND ");
}
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.KeyColumns[j]));
var value = operation.KeyValues[i, j];
if (value == null)
{
builder.Append(" IS NULL");
}
else
{
var typeMapping = Dependencies.TypeMapper.GetMappingForValue(value);
builder
.Append(" = ")
.Append(typeMapping.GenerateSqlLiteral(value));
}
}
builder
.Append(")")
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
}
/// <summary>
/// Builds commands for the given <see cref="RenameColumnOperation" />
/// by making calls on the given <see cref="MigrationCommandListBuilder" />.
/// </summary>
/// <param name="operation"> The operation. </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 build the commands. </param>
protected override void Generate(
RenameColumnOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema));
if (_connectionInfo.ServerVersion.SupportsRenameColumn)
{
builder.Append(" RENAME COLUMN ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.NewName));
EndStatement(builder);
return;
}
builder.Append(" CHANGE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" ");
var property = FindProperty(model, operation.Schema, operation.Table, operation.NewName);
if (property == null)
{
var type = operation[RelationalAnnotationNames.ColumnType];
if (type == null)
{
throw new InvalidOperationException(
$"Could not find the property corresponding to the column: {Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema)}.{Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.NewName)}. Specify the column type explicitly on 'RenameColumn' using the \"{RelationalAnnotationNames.ColumnType}\" annotation");
}
builder
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.NewName))
.Append(" ")
.Append(type)
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
return;
}
var typeMapping = Dependencies.TypeMappingSource.GetMapping(property);
var converter = typeMapping.Converter;
var clrType = (converter?.ProviderClrType ?? typeMapping.ClrType).UnwrapNullableType();
var columnType = (string)(operation[RelationalAnnotationNames.ColumnType]
?? property[RelationalAnnotationNames.ColumnType]);
var isNullable = property.IsColumnNullable();
var defaultValue = property.GetDefaultValue();
defaultValue = converter != null
? converter.ConvertToProvider(defaultValue)
: defaultValue;
defaultValue = (defaultValue == DBNull.Value ? null : defaultValue)
?? (isNullable
? null
: clrType == typeof(string)
? string.Empty
: clrType.IsArray
? Array.CreateInstance(clrType.GetElementType(), 0)
: clrType.GetDefaultValue());
var isRowVersion = property.ClrType == typeof(byte[]) &&
property.IsConcurrencyToken &&
property.ValueGenerated == ValueGenerated.OnAddOrUpdate;
var addColumnOperation = new AddColumnOperation
{
Schema = operation.Schema,
Table = operation.Table,
Name = operation.NewName,
ClrType = clrType,
ColumnType = columnType,
IsUnicode = property.IsUnicode(),
MaxLength = property.GetMaxLength(),
IsFixedLength = property.IsFixedLength(),
IsRowVersion = isRowVersion,
IsNullable = isNullable,
DefaultValue = defaultValue,
DefaultValueSql = property.GetDefaultValueSql(),
ComputedColumnSql = property.GetComputedColumnSql(),
};
ColumnDefinition(
addColumnOperation,
model,
builder);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
protected override void Generate(AlterColumnOperation operation, [CanBeNull] IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var type = operation.ColumnType ?? GetColumnType(operation.Schema, operation.Table, operation.Name, operation.ClrType, null, operation.MaxLength, false, model);
var generatedOnAddAnnotation = operation[NpgsqlAnnotationNames.Prefix + NpgsqlAnnotationNames.ValueGeneratedOnAdd];
var generatedOnAdd = generatedOnAddAnnotation != null && (bool)generatedOnAddAnnotation;
string sequenceName = null;
var defaultValueSql = operation.DefaultValueSql;
if (generatedOnAdd && operation.DefaultValue == null && operation.DefaultValueSql == null)
{
switch (type)
{
case "int":
case "int4":
case "bigint":
case "int8":
case "smallint":
case "int2":
sequenceName = $"{operation.Table}_{operation.Name}_seq";
Generate(new CreateSequenceOperation
{
Name = sequenceName,
ClrType = typeof(long)
}, model, builder, false);
defaultValueSql = $@"nextval({SqlGenerationHelper.DelimitIdentifier(sequenceName)})";
// Note: we also need to set the sequence ownership, this is done below
// after the ALTER COLUMN
break;
case "uuid":
defaultValueSql = "uuid_generate_v4()";
break;
}
}
var identifier = SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema);
var alterBase = $"ALTER TABLE {identifier} ALTER COLUMN {SqlGenerationHelper.DelimitIdentifier(operation.Name)} ";
// TYPE
builder.Append(alterBase)
.Append("TYPE ")
.Append(type)
.AppendLine(SqlGenerationHelper.StatementTerminator);
// NOT NULL
builder.Append(alterBase)
.Append(operation.IsNullable ? "DROP NOT NULL" : "SET NOT NULL")
.AppendLine(SqlGenerationHelper.StatementTerminator);
// DEFAULT
builder.Append(alterBase);
if (operation.DefaultValue != null || defaultValueSql != null)
{
builder.Append("SET");
DefaultValue(operation.DefaultValue, defaultValueSql, builder);
}
else
{
builder.Append("DROP DEFAULT");
}
// ALTER SEQUENCE
if (sequenceName != null)
{
// Terminate the DEFAULT above
builder.AppendLine(SqlGenerationHelper.StatementTerminator);
builder
.Append("ALTER SEQUENCE ")
.Append(SqlGenerationHelper.DelimitIdentifier(sequenceName))
.Append(" OWNED BY ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table))
.Append('.')
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name));
}
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
var comment = operation[NpgsqlAnnotationNames.Comment] as string;
if (comment != null)
{
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
var stringTypeMapping = Dependencies.TypeMapper.GetMapping(typeof(string));
builder
.Append("COMMENT ON TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.Append(" IS ")
.Append(stringTypeMapping.GenerateSqlLiteral(comment));
}
// Comments on the columns
foreach (var columnOp in operation.Columns.Where(c => c[NpgsqlAnnotationNames.Comment] != null))
{
var columnComment = columnOp[NpgsqlAnnotationNames.Comment];
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
var stringTypeMapping = Dependencies.TypeMapper.GetMapping(typeof(string));
builder
.Append("COMMENT ON COLUMN ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.Append('.')
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(columnOp.Name))
.Append(" IS ")
.Append(stringTypeMapping.GenerateSqlLiteral(columnComment));
}
if (terminate)
{
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
}
protected override void Generate([NotNull] DropPrimaryKeyOperation operation, [CanBeNull] IModel model, [NotNull] MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder.Append(@"DROP PROCEDURE IF EXISTS POMELO_BEFORE_DROP_PRIMARY_KEY;
CREATE PROCEDURE POMELO_BEFORE_DROP_PRIMARY_KEY(IN `SCHEMA_NAME_ARGUMENT` VARCHAR(255), IN `TABLE_NAME_ARGUMENT` VARCHAR(255))
BEGIN
DECLARE HAS_AUTO_INCREMENT_ID TINYINT(1);
DECLARE PRIMARY_KEY_COLUMN_NAME VARCHAR(255);
DECLARE PRIMARY_KEY_TYPE VARCHAR(255);
DECLARE SQL_EXP VARCHAR(1000);
SELECT COUNT(*)
INTO HAS_AUTO_INCREMENT_ID
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
AND `Extra` = 'auto_increment'
AND `COLUMN_KEY` = 'PRI'
LIMIT 1;
IF HAS_AUTO_INCREMENT_ID THEN
SELECT `COLUMN_TYPE`
INTO PRIMARY_KEY_TYPE
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
AND `COLUMN_KEY` = 'PRI'
LIMIT 1;
SELECT `COLUMN_NAME`
INTO PRIMARY_KEY_COLUMN_NAME
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
AND `COLUMN_KEY` = 'PRI'
LIMIT 1;
SET SQL_EXP = CONCAT('ALTER TABLE `', (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA())), '`.`', TABLE_NAME_ARGUMENT, '` MODIFY COLUMN `', PRIMARY_KEY_COLUMN_NAME, '` ', PRIMARY_KEY_TYPE, ' NOT NULL;');
SET @SQL_EXP = SQL_EXP;
PREPARE SQL_EXP_EXECUTE FROM @SQL_EXP;
EXECUTE SQL_EXP_EXECUTE;
DEALLOCATE PREPARE SQL_EXP_EXECUTE;
END IF;
END;");
builder.AppendLine();
if (String.IsNullOrWhiteSpace(operation.Schema))
{
builder.Append($"CALL POMELO_BEFORE_DROP_PRIMARY_KEY(NULL, '{ operation.Table }');");
}
else
{
builder.Append($"CALL POMELO_BEFORE_DROP_PRIMARY_KEY('{ operation.Schema }', '{ operation.Table }');");
}
builder.AppendLine();
builder.Append($"DROP PROCEDURE IF EXISTS POMELO_BEFORE_DROP_PRIMARY_KEY;");
builder.AppendLine();
builder
.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" DROP PRIMARY KEY;")
.AppendLine();
EndStatement(builder);
}
protected override void Generate(
CreateIndexOperation operation,
IModel model,
MigrationCommandListBuilder builder,
bool terminate)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var nullableColumns = operation.Columns
.Where(
c =>
{
var property = FindProperty(model, operation.Schema, operation.Table, c);
return(property == null || // Couldn't bind column to property
property.IsColumnNullable());
})
.ToList();
var memoryOptimized = IsMemoryOptimized(operation);
if (memoryOptimized)
{
builder.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table))
.Append(" ADD INDEX ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" ");
if (operation.IsUnique &&
nullableColumns.Count == 0)
{
builder.Append("UNIQUE ");
}
IndexTraits(operation, model, builder);
builder
.Append("(")
.Append(ColumnList(operation.Columns))
.Append(")");
}
else
{
base.Generate(operation, model, builder, terminate: false);
var clustered = operation[SqlServerFullAnnotationNames.Instance.Clustered] as bool?;
if (operation.IsUnique &&
(clustered != true) &&
nullableColumns.Count != 0)
{
builder.Append(" WHERE ");
for (var i = 0; i < nullableColumns.Count; i++)
{
if (i != 0)
{
builder.Append(" AND ");
}
builder
.Append(SqlGenerationHelper.DelimitIdentifier(nullableColumns[i]))
.Append(" IS NOT NULL");
}
}
}
if (terminate)
{
builder
.AppendLine(SqlGenerationHelper.StatementTerminator)
.EndCommand(suppressTransaction: memoryOptimized);
}
}
protected override void Generate(EnsureSchemaOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.AppendLine($"CREATE OR REPLACE FUNCTION MyCat_ef_ensure_schema() RETURNS VOID")
.AppendLine($"BEGIN")
.AppendLine($" IF NOT EXISTS(SELECT 1 FROM `information_schema`.`SCHEMATA` where `SCHEMA_NAME` = '{ operation.Name }')")
.AppendLine($" THEN")
.AppendLine($" CREATE SCHEMA { operation.Name };")
.AppendLine($" END IF;")
.AppendLine($"END")
.AppendLine(SqlGenerationHelper.BatchTerminator);
}
protected override void Generate(RenameTableOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var separate = false;
var name = operation.Name;
if (operation.NewName != null)
{
Rename(operation.Schema, operation.Name, operation.NewName, "TABLE", builder);
separate = true;
name = operation.NewName;
}
if (operation.NewSchema != null)
{
if (separate)
{
builder.AppendLine(SqlGenerationHelper.BatchTerminator);
}
Transfer(operation.NewSchema, operation.Schema, name, "TABLE", builder);
}
}
/// <summary>
/// Builds commands for the given <see cref="AlterColumnOperation" />
/// by making calls on the given <see cref="MigrationCommandListBuilder" />.
/// </summary>
/// <param name="operation"> The operation. </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 build the commands. </param>
protected override void Generate(
AlterColumnOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
IEnumerable <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,
IsFixedLength = operation.IsFixedLength
};
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();
return;
}
var narrowed = false;
if (IsOldColumnSupported(model))
{
if (IsIdentity(operation) != IsIdentity(operation.OldColumn))
{
throw new InvalidOperationException(JetStrings.AlterIdentityColumn);
}
var type = operation.ColumnType
?? GetColumnType(
operation.Schema,
operation.Table,
operation.Name,
operation,
model);
var oldType = operation.OldColumn.ColumnType
?? GetColumnType(
operation.Schema,
operation.Table,
operation.Name,
operation.OldColumn,
model);
narrowed = type != oldType || !operation.IsNullable && operation.OldColumn.IsNullable;
}
if (narrowed)
{
indexesToRebuild = GetIndexesToRebuild(property, operation)
.ToList();
DropIndexes(indexesToRebuild, builder);
}
DropDefaultConstraint(operation.Table, operation.Name, builder);
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table))
.Append(" ALTER COLUMN ");
// NB: DefaultValue, DefaultValueSql, and identity are handled elsewhere. Don't copy them here.
var definitionOperation = new AlterColumnOperation
{
Schema = operation.Schema,
Table = operation.Table,
Name = operation.Name,
ClrType = operation.ClrType,
ColumnType = operation.ColumnType,
IsUnicode = operation.IsUnicode,
IsFixedLength = operation.IsFixedLength,
MaxLength = operation.MaxLength,
IsRowVersion = operation.IsRowVersion,
IsNullable = operation.IsNullable,
ComputedColumnSql = operation.ComputedColumnSql,
OldColumn = operation.OldColumn
};
definitionOperation.AddAnnotations(
operation.GetAnnotations()
.Where(
a => a.Name != JetAnnotationNames.ValueGenerationStrategy &&
a.Name != JetAnnotationNames.Identity));
ColumnDefinition(
operation.Schema,
operation.Table,
operation.Name,
definitionOperation,
model,
builder);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
if (operation.DefaultValue != null ||
operation.DefaultValueSql != null)
{
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table))
.Append(" ADD");
DefaultValue(operation.DefaultValue, operation.DefaultValueSql, operation.ColumnType, builder);
builder
.Append(" FOR ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
}
// TODO: Implement comment/description support. (ADOX/DAO)
/*
* if (operation.OldColumn.Comment != operation.Comment)
* {
* var dropDescription = operation.OldColumn.Comment != null;
* if (dropDescription)
* {
* DropDescription(
* builder,
* operation.Schema,
* operation.Table,
* operation.Name);
* }
*
* if (operation.Comment != null)
* {
* AddDescription(
* builder, operation.Comment,
* operation.Schema,
* operation.Table,
* operation.Name,
* omitSchemaVariable: dropDescription);
* }
* }
*/
if (narrowed)
{
CreateIndexes(indexesToRebuild, builder);
}
builder.EndCommand();
}
protected override void Generate(CreateIndexOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
base.Generate(operation, model, builder, terminate: false);
var clustered = operation[SqlServerFullAnnotationNames.Instance.Clustered] as bool?;
if (operation.IsUnique
&& (clustered != true))
{
builder.Append(" WHERE ");
for (var i = 0; i < operation.Columns.Length; i++)
{
if (i != 0)
{
builder.Append(" AND ");
}
builder
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Columns[i]))
.Append(" IS NOT NULL");
}
}
builder.AppendLine(SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
protected override void Generate(
AlterDatabaseOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
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] AddPrimaryKeyOperation operation, [CanBeNull] IModel model, [NotNull] MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" ADD ");
PrimaryKeyConstraint(operation, model, builder);
builder.AppendLine(SqlGenerationHelper.StatementTerminator);
var annotations = model.GetAnnotations();
if (operation.Columns.Count() == 1)
{
builder.Append(@"DROP PROCEDURE IF EXISTS POMELO_AFTER_ADD_PRIMARY_KEY;
CREATE PROCEDURE POMELO_AFTER_ADD_PRIMARY_KEY(IN `SCHEMA_NAME_ARGUMENT` VARCHAR(255), IN `TABLE_NAME_ARGUMENT` VARCHAR(255), IN `COLUMN_NAME_ARGUMENT` VARCHAR(255))
BEGIN
DECLARE HAS_AUTO_INCREMENT_ID INT(11);
DECLARE PRIMARY_KEY_COLUMN_NAME VARCHAR(255);
DECLARE PRIMARY_KEY_TYPE VARCHAR(255);
DECLARE SQL_EXP VARCHAR(1000);
SELECT COUNT(*)
INTO HAS_AUTO_INCREMENT_ID
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
AND `COLUMN_NAME` = COLUMN_NAME_ARGUMENT
AND `COLUMN_TYPE` LIKE '%int%'
AND `COLUMN_KEY` = 'PRI';
IF HAS_AUTO_INCREMENT_ID THEN
SELECT `COLUMN_TYPE`
INTO PRIMARY_KEY_TYPE
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
AND `COLUMN_NAME` = COLUMN_NAME_ARGUMENT
AND `COLUMN_TYPE` LIKE '%int%'
AND `COLUMN_KEY` = 'PRI';
SELECT `COLUMN_NAME`
INTO PRIMARY_KEY_COLUMN_NAME
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
AND `COLUMN_NAME` = COLUMN_NAME_ARGUMENT
AND `COLUMN_TYPE` LIKE '%int%'
AND `COLUMN_KEY` = 'PRI';
SET SQL_EXP = CONCAT('ALTER TABLE `', (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA())), '`.`', TABLE_NAME_ARGUMENT, '` MODIFY COLUMN `', PRIMARY_KEY_COLUMN_NAME, '` ', PRIMARY_KEY_TYPE, ' NOT NULL AUTO_INCREMENT;');
SET @SQL_EXP = SQL_EXP;
PREPARE SQL_EXP_EXECUTE FROM @SQL_EXP;
EXECUTE SQL_EXP_EXECUTE;
DEALLOCATE PREPARE SQL_EXP_EXECUTE;
END IF;
END;");
builder.AppendLine();
if (operation.Schema == null)
{
builder.Append($"CALL POMELO_AFTER_ADD_PRIMARY_KEY(NULL, '{ operation.Table }', '{ operation.Columns.First() }');");
}
else
{
builder.Append($"CALL POMELO_AFTER_ADD_PRIMARY_KEY('{ operation.Schema }', '{ operation.Table }', '{ operation.Columns.First() }');");
}
builder.AppendLine();
builder.Append($"DROP PROCEDURE IF EXISTS POMELO_AFTER_ADD_PRIMARY_KEY;");
builder.AppendLine();
}
EndStatement(builder);
}
protected override void Generate(AlterTableOperation operation, IModel model, MigrationCommandListBuilder builder)
{
var madeChanges = false;
// Storage parameters
var oldStorageParameters = GetStorageParameters(operation.OldTable);
var newStorageParameters = GetStorageParameters(operation);
var newOrChanged = newStorageParameters.Where(p =>
!oldStorageParameters.ContainsKey(p.Key) ||
oldStorageParameters[p.Key] != p.Value
).ToList();
if (newOrChanged.Count > 0)
{
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema));
builder
.Append(" SET (")
.Append(string.Join(", ", newOrChanged.Select(p => $"{p.Key}={p.Value}")))
.Append(")");
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
madeChanges = true;
}
var removed = oldStorageParameters
.Select(p => p.Key)
.Where(pn => !newStorageParameters.ContainsKey(pn))
.ToList();
if (removed.Count > 0)
{
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema));
builder
.Append(" RESET (")
.Append(string.Join(", ", removed))
.Append(")");
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
madeChanges = true;
}
// Comment
var oldComment = operation.OldTable[NpgsqlAnnotationNames.Comment] as string;
var newComment = operation[NpgsqlAnnotationNames.Comment] as string;
if (oldComment != newComment)
{
var stringTypeMapping = Dependencies.TypeMapper.GetMapping(typeof(string));
builder
.Append("COMMENT ON TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.Append(" IS ")
.Append(stringTypeMapping.GenerateSqlLiteral(newComment));
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
madeChanges = true;
}
if (madeChanges)
{
EndStatement(builder);
}
}
protected override void Generate(
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));
// 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 sequenceName = null;
var defaultValueSql = operation.DefaultValueSql;
CheckForOldAnnotation(operation);
var valueGenerationStrategy = operation[NpgsqlAnnotationNames.ValueGenerationStrategy] as NpgsqlValueGenerationStrategy?;
if (valueGenerationStrategy == NpgsqlValueGenerationStrategy.SerialColumn)
{
switch (type)
{
case "int":
case "int4":
case "bigint":
case "int8":
case "smallint":
case "int2":
sequenceName = $"{operation.Table}_{operation.Name}_seq";
Generate(new CreateSequenceOperation
{
Name = sequenceName,
ClrType = typeof(long)
}, model, builder, false);
defaultValueSql = $@"nextval('{Dependencies.SqlGenerationHelper.DelimitIdentifier(sequenceName)}')";
// Note: we also need to set the sequence ownership, this is done below
// after the ALTER COLUMN
break;
}
}
var identifier = Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema);
var alterBase = $"ALTER TABLE {identifier} ALTER COLUMN {Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name)} ";
// TYPE
builder.Append(alterBase)
.Append("TYPE ")
.Append(type)
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
// NOT NULL
builder.Append(alterBase)
.Append(operation.IsNullable ? "DROP NOT NULL" : "SET NOT NULL")
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
// DEFAULT
builder.Append(alterBase);
if (operation.DefaultValue != null || defaultValueSql != null)
{
builder.Append("SET");
DefaultValue(operation.DefaultValue, defaultValueSql, builder);
}
else
{
builder.Append("DROP DEFAULT");
}
// Terminate the DEFAULT above
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
// ALTER SEQUENCE
if (sequenceName != null)
{
builder
.Append("ALTER SEQUENCE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(sequenceName))
.Append(" OWNED BY ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table))
.Append('.')
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name));
}
// Comment
var oldComment = operation.OldColumn[NpgsqlAnnotationNames.Comment] as string;
var newComment = operation[NpgsqlAnnotationNames.Comment] as string;
if (oldComment != newComment)
{
var stringTypeMapping = Dependencies.TypeMapper.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));
}
EndStatement(builder);
}