private static void CreateStgTableScript(DatabaseInfo database, DatabaseObject table, DirectoryInfo dirTables)
{
StringBuilder sb = new StringBuilder("create table [");
sb.Append(table.StagingAreaSchema);
sb.Append("].[");
sb.Append(table.DatabaseObjectName);
sb.AppendLine("]");
sb.AppendLine("(");
sb.AppendLine(TemplateComponents.AutogenWarning());
// add LoadLogId and DatabaseInfoId columns
sb.AppendLine("\t[LoadLogId] bigint not null,");
//sb.AppendLine("\t[DatabaseInfoId] int not null,");
//sb.AppendLine("\t[DatabaseGroupId] int not null,");
foreach (DatabaseColumn column in table.Columns.Where(x => x.UseColumn))
{
sb.Append("\t[");
sb.Append(column.DatabaseColumnName);
sb.Append("] ");
sb.Append(column.DataType);
sb.Append(column.GetDataTypeModifiers(false, false));
sb.AppendLine(",");
}
sb.AppendLine(") on [PRIMARY] with (data_compression = page);");
string sqlPath = Path.Combine(dirTables.FullName, table.DatabaseObjectName + ".sql");
//Console.WriteLine("Writing Table {0}", sqlPath);
Console.Write(".");
System.IO.File.WriteAllText(sqlPath, sb.ToString());
}
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());
}