private static int GetProviderType(DatabaseColumn column)
{
int providerType = -1;
if (column.DataType != null)
providerType = column.DataType.ProviderDbType;
return providerType;
}
public string WriteDataType(DatabaseColumn column)
{
if (column == null) return string.Empty;
if (string.IsNullOrEmpty(column.DbDataType)) return string.Empty;
var dataType = column.DbDataTypeStandard();
dataType = OtherDatabaseTypesToPostgreSql(dataType, column);
if ((dataType.StartsWith("TIMESTAMP", StringComparison.OrdinalIgnoreCase) || dataType == "TIME") &&
column.DateTimePrecision > 0)
dataType = dataType + " (" + column.DateTimePrecision + ")";
//write out datatype definition
if ((dataType == "VARCHAR" || dataType == "CHAR") && column.Length > 0)
{
dataType = dataType + " (" + column.Length + ")";
}
if (dataType == "NUMERIC" || dataType == "DECIMAL")
{
var scale = column.Scale;
var precision = column.Precision;
var writeScale = ((scale != null) && (scale > 0) ? "," + scale : "");
if (precision > 0) //Postgresql can have no specified precision
dataType = dataType + " (" + precision + writeScale + ")";
}
return dataType;
}
protected override string WriteDataType(DatabaseColumn column)
{
var type = new DataTypeWriter().WriteDataType(column);
type += (!column.Nullable ? " NOT NULL" : string.Empty);
var defaultValue = column.DefaultValue;
if (!string.IsNullOrEmpty(defaultValue))
{
defaultValue = FixDefaultValue(defaultValue);
const string defaultConstraint = " DEFAULT ";
var dbDataType = column.DbDataType.ToUpperInvariant();
if (DataTypeConverter.IsVariableString(dbDataType))
{
type += defaultConstraint + "'" + defaultValue + "'";
}
else //numeric default
{
type += defaultConstraint + defaultValue;
}
}
//cannot detect the "GENERATED BY DEFAULT" vs "GENERATED ALWAYS" version?
if (column.IsAutoNumber) type += " GENERATED BY DEFAULT AS IDENTITY";
if (column.IsPrimaryKey && Table.PrimaryKey.Columns.Count == 1)
type += " PRIMARY KEY";
return type;
}
public override string WriteDataType(DatabaseColumn column)
{
var type = new DataTypeWriter().WriteDataType(column);
if (column.IsPrimaryKey && (Table.PrimaryKey == null || Table.PrimaryKey.Columns.Count == 1))
{
type += " PRIMARY KEY";
if (column.IsAutoNumber) //must be integer primary key
{
return "INTEGER PRIMARY KEY AUTOINCREMENT";
}
}
if (!column.Nullable) type += " NOT NULL";
//if there's a default value, and it's not a guid generator or autonumber
if (!string.IsNullOrEmpty(column.DefaultValue) &&
!SqlTranslator.IsGuidGenerator(column.DefaultValue) &&
!column.IsAutoNumber)
{
var value = SqlTranslator.Fix(column.DefaultValue);
//SqlServer (N'string') format
if (value.StartsWith("(N'", StringComparison.OrdinalIgnoreCase))
value = value.Replace("(N'", "('");
type += " DEFAULT " + value;
}
return type;
}
public void GivenAlteredColumn()
{
//arrange
DatabaseSchema schema1 = CreateSchema();
var productsTable = CreateProductsTable();
schema1.Tables.Add(productsTable);
var nameColumn = new DatabaseColumn { Name = "Name", DbDataType = "NVARCHAR", Length = 10, Nullable = false };
productsTable.Columns.Add(nameColumn);
DatabaseSchema schema2 = CreateSchema();
var productsTable2 = CreateProductsTable();
var nameColumn2 = new DatabaseColumn { Name = "Name", DbDataType = "NVARCHAR", Length = 20, Nullable = true };
productsTable2.Columns.Add(nameColumn2);
schema2.Tables.Add(productsTable2);
//act
var comparison = new CompareSchemas(schema1, schema2);
var result = comparison.ExecuteResult();
//assert
var alterColumn = result.FirstOrDefault(x =>
x.ResultType == ResultType.Change &&
x.SchemaObjectType == SchemaObjectType.Column &&
x.TableName == "Products" &&
x.Name == "Name");
Assert.IsNotNull(alterColumn);
}
/// <summary>
/// Adds a dataType object to a column.
/// </summary>
/// <param name="column">The column.</param>
public static void AddDataType(DatabaseColumn column)
{
if (column == null) return;
//there is no data type at all
if (string.IsNullOrEmpty(column.DbDataType)) return;
//a datatype already assigned
if (column.DataType != null) return;
//use upper case
var dbType = column.DbDataType.ToUpperInvariant();
//nothing to convert
if (string.IsNullOrEmpty(dbType)) return;
var sqlType = SqlType.SqlServer;
var dataTypeList = new List<DataType>();
if (column.Table != null)
{
//look up the full schema if it exists
var schema = column.Table.DatabaseSchema;
if (schema != null)
{
var provider = schema.Provider;
sqlType = ProviderToSqlType.Convert(provider) ?? SqlType.SqlServer;
dataTypeList = schema.DataTypes;
}
}
//does the schema data types contain this type? if so, assign it.
var dataType = FindDataType(dbType, dataTypeList, sqlType, column.Length);
column.DataType = dataType;
}
public virtual string AddColumn(DatabaseTable databaseTable, DatabaseColumn databaseColumn)
{
var tableGenerator = CreateTableGenerator(databaseTable);
var addColumn = tableGenerator.WriteColumn(databaseColumn).Trim();
if (string.IsNullOrEmpty(databaseColumn.DefaultValue) && !databaseColumn.Nullable)
{
var dt = databaseColumn.DataType;
if (dt == null || dt.IsString)
{
addColumn += " DEFAULT ''"; //empty string
}
else if (dt.IsNumeric)
{
addColumn += " DEFAULT 0";
}
else if (dt.IsDateTime)
{
addColumn += " DEFAULT CURRENT_TIMESTAMP";
}
//make sure the NOT NULL is AFTER the default
addColumn = addColumn.Replace(" NOT NULL ", " ") + " NOT NULL";
}
return string.Format(CultureInfo.InvariantCulture,
"ALTER TABLE {0} ADD {1}",
TableName(databaseTable),
addColumn) + LineEnding();
}
protected override string WriteDataType(DatabaseColumn column)
{
var type = new DataTypeWriter().WriteDataType(column);
type += (!column.Nullable ? " NOT NULL" : string.Empty);
var defaultValue = column.DefaultValue;
if (!string.IsNullOrEmpty(defaultValue))
{
defaultValue = FixDefaultValue(defaultValue);
const string defaultConstraint = " DEFAULT ";
if (IsStringColumn(column))
{
type += defaultConstraint + "'" + defaultValue + "'";
}
else //numeric default
{
type += defaultConstraint + defaultValue;
}
}
//MySql auto-increments MUST BE primary key
if (column.IsAutoNumber) type += " AUTO_INCREMENT PRIMARY KEY";
else if (column.IsPrimaryKey && Table.PrimaryKey.Columns.Count == 1)
type += " PRIMARY KEY";
return type;
}
public override string DropColumn(DatabaseTable databaseTable, DatabaseColumn databaseColumn)
{
return string.Format(CultureInfo.InvariantCulture,
"ALTER TABLE {0} DROP COLUMN {1} CASCADE;",
TableName(databaseTable),
Escape(databaseColumn.Name));
}
public override string WriteDataType(DatabaseColumn column)
{
var defaultValue = string.Empty;
if (!string.IsNullOrEmpty(column.DefaultValue))
{
defaultValue = WriteDefaultValue(column);
}
var sql = DataTypeWriter.WriteDataType(column);
if (sql == "BIT") _hasBit = true;
if (column.IsAutoNumber)
{
var id = column.IdentityDefinition ?? new DatabaseColumnIdentity();
bool isLong = column.DataType != null && column.DataType.GetNetType() == typeof(long);
// Non trivial identities are hooked to a sequence up by AutoIncrementWriter.
// Newer postgres versions require specifying UNIQUE explicitly.
if (id.IsNonTrivialIdentity())
sql = (isLong ? " BIGINT" : " INT") + " NOT NULL UNIQUE";
else
sql = isLong ? " BIGSERIAL" : " SERIAL";
}
else
{
if (column.IsPrimaryKey)
sql += " NOT NULL";
else
sql += " " + (!column.Nullable ? " NOT NULL" : string.Empty) + defaultValue;
}
return sql;
}
public override string AlterColumn(DatabaseTable databaseTable, DatabaseColumn databaseColumn, DatabaseColumn originalColumn)
{
var sb = new StringBuilder();
var defaultName = "DF_" + databaseTable.Name + "_" + databaseColumn.Name;
if (originalColumn != null)
{
if (originalColumn.DefaultValue != null)
{
//have to drop default contraint
var df = FindDefaultConstraint(databaseTable, databaseColumn.Name);
if (df != null)
{
defaultName = df.Name;
sb.AppendLine("ALTER TABLE " + TableName(databaseTable)
+ " DROP CONSTRAINT " + Escape(defaultName) + ";");
}
}
}
//we could check if any of the properties are changed here
sb.AppendLine(base.AlterColumn(databaseTable, databaseColumn, originalColumn));
if (databaseColumn.DefaultValue != null)
{
//add default contraint
sb.AppendLine("ALTER TABLE " + TableName(databaseTable) +
" ADD CONSTRAINT " + Escape(defaultName) +
" DEFAULT " + databaseColumn.DefaultValue +
" FOR " + Escape(databaseColumn.Name) + ";");
}
return sb.ToString();
}
private static bool IsStringColumn(DatabaseColumn column)
{
var dataType = column.DbDataType.ToUpperInvariant();
var isString = (dataType == "NVARCHAR" || dataType == "VARCHAR" || dataType == "CHAR");
var dt = column.DataType;
if (dt != null && dt.IsString) isString = true;
return isString;
}
private void ConvertDataTable()
{
var columnsKeyMap = new ColumnsKeyMap(ColumnsDataTable);
var hasIsUnsigned = !string.IsNullOrEmpty(columnsKeyMap.IsUnsignedKey);
foreach (DataRowView row in ColumnsDataTable.DefaultView)
{
var column = new DatabaseColumn();
column.Name = row[columnsKeyMap.Key].ToString();
column.TableName = row[columnsKeyMap.TableKey].ToString();
if (!string.IsNullOrEmpty(columnsKeyMap.SchemaKey))
column.SchemaOwner = row[columnsKeyMap.SchemaKey].ToString();
if (string.Equals("sqlite_default_schema", column.SchemaOwner, StringComparison.OrdinalIgnoreCase))
column.SchemaOwner = string.Empty;
if (!string.IsNullOrEmpty(columnsKeyMap.OrdinalKey))
column.Ordinal = Convert.ToInt32(row[columnsKeyMap.OrdinalKey], CultureInfo.CurrentCulture);
if (!string.IsNullOrEmpty(columnsKeyMap.DatatypeKey))
column.DbDataType = row[columnsKeyMap.DatatypeKey].ToString();
if (hasIsUnsigned && CastToBoolean(row, columnsKeyMap.IsUnsignedKey))
column.DbDataType += " unsigned";
AddNullability(row, columnsKeyMap.NullableKey, column);
//the length unless it's an OleDb blob or clob
if (!string.IsNullOrEmpty(columnsKeyMap.LengthKey))
column.Length = GetNullableInt(row[columnsKeyMap.LengthKey]);
if (!string.IsNullOrEmpty(columnsKeyMap.DataLengthKey))
{
//oracle only
var dataLength = GetNullableInt(row[columnsKeyMap.DataLengthKey]);
//column length already set for char/varchar. For other data types, get data length
if (column.Length < 1)
column.Length = dataLength;
}
if (!string.IsNullOrEmpty(columnsKeyMap.PrecisionKey))
column.Precision = GetNullableInt(row[columnsKeyMap.PrecisionKey]);
if (!string.IsNullOrEmpty(columnsKeyMap.ScaleKey))
column.Scale = GetNullableInt(row[columnsKeyMap.ScaleKey]);
if (columnsKeyMap.DateTimePrecision != null)
{
column.DateTimePrecision = GetNullableInt(row[columnsKeyMap.DateTimePrecision]);
}
AddColumnDefault(row, columnsKeyMap.DefaultKey, column);
if (!string.IsNullOrEmpty(columnsKeyMap.PrimaryKeyKey) && (bool)row[columnsKeyMap.PrimaryKeyKey])
column.IsPrimaryKey = true;
if (!string.IsNullOrEmpty(columnsKeyMap.AutoIncrementKey) && (bool)row[columnsKeyMap.AutoIncrementKey])
column.IsAutoNumber = true;
if (!string.IsNullOrEmpty(columnsKeyMap.UniqueKey) && CastToBoolean(row, columnsKeyMap.UniqueKey))
column.IsUniqueKey = true;
_list.Add(column);
}
// Sort columns according to ordinal to get the original order in CREATE TABLE
_list.Sort((x, y) => x.Ordinal.CompareTo(y.Ordinal));
}
public static bool IsString(DatabaseColumn column, string dataType)
{
//all aliases for CHAR and VARCHAR. there's also a LONG VARCHAR
if (DataTypeConverter.IsVariableString(dataType) ||
DataTypeConverter.IsFixedLengthString(dataType))
return true;
if (column.DataType == null) return false;
return column.DataType.IsString;
}
public override string WriteDataType(DatabaseColumn column)
{
var dataType = base.WriteDataType(column);
if (dataType == "TEXT")
{
dataType = "NTEXT";
}
return dataType;
}
public override string WriteDataType(DatabaseColumn column)
{
if (column.IsComputed)
{
//Generated Always and Virtual are optional keywords
return "GENERATED ALWAYS AS " + column.ComputedDefinition + " VIRTUAL";
}
return new DataTypeWriter().WriteDataType(column);
}
public static bool IsSqlServerTimestamp(string dataType, DatabaseColumn column)
{
if (!dataType.StartsWith("TIMESTAMP", StringComparison.OrdinalIgnoreCase))
return false;
int providerType = -1;
if (column.DataType != null)
providerType = column.DataType.ProviderDbType;
return (providerType == (int)SqlDbType.Timestamp); //this is just a byte array
}
private static string OtherDatabaseTypesToPostgreSql(string dataType, DatabaseColumn column)
{
//string types
//character(n) (aka char(n)) character varying(n) aka varchar(n) and text
if (DataTypeConverter.IsFixedLengthString(dataType))
{
return "CHAR";
}
if (DataTypeConverter.IsLongString(dataType))
{
return "TEXT";
}
if (DataTypeConverter.IsVariableString(dataType))
{
if (column.Length == -1) return "TEXT";
return "VARCHAR";
}
//numeric types
if (dataType == "INT") return "INTEGER";
if (dataType == "INT4") return "INTEGER"; //this is a PostgreSql alias, we'll use standard SQL
//else if (dataType == "SERIAL") return "INTEGER"; //this is a PostgreSql alias, we'll use standard SQL
//else if (dataType == "BIGSERIAL") return "BIGINT"; //this is a PostgreSql alias, we'll use standard SQL
if (dataType == "INT8") return "BIGINT"; //this is a PostgreSql alias, we'll use standard SQL
if (dataType == "INT2") return "SMALLINT"; //this is a PostgreSql alias, we'll use standard SQL
if (dataType == "TINYINT") return "SMALLINT"; //this is a MsSql alias, we'll use standard SQL
if (dataType == "NUMBER")
return DataTypeConverter.OracleNumberConversion(column.Precision, column.Scale);
//float and real
if (dataType == "FLOAT4") return "REAL"; //this is a PostgreSql alias, we'll use standard SQL
if (dataType == "FLOAT") return "DOUBLE PRECISION";
//date times
//SqlServer Timestamp is a binary
if (DataTypeConverter.IsSqlServerTimestamp(dataType, column))
return "BYTEA"; //this is just a byte array- functionally you should redesign the table and perhaps use the system extension columns
if (DataTypeConverter.IsDateTime(dataType))
return "TIMESTAMP";
//bytes
if (DataTypeConverter.IsBlob(dataType, column.Length))
return "OID";//blobs become object ids
if (DataTypeConverter.IsBinary(dataType))
{
return "BYTEA";
}
//there is a native BIT(n) type in Postgresql, but in conversion we probably mean boolean.
if (dataType == "BIT" && !column.Length.HasValue) return "BOOLEAN";
//other types
if (dataType == "XMLTYPE") return "XML";
if (dataType == "UNIQUEIDENTIFIER") return "UUID";
return dataType;
}
/// <summary>
/// Creates sample data from a <see cref="DatabaseSchemaReader.DataSchema.DataType"/>
/// </summary>
/// <param name="column">The column.</param>
/// <returns></returns>
/// <exception cref="InvalidOperationException">Column is a non-nullable foreign key</exception>
public static object CreateData(DatabaseColumn column)
{
if (column.IsForeignKey)
{
if (column.Nullable)
return null;
throw new InvalidOperationException("Column is a non-nullable foreign key - cannot generate data");
}
return CreateData(column.DataType, column.Length, column.Precision, column.Scale);
}
/// <summary>
/// If a table has a trigger, we assume it's an Oracle trigger/sequence which is translated to identity for the primary key
/// </summary>
/// <param name="table">The table.</param>
/// <param name="column">The column.</param>
/// <returns></returns>
public static bool LooksLikeOracleIdentityColumn(DatabaseTable table, DatabaseColumn column)
{
if (!column.IsPrimaryKey) return false;
if (table.Triggers.Count == 0) return false;
//is there a trigger body which looks like it's using a sequence?
//if there's a sequence there, it's autogenerating a column - we assume the primary key!
return table.Triggers.Any(t => t.TriggerBody
.ToUpperInvariant()
.Contains(".NEXTVAL "));
}
public void TypeInteger()
{
var typewriter = new DataTypeWriter();
var column = new DatabaseColumn();
column.DataType = new DataType("NUMBER", "System.Int32");
var result = typewriter.Write(column);
Assert.AreEqual("int", result);
}
public void TestDataTypeDefinitionForInt()
{
//arrange
var column = new DatabaseColumn { DbDataType = "int", DataType = new DataType("int", "int") };
//act
var result = column.DataTypeDefinition();
//assert
Assert.AreEqual("int", result);
}
public void TypeString()
{
var typewriter = new DataTypeWriter();
var column = new DatabaseColumn();
column.DataType = new DataType("VARCHAR2", "System.String");
var result = typewriter.Write(column);
Assert.AreEqual("string", result);
}
private static string OtherDatabaseTypesToDb2(string dataType, DatabaseColumn column)
{
//string types
if (DataTypeConverter.IsFixedLengthString(dataType))
{
return "CHAR";
}
if (DataTypeConverter.IsLongString(dataType))
{
return dataType == "CLOB" ? "CLOB" : "DBCLOB";
}
if (DataTypeConverter.IsVariableString(dataType))
{
if (column.Length == -1) return "DBCLOB";
return "VARCHAR";
}
//numeric types
if (dataType == "SMALLINT") return dataType;
if (dataType == "BIGINT") return dataType;
if (dataType == "INTEGER") return dataType;
if (dataType == "INT") return "INTEGER";
if (dataType == "NUM") return "NUMERIC"; //DB2 alias
if (dataType == "DEC") return "DECIMAL"; //DB2 alias
if (dataType == "MONEY") return "DECIMAL(19,4)";
if (dataType == "BIT") return "SMALLINT"; //could be CHAR(1) but nicer with an integer
if (dataType == "NUMBER")
return DataTypeConverter.OracleNumberConversion(column.Precision, column.Scale);
//date times
//SqlServer Timestamp is a binary
if (DataTypeConverter.IsSqlServerTimestamp(dataType, column))
return "GRAPHIC";
if (DataTypeConverter.IsDateTime(dataType))
return "TIMESTAMP";
//bytes
if (DataTypeConverter.IsBlob(dataType, column.Length))
return "BLOB";
if (DataTypeConverter.IsBinary(dataType))
{
if (dataType == "LONG VARGRAPHIC") return dataType;
if (dataType == "GRAPHIC") return dataType;
return "VARGRAPHIC";
}
//other types
if (dataType == "XMLTYPE") return "XML";
if (dataType == "UNIQUEIDENTIFIER") return "CHAR(16) FOR BIT DATA";
return dataType;
}
private static void ConvertIndexes(DataTable dt, ICollection<DatabaseIndex> indexes)
{
if (dt == null) return;
//Npgsql
if (dt.Columns.Count == 0) return;
var indexKeyMap = new IndexKeyMap(dt);
foreach (DataRowView row in dt.DefaultView)
{
string name = row[indexKeyMap.Key].ToString();
if (string.IsNullOrEmpty(name)) continue; //all indexes should have a name
string schema = !String.IsNullOrEmpty(indexKeyMap.SchemaKey) ? row[indexKeyMap.SchemaKey].ToString() : String.Empty;
var tableName = row[indexKeyMap.TableKey].ToString();
var c = indexes.FirstOrDefault(f => f.Name == name && f.SchemaOwner == schema && f.TableName.Equals(tableName, StringComparison.OrdinalIgnoreCase));
if (c == null)
{
c = new DatabaseIndex();
c.Name = name;
c.SchemaOwner = schema;
c.TableName = tableName;
if (indexKeyMap.Typekey != null)
c.IndexType = row[indexKeyMap.Typekey].ToString();
if (FindBoolean(row, indexKeyMap.UniqueKey, "UNIQUE"))
{
c.IsUnique = true;
c.IndexType = "UNIQUE";
}
if (FindBoolean(row, indexKeyMap.PrimaryKey, String.Empty))
c.IndexType = "PRIMARY"; //primary keys should be unique too
indexes.Add(c);
}
if (string.IsNullOrEmpty(indexKeyMap.ColumnKey)) continue;
string colName = row[indexKeyMap.ColumnKey].ToString();
if (string.IsNullOrEmpty(colName)) continue;
var column = new DatabaseColumn();
column.Name = colName;
if (!string.IsNullOrEmpty(indexKeyMap.OrdinalKey))
{
int ordinal = Convert.ToInt32(row[indexKeyMap.OrdinalKey], CultureInfo.CurrentCulture);
column.Ordinal = ordinal;
}
if (ContainsColumn(c.Columns, colName)) continue;
c.Columns.Add(column);
if (c.Columns.Count > 1 && column.Ordinal != 0)
{
//the order of the datatable may be wrong
c.Columns.Sort((x, y) => x.Ordinal.CompareTo(y.Ordinal));
}
}
}
/// <summary>
/// For a column, returns the property name for a primary key
/// </summary>
/// <param name="column">The column.</param>
/// <returns></returns>
public virtual string PrimaryKeyName(DatabaseColumn column)
{
var primaryKeyName = column.NetName;
if (column.IsPrimaryKey && column.IsForeignKey)
{
//a foreign key will be written, so we need to avoid a collision
var refTable = column.ForeignKeyTable;
var fkDataType = refTable != null ? refTable.NetName : column.ForeignKeyTableName;
if (fkDataType == primaryKeyName)
primaryKeyName += "Id";
}
return primaryKeyName;
}
public static string OracleDataTypeForParameter(DatabaseColumn column)
{
if (column == null) return string.Empty;
if (string.IsNullOrEmpty(column.DbDataType)) return string.Empty;
var dataType = column.DbDataTypeStandard();
int providerType = GetProviderType(column);
var length = column.Length;
//oracle to sql server translation
dataType = SqlServerToOracleConversion(dataType, providerType, length);
return dataType;
}
public override string AddColumn(DatabaseTable databaseTable, DatabaseColumn databaseColumn)
{
var tableGenerator = CreateTableGenerator(databaseTable);
var addColumn = tableGenerator.WriteColumn(databaseColumn).Trim();
if (string.IsNullOrEmpty(databaseColumn.DefaultValue) && !databaseColumn.Nullable)
{
//for strings, a zero length string; for numbers, 0; for dates, current timestamp
addColumn += " DEFAULT";
}
return string.Format(CultureInfo.InvariantCulture,
"ALTER TABLE {0} ADD {1}",
TableName(databaseTable),
addColumn) + LineEnding();
}
private static string WriteSequence(DatabaseColumn column)
{
const string sequence = @"CREATE SEQUENCE ""{0}"" MINVALUE {1} START {1} INCREMENT {2};
ALTER TABLE ""{3}"" ALTER COLUMN ""{4}"" SET DEFAULT nextval('""{0}""');";
var id = column.IdentityDefinition;
return string.Format(CultureInfo.InvariantCulture,
sequence,
GetSequenceName(column),
id.IdentitySeed,
id.IdentityIncrement,
column.TableName,
column.Name
);
}
public override string DropDefault(DatabaseTable databaseTable, DatabaseColumn databaseColumn)
{
//there is no "DROP DEFAULT" in SqlServer (there is in SQLServer CE).
//You must use the default constraint name (which is probably autogenerated)
var sb = new StringBuilder();
sb.AppendLine("-- drop default for " + databaseColumn.Name);
var df = FindDefaultConstraint(databaseTable, databaseColumn.Name);
if (df != null)
{
sb.AppendLine("ALTER TABLE " + TableName(databaseTable)
+ " DROP CONSTRAINT " + Escape(df.Name) + ";");
}
return sb.ToString();
}
/// <summary>
/// Adds the column.
/// </summary>
/// <param name="databaseConstraint">The database constraint.</param>
/// <param name="databaseColumn">The database column.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">databaseConstraint</exception>
public static DatabaseConstraint AddColumn(this DatabaseConstraint databaseConstraint, DatabaseColumn databaseColumn)
{
if (databaseConstraint == null)
{
throw new ArgumentNullException("databaseConstraint");
}
if (databaseColumn == null)
{
throw new ArgumentNullException("databaseColumn");
}
if (databaseColumn.TableName != databaseConstraint.TableName)
{
throw new InvalidOperationException("Constraint and column must belong to same table");
}
databaseConstraint.Columns.Add(databaseColumn.Name);
switch (databaseConstraint.ConstraintType)
{
case ConstraintType.ForeignKey:
databaseColumn.IsForeignKey = true;
databaseColumn.ForeignKeyTableName = databaseConstraint.RefersToTable;
break;
case ConstraintType.PrimaryKey:
databaseColumn.IsPrimaryKey = true;
break;
case ConstraintType.UniqueKey:
databaseColumn.IsUniqueKey = true;
break;
}
return(databaseConstraint);
}
/// <summary>
/// Adds a foreign key with a single column
/// </summary>
/// <param name="databaseColumn">The database column.</param>
/// <param name="foreignKeyName">Name of the foreign key.</param>
/// <param name="foreignTableName">Name of the foreign table.</param>
/// <returns></returns>
public static DatabaseColumn AddForeignKey(this DatabaseColumn databaseColumn,
string foreignKeyName,
string foreignTableName)
{
return(AddForeignKey(databaseColumn, foreignKeyName, foreignTableName, null));
}
/// <summary>
/// Makes this column the primary key (or part of a composite key)
/// </summary>
/// <param name="databaseColumn">The database column.</param>
/// <returns></returns>
public static DatabaseColumn AddPrimaryKey(this DatabaseColumn databaseColumn)
{
return(databaseColumn.AddPrimaryKey(null));
}
/// <summary>
/// Adds a foreign key with a single column (without a name)
/// </summary>
/// <param name="databaseColumn">The database column.</param>
/// <param name="foreignTableName">Name of the foreign table.</param>
/// <returns></returns>
public static DatabaseColumn AddForeignKey(this DatabaseColumn databaseColumn, string foreignTableName)
{
return(databaseColumn.AddForeignKey(null, foreignTableName));
}
/// <summary>
/// Makes this column a unique key.
/// </summary>
/// <param name="databaseColumn">The database column.</param>
/// <returns></returns>
public static DatabaseColumn AddUniqueKey(this DatabaseColumn databaseColumn)
{
return(databaseColumn.AddUniqueKey(null));
}