public static void StandardMergeSP(DatabaseObject table, DirectoryInfo outputFolder, string LoadingSchema, string prefix)
{
string sqlScript = Templates.GetTemplateContent(TemplateFolder, "Load_StandardMerge.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> whenMatchedColumns;
if (joinColumns.Count() >= 1)
{
whenMatchedColumns = table.Columns.Where(column => column.IsPrimaryKey == false && column.IsIdentity == false && !DatabaseObject.allStandardColumns.Contains(column.DatabaseColumnName.ToLower()) && column.DatabaseColumnName != joinColumns.First().DatabaseColumnName);
}
else
{
whenMatchedColumns = table.Columns.Where(column => column.IsPrimaryKey == false && column.IsIdentity == false && !DatabaseObject.allStandardColumns.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.GetJoinColumnSql(whenMatchedColumns, "T", "S", false, true), // {4} = when matched critera
table.GetUpdateColumnSql(updateColumns, "T", "S"), // {5} = update column list
table.GetColumnListSql(insertColumns, string.Empty), // {6} = insert list
table.GetColumnListSql(insertColumns, "S"), // {7} = insert value list
prefix, // {8} = prefix for the stored proc name
andClauseForDelete // {9} = 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 CreateDataMartView(DatabaseObject table, DirectoryInfo dirDimViews, string viewSchema, string columnList, string templateName)
{
CreateDataMartView(table, dirDimViews, viewSchema, columnList, templateName, null);
}
private static void CreateDataTranslationViews(DatabaseInfo database, DatabaseObject table, DirectoryInfo dirViews)
{
// clean up table names here
NamingCleanup.CleanDatabaseObjectName(table);
StringBuilder sb = new StringBuilder("create view [");
sb.Append(table.StagingAreaSchema);
sb.Append("].[dt_");
sb.Append(table.DatabaseObjectName);
sb.AppendLine("]");
sb.AppendLine("as");
sb.AppendLine(TemplateComponents.AutogenWarning());
sb.AppendLine("select");
// add LoadLogId column
sb.AppendLine("\t-- system fields");
sb.AppendLine("\t[LoadLogId],");
// sb.AppendLine("\t[DatabaseInfoId],");
// sb.AppendLine("\t[DatabaseGroupId],");
sb.AppendLine();
sb.AppendLine("\t-- staging table fields");
bool addComma = false;
//foreach (DatabaseColumn column in table.Columns)
//{
// if (table.StagingAreaSchema.StartsWith("stgX"))
// {
// // clean up Quantum column names here
// NamingCleanup.CleanColumnNames(column);
// }
//}
foreach (DatabaseColumn column in table.Columns.Where(x => x.UseColumn).OrderBy(x => x.DatabaseColumnName))
{
if (addComma)
{
sb.AppendLine(",");
}
if (column.DataType == column.TargetDataType && column.Length == column.TargetLength && column.Precision == column.TargetPrecision && column.Scale == column.TargetScale)
{
// no cast to apply
sb.AppendLine();
sb.Append("\t-- no cast from ");
sb.Append(column.DataType.ToLower());
sb.Append(column.GetDataTypeModifiers(false, false));
AddMaxLength(sb, column);
sb.AppendLine();
switch (column.TargetDataType.ToLower())
{
case "nvarchar":
case "nchar":
case "varchar":
case "char":
sb.Append("\t");
TrimStringAsRequired(sb, column);
break;
default:
sb.Append("\t[");
sb.Append(column.DatabaseColumnName);
sb.Append("]");
break;
}
}
else
{
// we have a specific cast to apply
sb.AppendLine();
sb.Append("\t-- was ");
sb.Append(column.DataType.ToLower());
sb.Append(column.GetDataTypeModifiers(false, false));
AddMaxLength(sb, column);
sb.AppendLine();
sb.Append("\t");
switch (column.TargetDataType.ToLower())
{
case "nvarchar":
case "varchar":
case "nchar":
case "char":
if (column.DataType == "text")
{
// source data type is text, so first do a cast e.g.
// cast(rtrim(ltrim(left(cast([notes_text] as [varchar](500)) ,500))) as [varchar](500)) as [NotesText]
sb.Append("cast(rtrim(ltrim(left(cast([");
sb.Append(column.DatabaseColumnName);
sb.Append("] as [");
sb.Append(column.TargetDataType);
sb.Append("](");
sb.Append(column.TargetLength);
sb.Append(")),");
sb.Append(column.TargetLength);
sb.Append("))) as [");
sb.Append(column.TargetDataType);
sb.Append("](");
sb.Append(column.TargetLength);
sb.Append("))");
}
else
{
TrimStringAsRequired(sb, column);
}
break;
case "bit":
if (column.DataType.ToLower() == "tinyint")
{
if (column.MaxValueInTable <= 1)
{
ReplaceTinyIntWithBit(sb, column);
}
else
{
// leave as tinyint (no cast)
sb.Append("[");
sb.Append(column.DatabaseColumnName);
sb.Append("]");
}
}
else
{
ReplaceTinyIntWithBit(sb, column);
}
break;
case "decimal":
case "numeric":
sb.Append("cast([");
sb.Append(column.DatabaseColumnName);
sb.Append("] as ");
sb.Append(column.TargetDataType);
sb.Append("(");
sb.Append(column.Precision);
sb.Append(",");
sb.Append(column.Scale);
sb.Append("))");
break;
default:
sb.Append("cast([");
sb.Append(column.DatabaseColumnName);
sb.Append("] as ");
sb.Append(column.TargetDataType);
sb.Append(")");
break;
}
}
sb.Append(" as [");
sb.Append(column.TargetColumnName);
sb.Append("]");
addComma = true;
}
sb.AppendLine("");
sb.Append("from ");
sb.Append(table.StagingAreaSchema);
sb.Append(".[");
sb.Append(table.DatabaseObjectName);
sb.Append("]");
sb.AppendLine(";");
string sqlPath = Path.Combine(dirViews.FullName, table.DatabaseObjectName + ".sql");
//Console.WriteLine("Writing View {0}", sqlPath);
Console.Write("v");
System.IO.File.WriteAllText(sqlPath, sb.ToString());
}
