protected override string FormatNullable(ColumnDefinition column)
{
if (column.IsNullable.GetValueOrDefault())
return column.ModificationType == ColumnModificationType.Alter ? "NULL" : string.Empty;
return "NOT NULL";
}
public static bool ColumnTypesMatch(ColumnDefinition col1, ColumnDefinition col2)
{
FirebirdColumn column = new FirebirdColumn(new FirebirdOptions());
string colDef1 = column.GenerateForTypeAlter(col1);
string colDef2 = column.GenerateForTypeAlter(col2);
return colDef1 == colDef2;
}
protected void WriteColumn(ColumnDefinition column, StreamWriter output, bool isLastColumn)
{
string columnSyntax = ".WithColumn(\"" + column.Name + "\")";
switch (column.Type)
{
case DbType.Boolean:
columnSyntax += ".AsBoolean()";
break;
case DbType.Int16:
columnSyntax += ".AsInt16()";
break;
case DbType.Int32:
columnSyntax += ".AsInt32()";
break;
default:
columnSyntax += ".AsString()";
break;
}
if (column.IsIdentity)
columnSyntax += ".Identity()";
else if (column.IsIndexed)
columnSyntax += ".Indexed()";
if (column.IsNullable == false)
columnSyntax += ".NotNullable()";
if (isLastColumn) columnSyntax += ";";
output.WriteLine("\t\t\t\t" + columnSyntax);
}
public string GenerateAlterClause(ColumnDefinition column)
{
if (column.IsIdentity)
{
throw new NotSupportedException("Altering an identity column is not supported.");
}
var alterClauses = AlterClauseOrder.Aggregate(new StringBuilder(), (acc, newRow) =>
{
var clause = newRow(column);
if (acc.Length == 0)
{
acc.Append(newRow(column));
}
else if (!string.IsNullOrEmpty(clause))
{
acc.Append(clause.PadLeft(clause.Length + 1));
}
return acc;
});
return string.Format(
"ALTER COLUMN {0} SET DATA TYPE {1}",
Quoter.QuoteColumnName(column.Name),
alterClauses);
}
public virtual string GenerateDDLForColumn(ColumnDefinition column)
{
var sb = new StringBuilder();
sb.Append(column.Name);
sb.Append(" ");
sb.Append(GetTypeMap(column.Type.Value, column.Size, column.Precision));
if (!column.IsNullable)
{
sb.Append(" NOT NULL");
}
if (column.DefaultValue != null)
{
sb.Append(" DEFAULT ");
sb.Append(GetConstantValue(column.DefaultValue));
}
if (column.IsIdentity)
{
sb.Append(" IDENTITY(1,1)");
}
if (column.IsPrimaryKey)
{
sb.Append(" PRIMARY KEY CLUSTERED");
}
return sb.ToString();
}
protected override string FormatType(ColumnDefinition column)
{
if (column.IsIdentity)
return GetTypeMap(DbType.Int32, column.Size, column.Precision);
return base.FormatType(column);
}
protected virtual string FormatType(ColumnDefinition column)
{
if (!column.Type.HasValue)
return column.CustomType;
return GetTypeMap(column.Type.Value, column.Size, column.Precision);
}
public void Truncate(ColumnDefinition column)
{
column.Name = Truncate(column.Name);
column.TableName = Truncate(column.TableName);
if (column.IsPrimaryKey)
column.PrimaryKeyName = packKeyNames ? Pack(column.PrimaryKeyName) : Truncate(column.PrimaryKeyName);
}
protected override string FormatIdentity(ColumnDefinition column)
{
if (column.IsIdentity)
{
//todo: would like to throw a warning here
}
return string.Empty;
}
public void CanAddColumnWithGetDateDefault()
{
ColumnDefinition column = new ColumnDefinition { Name = "TestColumn1", Type = DbType.String,
Size = 5, DefaultValue = "GetDate()" };
var expression = new CreateColumnExpression { TableName = "TestTable1", Column = column };
var sql = _generator.Generate(expression);
sql.ShouldBe("ALTER TABLE [TestTable1] ADD [TestColumn1] NVARCHAR(5) NOT NULL DEFAULT GetDate()");
}
protected override string FormatPrimaryKey(ColumnDefinition column)
{
if (!column.IsPrimaryKey)
return string.Empty;
//Assume that if its IDENTITY and PRIMARY KEY, the it should be an AUTOINCREMENT column
return !column.IsIdentity ? "PRIMARY KEY" : "PRIMARY KEY AUTOINCREMENT";
}
public void CanUseSystemMethodCurrentDateTimeAsADefaultValueForAColumn()
{
var columnDefinition = new ColumnDefinition { Name = "NewColumn", Size = 15, Type = null, CustomType = "TIMESTAMP", DefaultValue = SystemMethods.CurrentDateTime };
var expression = new CreateColumnExpression { Column = columnDefinition, TableName = "NewTable" };
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE `NewTable` ADD COLUMN `NewColumn` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP");
}
protected override string FormatIdentity(ColumnDefinition column)
{
if (column.IsIdentity)
{
throw new DatabaseOperationNotSupportedException("Oracle does not support identity columns. Please use a SEQUENCE instead");
}
return string.Empty;
}
protected override string FormatDefaultValue(ColumnDefinition column)
{
var defaultValue = base.FormatDefaultValue(column);
if(!string.IsNullOrEmpty(defaultValue))
return string.Format("CONSTRAINT DF_{0}_{1} ", column.TableName, column.Name) + defaultValue;
return string.Empty;
}
protected virtual string FormatNullable(ColumnDefinition column)
{
if (column.IsNullable.HasValue && column.IsNullable.Value) {
return string.Empty;
}
else {
return "NOT NULL";
}
}
protected override string FormatNullable(ColumnDefinition column)
{
if (!(column.DefaultValue is ColumnDefinition.UndefinedDefaultValue))
return string.Empty;
return column.IsNullable.HasValue
? (column.IsNullable.Value ? "NULL" : "NOT NULL")
: String.Empty;
}
public void CanUseSystemMethodCurrentUTCDateTimeAsADefaultValueForAColumn()
{
const string tableName = "NewTable";
var columnDefinition = new ColumnDefinition { Name = "NewColumn", Size = 5, Type = DbType.String, DefaultValue = SystemMethods.CurrentUTCDateTime };
var expression = new CreateColumnExpression { Column = columnDefinition, TableName = tableName };
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"public\".\"NewTable\" ADD \"NewColumn\" varchar(5) NOT NULL DEFAULT (now() at time zone 'UTC')");
}
public void CanUseSystemMethodCurrentUTCDateTimeAsADefaultValueForAColumn()
{
const string tableName = "NewTable";
var columnDefinition = new ColumnDefinition { Name = "NewColumn", Size = 5, Type = DbType.String, DefaultValue = SystemMethods.CurrentUTCDateTime };
var expression = new CreateColumnExpression { Column = columnDefinition, TableName = tableName };
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE NewTable ADD COLUMN NewColumn VARGRAPHIC(5) CCSID 1200 NOT NULL DEFAULT (CURRENT_TIMESTAMP - CURRENT_TIMEZONE)");
}
public void CanUseSystemMethodCurrentUserAsADefaultValueForAColumn()
{
const string tableName = "NewTable";
var columnDefinition = new ColumnDefinition { Name = "NewColumn", Size = 18, Type = DbType.AnsiString, DefaultValue = SystemMethods.CurrentUser };
var expression = new CreateColumnExpression { Column = columnDefinition, TableName = tableName };
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE NewTable ADD COLUMN NewColumn VARCHAR(18) NOT NULL DEFAULT USER");
}
private string FormatAlterNullable(ColumnDefinition column)
{
if (!column.IsNullable.HasValue)
return "";
if (column.IsNullable.Value)
return "DROP NOT NULL";
return "SET NOT NULL";
}
private CreateTableExpression GetCreateTableExpression(string tableName)
{
var columnName1 = "ColumnName1";
var column1 = new ColumnDefinition { Name = columnName1, Type = DbType.String };
var expression = new CreateTableExpression { TableName = tableName };
expression.Columns.Add(column1);
return expression;
}
protected override string FormatIdentity(ColumnDefinition column)
{
//SQLite only supports the concept of Identity in combination with a single primary key
//see: http://www.sqlite.org/syntaxdiagrams.html#column-constraint syntax details
if (column.IsIdentity && !column.IsPrimaryKey && column.Type != DbType.Int32)
{
throw new ArgumentException("SQLite only supports identity on single integer, primary key coulmns");
}
return string.Empty;
}
public void CanUseSystemMethodCurrentUTCDateTimeAsADefaultValueForAColumn()
{
const string tableName = "NewTable";
var columnDefinition = new ColumnDefinition { Name = "NewColumn", Size = 5, Type = DbType.String, DefaultValue = SystemMethods.CurrentUTCDateTime };
var expression = new CreateColumnExpression { Column = columnDefinition, TableName = tableName };
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"NewTable\" ADD (\"NewColumn\" NVARCHAR(5) DEFAULT CURRENT_UTCTIMESTAMP);");
}
public void CanAddDecimalColumn()
{
var tableName = "NewTable";
var columnDefinition = new ColumnDefinition {Name = "NewColumn", Size = 19, Precision = 2, Type = DbType.Decimal};
var expression = new CreateColumnExpression {Column = columnDefinition, TableName = tableName};
ApplyDefaultConventions(expression);
var sql = generator.Generate(expression);
sql.ShouldBe("ALTER TABLE [NewTable] ADD COLUMN NewColumn NUMERIC NOT NULL");
}
/// <summary>
/// Either updates the IsNullable flag on the column, or creates/removes the SetNotNull expression, depending
/// on whether the column has a 'Set existing rows' expression.
/// </summary>
public virtual void SetNullable(bool isNullable)
{
var column = _builder.Column;
ExistingRowsData exRowExpr;
if (_existingRowsDataByColumn.TryGetValue(column, out exRowExpr))
{
if (exRowExpr.SetExistingRowsExpression != null)
{
if (isNullable)
{
//Remove additional expression to set column to not null.
_context.Expressions.Remove(exRowExpr.SetColumnNotNullableExpression);
exRowExpr.SetColumnNotNullableExpression = null;
}
else
{
//Add expression to set column to not null.
//If it already exists, just leave it.
if (exRowExpr.SetColumnNotNullableExpression == null)
{
//stuff that matters shouldn't change at this point, so we're free to make a
//copy of the col def.
//TODO: make a SetColumnNotNullExpression, which just takes the bare minimum.
ColumnDefinition notNullColDef = new ColumnDefinition
{
ModificationType = ColumnModificationType.Alter,
TableName = column.TableName,
Name = column.Name,
Type = column.Type,
CustomType = column.CustomType,
IsNullable = false,
};
exRowExpr.SetColumnNotNullableExpression = new AlterColumnExpression
{
Column = notNullColDef,
TableName = _builder.TableName,
SchemaName = _builder.SchemaName
};
_context.Expressions.Add(exRowExpr.SetColumnNotNullableExpression);
}
}
//Setting column explicitly to nullable, as the actual nullable value
//will be set by the SetColumnNotNullableExpression after the column is created and populated.
column.IsNullable = true;
return;
}
}
//At this point, we know there's no existing row expression, so just pass it onto the
//underlying column.
column.IsNullable = isNullable;
}
protected virtual string FormatCollation(ColumnDefinition column)
{
if (!string.IsNullOrEmpty(column.CollationName))
{
return "COLLATE " + column.CollationName;
}
else
{
return string.Empty;
}
}
protected override string FormatType(ColumnDefinition column)
{
if (column.IsIdentity)
{
if (column.Type == DbType.Int64)
return "bigserial";
return "serial";
}
return base.FormatType(column);
}
public void CanUseSystemMethodCurrentUTCDateTimeAsADefaultValueForAColumn()
{
const string tableName = "NewTable";
var columnDefinition = new ColumnDefinition { Name = "NewColumn", Type = DbType.DateTime, DefaultValue = SystemMethods.CurrentUTCDateTime };
var expression = new CreateColumnExpression { Column = columnDefinition, TableName = tableName };
string sql = generator.Generate(expression);
sql.ShouldBe("ALTER TABLE [dbo].[NewTable] ADD [NewColumn] DATETIME NOT NULL CONSTRAINT DF__NewColumn DEFAULT GETUTCDATE()");
}
public static bool DefaultValuesMatch(ColumnDefinition col1, ColumnDefinition col2)
{
if (col1.DefaultValue is ColumnDefinition.UndefinedDefaultValue && col2.DefaultValue is ColumnDefinition.UndefinedDefaultValue)
return true;
if (col1.DefaultValue is ColumnDefinition.UndefinedDefaultValue || col2.DefaultValue is ColumnDefinition.UndefinedDefaultValue)
return true;
FirebirdColumn column = new FirebirdColumn(new FirebirdOptions());
string col1Value = column.GenerateForDefaultAlter(col1);
string col2Value = column.GenerateForDefaultAlter(col2);
return col1Value != col2Value;
}
public void CanUseSystemMethodCurrentUserAsADefaultValueForAColumn()
{
const string tableName = "NewTable";
var columnDefinition = new ColumnDefinition { Name = "NewColumn", Size = 15, Type = DbType.String, DefaultValue = SystemMethods.CurrentUser };
var expression = new CreateColumnExpression { Column = columnDefinition, TableName = tableName };
string sql = generator.Generate(expression);
sql.ShouldBe("ALTER TABLE [dbo].[NewTable] ADD [NewColumn] NVARCHAR(15) NOT NULL CONSTRAINT DF__NewColumn DEFAULT CURRENT_USER");
}