/// <summary>
/// Get output part of SCD sql command
/// </summary>
/// <param name="tempTableName">SCD temporary table name</param>
/// <returns>Output part of SCD sql command</returns>
private string GetOutputPart(string tempTableName)
{
string result = TEMPLATE_OUTPUT;
ScdColumnList scdListBKs = new ScdColumnList();
ScdColumnList scdListAttributes = new ScdColumnList();
foreach (string scdTableName in _scdList.Keys)
{
scdListBKs.AddList(_scdList[scdTableName].BkList);
scdListAttributes.AddList(_scdList[scdTableName].AttributeList);
}
result = result.Replace("<tempTableName_scd>", tempTableName);
result = result.Replace("<attribute_select>", SCDHelper.GetSqlAttributeListDoubled(scdListAttributes.ScdColumns, "src.", "DELETED.", "", "", 2));
result = result.Replace("<vaild_from_select>", SCDHelper.GetSqlValidFrom(_scdList, false, "src.", 2));
result = result.Replace("<attribute_insert>", SCDHelper.GetSqlAttributeListDoubled(scdListAttributes.ScdColumns, "", "",
SCDConfiguration.POSTFIX_COLUMN_QUELLE, SCDConfiguration.POSTFIX_COLUMN_DWH_VOR_UPDATE, 2));
result = result.Replace("<vaild_from_insert>", SCDHelper.GetSqlValidFrom(_scdList, true, "", 2));
result = result.Replace("<bk_output>", SCDHelper.GetSqlBkList(scdListBKs.ScdColumns, "INSERTED.", 2));
result = result.Replace("<bk_into>", SCDHelper.GetSqlBkList(scdListBKs.ScdColumns, _prefixFK, "", 2));
result = result.Replace("<FK_ID>", _prefixFK + "ID");
return(result);
}
/// <summary>
/// Gets ValidFrom column (sql formatted)
/// </summary>
/// <param name="scdColumns">SCD column list</param>
/// <param name="spaces">Number of space at the beginnung of a row</param>
/// <returns>ValidFrom column</returns>
public static string GetSqlValidFromWithDataType(Dictionary <string, SCDConfiguration> scdList, int spaces)
{
string newLine = spaces > 0 ? Environment.NewLine : "";
string result = "";
foreach (SCDConfiguration scd in scdList.Values)
{
string columnName = SCDHelper.AddBrackets(scd.TableName + "_" + scd.ValidFrom.ColumnName);
result += (",").PadLeft(spaces + 1) + columnName + " " + scd.ValidFrom.DataType + newLine;
}
return(result);
}
/// <summary>
/// Gets ValidFrom column (sql formatted)
/// </summary>
/// <param name="scdColumns">SCD column list</param>
/// <param name="addTableNamePrefix">Add table name prefix?</param>
/// <param name="prefix">Column prefix</param>
/// <param name="spaces">Number of space at the beginnung of a row</param>
/// <returns>ValidFrom column</returns>
public static string GetSqlValidFrom(Dictionary <string, SCDConfiguration> scdList, bool addTableNamePrefix, string prefix, int spaces)
{
string newLine = spaces > 0 ? Environment.NewLine : "";
string result = "";
foreach (SCDConfiguration scd in scdList.Values)
{
string columnName = addTableNamePrefix ? scd.TableName + "_" + scd.ValidFrom.ColumnName : scd.ValidFrom.ColumnName;
columnName = prefix + SCDHelper.AddBrackets(columnName);
result += (",").PadLeft(spaces + 1) + columnName + newLine;
}
return(result);
}
/// <summary>
/// Get sql coomand for creating SCD temporary table
/// </summary>
/// <param name="tempTableName">SCD temporary table name</param>
/// <param name="enableIndexOnSCD">Disable SCD temporary table nonclusterd index?</param>
/// <returns>Sql coomand for creating SCD temporary table</returns>
private string GetCreateTempTable(string tempTableName, bool enableIndexOnSCD)
{
string result = TEMPLATE_CREATE_TEMP_TABLE.Replace("<tempTableName_scd>", tempTableName);
string indexBk = TEMPLATE_CREATE_CLUSTERD_INDEX_ON_TEMP_TABLE.Replace("<tempTableName_scd>", tempTableName);
string indexAttributes = "";
ScdColumnList scdListBKs = new ScdColumnList();
ScdColumnList scdListAttributes = new ScdColumnList();
foreach (string scdTableName in _scdList.Keys)
{
scdListBKs.AddList(_scdList[scdTableName].BkList);
scdListAttributes.AddList(_scdList[scdTableName].AttributeList);
}
result = result.Replace("<bk>", SCDHelper.GetSqlBkListWithDataType(scdListBKs.ScdColumns, _prefixFK, "", 2));
result = result.Replace("<attributes>", SCDHelper.GetSqlAttributeListWithDataType(scdListAttributes.ScdColumns, SCDConfiguration.POSTFIX_COLUMN_QUELLE, SCDConfiguration.POSTFIX_COLUMN_DWH_VOR_UPDATE, 2));
result = result.Replace("<valid_from>", SCDHelper.GetSqlValidFromWithDataType(_scdList, 2));
result = result.Replace("<FK_ID>", _prefixFK + "ID");
indexBk = indexBk.Replace("<index_columns>", " " +
SCDHelper.GetSqlBkList(scdListBKs.ScdColumns, _prefixFK, "", 2).Substring(3)); //Substring(3): Komma an 3. Stelle entfernen
if (enableIndexOnSCD)
{
foreach (SCDColumn scdColumn in scdListAttributes.ScdColumns)
{
string index = TEMPLATE_CREATE_NONCLUSTERD_INDEX_ON_TEMP_TABLE
.Replace("<tempTableName_scd>", tempTableName) //.TrimStart("[".ToCharArray()).TrimEnd("]".ToCharArray()))
.Replace("<index_name>", scdColumn.ColumnName);
List <SCDColumn> scdColumns = new List <SCDColumn>();
scdColumns.Add(scdColumn);
string columns = SCDHelper.GetSqlAttributeListDoubled(scdColumns, "", "", SCDConfiguration.POSTFIX_COLUMN_QUELLE, SCDConfiguration.POSTFIX_COLUMN_DWH_VOR_UPDATE, 2);
columns = " " + columns.Substring(3); // Komma an 3. Stelle entfernen
index = index.Replace("<index_columns>", columns);
indexAttributes += index + Environment.NewLine + Environment.NewLine;
}
}
return(result + Environment.NewLine + Environment.NewLine + indexBk + Environment.NewLine + Environment.NewLine + indexAttributes);
}
/// <summary>
/// Gets list of SCD columns with datatypes (sql formatted)
/// </summary>
/// <param name="scdColumns">SCD column list</param>
/// <param name="postfix1">postfix first column</param>
/// <param name="postfix2">postfix second column</param>
/// <param name="spaces">Number of space at the beginnung of a row</param>
/// <param name="generate2ndColumn">Generate 2nd column?</param>
/// <param name="afterDataType">Text that is inserted after datatype (i.e. NULL)</param>
/// <param name="prefixColumnName">Prefix for columnname</param>
/// <returns>list of SCD columns with datatypes (sql formatted)</returns>
public static string GetSqlColumnListWithDataType(List <SCDColumn> scdColumnList, string postfix1, string postfix2, int spaces, bool generate2ndColumn, string afterDataType, string prefixColumnName)
{
string result = "";
string newLine = spaces > 0 ? Environment.NewLine : "";
string prefix1 = "";
string prefix2 = "";
SCDHelper.AddBrackets(ref prefix1, ref postfix1);
SCDHelper.AddBrackets(ref prefix2, ref postfix2);
foreach (SCDColumn column in scdColumnList)
{
result += (",").PadLeft(spaces + 1) + prefix1 + prefixColumnName + column.ColumnName + postfix1 + " " + column.DataType + " " + afterDataType + newLine;
if (generate2ndColumn)
{
result += (",").PadLeft(spaces + 1) + prefix2 + prefixColumnName + column.ColumnName + postfix2 + " " + column.DataType + " " + afterDataType + newLine;
}
}
return(result);
}
/// <summary>
/// Get insert part of SCD merge command
/// </summary>
/// <param name="tempTableScd">SCD temporary table</param>
/// <returns>Insert part of SCD sql command</returns>
private string GetInsertPart(string tempTableScd)
{
string result = SCDConfiguration.TEMPLATE_INSERT;
result = result.Replace("<GranularityMaxValue>", GranularityMaxValue);
result = result.Replace("<scd_tablename>", TableName);
result = result.Replace("<tempTableName_scd>", tempTableScd);
result = result.Replace("<attributes_insert>", SCDHelper.GetSqlAttributeList(AttributeList, "", POSTFIX_COLUMN_QUELLE, 0));
result = result.Replace("<attributes_scd_insert>", SCDHelper.GetSqlAttributeList(AttributeList, "", "", 2));
result = result.Replace("<attributes_scd_mergeinsert>", SCDHelper.GetSqlAttributeList(AttributeList, "", "", 0));
result = result.Replace("<attributes_values>", SCDHelper.GetSqlAttributeList(AttributeList, "MR.", POSTFIX_COLUMN_QUELLE, 2));
result = result.Replace("<valid_from_values>", SCDHelper.GetSqlValidFrom(TableName, ValidFrom, "MR.", "", 0));
result = result.Replace("<attributes_wehre>", SCDHelper.GetSqlAttributeWhere(AttributeList, "MR."));
result = result.Replace("<attributes_output>", SCDHelper.GetSqlAttributeList(AttributeList, "MR.", POSTFIX_COLUMN_QUELLE, 2));
result = result.Replace("<valid_from_output>", SCDHelper.GetSqlValidFrom(TableName, ValidFrom, "MR.", "", 0));
result = result.Replace("<bk_scd>", SCDHelper.GetSqlBkList(BkList, PrefixFK, "", 2));
result = result.Replace("<bk_select_from_merge>", SCDHelper.GetSqlBkList(BkList, PrefixFK, "", 0));
result = result.Replace("<bk_values>", SCDHelper.GetSqlBkList(BkList, PrefixFK, "MR.", 2));
result = result.Replace("<merge_scd_bk_on>", SCDHelper.GetBkOnList(BkList, PrefixFK));
result = result.Replace("<FK_ID>", PrefixFK + "ID");
return(result);
}
/// <summary>
/// Creates sql command for creating the SCD table
/// </summary>
/// <returns>sql command for creating the SCD </returns>
public string GetCreateScdTables()
{
string result = "";
foreach (string tableName in _scdList.Keys)
{
SCDConfiguration scd = _scdList[tableName];
string bks = SCDHelper.GetSqlBkListWithDataType(scd.BkList, _prefixFK, "", 2);
string attributes = SCDHelper.GetSqlColumnListWithDataType(scd.AttributeList, "", "", 2, false, "NULL", "");
string create = TEMPLATE_CREATE_SCD_TABLE.Replace("<tableName>", tableName);
create = create.Replace("<GranularityMaxValue>", scd.GranularityMaxValue);
create = create.Replace("<bks>", bks);
create = create.Replace("<attributes>", attributes);
create = create.Replace("<FK_ID>", _prefixFK + "ID");
create += Environment.NewLine + Environment.NewLine;
result += create;
}
return(result);
}