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); }