public override string Generate(AlterDefaultConstraintExpression expression)
{
truncator.Truncate(expression);
return String.Format("ALTER TABLE {0} ALTER COLUMN {1} SET DEFAULT {2}",
Quoter.QuoteTableName(expression.TableName),
Quoter.QuoteColumnName(expression.ColumnName),
Quoter.QuoteValue(expression.DefaultValue)
);
}
public static AlterDefaultConstraintExpression GetAlterDefaultConstraintExpression()
{
var expression = new AlterDefaultConstraintExpression
{
ColumnName = TestColumnName1,
DefaultValue = 1,
TableName = TestTableName1
};
return expression;
}
public override string Generate(AlterDefaultConstraintExpression expression)
{
throw new NotImplementedException();
}
public override string Generate(AlterDefaultConstraintExpression expression)
{
return String.Format("ALTER TABLE {0}.{1} ALTER {2} DROP DEFAULT, ALTER {2} {3}", Quoter.QuoteSchemaName(expression.SchemaName), Quoter.QuoteTableName(expression.TableName), Quoter.QuoteColumnName(expression.ColumnName), ((PostgresColumn)Column).FormatAlterDefaultValue(expression.ColumnName, expression.DefaultValue));
}
public override string Generate(AlterDefaultConstraintExpression expression)
{
// before we alter a default constraint on a column, we have to drop any default value constraints in SQL Server
var builder = new StringBuilder();
builder.AppendLine(Generate(new DeleteDefaultConstraintExpression
{
ColumnName = expression.ColumnName,
SchemaName = expression.SchemaName,
TableName = expression.TableName
}));
builder.AppendLine();
builder.Append(String.Format("-- create alter table command to create new default constraint as string and run it\r\nALTER TABLE {0} WITH NOCHECK ADD CONSTRAINT {3} DEFAULT({2}) FOR {1};",
Quoter.QuoteTableName(expression.TableName),
Quoter.QuoteColumnName(expression.ColumnName),
Quoter.QuoteValue(expression.DefaultValue),
Quoter.QuoteConstraintName(SqlServerColumn.GetDefaultConstraintName(expression.TableName, expression.ColumnName))));
return builder.ToString();
}
public void Process(AlterDefaultConstraintExpression expression)
{
Process(Generator.Generate(expression));
}
public void Truncate(AlterDefaultConstraintExpression expression)
{
expression.TableName = Truncate(expression.TableName);
expression.ColumnName = Truncate(expression.ColumnName);
}
public abstract string Generate(AlterDefaultConstraintExpression expression);
public override string Generate(AlterDefaultConstraintExpression expression)
{
const string sql =
@"
DECLARE @default sysname, @sql nvarchar(max);
-- get name of default constraint
SELECT @default = name
FROM sys.default_constraints
WHERE parent_object_id = object_id('{0}')
AND type = 'D'
AND parent_column_id = (
SELECT column_id
FROM sys.columns
WHERE object_id = object_id('{0}')
AND name = '{1}'
);
-- create alter table command to drop contraint as string and run it
SET @sql = N'ALTER TABLE {0} DROP CONSTRAINT ' + @default;
EXEC sp_executesql @sql;
-- create alter table command to create new default constraint as string and run it
SET @sql = N'ALTER TABLE {0} WITH NOCHECK ADD CONSTRAINT [' + @default + '] DEFAULT({2}) FOR {1}';
EXEC sp_executesql @sql;";
return String.Format(sql,Quoter.QuoteTableName(expression.TableName), Quoter.QuoteColumnName(expression.ColumnName),Quoter.QuoteValue(expression.DefaultValue));
}
public override string Generate(AlterDefaultConstraintExpression expression)
{
return compatabilityMode.HandleCompatabilty("Altering of default constraints is not supporteed for MySql");
}
public override string Generate(AlterDefaultConstraintExpression expression)
{
return compatabilityMode.HandleCompatabilty("Sqlite does not support altering of default constraints");
}
public override string Generate(AlterDefaultConstraintExpression expression)
{
throw new DatabaseOperationNotSupportedException();
}
public override string Generate(AlterDefaultConstraintExpression expression)
{
const string sql =
@"
DECLARE @default sysname, @sql nvarchar(max);
-- get name of default constraint
SELECT @default = name
FROM sys.default_constraints
WHERE parent_object_id = object_id('{1}{2}')
AND type = 'D'
AND parent_column_id = (
SELECT column_id
FROM sys.columns
WHERE object_id = object_id('{1}{2}')
AND name = '{3}'
);
-- create alter table command to drop contraint as string and run it
SET @sql = N'ALTER TABLE {0}[{2}] DROP CONSTRAINT ' + @default;
EXEC sp_executesql @sql;
-- create alter table command to create new default constraint as string and run it
SET @sql = N'ALTER TABLE {0}[{2}] WITH NOCHECK ADD CONSTRAINT [' + @default + '] DEFAULT({4}) FOR {3}';
EXEC sp_executesql @sql;";
return FormatExpression(sql, FormatSchema(expression.SchemaName), FormatSchema(expression.SchemaName, false), expression.TableName, expression.ColumnName, FormatSqlEscape(GetConstantValue(expression.DefaultValue)));
}
public override string Generate(AlterDefaultConstraintExpression expression)
{
return compatabilityMode.HandleCompatabilty("Default constraints are not supported");
}
public override string Generate(AlterDefaultConstraintExpression expression)
{
return String.Format(AlterColumn, Quoter.QuoteTableName(expression.TableName), Column.Generate(new ColumnDefinition
{
ModificationType = ColumnModificationType.Alter,
Name = expression.ColumnName,
DefaultValue = expression.DefaultValue
}));
}