public string AddAlias(StarModelTableBase table)
{
if (_starTableAliases.ContainsKey(table))
{
return(_starTableAliases[table]);
}
int offset = 1;
string alias;
bool collision;
string name = table.Name.Substring(0, 2);
do
{
alias = "T_" + name + offset;
collision = _knownAliases.Contains(alias);
if (collision)
{
offset++;
}
} while (collision);
_knownAliases.Add(alias);
_starTableAliases.Add(table, alias);
return(alias);
}
public static string GetAliasName(this StarColumn column, StarModelTableBase dimension = null)
{
if (dimension == null)
{
return($"[{column.TableRef.GetAlias()}_{TrimName(column.Name)}]");
}
return($"[{dimension.GetAlias()}_{TrimName(column.Name)}]");
}
public string GetAlias(StarModelTableBase table)
{
if (_starTableAliases.ContainsKey(table))
{
return(_starTableAliases[table]);
}
return(AddAlias(table));
}
public static string GetFullName(this StarModelTableBase table, bool escaped = true)
{
if (escaped)
{
return($"[dbo].[{table.Name}]");
}
return($"dbo.{table.Name}");
}
private void RemoveForeignKeyColumns(StarModelTableBase table)
{
// Iterate through all relations where this table contain the foreign key(s)
foreach (var linkRelation in table.TableReference.Relations.Where(r => Equals(r.LinkTable, table.TableReference)))
{
// Remove each foreign key from table
foreach (var linkColumn in linkRelation.LinkColumns)
{
table.Columns.RemoveAll(c => Equals(c.ColumnRef, linkColumn));
}
}
}
public static void RenameDuplicateColumns(this StarModelTableBase table)
{
var duplicateGroups = table.Columns.GroupBy(sc => sc.Name)
.Where(group => group.Count() > 1);
foreach (var groups in duplicateGroups)
{
foreach (var starColumn in groups)
{
starColumn.Name = $"{starColumn.ColumnRef.Table.Name}_{starColumn.Name}";
}
}
}
private static void AddSurrogateKey(StarModelTableBase dimension)
{
// Increment ordinal of all columns
dimension.Columns.ForEach(c => c.Ordinal++);
var surrogateKey = new StarColumn(1, "SurKey", new DataType(OleDbType.Integer), StarColumnType.Key | StarColumnType.SurrogateKey);
surrogateKey.TableRef = dimension;
// Insert surrogatekey column
dimension.Columns.Insert(0, surrogateKey);
// Add new surrogatekey to all anchor relations that lacks an anchorcolumn
foreach (var relation in dimension.Relations.Where(r => Equals(r.AnchorTable, dimension) && !r.HasAnchorColumns()))
{
relation.AnchorColumns.Add(surrogateKey);
}
dimension.Constraints.PrimaryKey.Columns.Add(surrogateKey);
dimension.Constraints.NotNullables.Add(new StarModelComponents.NotNullable(surrogateKey));
}
private static void CreatePrimaryKey(Dictionary <StarColumn, Column> columnObjects, StarModelTableBase table, Table tbl)
{
// Only use the first primary key
//var starColumns = table.Constraints.PrimaryKey.Columns;
var keyStarColumns = table.Columns.Where(c => c.ColumnType.HasFlag(StarColumnType.Key)).ToList();
if (keyStarColumns != null && keyStarColumns.Any())
{
var keyColumns = columnObjects.Where(pair => keyStarColumns.Contains(pair.Key)).Select(pair => pair.Value);
// Create primary key index for the key columns
Index index = new Index(tbl, $"PK_{tbl.Name}");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
foreach (var keyColumn in keyColumns)
{
keyColumn.Nullable = false;
index.IndexedColumns.Add(new IndexedColumn(index, keyColumn.Name));
}
// Add index to table
tbl.Indexes.Add(index);
}
}
public Dimension(StarModelTableBase basedOn, bool isRolePlaying = true) : base(basedOn, isRolePlaying)
{
}
protected StarNode(StarModelTableBase table)
{
Table = table;
}
/// <summary>
/// Generate an INNER JOIN statement between a linkTable and a list of dimensions
/// </summary>
/// <param name="linkTable">Table linking dimensions</param>
/// <param name="dimensions">Dimensions to use in JOIN statement</param>
/// <returns>INNER JOIN between linkTable and dimensions</returns>
private string GenerateJoin(StarModelTableBase linkTable, List <Dimension> dimensions)
{
// The table already containing values
Table tableWithValues = linkTable.TableReference;
// All the relations, that the table defines a link in
List <Relation> relations = tableWithValues.Relations.Where(r => r.LinkTable == tableWithValues).ToList();
List <CrossReference> crossModelReferences = new List <CrossReference>();
// Add crossreference for each time or date dimension
foreach (var starRelation in linkTable.Relations)
{
if (starRelation.AnchorTable is TimeDimension || starRelation.AnchorTable is DateDimension)
{
crossModelReferences.Add(new CrossReference(tableWithValues, starRelation.LinkColumns.Select(sc => sc.ColumnRef).ToList(), (Dimension)starRelation.AnchorTable, starRelation.AnchorColumns));
}
if (starRelation.AnchorTable is JunkDimension)
{
crossModelReferences.Add(new CrossReference(
tableWithValues,
starRelation.AnchorTable.Columns.Where(c => !c.ColumnType.HasFlag(StarColumnType.Key)).Select(sc => sc.ColumnRef).ToList(),
(JunkDimension)starRelation.AnchorTable,
starRelation.AnchorTable.Columns.Where(c => !c.ColumnType.HasFlag(StarColumnType.Key)).ToList())
);
}
}
// Iterate through relations and find crossreferences
foreach (var relation in relations)
{
var anchorDimension = dimensions.FirstOrDefault(d => Equals(d.TableReference, relation.AnchorTable));
crossModelReferences.Add(new CrossReference(tableWithValues, relation.LinkColumns, anchorDimension, anchorDimension.Columns.Where(c => relation.AnchorColumns.Contains(c.ColumnRef)).ToList()));
}
List <string> joins = new List <string>();
// Build an INNER JOIN for each crossreference
foreach (var r in crossModelReferences)
{
string join = $" INNER JOIN {r.AnchorTbl.GetNameAsAlias()} ON ";
// Create a list of all join clauses in this relation
List <string> clauses = new List <string>();
for (var i = 0; i < r.LinkCols.Count; i++)
{
// Check if anchor column can contain null values
bool cannotContainNulls = r.AnchorTbl.Constraints.NotNullables.Any(c => c.Column == r.AnchorCols[i]);
// If temporal, then define special join clause
if (r.LinkCols[i].DataType.IsTemporal())
{
if (r.AnchorTbl is DateDimension)
{
clauses.Add(GenerateDateDimensionJoin(r.LinkCols.First(), (DateDimension)r.AnchorTbl));
}
else if (r.AnchorTbl is TimeDimension)
{
clauses.Add(GenerateTimeDimensionJoin(r.LinkCols.First(), (TimeDimension)r.AnchorTbl));
}
}
else
{
if (cannotContainNulls)
{
clauses.Add($"{r.LinkCols[i].GetNameWithAlias()} = {r.AnchorCols[i].GetNameWithAlias()}");
}
else
{
clauses.Add(
$"({r.LinkCols[i].GetNameWithAlias()} = {r.AnchorCols[i].GetNameWithAlias()} OR {r.LinkCols[i].GetNameWithAlias()} IS NULL AND {r.AnchorCols[i].GetNameWithAlias()} IS NULL)");
}
}
}
join += string.Join(" AND ", clauses);
joins.Add(join);
}
return(string.Join("\r\n", joins));
}
public static List <ColumnOrigin> GetOriginalTables(this StarModelTableBase dimension)
{
var columnOrigins = dimension.Columns.Where(c => !c.ColumnType.HasFlag(StarColumnType.SurrogateKey) && c.ColumnRef != null).GroupBy(c => c.ColumnRef.Table).Select(columns => new ColumnOrigin(columns.Key, new List <StarColumn>(columns))).ToList();
return(columnOrigins);
}
public static string GetAlias(this StarModelTableBase table)
{
return(AliasService.Instance.GetAlias(table));
}
public static string GetNameAsAlias(this StarModelTableBase table)
{
return($"{table.GetFullName()} AS [{AliasService.Instance.GetAlias(table)}]");
}
