public TargetRefColumnSqlModel(TargetReference fk, TargetReferenceColumn col, TargetEntitySqlModel targetSqlModel, ColumnInfo colinfo)
: base(colinfo)
{
_col = col;
_fk = fk;
_targetSqlModel = targetSqlModel;
}
public static bool EqualDefaultValues(ColumnInfo a, ColumnInfo b)
{
if (a.DefaultValue == null)
{
if (a.DefaultValue != b.DefaultValue)
{
return false;
}
}
else
{
if (!a.DefaultValue.Equals(b.DefaultValue))
{
return false;
}
}
return true;
}
public TableInfo ToTableInfo(bool includeHiddenColumns = false)
{
var res = new TableInfo(new DatabaseInfo());
var pk = new PrimaryKeyInfo(res);
var tableNames = Columns.Select(x => x.BaseTableName).Where(x => x != null).Distinct().ToList();
var schemaNames = Columns.Select(x => x.BaseSchemaName).Where(x => x != null).Distinct().ToList();
if (tableNames.Count == 1 && schemaNames.Count <= 1)
{
res.FullName = new NameWithSchema(schemaNames.FirstOrDefault(), tableNames.Single());
}
foreach (var column in Columns)
{
if (column.IsHidden && !includeHiddenColumns) continue;
var col = new ColumnInfo(res)
{
Name = column.Name,
NotNull = column.NotNull,
CommonType = column.CommonType.Clone(),
DataType = column.DataType,
AutoIncrement = column.AutoIncrement,
PrimaryKey = column.IsKey,
};
if (col.CommonType is DbTypeString) col.Length = column.Size;
if (column.AutoIncrement && col.CommonType != null)
{
col.CommonType.SetAutoincrement(true);
}
if (column.IsKey && res.FullName != null)
{
pk.Columns.Add(new ColumnReference {RefColumn = col});
}
res.Columns.Add(col);
}
if (pk.Columns.Count > 0) res.PrimaryKey = pk;
return res;
}
public virtual void GetNativeDataType(DbTypeBase commonType, ColumnInfo columnInfo)
{
switch (commonType.Code)
{
case DbTypeCode.Blob:
columnInfo.DataType = "blob";
return;
case DbTypeCode.Datetime:
columnInfo.DataType = "datetime";
return;
case DbTypeCode.String:
var str = (DbTypeString) commonType;
columnInfo.DataType = str.GetStandardSqlName();
columnInfo.Length = str.Length;
return;
case DbTypeCode.Guid:
columnInfo.DataType = "varchar";
columnInfo.Length = 50;
return;
case DbTypeCode.Float:
columnInfo.DataType = "float";
return;
case DbTypeCode.Numeric:
columnInfo.DataType = "numeric";
columnInfo.Precision = commonType.GetLength();
columnInfo.Scale = commonType.GetScale();
return;
case DbTypeCode.Text:
case DbTypeCode.Xml:
columnInfo.DataType = "nvarchar";
columnInfo.Length = -1;
return;
case DbTypeCode.Logical:
columnInfo.DataType = "int";
return;
}
columnInfo.DataType = "varchar";
columnInfo.Length = 50;
}
public virtual DbTypeBase CreateCommonType(ColumnInfo column)
{
return new DbTypeString();
}
private string GuessDefaultName(ColumnInfo col)
{
string defname = col.DefaultConstraint;
if (defname == null)
{
defname = String.Format("DF_{0}_{1}_{2}", col.OwnerTable.FullName.Schema ?? "dbo", col.OwnerTable.FullName.Name, col.Name);
}
return defname;
}
public override void ChangeColumn(ColumnInfo oldcol, ColumnInfo newcol, IEnumerable<ConstraintInfo> constraints)
{
if (DbDiffTool.EqualsColumns(oldcol, newcol, false, false))
{
DropDefault(oldcol);
if (oldcol.Name != newcol.Name) RenameColumn(oldcol, newcol.Name);
CreateDefault(newcol);
}
else
{
DropDefault(oldcol);
if (oldcol.Name != newcol.Name) RenameColumn(oldcol, newcol.Name);
Put("^alter ^table %f ^alter ^column %i ", newcol.OwnerTable.FullName, newcol.Name);
// remove autoincrement flag
var newcol2 = newcol.CloneColumn();
newcol2.SetDummyTable(newcol.OwnerTable.FullName);
newcol2.AutoIncrement = false;
ColumnDefinition(newcol2, false, true, true);
EndCommand();
CreateDefault(newcol);
this.CreateConstraints(constraints);
}
}
private void CreateDefault(ColumnInfo col)
{
if (col.DefaultValue == null) return;
string defsql = col.DefaultValue;
if (defsql != null)
{
var defname = GuessDefaultName(col);
PutCmd("^alter ^table %f ^add ^constraint %i ^default %s for %i", col.OwnerTable.FullName, defname, defsql, col.Name);
}
}
public static bool EqualTypes(ColumnInfo a, ColumnInfo b, DbDiffOptions opts)
{
if (a.DataType != b.DataType)
{
opts.DiffLogger.Trace("Column {0}, {1}: different types: {2}; {3}", a, b, a.DataType, b.DataType);
return false;
}
if (a.Length != b.Length)
{
opts.DiffLogger.Trace("Column {0}, {1}: different lengths: {2}; {3}", a, b, a.Length, b.Length);
return false;
}
if (a.Precision != b.Precision)
{
opts.DiffLogger.Trace("Column {0}, {1}: different lengths: {2}; {3}", a, b, a.Precision, b.Precision);
return false;
}
if (a.Scale != b.Scale)
{
opts.DiffLogger.Trace("Column {0}, {1}: different scale: {2}; {3}", a, b, a.Scale, b.Scale);
return false;
}
return true;
}
public void ChangeColumn(ColumnInfo column, ColumnInfo newcolumn)
{
ColumnInfo col = Structure.FindOrCreateColumn(column);
AddOperation(new AlterOperation_ChangeColumn { OldObject = col, ParentTable = col.OwnerTable, NewObject = newcolumn.CloneColumn()});
}
public void RenameColumn(ColumnInfo column, string name)
{
var col = Structure.FindOrCreateColumn(column);
AddOperation(new AlterOperation_RenameColumn { OldObject = col, ParentTable = col.OwnerTable, NewName = new NameWithSchema(name) });
}
public void DropColumn(ColumnInfo column, PlanPosition pos = PlanPosition.End)
{
var col = Structure.FindOrCreateColumn(column);
AddOperation(new AlterOperation_DropColumn { ParentTable = col.OwnerTable, OldObject = col }, pos);
}
public ColumnInfo FindOrCreateColumn(ColumnInfo col)
{
var res = FindColumn(col.Name);
if (res == null)
{
res = col.CloneColumn(this);
res.GroupId = Guid.NewGuid().ToString();
Columns.Add(res);
}
return res;
}
public void DropColumn(ColumnInfo column)
{
Columns.RemoveAll(c => c.Name == column.Name);
}
public ColumnInfo AddColumn(string columnName, string dataType, DbTypeBase commonType)
{
var newColumn = new ColumnInfo(this)
{
Name = columnName,
DataType = dataType,
CommonType = commonType,
};
Columns.Add(newColumn);
return newColumn;
}
public void CreateColumn(TableInfo table, ColumnInfo newcol, PlanPosition pos = PlanPosition.End)
{
TableInfo tbl = Structure.FindOrCreateTable(table.FullName);
AddOperation(new AlterOperation_CreateColumn { ParentTable = tbl, NewObject = newcol.CloneColumn() }, pos);
}
public virtual void ColumnDefinition(ColumnInfo col, bool includeDefault, bool includeNullable, bool includeCollate)
{
if (col.ComputedExpression != null)
{
Put("^as %s", col.ComputedExpression);
if (col.IsPersisted) Put(" ^persisted");
return;
}
Put("%k", col.DataType);
if (col.Length != 0 && (col.CommonType == null || col.CommonType is DbTypeString))
{
if (col.Length == -1 || col.Length > 8000) Put("(^max)");
else Put("(%s)", col.Length);
}
if (col.Precision > 0 && col.CommonType is DbTypeNumeric && (col.DataType.ToLower() != "money"))
{
Put("(%s,%s)", col.Precision, col.Scale);
}
if (col.AutoIncrement)
{
Put(" ^identity");
}
WriteRaw(" ");
if (col.IsSparse)
{
Put(" ^sparse ");
}
if (includeNullable)
{
Put(col.NotNull ? "^not ^null" : "^null");
}
if (includeDefault && col.DefaultValue != null)
{
ColumnDefinition_Default(col);
}
}
public static bool EqualsColumns(ColumnInfo a, ColumnInfo b, bool checkName, bool checkDefault)
{
return EqualsColumns(a, b, checkName,checkDefault, new DbDiffOptions(), null);
}
private void ColumnDefinition_Default(ColumnInfo col)
{
string defsql = col.DefaultValue;
if (col.DefaultConstraint != null)
{
Put(" ^constraint %i ^default %s ", col.DefaultConstraint, defsql);
}
else
{
Put(" ^default %s ", defsql);
}
}
public static bool EqualsColumns(ColumnInfo a, ColumnInfo b, bool checkName, bool checkDefault, DbDiffOptions opts, DbObjectPairing pairing)
{
if (checkName && !DbDiffTool.EqualNames(a.Name, b.Name, opts))
{
opts.DiffLogger.Trace("Column, different name: {0}; {1}", a, b);
return false;
}
//if (!DbDiffTool.EqualFullNames(a.Domain, b.Domain, opts))
//{
// opts.DiffLogger.Trace("Column {0}, {1}: different domain: {2}; {3}", a, b, a.Domain, b.Domain);
// return false;
//}
if (a.ComputedExpression != b.ComputedExpression)
{
opts.DiffLogger.Trace("Column {0}, {1}: different computed expression: {2}; {3}", a, b, a.ComputedExpression, b.ComputedExpression);
return false;
}
if (a.ComputedExpression != null)
{
return true;
}
if (checkDefault)
{
if (a.DefaultValue == null)
{
if (a.DefaultValue != b.DefaultValue)
{
opts.DiffLogger.Trace("Column {0}, {1}: different default values: {2}; {3}", a, b, a.DefaultValue, b.DefaultValue);
return false;
}
}
else
{
if (!a.DefaultValue.Equals(b.DefaultValue))
{
opts.DiffLogger.Trace("Column {0}, {1}: different default values: {2}; {3}", a, b, a.DefaultValue, b.DefaultValue);
return false;
}
}
if (a.DefaultConstraint != b.DefaultConstraint)
{
opts.DiffLogger.Trace("Column {0}, {1}: different default constraint names: {2}; {3}", a, b, a.DefaultConstraint, b.DefaultConstraint);
return false;
}
}
if (a.NotNull != b.NotNull)
{
opts.DiffLogger.Trace("Column {0}, {1}: different nullable: {2}; {3}", a, b, a.NotNull, b.NotNull);
return false;
}
if (a.AutoIncrement != b.AutoIncrement)
{
opts.DiffLogger.Trace("Column {0}, {1}: different autoincrement: {2}; {3}", a, b, a.AutoIncrement, b.AutoIncrement);
return false;
}
if (a.IsSparse != b.IsSparse)
{
opts.DiffLogger.Trace("Column {0}, {1}: different is_sparse: {2}; {3}", a, b, a.IsSparse, b.IsSparse);
return false;
}
if (!EqualTypes(a, b, opts))
{
return false;
}
//var btype = b.DataType;
//var atype = a.DataType;
//if (pairing != null && pairing.Target != null && pairing.Source.Dialect != null)
//{
// btype = pairing.Source.Dialect.MigrateDataType(b, btype, pairing.Source.Dialect.GetDefaultMigrationProfile(), null);
// btype = pairing.Source.Dialect.GenericTypeToSpecific(btype).ToGenericType();
// // normalize type
// atype = pairing.Source.Dialect.GenericTypeToSpecific(atype).ToGenericType();
//}
//if (!EqualTypes(atype, btype, opts))
//{
// opts.DiffLogger.Trace("Column {0}, {1}: different types: {2}; {3}", a, b, a.DataType, b.DataType);
// return false;
//}
//if (!opts.IgnoreColumnCollation && a.Collation != b.Collation)
//{
// opts.DiffLogger.Trace("Column {0}, {1}: different collations: {2}; {3}", a, b, a.Collation, b.Collation);
// return false;
//}
//if (!opts.IgnoreColumnCharacterSet && a.CharacterSet != b.CharacterSet)
//{
// opts.DiffLogger.Trace("Column {0}, {1}: different character sets: {2}; {3}", a, b, a.CharacterSet, b.CharacterSet);
// return false;
//}
return true;
}
public virtual void CreateColumn(ColumnInfo column, IEnumerable<ConstraintInfo> constraints)
{
Put("^alter ^table %f ^add %i ", column.OwnerTable, column.Name);
ColumnDefinition(column, true, true, true);
InlineConstraints(constraints);
EndCommand();
}
public override void RenameColumn(ColumnInfo column, string newcol)
{
PutCmd("^execute sp_rename '%f.%i', '%s', 'COLUMN'", column.OwnerTable.FullName, column.Name, newcol);
}
public virtual void DropColumn(ColumnInfo column)
{
PutCmd("^alter ^table %f ^drop ^column %i", column.OwnerTable.FullName, column.Name);
}
private void DropDefault(ColumnInfo col)
{
if (!String.IsNullOrEmpty(col.DefaultConstraint)) PutCmd("^alter ^table %f ^drop ^constraint %i", col.OwnerTable.FullName, col.DefaultConstraint);
}
public virtual void RenameColumn(ColumnInfo column, string newcol)
{
throw new System.NotImplementedException();
}
public TableColumnModel(ColumnInfo column)
{
_column = column;
}
public virtual void ChangeColumn(ColumnInfo oldcol, ColumnInfo newcol, IEnumerable<ConstraintInfo> constraints)
{
throw new System.NotImplementedException();
}
public ColumnInfo CloneColumn(TableInfo ownTable = null)
{
var res = new ColumnInfo(ownTable ?? OwnerTable);
res.Assign(this);
return res;
}
TableInfo GetStructure(SocialExplorer.IO.FastDBF.DbfFile dbf)
{
var res = new TableInfo(null);
//output column names
for (int i = 0; i < dbf.Header.ColumnCount; i++)
{
DbTypeBase type;
// convert DBF type to DA type
switch (dbf.Header[i].ColumnType)
{
case DbfColumn.DbfColumnType.Binary:
type = new DbTypeBlob();
break;
case DbfColumn.DbfColumnType.Boolean:
type = new DbTypeLogical();
break;
case DbfColumn.DbfColumnType.Date:
type = new DbTypeDatetime {SubType = DbDatetimeSubType.Date};
break;
case DbfColumn.DbfColumnType.Character:
type = new DbTypeString {Length = dbf.Header[i].Length};
break;
case DbfColumn.DbfColumnType.Integer:
type = new DbTypeInt();
break;
case DbfColumn.DbfColumnType.Memo:
type = new DbTypeText();
break;
case DbfColumn.DbfColumnType.Number:
type = new DbTypeNumeric
{
Precision = dbf.Header[i].Length,
Scale = dbf.Header[i].DecimalCount,
};
break;
default:
type = new DbTypeString();
break;
}
var col = new ColumnInfo(res);
col.Name = dbf.Header[i].Name;
col.CommonType = type;
//col.FillTypeFromCommonType();
res.Columns.Add(col);
}
return res;
}
ColumnInfo[] IColumnMapping.GetOutputColumns(TableInfo inputTable, IShellContext context)
{
var column = new ColumnInfo(new TableInfo(null)) {CommonType = new DbTypeString(), Name = Name, DataType = "nvarchar", Length = -1};
return new[] {column};
}
public void DropColumn(ColumnInfo column)
{
Columns.RemoveAll(c => c.Name == column.Name);
}