public ColumnRelationship(IColumn fromColumn, IColumn toColumn, RelationshipOrigin relationshipOrigin)
{
FromColumn = fromColumn;
Identifier = $"{fromColumn.MultiPartIdentifier}.{toColumn.MultiPartIdentifier}";
ToColumn = toColumn;
RelationshipOrigin = relationshipOrigin;
}
public TableRelationship(IReadOnlyDictionary <string, IMetadataIdentifier> metadataIdentifierByMultiPartIdentifier, string fromTableMultiPartIdentifier, string toTableMultiPartIdentifier, RelationshipOrigin relationshipOrigin)
{
FromTable = (ITable)metadataIdentifierByMultiPartIdentifier[fromTableMultiPartIdentifier];
ToTable = (ITable)metadataIdentifierByMultiPartIdentifier[toTableMultiPartIdentifier];
Source = fromTableMultiPartIdentifier;
Target = toTableMultiPartIdentifier;
RelationshipOrigin = relationshipOrigin;
}
public IEnumerable <(IColumn FromColumn, IColumn ToColumn, RelationshipOrigin RelationshipOrigin)> GetColumnRelationshipsWithSqlModuleDefinition(IEnumerable <IServer> servers, RelationshipOrigin relationshipOrigin)
{
foreach (var server in servers)
{
foreach (var database in server.Databases)
{
using (var sqlConnection = new SqlConnection($"Data Source={server.ServerName};Initial Catalog={database.DatabaseName};Integrated Security=true"))
{
IEnumerable <string> sqlModuleDefinitions;
try
{
sqlModuleDefinitions = sqlConnection.Query <string>(@"
SELECT SM.DEFINITION AS SqlModuleDefinition
FROM SYS.OBJECTS O
INNER JOIN SYS.SQL_MODULES SM ON O.OBJECT_ID = SM.OBJECT_ID;
");
}
catch (Exception)
{
yield break;
}
foreach (var sqlModuleDefinition in sqlModuleDefinitions)
{
foreach (var columnRelationship in GetColumnRelationshipsByChangeScriptDefinition(sqlModuleDefinition, server.ServerName, database.DatabaseName, relationshipOrigin))
{
yield return(columnRelationship.FromColumn, columnRelationship.ToColumn, relationshipOrigin);
}
}
}
}
}
}
public IEnumerable <(IColumn FromColumn, IColumn ToColumn, RelationshipOrigin RelationshipOrigin)> GetColumnRelationshipsWithChangeScriptReference(string dataDirectoryPath, RelationshipOrigin relationshipOrigin)
{
var dataDirectoryUri = new Uri(dataDirectoryPath);
if (!Directory.Exists(dataDirectoryUri.LocalPath))
{
yield break;
}
var repositoryPath = Repository.Discover(dataDirectoryPath);
if (string.IsNullOrEmpty(repositoryPath))
{
yield break;
}
using (var repository = new Repository(repositoryPath))
{
var contextualServerName = dataDirectoryUri.Host.ToLower();
var head = repository.Head;
var tip = head.Tip;
var tree = tip.Tree;
foreach (var databaseNameWithFileContent in GetDatabaseNameWithFileContent(contextualServerName, tree))
{
var transactSqlText = databaseNameWithFileContent.FileContent;
var databaseName = databaseNameWithFileContent.DatabaseName;
foreach (var columnRelationship in GetColumnRelationshipsByChangeScriptDefinition(transactSqlText, contextualServerName, databaseName, relationshipOrigin))
{
yield return(columnRelationship.FromColumn, columnRelationship.ToColumn, relationshipOrigin);
}
}
}
}
public IEnumerable <(IColumn FromColumn, IColumn ToColumn, RelationshipOrigin RelationshipOrigin)> GetColumnRelationshipsWithKeyReferenceDefinition(IEnumerable <IServer> servers, RelationshipOrigin relationshipOrigin)
{
foreach (var server in servers)
{
foreach (var database in server.Databases)
{
using (var sqlConnection = new SqlConnection($"Data Source={server.ServerName};Initial Catalog={database.DatabaseName};Integrated Security=true"))
{
IEnumerable <dynamic> keyReferenceDefinitions;
try
{
keyReferenceDefinitions = sqlConnection.Query(@"
WITH ObjectExpression
AS (
SELECT @@SERVERNAME AS ServerName
, DB_NAME() AS DatabaseName
, ISC.TABLE_SCHEMA AS SchemaName
, O.NAME AS TableName
, ISC.COLUMN_NAME AS ColumnName
, O.OBJECT_ID AS TableIdentifier
, C.COLUMN_ID AS ColumnIdentifier
FROM SYS.OBJECTS O
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS ISC ON SCHEMA_NAME(O.SCHEMA_ID) = ISC.TABLE_SCHEMA AND O.NAME = ISC.TABLE_NAME
INNER JOIN SYS.COLUMNS AS C ON O.OBJECT_ID = C.OBJECT_ID AND ISC.COLUMN_NAME = C.NAME
), ColumnRelationshipExpression
AS (
SELECT ObjectExpressionFrom.ServerName AS FromServerName
, ObjectExpressionFrom.DatabaseName AS FromDatabaseName
, ObjectExpressionFrom.SchemaName AS FromSchemaName
, ObjectExpressionFrom.TableName AS FromTableName
, ObjectExpressionFrom.ColumnName AS FromColumnName
, ObjectExpressionTo.ServerName AS ToServerName
, ObjectExpressionTo.DatabaseName AS ToDatabaseName
, ObjectExpressionTo.SchemaName AS ToSchemaName
, ObjectExpressionTo.TableName AS ToTableName
, ObjectExpressionTo.ColumnName AS ToColumnName
FROM SYS.FOREIGN_KEY_COLUMNS AS FKC
INNER JOIN ObjectExpression AS ObjectExpressionFrom ON FKC.PARENT_OBJECT_ID = ObjectExpressionFrom.TableIdentifier AND ObjectExpressionFrom.ColumnIdentifier = FKC.PARENT_COLUMN_ID
INNER JOIN ObjectExpression AS ObjectExpressionTo ON FKC.REFERENCED_OBJECT_ID = ObjectExpressionTo.TableIdentifier AND ObjectExpressionTo.ColumnIdentifier = FKC.REFERENCED_COLUMN_ID
)
SELECT LOWER(CONCAT(FromServerName, '.', FromDatabaseName, '.', FromSchemaName, '.', FromTableName, '.', FromColumnName, '.',
ToServerName, '.', ToDatabaseName, '.', ToSchemaName, '.', ToTableName, '.', ToColumnName)) AS Identifier
, LOWER(CONCAT(FromServerName, '.', FromDatabaseName, '.', FromSchemaName, '.', FromTableName, '.', FromColumnName)) AS FromColumnMultiPartIdentifier
, LOWER(CONCAT(ToServerName, '.', ToDatabaseName, '.', ToSchemaName, '.', ToTableName, '.', ToColumnName)) AS ToColumnMultiPartIdentifier
FROM ColumnRelationshipExpression
ORDER BY FromColumnMultiPartIdentifier
, ToColumnMultiPartIdentifier
");
}
catch (Exception)
{
yield break;
}
foreach (var keyReferenceDefinition in keyReferenceDefinitions)
{
var fromColumn = _metadataIdentifierByMultiPartIdentifier[keyReferenceDefinition.FromColumnMultiPartIdentifier];
var toColumn = _metadataIdentifierByMultiPartIdentifier[keyReferenceDefinition.ToColumnMultiPartIdentifier];
yield return(fromColumn, toColumn, relationshipOrigin);
}
}
}
}
}
public IEnumerable <(IColumn FromColumn, IColumn ToColumn, RelationshipOrigin RelationshipOrigin)> GetColumnRelationshipsByDatabaseStructuralCloneReference(IEnumerable <IServer> servers, RelationshipOrigin relationshipOrigin)
{
yield break;
}
public IEnumerable <IColumnRelationship> ParseBooleanComparisonExpression(BooleanComparisonExpression booleanComparisonExpression, IDictionary <string, ITable> tableByAliasIdentifier, IList <int> booleanComparisonExpressionIdentifiers, RelationshipOrigin relationshipOrigin)
{
if (booleanComparisonExpression.ComparisonType == BooleanComparisonType.NotEqualToBrackets || booleanComparisonExpression.ComparisonType == BooleanComparisonType.NotEqualToExclamation)
{
yield break;
}
var columnReferenceExpressions = _visitorProvider.GetColumnReferenceExpressions(booleanComparisonExpression).AsList();
if (columnReferenceExpressions.Count != 2)
{
yield break;
}
var fromColumnReferenceExpression = columnReferenceExpressions[0];
if (!TryParseColumn(fromColumnReferenceExpression, tableByAliasIdentifier, out IColumn fromColumn))
{
yield break;
}
var toColumnReferenceExpression = columnReferenceExpressions[1];
if (!TryParseColumn(toColumnReferenceExpression, tableByAliasIdentifier, out IColumn toColumn))
{
yield break;
}
var booleanComparisonExpressionIdentifier = booleanComparisonExpression.FirstTokenIndex + booleanComparisonExpression.LastTokenIndex;
if (booleanComparisonExpressionIdentifiers.Contains(booleanComparisonExpressionIdentifier))
{
yield break;
}
booleanComparisonExpressionIdentifiers.Add(booleanComparisonExpressionIdentifier);
var columnRelationship = new ColumnRelationship(fromColumn, toColumn, relationshipOrigin);
yield return(columnRelationship);
}
public IEnumerable <IColumnRelationship> ParseViewTableColumnToBaseTableColumnRelationships(TSqlStatement tsqlStatement, string contextualServerName, string contextualDatabaseName, RelationshipOrigin relationshipOrigin)
{
var viewStatementBody = tsqlStatement as ViewStatementBody;
if (viewStatementBody == null)
{
yield break;
}
if (!TryParseTable(contextualServerName, contextualDatabaseName, viewStatementBody.SchemaObjectName, out ITable viewTable))
{
yield break;
}
var selectStatement = viewStatementBody.SelectStatement;
var queryExpression = selectStatement.QueryExpression;
var querySpecification = queryExpression as QuerySpecification;
if (!(queryExpression is QuerySpecification))
{
yield break;
}
var selectElements = querySpecification.SelectElements;
if (viewTable.Columns.Count != selectElements.Count)
{
yield break;
}
var viewStatementFromClause = querySpecification.FromClause;
if (!TryParseTableByAliasIdentifier(viewStatementFromClause, contextualServerName, contextualDatabaseName, out IDictionary <string, ITable> viewTableByAliasIdentifier))
{
yield break;
}
for (var index = 0; index < selectElements.Count; index++)
{
var selectElement = selectElements[index];
var selectScalarExpression = (SelectScalarExpression)selectElement;
var scalarExpression = selectScalarExpression.Expression;
var columnReferenceExpression = scalarExpression as ColumnReferenceExpression;
if (!(scalarExpression is ColumnReferenceExpression))
{
continue;
}
var viewColumns = viewTable.Columns.AsList();
var viewTableColumn = viewColumns[index];
if (!TryParseColumn(columnReferenceExpression, viewTableByAliasIdentifier, out IColumn baseTableColumn))
{
continue;
}
var columnRelationship = new ColumnRelationship(viewTableColumn, baseTableColumn, relationshipOrigin);
yield return(columnRelationship);
}
}
public IEnumerable <IColumnRelationship> ParseFromClauseRelationships(TSqlStatement tsqlStatement, string contextualServerName, string contextualDatabaseName, IList <int> booleanComparisonExpressionIdentifiers, RelationshipOrigin relationshipOrigin)
{
foreach (var fromClause in _visitorProvider.GetFromClauses(tsqlStatement))
{
if (!IsParsableFromClause(fromClause))
{
continue;
}
if (!TryParseTableByAliasIdentifier(fromClause, contextualServerName, contextualDatabaseName, out IDictionary <string, ITable> tableByAliasIdentifier))
{
continue;
}
foreach (var booleanComparisonExpression in _visitorProvider.GetBooleanComparisonExpressions(fromClause))
{
foreach (var columnRelationship in ParseBooleanComparisonExpression(booleanComparisonExpression, tableByAliasIdentifier, booleanComparisonExpressionIdentifiers, relationshipOrigin))
{
if (columnRelationship.FromColumn.Table.MultiPartIdentifier == columnRelationship.ToColumn.Table.MultiPartIdentifier)
{
continue;
}
yield return(columnRelationship);
}
}
}
}
public IEnumerable <IColumnRelationship> GetColumnRelationshipsByChangeScriptDefinition(string sql, string contextualServerName, string contextualDatabaseName, RelationshipOrigin relationshipOrigin)
{
sql = sql.ToLower();
contextualServerName = contextualServerName.ToLower();
contextualDatabaseName = contextualDatabaseName.ToLower();
var tsqlScript = (TSqlScript)_sqlParserFacade.Parse(sql, out IList <ParseError> parseErrors);
if (parseErrors.Any())
{
yield break;
}
var booleanComparisonExpressionIdentifiers = new List <int>();
foreach (var tsqlBatch in tsqlScript.Batches)
{
for (var index = 0; index < tsqlBatch.Statements.Count; index++)
{
var tsqlStatement = tsqlBatch.Statements[index];
switch (tsqlStatement)
{
case PrintStatement printStatement:
continue;
case ExecuteStatement executeStatement:
foreach (var literal in _visitorProvider.GetLiteral(executeStatement))
{
if (literal.LiteralType != LiteralType.String)
{
continue;
}
var nestedTSqlScript = (TSqlScript)_sqlParserFacade.Parse(literal.Value, out IList <ParseError> nestedParseErrors);
if (nestedParseErrors.Any())
{
continue;
}
foreach (var nestedTSqlBatch in nestedTSqlScript.Batches)
{
foreach (var nestedTsqlStatements in nestedTSqlBatch.Statements)
{
tsqlBatch.Statements.Insert(index + 1, nestedTsqlStatements);
}
}
}
break;
case UseStatement useStatement:
var usingDatabaseName = useStatement.DatabaseName.Value.ToLower();
if (usingDatabaseName != "master" && usingDatabaseName != contextualDatabaseName)
{
contextualDatabaseName = usingDatabaseName;
}
continue;
}
foreach (var columnRelationship in ParseFromClauseRelationships(tsqlStatement, contextualServerName, contextualDatabaseName, booleanComparisonExpressionIdentifiers, relationshipOrigin))
{
yield return(columnRelationship);
}
foreach (var columnRelationship in ParseViewTableColumnToBaseTableColumnRelationships(tsqlStatement, contextualServerName, contextualDatabaseName, relationshipOrigin))
{
yield return(columnRelationship);
}
}
}
}
