//TODO : Find way to insert transaction only when generating script
/*protected override void Generate(SqlOperation sqlOperation)
* {
* if (sqlOperation.SuppressTransaction)
* {
* throw new ArgumentException(nameof(sqlOperation), "Migration with transaction doesn't work with SqlOperation that suppress transaction.");
* }
* Statement("GO");
*
* base.Generate(sqlOperation);
*
* Statement("GO");
* }
*
* public override IEnumerable<MigrationStatement> Generate(IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken)
* {
* yield return new MigrationStatement { Sql = "BEGIN TRAN" };
*
* foreach (var migrationStatement in base.Generate(migrationOperations, providerManifestToken))
* {
* yield return migrationStatement;
* }
*
* yield return new MigrationStatement { Sql = "COMMIT TRAN" };
* }*/
protected override void Generate(AddColumnOperation addColumnOperation)
{
SetAnnotatedColumn(addColumnOperation.Column, addColumnOperation.Table);
base.Generate(addColumnOperation);
}
protected override void Generate(AddColumnOperation addColumnOperation)
{
SetSqlFuncColumn(addColumnOperation.Column);
base.Generate(addColumnOperation);
}
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(suppressTransaction: IsMemoryOptimized(operation));
return;
}
var narrowed = false;
if (IsOldColumnSupported(model))
{
var valueGenerationStrategy = operation[
SqlServerFullAnnotationNames.Instance.ValueGenerationStrategy] as SqlServerValueGenerationStrategy?;
var identity = valueGenerationStrategy == SqlServerValueGenerationStrategy.IdentityColumn;
var oldValueGenerationStrategy = operation.OldColumn[
SqlServerFullAnnotationNames.Instance.ValueGenerationStrategy] as SqlServerValueGenerationStrategy?;
var oldIdentity = oldValueGenerationStrategy == SqlServerValueGenerationStrategy.IdentityColumn;
if (identity != oldIdentity)
{
throw new InvalidOperationException(SqlServerStrings.AlterIdentityColumn);
}
var type = operation.ColumnType
?? GetColumnType(
operation.Schema,
operation.Table,
operation.Name,
operation.ClrType,
operation.IsUnicode,
operation.MaxLength,
operation.IsRowVersion,
model);
var oldType = operation.OldColumn.ColumnType
?? GetColumnType(
operation.Schema,
operation.Table,
operation.Name,
operation.OldColumn.ClrType,
operation.OldColumn.IsUnicode,
operation.OldColumn.MaxLength,
operation.OldColumn.IsRowVersion,
model);
narrowed = type != oldType || !operation.IsNullable && operation.OldColumn.IsNullable;
}
if (narrowed)
{
indexesToRebuild = GetIndexesToRebuild(property, operation).ToList();
DropIndexes(indexesToRebuild, builder);
}
DropDefaultConstraint(operation.Schema, operation.Table, operation.Name, builder);
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" ALTER COLUMN ");
ColumnDefinition(
operation.Schema,
operation.Table,
operation.Name,
operation.ClrType,
operation.ColumnType,
operation.IsUnicode,
operation.MaxLength,
operation.IsRowVersion,
operation.IsNullable,
/*defaultValue:*/ null,
/*defaultValueSql:*/ null,
operation.ComputedColumnSql,
/*identity:*/ false,
operation,
model,
builder);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
if (operation.DefaultValue != null ||
operation.DefaultValueSql != null)
{
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" ADD");
DefaultValue(operation.DefaultValue, operation.DefaultValueSql, builder);
builder
.Append(" FOR ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
}
if (narrowed)
{
CreateIndexes(indexesToRebuild, builder);
}
builder.EndCommand(suppressTransaction: IsMemoryOptimized(operation));
}
protected override void Generate(
AddColumnOperation operation,
IModel model,
MigrationCommandListBuilder builder)
=> Generate(operation, model, builder, terminate: true);
protected override void Generate(AddColumnOperation operation, IModel model, MigrationCommandListBuilder builder) => base.Generate(operation, model, builder);
/// <summary>
/// 修改列。
/// </summary>
/// <param name="operation">操作实例。</param>
/// <param name="builder"><see cref="MigrationCommandListBuilder"/>实例对象。</param>
protected override void Generate(
AlterColumnOperation operation,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
if (operation.ComputedColumnSql != null)
{
var dropColumnOperation = new DropColumnOperation
{
Table = operation.Table,
Name = operation.Name
};
var addColumnOperation = new AddColumnOperation
{
Table = operation.Table,
Name = operation.Name,
ClrType = operation.ClrType,
ColumnType = operation.ColumnType,
IsUnicode = operation.IsUnicode,
MaxLength = operation.MaxLength,
IsRowVersion = operation.IsRowVersion,
IsNullable = operation.IsNullable,
DefaultValue = operation.DefaultValue,
DefaultValueSql = operation.DefaultValueSql,
ComputedColumnSql = operation.ComputedColumnSql
};
Generate(dropColumnOperation, builder, false);
builder.AppendLine(SqlHelper.StatementTerminator);
Generate(addColumnOperation, builder, false);
builder.AppendLine(SqlHelper.StatementTerminator);
builder.EndCommand();
return;
}
DropDefaultConstraint(operation.Table, operation.Name, builder);
builder
.Append("ALTER TABLE ")
.Append(operation.Table)
.Append(" ALTER COLUMN ");
ColumnDefinition(
operation.Table,
operation.Name,
operation.ClrType,
operation.ColumnType,
operation.IsUnicode,
operation.MaxLength,
operation.IsRowVersion,
operation.IsIdentity,
operation.IsNullable,
/*defaultValue:*/ null,
/*defaultValueSql:*/ null,
operation.ComputedColumnSql,
operation,
builder);
builder.AppendLine(SqlHelper.StatementTerminator);
if ((operation.DefaultValue != null) ||
(operation.DefaultValueSql != null))
{
builder
.Append("ALTER TABLE ")
.Append(operation.Table)
.Append(" ADD");
DefaultValue(operation.DefaultValue, operation.DefaultValueSql, builder);
builder
.Append(" FOR ")
.Append(SqlHelper.DelimitIdentifier(operation.Name))
.AppendLine(SqlHelper.StatementTerminator);
}
builder.EndCommand();
}
public void CreateTableOperation_with_annotations(bool autoincrement, string pkName)
{
var addIdColumn = new AddColumnOperation
{
Name = "Id",
ClrType = typeof(long),
ColumnType = "INTEGER",
IsNullable = false,
};
if (autoincrement)
{
addIdColumn.AddAnnotation(SqliteAnnotationNames.Prefix + SqliteAnnotationNames.Autoincrement, true);
}
Generate(
new CreateTableOperation
{
Name = "People",
Columns =
{
addIdColumn,
new AddColumnOperation
{
Name = "EmployerId",
ClrType = typeof(int),
ColumnType = "int",
IsNullable = true
},
new AddColumnOperation
{
Name = "SSN",
ClrType = typeof(string),
ColumnType = "char(11)",
IsNullable = true
}
},
PrimaryKey = new AddPrimaryKeyOperation
{
Name = pkName,
Columns = new[] { "Id" }
},
UniqueConstraints =
{
new AddUniqueConstraintOperation
{
Columns = new[]{ "SSN" }
}
},
ForeignKeys =
{
new AddForeignKeyOperation
{
Columns = new[] { "EmployerId" },
PrincipalTable = "Companies",
PrincipalColumns = new[]{ "Id" }
}
}
});
Assert.Equal(
"CREATE TABLE \"People\" (" + EOL +
" \"Id\" INTEGER NOT NULL" +
(pkName != null ? $" CONSTRAINT \"{pkName}\"" : "")
+ " PRIMARY KEY" +
(autoincrement ? " AUTOINCREMENT," : ",") + EOL +
" \"EmployerId\" int," + EOL +
" \"SSN\" char(11)," + EOL +
" UNIQUE (\"SSN\")," + EOL +
" FOREIGN KEY (\"EmployerId\") REFERENCES \"Companies\" (\"Id\")" + EOL +
");" + EOL,
Sql);
}
protected override void Generate(AddColumnOperation addColumnOperation, IndentedTextWriter writer)
{
var add = new AddColumnOperation(TrimSchemaPrefix(addColumnOperation.Table), addColumnOperation.Column);
base.Generate(add, writer);
}
public async Task AddPropertyAsync(string entityName,
[FromQuery] string propertyName,
[FromQuery] PropertyType propertyType,
[FromQuery] bool isNullable)
{
var dbEntiy = _db.Model.FindEntityType(entityName);
MetadataModel model = null;
var customization = await _db.Customizations.OrderByDescending(p => p.Id).FirstOrDefaultAsync();
if (customization != null)
{
var entities = JsonSerializer.Deserialize <IEnumerable <EntityMetadata> >(customization.Metadata);
model = new MetadataModel(customization.Id, entities);
}
else
{
model = new MetadataModel(0, new EntityMetadata[] { });
}
EntityMetadata entity = model.Entities.FirstOrDefault(p => p.EntityName == entityName);
if (entity == null)
{
entity = new EntityMetadata(entityName, new PropertyMetadata[] { });
model.Entities.Add(entity);
}
var prop = new PropertyMetadata(propertyName, isNullable, propertyType);
entity.Properties.Add(prop);
var addColumn = new AddColumnOperation
{
Name = propertyName,
ClrType = CrmContext.GetClrType(prop),
IsNullable = prop.IsNullable,
Table = dbEntiy.GetTableName(),
Schema = dbEntiy.GetSchema(),
};
var generator = ((IInfrastructure <IServiceProvider>)_db).Instance.GetRequiredService <IMigrationsSqlGenerator>();
var scripts = generator.Generate(new[] { addColumn });
await using (var transaction = await _db.Database.BeginTransactionAsync())
{
foreach (var script in scripts)
{
await _db.Database.ExecuteSqlRawAsync(script.CommandText);
}
var dbCustomization = new Customization {
Created = DateTime.UtcNow, Metadata = JsonSerializer.Serialize(model.Entities)
};
_db.Add(dbCustomization);
await _db.SaveChangesAsync();
var newId = dbCustomization.Id;
var newModel = new MetadataModel(newId, model.Entities);
await transaction.CommitAsync();
_setter.SetModel(newModel);
}
}
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 List <MigrationOperation> GetOperations()
{
List <MigrationOperation> operations = new List <MigrationOperation>();
if (this.DeletedIndexes != null)
{
foreach (var index in DeletedIndexes)
{
var _DropIndexOperation = new DropIndexOperation();
_DropIndexOperation.Table = this.OldTableName.ToLower();
_DropIndexOperation.Name = EF_CreateTable_Action.GetIndexName(index.Keys.Split(','), this.TableId);
operations.Add(_DropIndexOperation);
}
}
if (this.DeletedColumns != null)
{
foreach (var column in this.DeletedColumns)
{
var _DropColumnOperation = new DropColumnOperation()
{
Name = column.Name,
Table = this.OldTableName.ToLower(),
};
operations.Add(_DropColumnOperation);
}
}
if (this.ChangedColumns != null)
{
foreach (var column in this.ChangedColumns)
{
if (column.ChangedProperties["Name"] != null)
{
var _RenameColumnOperation = new RenameColumnOperation()
{
Name = column.ChangedProperties["Name"].OriginalValue.ToString().ToLower(),
NewName = column.Name.ToLower(),
Table = this.OldTableName.ToLower(),
};
operations.Add(_RenameColumnOperation);
}
if (column.ChangedProperties["IsAutoIncrement"] != null)
{
var original = (bool)column.ChangedProperties["IsAutoIncrement"].OriginalValue;
if (original)
{
var _DropSequenceOperation = new DropSequenceOperation()
{
Name = column.Name,
};
operations.Add(_DropSequenceOperation);
}
else
{
var _CreateSequenceOperation = new CreateSequenceOperation()
{
StartValue = 1,
Name = column.Name,
};
operations.Add(_CreateSequenceOperation);
}
}
if (column.ChangedProperties["IsPKID"] != null)
{
var original = (bool)column.ChangedProperties["IsPKID"].OriginalValue;
if (original)
{
var _DropPrimaryKeyOperation = new DropPrimaryKeyOperation()
{
Name = column.Name,
Table = this.OldTableName.ToLower(),
};
operations.Add(_DropPrimaryKeyOperation);
}
else
{
var _AddPrimaryKeyOperation = new AddPrimaryKeyOperation()
{
Columns = new string[] { column.Name },
Table = this.OldTableName.ToLower(),
};
operations.Add(_AddPrimaryKeyOperation);
}
}
if (column.ChangedProperties.Any(m => m.Key != "Name" && m.Key != "IsAutoIncrement" && m.Key != "IsPKID") == false)
{
continue;
}
var olddbtype = column.dbType;
if (column.ChangedProperties["dbType"] != null)
{
olddbtype = column.ChangedProperties["dbType"].OriginalValue.ToString();
}
var oldDefaultValue = column.defaultValue;
if (column.ChangedProperties["defaultValue"] != null)
{
oldDefaultValue = column.ChangedProperties["defaultValue"].OriginalValue.ToString();
}
string oldComputedColumnSql = null;
var oldlength = column.length;
if (column.ChangedProperties["length"] != null)
{
oldlength = column.ChangedProperties["length"].OriginalValue.ToString();
}
if (!string.IsNullOrEmpty(oldlength))
{
//借用ComputedColumnSql字段存放length
oldComputedColumnSql = oldlength;
}
var _AlterColumnOperation = new AlterColumnOperation()
{
Table = this.OldTableName.ToLower(),
ClrType = EF_CreateTable_Action.GetCSharpType(column.dbType),
ColumnType = column.dbType,
DefaultValue = column.defaultValue,
IsNullable = column.CanNull.GetValueOrDefault(),
Name = column.Name.ToLower(),
//OldColumn = new ColumnOperation() {
// ClrType = EF_CreateTable_Action.GetCSharpType(olddbtype),
// ColumnType = olddbtype,
// DefaultValue = oldDefaultValue,
// ComputedColumnSql = oldComputedColumnSql,
//},
};
if (!string.IsNullOrEmpty(column.length))
{
//借用ComputedColumnSql字段存放length
_AlterColumnOperation.ComputedColumnSql = column.length;
}
operations.Add(_AlterColumnOperation);
}
}
if (this.NewTableName.ToLower() != this.OldTableName.ToLower())
{
var _RenameTableOperation = new RenameTableOperation()
{
Name = OldTableName.ToLower(),
NewName = NewTableName.ToLower(),
};
operations.Add(_RenameTableOperation);
}
if (this.NewColumns != null)
{
foreach (var column in this.NewColumns)
{
var _AddColumnOperation = new AddColumnOperation()
{
Table = this.NewTableName.ToLower(),
ClrType = EF_CreateTable_Action.GetCSharpType(column.dbType),
ColumnType = column.dbType,
DefaultValue = column.defaultValue,
IsUnicode = true,
IsNullable = column.CanNull.GetValueOrDefault(),
Name = column.Name.ToLower(),
};
if (!string.IsNullOrEmpty(column.length))
{
//借用ComputedColumnSql字段存放length
_AddColumnOperation.ComputedColumnSql = column.length;
}
operations.Add(_AddColumnOperation);
}
}
if (this.NewIndexes != null)
{
foreach (var indexCfg in this.NewIndexes)
{
var keynames = indexCfg.Keys.Split(',');
var _CreateIndexOperation = new CreateIndexOperation();
_CreateIndexOperation.Table = this.NewTableName.ToLower();
_CreateIndexOperation.Name = EF_CreateTable_Action.GetIndexName(keynames, this.TableId);
_CreateIndexOperation.Columns = keynames.Select(m => m.ToLower()).ToArray();
_CreateIndexOperation.IsUnique = indexCfg.IsUnique.GetValueOrDefault();
operations.Add(_CreateIndexOperation);
}
}
return(operations);
}
protected override void Generate(AddColumnOperation addColumnOperation)
{
SetDateCreatedColumn(addColumnOperation.Column);
base.Generate(addColumnOperation);
}
protected override void Generate(AddColumnOperation addColumnOperation)
{
SetCreatedModifiedAtColumn(addColumnOperation.Column);
base.Generate(addColumnOperation);
}
public void CreateTableOperation_with_annotations(bool autoincrement, string pkName)
{
var addIdColumn = new AddColumnOperation
{
Name = "Id",
Table = "People",
ClrType = typeof(long),
ColumnType = "INTEGER",
IsNullable = false
};
if (autoincrement)
{
addIdColumn.AddAnnotation(SqliteAnnotationNames.Autoincrement, true);
}
Generate(
new CreateTableOperation
{
Name = "People",
Columns =
{
addIdColumn,
new AddColumnOperation
{
Name = "EmployerId",
Table = "People",
ClrType = typeof(int),
ColumnType = "int",
IsNullable = true
},
new AddColumnOperation
{
Name = "SSN",
Table = "People",
ClrType = typeof(string),
ColumnType = "char(11)",
IsNullable = true
}
},
PrimaryKey = new AddPrimaryKeyOperation {
Name = pkName, Columns = new[] { "Id" }
},
UniqueConstraints = { new AddUniqueConstraintOperation {
Columns = new[] { "SSN"}
} },
ForeignKeys =
{
new AddForeignKeyOperation
{
Columns = new[] { "EmployerId" },
PrincipalTable = "Companies",
PrincipalColumns = new[]{ "Id" }
}
}
});
AssertSql(
$@"CREATE TABLE ""People"" (
""Id"" INTEGER NOT NULL{(pkName != null ? $@" CONSTRAINT ""{pkName}""" : "")} PRIMARY KEY{(autoincrement ? " AUTOINCREMENT," : ",")}
""EmployerId"" int NULL,
""SSN"" char(11) NULL,
UNIQUE (""SSN""),
FOREIGN KEY (""EmployerId"") REFERENCES ""Companies"" (""Id"")
);
");
}
protected virtual void Generate(
[NotNull] AddColumnOperation operation,
[CanBeNull] IModel model,
[NotNull] MigrationCommandListBuilder builder)
=> Generate(operation, model, builder, terminate: true);
