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); }