protected override void Generate(DropIndexOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("DROP INDEX ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.AppendLine(SqlGenerationHelper.StatementTerminator)
.EndCommand();
}
protected override void Generate(
AlterColumnOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
DropDefaultConstraint(operation.Schema, operation.Table, operation.Name, builder);
builder
.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" ALTER COLUMN ");
ColumnDefinition(
operation.Schema,
operation.Table,
operation.Name,
operation.ClrType,
operation.ColumnType,
operation.IsUnicode,
operation.MaxLength,
operation.IsRowVersion,
operation.IsNullable,
/*defaultValue:*/ null,
/*defaultValueSql:*/ null,
operation.ComputedColumnSql,
/*identity:*/ false,
operation,
model,
builder);
builder.AppendLine(SqlGenerationHelper.StatementTerminator);
if ((operation.DefaultValue != null)
|| (operation.DefaultValueSql != null))
{
builder
.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" ADD");
DefaultValue(operation.DefaultValue, operation.DefaultValueSql, builder);
builder
.Append(" FOR ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.AppendLine(SqlGenerationHelper.StatementTerminator);
}
EndStatement(builder);
}
protected override void Generate(RenameTableOperation operation, IModel model, MigrationCommandListBuilder 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))
.AppendLine(SqlGenerationHelper.StatementTerminator)
.EndCommand();
}
}
protected override void DefaultValue(object defaultValue, string defaultValueSql, MigrationCommandListBuilder builder)
{
Check.NotNull(builder, nameof(builder));
if (defaultValueSql != null)
{
builder
.Append(" DEFAULT ")
.Append(defaultValueSql);
}
else if (defaultValue != null)
{
builder
.Append(" DEFAULT ")
.Append(SqlGenerationHelper.GenerateLiteral(defaultValue));
}
}
public virtual void Generate(MyCatCreateDatabaseOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("CREATE SCHEMA ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.AppendLine(SqlGenerationHelper.BatchTerminator);
}
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(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" DROP PRIMARY KEY;")
.AppendLine();
EndStatement(builder);
}
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)
{
type = GetColumnType(schema, table, name, clrType, unicode, maxLength, rowVersion, model);
}
var property = FindProperty(model, schema, table, name);
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 autoIncrement = false;
var valueGenerationStrategy = annotatable[OracleAnnotationNames.ValueGenerationStrategy] as OracleValueGenerationStrategy?;
if (!valueGenerationStrategy.HasValue)
{
if (property?.ValueGenerated == ValueGenerated.OnAdd)
{
valueGenerationStrategy = OracleValueGenerationStrategy.IdentityColumn;
}
else if (property?.ValueGenerated == ValueGenerated.OnAddOrUpdate)
{
valueGenerationStrategy = OracleValueGenerationStrategy.ComputedColumn;
}
else
{
// check 1.x Value Generation Annotations
var generatedOnAddAnnotation = annotatable[OracleAnnotationNames.LegacyValueGeneratedOnAdd];
if (generatedOnAddAnnotation != null && (bool)generatedOnAddAnnotation)
{
valueGenerationStrategy = OracleValueGenerationStrategy.IdentityColumn;
}
var generatedOnAddOrUpdateAnnotation = annotatable[OracleAnnotationNames.LegacyValueGeneratedOnAddOrUpdate];
if (generatedOnAddOrUpdateAnnotation != null && (bool)generatedOnAddOrUpdateAnnotation)
{
valueGenerationStrategy = OracleValueGenerationStrategy.ComputedColumn;
}
}
}
if ((valueGenerationStrategy == OracleValueGenerationStrategy.IdentityColumn) && string.IsNullOrWhiteSpace(defaultValueSql) && defaultValue == null)
{
switch (matchType)
{
case "tinyint":
case "smallint":
case "mediumint":
case "int":
case "bigint":
autoIncrement = true;
break;
case "datetime":
if (!_options.ConnectionSettings.ServerVersion.SupportsDateTime6)
{
throw new InvalidOperationException(
$"Error in {table}.{name}: DATETIME does not support values generated " +
"on Add or Update in Oracle <= 5.5, try explicitly setting the column type to TIMESTAMP");
}
goto case "timestamp";
case "timestamp":
defaultValueSql = $"CURRENT_TIMESTAMP({matchLen})";
break;
}
}
string onUpdateSql = null;
if (valueGenerationStrategy == OracleValueGenerationStrategy.ComputedColumn)
{
switch (matchType)
{
case "datetime":
if (!_options.ConnectionSettings.ServerVersion.SupportsDateTime6)
{
throw new InvalidOperationException($"Error in {table}.{name}: DATETIME does not support values generated " +
"on Add or Update in Oracle <= 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(Dependencies.SqlGenerationHelper.DelimitIdentifier(name))
.Append(" ")
.Append(type ?? GetColumnType(schema, table, name, clrType, unicode, maxLength, rowVersion, model));
if (!nullable)
{
builder.Append(" NOT NULL");
}
if (autoIncrement)
{
builder.Append(" AUTO_INCREMENT");
}
else
{
if (defaultValueSql != null)
{
builder
.Append(" DEFAULT ")
.Append(defaultValueSql);
}
else if (defaultValue != null)
{
var defaultValueLiteral = Dependencies.TypeMapper.GetMapping(clrType);
builder
.Append(" DEFAULT ")
.Append(defaultValueLiteral.GenerateSqlLiteral(defaultValue));
}
if (onUpdateSql != null)
{
builder
.Append(" ON UPDATE ")
.Append(onUpdateSql);
}
}
}
/// <summary>
/// Builds commands for the given <see cref="AlterColumnOperation" />
/// by making calls on the given <see cref="MigrationCommandListBuilder" />.
/// </summary>
/// <param name="operation"> The operation. </param>
/// <param name="model"> The target model which may be <c>null</c> if the operations exist without a model. </param>
/// <param name="builder"> The command builder to use to build the commands. </param>
protected override void Generate(
AlterColumnOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
IEnumerable <IIndex> indexesToRebuild = null;
var property = FindProperty(model, operation.Schema, operation.Table, operation.Name);
if (operation.ComputedColumnSql != null)
{
var dropColumnOperation = new DropColumnOperation
{
Schema = operation.Schema,
Table = operation.Table,
Name = operation.Name
};
if (property != null)
{
dropColumnOperation.AddAnnotations(_migrationsAnnotations.ForRemove(property));
}
var addColumnOperation = new AddColumnOperation
{
Schema = operation.Schema,
Table = operation.Table,
Name = operation.Name,
ClrType = operation.ClrType,
ColumnType = operation.ColumnType,
IsUnicode = operation.IsUnicode,
MaxLength = operation.MaxLength,
IsRowVersion = operation.IsRowVersion,
IsNullable = operation.IsNullable,
DefaultValue = operation.DefaultValue,
DefaultValueSql = operation.DefaultValueSql,
ComputedColumnSql = operation.ComputedColumnSql,
IsFixedLength = operation.IsFixedLength
};
addColumnOperation.AddAnnotations(operation.GetAnnotations());
// TODO: Use a column rebuild instead
indexesToRebuild = GetIndexesToRebuild(property, operation)
.ToList();
DropIndexes(indexesToRebuild, builder);
Generate(dropColumnOperation, model, builder, terminate: false);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
Generate(addColumnOperation, model, builder, terminate: false);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
CreateIndexes(indexesToRebuild, builder);
builder.EndCommand();
return;
}
var narrowed = false;
if (IsOldColumnSupported(model))
{
if (IsIdentity(operation) != IsIdentity(operation.OldColumn))
{
throw new InvalidOperationException(JetStrings.AlterIdentityColumn);
}
var type = operation.ColumnType
?? GetColumnType(
operation.Schema,
operation.Table,
operation.Name,
operation,
model);
var oldType = operation.OldColumn.ColumnType
?? GetColumnType(
operation.Schema,
operation.Table,
operation.Name,
operation.OldColumn,
model);
narrowed = type != oldType || !operation.IsNullable && operation.OldColumn.IsNullable;
}
if (narrowed)
{
indexesToRebuild = GetIndexesToRebuild(property, operation)
.ToList();
DropIndexes(indexesToRebuild, builder);
}
DropDefaultConstraint(operation.Table, operation.Name, builder);
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table))
.Append(" ALTER COLUMN ");
// NB: DefaultValue, DefaultValueSql, and identity are handled elsewhere. Don't copy them here.
var definitionOperation = new AlterColumnOperation
{
Schema = operation.Schema,
Table = operation.Table,
Name = operation.Name,
ClrType = operation.ClrType,
ColumnType = operation.ColumnType,
IsUnicode = operation.IsUnicode,
IsFixedLength = operation.IsFixedLength,
MaxLength = operation.MaxLength,
IsRowVersion = operation.IsRowVersion,
IsNullable = operation.IsNullable,
ComputedColumnSql = operation.ComputedColumnSql,
OldColumn = operation.OldColumn
};
definitionOperation.AddAnnotations(
operation.GetAnnotations()
.Where(
a => a.Name != JetAnnotationNames.ValueGenerationStrategy &&
a.Name != JetAnnotationNames.Identity));
ColumnDefinition(
operation.Schema,
operation.Table,
operation.Name,
definitionOperation,
model,
builder);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
if (operation.DefaultValue != null ||
operation.DefaultValueSql != null)
{
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table))
.Append(" ADD");
DefaultValue(operation.DefaultValue, operation.DefaultValueSql, operation.ColumnType, builder);
builder
.Append(" FOR ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
}
// TODO: Implement comment/description support. (ADOX/DAO)
/*
* if (operation.OldColumn.Comment != operation.Comment)
* {
* var dropDescription = operation.OldColumn.Comment != null;
* if (dropDescription)
* {
* DropDescription(
* builder,
* operation.Schema,
* operation.Table,
* operation.Name);
* }
*
* if (operation.Comment != null)
* {
* AddDescription(
* builder, operation.Comment,
* operation.Schema,
* operation.Table,
* operation.Name,
* omitSchemaVariable: dropDescription);
* }
* }
*/
if (narrowed)
{
CreateIndexes(indexesToRebuild, builder);
}
builder.EndCommand();
}
protected override void 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);
}
}
protected override void Generate(
AlterDatabaseOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
if (!IsMemoryOptimized(operation))
{
return;
}
builder.AppendLine("IF SERVERPROPERTY('IsXTPSupported') = 1 AND SERVERPROPERTY('EngineEdition') <> 5");
using (builder.Indent())
{
builder
.AppendLine("BEGIN")
.AppendLine("IF NOT EXISTS (");
using (builder.Indent())
{
builder
.Append("SELECT 1 FROM [sys].[filegroups] [FG] ")
.Append("JOIN [sys].[database_files] [F] ON [FG].[data_space_id] = [F].[data_space_id] ")
.AppendLine("WHERE [FG].[type] = N'FX' AND [F].[type] = 2)");
}
using (builder.Indent())
{
builder
.AppendLine("BEGIN")
.AppendLine("DECLARE @db_name NVARCHAR(MAX) = DB_NAME();")
.AppendLine("DECLARE @fg_name NVARCHAR(MAX);")
.AppendLine("SELECT TOP(1) @fg_name = [name] FROM [sys].[filegroups] WHERE [type] = N'FX';")
.AppendLine()
.AppendLine("IF @fg_name IS NULL");
using (builder.Indent())
{
builder
.AppendLine("BEGIN")
.AppendLine("SET @fg_name = @db_name + N'_MODFG';")
.AppendLine("EXEC(N'ALTER DATABASE CURRENT ADD FILEGROUP [' + @fg_name + '] CONTAINS MEMORY_OPTIMIZED_DATA;');")
.AppendLine("END");
}
builder
.AppendLine()
.AppendLine("DECLARE @path NVARCHAR(MAX);")
.Append("SELECT TOP(1) @path = [physical_name] FROM [sys].[database_files] ")
.AppendLine("WHERE charindex('\\', [physical_name]) > 0 ORDER BY [file_id];")
.AppendLine("IF (@path IS NULL)")
.IncrementIndent().AppendLine("SET @path = '\\' + @db_name;").DecrementIndent()
.AppendLine()
.AppendLine("DECLARE @filename NVARCHAR(MAX) = right(@path, charindex('\\', reverse(@path)) - 1);")
.AppendLine("SET @filename = REPLACE(left(@filename, len(@filename) - charindex('.', reverse(@filename))), '''', '''''') + N'_MOD';")
.AppendLine("DECLARE @new_path NVARCHAR(MAX) = REPLACE(CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(MAX)), '''', '''''') + @filename;")
.AppendLine()
.AppendLine("EXEC(N'");
using (builder.Indent())
{
builder
.AppendLine("ALTER DATABASE CURRENT")
.AppendLine("ADD FILE (NAME=''' + @filename + ''', filename=''' + @new_path + ''')")
.AppendLine("TO FILEGROUP [' + @fg_name + '];')");
}
builder.AppendLine("END");
}
builder.AppendLine("END");
}
builder.AppendLine()
.AppendLine("IF SERVERPROPERTY('IsXTPSupported') = 1")
.AppendLine("EXEC(N'");
using (builder.Indent())
{
builder
.AppendLine("ALTER DATABASE CURRENT")
.AppendLine("SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON;')");
}
builder.EndCommand(suppressTransaction: true);
}
protected override void ColumnDefinition(
string schema,
string table,
string name,
Type clrType,
string type,
bool? unicode,
int? maxLength,
bool rowVersion,
bool nullable,
object defaultValue,
string defaultValueSql,
string computedColumnSql,
IAnnotatable annotatable,
IModel model,
MigrationCommandListBuilder builder)
{
base.ColumnDefinition(
schema, table, name, clrType, type, unicode, maxLength, rowVersion, nullable,
defaultValue, defaultValueSql, computedColumnSql, annotatable, model, builder);
var inlinePk = annotatable[SqliteFullAnnotationNames.Instance.InlinePrimaryKey] as bool?;
if (inlinePk == true)
{
var inlinePkName = annotatable[
SqliteFullAnnotationNames.Instance.InlinePrimaryKeyName] as string;
if (!string.IsNullOrEmpty(inlinePkName))
{
builder
.Append(" CONSTRAINT ")
.Append(SqlGenerationHelper.DelimitIdentifier(inlinePkName));
}
builder.Append(" PRIMARY KEY");
var autoincrement = annotatable[SqliteFullAnnotationNames.Instance.Autoincrement] as bool?;
if (autoincrement == true)
{
builder.Append(" AUTOINCREMENT");
}
}
}
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));
var matchType = type;
var matchLen = "";
var match = TypeRe.Match(type ?? "-");
if (match.Success)
{
matchType = match.Groups[1].Value.ToUpper();
if (!string.IsNullOrWhiteSpace(match.Groups[2].Value))
{
matchLen = match.Groups[2].Value;
}
}
var Identity = false;
var valueGenerationStrategy = annotatable[FirebirdSqlAnnotationNames.ValueGenerationStrategy] as FirebirdSqlValueGenerationStrategy?;
if ((valueGenerationStrategy == FirebirdSqlValueGenerationStrategy.IdentityColumn) && string.IsNullOrWhiteSpace(defaultValueSql) && defaultValue == null)
{
switch (matchType)
{
case "INTEGER":
case "BIGINT":
Identity = true;
break;
case "DATETIME":
case "TIMESTAMP":
defaultValueSql = $"CURRENT_DATE";
break;
}
}
string onUpdateSql = null;
if (valueGenerationStrategy == FirebirdSqlValueGenerationStrategy.ComputedColumn)
{
//Not Implemented
}
builder
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(name))
.Append(" ")
.Append(type ?? GetColumnType(schema, table, name, clrType, unicode, maxLength, rowVersion, model));
if (!nullable && !Identity)
{
builder.Append(" NOT NULL");
}
if (!nullable && Identity)
{
builder.Append(" GENERATED BY DEFAULT AS IDENTITY NOT NULL");
}
else
{
if (defaultValueSql != null)
{
builder
.Append(" DEFAULT ")
.Append(defaultValueSql);
}
else if (defaultValue != null)
{
var defaultValueLiteral = Dependencies.TypeMapper.GetMapping(clrType);
builder
.Append(" DEFAULT ")
.Append(defaultValueLiteral.GenerateSqlLiteral(defaultValue));
}
if (onUpdateSql != null)
{
builder
.Append(" ON UPDATE ")
.Append(onUpdateSql);
}
}
}
protected override void IndexTraits(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var clustered = operation[SqlServerFullAnnotationNames.Instance.Clustered] as bool?;
if (clustered.HasValue)
{
builder.Append(clustered.Value ? "CLUSTERED " : "NONCLUSTERED ");
}
}
protected override void Generate(EnsureSchemaOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
if (string.Equals(operation.Name, "DBO", StringComparison.OrdinalIgnoreCase))
{
return;
}
builder
.Append("IF SCHEMA_ID(")
.Append(SqlGenerationHelper.GenerateLiteral(operation.Name))
.Append(") IS NULL EXEC(N'CREATE SCHEMA ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append("')")
.AppendLine(SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
protected override void Generate(CreateIndexOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
base.Generate(operation, model, builder, terminate: false);
var clustered = operation[SqlServerFullAnnotationNames.Instance.Clustered] as bool?;
if (operation.IsUnique
&& (clustered != true))
{
builder.Append(" WHERE ");
for (var i = 0; i < operation.Columns.Length; i++)
{
if (i != 0)
{
builder.Append(" AND ");
}
builder
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Columns[i]))
.Append(" IS NOT NULL");
}
}
builder.AppendLine(SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
protected override void Generate(DropColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
{
var identifier = SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema);
var alterBase = $"ALTER TABLE {identifier} DROP COLUMN {SqlGenerationHelper.DelimitIdentifier(operation.Name)}";
builder.Append(alterBase).Append(SqlGenerationHelper.StatementTerminator);
}
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,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var property = FindProperty(model, operation.Schema, operation.Table, operation.Name);
if (operation.ComputedColumnSql != null)
{
var dropColumnOperation = new DropColumnOperation
{
Schema = operation.Schema,
Table = operation.Table,
Name = operation.Name
};
var addColumnOperation = new AddColumnOperation
{
Schema = operation.Schema,
Table = operation.Table,
Name = operation.Name,
ClrType = operation.ClrType,
ColumnType = operation.ColumnType,
IsUnicode = operation.IsUnicode,
MaxLength = operation.MaxLength,
IsRowVersion = operation.IsRowVersion,
IsNullable = operation.IsNullable,
DefaultValue = operation.DefaultValue,
DefaultValueSql = operation.DefaultValueSql,
ComputedColumnSql = operation.ComputedColumnSql,
IsFixedLength = operation.IsFixedLength
};
addColumnOperation.AddAnnotations(operation.GetAnnotations());
Generate(dropColumnOperation, model, builder);
Generate(addColumnOperation, model, builder);
return;
}
var valueGenerationStrategy = operation[
OracleAnnotationNames.ValueGenerationStrategy] as OracleValueGenerationStrategy?;
var identity = valueGenerationStrategy == OracleValueGenerationStrategy.IdentityColumn;
if (IsOldColumnSupported(model))
{
var oldValueGenerationStrategy = operation.OldColumn[
OracleAnnotationNames.ValueGenerationStrategy] as OracleValueGenerationStrategy?;
var oldIdentity = oldValueGenerationStrategy == OracleValueGenerationStrategy.IdentityColumn;
if (oldIdentity &&
!identity)
{
DropIdentity(operation, builder);
}
if (operation.OldColumn.DefaultValue != null ||
operation.OldColumn.DefaultValueSql != null &&
(operation.DefaultValue == null ||
operation.DefaultValueSql == null))
{
DropDefaultConstraint(operation.Schema, operation.Table, operation.Name, builder);
}
}
else
{
if (!identity)
{
DropIdentity(operation, builder);
}
if (operation.DefaultValue == null &&
operation.DefaultValueSql == null)
{
DropDefaultConstraint(operation.Schema, operation.Table, operation.Name, builder);
}
}
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" MODIFY ");
ColumnDefinition(
operation.Schema,
operation.Table,
operation.Name,
operation.ClrType,
operation.ColumnType,
operation.IsUnicode,
operation.MaxLength,
operation.IsFixedLength,
operation.IsRowVersion,
operation.IsNullable,
operation.DefaultValue,
operation.DefaultValueSql,
operation.ComputedColumnSql,
identity,
operation,
model,
builder);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
builder.EndCommand();
}
protected override void Generate(
[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>
/// Builds commands for the given <see cref="RenameColumnOperation" />
/// by making calls on the given <see cref="MigrationCommandListBuilder" />.
/// </summary>
/// <param name="operation"> The operation. </param>
/// <param name="model"> The target model which may be <c>null</c> if the operations exist without a model. </param>
/// <param name="builder"> The command builder to use to build the commands. </param>
protected override void Generate(
RenameColumnOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema));
if (_connectionInfo.ServerVersion.SupportsRenameColumn)
{
builder.Append(" RENAME COLUMN ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" TO ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.NewName));
EndStatement(builder);
return;
}
builder.Append(" CHANGE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" ");
var property = FindProperty(model, operation.Schema, operation.Table, operation.NewName);
if (property == null)
{
var type = operation[RelationalAnnotationNames.ColumnType];
if (type == null)
{
throw new InvalidOperationException(
$"Could not find the property corresponding to the column: {Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema)}.{Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.NewName)}. Specify the column type explicitly on 'RenameColumn' using the \"{RelationalAnnotationNames.ColumnType}\" annotation");
}
builder
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.NewName))
.Append(" ")
.Append(type)
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
return;
}
var typeMapping = Dependencies.TypeMappingSource.GetMapping(property);
var converter = typeMapping.Converter;
var clrType = (converter?.ProviderClrType ?? typeMapping.ClrType).UnwrapNullableType();
var columnType = (string)(operation[RelationalAnnotationNames.ColumnType]
?? property[RelationalAnnotationNames.ColumnType]);
var isNullable = property.IsColumnNullable();
var defaultValue = property.GetDefaultValue();
defaultValue = converter != null
? converter.ConvertToProvider(defaultValue)
: defaultValue;
defaultValue = (defaultValue == DBNull.Value ? null : defaultValue)
?? (isNullable
? null
: clrType == typeof(string)
? string.Empty
: clrType.IsArray
? Array.CreateInstance(clrType.GetElementType(), 0)
: clrType.GetDefaultValue());
var isRowVersion = (property.ClrType == typeof(DateTime) || property.ClrType == typeof(byte[])) &&
property.IsConcurrencyToken &&
property.ValueGenerated == ValueGenerated.OnAddOrUpdate;
var addColumnOperation = new AddColumnOperation
{
Schema = operation.Schema,
Table = operation.Table,
Name = operation.NewName,
ClrType = clrType,
ColumnType = columnType,
IsUnicode = property.IsUnicode(),
MaxLength = property.GetMaxLength(),
IsFixedLength = property.IsFixedLength(),
IsRowVersion = isRowVersion,
IsNullable = isNullable,
DefaultValue = defaultValue,
DefaultValueSql = property.GetDefaultValueSql(),
ComputedColumnSql = property.GetComputedColumnSql(),
};
ColumnDefinition(
addColumnOperation,
model,
builder);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
protected override void Generate(AlterColumnOperation operation, 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);
}
/// <summary>
/// Generates a SQL fragment for a column definition for the given column metadata.
/// </summary>
/// <param name="schema"> The schema that contains the table, or <c>null</c> to use the default schema. </param>
/// <param name="table"> The table that contains the column. </param>
/// <param name="name"> The column name. </param>
/// <param name="operation"> The column metadata. </param>
/// <param name="model"> The target model which may be <c>null</c> if the operations exist without a model. </param>
/// <param name="builder"> The command builder to use to add the SQL fragment. </param>
protected override void ColumnDefinition(
[CanBeNull] string schema,
[NotNull] string table,
[NotNull] string name,
[NotNull] ColumnOperation operation,
[CanBeNull] IModel model,
[NotNull] MigrationCommandListBuilder builder)
{
Check.NotEmpty(name, nameof(name));
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var matchType = operation.ColumnType ?? GetColumnType(schema, table, name, operation, model);
var matchLen = "";
var match = _typeRegex.Match(matchType ?? "-");
if (match.Success)
{
matchType = match.Groups[1].Value.ToLower();
if (!string.IsNullOrWhiteSpace(match.Groups[2].Value))
{
matchLen = match.Groups[2].Value;
}
}
var valueGenerationStrategy = MySqlValueGenerationStrategyCompatibility.GetValueGenerationStrategy(operation.GetAnnotations().OfType <IAnnotation>().ToArray());
var autoIncrement = false;
if (valueGenerationStrategy == MySqlValueGenerationStrategy.IdentityColumn &&
string.IsNullOrWhiteSpace(operation.DefaultValueSql) && operation.DefaultValue == null)
{
switch (matchType)
{
case "tinyint":
case "smallint":
case "mediumint":
case "int":
case "bigint":
autoIncrement = true;
break;
case "datetime":
if (!_connectionInfo.ServerVersion.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":
operation.DefaultValueSql = $"CURRENT_TIMESTAMP({matchLen})";
break;
}
}
string onUpdateSql = null;
if (operation.IsRowVersion || valueGenerationStrategy == MySqlValueGenerationStrategy.ComputedColumn)
{
switch (matchType)
{
case "datetime":
if (!_connectionInfo.ServerVersion.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(operation.DefaultValueSql) && operation.DefaultValue == null)
{
operation.DefaultValueSql = $"CURRENT_TIMESTAMP({matchLen})";
}
onUpdateSql = $"CURRENT_TIMESTAMP({matchLen})";
break;
}
}
if (operation.ComputedColumnSql == null)
{
ColumnDefinitionWithCharSet(schema, table, name, operation, model, builder);
if (autoIncrement)
{
builder.Append(" AUTO_INCREMENT");
}
else
{
if (onUpdateSql != null)
{
builder
.Append(" ON UPDATE ")
.Append(onUpdateSql);
}
}
}
else
{
builder
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(name))
.Append(" ")
.Append(operation.ColumnType ?? GetColumnType(schema, table, name, operation, model));
builder
.Append(" AS ")
.Append($"({operation.ComputedColumnSql})");
if (operation.IsNullable && _connectionInfo.ServerVersion.SupportsNullableGeneratedColumns)
{
builder.Append(" NULL");
}
}
}
protected override void Generate(AlterColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var newColumnType = operation.ColumnType;
if (newColumnType == null)
{
var property = FindProperty(model, operation.Schema, operation.Table, operation.Name);
newColumnType = property != null
? Dependencies.TypeMapper.GetMapping(property).StoreType
: Dependencies.TypeMapper.GetMapping(operation.ClrType).StoreType;
}
var oldColumnType = operation.OldColumn?.ColumnType;
var identifier = Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema);
// default value
var defaultBase = $"ALTER TABLE {identifier} ALTER COLUMN {Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name)}";
var hasDefaultValue = operation.DefaultValue != null;
var hasDefaultSql = !string.IsNullOrWhiteSpace(operation.DefaultValueSql);
var hasDefault = hasDefaultValue || hasDefaultSql;
// drop old default value if supported
if (columnSupportsDefault(oldColumnType))
{
builder.Append(defaultBase)
.Append(" DROP DEFAULT")
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
}
// alter the column
var alterBase = $"ALTER TABLE {identifier} MODIFY COLUMN {Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name)}";
builder.Append(alterBase)
.Append(" ")
.Append(newColumnType)
.Append(operation.IsNullable ? " NULL" : " NOT NULL")
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
// set default value after altering column if new column definition has a default value
if (hasDefault)
{
if (!columnSupportsDefault(newColumnType))
{
throw new NotSupportedException($"{newColumnType} column can't have a default value");
}
if (hasDefaultValue)
{
var typeMapping = Dependencies.TypeMapper.GetMapping(operation.DefaultValue.GetType());
builder.Append(defaultBase)
.Append(" SET DEFAULT ")
.Append(typeMapping.GenerateSqlLiteral(operation.DefaultValue))
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
}
else if (hasDefaultSql)
{
builder.Append(defaultBase)
.Append(" SET DEFAULT ")
.Append(operation.DefaultValueSql)
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
}
}
EndStatement(builder);
}
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
? Dependencies.TypeMapper.GetMapping(property).StoreType
: Dependencies.TypeMapper.GetMapping(operation.ClrType).StoreType;
}
var identifier = Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema);
var alterBase = $"ALTER TABLE {identifier} MODIFY COLUMN {Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name)}";
// TYPE
builder.Append(alterBase)
.Append(" ")
.Append(type)
.Append(operation.IsNullable ? " NULL" : " NOT NULL")
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
switch (type)
{
case "tinyblob":
case "blob":
case "mediumblob":
case "longblob":
case "tinytext":
case "text":
case "mediumtext":
case "longtext":
case "geometry":
case "point":
case "linestring":
case "polygon":
case "multipoint":
case "multilinestring":
case "multipolygon":
case "geometrycollection":
case "json":
if (operation.DefaultValue != null || !string.IsNullOrWhiteSpace(operation.DefaultValueSql))
{
throw new NotSupportedException($"{type} column can't have a default value");
}
break;
default:
alterBase = $"ALTER TABLE {identifier} ALTER COLUMN {Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name)}";
builder.Append(alterBase);
if (operation.DefaultValue != null)
{
var typeMapping = Dependencies.TypeMapper.GetMapping(operation.DefaultValue.GetType());
builder.Append(" SET DEFAULT ")
.Append(typeMapping.GenerateSqlLiteral(operation.DefaultValue))
.AppendLine(Dependencies.SqlGenerationHelper.BatchTerminator);
}
else if (!string.IsNullOrWhiteSpace(operation.DefaultValueSql))
{
builder.Append(" SET DEFAULT ")
.Append(operation.DefaultValueSql)
.AppendLine(Dependencies.SqlGenerationHelper.BatchTerminator);
}
else
{
builder.Append(" DROP DEFAULT;");
}
break;
}
EndStatement(builder);
}
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(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" ADD ");
PrimaryKeyConstraint(operation, model, builder);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
var annotations = model.GetAnnotations();
if (operation.Columns.Count() == 1)
{
builder.Append(@"DROP PROCEDURE IF EXISTS POMELO_AFTER_ADD_PRIMARY_KEY;
CREATE PROCEDURE POMELO_AFTER_ADD_PRIMARY_KEY(IN `SCHEMA_NAME_ARGUMENT` VARCHAR(255), IN `TABLE_NAME_ARGUMENT` VARCHAR(255), IN `COLUMN_NAME_ARGUMENT` VARCHAR(255))
BEGIN
DECLARE HAS_AUTO_INCREMENT_ID INT(11);
DECLARE PRIMARY_KEY_COLUMN_NAME VARCHAR(255);
DECLARE PRIMARY_KEY_TYPE VARCHAR(255);
DECLARE SQL_EXP VARCHAR(1000);
SELECT COUNT(*)
INTO HAS_AUTO_INCREMENT_ID
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
AND `COLUMN_NAME` = COLUMN_NAME_ARGUMENT
AND `COLUMN_TYPE` LIKE '%int%'
AND `COLUMN_KEY` = 'PRI';
IF HAS_AUTO_INCREMENT_ID THEN
SELECT `COLUMN_TYPE`
INTO PRIMARY_KEY_TYPE
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
AND `COLUMN_NAME` = COLUMN_NAME_ARGUMENT
AND `COLUMN_TYPE` LIKE '%int%'
AND `COLUMN_KEY` = 'PRI';
SELECT `COLUMN_NAME`
INTO PRIMARY_KEY_COLUMN_NAME
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
AND `COLUMN_NAME` = COLUMN_NAME_ARGUMENT
AND `COLUMN_TYPE` LIKE '%int%'
AND `COLUMN_KEY` = 'PRI';
SET SQL_EXP = CONCAT('ALTER TABLE `', (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA())), '`.`', TABLE_NAME_ARGUMENT, '` MODIFY COLUMN `', PRIMARY_KEY_COLUMN_NAME, '` ', PRIMARY_KEY_TYPE, ' NOT NULL AUTO_INCREMENT;');
SET @SQL_EXP = SQL_EXP;
PREPARE SQL_EXP_EXECUTE FROM @SQL_EXP;
EXECUTE SQL_EXP_EXECUTE;
DEALLOCATE PREPARE SQL_EXP_EXECUTE;
END IF;
END;");
builder.AppendLine();
if (operation.Schema == null)
{
builder.Append($"CALL POMELO_AFTER_ADD_PRIMARY_KEY(NULL, '{ operation.Table }', '{ operation.Columns.First() }');");
}
else
{
builder.Append($"CALL POMELO_AFTER_ADD_PRIMARY_KEY('{ operation.Schema }', '{ operation.Table }', '{ operation.Columns.First() }');");
}
builder.AppendLine();
builder.Append($"DROP PROCEDURE IF EXISTS POMELO_AFTER_ADD_PRIMARY_KEY;");
builder.AppendLine();
}
EndStatement(builder);
}
protected override void Generate(AlterTableOperation operation, IModel model, MigrationCommandListBuilder builder)
{
var madeChanges = false;
// Storage parameters
var oldStorageParameters = GetStorageParameters(operation.OldTable);
var newStorageParameters = GetStorageParameters(operation);
var newOrChanged = newStorageParameters.Where(p =>
!oldStorageParameters.ContainsKey(p.Key) ||
oldStorageParameters[p.Key] != p.Value
).ToList();
if (newOrChanged.Count > 0)
{
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema));
builder
.Append(" SET (")
.Append(string.Join(", ", newOrChanged.Select(p => $"{p.Key}={p.Value}")))
.Append(")");
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
madeChanges = true;
}
var removed = oldStorageParameters
.Select(p => p.Key)
.Where(pn => !newStorageParameters.ContainsKey(pn))
.ToList();
if (removed.Count > 0)
{
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema));
builder
.Append(" RESET (")
.Append(string.Join(", ", removed))
.Append(")");
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
madeChanges = true;
}
// Comment
var oldComment = operation.OldTable[NpgsqlAnnotationNames.Comment] as string;
var newComment = operation[NpgsqlAnnotationNames.Comment] as string;
if (oldComment != newComment)
{
var stringTypeMapping = Dependencies.TypeMapper.GetMapping(typeof(string));
builder
.Append("COMMENT ON TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.Append(" IS ")
.Append(stringTypeMapping.GenerateSqlLiteral(newComment));
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
madeChanges = true;
}
if (madeChanges)
{
EndStatement(builder);
}
}
protected override void ColumnDefinition(AddColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
{
builder.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name)).Append(" ").Append(operation.ColumnType ?? GetColumnType(operation.Schema, operation.Table, operation.Name, operation, model));
}
protected override void Generate(AlterColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
// Never touch system columns
if (IsSystemColumn(operation.Name))
{
return;
}
var type = operation.ColumnType ?? GetColumnType(operation.Schema, operation.Table, operation.Name, operation.ClrType, null, operation.MaxLength, false, model);
string sequenceName = null;
var defaultValueSql = operation.DefaultValueSql;
CheckForOldAnnotation(operation);
var valueGenerationStrategy = operation[NpgsqlAnnotationNames.ValueGenerationStrategy] as NpgsqlValueGenerationStrategy?;
if (valueGenerationStrategy == NpgsqlValueGenerationStrategy.SerialColumn)
{
switch (type)
{
case "int":
case "int4":
case "bigint":
case "int8":
case "smallint":
case "int2":
sequenceName = $"{operation.Table}_{operation.Name}_seq";
Generate(new CreateSequenceOperation
{
Name = sequenceName,
ClrType = typeof(long)
}, model, builder, false);
defaultValueSql = $@"nextval('{Dependencies.SqlGenerationHelper.DelimitIdentifier(sequenceName)}')";
// Note: we also need to set the sequence ownership, this is done below
// after the ALTER COLUMN
break;
}
}
var identifier = Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema);
var alterBase = $"ALTER TABLE {identifier} ALTER COLUMN {Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name)} ";
// TYPE
builder.Append(alterBase)
.Append("TYPE ")
.Append(type)
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
// NOT NULL
builder.Append(alterBase)
.Append(operation.IsNullable ? "DROP NOT NULL" : "SET NOT NULL")
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
// DEFAULT
builder.Append(alterBase);
if (operation.DefaultValue != null || defaultValueSql != null)
{
builder.Append("SET");
DefaultValue(operation.DefaultValue, defaultValueSql, builder);
}
else
{
builder.Append("DROP DEFAULT");
}
// Terminate the DEFAULT above
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
// ALTER SEQUENCE
if (sequenceName != null)
{
builder
.Append("ALTER SEQUENCE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(sequenceName))
.Append(" OWNED BY ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table))
.Append('.')
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name));
}
// Comment
var oldComment = operation.OldColumn[NpgsqlAnnotationNames.Comment] as string;
var newComment = operation[NpgsqlAnnotationNames.Comment] as string;
if (oldComment != newComment)
{
var stringTypeMapping = Dependencies.TypeMapper.GetMapping(typeof(string));
builder
.Append("COMMENT ON COLUMN ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append('.')
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" IS ")
.Append(stringTypeMapping.GenerateSqlLiteral(newComment));
}
EndStatement(builder);
}
public virtual void Generate(MyCatDropDatabaseOperation operation, IModel model, MigrationCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var dbName = SqlGenerationHelper.DelimitIdentifier(operation.Name);
builder
.Append("DROP DATABASE ")
.Append(dbName);
}
protected override void Generate(
CreateTableOperation operation,
IModel model,
MigrationCommandListBuilder builder,
bool terminate)
{
// Filter out any system columns
if (operation.Columns.Any(c => IsSystemColumn(c.Name)))
{
var filteredOperation = new CreateTableOperation
{
Name = operation.Name,
Schema = operation.Schema,
PrimaryKey = operation.PrimaryKey,
};
filteredOperation.Columns.AddRange(operation.Columns.Where(c => !_systemColumnNames.Contains(c.Name)));
filteredOperation.ForeignKeys.AddRange(operation.ForeignKeys);
filteredOperation.UniqueConstraints.AddRange(operation.UniqueConstraints);
operation = filteredOperation;
}
base.Generate(operation, model, builder, false);
// CockroachDB "interleave in parent" (https://www.cockroachlabs.com/docs/stable/interleave-in-parent.html)
var interleaveInParentStr = operation[CockroachDbAnnotationNames.InterleaveInParent] as string;
if (interleaveInParentStr != null)
{
var interleaveInParent = new CockroachDbInterleaveInParent(operation);
var parentTableSchema = interleaveInParent.ParentTableSchema;
var parentTableName = interleaveInParent.ParentTableName;
var interleavePrefix = interleaveInParent.InterleavePrefix;
builder
.AppendLine()
.Append("INTERLEAVE IN PARENT ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(parentTableName, parentTableSchema))
.Append(" (")
.Append(string.Join(", ", interleavePrefix.Select(c => Dependencies.SqlGenerationHelper.DelimitIdentifier(c))))
.Append(')');
}
var storageParameters = GetStorageParameters(operation);
if (storageParameters.Count > 0)
{
builder
.AppendLine()
.Append("WITH (")
.Append(string.Join(", ", storageParameters.Select(p => $"{p.Key}={p.Value}")))
.Append(')');
}
// Comment on the table
var comment = operation[NpgsqlAnnotationNames.Comment] as string;
if (comment != null)
{
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
var stringTypeMapping = Dependencies.TypeMapper.GetMapping(typeof(string));
builder
.Append("COMMENT ON TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.Append(" IS ")
.Append(stringTypeMapping.GenerateSqlLiteral(comment));
}
// Comments on the columns
foreach (var columnOp in operation.Columns.Where(c => c[NpgsqlAnnotationNames.Comment] != null))
{
var columnComment = columnOp[NpgsqlAnnotationNames.Comment];
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
var stringTypeMapping = Dependencies.TypeMapper.GetMapping(typeof(string));
builder
.Append("COMMENT ON COLUMN ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.Append('.')
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(columnOp.Name))
.Append(" IS ")
.Append(stringTypeMapping.GenerateSqlLiteral(columnComment));
}
if (terminate)
{
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
EndStatement(builder);
}
}
protected override void ForeignKeyAction(ReferentialAction referentialAction, MigrationCommandListBuilder builder)
{
Check.NotNull(builder, nameof(builder));
if (referentialAction == ReferentialAction.Restrict)
{
builder.Append("NO ACTION");
}
else
{
base.ForeignKeyAction(referentialAction, builder);
}
}
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, model, operation.Schema, operation.Table));
return;
}
var narrowed = false;
if (IsOldColumnSupported(model))
{
var valueGenerationStrategy = operation[
OracleAnnotationNames.ValueGenerationStrategy] as OracleValueGenerationStrategy?;
var identity = valueGenerationStrategy == OracleValueGenerationStrategy.IdentityColumn;
var oldValueGenerationStrategy = operation.OldColumn[
OracleAnnotationNames.ValueGenerationStrategy] as OracleValueGenerationStrategy?;
var oldIdentity = oldValueGenerationStrategy == OracleValueGenerationStrategy.IdentityColumn;
if (identity != oldIdentity)
{
throw new InvalidOperationException(OracleStrings.AlterIdentityColumn);
}
var type = operation.ColumnType
?? GetColumnType(
operation.Schema,
operation.Table,
operation.Name,
operation.ClrType,
operation.IsUnicode,
operation.MaxLength,
operation.IsRowVersion,
model);
var oldType = operation.OldColumn.ColumnType
?? GetColumnType(
operation.Schema,
operation.Table,
operation.Name,
operation.OldColumn.ClrType,
operation.OldColumn.IsUnicode,
operation.OldColumn.MaxLength,
operation.OldColumn.IsRowVersion,
model);
narrowed = type != oldType || !operation.IsNullable && operation.OldColumn.IsNullable;
}
if (narrowed)
{
indexesToRebuild = GetIndexesToRebuild(property, operation).ToList();
DropIndexes(indexesToRebuild, builder);
}
DropDefaultConstraint(operation.Schema, operation.Table, operation.Name, builder);
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" ALTER COLUMN ");
ColumnDefinition(
operation.Schema,
operation.Table,
operation.Name,
operation.ClrType,
operation.ColumnType,
operation.IsUnicode,
operation.MaxLength,
operation.IsRowVersion,
operation.IsNullable,
/*defaultValue:*/ null,
/*defaultValueSql:*/ null,
operation.ComputedColumnSql,
/*identity:*/ false,
operation,
model,
builder);
builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
if (operation.DefaultValue != null ||
operation.DefaultValueSql != null)
{
builder
.Append("ALTER TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" ADD");
DefaultValue(operation.DefaultValue, operation.DefaultValueSql, builder);
builder
.Append(" FOR ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
}
if (narrowed)
{
CreateIndexes(indexesToRebuild, builder);
}
builder.EndCommand(suppressTransaction: IsMemoryOptimized(operation, model, operation.Schema, operation.Table));
}
protected override void Generate(AlterColumnOperation operation, IModel model, MigrationCommandListBuilder 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).StoreType
: TypeMapper.GetMapping(operation.ClrType).StoreType;
}
var serial = operation.FindAnnotation(MyCatAnnotationNames.Prefix + MyCatAnnotationNames.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":
builder.Append("UUID()");
break;
default:
throw new NotImplementedException($"Not supporting creating IsIdentity for {type}");
}
}
else
{
builder.Append(" DROP DEFAULT ");
}
}
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);
}
}
