public static void ProcessTSqlObjectIntoDimensionScriptFiles(ParsedArgs parsedArgs, TSqlObject table, ModelRelationshipClass relationshipTypeColumns, ModelRelationshipClass relationshipTypeSchema)
{
string stagingSchemaName = GetSchemaName(table);
string templateDimCoreName = GetObjectName(table).Replace("_dimSrc_stg", "");
List <String> listOfColumns = new List <String>();
foreach (var col in table.GetReferenced(relationshipTypeColumns, DacQueryScopes.UserDefined))
{
String column = GetColumnName(col);
listOfColumns.Add(column);
}
GenerateDimension(listOfColumns, templateDimCoreName, parsedArgs, stagingSchemaName);
}
static void Main(string[] args)
{
ParsedArgs parsedArgs = new ParsedArgs(args);
Microsoft.SqlServer.Dac.Model.TSqlModel sqlModel = new Microsoft.SqlServer.Dac.Model.TSqlModel(parsedArgs.DacPacFileName);
//Where takes a predicate thing. No, I haven't figured out how to do that without lambda stuff yet. But this is tolerably readable for main control flow, I think.
var tables = sqlModel.GetObjects(DacQueryScopes.Default, Table.TypeClass).ToList().Where(table => table.Name.ToString().EndsWith("_dimSrc_stg]"));
var views = sqlModel.GetObjects(DacQueryScopes.Default, View.TypeClass).ToList().Where(view => view.Name.ToString().EndsWith("_dimSrc_stg]"));
foreach (var table in tables)
{
ProcessTSqlObjectIntoDimensionScriptFiles(parsedArgs, table, Table.Columns, Table.Schema);
}
foreach (var view in views)
{
ProcessTSqlObjectIntoDimensionScriptFiles(parsedArgs, view, View.Columns, View.Schema);
}
Console.WriteLine("Press any key to close!");
Console.ReadLine();
}
public static void GenerateDimension(List <string> listOfColumns, String templateDimCoreName, ParsedArgs parsedArgs, String StagingSchema)
{
List <String> listOfNks = listOfColumns.Where(mystring => mystring.StartsWith("NK_")).ToList <String>();
LineProcessorConfig lineProcessorConfigNK = new LineProcessorConfig("NaturalKey_ReplacementPoint", listOfNks);
List <String> listOfDims = listOfColumns.Where(mystring => !mystring.StartsWith("Ctl_")).Where(mystring => !mystring.StartsWith("NK_")).ToList <String>();
LineProcessorConfig lineProcessorConfigDim = new LineProcessorConfig("DimensionAttribute_ReplacementPoint", listOfDims);
List <String> listOfCtl = listOfColumns.Where(mystring => mystring.StartsWith("Ctl_")).ToList <String>();
//We should error out if table/view exists and listOfCtl <> List<String>{"Ctl_EffectiveDate"}
//Something like:
//method list all files in template directory to output directory, get string renaming them to swap out the templateDimCoreName in the file name.
//StreamReader the source file. Foreach line, create line processor for nk, get back line. Create line processor for dim (with that returned line), get back line.
//StreamWriter out the line to a file with the new name in output directory.
//
List <String> templateFiles = new List <string> {
"Stored Procedures\\templateDimCoreName_dimSetup_Idempotent_MissingMember_usp.sql"
, "Stored Procedures\\templateDimCoreName_dimUpsert_Step1_Update_usp.sql"
, "Stored Procedures\\templateDimCoreName_dimUpsert_Step2_Insert_usp.sql"
, "Stored Procedures\\templateDimCoreName_orchestration_usp.sql"
, "Stored Procedures\\templateDimCoreName_predim_orchestration_usp.sql"
, "Stored Procedures\\templateDimCoreName_predim_setup_clearTables_usp.sql"
, "Stored Procedures\\templateDimCoreName_predim_Step1_copycurrent_usp.sql"
, "Stored Procedures\\templateDimCoreName_predim_Step2_prep_usp.sql"
, "Stored Procedures\\templateDimCoreName_predim_Step3_prep_updateSCDStatus_usp.sql"
, "Stored Procedures\\templateDimCoreName_predim_Step4_prep_DeleteIgnorable.sql"
, "Stored Procedures\\templateDimCoreName_predim_Step5_Check_OneRowPerKey_usp.sql"
//,"Tables\\templateDimCoreName_dimSrc_stg.sql"
, "Tables\\templateDimCoreName_predim_copycurrent.sql"
, "Tables\\templateDimCoreName_predim_prep.sql"
, "Views\\templateDimCoreName_dimUpsert_GetMaxSK_vw.sql"
, "Views\\templateDimCoreName_dimUpsert_Step2_Insert_SelectClause_vw.sql"
, "Views\\templateDimCoreName_FindUpdates_vw.sql"
, "Views\\templateDimCoreName_predim_Step2_prep_columnTransformations_vw.sql"
};
foreach (String file in templateFiles)
{
TransformFileInFlight(templateDimCoreName, parsedArgs.SCDType6TemplateDirectory, parsedArgs.OutputDirectory, parsedArgs.DimensionSchema, parsedArgs.templateSchema, StagingSchema, lineProcessorConfigNK, lineProcessorConfigDim, file);
}
string dimFile = "Tables\\templateDimCoreName_Dim.sql";
TransformFileInFlight(templateDimCoreName, parsedArgs.SCDType6DimensionDirectory, parsedArgs.OutputDirectory, parsedArgs.DimensionSchema, parsedArgs.templateSchema, StagingSchema, lineProcessorConfigNK, lineProcessorConfigDim, dimFile);
string dimSKLookupFile = "Functions\\templateDimCoreName_DimSKLookup_usvf.sql";
TransformFileInFlight(templateDimCoreName, parsedArgs.SCDType6DimensionDirectory, parsedArgs.OutputDirectory, parsedArgs.DimensionSchema, parsedArgs.templateSchema, StagingSchema, lineProcessorConfigNK, lineProcessorConfigDim, dimSKLookupFile);
Console.WriteLine(templateDimCoreName);
Console.WriteLine(String.Join("\r\n", lineProcessorConfigDim.perLineSubstitutions.ToArray()));
Console.WriteLine(String.Join("\r\n", lineProcessorConfigNK.perLineSubstitutions.ToArray()));
}
