protected override void ColumnDefinition([CanBeNull] string schema, [NotNull] string table, [NotNull] string name, [NotNull] Type clrType, [CanBeNull] string type, [CanBeNull] bool?unicode, [CanBeNull] int?maxLength, bool rowVersion, bool nullable, [CanBeNull] object defaultValue, [CanBeNull] string defaultValueSql, [CanBeNull] string computedColumnSql, [NotNull] IAnnotatable annotatable, [CanBeNull] IModel model, [NotNull] MigrationCommandListBuilder builder)
{
Check.NotEmpty(name, nameof(name));
Check.NotNull(annotatable, nameof(annotatable));
Check.NotNull(clrType, nameof(clrType));
Check.NotNull(builder, nameof(builder));
if (type == null)
{
var property = FindProperty(model, schema, table, name);
type = TypeMapper.FindMapping(property).StoreType;
}
var matchType = type;
var matchLen = "";
var match = TypeRe.Match(type);
if (match.Success)
{
matchType = match.Groups[1].Value.ToLower();
if (!string.IsNullOrWhiteSpace(match.Groups[2].Value))
{
matchLen = match.Groups[2].Value;
}
}
var generatedOnAddAnnotation = annotatable[MySqlAnnotationNames.Prefix + MySqlAnnotationNames.ValueGeneratedOnAdd];
var generatedOnAdd = generatedOnAddAnnotation != null && (bool)generatedOnAddAnnotation;
var generatedOnAddOrUpdateAnnotation = annotatable[MySqlAnnotationNames.Prefix + MySqlAnnotationNames.ValueGeneratedOnAddOrUpdate];
var generatedOnAddOrUpdate = generatedOnAddOrUpdateAnnotation != null && (bool)generatedOnAddOrUpdateAnnotation;
if (generatedOnAdd && string.IsNullOrWhiteSpace(defaultValueSql) && defaultValue == null)
{
switch (matchType)
{
case "int":
case "int4":
type = "int AUTO_INCREMENT";
break;
case "bigint":
case "int8":
type = "bigint AUTO_INCREMENT";
break;
case "smallint":
case "int2":
type = "short AUTO_INCREMENT";
break;
case "datetime":
if (_mySqlTypeMapper != null && !_mySqlTypeMapper.ConnectionSettings.SupportsDateTime6)
{
throw new InvalidOperationException($"Error in {table}.{name}: DATETIME does not support values generated " +
"on Add or Update in MySql <= 5.5, try explicitly setting the column type to TIMESTAMP");
}
goto case "timestamp";
case "timestamp":
defaultValueSql = $"CURRENT_TIMESTAMP({matchLen})";
break;
}
}
string onUpdateSql = null;
if (generatedOnAddOrUpdate)
{
switch (matchType)
{
case "datetime":
if (_mySqlTypeMapper != null && !_mySqlTypeMapper.ConnectionSettings.SupportsDateTime6)
{
throw new InvalidOperationException($"Error in {table}.{name}: DATETIME does not support values generated " +
"on Add or Update in MySql <= 5.5, try explicitly setting the column type to TIMESTAMP");
}
goto case "timestamp";
case "timestamp":
if (string.IsNullOrWhiteSpace(defaultValueSql) && defaultValue == null)
{
defaultValueSql = $"CURRENT_TIMESTAMP({matchLen})";
}
onUpdateSql = $"CURRENT_TIMESTAMP({matchLen})";
break;
}
}
builder
.Append(SqlGenerationHelper.DelimitIdentifier(name))
.Append(" ")
.Append(type ?? GetColumnType(schema, table, name, clrType, unicode, maxLength, rowVersion, model));
if (!nullable)
{
builder.Append(" NOT NULL");
}
if (defaultValueSql != null)
{
builder
.Append(" DEFAULT ")
.Append(defaultValueSql);
}
else if (defaultValue != null)
{
builder
.Append(" DEFAULT ")
.Append(SqlGenerationHelper.GenerateLiteral(defaultValue));
}
if (onUpdateSql != null)
{
builder
.Append(" ON UPDATE ")
.Append(onUpdateSql);
}
}
private void AppendMergeCommandHeader(
StringBuilder commandStringBuilder,
string name,
string schema,
string toInsertTableAlias,
IReadOnlyList <ModificationCommand> modificationCommands,
IReadOnlyList <ColumnModification> writeOperations,
string additionalColumns = null)
{
commandStringBuilder.Append("MERGE ");
SqlGenerationHelper.DelimitIdentifier(commandStringBuilder, name, schema);
commandStringBuilder
.Append(" USING (");
AppendValuesHeader(commandStringBuilder, writeOperations);
AppendValues(commandStringBuilder, writeOperations, "0");
for (var i = 1; i < modificationCommands.Count; i++)
{
commandStringBuilder.AppendLine(",");
AppendValues(
commandStringBuilder,
modificationCommands[i].ColumnModifications.Where(o => o.IsWrite).ToList(),
i.ToString(CultureInfo.InvariantCulture));
}
commandStringBuilder
.Append(") AS ").Append(toInsertTableAlias)
.Append(" (")
.AppendJoin(
writeOperations,
SqlGenerationHelper,
(sb, o, helper) => helper.DelimitIdentifier(sb, o.ColumnName));
if (additionalColumns != null)
{
commandStringBuilder
.Append(", ")
.Append(additionalColumns);
}
commandStringBuilder
.Append(")")
.AppendLine(" ON 1=0")
.AppendLine("WHEN NOT MATCHED THEN");
commandStringBuilder
.Append("INSERT ")
.Append("(")
.AppendJoin(
writeOperations,
SqlGenerationHelper,
(sb, o, helper) => helper.DelimitIdentifier(sb, o.ColumnName))
.Append(")");
AppendValuesHeader(commandStringBuilder, writeOperations);
commandStringBuilder
.Append("(")
.AppendJoin(
writeOperations,
toInsertTableAlias,
SqlGenerationHelper,
(sb, o, alias, helper) =>
{
sb.Append(alias).Append(".");
helper.DelimitIdentifier(sb, o.ColumnName);
})
.Append(")");
}
protected override void Generate(
[NotNull] RenameColumnOperation operation,
[CanBeNull] IModel model,
[NotNull] MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
string createTableSyntax = null;
var connection = _relationalConnection.DbConnection;
var opened = false;
if (connection.State == ConnectionState.Closed)
{
connection.Open();
opened = true;
}
try
{
using (var cmd = _relationalConnection.DbConnection.CreateCommand())
{
var schemaText = string.IsNullOrWhiteSpace(operation.Schema) ? "" : $"`{operation.Schema}`.";
cmd.CommandText = $"SHOW CREATE TABLE {schemaText}`{operation.Table}`";
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
createTableSyntax = reader.GetFieldValue <string>(1);
}
}
}
}
finally
{
if (opened)
{
connection.Close();
}
}
if (createTableSyntax == null)
{
throw new InvalidOperationException($"Could not find SHOW CREATE TABLE syntax for table: '{operation.Table}'");
}
var columnDefinitionRe = new Regex($"^\\s*`?{operation.Name}`?\\s(.*)?$", RegexOptions.Multiline);
var match = columnDefinitionRe.Match(createTableSyntax);
string columnDefinition;
if (match.Success)
{
columnDefinition = match.Groups[1].Value.TrimEnd(',');
}
else
{
throw new InvalidOperationException($"Could not find column definition for table: '{operation.Table}' column: {operation.Name}");
}
builder.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" CHANGE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.NewName))
.Append(" ")
.Append(columnDefinition);
EndStatement(builder);
}
/// <summary>
/// Overridden by database providers to generate a SQL Script that will `BEGIN` a block
/// of SQL if and only if the migration with the given identifier already exists in the history table.
/// </summary>
/// <param name="migrationId"> The migration identifier. </param>
/// <returns> The generated SQL. </returns>
public override string GetBeginIfExistsScript(string migrationId) => $@"
DROP PROCEDURE IF EXISTS {MigrationsScript};
DELIMITER //
CREATE PROCEDURE {MigrationsScript}()
BEGIN
IF EXISTS(SELECT 1 FROM {SqlGenerationHelper.DelimitIdentifier(TableName, TableSchema)} WHERE {SqlGenerationHelper.DelimitIdentifier(MigrationIdColumnName)} = '{migrationId}') THEN
";
protected override void AppendIdentityWhereCondition(StringBuilder commandStringBuilder, ColumnModification columnModification)
{
SqlGenerationHelper.DelimitIdentifier(commandStringBuilder, columnModification.ColumnName);
commandStringBuilder.Append(" = ")
.Append("LAST_INSERT_ID()");
}
protected override void AppendIdentityWhereCondition([NotNull] StringBuilder commandStringBuilder, [NotNull] ColumnModification columnModification)
{
SqlGenerationHelper.DelimitIdentifier(commandStringBuilder, columnModification.ColumnName);
commandStringBuilder.Append($" = {_constants.IdentityColumnSql}");
}
protected override void ColumnDefinition([CanBeNull] string schema, [NotNull] string table, [NotNull] string name, [NotNull] Type clrType, [CanBeNull] string type, [CanBeNull] bool?unicode, [CanBeNull] int?maxLength, bool rowVersion, bool nullable, [CanBeNull] object defaultValue, [CanBeNull] string defaultValueSql, [CanBeNull] string computedColumnSql, [NotNull] IAnnotatable annotatable, [CanBeNull] IModel model, [NotNull] MigrationCommandListBuilder builder)
{
Check.NotEmpty(name, nameof(name));
Check.NotNull(annotatable, nameof(annotatable));
Check.NotNull(clrType, nameof(clrType));
Check.NotNull(builder, nameof(builder));
if (type == null)
{
var property = FindProperty(model, schema, table, name);
type = TypeMapper.FindMapping(property).StoreType;
}
var generatedOnAddAnnotation = annotatable[MyCatAnnotationNames.Prefix + MyCatAnnotationNames.ValueGeneratedOnAdd];
var generatedOnAdd = generatedOnAddAnnotation != null && (bool)generatedOnAddAnnotation;
var generatedOnAddOrUpdateAnnotation = annotatable[MyCatAnnotationNames.Prefix + MyCatAnnotationNames.ValueGeneratedOnAddOrUpdate];
var generatedOnAddOrUpdate = generatedOnAddOrUpdateAnnotation != null && (bool)generatedOnAddOrUpdateAnnotation;
if (generatedOnAdd && string.IsNullOrWhiteSpace(defaultValueSql) && defaultValue == null)
{
switch (type)
{
case "int":
case "int4":
type = "int AUTO_INCREMENT";
break;
case "bigint":
case "int8":
type = "bigint AUTO_INCREMENT";
break;
case "smallint":
case "int2":
type = "short AUTO_INCREMENT";
break;
case "datetime":
case "timestamp":
defaultValueSql = "CURRENT_TIMESTAMP";
break;
}
}
string onUpdateSql = null;
if (generatedOnAddOrUpdate)
{
switch (type)
{
case "datetime":
case "timestamp":
if (string.IsNullOrWhiteSpace(defaultValueSql) && defaultValue == null)
{
defaultValueSql = "CURRENT_TIMESTAMP";
}
onUpdateSql = "CURRENT_TIMESTAMP";
break;
}
}
builder
.Append(SqlGenerationHelper.DelimitIdentifier(name))
.Append(" ")
.Append(type ?? GetColumnType(schema, table, name, clrType, unicode, maxLength, rowVersion, model));
if (!nullable)
{
builder.Append(" NOT NULL");
}
if (defaultValueSql != null)
{
builder
.Append(" DEFAULT ")
.Append(defaultValueSql);
}
else if (defaultValue != null)
{
builder
.Append(" DEFAULT ")
.Append(SqlGenerationHelper.GenerateLiteral(defaultValue));
}
if (onUpdateSql != null)
{
builder
.Append(" ON UPDATE ")
.Append(onUpdateSql);
}
}
public override string GetBeginIfExistsScript(string migrationId) => $@"
DO $EF$
BEGIN
IF EXISTS(SELECT 1 FROM {SqlGenerationHelper.DelimitIdentifier(TableName, TableSchema)} WHERE ""{MigrationIdColumnName}"" = '{migrationId}') THEN";
// ReSharper disable once ParameterTypeCanBeEnumerable.Local
private void AppendInsertOutputClause(
StringBuilder commandStringBuilder,
string name,
string schema,
IReadOnlyList <ColumnModification> operations,
IReadOnlyList <ColumnModification> allOperations)
{
if (allOperations.Count > 0 && allOperations[0] == operations[0])
{
commandStringBuilder
.Append("SELECT LAST_INSERT_ID()");
if (operations.Count > 1)
{
for (var i = 1; i < operations.Count; i++)
{
commandStringBuilder.Append($", (SELECT { SqlGenerationHelper.DelimitIdentifier(operations[i].ColumnName) } FROM { SqlGenerationHelper.DelimitIdentifier(name, schema) } WHERE { SqlGenerationHelper.DelimitIdentifier(operations.First().ColumnName) } = LAST_INSERT_ID())");
}
}
commandStringBuilder.Append(SqlGenerationHelper.StatementTerminator).AppendLine();
}
else if (operations.Count > 0)
{
commandStringBuilder
.Append("SELECT ");
bool isFirst = true;
foreach (var x in operations)
{
if (!isFirst)
{
commandStringBuilder.Append(", ");
}
if (isFirst)
{
isFirst = false;
}
commandStringBuilder.Append($"{ SqlGenerationHelper.DelimitIdentifier(x.ColumnName) }");
}
commandStringBuilder
.Append($" FROM { SqlGenerationHelper.DelimitIdentifier(name, schema) }")
.Append(" WHERE ");
var predicates = new List <string>();
foreach (var x in allOperations.Where(y => y.IsKey))
{
predicates.Add($"{SqlGenerationHelper.DelimitIdentifier(x.ColumnName)} = @{ x.ParameterName }");
}
commandStringBuilder
.Append(string.Join(" AND ", predicates))
.Append(SqlGenerationHelper.StatementTerminator).AppendLine();
}
}
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);
}
public override void AppendNextSequenceValueOperation(StringBuilder commandStringBuilder, string name, string schema)
{
commandStringBuilder.Append("SELECT nextval('");
SqlGenerationHelper.DelimitIdentifier(commandStringBuilder, Check.NotNull(name, nameof(name)), schema);
commandStringBuilder.Append("')");
}
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[NpgsqlFullAnnotationNames.Instance.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('{SqlGenerationHelper.DelimitIdentifier(sequenceName)}')";
// Note: we also need to set the sequence ownership, this is done below
// after the ALTER COLUMN
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");
}
// Terminate the DEFAULT above
builder.AppendLine(SqlGenerationHelper.StatementTerminator);
// ALTER SEQUENCE
if (sequenceName != null)
{
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(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, null, 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;
default:
throw new InvalidOperationException($"Column {operation.Name} of type {type} has ValueGenerated.OnAdd but no default value is defined");
}
}
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(AlterColumnOperation operation, IModel model, RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
// TODO: There is probably duplication here with other methods. See ColumnDefinition.
//TODO: this should provide feature parity with the EF6 provider, check if there's anything missing for EF7
var type = operation.ColumnType;
if (operation.ColumnType == null)
{
var property = FindProperty(model, operation.Schema, operation.Table, operation.Name);
type = property != null
? TypeMapper.GetMapping(property).DefaultTypeName
: TypeMapper.GetMapping(operation.ClrType).DefaultTypeName;
}
var serial = operation.FindAnnotation(NpgsqlAnnotationNames.Prefix + NpgsqlAnnotationNames.Serial);
var isSerial = serial != null && (bool)serial.Value;
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);
builder.Append(alterBase);
if (operation.DefaultValue != null)
{
builder.Append(" SET DEFAULT ")
.Append(SqlGenerationHelper.GenerateLiteral((dynamic)operation.DefaultValue))
.AppendLine(SqlGenerationHelper.StatementTerminator);
}
else if (!string.IsNullOrWhiteSpace(operation.DefaultValueSql))
{
builder.Append(" SET DEFAULT ")
.Append(operation.DefaultValueSql)
.AppendLine(SqlGenerationHelper.StatementTerminator);
}
else if (isSerial)
{
builder.Append(" SET DEFAULT ");
switch (type)
{
case "smallint":
case "int":
case "bigint":
case "real":
case "double precision":
case "numeric":
//TODO: need function CREATE SEQUENCE IF NOT EXISTS and set to it...
//Until this is resolved changing IsIdentity from false to true
//on types int2, int4 and int8 won't switch to type serial2, serial4 and serial8
throw new NotImplementedException("Not supporting creating sequence for integer types");
case "uuid":
builder.Append("uuid_generate_v4()");
break;
default:
throw new NotImplementedException($"Not supporting creating IsIdentity for {type}");
}
}
else
{
builder.Append(" DROP DEFAULT ");
}
}
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 `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 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 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 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 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();
builder.Append($"CALL POMELO_AFTER_ADD_PRIMARY_KEY('{ 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 AppendIdentityWhereCondition(StringBuilder commandStringBuilder, ColumnModification columnModification)
{
ThrowIf.Argument.IsNull(columnModification, "columnModification");
ThrowIf.Argument.IsNull(commandStringBuilder, "commandStringBuilder");
commandStringBuilder.AppendFormat("{0}=LAST_INSERT_ID()", SqlGenerationHelper.DelimitIdentifier(columnModification.ColumnName));
}
protected override void Generate(AlterColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var type = operation.ColumnType;
if (operation.ColumnType == null)
{
var property = FindProperty(model, operation.Schema, operation.Table, operation.Name);
type = property != null
? TypeMapper.GetMapping(property).StoreType
: TypeMapper.GetMapping(operation.ClrType).StoreType;
}
var serial = operation.FindAnnotation(MySqlAnnotationNames.Prefix + MySqlAnnotationNames.Serial);
var isSerial = serial != null && (bool)serial.Value;
var identifier = SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema);
var alterBase = $"ALTER TABLE {identifier} MODIFY COLUMN {SqlGenerationHelper.DelimitIdentifier(operation.Name)}";
// TYPE
builder.Append(alterBase)
.Append(" ")
.Append(type)
.Append(operation.IsNullable ? " NULL" : " NOT NULL")
.AppendLine(SqlGenerationHelper.StatementTerminator);
alterBase = $"ALTER TABLE {identifier} ALTER COLUMN {SqlGenerationHelper.DelimitIdentifier(operation.Name)}";
builder.Append(alterBase);
if (operation.DefaultValue != null)
{
builder.Append(" SET DEFAULT ")
.Append(SqlGenerationHelper.GenerateLiteral((dynamic)operation.DefaultValue))
.AppendLine(SqlGenerationHelper.BatchTerminator);
}
else if (!string.IsNullOrWhiteSpace(operation.DefaultValueSql))
{
builder.Append(" SET DEFAULT ")
.Append(operation.DefaultValueSql)
.AppendLine(SqlGenerationHelper.BatchTerminator);
}
else if (isSerial)
{
builder.Append(" SET DEFAULT ");
switch (type)
{
case "smallint":
case "int":
case "bigint":
case "real":
case "double precision":
case "numeric":
//TODO: need function CREATE SEQUENCE IF NOT EXISTS and set to it...
//Until this is resolved changing IsIdentity from false to true
//on types int2, int4 and int8 won't switch to type serial2, serial4 and serial8
throw new NotImplementedException("Not supporting creating sequence for integer types");
case "char(38)":
case "uuid":
case "uniqueidentifier":
break;
default:
throw new NotImplementedException($"Not supporting creating IsIdentity for {type}");
}
}
else
{
builder.Append(" DROP DEFAULT;");
}
EndStatement(builder);
}
protected override void AppendIdentityWhereCondition(StringBuilder commandStringBuilder, ColumnModification columnModification)
=> commandStringBuilder
.Append(SqlGenerationHelper.DelimitIdentifier(columnModification.ColumnName))
.Append(" = ")
.Append("provider_specific_identity()");
public override ResultSetMapping AppendUpdateOperation(StringBuilder commandStringBuilder, ModificationCommand command, int commandPosition)
{
var sqlGenerationHelper = (IFbSqlGenerationHelper)SqlGenerationHelper;
var name = command.TableName;
var operations = command.ColumnModifications;
var writeOperations = operations.Where(o => o.IsWrite).ToList();
var readOperations = operations.Where(o => o.IsRead).ToList();
var conditionOperations = operations.Where(o => o.IsCondition).ToList();
var inputOperations = GenerateParameters(operations.Where(o => o.IsWrite || o.IsCondition)).ToList();
var anyRead = readOperations.Any();
commandStringBuilder.Append("EXECUTE BLOCK (");
commandStringBuilder.AppendJoin(inputOperations, (b, p) =>
{
b.Append(p.name);
b.Append(" ");
b.Append(p.type);
b.Append(" = ?");
}, ", ");
commandStringBuilder.AppendLine(")");
commandStringBuilder.Append("RETURNS (");
if (anyRead)
{
commandStringBuilder.AppendJoin(readOperations, (b, e) =>
{
var type = GetColumnType(e);
b.Append(SqlGenerationHelper.DelimitIdentifier(e.ColumnName));
b.Append(" ");
b.Append(type);
}, ", ");
}
else
{
commandStringBuilder.Append("ROWS_AFFECTED INT");
}
commandStringBuilder.AppendLine(")");
commandStringBuilder.AppendLine("AS");
commandStringBuilder.AppendLine("BEGIN");
var oldParameterNameMarker = sqlGenerationHelper.ParameterNameMarker;
sqlGenerationHelper.ParameterNameMarker = ":";
try
{
AppendUpdateCommandHeader(commandStringBuilder, name, null, writeOperations);
AppendWhereClause(commandStringBuilder, conditionOperations);
}
finally
{
sqlGenerationHelper.ParameterNameMarker = oldParameterNameMarker;
}
if (anyRead)
{
commandStringBuilder.AppendLine();
commandStringBuilder.Append("RETURNING ");
commandStringBuilder.AppendJoin(readOperations, (b, e) =>
{
b.Append(SqlGenerationHelper.DelimitIdentifier(e.ColumnName));
}, ", ");
commandStringBuilder.Append(" INTO ");
commandStringBuilder.AppendJoin(readOperations, (b, e) =>
{
b.Append(":");
b.Append(SqlGenerationHelper.DelimitIdentifier(e.ColumnName));
}, ", ");
}
commandStringBuilder.Append(SqlGenerationHelper.StatementTerminator).AppendLine();
if (!anyRead)
{
commandStringBuilder.AppendLine("ROWS_AFFECTED = ROW_COUNT;");
commandStringBuilder.AppendLine("SUSPEND;");
}
else
{
commandStringBuilder.AppendLine("IF (ROW_COUNT > 0) THEN");
commandStringBuilder.AppendLine("SUSPEND;");
}
commandStringBuilder.AppendLine("END");
commandStringBuilder.Append(SqlGenerationHelper.StatementTerminator).AppendLine();
return(ResultSetMapping.LastInResultSet);
}
/// <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>
protected override void AppendIdentityWhereCondition(StringBuilder commandStringBuilder, IColumnModification columnModification)
{
SqlGenerationHelper.DelimitIdentifier(commandStringBuilder, "rowid");
commandStringBuilder.Append(" = ")
.Append("last_insert_rowid()");
}