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