public override string Generate(DeleteColumnExpression expression)
{
// before we drop a column, we have to drop any default value constraints in SQL Server
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 as string and run it
SET @sql = N'ALTER TABLE {0} DROP CONSTRAINT ' + @default;
EXEC sp_executesql @sql;
-- now we can finally drop column
ALTER TABLE {0} DROP COLUMN {1};";
return String.Format(sql, Quoter.QuoteTableName(expression.TableName), Quoter.QuoteColumnName(expression.ColumnName));
}
public override string Generate(DeleteColumnExpression expression)
{
if (expression.ColumnNames.Count != 1)
{
throw new DatabaseOperationNotSupportedException();
}
// Limited functionality in CE, for now will just drop the column.. no DECLARE support!
const string sql = @"ALTER TABLE {0} DROP COLUMN {1};";
return String.Format(sql, Quoter.QuoteTableName(expression.TableName), Quoter.QuoteColumnName(expression.ColumnNames.ElementAt(0)));
}
public override string Generate(DeleteColumnExpression expression)
{
StringBuilder builder = new StringBuilder();
foreach (string columnName in expression.ColumnNames) {
if (expression.ColumnNames.First() != columnName) builder.AppendLine(";");
builder.AppendFormat("ALTER TABLE {0}.{1} DROP COLUMN {2}",
Quoter.QuoteSchemaName(expression.SchemaName),
Quoter.QuoteTableName(expression.TableName),
Quoter.QuoteColumnName(columnName));
}
return builder.ToString();
}
public override void Process(DeleteColumnExpression expression)
{
var tableDefinition = GetTableSchema(expression.TableName);
var columnDefinitions = tableDefinition.Columns.ToList();
var indexDefinitions = tableDefinition.Indexes.ToList();
var columnsToRemove = expression.ColumnNames.ToList();
columnDefinitions.RemoveAll(c => columnsToRemove.Remove(c.Name));
indexDefinitions.RemoveAll(i => i.Columns.Any(c => expression.ColumnNames.Contains(c.Name)));
tableDefinition.Columns = columnDefinitions;
tableDefinition.Indexes = indexDefinitions;
if (columnsToRemove.Any())
{
throw new ApplicationException(string.Format("Column {0} does not exist on table {1}.", columnsToRemove.First(), expression.TableName));
}
ProcessAlterTable(tableDefinition);
}
public void CanDeleteColumn()
{
DeleteColumnExpression expression = new DeleteColumnExpression { TableName = table, ColumnName = column };
string sql = generator.Generate(expression);
sql.ShouldBe(string.Format("ALTER TABLE [{0}] DROP COLUMN {1}", table, column));
}
public override string Generate(DeleteColumnExpression expression)
{
// before we drop a column, we have to drop any default value constraints in SQL Server
var builder = new StringBuilder();
foreach (string column in expression.ColumnNames)
{
if (expression.ColumnNames.First() != column) builder.AppendLine("GO");
BuildDelete(expression, column, builder);
}
return builder.ToString();
}
protected virtual void BuildDelete(DeleteColumnExpression expression, string columnName, StringBuilder builder)
{
builder.AppendLine(Generate(new DeleteDefaultConstraintExpression {
ColumnName = columnName,
SchemaName = expression.SchemaName,
TableName = expression.TableName
}));
builder.AppendLine();
builder.AppendLine(String.Format("-- now we can finally drop column\r\nALTER TABLE {0} DROP COLUMN {1};",
Quoter.QuoteTableName(expression.TableName),
Quoter.QuoteColumnName(columnName)));
}
public override string Generate(DeleteColumnExpression expression)
{
return string.Format("ALTER TABLE {0}.{1} DROP COLUMN {2}", FormatSchema(expression.SchemaName), FormatIdentifier(expression.TableName), FormatIdentifier(expression.ColumnName));
}
public override string Generate(DeleteColumnExpression expression)
{
// Limited functionality in CE, for now will just drop the column.. no DECLARE support!
const string sql = @"ALTER TABLE {0} DROP COLUMN {1};";
return String.Format(sql, Quoter.QuoteTableName(expression.TableName), Quoter.QuoteColumnName(expression.ColumnName));
}
public void ErrorIsReturnedWhenTableNameIsNull()
{
var expression = new DeleteColumnExpression { TableName = null };
var errors = ValidationHelper.CollectErrors(expression);
errors.ShouldContain(ErrorMessages.TableNameCannotBeNullOrEmpty);
}
public override string Generate(DeleteColumnExpression expression)
{
truncator.Truncate(expression);
return base.Generate(expression);
}
public IDeleteColumnFromTableSyntax Column(string columnName)
{
var expression = new DeleteColumnExpression { ColumnName = columnName };
_context.Expressions.Add(expression);
return new DeleteColumnExpressionBuilder(expression);
}
public void ErrorIsNotReturnedWhenTableNameIsNotNullEmptyString()
{
var expression = new DeleteColumnExpression { TableName = "Bacon" };
var errors = ValidationHelper.CollectErrors(expression);
errors.ShouldNotContain(ErrorMessages.TableNameCannotBeNullOrEmpty);
}
public void CanDropMultipleColumns()
{
var expression = new DeleteColumnExpression();
expression.TableName = "NewTable";
expression.ColumnNames.Add("NewColumn");
expression.ColumnNames.Add("OtherColumn");
string sql = generator.Generate(expression);
sql.ShouldBe("ALTER TABLE \"NewTable\" DROP \"NewColumn\";" + Environment.NewLine +
"ALTER TABLE \"NewTable\" DROP \"OtherColumn\"");
}
public override string Generate(DeleteColumnExpression expression)
{
return String.Format(DropColumn, Quoter.QuoteTableName(expression.TableName), Quoter.QuoteColumnName(expression.ColumnName));
}
public void CanDropColumn()
{
var tableName = "NewTable";
var columnName = "NewColumn";
var expression = new DeleteColumnExpression();
expression.TableName = tableName;
expression.ColumnName = columnName;
var sql = generator.Generate(expression);
var expectedSql =
@"
DECLARE @default sysname, @sql nvarchar(max);
-- get name of default constraint
SELECT @default = name
FROM sys.default_constraints
WHERE parent_object_id = object_id('NewTable')
AND type = 'D'
AND parent_column_id = (
SELECT column_id
FROM sys.columns
WHERE object_id = object_id('NewTable')
AND name = 'NewColumn'
);
-- create alter table command as string and run it
SET @sql = N'ALTER TABLE [NewTable] DROP CONSTRAINT ' + @default;
EXEC sp_executesql @sql;
-- now we can finally drop column
ALTER TABLE [NewTable] DROP COLUMN [NewColumn];";
sql.ShouldBe(expectedSql);
}
public void CanDropMultipleColumns()
{
var expression = new DeleteColumnExpression();
expression.TableName = "NewTable";
expression.ColumnNames.Add("NewColumn");
expression.ColumnNames.Add("OtherColumn");
string sql = generator.Generate(expression);
sql.ShouldBe("ALTER TABLE \"public\".\"NewTable\" DROP COLUMN \"NewColumn\";\r\n" +
"ALTER TABLE \"public\".\"NewTable\" DROP COLUMN \"OtherColumn\"");
}
public override string Generate(DeleteColumnExpression expression)
{
return compatabilityMode.HandleCompatabilty("Sqlite does not support deleting of columns");
}
public void ErrorIsReturnedWhenColumnIsSpecifiedMultipleTimes()
{
var expression = new DeleteColumnExpression { ColumnNames = { "Bacon", "Bacon" } };
var errors = ValidationHelper.CollectErrors(expression);
errors.ShouldContain(ErrorMessages.ColumnNamesMustBeUnique);
}
public override string Generate(DeleteColumnExpression expression)
{
// before we drop 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("-- now we can finally drop column\r\nALTER TABLE {0} DROP COLUMN {1};",
Quoter.QuoteTableName(expression.TableName),
Quoter.QuoteColumnName(expression.ColumnName)));
return builder.ToString();
}
public override string Generate(DeleteColumnExpression expression)
{
return String.Format("ALTER TABLE {0} DROP COLUMN {1}", expression.TableName, expression.ColumnName);
}
public override string Generate(DeleteColumnExpression expression)
{
return string.Format("ALTER TABLE {0}.{1} DROP COLUMN {2}", Quoter.QuoteSchemaName(expression.SchemaName), Quoter.QuoteTableName(expression.TableName), Quoter.QuoteColumnName(expression.ColumnName));
}
public override string Generate(DeleteColumnExpression expression)
{
StringBuilder builder = new StringBuilder();
foreach (string columnName in expression.ColumnNames)
{
if (expression.ColumnNames.First() != columnName) builder.AppendLine(";");
builder.AppendFormat(DropColumn, Quoter.QuoteTableName(expression.TableName), Quoter.QuoteColumnName(columnName));
}
return builder.ToString();
}
public abstract string Generate(DeleteColumnExpression expression);
public void ErrorIsReturnedWhenColumnNameIsEmptyString()
{
var expression = new DeleteColumnExpression { ColumnName = String.Empty };
var errors = ValidationHelper.CollectErrors(expression);
errors.ShouldContain(ErrorMessages.ColumnNameCannotBeNullOrEmpty);
}
public void CanDropColumn()
{
string tableName = "NewTable";
string columnName = "NewColumn";
var expression = new DeleteColumnExpression();
expression.TableName = tableName;
expression.ColumnName = columnName;
string sql = generator.Generate(expression);
sql.ShouldBe("ALTER TABLE \"public\".\"NewTable\" DROP COLUMN \"NewColumn\"");
}
public void ToStringIsDescriptive()
{
var expression = new DeleteColumnExpression { TableName = "Test", ColumnName = "Bacon" };
expression.ToString().ShouldBe("DeleteColumn Test Bacon");
}
public void Truncate(DeleteColumnExpression expression)
{
expression.TableName = Truncate(expression.TableName);
expression.ColumnNames = TruncateNames(expression.ColumnNames);
}
public void CanDropColumn()
{
var tableName = "NewTable";
var columnName = "NewColumn";
var expression = new DeleteColumnExpression();
expression.TableName = tableName;
expression.ColumnName = columnName;
var sql = generator.Generate(expression);
sql.ShouldBe("ALTER TABLE `NewTable` DROP COLUMN NewColumn");
}
public virtual void Process(DeleteColumnExpression expression)
{
Process(Generator.Generate(expression));
}
