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()"); }