private void GetPrimaryKey(TSqlTable table, DatabaseTable dbTable)
{
if (table.PrimaryKeyConstraints.Count() == 0)
{
return;
}
var pk = table.PrimaryKeyConstraints.First();
var primaryKey = new DatabasePrimaryKey
{
Name = pk.Name.HasName ? pk.Name.Parts[1] : null,
Table = dbTable
};
if (!pk.Clustered)
{
primaryKey["SqlServer:Clustered"] = false;
}
foreach (var pkCol in pk.Columns)
{
var dbCol = dbTable.Columns
.Single(c => c.Name == pkCol.Name.Parts[2]);
primaryKey.Columns.Add(dbCol);
}
dbTable.PrimaryKey = primaryKey;
}
/// <summary>
/// Outputs a table
/// </summary>
/// <param name="tbl"></param>
/// <param name="outputFormat"></param>
/// <returns></returns>
private void OutputDiagramTableDef(TSqlTable tbl)
{
StringBuilder outputString = new StringBuilder();
if (format == DiagramFormat.PlantUML)
{
// need to string [] from the name
diagram.AppendFormat("table({0}) {{\n", removeQualifiers(tbl.Name.ToString()));
}
else if (format == DiagramFormat.GraphViz)
{
diagram.AppendFormat("{0} [label=<\n", removeQualifiers(tbl.Name.ToString()).Replace(".", "_"));
diagram.AppendLine("\t<table border=\"0\" cellborder=\"1\" cellspacing=\"0\" cellpadding=\"4\">");
diagram.AppendFormat("\t\t<tr><td bgcolor=\"lightblue\">{0}</td></tr>\n", removeQualifiers(tbl.Name.ToString()));
}
OutputDiagramColumns(tbl);
if (format == DiagramFormat.PlantUML)
{
outputString.AppendLine("}\n");
outputString.AppendLine("");
}
else if (format == DiagramFormat.GraphViz)
{
outputString.AppendLine("\t</table>");
outputString.AppendLine(">]\n");
outputString.AppendLine("");
}
}
private void GetUniqueConstraints(TSqlTable table, DatabaseModel dbModel)
{
var dbTable = dbModel.Tables
.Single(t => t.Name == table.Name.Parts[1] &&
t.Schema == table.Name.Parts[0]);
var uqs = table.UniqueConstraints.ToList();
foreach (var uq in uqs)
{
var uniqueConstraint = new DatabaseUniqueConstraint
{
Name = uq.Name.HasName ? uq.Name.Parts[1] : null,
Table = dbTable
};
if (uq.Clustered)
{
uniqueConstraint["SqlServer:Clustered"] = true;
}
foreach (var uqCol in uq.Columns)
{
var dbCol = dbTable.Columns
.Single(c => c.Name == uqCol.Name.Parts[2]);
uniqueConstraint.Columns.Add(dbCol);
}
dbTable.UniqueConstraints.Add(uniqueConstraint);
}
}
private void GetIndexes(TSqlTable table, DatabaseModel dbModel)
{
var dbTable = dbModel.Tables
.Single(t => t.Name == table.Name.Parts[1] &&
t.Schema == table.Name.Parts[0]);
var ixs = table.Indexes.ToList();
foreach (var sqlIx in ixs)
{
var ix = sqlIx as TSqlIndex;
if (sqlIx == null)
{
continue;
}
var index = new DatabaseIndex
{
Name = ix.Name.Parts[2],
Table = dbTable,
IsUnique = ix.Unique,
Filter = ix.FilterPredicate
};
if (ix.Clustered)
{
index["SqlServer:Clustered"] = true;
}
foreach (var column in ix.Columns)
{
var dbCol = dbTable.Columns
.SingleOrDefault(c => c.Name == column.Name.Parts[2]);
if (dbCol != null)
{
index.Columns.Add(dbCol);
}
}
var includedColumns = new List <string>();
foreach (var includedColumn in ix.IncludedColumns)
{
includedColumns.Add(includedColumn.Name.Parts[2]);
}
if (includedColumns.Count != 0)
{
index["SqlServer:Include"] = includedColumns.ToArray();
}
if (index.Columns.Count > 0)
{
dbTable.Indexes.Add(index);
}
}
}
/// <summary>
/// Loops over all
/// </summary>
/// <param name="model"></param>
/// <param name="outputFormat"></param>
/// <returns></returns>
private void OutputDiagramSchemaDef()
{
var schemas = model.GetObjects <TSqlSchema>(DacQueryScopes.Default);
//StringBuilder outputstring = new StringBuilder();
foreach (var schema in schemas)
{
if (schema.Name.Parts[0] == "dbo")
{
if (format == DiagramFormat.PlantUML)
{
diagram.AppendFormat("\npackage {0} {{", schema.Name);
}
// put tables here
var x = schema.GetChildren(DacQueryScopes.UserDefined);
foreach (var thing in x)
{
if (thing.ObjectType == ModelSchema.Table)
{
var tbl = new TSqlTable(thing);
diagram.Append("\n");
OutputDiagramTableDef(tbl);
diagram.Append("\n");
}
}
//outputstring.Append("}\n");
}
}
schemas = model.GetObjects <TSqlSchema>(DacQueryScopes.UserDefined);
foreach (var schema in schemas)
{
diagram.AppendFormat("\npackage {0} {{", schema.Name);
// put tables here
var x = schema.GetChildren(DacQueryScopes.UserDefined);
foreach (var thing in x)
{
if (thing.ObjectType == ModelSchema.Table)
{
var tbl = new TSqlTable(thing);
diagram.Append("\n");
OutputDiagramTableDef(tbl);
diagram.Append("\n");
}
}
diagram.Append("\n}}");
}
}
public void BasicInstantiation()
{
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions()
{
}))
{
string createTable = @"
CREATE TABLE [dbo].[Table1]
(
[Id] INT NOT NULL PRIMARY KEY
)
";
string createCheck = @"
ALTER TABLE [dbo].[Table1]
ADD CONSTRAINT [check1]
CHECK (1 > 0)
";
model.AddObjects(createTable);
model.AddObjects(createCheck);
IEnumerable <TSqlObject> tables = model.GetObjects(DacQueryScopes.Default, Table.TypeClass);
tables.Single().GetReferencing(View.BodyDependencies);
TSqlTypedModel typedModel = new TSqlTypedModel(model);
var genericTables = typedModel.GetObjects <TSqlTable>(DacQueryScopes.Default);
var sql90Tables = typedModel.GetObjects <ISql90TSqlTable>(DacQueryScopes.Default);
TSqlTable genericTable = genericTables.First();
ISql90TSqlTable sql90Table = sql90Tables.First();
IList <TSqlColumn> genericColumns = genericTable.Columns.ToList();
IList <ISql90TSqlColumn> sql90Columns = sql90Table.Columns.ToList();
Assert.AreEqual(genericColumns.Count, sql90Columns.Count, "Column counts should not be different between implementations");
// iterate of generic columns
for (int i = 0; i < genericColumns.Count; i++)
{
TSqlColumn col = genericColumns[i];
ISql90TSqlColumn sql90Col = sql90Columns[i];
Assert.AreEqual(col.Collation, sql90Col.Collation, "Collation is not the same");
Assert.AreEqual(col.Expression, sql90Col.Expression, "Expression is not equal");
}
Assert.AreEqual(2, genericTable.AllConstraints.Count(), "Incorrect number of constraints");
Assert.AreEqual(1, genericTable.CheckConstraints.Count(), "Incorrect number of check constraints");
Assert.AreEqual(1, genericTable.PrimaryKeyConstraints.Count(), "Incorrect number of Primary Key Constraints");
//TODO: Code gen the Reverse relationships for all explicitly implemented interfaces
Assert.AreEqual(2, ((TSqlTable)sql90Table).AllConstraints.Count(), "Incorrect number of constraints");
Assert.AreEqual(1, ((TSqlTable)sql90Table).CheckConstraints.Count(), "Incorrect number of check constraints");
Assert.AreEqual(1, ((TSqlTable)sql90Table).PrimaryKeyConstraints.Count(), "Incorrect number of Primary Key Constraints");
}
}
private void GetForeignKeys(TSqlTable table, DatabaseModel dbModel)
{
var dbTable = dbModel.Tables
.Single(t => t.Name == table.Name.Parts[1] &&
t.Schema == table.Name.Parts[0]);
var fks = table.ForeignKeyConstraints.ToList();
foreach (var fk in fks)
{
var foreignTable = dbModel.Tables
.SingleOrDefault(t => t.Name == fk.ForeignTable.First().Name.Parts[1] &&
t.Schema == fk.ForeignTable.First().Name.Parts[0]);
if (foreignTable == null)
{
continue;
}
var foreignKey = new DatabaseForeignKey
{
Name = fk.Name.HasName ? fk.Name.Parts[1] : null,
Table = dbTable,
PrincipalTable = foreignTable,
OnDelete = ConvertToReferentialAction(fk.DeleteAction)
};
foreach (var fkCol in fk.Columns)
{
var dbCol = dbTable.Columns
.Single(c => c.Name == fkCol.Name.Parts[2]);
foreignKey.Columns.Add(dbCol);
}
foreach (var fkCol in fk.ForeignColumns)
{
var dbCol = foreignTable.Columns
.SingleOrDefault(c => c.Name == fkCol.Name.Parts[2]);
if (dbCol != null)
{
foreignKey.PrincipalColumns.Add(dbCol);
}
}
if (foreignKey.PrincipalColumns.Count > 0)
{
dbTable.ForeignKeys.Add(foreignKey);
}
}
}
private List <ColumnDescriptor> BuildColumnDescriptors(TSqlTable table)
{
if (!CanDecodeAllColumns(table))
{
//throw new InvalidOperationException
MessageBox.Show("Unable to work out column types for: " + table.Name.GetSchemaObjectName() +
" \r\nIf it has any user defined types then MergeUi doesn't support that right now");
return(null);
}
return(table.Columns.Where(column => column.ColumnType == ColumnType.Column).Select(column => new ColumnDescriptor(column)).ToList());
}
private IEnumerable <Constraint> BuildConstraints(TSqlTable table)
{
var constraints = new List <Constraint>();
foreach (var c in table.PrimaryKeyConstraints)
{
constraints.Add(new Constraint()
{
Name = c.Name, Type = ConstraintType.PrimaryKey
});
}
return(constraints);
}
private bool CanDecodeAllColumns(TSqlTable table)
{
try
{
foreach (TSqlColumn column in table.Columns.Where(column => column.ColumnType == ColumnType.Column))
{
if (column.ObjectType == UserDefinedType.TypeClass)
{
}
var descriptor = new ColumnDescriptor(column);
}
}
catch (Exception e)
{
return(false);
}
return(true);
}
public ProcedureBuilder(string testSchema, string testName, TSqlTableValuedFunction procedureUnderTest)
{
CodeType = CodeType.Function;
_testProcedure.StatementList = new StatementList();
foreach (var t in procedureUnderTest.BodyDependencies)
{
if (t.ObjectType == ModelSchema.Table)
{
//table to fake
var table = new TSqlTable(t.Element);
AddTable(table.Name);
}
}
foreach (var p in procedureUnderTest.Parameters)
{
AddParameter(p.Name.GetName().UnQuote(), GetParameterType(p.DataType));
}
CreateTestProcedureDefinition(testSchema, testName);
CreateSelectForFunctionUnderTest(procedureUnderTest.Name);
}
private void GetColumns(TSqlTable item, DatabaseTable dbTable, IReadOnlyDictionary <string, (string storeType, string typeName)> typeAliases, List <TSqlDefaultConstraint> defaultConstraints)
private static void GetColumns(TSqlTable item, DatabaseTable dbTable, IReadOnlyDictionary <string, (string StoreType, string TypeName)> typeAliases, List <TSqlDefaultConstraint> defaultConstraints, TSqlTypedModel model)
/// <summary>
/// Outputs the columns of a table
/// </summary>
/// <param name="outputString"></param>
/// <param name="t"></param>
/// <param name="outputFormat"></param>
/// <returns></returns>
private void OutputDiagramColumns(TSqlTable t)
{
foreach (var Column in t.Columns)
{
if (format == DiagramFormat.PlantUML)
{
diagram.AppendFormat("\t{0}:", Column.Name.Parts[2]);
}
else if (format == DiagramFormat.GraphViz)
{
diagram.AppendFormat("\t\t<tr><td align=\"left\">{0}:", Column.Name.Parts[2]);
}
foreach (var columnDataType in Column.DataType)
{
if (format == DiagramFormat.PlantUML)
{
diagram.AppendFormat(" {0}", removeQualifiers(columnDataType.Name.ToString()));
}
else if (format == DiagramFormat.GraphViz)
{
diagram.AppendFormat("{0}", removeQualifiers(columnDataType.Name.ToString()));
}
}
//Check to see if the column is a PK
foreach (var pk in t.PrimaryKeyConstraints)
{
foreach (var primaryKeyColumn in pk.Columns)
{
if (primaryKeyColumn.Name.Parts[2] == Column.Name.Parts[2])
{
if (format == DiagramFormat.PlantUML)
{
diagram.AppendFormat("<<PK>>");
}
else if (format == DiagramFormat.GraphViz)
{
diagram.Append("(PK)");
}
}
}
}
// Check to see if the column is a FK
foreach (var fk in t.ForeignKeyConstraints)
{
foreach (var foreignKeyColumn in fk.Columns)
{
if (foreignKeyColumn.Name.Parts[2] == Column.Name.Parts[2])
{
if (format == DiagramFormat.PlantUML)
{
diagram.AppendFormat("<<FK>>");
}
else if (format == DiagramFormat.GraphViz)
{
diagram.Append("(FK)");
}
}
}
}
if (format == DiagramFormat.PlantUML)
{
diagram.AppendFormat("\n");
}
else if (format == DiagramFormat.GraphViz)
{
diagram.AppendFormat("</td></tr>\n");
}
}
}
public void TSqlStatementExtensions_GetAllTables_TSqlTable_Constructor()
{
var tables = new TSqlTable("Person");
Assert.AreEqual("Person", tables.TableName);
}
public TableDescriptor(TSqlTable table)
{
Columns = BuildColumnDescriptors(table);
Name = table.Name;
Constraints = BuildConstraints(table);
}
