/// <summary>
/// ALTER TABLE only allows columns to be added that can contain nulls,
/// or have a DEFAULT definition specified,
/// or the column being added is an identity or timestamp column,
/// or alternatively if none of the previous conditions are satisfied the table must be empty
/// to allow addition of this column.
/// </summary>
public SQLStatementWriter WriteAlterColumnStatement(JMXAttribute att, bool addNew = false, JMXSchema fromSchema = null)
{
if (fromSchema == null)
{
fromSchema = _schema;
}
string action = addNew ? "add" : "alter column";
Write($"alter table [{fromSchema.DbObjectName.AreaName}].[{fromSchema.DbObjectName.ObjectName}] {action} [{att.FieldName}]\t{att.ServerDataType}");
MdbTypeInfo ti = _typeMapping.GetServerTypeMap()[att.ServerDataType];
if (att.DataType == MdbType.@decimal && !ti.FixedSize && !att.DataSize.IsEmpty())
{
Write($"({att.DataSize.Precision},{att.DataSize.Scale})");
}
else if (_typeMapping.GetVariableLenghtDataTypes().Contains(att.ServerDataType))
{
if (att.DataSize.Size == -1)
{
Write("(max)");
}
else
{
Write($"({att.DataSize.Size})");
}
}
Write($"\t{att.NullOption}\n");
return(this);
}
public SQLStatementWriter WriteDropColumnStatement(JMXAttribute att, JMXSchema fromSchema = null)
{
if (fromSchema == null)
{
fromSchema = _schema;
}
Write($"alter table [{fromSchema.DbObjectName.AreaName}].[{fromSchema.DbObjectName.ObjectName}] drop column [{att.FieldName}]\n");
return(this);
}
public SQLStatementWriter WriteEqualsColumnStatement(JMXSchema fromSchema,
JMXAttribute att, string variableName, string sep = "")
{
if (fromSchema == null)
{
fromSchema = _schema;
}
Write($"{sep}{fromSchema.DbObjectName.ObjectName}.{att.FieldName} = {variableName}");
return(this);
}
public SQLStatementWriter WriteDropConstraintStatement(JMXAttribute att, JMXSchema fromSchema = null)
{
if (fromSchema == null)
{
fromSchema = _schema;
}
if (!att.DefaultConstraint.IsEmpty())
{
Write($"alter table {fromSchema.DbObjectName} drop constraint {att.DefaultConstraint.ConstraintName}\n");
}
if (!att.CheckConstraint.IsEmpty())
{
Write($"alter table {fromSchema.DbObjectName} drop constraint {att.CheckConstraint.ConstraintName}\n");
}
return(this);
}
public SQLStatementWriter WriteCreateConstraintStatement(JMXAttribute att, JMXSchema fromSchema = null)
{
if (fromSchema == null)
{
fromSchema = _schema;
}
if (!att.CheckConstraint.IsEmpty())
{
Write($"alter table {fromSchema.DbObjectName} add constraint {att.CheckConstraint.ConstraintName} check({att.CheckConstraint.Definition})\n");
}
if (!att.DefaultConstraint.IsEmpty())
{
Write($"alter table {fromSchema.DbObjectName} add constraint {att.DefaultConstraint.ConstraintName} default({att.DefaultConstraint.Definition}) for [{att.FieldName}]\n");
}
return(this);
}
public SQLStatementWriter WriteSelectColumnStatement(JMXSchema fromSchema,
JMXAttribute att, string sep)
{
if (fromSchema == null)
{
fromSchema = _schema;
}
if (att.Required)
{
WriteLine($"\t{sep}{fromSchema.DbObjectName.ObjectName}.{att.FieldName} as {att.AttribName}");
}
else if (att.DataType == MdbType.@object)
{
WriteLine($"\t{sep}'***' {att.AttribName}");
}
else
{
string emptyValue = att.DataType.Type().IsNumeric() ? "0" : "''";
WriteLine($"\t{sep}COALESCE({fromSchema.DbObjectName.ObjectName}.{att.FieldName}, {emptyValue}) as {att.AttribName}");
}
return(this);
}
