public override void ChangeColumn(string table, Column column)
{
if (!ColumnExists(table, column.Name))
{
Logger.Warn("Column {0}.{1} does not exist", table, column.Name);
return;
}
var existingColumn = GetColumnByName(table, column.Name);
column.Name = existingColumn.Name; // name might have different case.
string tempColumn = "temp_" + column.Name;
RenameColumn(table, column.Name, tempColumn);
// check if this is not-null
bool isNotNull = (column.ColumnProperty & ColumnProperty.NotNull) == ColumnProperty.NotNull;
// remove the not-null option
column.ColumnProperty = (column.ColumnProperty & ~ColumnProperty.NotNull);
AddColumn(table, column);
ExecuteQuery(String.Format("UPDATE {0} SET {1}={2}", table, Dialect.Quote(column.Name), tempColumn));
RemoveColumn(table, tempColumn);
// if is not null, set that now
if (isNotNull) ExecuteQuery(string.Format("ALTER TABLE {0} ALTER COLUMN {1} SET NOT NULL", table, Dialect.Quote(column.Name)));
}
public override void Up()
{
var col = new Column("Url", DbType.String, 255, ColumnProperty.Null);
foreach (string table in _tables)
Database.AddColumn(table, col);
}
public override void MapColumnProperties(Column column)
{
Name = column.Name;
indexed = PropertySelected(column.ColumnProperty, ColumnProperty.Indexed);
var vals = new List<string>();
AddName(vals);
AddType(vals);
AddIdentity(column, vals);
AddUnsigned(column, vals);
AddPrimaryKey(column, vals);
AddIdentityAgain(column, vals);
AddUnique(column, vals);
AddForeignKey(column, vals);
AddDefaultValue(column, vals);
// null / not-null comes last on Oracle - otherwise if use Null/Not-null + default, bad things happen
// (http://geekswithblogs.net/faizanahmad/archive/2009/08/07/add-new-columnfield-in-oracle-db-table---ora.aspx)
AddNotNull(column, vals);
AddNull(column, vals);
columnSql = String.Join(" ", vals.ToArray());
}
public IndexKeyColumn(Column column, SortDirection sortDirection)
{
if (column == null) throw new ArgumentNullException("column");
Column = column;
SortDirection = sortDirection;
}
public void MapColumnProperties(Column column)
{
Name = column.Name;
indexed = PropertySelected(column.ColumnProperty, ColumnProperty.Indexed);
List<string> vals = new List<string>();
vals.Add(dialect.ColumnNameNeedsQuote ? QuotedName : Name);
vals.Add(type);
if (! dialect.IdentityNeedsType)
AddValueIfSelected(column, ColumnProperty.Identity, vals);
AddValueIfSelected(column, ColumnProperty.Unsigned, vals);
if (! PropertySelected(column.ColumnProperty, ColumnProperty.PrimaryKey) || dialect.NeedsNotNullForIdentity)
AddValueIfSelected(column, ColumnProperty.NotNull, vals);
AddValueIfSelected(column, ColumnProperty.PrimaryKey, vals);
if (dialect.IdentityNeedsType)
AddValueIfSelected(column, ColumnProperty.Identity, vals);
AddValueIfSelected(column, ColumnProperty.Unique, vals);
AddValueIfSelected(column, ColumnProperty.ForeignKey, vals);
if (column.DefaultValue != null)
vals.Add(dialect.Default(column.DefaultValue));
columnSql = String.Join(" ", vals.ToArray());
}
public virtual void MapColumnProperties(Column column)
{
Name = column.Name;
indexed = PropertySelected(column.ColumnProperty, ColumnProperty.Indexed);
var vals = new List<string>();
AddName(vals);
AddType(vals);
AddIdentity(column, vals);
AddUnsigned(column, vals);
AddNotNull(column, vals);
AddNull(column, vals);
AddPrimaryKey(column, vals);
AddIdentityAgain(column, vals);
AddUnique(column, vals);
AddForeignKey(column, vals);
AddDefaultValue(column, vals);
columnSql = String.Join(" ", vals.ToArray());
}
public ColumnPropertiesMapper GetColumnMapper(Column column)
{
string type = column.Size > 0 ? GetTypeName(column.Type, column.Size) : GetTypeName(column.Type);
if (! IdentityNeedsType && column.IsIdentity)
type = String.Empty;
return new ColumnPropertiesMapper(this, type);
}
public ColumnPropertiesMapper GetAndMapColumnProperties(Column column)
{
ColumnPropertiesMapper mapper = GetColumnMapper(column);
mapper.MapColumnProperties(column);
if (column.DefaultValue != null)
mapper.Default = column.DefaultValue;
return mapper;
}
public void Int32_is_unsigned_compatible()
{
//arange
Column column = new Column("test", DbType.Int32, ColumnProperty.Unsigned);
//act
ColumnPropertiesMapper mapper = _dialect.GetAndMapColumnProperties(column);
//assert
StringAssert.Contains("UNSIGNED", mapper.ColumnSql);
}
public void Guid_is_not_unsigned_compatible()
{
//arrange
Column column = new Column("test", DbType.Guid, ColumnProperty.Unsigned);
//act
ColumnPropertiesMapper mapper = _dialect.GetAndMapColumnProperties(column);
//assert
Assert.IsFalse(mapper.ColumnSql.Contains("UNSIGNED"));
}
public static Column[] MergeNameWithStandardFields(string indicatedName, Column nameColumn)
{
return new[]
{
new Column(CreatePrimaryKeyColumnName(indicatedName), DbType.Int32, ColumnProperty.PrimaryKeyWithIdentity),
nameColumn,
new Column("Depricated", DbType.Boolean, ColumnProperty.NotNull, 0),
new Column("IsDeleted", DbType.Boolean, ColumnProperty.Null, 0),
new Column("CreatedBy", DbType.Int32, ColumnProperty.NotNull),
new Column("CreatedDate", DbType.DateTime, ColumnProperty.NotNull, "(getdate())")
};
}
public override void ChangeColumn(string table, Column column)
{
if (!ColumnExists(table, column.Name))
{
Logger.Warn("Column {0}.{1} does not exist", table, column.Name);
return;
}
var existingColumn = GetColumnByName(table, column.Name);
if (column.Type == DbType.String)
{
RenameColumn(table, column.Name, TemporaryColumnName);
// check if this is not-null
bool isNotNull = (column.ColumnProperty & ColumnProperty.NotNull) == ColumnProperty.NotNull;
// remove the not-null option
column.ColumnProperty = (column.ColumnProperty & ~ColumnProperty.NotNull);
AddColumn(table, column);
CopyDataFromOneColumnToAnother(table, TemporaryColumnName, column.Name);
RemoveColumn(table, TemporaryColumnName);
//RenameColumn(table, TemporaryColumnName, column.Name);
string columnName = QuoteColumnNameIfRequired(column.Name);
// now set the column to not-null
if (isNotNull) ExecuteQuery(String.Format("ALTER TABLE {0} MODIFY ({1} NOT NULL)", table, columnName));
}
else
{
if (((existingColumn.ColumnProperty & ColumnProperty.NotNull) == ColumnProperty.NotNull)
&& ((column.ColumnProperty & ColumnProperty.NotNull) == ColumnProperty.NotNull))
{
// was not null, and is being change to not-null - drop the not-null all together
column.ColumnProperty = column.ColumnProperty & ~ColumnProperty.NotNull;
}
else if
(((existingColumn.ColumnProperty & ColumnProperty.Null) == ColumnProperty.Null)
&& ((column.ColumnProperty & ColumnProperty.Null) == ColumnProperty.Null))
{
// was null, and is being changed to null - drop the null all together
column.ColumnProperty = column.ColumnProperty & ~ColumnProperty.Null;
}
ColumnPropertiesMapper mapper = _dialect.GetAndMapColumnProperties(column);
ChangeColumn(table, mapper.ColumnSql);
}
}
public override void ChangeColumn(string table, Column column)
{
if (!ColumnExists(table, column.Name))
{
Logger.Warn("Column {0}.{1} does not exist", table, column.Name);
return;
}
string tempColumn = "temp_" + column.Name;
RenameColumn(table, column.Name, tempColumn);
AddColumn(table, column);
ExecuteQuery(String.Format("UPDATE {0} SET {1}={2}", table, column.Name, tempColumn));
RemoveColumn(table, tempColumn);
}
public override void Up()
{
Column[] columns = new Column[]
{
new Column("Id", DbType.Int32, ColumnProperty.PrimaryKeyWithIdentity),
new Column("TaskId", DbType.Int32,
ColumnProperty.ForeignKey | ColumnProperty.NotNull),
new Column("Estimate", DbType.Double, ColumnProperty.NotNull),
new Column("Timestamp", DbType.DateTime, ColumnProperty.NotNull)
};
Database.AddTable("TaskEstimateHistory", columns);
Database.AddForeignKey("FK_TaskEstimateHistory_Task", "TaskEstimateHistory", "TaskId", "Tasks", "Id", ForeignKeyConstraint.Cascade);
}
public override void Up()
{
Column[] columns = new Column[]
{
new Column("Id", DbType.Int32, ColumnProperty.PrimaryKeyWithIdentity),
new Column("Name", DbType.String, 255, ColumnProperty.NotNull),
new Column("Description", DbType.String),
new Column("BusinessValue", DbType.Int32),
new Column("Priority", DbType.Int32),
new Column("Estimate", DbType.Int32),
new Column("ProjectId", DbType.Int32, ColumnProperty.ForeignKey | ColumnProperty.NotNull),
};
Database.AddTable("Stories", columns);
Database.AddForeignKey("FK_Stories_Project", "Stories", "ProjectId", "Projects", "Id", ForeignKeyConstraint.Cascade);
}
public void MapColumnProperties(Column column)
{
Name = column.Name;
indexed = PropertySelected(column.ColumnProperty, ColumnProperty.Indexed);
List<string> vals = new List<string>();
vals.Add(dialect.ColumnNameNeedsQuote ? QuotedName : Name);
vals.Add(type);
if(!string.IsNullOrEmpty(column.Collation))
{
if(new DbType[] { DbType.String, DbType.StringFixedLength, DbType.AnsiString, DbType.AnsiStringFixedLength }.Contains(column.Type))
{
vals.Add(dialect.AddCollation(column.Collation));
}
}
if(!dialect.IdentityNeedsType)
AddValueIfSelected(column, ColumnProperty.Identity, vals);
AddValueIfSelected(column, ColumnProperty.Unsigned, vals);
if(!PropertySelected(column.ColumnProperty, ColumnProperty.PrimaryKey) || dialect.NeedsNotNullForIdentity)
{
AddValueIfSelected(column, ColumnProperty.NotNull, vals);
}
else
{
AddValueIfSelected(column, ColumnProperty.Null, vals);
}
AddValueIfSelected(column, ColumnProperty.PrimaryKey, vals);
if(dialect.IdentityNeedsType)
AddValueIfSelected(column, ColumnProperty.Identity, vals);
AddValueIfSelected(column, ColumnProperty.Unique, vals);
AddValueIfSelected(column, ColumnProperty.ForeignKey, vals);
if(column.DefaultValue != null)
vals.Add(dialect.Default(column.DefaultValue));
columnSql = String.Join(" ", vals.ToArray());
}
public virtual Column[] GetColumns(string table)
{
var columns = new List<Column>();
using (
IDataReader reader =
ExecuteQuery(
String.Format("select COLUMN_NAME, IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS where table_name = '{0}'", table)))
{
while (reader.Read())
{
var column = new Column(reader.GetString(0), DbType.String);
string nullableStr = reader.GetString(1);
bool isNullable = nullableStr == "YES";
column.ColumnProperty |= isNullable ? ColumnProperty.Null : ColumnProperty.NotNull;
columns.Add(column);
}
}
return columns.ToArray();
}
public override void ChangeColumn(string table, Column column)
{
if (!ColumnExists(table, column.Name))
{
Logger.Warn("Column {0}.{1} does not exist", table, column.Name);
return;
}
var tempColumn = new Column("temp_" + column.Name)
{
ColumnProperty = column.ColumnProperty,
DefaultValue = column.DefaultValue ?? null,
Size = column.Size,
Type = column.Type
};
AddColumn(table, tempColumn);
ExecuteQuery(String.Format("UPDATE {0} SET {1}={2}", table, tempColumn.Name, column.Name));
RemoveColumn(table, column.Name);
RenameColumn(table, tempColumn.Name, column.Name);
}
// XXX: Using INFORMATION_SCHEMA.COLUMNS should work, but it was causing trouble, so I used the MySQL specific thing.
public override Column[] GetColumns(string table)
{
List<Column> columns = new List<Column>();
using (
IDataReader reader =
ExecuteQuery(
String.Format("SHOW COLUMNS FROM {0}", table)))
{
while (reader.Read())
{
Column column = new Column(reader.GetString(0), DbType.String);
string nullableStr = reader.GetString(2);
bool isNullable = nullableStr == "YES";
column.ColumnProperty |= isNullable ? ColumnProperty.Null : ColumnProperty.NotNull;
columns.Add(column);
}
}
return columns.ToArray();
}
} } public void AddColumn(string table, Column column)
public virtual Column[] GetColumns(string table)
{
List<Column> columns = new List<Column>();
using (
IDataReader reader =
ExecuteQuery(
String.Format("select COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from information_schema.columns where table_name = '{0}'", table)))
{
while (reader.Read())
{
Column column = new Column(reader.GetString(0), Dialect.GetDbType(reader.GetString(2)));
string nullableStr = reader.GetString(1);
bool isNullable = nullableStr == "YES";
column.ColumnProperty |= isNullable ? ColumnProperty.Null : ColumnProperty.NotNull;
int? size = reader.GetValue(3).Equals(DBNull.Value) ? null : (int?)reader.GetInt32(3);
if (size.HasValue)
{
column.Size = size.Value;
}
columns.Add(column);
}
}
return columns.ToArray();
}
public void AddColumn(string table, Column column)
{
// No Op
}
public virtual void ChangeColumn(string table, Column column)
{
if (!ColumnExists(table, column.Name))
{
Logger.Warn("Column {0}.{1} does not exist", table, column.Name);
return;
}
ColumnPropertiesMapper mapper = _dialect.GetAndMapColumnProperties(column);
ChangeColumn(table, mapper.ColumnSql);
}
public virtual void AddColumn(string table, Column column)
{
if (!TableExists(table))
throw new TableDoesntExistsException(table);
if (ColumnExists(table, column.Name))
throw new ColumnAlreadyExistsException(table, column.Name);
ColumnPropertiesMapper mapper = Dialect.GetAndMapColumnProperties(column);
AddColumn(table, mapper.ColumnSql);
}
public void ChangeColumn(string table, Column column)
{
// No Op
}
public void ChangeColumn(string table, Column column)
{
_sqlServerTransformationProvider.ChangeColumn(table, column);
}
public override Column[] GetColumns(string table)
{
var columns = new List<Column>();
foreach (string columnDef in GetColumnDefs(table))
{
string name = ExtractNameFromColumnDef(columnDef);
// FIXME: Need to get the real type information
var column = new Column(name, DbType.String);
bool isNullable = IsNullable(columnDef);
column.ColumnProperty |= isNullable ? ColumnProperty.Null : ColumnProperty.NotNull;
columns.Add(column);
}
return columns.ToArray();
}
public override Column[] GetColumns(string table)
{
List<Column> columns = new List<Column>();
using (
IDataReader reader =
ExecuteQuery(
String.Format("select COLUMN_NAME, IS_NULLABLE from information_schema.columns where table_name = lower('{0}');", table)))
{
// FIXME: Mostly duplicated code from the Transformation provider just to support stupid case-insensitivty of Postgre
while (reader.Read())
{
Column column = new Column(reader[0].ToString(), DbType.String);
bool isNullable = reader.GetString(1) == "YES";
column.ColumnProperty |= isNullable ? ColumnProperty.Null : ColumnProperty.NotNull;
columns.Add(column);
}
}
return columns.ToArray();
}
void GuardAgainstMaximumColumnNameLengthForOracle(string name, Column[] columns)
{
foreach (Column column in columns)
{
if (column.Name.Length > 30)
{
throw new ArgumentException(
string.Format("When adding table: \"{0}\", the column: \"{1}\", the name of the column is: {2} characters in length, but maximum length for an oracle identifier is 30 characters", name,
column.Name, column.Name.Length), "columns");
}
}
}
private void AddValueIfSelected(Column column, ColumnProperty property, ICollection<string> vals)
{
if (PropertySelected(column.ColumnProperty, property))
vals.Add(dialect.SqlForProperty(property));
}