public PrimaryKeyInfo ClonePrimaryKey(TableInfo ownTable = null)
{
var res = new PrimaryKeyInfo(ownTable ?? OwnerTable);
res.Assign(this);
return(res);
}
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.DataType += $"({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 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 CreatePrimaryKey(PrimaryKeyInfo pk)
{
Put("^alter ^table %f ^add ^constraint %i ^primary ^key", pk.OwnerTable, pk.ConstraintName);
WriteRaw(" (");
ColumnRefs(pk.Columns);
WriteRaw(")");
EndCommand();
}
public virtual void DropPrimaryKey(PrimaryKeyInfo pk)
{
DropConstraint(pk);
}
public ICdlWriter CreateWriter(TableInfo inputRowFormat, CopyTableTargetOptions options, IShellContext context, DataFormatSettings sourceDataFormat)
{
var connection = GetConnectionProvider(context);
using (var conn = connection.Connect())
{
var db = new DatabaseInfo();
db.LinkedInfo = LinkedInfo;
var tbl = inputRowFormat.CloneTable(db);
tbl.FullName = GetFullName(context);
foreach (var col in tbl.Columns) col.AutoIncrement = false;
tbl.ForeignKeys.Clear();
if (tbl.PrimaryKey != null) tbl.PrimaryKey.ConstraintName = null;
tbl.AfterLoadLink();
if (IdentityColumn != null)
{
var col = new ColumnInfo(tbl);
col.Name = IdentityColumn;
col.DataType = "int";
col.AutoIncrement = true;
col.NotNull = true;
var pk = new PrimaryKeyInfo(tbl);
pk.Columns.Add(new ColumnReference {RefColumn = col});
pk.ConstraintName = "PK_" + tbl.Name;
tbl.PrimaryKey = pk;
tbl.Columns.Insert(0, col);
}
//var sw = new StringWriter();
var so = new ConnectionSqlOutputStream(conn, null, connection.Factory.CreateDialect());
var dmp = connection.Factory.CreateDumper(so, new SqlFormatProperties());
if (DropIfExists) dmp.DropTable(tbl, true);
bool useExistingTable = false;
if (UseIfExists)
{
var ts = context.GetDatabaseStructure(connection.ProviderString);
useExistingTable = ts.FindTableLike(tbl.FullName.Schema, tbl.FullName.Name) != null;
}
if (!useExistingTable)
{
tbl.Columns.ForEach(x => x.EnsureDataType(connection.Factory.CreateSqlTypeProvider()));
dmp.CreateTable(tbl);
}
//using (var cmd = conn.CreateCommand())
//{
// cmd.CommandText = sw.ToString();
// cmd.ExecuteNonQuery();
//}
return new TableWriter(context, connection, GetFullName(context), inputRowFormat, options, useExistingTable ? null : tbl, LinkedInfo, sourceDataFormat);
}
}
public void CreatePrimaryKey(PrimaryKeyInfo pk)
{
_database.FindTable(pk.OwnerTable).AddConstraint(pk);
}
public void DropPrimaryKey(PrimaryKeyInfo pk)
{
_database.FindTable(pk.OwnerTable).DropConstraint(pk);
}
private void TestDiff(Action<DatabaseInfo> mangle, string expectedResult)
{
var db1 = new DatabaseInfo();
var t1 = new TableInfo(db1)
{
Name = "t1",
Schema = "dbo",
};
t1.Columns.Add(new ColumnInfo(t1)
{
Name = "c1",
DataType = "int",
NotNull = true,
CommonType = new DbTypeInt(),
});
t1.Columns.Add(new ColumnInfo(t1)
{
Name = "c2",
DataType = "int",
NotNull = true,
CommonType = new DbTypeInt(),
});
var ix = new IndexInfo(t1);
ix.Columns.Add(new ColumnReference { RefColumn = t1.Columns[0] });
ix.ConstraintName = "ix1";
t1.Indexes.Add(ix);
var pk = new PrimaryKeyInfo(t1);
pk.Columns.Add(new ColumnReference {RefColumn = t1.Columns[0]});
pk.ConstraintName = "pk_t1";
t1.PrimaryKey = pk;
db1.Tables.Add(t1);
var v1 = new ViewInfo(db1)
{
Name = "v1",
Schema = "dbo",
CreateSql = "create view v1 as select * from t1",
};
db1.Views.Add(v1);
var db2 = db1.CloneDatabase();
mangle(db2);
var plan = new AlterPlan(db1);
DbDiffTool.AlterDatabase(plan, db1, db2, new DbDiffOptions());
var caps = SqlServerDatabaseFactory.Instance.DumperCaps;
plan.AddLogicalDependencies(caps, new DbDiffOptions());
plan.Transform(caps, new DbDiffOptions());
var runner = plan.CreateRunner();
var sw = new StringWriter();
var sqlo = new SqlOutputStream(SqlServerDatabaseFactory.Instance.CreateDialect(), sw, new SqlFormatProperties());
var dmp = SqlServerDatabaseFactory.Instance.CreateDumper(sqlo, new SqlFormatProperties());
runner.Run(dmp, new DbDiffOptions());
string sql = sw.ToString();
Assert.IsNotNull(sql);
//string expectedTran = TransformSql(expectedResult);
//string sqlTran = TransformSql(sql);
//for(int i = 0; i < expectedResult.Length; i++)
//{
// Assert.AreEqual(expectedTran[i], sqlTran[i]);
//}
Assert.AreEqual(TransformSql(expectedResult), TransformSql(sql));
}
public PrimaryKeyInfo ClonePrimaryKey(TableInfo ownTable = null)
{
var res = new PrimaryKeyInfo(ownTable ?? OwnerTable);
res.Assign(this);
return res;
}