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());
}
public static void CreateObjectsFromMetadata(SqlConnection conn)
{
//try
//{
Console.WriteLine();
Console.WriteLine("Creating objects for {0}", DatabaseName);
// get a reference to the database so we can generate tables
string sql = string.Format(@"
select
B.DatabaseInfoId,
B.DatabaseName,
B.DatabaseDescription,
B.ServerName
from Metadata.DatabaseInfo B
join metadata.DatabaseUse A on
A.DatabaseUseId = B.DatabaseUseId
where A.DatabaseUse = 'TARGET' and B.DatabaseName = '{0}'
", DatabaseName);
List <DatabaseInfo> databases = conn.Query <DatabaseInfo>(sql).ToList();
DatabaseInfo database = databases.First(); // we only have one database to generate for
string[] staticDims = { "" };
DirectoryInfo dirPostDeployFolder = Utilities.GetAndCleanOutputDir(DatabaseName, @"Scripts\Post-Deploy");
DirectoryInfo dirLoadProcs = Utilities.GetAndCleanOutputDir(DatabaseName, LoadingSchema, "Stored Procedures");
DirectoryInfo dirLookupProcs = Utilities.GetAndCleanOutputDir(DatabaseName, LookupSchema, "Stored Procedures");
DirectoryInfo dirLoadViewStubs = Utilities.GetAndCleanOutputDir(DatabaseName, LoadingSchema, "Views");
DirectoryInfo dirTables = Utilities.GetAndCleanOutputDir(DatabaseName, TableSchema, "Tables");
DirectoryInfo dirDataMartViews = Utilities.GetAndCleanOutputDir(DatabaseName, DataMartSchema, "Views");
DirectoryInfo dirCubeViews = Utilities.GetAndCleanOutputDir(DatabaseName, CubeSchema, "Views");
DirectoryInfo dirExtractViews = Utilities.GetAndCleanOutputDir(DatabaseName, ExtractSchema, "Views");
string checkInsertViewsSP = string.Empty;
string insertUnknowns = string.Empty;
DataModel dm = PopulateFromMetadataDatabase.PopulateDataModelFromMetadataDatabase(database, conn);
foreach (DatabaseObject table in dm.Objects.Where(x => x.SchemaName.ToUpper() == TableSchema.ToUpper() && x.DatabaseObjectType.Trim().ToUpper() == "U"))
{
// create an artificial sort order where the dimXXXid columns are at the beginning
foreach (DatabaseColumn column in table.Columns.Where(x => x.DatabaseColumnName.ToLower().StartsWith("dim")))
{
column.DatabaseColumnSortOrder = "0" + column.DatabaseColumnName;
}
string[] firstColumns = { "XXX" };
foreach (DatabaseColumn column in table.Columns.Where(x => firstColumns.Contains(x.DatabaseColumnName)))
{
column.DatabaseColumnSortOrder = "1" + column.DatabaseColumnName;
}
if (!staticDims.Contains(table.DatabaseObjectName) && (
(table.DatabaseObjectName.StartsWith("Dim") || table.DatabaseObjectName.StartsWith("Security") ||
table.DatabaseObjectName.StartsWith("Active") || table.DatabaseObjectName.StartsWith("Masked"))
))
{
// if we have a primary key in the table, then use standard merge template, otherwise use truncate and load
int iCount = table.Columns.Where(column => column.IsPrimaryKey == true).Count();
if (iCount > 0)
{
TemplateCommon.StandardMergeSP(table, dirLoadProcs, LoadingSchema, null);
}
else
{
TemplateCommon.StandardInsertSP(table, dirLoadProcs, LoadingSchema, null);
}
}
if (table.DatabaseObjectName.StartsWith("Fact"))
{
// if we have a primary key in the fact table, then use simple merge template, otherwise use truncate and load
int iCount = table.Columns.Where(column => column.IsPrimaryKey == true).Count();
if (iCount > 0)
{
TemplateCommon.StandardMergeSP(table, dirLoadProcs, LoadingSchema, null);
}
else
{
TemplateCommon.StandardInsertSP(table, dirLoadProcs, LoadingSchema, null);
}
}
if (table.DatabaseObjectName.StartsWith("Ref") || table.DatabaseObjectName.StartsWith("Bridge"))
{
TemplateCommon.StandardInsertSP(table, dirLoadProcs, LoadingSchema, null);
}
{
IEnumerable <DatabaseColumn> loadViewFilteredColumns = table.Columns;
//.Where(column => (column.DatabaseColumnName.ToLower() != DatabaseObject.updatedloadlogid) &&
// column.DatabaseColumnName.ToLower().EndsWith("key") && !column.IsIdentity);
// Create stub of the load view
CreateLoadViewStub(table, dirLoadViewStubs, LoadingSchema, table.GetColumnListSql(loadViewFilteredColumns), StagingAreaSchema);
}
{
string[] excludeColumns = { "XXX" };
IEnumerable <DatabaseColumn> dataMartViewFilteredColumns = table.Columns.Where(column => (!DatabaseObject.allStandardColumns.Contains(column.DatabaseColumnName.ToLower()) &&
!excludeColumns.Contains(column.DatabaseColumnName.ToLower()) &&
!(column.DatabaseColumnName.ToLower().StartsWith("fact") && column.DatabaseColumnName.ToLower().EndsWith("key"))));
// DataMart views
CreateDataMartView(table, dirDataMartViews, DataMartSchema, table.GetViewColumnListSql(dataMartViewFilteredColumns));
// cube views
CreateDataMartView(table, dirCubeViews, CubeSchema, table.GetColumnListSql(dataMartViewFilteredColumns));
}
string[] excludeDimensions = { "" };
if (!excludeDimensions.Contains(table.DatabaseObjectName.ToLower()))
{
IEnumerable <DatabaseColumn> checkInsertViewFilteredColumns = table.Columns.Where(column => (!DatabaseObject.allStandardColumns.Contains(column.DatabaseColumnName.ToLower()) &&
!column.IsIdentity));
checkInsertViewsSP += TemplateComponents.CheckInsertView(LoadingSchema, table.DatabaseObjectName, table.GetColumnListSql(checkInsertViewFilteredColumns));
// include dimensions
if (table.DatabaseObjectName.ToLower().StartsWith("dim"))
{
insertUnknowns += InsertUnknowns(LoadingSchema, table);
}
}
}
CheckInsertViews(dirLoadProcs, checkInsertViewsSP);
InsertUnknownsScript(dirPostDeployFolder, insertUnknowns);
foreach (DatabaseObject table in dm.Objects.Where(x => x.SchemaName.ToUpper() == LookupSchema.ToUpper() && x.DatabaseObjectType.Trim().ToUpper() == "U"))
{
if (table.DatabaseObjectName.StartsWith("Bridge"))
{
TemplateCommon.StandardInsertSP(table, dirLookupProcs, LookupSchema, "lookup");
}
else
{
TemplateCommon.StandardMergeSP(table, dirLookupProcs, LookupSchema, "lookup");
}
}
Console.WriteLine("Done all " + DatabaseName);
//}
//catch (Exception ex)
//{
// Console.WriteLine("Error in EssentialsAccountsDB.CreateObjectsFromMetadata {0}", ex.Message);
//}
}
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());
}