protected virtual void Generate(
[NotNull] AlterSequenceOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("ALTER SEQUENCE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema));
SequenceOptions(operation, model, builder);
}
protected virtual void Generate(
[NotNull] AddPrimaryKeyOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder 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);
}
private ResultSetMapping AppendSelectCommand(
StringBuilder commandStringBuilder,
IReadOnlyList <ColumnModification> readOperations,
IReadOnlyList <ColumnModification> keyOperations,
string insertedTableName,
int insertedTableIndex,
string tableName,
string schema,
string orderColumn = null)
{
commandStringBuilder
.AppendLine()
.Append("SELECT ")
.AppendJoin(
readOperations,
SqlGenerationHelper,
(sb, o, helper) => helper.DelimitIdentifier(sb, o.ColumnName, "t"))
.Append(" FROM ");
SqlGenerationHelper.DelimitIdentifier(commandStringBuilder, tableName, schema);
commandStringBuilder
.Append(" t")
.AppendLine()
.Append("INNER JOIN ")
.Append(insertedTableName).Append(insertedTableIndex)
.Append(" i")
.Append(" ON ")
.AppendJoin(
keyOperations, (sb, c) =>
{
sb.Append("(");
SqlGenerationHelper.DelimitIdentifier(sb, c.ColumnName, "t");
sb.Append(" = ");
SqlGenerationHelper.DelimitIdentifier(sb, c.ColumnName, "i");
sb.Append(")");
}, " AND ");
if (orderColumn != null)
{
commandStringBuilder
.AppendLine()
.Append("ORDER BY ");
SqlGenerationHelper.DelimitIdentifier(commandStringBuilder, orderColumn, "i");
}
commandStringBuilder
.Append(SqlGenerationHelper.StatementTerminator).AppendLine()
.AppendLine();
return(ResultSetMapping.LastInResultSet);
}
/// <summary>
/// This API supports the Entity Framework Core infrastructure and is not intended to be used
/// directly from your code. This API may change or be removed in future releases.
/// </summary>
public override string GetDeleteScript(string migrationId)
{
Check.NotEmpty(migrationId, nameof(migrationId));
return(new StringBuilder()
.Append("DELETE FROM ")
.AppendLine(SqlGenerationHelper.DelimitIdentifier(TableName, TableSchema))
.Append("WHERE ")
.Append(SqlGenerationHelper.DelimitIdentifier(MigrationIdColumnName))
.Append(" = N'")
.Append(SqlGenerationHelper.EscapeLiteral(migrationId))
.AppendLine("';")
.ToString());
}
protected override void Generate(RenameTableOperation operation, IModel model, RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
if (operation.NewName != null)
{
builder
.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" RENAME TO ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.NewName));
}
}
protected override void Generate([NotNull] DropForeignKeyOperation 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(" DROP FOREIGN KEY ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.AppendLine(SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
protected override void Generate(
DropIndexOperation operation,
IModel model,
RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("DROP INDEX ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" ON ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema));
}
protected override void Generate(DropIndexOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" DROP INDEX ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.AppendLine(SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
protected virtual void Generate(
[NotNull] RestartSequenceOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("ALTER SEQUENCE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.Append(" RESTART WITH ")
.Append(SqlGenerationHelper.GenerateLiteral(operation.StartValue));
}
protected virtual void Generate(
[NotNull] DropUniqueConstraintOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" DROP CONSTRAINT ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name));
}
/// <summary>
/// 创建存储过程命令
/// </summary>
/// <returns></returns>
protected override RawSqlCommand CreateStoreCommand()
{
try
{
if (Check.IsTraceEnabled(m_oracleLogger?.Logger))
{
Trace <DbLoggerCategory.Update> .Write(m_oracleLogger, LogLevel.Trace, OracleTraceTag.Entry, OracleTraceClassName.OracleModificationCommandBatch, OracleTraceFuncName.CreateStoreCommand);
}
var commandText = GetCommandText();
var ommandBuilder = _commandBuilderFactory.Create().Append(commandText);
var parameterValues = new Dictionary <string, object>(GetParameterCount());
foreach (ColumnModification item in ModificationCommands.SelectMany((ModificationCommand t) => t.ColumnModifications))
{
if (item.UseCurrentValueParameter)
{
ommandBuilder.AddParameter(item.ParameterName, SqlGenerationHelper.GenerateParameterName(item.ParameterName), item.Property);
parameterValues.Add(item.ParameterName, item.Value);
}
if (item.UseOriginalValueParameter)
{
ommandBuilder.AddParameter(item.OriginalParameterName, SqlGenerationHelper.GenerateParameterName(item.OriginalParameterName), item.Property);
parameterValues.Add(item.OriginalParameterName, item.OriginalValue);
}
}
for (int i = 1; i < _cursorPosition; i++)
{
var parameterName = $"cur{i}";
ommandBuilder.AddRawParameter(parameterName, (DbParameter) new OracleParameter(parameterName, OracleDbType.RefCursor, (object)DBNull.Value, ParameterDirection.Output));
}
return(new RawSqlCommand(ommandBuilder.Build(), parameterValues));
}
catch (Exception ex)
{
if (Check.IsErrorEnabled(m_oracleLogger?.Logger))
{
Trace <DbLoggerCategory.Update> .Write(m_oracleLogger, LogLevel.Error, OracleTraceTag.Error, OracleTraceClassName.OracleModificationCommandBatch, OracleTraceFuncName.CreateStoreCommand, ex.ToString());
}
throw;
}
finally
{
if (Check.IsTraceEnabled(m_oracleLogger?.Logger))
{
Trace <DbLoggerCategory.Update> .Write(m_oracleLogger, LogLevel.Trace, OracleTraceTag.Exit, OracleTraceClassName.OracleModificationCommandBatch, OracleTraceFuncName.CreateStoreCommand);
}
}
}
protected virtual void Generate(
[NotNull] SqlServerCreateDatabaseOperation operation,
[CanBeNull] IModel model,
[NotNull] MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("CREATE DATABASE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name));
if (!string.IsNullOrEmpty(operation.FileName))
{
var fileName = ExpandFileName(operation.FileName);
var name = Path.GetFileNameWithoutExtension(fileName);
var logFileName = Path.ChangeExtension(fileName, ".ldf");
var logName = name + "_log";
// Match default naming behavior of SQL Server
logFileName = logFileName.Insert(logFileName.Length - ".ldf".Length, "_log");
builder
.AppendLine()
.Append("ON (NAME = '")
.Append(SqlGenerationHelper.EscapeLiteral(name))
.Append("', FILENAME = '")
.Append(SqlGenerationHelper.EscapeLiteral(fileName))
.Append("')")
.AppendLine()
.Append("LOG ON (NAME = '")
.Append(SqlGenerationHelper.EscapeLiteral(logName))
.Append("', FILENAME = '")
.Append(SqlGenerationHelper.EscapeLiteral(logFileName))
.Append("')");
}
builder
.AppendLine(SqlGenerationHelper.StatementTerminator)
.EndCommand(suppressTransaction: true)
.Append("IF SERVERPROPERTY('EngineEdition') <> 5 EXEC(N'ALTER DATABASE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" SET READ_COMMITTED_SNAPSHOT ON")
.Append(SqlGenerationHelper.StatementTerminator)
.Append("')")
.AppendLine(SqlGenerationHelper.StatementTerminator)
.EndCommand(suppressTransaction: true);
}
protected virtual void ColumnDefinition(
[CanBeNull] string schema,
[CanBeNull] string table,
[NotNull] string name,
[NotNull] Type clrType,
[CanBeNull] string type,
bool nullable,
[CanBeNull] object defaultValue,
[CanBeNull] string defaultValueSql,
[CanBeNull] string computedColumnSql,
bool identity,
[NotNull] IAnnotatable annotatable,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotEmpty(name, nameof(name));
Check.NotNull(clrType, nameof(clrType));
Check.NotNull(annotatable, nameof(annotatable));
Check.NotNull(builder, nameof(builder));
if (computedColumnSql != null)
{
builder
.Append(SqlGenerationHelper.DelimitIdentifier(name))
.Append(" AS ")
.Append(computedColumnSql);
return;
}
base.ColumnDefinition(
schema,
table,
name,
clrType,
type,
nullable,
defaultValue,
defaultValueSql,
computedColumnSql,
annotatable,
model,
builder);
if (identity)
{
builder.Append(" IDENTITY");
}
}
protected virtual void Generate(
[NotNull] CreateTableOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("CREATE TABLE ")
.Append(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 (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);
}
builder.AppendLine();
}
builder.Append(")");
}
protected override void Generate(
DropForeignKeyOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
ThrowIf.Argument.IsNull(operation, "operation");
ThrowIf.Argument.IsNull(builder, "builder");
builder
.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" DROP FOREIGN KEY ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.AppendLine(SqlGenerationHelper.StatementTerminator);
}
/// <summary>
/// This is an internal API that supports the Entity Framework Core infrastructure and not subject to
/// the same compatibility standards as public APIs. It may be changed or removed without notice in
/// any release. You should only use it directly in your code with extreme caution and knowing that
/// doing so can result in application failures when updating to a new Entity Framework Core release.
/// </summary>
public override string GetBeginIfExistsScript(string migrationId)
{
var stringTypeMapping = Dependencies.TypeMappingSource.GetMapping(typeof(string));
return(new StringBuilder()
.Append("IF EXISTS(SELECT * FROM ")
.Append(SqlGenerationHelper.DelimitIdentifier(TableName, TableSchema))
.Append(" WHERE ")
.Append(SqlGenerationHelper.DelimitIdentifier(MigrationIdColumnName))
.Append(" = ")
.Append(stringTypeMapping.GenerateSqlLiteral(migrationId))
.AppendLine(")")
.Append("BEGIN")
.ToString());
}
public override string GetBeginIfNotExistsScript(string migrationId)
{
ThrowIf.Argument.IsNull(migrationId, "migrationId");
return(new StringBuilder()
.Append("IF NOT EXISTS(SELECT * FROM ")
.Append(SqlGenerationHelper.DelimitIdentifier(TableName, TableSchema))
.Append(" WHERE ")
.Append(SqlGenerationHelper.DelimitIdentifier(MigrationIdColumnName))
.Append(" = '")
.Append(SqlGenerationHelper.EscapeLiteral(migrationId))
.AppendLine("')")
.Append("BEGIN")
.ToString());
}
/// <summary>
/// Generates a SQL script to delete a row from the history table.
/// </summary>
/// <param name="migrationId"> The migration identifier of the row to delete. </param>
/// <returns> The generated SQL. </returns>
public virtual string GetDeleteScript(string migrationId)
{
Check.NotEmpty(migrationId, nameof(migrationId));
var stringTypeMapping = Dependencies.TypeMappingSource.GetMapping(typeof(string));
return(new StringBuilder().Append("DELETE FROM ")
.AppendLine(SqlGenerationHelper.DelimitIdentifier(TableName, TableSchema))
.Append("WHERE ")
.Append(SqlGenerationHelper.DelimitIdentifier(MigrationIdColumnName))
.Append(" = ")
.Append(stringTypeMapping.GenerateSqlLiteral(migrationId))
.AppendLine(SqlGenerationHelper.StatementTerminator)
.ToString());
}
protected override void Generate(
DropIndexOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
ThrowIf.Argument.IsNull(operation, "operation");
ThrowIf.Argument.IsNull(builder, "builder");
builder
.Append("DROP INDEX ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" ON ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.AppendLine(SqlGenerationHelper.StatementTerminator);
}
protected virtual void Generate(
[NotNull] DropSequenceOperation operation,
[CanBeNull] IModel model,
[NotNull] MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("DROP SEQUENCE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.AppendLine(SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
protected override void Generate(
[NotNull] RenameColumnOperation 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(" RENAME COLUMN ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" TO ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.NewName));
}
public override string GetBeginIfExistsScript(string migrationId)
{
Check.NotEmpty(migrationId, nameof(migrationId));
return(new StringBuilder()
.Append("IF EXISTS(SELECT * FROM ")
.Append(SqlGenerationHelper.DelimitIdentifier(TableName, TableSchema))
.Append(" WHERE ")
.Append(SqlGenerationHelper.DelimitIdentifier(MigrationIdColumnName))
.Append(" = N'")
.Append(SqlGenerationHelper.EscapeLiteral(migrationId))
.AppendLine("')")
.Append("BEGIN")
.ToString());
}
protected virtual void Generate(
[NotNull] AddUniqueConstraintOperation 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 ");
UniqueConstraint(operation, model, builder);
EndStatement(builder);
}
private void AppendInsertOutputClause(
StringBuilder commandStringBuilder,
string name,
string schema,
IReadOnlyList <ColumnModification> operations,
IReadOnlyList <ColumnModification> allOperations)
{
if (allOperations.Count > 0 && allOperations[0] == operations[0])
{
commandStringBuilder
.AppendLine($" RETURNING {SqlGenerationHelper.DelimitIdentifier(operations.First().ColumnName)} INTO :regAffeted;")
.AppendLine("IF (ROW_COUNT > 0) THEN")
.AppendLine(" SUSPEND;");
}
}
protected override void Generate(AlterTableOperation operation, IModel model, MigrationCommandListBuilder builder)
{
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(SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema));
builder
.Append(" SET (")
.Append(string.Join(", ", newOrChanged.Select(p => $"{p.Key}={p.Value}")))
.Append(")");
builder.AppendLine(SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
var removed = oldStorageParameters
.Select(p => p.Key)
.Where(pn => !newStorageParameters.ContainsKey(pn))
.ToList();
if (removed.Count > 0)
{
builder
.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema));
builder
.Append(" RESET (")
.Append(string.Join(", ", removed))
.Append(")");
builder.AppendLine(SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
base.Generate(operation, model, builder);
}
private void AppendReturnInsert(StringBuilder commandStringBuilder, string name, IReadOnlyList <ColumnModification> operations, int commandPosition)
{
if (operations.Count > 0)
{
commandStringBuilder.AppendLine().Append("RETURNING ").AppendJoin(operations, delegate(StringBuilder sb, ColumnModification cm)
{
sb.Append(SqlGenerationHelper.DelimitIdentifier(cm.ColumnName));
})
.Append(" INTO ")
.AppendJoin(operations, delegate(StringBuilder sb, ColumnModification cm)
{
sb.Append($"l{name}({commandPosition + 1}).{SqlGenerationHelper.DelimitIdentifier(cm.ColumnName)}");
});
}
commandStringBuilder.AppendLine(SqlGenerationHelper.StatementTerminator);
}
protected virtual void Generate(
[NotNull] DropPrimaryKeyOperation 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(" DROP CONSTRAINT ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name));
EndStatement(builder);
}
/// <summary>
/// This API supports the Entity Framework Core infrastructure and is not intended to be used
/// directly from your code. This API may change or be removed in future releases.
/// </summary>
public override string GetCreateIfNotExistsScript()
{
var builder = new IndentedStringBuilder();
builder
.Append("IF NOT EXISTS (SELECT * FROM (SHOW TABLES) WHERE Name = '")
.Append(SqlGenerationHelper.EscapeLiteral(TableName))
.Append("') THEN ");
using (builder.Indent())
{
builder.AppendLines(GetCreateScript());
}
builder.AppendLine(";");
return(builder.ToString());
}
protected virtual void Transfer(
[NotNull] string newSchema,
[CanBeNull] string schema,
[NotNull] string name,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotEmpty(newSchema, nameof(newSchema));
Check.NotEmpty(name, nameof(name));
Check.NotNull(builder, nameof(builder));
builder
.Append("ALTER SCHEMA ")
.Append(SqlGenerationHelper.DelimitIdentifier(newSchema))
.Append(" TRANSFER ")
.Append(SqlGenerationHelper.DelimitIdentifier(name, schema));
}
protected override void AppendIdentityWhereCondition(StringBuilder builder, ColumnModification columnModification)
{
Check.NotNull(builder, nameof(builder));
Check.NotNull(columnModification, nameof(columnModification));
var castAs = columnModification.Property.ClrType == typeof(int)
? "int"
: "bigint";
builder
.Append(SqlGenerationHelper.DelimitIdentifier(columnModification.ColumnName))
.Append(" = ")
.Append("CAST (@@IDENTITY AS ")
.Append(castAs)
.Append(")");
}
