public void OracleCreatesSql()
{
var mapper = new ColumnPropertiesMapper(new OracleDialect(), "varchar(30)");
mapper.MapColumnProperties(new Column("foo", DbType.String, 0));
Assert.AreEqual("foo varchar(30)", mapper.ColumnSql);
}
public void OracleIndexSqlIsNoNullWhenIndexed()
{
var mapper = new ColumnPropertiesMapper(new OracleDialect(), "char(1)");
mapper.MapColumnProperties(new Column("foo", DbType.StringFixedLength, 1, ColumnProperty.Indexed));
Assert.IsNotNull(mapper.IndexSql);
}
public void SqlServerCreatesSql()
{
var mapper = new ColumnPropertiesMapper(new SqlServerDialect(), "varchar(30)");
mapper.MapColumnProperties(new Column("foo", DbType.String, 0));
Assert.AreEqual("[foo] varchar(30)", mapper.ColumnSql);
}
public void PostgresIndexSqlIsNullWhenIndexedFalse()
{
var mapper = new ColumnPropertiesMapper(new PostgreSQLDialect(), "char(1)");
mapper.MapColumnProperties(new Column("foo", DbType.StringFixedLength, 1, 0));
Assert.IsNull(mapper.IndexSql);
}
public void SqlServerCreatesNotNullSql()
{
var mapper = new ColumnPropertiesMapper(new SqlServerDialect(), "varchar(30)");
mapper.MapColumnProperties(new Column("foo", DbType.String, ColumnProperty.NotNull));
Assert.AreEqual("foo varchar(30) NOT NULL", mapper.ColumnSql);
}
public void SqlServerCreatesSqWithNullDefault()
{
var mapper = new ColumnPropertiesMapper(new SqlServerDialect(), "varchar(30)");
mapper.MapColumnProperties(new Column("foo", DbType.String, 0, "NULL"));
Assert.AreEqual("foo varchar(30) DEFAULT NULL", mapper.ColumnSql);
}
public override void ChangeColumn(string table, Column column)
{
if (column.DefaultValue == null || column.DefaultValue == DBNull.Value)
{
base.ChangeColumn(table, column);
}
else
{
var def = column.DefaultValue;
var notNull = column.ColumnProperty.IsSet(ColumnProperty.NotNull);
column.DefaultValue = null;
column.ColumnProperty = column.ColumnProperty.Set(ColumnProperty.Null);
column.ColumnProperty = column.ColumnProperty.Clear(ColumnProperty.NotNull);
base.ChangeColumn(table, column);
ColumnPropertiesMapper mapper = _dialect.GetAndMapColumnPropertiesWithoutDefault(column);
ExecuteNonQuery(string.Format("ALTER TABLE {0} ADD CONSTRAINT {1} {2} FOR {3}", this.QuoteTableNameIfRequired(table), "DF_" + table + "_" + column.Name, _dialect.Default(def), this.QuoteColumnNameIfRequired(column.Name)));
if (notNull)
{
column.ColumnProperty = column.ColumnProperty.Set(ColumnProperty.NotNull);
column.ColumnProperty = column.ColumnProperty.Clear(ColumnProperty.Null);
base.ChangeColumn(table, column);
}
}
}
public void SQLiteIndexSqlWithEmptyStringDefault()
{
var mapper = new ColumnPropertiesMapper(new SQLiteDialect(), "varchar(30)");
mapper.MapColumnProperties(new Column("foo", DbType.String, 1, ColumnProperty.NotNull, string.Empty));
Assert.AreEqual("foo varchar(30) NOT NULL DEFAULT ''", mapper.ColumnSql);
}
public override void ChangeColumn(string table, Column column)
{
var oldColumn = GetColumnByName(table, column.Name);
var isUniqueSet = column.ColumnProperty.IsSet(ColumnProperty.Unique);
column.ColumnProperty = column.ColumnProperty.Clear(ColumnProperty.Unique);
if (!ColumnExists(table, column.Name))
{
Logger.Warn("Column {0}.{1} does not exist", table, column.Name);
return;
}
ColumnPropertiesMapper mapper = _dialect.GetAndMapColumnProperties(column);
string change1 = string.Format("{0} TYPE {1}", QuoteColumnNameIfRequired(mapper.Name), mapper.type);
#region Field Type Converters...
if ((oldColumn.Type == DbType.Int16 || oldColumn.Type == DbType.Int32 || oldColumn.Type == DbType.Int64 || oldColumn.Type == DbType.Decimal) && column.Type == DbType.Boolean)
{
change1 += string.Format(" USING CASE {0} WHEN 1 THEN true ELSE false END", QuoteColumnNameIfRequired(mapper.Name));
}
else if (column.Type == DbType.Boolean)
{
change1 += string.Format(" USING CASE {0} WHEN '1' THEN true ELSE false END", QuoteColumnNameIfRequired(mapper.Name));
}
#endregion
ChangeColumn(table, change1);
if (mapper.Default != null)
{
string change2 = string.Format("{0} SET {1}", QuoteColumnNameIfRequired(mapper.Name), _dialect.Default(mapper.Default));
ChangeColumn(table, change2);
}
else
{
string change2 = string.Format("{0} DROP DEFAULT", QuoteColumnNameIfRequired(mapper.Name));
ChangeColumn(table, change2);
}
if (column.ColumnProperty.HasFlag(ColumnProperty.NotNull))
{
string change3 = string.Format("{0} SET NOT NULL", QuoteColumnNameIfRequired(mapper.Name));
ChangeColumn(table, change3);
}
else
{
string change3 = string.Format("{0} DROP NOT NULL", QuoteColumnNameIfRequired(mapper.Name));
ChangeColumn(table, change3);
}
if (isUniqueSet)
{
AddUniqueConstraint(string.Format("UX_{0}_{1}", table, column.Name), table, new string[] { column.Name });
}
}
public void SqlServerCreatesSqWithBooleanDefault()
{
ColumnPropertiesMapper mapper = new ColumnPropertiesMapper(new SqlServerDialect(), "bit");
mapper.MapColumnProperties(new Column("foo", DbType.Boolean, 0, false));
Assert.AreEqual("foo bit DEFAULT 0", mapper.ColumnSql);
mapper.MapColumnProperties(new Column("bar", DbType.Boolean, 0, true));
Assert.AreEqual("bar bit DEFAULT 1", mapper.ColumnSql);
}
public void SqlServerCreatesSqWithBooleanDefault()
{
var mapper = new ColumnPropertiesMapper(new SqlServerDialect(), "bit");
mapper.MapColumnProperties(new Column("foo", DbType.Boolean, 0, false));
Assert.AreEqual("foo bit DEFAULT 0", mapper.ColumnSql);
mapper.MapColumnProperties(new Column("bar", DbType.Boolean, 0, true));
Assert.AreEqual("bar bit DEFAULT 1", 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;
}
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)
{
using (var cmd = CreateCommand())
ExecuteQuery(cmd, 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 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"));
}
/// <summary>
/// Add a new column to an existing table.
/// </summary>
/// <param name = "table">Table to which to add the column</param>
/// <param name = "column">Column name</param>
/// <param name = "type">Date type of the column</param>
/// <param name = "size">Max length of the column</param>
/// <param name = "property">Properties of the column, see <see cref = "ColumnProperty">ColumnProperty</see>,</param>
/// <param name = "defaultValue">Default value</param>
public override void AddColumn(string table, string column, DbType type, int size, ColumnProperty property,
object defaultValue)
{
table = CleanTableName(table);
if (ColumnExists(table, column))
{
Logger.Warn("Column {0}.{1} already exists", table, column);
return;
}
ColumnPropertiesMapper mapper =
_dialect.GetAndMapColumnProperties(new Column(column, type, size, property, defaultValue));
AddColumn(table, mapper.ColumnSql);
}
public void OracleIndexSqlIsNoNullWhenIndexed()
{
ColumnPropertiesMapper mapper = new ColumnPropertiesMapper(new OracleDialect(), "char(1)");
mapper.MapColumnProperties(new Column("foo", DbType.StringFixedLength, 1, ColumnProperty.Indexed));
Assert.IsNotNull(mapper.IndexSql);
}
public void OracleCreatesNotNullSql()
{
ColumnPropertiesMapper mapper = new ColumnPropertiesMapper(new OracleDialect(), "varchar(30)");
mapper.MapColumnProperties(new Column("foo", DbType.String, ColumnProperty.NotNull));
Assert.AreEqual("foo varchar(30) NOT NULL", mapper.ColumnSql);
}
public override void AddTable(string name, string engine, params IDbField[] fields)
{
if (TableExists(name))
{
Logger.Warn("Table {0} already exists", name);
return;
}
var columns = fields.Where(x => x is Column).Cast <Column>().ToArray();
List <string> pks = GetPrimaryKeys(columns);
bool compoundPrimaryKey = pks.Count > 1;
var columnProviders = new List <ColumnPropertiesMapper>(columns.Length);
foreach (Column column in columns)
{
// Remove the primary key notation if compound primary key because we'll add it back later
if (compoundPrimaryKey && column.IsPrimaryKey)
{
column.ColumnProperty = column.ColumnProperty ^ ColumnProperty.PrimaryKey;
column.ColumnProperty = column.ColumnProperty | ColumnProperty.NotNull; // PK is always not-null
}
ColumnPropertiesMapper mapper = _dialect.GetAndMapColumnProperties(column);
columnProviders.Add(mapper);
}
string columnsAndIndexes = JoinColumnsAndIndexes(columnProviders);
var table = _dialect.TableNameNeedsQuote ? _dialect.Quote(name) : QuoteTableNameIfRequired(name);
string sqlCreate;
sqlCreate = String.Format("CREATE TABLE {0} ({1}", table, columnsAndIndexes);
if (compoundPrimaryKey)
{
sqlCreate += String.Format(", PRIMARY KEY ({0}) ", String.Join(",", pks.ToArray()));
}
var uniques = fields.Where(x => x is Unique).Cast <Unique>().ToArray();
foreach (var u in uniques)
{
var nm = "";
if (!string.IsNullOrEmpty(u.Name))
{
nm = string.Format(" CONSTRAINT {0}", u.Name);
}
sqlCreate += String.Format(",{0} UNIQUE ({1})", nm, String.Join(",", u.KeyColumns));
}
var foreignKeys = fields.Where(x => x is ForeignKeyConstraint).Cast <ForeignKeyConstraint>().ToArray();
foreach (var fk in foreignKeys)
{
var nm = "";
if (!string.IsNullOrEmpty(fk.Name))
{
nm = string.Format(" CONSTRAINT {0}", fk.Name);
}
sqlCreate += String.Format(",{0} FOREIGN KEY ({1}) REFERENCES {2}({3})", nm, String.Join(",", fk.Columns), fk.PkTable, String.Join(",", fk.PkColumns));
}
//table = QuoteTableNameIfRequired(table);
//ExecuteNonQuery(String.Format("ALTER TABLE {0} ADD CONSTRAINT {1} UNIQUE({2}) ", table, name, string.Join(", ", columns)));
sqlCreate += ")";
ExecuteNonQuery(sqlCreate);
var indexes = fields.Where(x => x is Index).Cast <Index>().ToArray();
foreach (var index in indexes)
{
AddIndex(name, index);
}
}
public void PostgresIndexSqlIsNullWhenIndexedFalse()
{
ColumnPropertiesMapper mapper = new ColumnPropertiesMapper(new PostgreSQLDialect(), "char(1)");
mapper.MapColumnProperties(new Column("foo", DbType.StringFixedLength, 1, 0));
Assert.IsNull(mapper.IndexSql);
}
public void SqlServerCreatesSql()
{
ColumnPropertiesMapper mapper = new ColumnPropertiesMapper(new SqlServerDialect(), "varchar(30)");
mapper.MapColumnProperties(new Column("foo", DbType.String, 0));
Assert.AreEqual("foo varchar(30)", mapper.ColumnSql);
}
public void SqlServerCreatesNotNullSql()
{
var mapper = new ColumnPropertiesMapper(new SqlServerDialect(), "varchar(30)");
mapper.MapColumnProperties(new Column("foo", DbType.String, ColumnProperty.NotNull));
Assert.AreEqual("[foo] varchar(30) NOT NULL", mapper.ColumnSql);
}
public void SqlServerCreatesSqWithDefault()
{
ColumnPropertiesMapper mapper = new ColumnPropertiesMapper(new SqlServerDialect(), "varchar(30)");
mapper.MapColumnProperties(new Column("foo", DbType.String, 0, "'NEW'"));
Assert.AreEqual("foo varchar(30) DEFAULT 'NEW'", mapper.ColumnSql);
}
public void SQLiteIndexSqlWithEmptyStringDefault()
{
var mapper = new ColumnPropertiesMapper(new SQLiteDialect(), "varchar(30)");
mapper.MapColumnProperties(new Column("foo", DbType.String, 1, ColumnProperty.NotNull, string.Empty));
Assert.AreEqual("foo varchar(30) NOT NULL DEFAULT ''", mapper.ColumnSql);
}
