public static string BuildMergeSproc(this MergeVariables variables)
{
var sproc = "";
if (variables.MergeIdentifierColumns.Any())
{
sproc = $@"{General.Head()}
{MergeSproc.Drop(variables.SchemaName, variables.ProcedureName())}
{TableType.Drop(variables.TableTypeName(), variables.SchemaName)}
{TableType.Head(variables.TableTypeName(), variables.SchemaName)}
{TableType.Body(variables.TableTypeName(), variables.SchemaName, variables.TableTypeColumns)}
{TableType.Tail(variables.TableTypeName(), variables.SchemaName)}
{MergeSproc.Head(variables.SchemaName, variables.ProcedureName(), variables.TableTypeVariableName(), variables.TableTypeName())}";
if (variables.TablesToJoin != null && variables.TablesToJoin.Any())
{
sproc += $@"{MergeSproc.TableVariable(variables.ProcedureName(), variables.Table, variables.TableTypeVariableName(), variables.SchemaName, variables.TablesToJoin)}
{MergeSproc.Body($"{variables.ProcedureName()}Variable", variables.MergeIdentifierColumns.Select(c => c.Name), variables.DeleteIdentifierColumns.Select(c => c.Name), variables.Table, variables.SchemaName)}";
}
else
{
sproc += $"{MergeSproc.Body(variables.TableTypeVariableName(), variables.MergeIdentifierColumns.Select(c => c.Name), variables.DeleteIdentifierColumns.Select(c => c.Name), variables.Table, variables.SchemaName)}";
}
sproc += $@"{MergeSproc.Tail(variables.SchemaName, variables.ProcedureName())}"; //got to figure out how to use the table type columns in the merge body (getting id's for columns that don't exist)
}
return(sproc);
}
public MergeStatement(
string statement,
MergeVariables variables)
{
Statement = statement;
Variables = variables;
}
public async Task <IEnumerable <MergeStatement> > GenerateMergeSprocs(string connectionString, IEnumerable <string> schemaNames)
{
Schema databaseSchema = null;
var schemaName = "Analytics";
/*
* build up database objects
*/
try
{
var databaseResult = await _dataAccess.GetDatabase(connectionString);
var database = await _databaseStructure.CalculateStructure(connectionString.DatabaseName(), databaseResult);
// var databaseInformation = await _dataAccess.GetDatabaseInformation(connectionString);
//
// var columnInfo = databaseInformation.Columns;
//
// var fkInfo = databaseInformation.ForeignKeys;
//
// var extendedProps = databaseInformation.ExtendedProperties;
//
// databaseSchema = Schema
// .From(columnInfo, extendedProps, fkInfo)
// .Where(s => s.Name.Equals(schemaName))
// .FirstOrDefault();
//
// databaseSchema
// .CalculateDependencies();
//
// logger.LogInformation("Read schema information for [{schemaName}] with [{tableCount}] tables",
// schemaName,
// databaseSchema.Tables.Count());
}
catch (Exception ex)
{
logger.LogError(
"Unable to calculate schema: [{schemaName}] due to: {ex}",
schemaName,
ex.Message);
}
//
//calculate dependency tree and build data structure
//
var dependencyTree = DependencyTree.Calculate(databaseSchema);
var dataStructure = new DataStructure(databaseSchema, dependencyTree);
logger.LogInformation("Calculated dependency tree with [{branchCount}] branches",
dependencyTree.Branches.Count());
//
//build up merge variables
//
var(mergeVariables, errornousTables) = MergeVariables.From(dataStructure);
if (errornousTables.Any())
{
logger.LogError("Tables that won't generate merge sprocs: [{tables}], due to exceptions: [{exceptions}]",
dataStructure.Schema.Tables.Select(t => t.Name).Except(mergeVariables.Select(m => m.Table.Name)),
errornousTables);
}
logger.LogInformation("Calculated [{mergeVariableCount}] merge variables",
mergeVariables.Count());
//
//Create merge statements
//
var mergeStatements = new List <MergeStatement>();
foreach (var mergeVariable in mergeVariables)
{
var script = await _scriptFactory.ScriptFrom(mergeVariable);
mergeStatements.Add(new MergeStatement(script, mergeVariable));
}
logger.LogInformation("Built [{mergeStatementCount}] merge statements",
mergeStatements.Count());
return(mergeStatements);
}
