private static string InsertUnknowns(string LoadingSchema, DatabaseObject table)
{
string sqlScript = Templates.GetTemplateContent(DatabaseName, "InsertUnknowns.sql");
string sql = string.Empty;
if (table.Columns.Count(column => column.IsPrimaryKey) > 0)
{
string setIdentity = "--";
if (table.Columns.Where(column => column.IsIdentity == true).Count() > 0)
{
setIdentity = string.Empty;
}
string[] excludeColumnsFromInsertView = { DatabaseObject.updatedloadlogid };
IEnumerable <DatabaseColumn> filteredColumns = table.Columns.Where(column => (!excludeColumnsFromInsertView.Contains(column.DatabaseColumnName.ToLower()) && !column.IsIdentity && !column.IsNullable));
sql = string.Format(sqlScript,
table.SchemaName,
table.DatabaseObjectName,
table.DatabaseObjectName + "Key",
table.GetColumnListSql(filteredColumns, string.Empty),
table.GetUnknownList(filteredColumns, "N'UNKNOWN'", "N'U'", -1),
-1,
setIdentity);
}
return(sql);
}
private static void CreateDataMartView(DatabaseObject table, DirectoryInfo dirDimViews, string viewSchema, string columnList, string templateName, string outputFileName)
{
if (templateName == null)
{
templateName = "CreateDataMartView.sql";
}
if (outputFileName == null)
{
outputFileName = table.DatabaseObjectName;
}
string sqlScript = Templates.GetTemplateContent(DatabaseName, templateName);
string sql = string.Format(sqlScript,
viewSchema, // {0} = view schema name
outputFileName, // {1} = table name
table.SchemaName, // {2} = table schema
columnList // {3} = column list
);
string sqlPath = Path.Combine(dirDimViews.FullName, string.Format("{0}.sql", outputFileName));
Console.Write("v");
System.IO.File.WriteAllText(sqlPath, sql);
}
private static void InsertUnknownsScript(DirectoryInfo dirPostDeployFolder, string insertUnknowns)
{
string sqlScript = Templates.GetTemplateContent(DatabaseName, "InsertUnknownsScript.sql");
string sql = string.Format(sqlScript, insertUnknowns);
string sqlPath = Path.Combine(dirPostDeployFolder.FullName, "AddNegativePKToDimTables.sql");
Console.WriteLine("Generated AddNegativePKToDimTables.sql");
System.IO.File.WriteAllText(sqlPath, sql);
}
private static void CheckInsertViews(DirectoryInfo dirProcs, string AllChecks)
{
string sqlScript = Templates.GetTemplateContent(DatabaseName, "CheckInsertViews.sql");
string sql = string.Format(sqlScript, LoadingSchema, AllChecks);
string sqlPath = Path.Combine(dirProcs.FullName, "CheckInsertViews.sql");
Console.WriteLine();
Console.WriteLine("Generated CheckInsertViews.sql");
System.IO.File.WriteAllText(sqlPath, sql);
}
public static void SimpleMergeSP(DatabaseObject table, DirectoryInfo outputFolder, string LoadingSchema, string prefix)
{
string sqlScript = Templates.GetTemplateContent(TemplateFolder, "Load_SimpleMerge.sql");
IEnumerable <DatabaseColumn> joinColumns = table.Columns.Where(column => column.IsPrimaryKey == true);
if (prefix == null)
{
prefix = string.Empty;
}
if (joinColumns.Count() == 0)
{
Console.WriteLine();
Console.WriteLine("Target table has no primary key(s) so cannot create correctly formed MERGE statement {0}", table.DatabaseObjectName);
Console.WriteLine();
}
IEnumerable <DatabaseColumn> updateColumns;
if (joinColumns.Count() >= 1)
{
updateColumns = table.Columns.Where(column => column.IsPrimaryKey == false && column.IsIdentity == false && !DatabaseObject.nonUpdateableStandardColumns.Contains(column.DatabaseColumnName.ToLower()) && column.DatabaseColumnName != joinColumns.First().DatabaseColumnName);
}
else
{
updateColumns = table.Columns.Where(column => column.IsPrimaryKey == false && column.IsIdentity == false && !DatabaseObject.nonUpdateableStandardColumns.Contains(column.DatabaseColumnName.ToLower()));
}
IEnumerable <DatabaseColumn> insertColumns = table.Columns.Where(column => column.DatabaseColumnName.ToLower() != DatabaseObject.updatedloadlogid && column.DatabaseColumnName.ToLower() != DatabaseObject.loadlogid && column.IsIdentity == false);
IEnumerable <DatabaseColumn> identityColumns = table.Columns.Where(column => column.IsIdentity);
string andClauseForDelete = string.Empty;
if (table.DatabaseObjectName.ToLower().StartsWith("dim") && identityColumns.Count() >= 1)
{
andClauseForDelete = string.Format("and ({0} > -1) ", identityColumns.First().DatabaseColumnName);
}
string sql = string.Format(sqlScript,
LoadingSchema, // {0} = loading schema name
table.DatabaseObjectName, // {1} = table name
table.SchemaName, // {2} = table schema
table.GetJoinColumnSql(joinColumns, "T", "S", false, false), // {3} = join criteia
table.GetUpdateColumnSql(updateColumns, "T", "S"), // {4} = update column list
table.GetColumnListSql(insertColumns, string.Empty), // {5} = insert list
table.GetColumnListSql(insertColumns, "S"), // {6} = insert value list
prefix, // {7} = prefix for the stored proc name
andClauseForDelete // {8} = and clause for the delete part of merge
);
string sqlPath = Path.Combine(outputFolder.FullName, string.Format("Load_{0}{1}.sql", prefix, table.DatabaseObjectName));
Console.Write(".");
System.IO.File.WriteAllText(sqlPath, sql);
}
private static void CreateLoadViewStub(DatabaseObject table, DirectoryInfo dirLoadViewSubs, string viewSchema, string columnList, string StagingAreaSchema)
{
string sqlScript = Templates.GetTemplateContent(DatabaseName, "CreateLoadViewStub.sql");
string sql = string.Format(sqlScript,
viewSchema, // {0} = view schema name
table.DatabaseObjectName, // {1} = table name
columnList, // {2} = column list
"dt_" + table.DatabaseObjectName, // {3} = data translation list
StagingAreaSchema // {4} = staging area schema
);
string sqlPath = Path.Combine(dirLoadViewSubs.FullName, string.Format("{0}Insert.sql", table.DatabaseObjectName));
Console.Write("s");
System.IO.File.WriteAllText(sqlPath, sql);
}
public static void StandardInsertSP(DatabaseObject table, DirectoryInfo outputFolder, string LoadingSchema, string prefix)
{
string sqlScript = Templates.GetTemplateContent(TemplateFolder, "Load_StandardInsert.sql");
if (prefix == null)
{
prefix = string.Empty;
}
string sql = string.Format(sqlScript,
LoadingSchema, // {0} = loading schema name
table.DatabaseObjectName, // {1} = table name
table.SchemaName, // {2} = table schema
table.GetColumnListSql(table.Columns.Where(column => !DatabaseObject.standardColumns.Contains(column.DatabaseColumnName.ToLower())), string.Empty), // {3} = column list
prefix // {4} = prefix for the stored proc name
);
string sqlPath = Path.Combine(outputFolder.FullName, string.Format("Load_{0}{1}.sql", prefix, table.DatabaseObjectName));
Console.Write(".");
System.IO.File.WriteAllText(sqlPath, sql);
}
public static string CheckInsertView(string LoadingSchema, string DatabaseObjectName, string AllColumns)
{
string sqlScript = Templates.GetTemplateContent(TemplateFolder, "CheckInsertView.sql");
return(string.Format(sqlScript, LoadingSchema, DatabaseObjectName, AllColumns));
}
public static string PleaseAdjust()
{
return(Templates.GetTemplateContent(TemplateFolder, "PleaseAdjust.sql"));
}
public static string AutogenWarning()
{
return(Templates.GetTemplateContent(TemplateFolder, "AutogenWarning.sql"));
}