private void CreateClusteredIndex(string viewName, string primaryKeyColumnName)
{
return; // This won't work because most of our views contain left joins.
string statement = $"CREATE UNIQUE CLUSTERED INDEX CX_{viewName} ON {ToQualified(_viewSchema, viewName)} ([{primaryKeyColumnName}]);";
SqlUtil.ExecuteCommand(_connectionString, statement);
}
private void DropExistingViews(string schema)
{
using (var context = new SchemaDbContext(_connectionString))
{
foreach (var view in context.Views.Where(_ => _.TableSchema == schema).ToList())
{
string sql = "DROP VIEW [" + view.TableSchema + "].[" + view.TableName + "];";
SqlUtil.ExecuteCommand(_connectionString, sql);
}
}
}
private void CreateSchema(string schema)
{
using (var context = new SchemaDbContext(_connectionString))
{
if (context.Schemata.SingleOrDefault(_ => _.SchemaName == schema) == null)
{
string sql = $"CREATE SCHEMA [{schema}] AUTHORIZATION [{_dataMartSchemaOwner}];";
SqlUtil.ExecuteCommand(_connectionString, sql);
}
}
}
public void CreateView(string tableName, ViewDefinition viewDefinition)
{
var columnClauses = new List <string>();
var joinClauses = new List <string>();
foreach (var columnGroup in viewDefinition.ColumnGroups)
{
if (columnGroup is ViewDefinition.PrimaryKey)
{
columnClauses.Add(GetSurrogateKeyDefinition(_schema, tableName, columnGroup.Columns) + " AS [" + tableName + "Key]");
}
else
if (columnGroup is ViewDefinition.ReferenceKey)
{
var referenceKey = columnGroup as ViewDefinition.ReferenceKey;
var relationship = referenceKey.Relationship;
if (referenceKey.KeyType == ViewDefinition.ReferenceKeyType.LookupReference)
{
var relationshipColumn = relationship.RelationshipColumns[0];
string lookupKey = ApplyLookupColumnQualifier(relationshipColumn.Column.ColumnName) + "Key";
columnClauses.Add(ToQualified(null, relationship.PrimaryKeyCorrelationName, relationshipColumn.PrimaryKeyColumn.ColumnName) + " AS [" + lookupKey + "]");
}
else if (referenceKey.KeyType == ViewDefinition.ReferenceKeyType.Denormalized)
{
columnClauses.AddRange(relationship.RelationshipColumns.Select(c => ToQualified(null, relationship.PrimaryKeyCorrelationName, c.Column.ColumnName) + " AS [" + c.Column.ColumnName + "]"));
}
else
{
columnClauses.Add(GetSurrogateKeyDefinition(_schema, tableName, relationship.RelationshipColumns.Select(rc => rc.Column)) + " AS [" + relationship.PrimaryKeyCorrelationName + "Key]");
}
joinClauses.Add(GetRelationshipJoin(tableName, referenceKey.Relationship));
}
else
{
columnClauses.AddRange(columnGroup.Columns.Select(_ => ToQualified(_schema, tableName, _.ColumnName)));
}
}
string sql = "SELECT"
+ string.Join(", ", columnClauses.Select(_ => $"\n\t{_}"))
+ $"\nFROM [{_schema}].[{tableName}]\n"
+ string.Join("\n", joinClauses);
var viewSchema = viewDefinition.Type == ViewType.Lookup ? _lookupSchema : _viewSchema;
var viewPrefix = viewDefinition.Type == ViewType.Lookup ? "Lkp" : (viewDefinition.Type == ViewType.Dimension ? "Dim" : "Fact");
string statement = $"CREATE VIEW {ToQualified(viewSchema, viewPrefix + tableName)} WITH SCHEMABINDING AS\n{sql};";
SqlUtil.ExecuteCommand(_connectionString, statement);
}
private void CreateView(string schema, string viewName, string sql)
{
string statement = $"CREATE VIEW {ToQualified(schema, viewName)} WITH SCHEMABINDING AS\n{sql};";
SqlUtil.ExecuteCommand(_connectionString, statement);
}
