public IEnumerable<string> AddColumn(string tableName, Column column)
{
AddMethodName();
AddNewObjectNames(column.Name);
_dataTypes.Add(new UsedDataType(column.DataType, false, false));
return Enumerable.Empty<string>();
}
public IEnumerable<string> AddColumn(string tableName, Column column)
{
// assemble ALTER TABLE statements
string commandText = string.Format(@"{0} ADD ", AlterTable(tableName));
commandText += GetColumnString(column, false);
yield return commandText;
}
protected override IEnumerable<string> DropDefaultConstraint(string tableName, Column column, bool checkIfExists)
{
// checkIfExists can be ignored
yield return string.Format(CultureInfo.InvariantCulture, @"ALTER TABLE {0} ALTER COLUMN {1} DROP DEFAULT",
Escape(tableName),
Escape(column.Name));
}
public IEnumerable<string> AddColumn(TableName tableName, Column column)
{
string commandText = string.Format(CultureInfo.InvariantCulture, @"ALTER TABLE {0} ADD {1}",
Escape(tableName.Name),
GetColumnSpec(column));
yield return commandText;
}
public IEnumerable <string> AlterColumn(string tableName, Column column)
{
// drop (possibly) existing default constraint
Table table = GetTable(tableName);
var c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
Nullable = column.IsNullable,
DataType = Convert(column.DataType),
};
DefaultConstraint defaultConstraint = AddDefaultConstraint(c, tableName, "dummy");
defaultConstraint.Drop();
foreach (string command in ScriptChanges(table.Parent.Parent))
{
yield return(string.Format(CultureInfo.InvariantCulture, "IF OBJECT_ID('{0}') IS NOT NULL ", defaultConstraint.Name) + command);
}
// alter column
table = GetTable(tableName);
c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
Nullable = column.IsNullable,
DataType = Convert(column.DataType),
};
if (column.DefaultValue != null)
{
AddDefaultConstraint(c, tableName, column.DefaultValue);
}
c.Alter();
foreach (string command in ScriptChanges(table.Parent.Parent))
{
yield return(command);
}
}
public IEnumerable<string> AddColumn(string tableName, Column column)
{
if (column.IsRowVersion)
{
ThrowRowVersionNotSupportedException();
}
yield return string.Format(CultureInfo.InvariantCulture, @"ALTER TABLE ""{0}"" ADD COLUMN {1}", tableName, GetColumnDefinition(column));
}
protected override IEnumerable<string> DropDefaultConstraint(string tableName, Column column, bool checkIfExists)
{
string constraintName = GetDefaultConstraintName(tableName, column.Name);
string commandText = DropConstraint(tableName, constraintName);
if (checkIfExists)
{
commandText = PrefixIfObjectExists(constraintName, commandText);
}
yield return commandText;
}
private const int MaximumDbObjectNameLength = 64; // http://stackoverflow.com/questions/6868302/maximum-length-of-a-table-name-in-mysql
#endregion Fields
#region Methods
public IEnumerable<string> AddColumn(string tableName, Column column)
{
// assemble ALTER TABLE statements
string commandText = string.Format(CultureInfo.InvariantCulture, @"{0} ADD ", AlterTable(tableName));
string defaultConstraintClause = GetDefaultConstraintClause(tableName, column.Name, column.DefaultValue);
commandText += string.Format(CultureInfo.InvariantCulture, "{0} {1} {2}NULL{3}",
Escape(column.Name),
GetTypeSpecifier(column.DataType),
column.IsNullable ? string.Empty : "NOT ",
defaultConstraintClause);
yield return commandText;
}
public IEnumerable<string> AddColumn(string tableName, Column column)
{
if (column.IsRowVersion)
{
ThrowRowVersionNotSupportedException();
}
// assemble ALTER TABLE statements
string commandText = string.Format(@"{0} ADD ", AlterTable(tableName));
commandText += GetColumnString(column, false);
yield return commandText;
}
public IEnumerable <string> DropDefault(string tableName, Column column)
{
Table table = GetTable(tableName);
var c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
DataType = Convert(column.DataType),
Nullable = column.IsNullable,
};
DefaultConstraint defaultConstraint = AddDefaultConstraint(c, tableName, column.DefaultValue);
defaultConstraint.Drop();
return(ScriptChanges(table.Parent.Parent));
}
public IEnumerable<string> AddColumn(string tableName, Column column)
{
Table table = GetTable(tableName);
var c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
DataType = Convert(column.DataType),
Nullable = column.IsNullable,
};
if (column.DefaultValue != null)
{
AddDefaultConstraint(c, tableName, column.DefaultValue);
}
table.Columns.Add(c);
table.Alter();
return ScriptChanges(table.Parent.Parent);
}
public IEnumerable <string> AddColumn(string tableName, Column column)
{
Table table = GetTable(tableName);
var c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
DataType = Convert(column.DataType),
Nullable = column.IsNullable,
};
if (column.DefaultValue != null)
{
AddDefaultConstraint(c, tableName, column.DefaultValue);
}
table.Columns.Add(c);
table.Alter();
return(ScriptChanges(table.Parent.Parent));
}
public IEnumerable<string> AlterColumn(string tableName, Column column)
{
// remove any existing default value constraints (before possibly adding new ones)
foreach (string text in DropDefaultConstraint(tableName, column, true))
{
yield return text;
}
yield return AlterTable(tableName) + string.Format(CultureInfo.InvariantCulture, " ALTER COLUMN [{0}] {1} {2}NULL",
column.Name,
GetTypeSpecifier(column.DataType),
column.IsNullable ? string.Empty : "NOT ");
if (column.DefaultValue != null)
{
yield return AlterTable(tableName) + string.Format(CultureInfo.InvariantCulture, " ADD {0} FOR {1}",
GetDefaultConstraintClause(tableName, column.Name, column.DefaultValue),
Escape(column.Name));
}
}
public IEnumerable<string> AlterColumn(string tableName, Column column)
{
string query = @"declare
l_nullable varchar2(1);
l_datatype varchar2(106);
begin
select
nullable into l_nullable
from user_tab_columns
where table_name = '{0}'
and column_name = '{1}';
select
data_type into l_datatype
from user_tab_columns
where table_name = '{0}'
and column_name = '{1}';
if l_nullable = 'N' and l_datatype != '{2}' then
execute immediate 'alter table ""{0}"" modify (""{1}"" {2} {5} {3} )';
end if;
if l_nullable = 'Y' and l_datatype != '{2}' then
execute immediate 'alter table ""{0}"" modify (""{1}"" {2} {5} {4} )';
end if;
if l_nullable = 'N' and l_datatype = '{2}' then
execute immediate 'alter table ""{0}"" modify (""{1}"" {5} {3} )';
end if;
if l_nullable = 'Y' and l_datatype = '{2}' then
execute immediate 'alter table ""{0}"" modify (""{1}"" {5} {4} )';
end if;
end;";
query = query.Replace(Environment.NewLine, " ");
string colN = column.IsNullable ? "NULL" : "";
string colY = column.IsNullable ? "" : "NOT NULL";
string defaultConstraintClause = (column.DefaultValue == null) ? "DEFAULT NULL" : string.Format(CultureInfo.InvariantCulture, " DEFAULT {0}", GetDefaultValueAsString(column.DefaultValue).Replace("'", "''"));
yield return string.Format(CultureInfo.InvariantCulture, query, tableName, column.Name, GetTypeSpecifier(column.DataType), colN, colY, defaultConstraintClause);
}
public IEnumerable<string> AlterColumn(string tableName, Column column)
{
// drop (possibly) existing default constraint
Table table = GetTable(tableName);
var c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
Nullable = column.IsNullable,
DataType = Convert(column.DataType),
};
DefaultConstraint defaultConstraint = AddDefaultConstraint(c, tableName, "dummy");
defaultConstraint.Drop();
foreach (string command in ScriptChanges(table.Parent.Parent))
{
yield return string.Format(CultureInfo.InvariantCulture, "IF OBJECT_ID('{0}') IS NOT NULL ", defaultConstraint.Name) + command;
}
// alter column
table = GetTable(tableName);
c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
Nullable = column.IsNullable,
DataType = Convert(column.DataType),
};
if (column.DefaultValue != null)
{
AddDefaultConstraint(c, tableName, column.DefaultValue);
}
c.Alter();
foreach (string command in ScriptChanges(table.Parent.Parent))
{
yield return command;
}
}
public IEnumerable<string> DropDefault(string tableName, Column column)
{
AddMethodName();
return Enumerable.Empty<string>();
}
protected abstract IEnumerable<string> DropDefaultConstraint(string tableName, Column column, bool checkIfExists);
public IEnumerable<string> DropDefault(TableName tableName, Column column)
{
yield return string.Format(CultureInfo.InvariantCulture, "ALTER TABLE {0} ALTER COLUMN {1} DROP DEFAULT",
Escape(tableName.Name),
Escape(column.Name));
}
public IEnumerable<string> AlterColumn(string tableName, Column column)
{
// http://www.sqlite.org/omitted.html
throw new NotSupportedException("Rename the table, create a new table with the correct columns, and copy the contents from the renamed table.");
}
private string GetColumnDefinition(Column column)
{
return string.Format(CultureInfo.InvariantCulture, @"""{0}"" {1}{2}",
column.Name,
GetTypeSpecifier(column.DataType),
GetColumnConstraint(column));
}
private string GetColumnString(Column column, bool isIdentity)
{
string defaultConstraintClause = string.Empty;
string commandText = string.Empty;
if (!isIdentity && !GetTypeSpecifier(column.DataType).EndsWith("LOB", StringComparison.OrdinalIgnoreCase))
{
string defaultValue = column.DefaultValue == null ? "NULL" : GetDefaultValueAsString(column.DefaultValue);
defaultConstraintClause = string.Format(CultureInfo.InvariantCulture, " DEFAULT {0}", defaultValue);
}
else if (column.DefaultValue != null)
{
throw new InvalidOperationException("Cannot add default values to identity columns or LOB columns."); // CLEAN: add validation for these cases
}
commandText += string.Format(CultureInfo.InvariantCulture, "{0} {1} {3} {4} {2}NULL",
Escape(column.Name),
GetTypeSpecifier(column.DataType),
column.IsNullable ? string.Empty : "NOT ",
defaultConstraintClause,
isIdentity ? Identity : string.Empty);
return commandText;
}
public IEnumerable<string> DropDefault(string tableName, Column column)
{
Debug.Assert(column.DefaultValue == null, "The DefaultValue must be null as we are going to call AlterColumn with it.");
return AlterColumn(tableName, column);
}
public IEnumerable<string> ToSql(IProvider provider, IMigrationContext context)
{
var dataType = new DataType(_type, Size, Scale);
var column = new Column(Parent.ColumnName, dataType, _isNullable, DefaultValue, false);
return provider.AlterColumn(new TableName(Parent.Parent.TableName, Parent.Parent.Schema ?? context.GetDefaultSchema()), column);
}
public IEnumerable<string> AlterColumn(TableName tableName, Column column)
{
yield return string.Format(CultureInfo.InvariantCulture, "ALTER TABLE {0} MODIFY COLUMN {1}",
Escape(tableName.Name),
GetColumnSpec(column));
}
public IEnumerable<string> DropDefault(string tableName, Column column)
{
Table table = GetTable(tableName);
var c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
DataType = Convert(column.DataType),
Nullable = column.IsNullable,
};
DefaultConstraint defaultConstraint = AddDefaultConstraint(c, tableName, column.DefaultValue);
defaultConstraint.Drop();
return ScriptChanges(table.Parent.Parent);
}
private string GetColumnSpec(Column column)
{
string defaultValue = GetDefaultValueAsString(column.DefaultValue);
return string.Format(CultureInfo.InvariantCulture, @"{0} {1} {2}NULL {3}",
Escape(column.Name),
GetTypeSpecifier(column.DataType),
column.IsNullable ? string.Empty : "NOT ",
defaultValue.Length > 0 ? "DEFAULT " + defaultValue : string.Empty);
}
public IEnumerable<string> AlterColumn(TableName tableName, Column column)
{
AddMethodName();
_dataTypes.Add(new UsedDataType(column.DataType, false, false));
return Enumerable.Empty<string>();
}
private string GetColumnConstraint(Column column)
{
CreatedColumn createdColumn = column as CreatedColumn;
if (createdColumn != null && createdColumn.IsIdentity && !createdColumn.IsPrimaryKey)
{
// FEATURE: extend the validation that is uses the actual migration commands such that this exception can be caught by the validation
throw new NotSupportedException("Identity can only be used on primary key columns.");
}
// INTEGER column as PK is automatically AUTOINCREMENT: http://www.sqlite.org/autoinc.html
return string.Format(CultureInfo.InvariantCulture, "{0}{1}",
createdColumn != null && createdColumn.IsIdentity ? string.Empty : (column.IsNullable ? " NULL" : " NOT NULL"),
column.DefaultValue != null ? " DEFAULT " + GetDefaultValueAsString(column.DefaultValue) : string.Empty);
}
public IEnumerable<string> AlterColumn(string tableName, Column column)
{
yield return string.Format("{0} ADD {1}", AlterTable(tableName), GetColumnString(column, false));
}
public IEnumerable<string> AddColumn(string tableName, Column column)
{
yield return string.Format(CultureInfo.InvariantCulture, @"ALTER TABLE ""{0}"" ADD COLUMN {1}", tableName, GetColumnDefinition(column));
}
public IEnumerable<string> DropDefault(string tableName, Column column)
{
Debug.Assert(column.DefaultValue == null);
return DropDefaultConstraint(tableName, column.Name, false);
}
public IEnumerable<string> ToSql(IProvider provider, IRuntimeContext context)
{
var dataType = new DataType(_type, Size, Scale);
var column = new Column(Parent.ColumnName, dataType, _isNullable, DefaultValue, false);
return provider.AlterColumn(Parent.Parent.TableName, column);
}
public IEnumerable<string> AlterColumn(string tableName, Column column)
{
string defaultValue = GetDefaultValueAsString(column.DefaultValue);
yield return string.Format(CultureInfo.InvariantCulture, "ALTER TABLE {0} MODIFY COLUMN {1}",
Escape(tableName),
GetColumnSpec(column));
}
