/// <summary>
/// Initializes a new instance of the <see cref="Procedure"/> class.
/// </summary>
/// <param name="prefix">The prefix used on stored procedure names.</param>
public Procedure(dac.TSqlObject tSqlObject, string prefix, IEnumerable <dac.TSqlObject> primaryKeys, IDictionary <dac.TSqlObject, IEnumerable <ForeignKeyConstraintDefinition> > foreignKeys)
{
this.Prefix = prefix ?? "";
this.RawName = tSqlObject.Name.Parts.Last();
this.Name = this.RawName.Substring(this.Prefix.Length);
this.Parameters = tSqlObject.GetReferenced(dac.Procedure.Parameters).Select(x => new Parameter(x, primaryKeys, foreignKeys));
TSqlFragment fragment;
TSqlModelUtils.TryGetFragmentForAnalysis(tSqlObject, out fragment);
var selectVisitor = new SelectVisitor();
fragment.Accept(selectVisitor);
var bodyColumnTypes = tSqlObject.GetReferenced(dac.Procedure.BodyDependencies)
.Where(x => x.ObjectType.Name == "Column")
.GroupBy(bd => string.Join(".", bd.Name.Parts))
.Select(grp => grp.First())
.ToDictionary(
key => string.Join(".", key.Name.Parts),
val => new DataType
{
Map = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, val.GetReferenced(dac.Column.DataType).First().Name.Parts.Last()),
Nullable = dac.Column.Nullable.GetValue <bool>(val)
},
StringComparer.InvariantCultureIgnoreCase);
var unions = selectVisitor.Nodes.OfType <BinaryQueryExpression>().Select(bq => GetQueryFromUnion(bq)).Where(x => x != null);
var selects = selectVisitor.Nodes.OfType <QuerySpecification>().Concat(unions);
this.Selects = selects.Select(s => new Select(s, bodyColumnTypes)).ToList();
}
/// <summary>
/// Initializes a new instance of the <see cref="Column" /> class.
/// </summary>
/// <param name="tSqlObject">The TSqlObject representing the column.</param>
/// <param name="tSqlTable">The table or view this column belongs to.</param>
/// <param name="primaryKeys">The primary keys.</param>
/// <param name="foreignKeys">The foreign keys.</param>
public Column(dac.TSqlObject tSqlObject, dac.TSqlObject tSqlTable, IEnumerable <dac.TSqlObject> primaryKeys, IDictionary <dac.TSqlObject, IEnumerable <ForeignKeyConstraintDefinition> > foreignKeys)
{
this.Name = tSqlObject.Name.Parts.Last();
var fullName = string.Join(".", tSqlObject.Name.Parts);
this.IsPrimaryKey = primaryKeys.Any(p => string.Join(".", p.Name.Parts) == fullName);
// Get relationships where this column is the child.
IEnumerable <ForeignKeyConstraintDefinition> myForeignKeys;
foreignKeys.TryGetValue(tSqlTable, out myForeignKeys);
myForeignKeys = myForeignKeys ?? Enumerable.Empty <ForeignKeyConstraintDefinition>();
this.ParentRelationships = from f in myForeignKeys
where f.Columns.Any(c => c.Value == this.Name)
select new RelationshipIdentifier
{
TableOrView = f.ReferenceTableName.BaseIdentifier != null ? f.ReferenceTableName.BaseIdentifier.Value : null,
Schema = f.ReferenceTableName.SchemaIdentifier != null ? f.ReferenceTableName.SchemaIdentifier.Value : null,
Database = f.ReferenceTableName.DatabaseIdentifier != null ? f.ReferenceTableName.DatabaseIdentifier.Value : null,
Columns = f.ReferencedTableColumns.Select(c => c.Value)
};
this.IsForeignKey = this.ParentRelationships.Any();
// Get relationships where this column is the parent.
var childTables = foreignKeys.Where(f => f.Value.Any(v =>
v.ReferenceTableName.BaseIdentifier.Value == tSqlTable.Name.Parts.Last() &&
v.ReferencedTableColumns.Any(c => c.Value == this.Name)));
this.ChildRelationships = from t in childTables
from r in t.Value
let tableParts = t.Key.Name.Parts.Count()
select new RelationshipIdentifier
{
TableOrView = t.Key.Name.Parts.Last(),
Schema = tableParts > 1 ? t.Key.Name.Parts.ElementAt(tableParts - 2) : null,
Database = tableParts > 2 ? t.Key.Name.Parts.ElementAt(tableParts - 3) : null,
Columns = r.Columns.Select(c => c.Value)
};
if (tSqlObject.ObjectType.Name == "TableTypeColumn")
{
var sqlDataTypeName = tSqlObject.GetReferenced(dac.TableTypeColumn.DataType).ToList().First().Name.Parts.Last();
this.DataTypes = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, sqlDataTypeName);
this.IsIdentity = dac.TableTypeColumn.IsIdentity.GetValue <bool>(tSqlObject);
this.IsNullable = dac.TableTypeColumn.Nullable.GetValue <bool>(tSqlObject);
this.Precision = dac.TableTypeColumn.Precision.GetValue <int>(tSqlObject);
this.Scale = dac.TableTypeColumn.Scale.GetValue <int>(tSqlObject);
this.Length = dac.TableTypeColumn.Length.GetValue <int>(tSqlObject);
}
else
{
var sqlDataTypeName = tSqlObject.GetReferenced(dac.Column.DataType).ToList().First().Name.Parts.Last();
this.DataTypes = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, sqlDataTypeName);
this.IsIdentity = dac.Column.IsIdentity.GetValue <bool>(tSqlObject);
this.IsNullable = dac.Column.Nullable.GetValue <bool>(tSqlObject);
this.Precision = dac.Column.Precision.GetValue <int>(tSqlObject);
this.Scale = dac.Column.Scale.GetValue <int>(tSqlObject);
this.Length = dac.Column.Length.GetValue <int>(tSqlObject);
}
}
private void SetProperties(dac.TSqlObject tSqlObject)
{
var sqlDataTypeName = tSqlObject.GetReferenced(dac.Column.DataType).ToList().First().Name.Parts.Last();
this.DataTypes = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, sqlDataTypeName);
this.IsIdentity = dac.Column.IsIdentity.GetValue <bool>(tSqlObject);
this.IsNullable = dac.Column.Nullable.GetValue <bool>(tSqlObject);
this.Precision = dac.Column.Precision.GetValue <int>(tSqlObject);
this.Scale = dac.Column.Scale.GetValue <int>(tSqlObject);
this.Length = dac.Column.Length.GetValue <int>(tSqlObject);
}
/// <summary>
/// Initializes a new instance of the <see cref="Table" /> class.
/// </summary>
/// <param name="tSqlObject">The TSqlObject representing the table.</param>
/// <param name="primaryKeys">The primary keys.</param>
/// <param name="foreignKeys">The foreign keys.</param>
public Table(dac.TSqlObject tSqlObject, IEnumerable <dac.TSqlObject> primaryKeys, IDictionary <dac.TSqlObject, IEnumerable <ForeignKeyConstraintDefinition> > foreignKeys)
{
// Get the name.
this.Name = tSqlObject.Name.Parts.Last();
// Get the columns
var columns = new List <Column>();
var sqlColumns = tSqlObject.ObjectType.Name == "TableType" ? tSqlObject.GetReferenced(dac.TableType.Columns) : tSqlObject.GetReferenced(dac.Table.Columns);
foreach (var sqlColumn in sqlColumns)
{
var column = new Column(sqlColumn, tSqlObject, primaryKeys, foreignKeys);
columns.Add(column);
}
this.Columns = columns;
}
/// <summary>
/// Initializes a new instance of the <see cref="Table" /> class.
/// </summary>
/// <param name="tSqlObject">The TSqlObject representing the table.</param>
/// <param name="primaryKeys">The primary keys.</param>
/// <param name="foreignKeys">The foreign keys.</param>
public Table(dac.TSqlObject tSqlObject)
{
// Get the name.
this.Name = tSqlObject.Name.Parts.Last();
// Get the columns
var columns = new List <Column>();
var sqlColumns = tSqlObject.ObjectType.Name == "TableType" ? tSqlObject.GetReferenced(dac.TableType.Columns) : tSqlObject.GetReferenced(dac.Table.Columns);
foreach (var sqlColumn in sqlColumns)
{
var column = new Column(sqlColumn);
columns.Add(column);
}
this.Columns = columns;
}
/// <summary>
/// Initializes a new instance of the <see cref="Column" /> class.
/// </summary>
/// <param name="tSqlObject">The TSqlObject representing the column.</param>
/// <param name="tSqlTable">The table or view this column belongs to.</param>
/// <param name="primaryKeys">The primary keys.</param>
/// <param name="foreignKeys">The foreign keys.</param>
public Column(dac.TSqlObject tSqlObject)
{
this.Name = tSqlObject.Name.Parts.Last();
if (tSqlObject.ObjectType.Name == "TableTypeColumn")
{
var sqlDataTypeName = tSqlObject.GetReferenced(dac.TableTypeColumn.DataType).ToList().First().Name.Parts.Last();
this.DataTypes = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, sqlDataTypeName);
this.IsIdentity = dac.TableTypeColumn.IsIdentity.GetValue <bool>(tSqlObject);
this.IsNullable = dac.TableTypeColumn.Nullable.GetValue <bool>(tSqlObject);
this.Precision = dac.TableTypeColumn.Precision.GetValue <int>(tSqlObject);
this.Scale = dac.TableTypeColumn.Scale.GetValue <int>(tSqlObject);
this.Length = dac.TableTypeColumn.Length.GetValue <int>(tSqlObject);
}
else
{
dac.ColumnType metaType = tSqlObject.GetMetadata <dac.ColumnType>(dac.Column.ColumnType);
switch (metaType)
{
case dac.ColumnType.Column:
case dac.ColumnType.ColumnSet:
SetProperties(tSqlObject);
break;
case dac.ColumnType.ComputedColumn:
// use the referenced column - this works for simple view referenced
// column but not for a computed expression like [Name] = [FirstName] + ' ' + [LastName]
var referenced = tSqlObject.GetReferenced().ToArray();
if (referenced.Length == 1)
{
var tSqlObjectReferenced = referenced[0];
SetProperties(tSqlObjectReferenced);
}
else
{
// TODO: how to get and evaluate the expression?
}
break;
}
}
}
/// <summary>
/// Initializes a new instance of the <see cref="Parameter" /> class.
/// </summary>
/// <param name="tSqlObject">The TSqlObject representing the parameter.</param>
/// <param name="primaryKeys">The primary keys.</param>
/// <param name="foreignKeys">The foreign keys.</param>
public Parameter(dac.TSqlObject tSqlObject, IEnumerable <dac.TSqlObject> primaryKeys, IDictionary <dac.TSqlObject, IEnumerable <ForeignKeyConstraintDefinition> > foreignKeys)
{
this.Name = tSqlObject.Name.Parts.Last().Trim('@');
this.IsOutput = dac.Parameter.IsOutput.GetValue <bool>(tSqlObject);
var dataType = tSqlObject.GetReferenced(dac.Parameter.DataType).ToList().FirstOrDefault();
if (dataType == null)
{
this.DataTypes = DataTypeHelper.Instance.GetMap(TypeFormat.DotNetFrameworkType, "Object");
}
else if (dataType.ObjectType.Name == "TableType")
{
this.TableValue = new Table(dataType, primaryKeys, foreignKeys);
}
else
{
var sqlDataTypeName = dataType.Name.Parts.Last();
this.DataTypes = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, sqlDataTypeName);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="Table" /> class.
/// </summary>
/// <param name="tSqlObject">The TSqlObject representing the table.</param>
/// <param name="primaryKeys">The primary keys.</param>
/// <param name="foreignKeys">The foreign keys.</param>
public Table(dac.TSqlObject tSqlObject)
{
if (tSqlObject == null)
{
throw new ArgumentNullException(nameof(tSqlObject));
}
// Get the name.
this.Name = tSqlObject.Name.Parts.Last();
// Get the columns
var columns = new List <Column>();
var sqlColumns = tSqlObject.ObjectType.Name == "TableType" ? tSqlObject.GetReferenced(dac.TableType.Columns) : tSqlObject.GetReferenced(dac.Table.Columns);
foreach (var sqlColumn in sqlColumns)
{
var column = new Column(sqlColumn);
columns.Add(column);
}
this.Columns = columns;
}
/// <summary>
/// Initializes a new instance of the <see cref="Procedure"/> class.
/// </summary>
/// <param name="prefix">The prefix used on stored procedure names.</param>
public Procedure(dac.TSqlObject tSqlObject)
{
this.Name = tSqlObject.Name.Parts.Last();
this.Schema = tSqlObject.Name.Parts.First();
var parser = new TSql150Parser(false);
IList <ParseError> errors;
var frag = parser.Parse(new StringReader(tSqlObject.GetScript()), out errors);
var selectVisitor = new SqlSharpener.SelectVisitor();
frag.Accept(selectVisitor);
var depends = tSqlObject.GetReferenced(dac.Procedure.BodyDependencies)
.Where(x => x.ObjectType.Name == "Column")
.ToList();
if (depends.Count() > 0)
{
var bodyColumnTypes = depends
.GroupBy(bd => string.Join(".", bd.Name.Parts))
.Select(grp => grp.First())
.ToDictionary(
key => string.Join(".", key.Name.Parts),
val => new DataType
{
Map = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, val.GetReferenced(dac.Column.DataType).FirstOrDefault()?.Name.Parts.Last()),
Nullable = dac.Column.Nullable.GetValue <bool>(val)
},
StringComparer.InvariantCultureIgnoreCase);
var unions = selectVisitor.Nodes.OfType <BinaryQueryExpression>().Select(bq => GetQueryFromUnion(bq)).Where(x => x != null);
var selects = selectVisitor.Nodes.OfType <QuerySpecification>().Concat(unions);
this.Selects = selects.Select(s => new Select(s, bodyColumnTypes)).ToList();
}
}
/// <summary>
/// Initializes a new instance of the <see cref="Column" /> class.
/// </summary>
/// <param name="tSqlObject">The TSqlObject representing the column.</param>
/// <param name="tSqlTable">The table or view this column belongs to.</param>
/// <param name="primaryKeys">The primary keys.</param>
/// <param name="foreignKeys">The foreign keys.</param>
public Column(dac.TSqlObject tSqlObject, dac.TSqlObject tSqlTable, IEnumerable <dac.TSqlObject> primaryKeys, IDictionary <dac.TSqlObject, IEnumerable <ForeignKeyConstraintDefinition> > foreignKeys)
{
this.Name = tSqlObject.Name.Parts.Last();
var fullName = string.Join(".", tSqlObject.Name.Parts);
this.IsPrimaryKey = primaryKeys.Any(p => string.Join(".", p.Name.Parts) == fullName);
// Get relationships where this column is the child.
IEnumerable <ForeignKeyConstraintDefinition> myForeignKeys;
foreignKeys.TryGetValue(tSqlTable, out myForeignKeys);
myForeignKeys = myForeignKeys ?? Enumerable.Empty <ForeignKeyConstraintDefinition>();
this.ParentRelationships = from f in myForeignKeys
where f.Columns.Any(c => c.Value == this.Name)
select new RelationshipIdentifier
{
TableOrView = f.ReferenceTableName.BaseIdentifier != null ? f.ReferenceTableName.BaseIdentifier.Value : null,
Schema = f.ReferenceTableName.SchemaIdentifier != null ? f.ReferenceTableName.SchemaIdentifier.Value : null,
Database = f.ReferenceTableName.DatabaseIdentifier != null ? f.ReferenceTableName.DatabaseIdentifier.Value : null,
Columns = f.ReferencedTableColumns.Select(c => c.Value)
};
this.IsForeignKey = this.ParentRelationships.Any();
// Get relationships where this column is the parent.
var childTables = foreignKeys.Where(f => f.Value.Any(v =>
v.ReferenceTableName.BaseIdentifier.Value == tSqlTable.Name.Parts.Last() &&
v.ReferencedTableColumns.Any(c => c.Value == this.Name)));
this.ChildRelationships = from t in childTables
from r in t.Value
let tableParts = t.Key.Name.Parts.Count()
select new RelationshipIdentifier
{
TableOrView = t.Key.Name.Parts.Last(),
Schema = tableParts > 1 ? t.Key.Name.Parts.ElementAt(tableParts - 2) : null,
Database = tableParts > 2 ? t.Key.Name.Parts.ElementAt(tableParts - 3) : null,
Columns = r.Columns.Select(c => c.Value)
};
if (tSqlObject.ObjectType.Name == "TableTypeColumn")
{
var sqlDataTypeName = tSqlObject.GetReferenced(dac.TableTypeColumn.DataType).ToList().First().Name.Parts.Last();
this.DataTypes = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, sqlDataTypeName);
this.IsIdentity = dac.TableTypeColumn.IsIdentity.GetValue <bool>(tSqlObject);
this.IsNullable = dac.TableTypeColumn.Nullable.GetValue <bool>(tSqlObject);
this.Precision = dac.TableTypeColumn.Precision.GetValue <int>(tSqlObject);
this.Scale = dac.TableTypeColumn.Scale.GetValue <int>(tSqlObject);
this.Length = dac.TableTypeColumn.Length.GetValue <int>(tSqlObject);
}
else
{
dac.ColumnType metaType = tSqlObject.GetMetadata <dac.ColumnType>(dac.Column.ColumnType);
switch (metaType)
{
case dac.ColumnType.Column:
case dac.ColumnType.ColumnSet:
SetProperties(tSqlObject);
break;
case dac.ColumnType.ComputedColumn:
// use the referenced column - this works for simple view referenced
// column but not for a computed expression like [Name] = [FirstName] + ' ' + [LastName]
var referenced = tSqlObject.GetReferenced().ToArray();
if (referenced.Length == 1)
{
var tSqlObjectReferenced = referenced[0];
SetProperties(tSqlObjectReferenced);
}
else
{
// TODO: how to get and evaluate the expression?
}
break;
}
}
}
