public void IndexedColumn_Churn() { IColumn <ByteBlock> c = new IndexedColumn(ColumnFactory.CreateSortedColumn <ByteBlock>(new ByteBlockColumn(ByteBlock.Zero), 0), new DefaultWordSplitter()); c.SetSize(4); c[0] = "First Value"; c[1] = "Second Value"; c[2] = "this value is yet another one"; ColumnTests.AssertConsistent(c); // Remove the last word added and ensure no problems (unique case, no swap and remove) Assert.AreEqual("2", ColumnTests.GetMatches(c, Operator.MatchesExact, "one")); c[2] = "this value is yet another"; ColumnTests.AssertConsistent(c); Assert.AreEqual("", ColumnTests.GetMatches(c, Operator.MatchesExact, "one")); Assert.AreEqual("0, 1, 2", ColumnTests.GetMatches(c, Operator.MatchesExact, "value")); // Reset a value to itself (reusing the read ByteBlock); ensure no problems c[1] = c[1]; ColumnTests.AssertConsistent(c); Assert.AreEqual("0, 1, 2", ColumnTests.GetMatches(c, Operator.MatchesExact, "value")); // Set a value to null; ensure no problems c[0] = (ByteBlock)(string)null; ColumnTests.AssertConsistent(c); Assert.AreEqual("", ColumnTests.GetMatches(c, Operator.MatchesExact, "first")); Assert.AreEqual("1, 2", ColumnTests.GetMatches(c, Operator.MatchesExact, "value")); }
public void CreateTableProduct() { Table product = new Table(_database, "Product"); Index index = new Index(product, "barecode_Index"); index.IsClustered = true; IndexedColumn columnIndex = new IndexedColumn(index, "barecode", true); index.IndexedColumns.Add(columnIndex); Column column = new Column(product, "nameProduct", DataType.VarChar(50)); product.Columns.Add(column); column = new Column(product, "priceProduct", DataType.Float); product.Columns.Add(column); column = new Column(product, "descriptionProduct", DataType.VarCharMax); product.Columns.Add(column); column = new Column(product, "imageProduct", DataType.VarBinaryMax); product.Columns.Add(column); column = new Column(product, "qteEnStockProduct", DataType.Int); product.Columns.Add(column); product.Create(); }
private static void createIndexes(Table sourcetable, Table copiedtable) { foreach (Index srcind in sourcetable.Indexes) { if (!srcind.IsDisabled && (srcind.IsClustered || (!srcind.IsClustered && !srcind.IsXmlIndex))) { var name = copiedtable.Name + "_" + srcind.Name; var index = new Index(copiedtable, name); index.IndexKeyType = srcind.IndexKeyType; index.IsClustered = srcind.IsClustered; index.IsUnique = srcind.IsUnique; index.CompactLargeObjects = srcind.CompactLargeObjects; index.IgnoreDuplicateKeys = srcind.IgnoreDuplicateKeys; index.IsFullTextKey = srcind.IsFullTextKey; index.PadIndex = srcind.PadIndex; index.FileGroup = srcind.FileGroup; foreach (IndexedColumn srccol in srcind.IndexedColumns) { var column = new IndexedColumn(index, srccol.Name, srccol.Descending); column.IsIncluded = srccol.IsIncluded; index.IndexedColumns.Add(column); } index.Create(); } } }
public IndexColumn([NotNull] IndexedColumn indexedColumn) { Assert.ArgumentNotNull(indexedColumn, nameof(indexedColumn)); IsIncluded = indexedColumn.IsIncluded; IsComputed = indexedColumn.IsComputed; Descending = indexedColumn.Descending; }
private Index CreateIndexCopy(Table targetTable, Index index) { var newIndex = new Index(targetTable, Guid.NewGuid().ToString("N")) { BoundingBoxXMax = index.BoundingBoxXMax, BoundingBoxXMin = index.BoundingBoxXMin, BoundingBoxYMax = index.BoundingBoxYMax, BoundingBoxYMin = index.BoundingBoxYMin, BucketCount = index.BucketCount, CellsPerObject = index.CellsPerObject, CompactLargeObjects = index.CompactLargeObjects, DisallowPageLocks = index.DisallowPageLocks, DisallowRowLocks = index.DisallowRowLocks, FileGroup = index.FileGroup, FileStreamFileGroup = index.FileStreamFileGroup, FileStreamPartitionScheme = index.FileStreamPartitionScheme, FillFactor = index.FillFactor, FilterDefinition = index.FilterDefinition, IgnoreDuplicateKeys = index.IgnoreDuplicateKeys, IndexKeyType = index.IndexKeyType, IndexType = index.IndexType, IndexedXmlPathName = index.IndexedXmlPathName, IsClustered = index.IsClustered, IsFullTextKey = index.IsFullTextKey, IsMemoryOptimized = index.IsMemoryOptimized, IsUnique = index.IsUnique, Level1Grid = index.Level1Grid, Level2Grid = index.Level2Grid, Level3Grid = index.Level3Grid, Level4Grid = index.Level4Grid, LowPriorityAbortAfterWait = index.LowPriorityAbortAfterWait, LowPriorityMaxDuration = index.LowPriorityMaxDuration, MaximumDegreeOfParallelism = index.MaximumDegreeOfParallelism, NoAutomaticRecomputation = index.NoAutomaticRecomputation, OnlineIndexOperation = index.OnlineIndexOperation, PadIndex = index.PadIndex, ParentXmlIndex = index.ParentXmlIndex, PartitionScheme = index.PartitionScheme, SecondaryXmlIndexType = index.SecondaryXmlIndexType, SortInTempdb = index.SortInTempdb, SpatialIndexType = index.SpatialIndexType }; newIndex.ExtendedProperties.Add(new ExtendedProperty(newIndex, "CopyOf", index.Name)); foreach (IndexedColumn column in index.IndexedColumns) { var newColumn = new IndexedColumn(newIndex, column.Name, column.Descending) { IsIncluded = column.IsIncluded }; newIndex.IndexedColumns.Add(newColumn); } return(newIndex); }
public static void AddColumnToIndex(Table table, Column column, Index ind) { string oldName = ind.Name; var idx = new Index(table, oldName) { BoundingBoxYMax = ind.BoundingBoxYMax, BoundingBoxYMin = ind.BoundingBoxYMin, BoundingBoxXMax = ind.BoundingBoxXMax, BoundingBoxXMin = ind.BoundingBoxXMin, CompactLargeObjects = ind.CompactLargeObjects, DisallowRowLocks = ind.DisallowRowLocks, FileGroup = ind.FileGroup, FileStreamFileGroup = ind.FileStreamFileGroup, FileStreamPartitionScheme = ind.FileStreamPartitionScheme, FillFactor = ind.FillFactor, FilterDefinition = ind.FilterDefinition, IgnoreDuplicateKeys = ind.IgnoreDuplicateKeys, IndexKeyType = ind.IndexKeyType, IsClustered = ind.IsClustered, IsFullTextKey = ind.IsFullTextKey, IsUnique = ind.IsUnique, Level1Grid = ind.Level1Grid, Level2Grid = ind.Level2Grid, Level3Grid = ind.Level3Grid, Level4Grid = ind.Level4Grid, MaximumDegreeOfParallelism = ind.MaximumDegreeOfParallelism, NoAutomaticRecomputation = ind.NoAutomaticRecomputation, OnlineIndexOperation = ind.OnlineIndexOperation, PadIndex = ind.PadIndex, ParentXmlIndex = ind.ParentXmlIndex, PartitionScheme = ind.PartitionScheme, SecondaryXmlIndexType = ind.SecondaryXmlIndexType, SortInTempdb = ind.SortInTempdb, SpatialIndexType = ind.SpatialIndexType, }; foreach (IndexedColumn iColumn in ind.IndexedColumns) { var newIdxColumn = new IndexedColumn(idx, iColumn.Name) { Descending = iColumn.Descending, IsIncluded = iColumn.IsIncluded, }; idx.IndexedColumns.Add(newIdxColumn); } idx.IndexedColumns.Add(new IndexedColumn(idx, column.Name)); bool oldDisallowPageLocks = ind.DisallowPageLocks; ind.Drop(); idx.Create(); idx.DisallowPageLocks = oldDisallowPageLocks; idx.Alter(); }
private string FormatColumn(IndexedColumn column) { string s = column.Name; if (column.Order == Order.Descending) { s += " " + Constants.Descending; } return(s); }
public static void AddClusterIndex(this Table tb, string columnName) { Index idx = new Index(tb, string.Format("IDX_{0}_{1}", tb.Name, columnName)); IndexedColumn indexedColumn = new IndexedColumn(idx, columnName); idx.IndexedColumns.Add(indexedColumn); idx.IndexKeyType = IndexKeyType.None; idx.IsClustered = true; idx.Create(); }
public static void AddPrimaryKey(this Table tb, string columnName) { //create primary key index Index pk = new Index(tb, string.Format("PK_{0}_{1}", tb.Name, columnName)); IndexedColumn indexedColumn = new IndexedColumn(pk, columnName); pk.IndexedColumns.Add(indexedColumn); pk.IndexKeyType = IndexKeyType.DriPrimaryKey; pk.IsClustered = false; pk.Create(); }
/// <summary> /// Create index for split table /// </summary> /// <param name="productionTable">Split production table</param> /// <param name="surveyId">Survey ID</param> /// <param name="databaseEngine">Pointer of database class</param> /// <returns></returns> public override MySmoObjectBase CreateSplitItem( Table productionTable, int surveyId, Database databaseEngine) { var newIndex = new Index(); var modelIndex = (Index)SourceSmoObject; newIndex.Parent = productionTable; if (surveyId == 0) { newIndex.Name = modelIndex.Name; } else { newIndex.Name = databaseEngine.GetTemplateNameByOriginalName( modelIndex.Name, surveyId); } newIndex.IgnoreDuplicateKeys = modelIndex.IgnoreDuplicateKeys; newIndex.IndexKeyType = modelIndex.IndexKeyType; newIndex.IsClustered = modelIndex.IsClustered; newIndex.IsUnique = modelIndex.IsUnique; newIndex.NoAutomaticRecomputation = modelIndex.NoAutomaticRecomputation; newIndex.OnlineIndexOperation = modelIndex.OnlineIndexOperation; newIndex.DisallowPageLocks = modelIndex.DisallowPageLocks; newIndex.DisallowRowLocks = modelIndex.DisallowRowLocks; newIndex.SortInTempdb = modelIndex.SortInTempdb; newIndex.IsFullTextKey = modelIndex.IsFullTextKey; newIndex.PadIndex = modelIndex.PadIndex; newIndex.FillFactor = modelIndex.FillFactor; newIndex.MaximumDegreeOfParallelism = modelIndex.MaximumDegreeOfParallelism; foreach (IndexedColumn indexedColumn in modelIndex.IndexedColumns) { var newIndexedColumn = new IndexedColumn( newIndex, indexedColumn.Name, indexedColumn.Descending) { IsIncluded = indexedColumn.IsIncluded }; newIndex.IndexedColumns.Add(newIndexedColumn); } return(new MySmoObjectBase( newIndex, newIndex.Name, productionTable.Name)); }
private static Dictionary <string, List <ushort> > GetColumnIndexAsDictionary(IColumn <object> column) { IndexedColumn indexedColumn = FindColumnComponent <IndexedColumn>(column); if (indexedColumn == null) { return(null); } else { return(indexedColumn.ConvertToDictionary()); } }
public static void AddIndex(this Table tb, IEnumerable <string> columnNames) { Index idx = new Index(tb, string.Format("IDX_{0}_{1}", tb.Name, string.Join("_", columnNames))); foreach (string name in columnNames) { IndexedColumn indexedColumn = new IndexedColumn(idx, name); idx.IndexedColumns.Add(indexedColumn); } idx.IndexKeyType = IndexKeyType.None; idx.IsClustered = false; idx.Create(); }
public void IndexedColumn_Where() { IColumn <ByteBlock> c = new IndexedColumn(ColumnFactory.CreateSortedColumn <ByteBlock>(new ByteBlockColumn(ByteBlock.Zero), 0), new DefaultWordSplitter()); // Verify no error searching empty column Assert.AreEqual("", ColumnTests.GetMatches(c, Operator.Matches, "missing")); c.SetSize(4); c[0] = "First Value"; c[1] = "Second Value"; c[2] = "this is a different value"; ColumnTests.AssertConsistent(c); // Verify passthrough to base column Assert.AreEqual("0", ColumnTests.GetMatches(c, Operator.Equals, "First Value")); // Verify unknown words don't match (no error) Assert.AreEqual("", ColumnTests.GetMatches(c, Operator.Matches, "missing")); // Verify all match for shared words Assert.AreEqual("0, 1, 2", ColumnTests.GetMatches(c, Operator.Matches, "value")); Assert.AreEqual("0, 1, 2", ColumnTests.GetMatches(c, Operator.MatchesExact, "value")); // Verify prefixes match for 'Matches' and not 'MatchesExact' Assert.AreEqual("0, 1, 2", ColumnTests.GetMatches(c, Operator.Matches, "val")); Assert.AreEqual("", ColumnTests.GetMatches(c, Operator.MatchesExact, "val")); // Verify unique words return only those matches Assert.AreEqual("0", ColumnTests.GetMatches(c, Operator.Matches, "first")); Assert.AreEqual("2", ColumnTests.GetMatches(c, Operator.Matches, "diff")); // Verify matches fallthrough to equals Assert.AreEqual("0", ColumnTests.GetMatches(c, Operator.Matches, "First Value")); c[0] = "Updated Value Here"; ColumnTests.AssertConsistent(c); // Verify items no longer match for removed words Assert.AreEqual("", ColumnTests.GetMatches(c, Operator.Matches, "first")); // Verify items continue to match for kept words Assert.AreEqual("0, 1, 2", ColumnTests.GetMatches(c, Operator.MatchesExact, "value")); // Verify items match for new words Assert.AreEqual("0", ColumnTests.GetMatches(c, Operator.MatchesExact, "updated")); }
internal static string ToCreateScript(this IndexedColumn column, Table table) { if (String.IsNullOrEmpty(column.Name)) { throw new ArgumentException($"{column} {nameof(IndexedColumn)}.{nameof(IndexedColumn.Name)} cannot be null or empty."); } var builder = new StringBuilder(); builder.Append($"[{column.Name}]"); if (!String.IsNullOrEmpty(column.CollationName)) { builder.Append($"collate {column.CollationName}"); } builder.Append($" {column.Direction.ToSqlString()}"); return(builder.ToString()); }
public IEnumerable<string> AddIndex(string tableName, IEnumerable<string> columnNames, string indexName) { Table table = GetTable(tableName); Index index = new Index(table, indexName); table.Indexes.Add(index); foreach (string columnName in columnNames) { var column = new Microsoft.SqlServer.Management.Smo.Column(table, columnName) { DataType = Microsoft.SqlServer.Management.Smo.DataType.Variant }; table.Columns.Add(column); IndexedColumn indexedColumn = new IndexedColumn(index, columnName); index.IndexedColumns.Add(indexedColumn); } index.Create(); return ScriptChanges(table.Parent.Parent); }
private static void CreatePurchaseOrderTable(Database database) { Table purchaseOrdersTable = new Table(database, "PurchaseOrders"); purchaseOrdersTable.Schema = "dbo"; Column idColumn = new Column(purchaseOrdersTable, "Id", DataType.Int); idColumn.Identity = true; idColumn.IdentityIncrement = 1; idColumn.IdentitySeed = 1; Column customerIdColumn = new Column(purchaseOrdersTable, "CustomerId", DataType.Int); Column orderDateTimeColumn = new Column(purchaseOrdersTable, "OrderDateTime", DataType.DateTime); Column orderTotalColumn = new Column(purchaseOrdersTable, "OrderTotal", DataType.Money); purchaseOrdersTable.Columns.Add(idColumn); purchaseOrdersTable.Columns.Add(customerIdColumn); purchaseOrdersTable.Columns.Add(orderDateTimeColumn); purchaseOrdersTable.Columns.Add(orderTotalColumn); ForeignKey foreignKey = new ForeignKey(purchaseOrdersTable, "FK_PurchaseOrders_CustomerId_Customers_Id"); ForeignKeyColumn foreignKeyColumn = new ForeignKeyColumn(foreignKey, "CustomerId", "Id"); foreignKey.ReferencedTable = "Customers"; foreignKey.ReferencedTableSchema = "dbo"; foreignKey.Columns.Add(foreignKeyColumn); purchaseOrdersTable.ForeignKeys.Add(foreignKey); Index index = new Index(purchaseOrdersTable, "PK_PurchaseOrders"); index.IndexKeyType = IndexKeyType.DriPrimaryKey; IndexedColumn idIndexedColumn = new IndexedColumn(index, "Id"); index.IndexedColumns.Add(idIndexedColumn); purchaseOrdersTable.Indexes.Add(index); purchaseOrdersTable.Create(); }
public IEnumerable <string> AddIndex(string tableName, IEnumerable <string> columnNames, string indexName) { Table table = GetTable(tableName); Index index = new Index(table, indexName); table.Indexes.Add(index); foreach (string columnName in columnNames) { var column = new Microsoft.SqlServer.Management.Smo.Column(table, columnName) { DataType = Microsoft.SqlServer.Management.Smo.DataType.Variant }; table.Columns.Add(column); IndexedColumn indexedColumn = new IndexedColumn(index, columnName); index.IndexedColumns.Add(indexedColumn); } index.Create(); return(ScriptChanges(table.Parent.Parent)); }
/// <summary> /// Create indexes for WKB table DEPRECATED!!!!! /// </summary> /// <param name="conn"></param> /// <param name="tableName"></param> public static void CreateWkbIndexes(SqlConnection conn, string tableName) { Server srv = new Server(new ServerConnection(new SqlConnection((conn.ConnectionString)))); Database db = srv.Databases[conn.Database]; Table tbl = db.Tables[tableName]; var envelopeFields = new[] { WkbfieldsDblEnvelopeMinX, WkbfieldsDblEnvelopeMinY, WkbfieldsDblEnvelopeMaxX, WkbfieldsDblEnvelopeMaxY }; foreach (string envelopeField in envelopeFields) { Index idx = new Index(tbl, "IDX_" + envelopeField); IndexedColumn indCol = new IndexedColumn(idx, envelopeField); idx.IndexedColumns.Add(indCol); idx.IndexKeyType = IndexKeyType.None; idx.IsClustered = false; idx.FillFactor = 50; idx.Create(); idx.Alter(); } }
private static void CreateCustomerTable(Database database) { Table customersTable = new Table(database, "Customers"); customersTable.Schema = "dbo"; Column idColumn = new Column(customersTable, "Id", DataType.Int); idColumn.Identity = true; idColumn.IdentityIncrement = 1; idColumn.IdentitySeed = 1; Column nameColumn = new Column(customersTable, "Name", DataType.VarChar(200)); nameColumn.Nullable = false; Column addressColumn = new Column(customersTable, "Address", DataType.VarChar(4000)); Column cityColumn = new Column(customersTable, "City", DataType.VarChar(50)); Column stateColumn = new Column(customersTable, "State", DataType.Char(2)); Column zipColumn = new Column(customersTable, "Zip", DataType.VarChar(9)); customersTable.Columns.Add(idColumn); customersTable.Columns.Add(nameColumn); customersTable.Columns.Add(addressColumn); customersTable.Columns.Add(cityColumn); customersTable.Columns.Add(stateColumn); customersTable.Columns.Add(zipColumn); Index index = new Index(customersTable, "PK_Customers"); index.IndexKeyType = IndexKeyType.DriPrimaryKey; IndexedColumn idIndexedColumn = new IndexedColumn(index, "Id"); index.IndexedColumns.Add(idIndexedColumn); customersTable.Indexes.Add(index); customersTable.Create(); }
public static void BuildIndexes(SqlConnectionStringBuilder csb, string dbName, params TableInfo[] tables) { if (string.IsNullOrWhiteSpace(dbName)) { throw new InvalidArgumentException("dbName is missing"); } ServerConnection connection = new ServerConnection(csb.DataSource, csb.UserID, csb.Password); Server server = new Server(connection); // Reference the AdventureWorks2012 database. Database database = server.Databases[dbName]; foreach (var tab in tables) { Table table = database.Tables[tab.Name]; // Define an Index object variable by providing the parent table and index name in the constructor. Index idx = new Index(table, tab.Name + "_PrimaryKey"); foreach (string name in tab.Values) { // Add indexed columns to the index. IndexedColumn icol = new IndexedColumn(idx, name, true); idx.IndexedColumns.Add(icol); } // Set the index properties. idx.IndexKeyType = IndexKeyType.DriPrimaryKey; idx.IsClustered = false; idx.FillFactor = 90; // Create the index on the instance of SQL Server. idx.Create(); // Modify the page locks property. idx.DisallowPageLocks = true; // Run the Alter method to make the change on the instance of SQL Server. idx.Alter(); } }
public void IndexedColumn_DictionaryConversion() { IndexedColumn c = new IndexedColumn(ColumnFactory.CreateSortedColumn <ByteBlock>(new ByteBlockColumn(ByteBlock.Zero), 0), new DefaultWordSplitter()); c.SetSize(4); c[0] = "First Value"; c[1] = "Second Value"; c[2] = "this is a different value"; ColumnTests.AssertConsistent(c); // Verify Dictionary conversion Dictionary <string, List <ushort> > dictionary = c.ConvertToDictionary(); Assert.AreEqual(7, dictionary.Keys.Count); Assert.AreEqual("0, 1, 2", String.Join(", ", dictionary["value"])); Assert.AreEqual("0", String.Join(", ", dictionary["first"])); Assert.AreEqual("1", String.Join(", ", dictionary["second"])); Assert.AreEqual("2", String.Join(", ", dictionary["this"])); Assert.AreEqual("2", String.Join(", ", dictionary["is"])); Assert.AreEqual("2", String.Join(", ", dictionary["a"])); Assert.AreEqual("2", String.Join(", ", dictionary["different"])); }
///-------------------------------------------------------------------------------- /// <summary>This loads information from a SQL indexed column.</summary> /// /// <param name="sqlIndexedColumn">The input sql indexed column.</param> ///-------------------------------------------------------------------------------- public void LoadColumn(IndexedColumn sqlIndexedColumn) { try { // load the basic index column information SqlIndexedColumnName = sqlIndexedColumn.Name; DbID = sqlIndexedColumn.ID; IsIncluded = sqlIndexedColumn.IsIncluded; IsComputed = sqlIndexedColumn.IsComputed; Descending = sqlIndexedColumn.Descending; Urn = sqlIndexedColumn.Urn; State = sqlIndexedColumn.State.ToString(); // load information for each property //foreach (Microsoft.SqlServer.Management.Smo.Property loopProperty in sqlIndexedColumn.Properties) //{ // if (loopProperty.Expensive == false && loopProperty.IsNull == false && !String.IsNullOrEmpty(loopProperty.Value.ToString())) // { // SqlProperty property = new SqlProperty(); // property.SqlPropertyID = Guid.NewGuid(); // property.SqlIndexedColumn = this; // property.LoadProperty(loopProperty); // SqlPropertyList.Add(property); // } //} } catch (ApplicationAbortException) { throw; } catch (Exception ex) { bool reThrow = BusinessConfiguration.HandleException(ex); if (reThrow) { throw; } } }
private static IUntypedColumn BuildByteBlock(ColumnDetails details, string[] typeComponents) { ByteBlock defaultValue; Value.Create(details.Default).TryConvert <ByteBlock>(out defaultValue); // Build the raw column IColumn <ByteBlock> columnSoFar = new ByteBlockColumn(defaultValue); // Wrap the column as requested (the last component is the type itself) for (int i = typeComponents.Length - 2; i >= 0; --i) { string fullComponent = typeComponents[i]; string[] componentParts = fullComponent.Split('[', ']'); switch (componentParts[0]) { case "sorted": columnSoFar = CreateSortedColumn(columnSoFar, 0); break; case "indexed": columnSoFar = new IndexedColumn(columnSoFar, BuildSplitter(componentParts.Length > 1 ? componentParts[1] : "default")); break; default: throw new ArribaException(StringExtensions.Format("Column Type Wrapper '{0}' is not currently supported.", typeComponents[i])); } } // De-type the column for generic use var utc = new UntypedColumn <ByteBlock>(columnSoFar); // Tell it the column name utc.Name = details.Name; return(utc); }
private void createStgIndex(Index i, TableViewBase parent) { if (i.PartitionScheme == "") { throw (new System.NotSupportedException( String.Format("The index '{0}' is not aligned to a Partition Scheme", i.Name))); } // todo: differentiate between Base Table as source, and View as source // LZAV: Index stgIndex = new Index(parent, parent.Name + "_" + i.Name); String indexName = parent.Name + "_" + i.Name; // LZAV if (indexName.Length > 128) // LZAV { indexName = "IX_CL_" + parent.Name; // LZAV } Index stgIndex = new Index(parent, indexName); // LZAV foreach (IndexedColumn iCol in i.IndexedColumns) { IndexedColumn stgICol = new IndexedColumn(stgIndex, iCol.Name, iCol.Descending); stgICol.IsIncluded = iCol.IsIncluded; stgIndex.IndexedColumns.Add(stgICol); } stgIndex.IndexType = i.IndexType; stgIndex.IndexKeyType = i.IndexKeyType; stgIndex.IsClustered = i.IsClustered; stgIndex.IsUnique = i.IsUnique; stgIndex.CompactLargeObjects = i.CompactLargeObjects; stgIndex.IgnoreDuplicateKeys = i.IgnoreDuplicateKeys; stgIndex.IsFullTextKey = i.IsFullTextKey; stgIndex.PadIndex = i.PadIndex; stgIndex.FileGroup = db.PartitionSchemes[i.PartitionScheme].FileGroups[partitionNumber - 1]; // add the partitioning column to the index if it is not already there String partitionKeyName = i.PartitionSchemeParameters[0].Name; if (stgIndex.IndexedColumns[partitionKeyName] == null) { IndexedColumn stgICol = new IndexedColumn(stgIndex, partitionKeyName); // It is added as a Key to the Clustered index and as an Include column to a Nonclustered stgICol.IsIncluded = !stgIndex.IsClustered; stgIndex.IndexedColumns.Add(stgICol); } if (srv.VersionMajor >= 10) { // Define compression property to match by creating a Physical Partition object (not applicable to Colstore) { PhysicalPartition stgPartition = new PhysicalPartition(stgIndex, 1); if (i.IndexType != IndexType.NonClusteredColumnStoreIndex) { stgPartition.DataCompression = i.PhysicalPartitions[partitionNumber - 1].DataCompression; } stgIndex.PhysicalPartitions.Add(stgPartition); } // Handle Filtered Index if (i.HasFilter) { stgIndex.FilterDefinition = i.FilterDefinition; } } scriptChunks.Add(stgIndex.Script()); if (executeCommands) { stgIndex.Create(); } }
public static void AddColumnToIndex(Table table, Column column, Index ind) { string oldName = ind.Name; var idx = new Index(table, oldName) { BoundingBoxYMax = ind.BoundingBoxYMax, BoundingBoxYMin = ind.BoundingBoxYMin, BoundingBoxXMax = ind.BoundingBoxXMax, BoundingBoxXMin = ind.BoundingBoxXMin, CompactLargeObjects = ind.CompactLargeObjects, DisallowRowLocks = ind.DisallowRowLocks, FileGroup = ind.FileGroup, FileStreamFileGroup = ind.FileStreamFileGroup, FileStreamPartitionScheme = ind.FileStreamPartitionScheme, FillFactor = ind.FillFactor, FilterDefinition = ind.FilterDefinition, IgnoreDuplicateKeys = ind.IgnoreDuplicateKeys, IndexKeyType = ind.IndexKeyType, IsClustered = ind.IsClustered, IsFullTextKey = ind.IsFullTextKey, IsUnique = ind.IsUnique, Level1Grid = ind.Level1Grid, Level2Grid = ind.Level2Grid, Level3Grid = ind.Level3Grid, Level4Grid = ind.Level4Grid, MaximumDegreeOfParallelism = ind.MaximumDegreeOfParallelism, NoAutomaticRecomputation = ind.NoAutomaticRecomputation, OnlineIndexOperation = ind.OnlineIndexOperation, PadIndex = ind.PadIndex, ParentXmlIndex = ind.ParentXmlIndex, PartitionScheme = ind.PartitionScheme, SecondaryXmlIndexType = ind.SecondaryXmlIndexType, SortInTempdb = ind.SortInTempdb, SpatialIndexType = ind.SpatialIndexType, }; foreach (IndexedColumn iColumn in ind.IndexedColumns) { var newIdxColumn = new IndexedColumn(idx, iColumn.Name) { Descending = iColumn.Descending, IsIncluded = iColumn.IsIncluded, }; idx.IndexedColumns.Add(newIdxColumn); } idx.IndexedColumns.Add(new IndexedColumn(idx, column.Name)); bool oldDisallowPageLocks = ind.DisallowPageLocks; ind.Drop(); idx.Create(); idx.DisallowPageLocks = oldDisallowPageLocks; idx.Alter(); }
private void CreatePrimaryKey() { Index index = new Index(this.table, "PK_" + this.table.Name); foreach (var column in this.keyColumns) { IndexedColumn indexedColumn = new IndexedColumn(index, column.Name); index.IndexedColumns.Add(indexedColumn); } index.IndexKeyType = IndexKeyType.DriPrimaryKey; this.table.Indexes.Add(index); }
public void SynchronizeTable(SchemaTable schemaTable) { var smoTable = GetSmoTable(schemaTable); if (smoTable == null) { smoTable = new Table(GetSmoDatabase(), schemaTable.Name); foreach (var column in schemaTable.Columns) { var newcol = column.GetSmoColumn(smoTable); smoTable.Columns.Add(newcol); } smoTable.Create(); // primary key if (!schemaTable.IsLogTable && !schemaTable.IsProvodkasTable) { var pk = new Index(smoTable, "PK_" + schemaTable.Name); var icol = new IndexedColumn(pk, schemaTable.GetPrimaryKeyColumn().Name, false); pk.IndexedColumns.Add(icol); pk.IndexKeyType = IndexKeyType.DriPrimaryKey; pk.IsClustered = true; pk.FillFactor = 50; pk.Create(); } } else { foreach (var schemaColumn in schemaTable.Columns) { if (smoTable.Columns.Contains(schemaColumn.Name)) { if ((schemaColumn.Table.GetPrimaryKeyColumn()==null || schemaColumn.Name != schemaColumn.Table.GetPrimaryKeyColumn().Name) && !(schemaColumn.DataType is TimestampDataType)) { var smoColumn = smoTable.Columns[schemaColumn.Name]; var newDataType = schemaColumn.DataType.GetSmoDataType(); if (!smoColumn.DataType.Equals(newDataType)) smoColumn.DataType = newDataType; if (smoColumn.Nullable != !schemaColumn.IsNotNullable) smoColumn.Nullable = !schemaColumn.IsNotNullable; } } else { var newcol = schemaColumn.GetSmoColumn(smoTable); smoTable.Columns.Add(newcol); } } smoTable.Alter(); } }
private void createStgIndex(Index i, TableViewBase parent) { if (i.PartitionScheme == "") throw (new System.NotSupportedException( String.Format("The index '{0}' is not aligned to a Partition Scheme", i.Name))); // todo: differentiate between Base Table as source, and View as source // LZAV: Index stgIndex = new Index(parent, parent.Name + "_" + i.Name); String indexName = parent.Name + "_" + i.Name; // LZAV if (indexName.Length > 128) // LZAV indexName = "IX_CL_" + parent.Name; // LZAV Index stgIndex = new Index(parent, indexName); // LZAV foreach (IndexedColumn iCol in i.IndexedColumns) { IndexedColumn stgICol = new IndexedColumn(stgIndex, iCol.Name, iCol.Descending); stgICol.IsIncluded = iCol.IsIncluded; stgIndex.IndexedColumns.Add(stgICol); } stgIndex.IndexType = i.IndexType; stgIndex.IndexKeyType = i.IndexKeyType; stgIndex.IsClustered = i.IsClustered; stgIndex.IsUnique = i.IsUnique; stgIndex.CompactLargeObjects = i.CompactLargeObjects; stgIndex.IgnoreDuplicateKeys = i.IgnoreDuplicateKeys; stgIndex.IsFullTextKey = i.IsFullTextKey; stgIndex.PadIndex = i.PadIndex; stgIndex.FileGroup = db.PartitionSchemes[i.PartitionScheme].FileGroups[partitionNumber - 1]; // add the partitioning column to the index if it is not already there String partitionKeyName = i.PartitionSchemeParameters[0].Name; if (stgIndex.IndexedColumns[partitionKeyName] == null) { IndexedColumn stgICol = new IndexedColumn(stgIndex, partitionKeyName); // It is added as a Key to the Clustered index and as an Include column to a Nonclustered stgICol.IsIncluded = !stgIndex.IsClustered; stgIndex.IndexedColumns.Add(stgICol); } if (srv.VersionMajor >= 10) { // Define compression property to match by creating a Physical Partition object (not applicable to Colstore) { PhysicalPartition stgPartition = new PhysicalPartition(stgIndex, 1); if (i.IndexType != IndexType.NonClusteredColumnStoreIndex) { stgPartition.DataCompression = i.PhysicalPartitions[partitionNumber - 1].DataCompression; } stgIndex.PhysicalPartitions.Add(stgPartition); } // Handle Filtered Index if (i.HasFilter) { stgIndex.FilterDefinition = i.FilterDefinition; } } scriptChunks.Add(stgIndex.Script()); if (executeCommands) stgIndex.Create(); }
public int CreateTable(Type ty, CreateFlags createFlags = CreateFlags.None) { if (_tables == null) { _tables = new Dictionary <string, TableMapping>(); } if (!_tables.TryGetValue(ty.FullName, out TableMapping value)) { value = GetMapping(ty, createFlags); _tables.Add(ty.FullName, value); } string str = "create table if not exists \"" + value.TableName + "\"(\n"; IEnumerable <string> source = from p in value.Columns select Orm.SqlDecl(p, StoreDateTimeAsTicks); string str2 = string.Join(",\n", source.ToArray()); str += str2; str += ")"; int num = Execute(str); if (num == 0) { MigrateTable(value); } Dictionary <string, IndexInfo> dictionary = new Dictionary <string, IndexInfo>(); TableMapping.Column[] columns = value.Columns; foreach (TableMapping.Column column in columns) { foreach (IndexedAttribute index in column.Indices) { string text = index.Name ?? (value.TableName + "_" + column.Name); if (!dictionary.TryGetValue(text, out IndexInfo value2)) { value2 = default(IndexInfo); IndexInfo indexInfo = value2; indexInfo.IndexName = text; indexInfo.TableName = value.TableName; indexInfo.Unique = index.Unique; indexInfo.Columns = new List <IndexedColumn>(); value2 = indexInfo; dictionary.Add(text, value2); } if (index.Unique != value2.Unique) { throw new Exception("All the columns in an index must have the same value for their Unique property"); } value2.Columns.Add(new IndexedColumn { Order = index.Order, ColumnName = column.Name }); } } foreach (string key in dictionary.Keys) { IndexInfo indexInfo2 = dictionary[key]; string[] array = new string[indexInfo2.Columns.Count]; if (indexInfo2.Columns.Count == 1) { string[] array2 = array; IndexedColumn indexedColumn = indexInfo2.Columns[0]; array2[0] = indexedColumn.ColumnName; } else { indexInfo2.Columns.Sort((IndexedColumn lhs, IndexedColumn rhs) => lhs.Order - rhs.Order); int j = 0; for (int count = indexInfo2.Columns.Count; j < count; j++) { string[] array3 = array; int num2 = j; IndexedColumn indexedColumn2 = indexInfo2.Columns[j]; array3[num2] = indexedColumn2.ColumnName; } } num += CreateIndex(key, indexInfo2.TableName, array, indexInfo2.Unique); } return(num); }
/// <summary> /// Создаём таблицу из схемы модуля /// </summary> /// <param name="module">Метаданные модуля</param> public void CreateTableFromModuleSchema(ModuleMetadata module) { if (!_database.Tables.Contains(module.ModuleName)) { try { // создаём таблицу Table targetTable = new Table(_database, module.ModuleName); // // добавляем базовые столбцы в таблицу // #region Внешниый ключ Column plowMachineIdColumn = new Column(targetTable, "PlowMachineId"); plowMachineIdColumn.DataType = DataType.UniqueIdentifier; plowMachineIdColumn.RowGuidCol = true; plowMachineIdColumn.Nullable = false; ForeignKey fk = new ForeignKey(targetTable, "FK_" + module.ModuleName + "_PlowMachine"); ForeignKeyColumn fk_column = new ForeignKeyColumn(fk, "PlowMachineId"); fk_column.ReferencedColumn = "PlowMachineId"; fk.ReferencedTable = "PlowMachines"; fk.Columns.Add(fk_column); targetTable.ForeignKeys.Add(fk); targetTable.Columns.Add(plowMachineIdColumn); #endregion // // добавляем столбцы в таблицу // foreach (FieldMetadata f in module.MetadataFields) { Column column = CreateColumn(targetTable, f); targetTable.Columns.Add(column); } targetTable.Create(); #region Первичный ключ Index idx = new Index(targetTable, "PK_" + module.ModuleName); IndexedColumn idxc = new IndexedColumn(idx, plowMachineIdColumn.Name); idx.IndexedColumns.Add(idxc); idx.IndexKeyType = IndexKeyType.DriPrimaryKey; idx.IsClustered = true; idx.IsUnique = true; idx.Create(); #endregion } catch (Microsoft.SqlServer.Management.Smo.InvalidSmoOperationException) { throw; } } else { throw new InvalidOperationException("Таблица с именем '" + module.ModuleName + "' уже существует в БД."); } }
private static void ApplyIndexesForeignKeysChecks(Database destinationDatabase, NamedSmoObject namedSmoObject, string schema) { Table destinationTable = destinationDatabase.Tables[namedSmoObject.Name, schema]; #region Indexes foreach (Index sourceIndex in (namedSmoObject as Table).Indexes) { string name = sourceIndex.Name; Index index = new Index(destinationTable, name); index.IndexKeyType = sourceIndex.IndexKeyType; index.IsClustered = sourceIndex.IsClustered; index.IsUnique = sourceIndex.IsUnique; index.CompactLargeObjects = sourceIndex.CompactLargeObjects; index.IgnoreDuplicateKeys = sourceIndex.IgnoreDuplicateKeys; index.IsFullTextKey = sourceIndex.IsFullTextKey; index.PadIndex = sourceIndex.PadIndex; index.FileGroup = sourceIndex.FileGroup; foreach (IndexedColumn sourceIndexedColumn in sourceIndex.IndexedColumns) { IndexedColumn column = new IndexedColumn(index, sourceIndexedColumn.Name, sourceIndexedColumn.Descending); column.IsIncluded = sourceIndexedColumn.IsIncluded; index.IndexedColumns.Add(column); } index.FileGroup = destinationTable.FileGroup ?? index.FileGroup; index.Create(); } #endregion #region ForeignKeys foreach (ForeignKey sourceFK in (namedSmoObject as Table).ForeignKeys) { string name = sourceFK.Name; ForeignKey foreignkey = new ForeignKey(destinationTable, name); foreignkey.DeleteAction = sourceFK.DeleteAction; foreignkey.IsChecked = sourceFK.IsChecked; foreignkey.IsEnabled = sourceFK.IsEnabled; foreignkey.ReferencedTable = sourceFK.ReferencedTable; foreignkey.ReferencedTableSchema = sourceFK.ReferencedTableSchema; foreignkey.UpdateAction = sourceFK.UpdateAction; foreach (ForeignKeyColumn sourceFKColumn in sourceFK.Columns) { string referencedColumn = sourceFKColumn.ReferencedColumn; ForeignKeyColumn column = new ForeignKeyColumn(foreignkey, sourceFKColumn.Name, referencedColumn); foreignkey.Columns.Add(column); } foreignkey.Create(); } #endregion #region Checks foreach (Check chkConstr in (namedSmoObject as Table).Checks) { Check check = new Check(destinationTable, chkConstr.Name); check.IsChecked = chkConstr.IsChecked; check.IsEnabled = chkConstr.IsEnabled; check.Text = chkConstr.Text; check.Create(); } #endregion }
public string CreateTableBySMO(Models.Database database, string destination_tablename) { try { string conString = string.Empty; string tablename = database.table.Substring(database.table.IndexOf('.') + 1); if (database.authentication == "WA") { conString = "Data Source=" + database.servername + ";Initial Catalog =" + database.database + ";Integrated Security=True"; } else if (database.authentication == "SQL") { conString = "Data Source=" + database.servername + ";Initial Catalog =" + database.database + ";Integrated Security=False; User ID = " + database.username + "; Password = "******""; } SqlConnection connection = new SqlConnection(conString); var conn = new ServerConnection(connection); //Connect to the local, default instance of SQL Server. Server srv; srv = new Server(conn); //Reference the AdventureWorks2012 database. Database testDB = srv.Databases[database.database]; Table myTable = testDB.Tables[tablename]; myTable.Refresh(); Table newTable = new Table(testDB, destination_tablename); foreach (Column col in myTable.Columns) { Column newColumn = new Column(newTable, col.Name); newColumn.DataType = col.DataType; newColumn.Default = col.Default; newColumn.Identity = col.Identity; newColumn.IdentityIncrement = col.IdentityIncrement; newColumn.IdentitySeed = col.IdentitySeed; newColumn.Nullable = col.Nullable; newTable.Columns.Add(newColumn); } newTable.Create(); #region Creating Foreign Keys if ((myTable as Table).ForeignKeys.Count != 0) { foreach (ForeignKey sourcefk in (myTable as Table).ForeignKeys) { try { string name = Guid.NewGuid().ToString(); ForeignKey foreignkey = new ForeignKey(newTable, name); foreignkey.DeleteAction = sourcefk.DeleteAction; foreignkey.IsChecked = sourcefk.IsChecked; foreignkey.IsEnabled = sourcefk.IsEnabled; foreignkey.ReferencedTable = sourcefk.ReferencedTable; foreignkey.ReferencedTableSchema = sourcefk.ReferencedTableSchema; foreignkey.UpdateAction = sourcefk.UpdateAction; foreach (ForeignKeyColumn scol in sourcefk.Columns) { string refcol = scol.ReferencedColumn; ForeignKeyColumn column = new ForeignKeyColumn(foreignkey, scol.Name, refcol); foreignkey.Columns.Add(column); } foreignkey.Create(); } catch (Exception ex) { throw; } } } #endregion #region Creating Indexes if ((myTable as Table).Indexes.Count != 0) { foreach (Index srcind in (myTable as Table).Indexes) { try { string name = Guid.NewGuid().ToString(); Index index = new Index(newTable, name); index.IndexKeyType = srcind.IndexKeyType; index.IsClustered = srcind.IsClustered; index.IsUnique = srcind.IsUnique; index.CompactLargeObjects = srcind.CompactLargeObjects; index.IgnoreDuplicateKeys = srcind.IgnoreDuplicateKeys; index.IsFullTextKey = srcind.IsFullTextKey; index.PadIndex = srcind.PadIndex; index.FileGroup = srcind.FileGroup; foreach (IndexedColumn srccol in srcind.IndexedColumns) { IndexedColumn column = new IndexedColumn(index, srccol.Name, srccol.Descending); column.IsIncluded = srccol.IsIncluded; index.IndexedColumns.Add(column); } index.FileGroup = newTable.FileGroup ?? index.FileGroup; index.Create(); } catch (Exception exc) { throw; } } } #endregion return(newTable.Schema + "." + newTable.Name); } catch (Exception) { return(string.Empty); } }
private void DwGenerateTable(DsDwTableMap_M aDsDwTableMap) { if (aDsDwTableMap != null && aDsDwTableMap.DsDwColumnMapList != null) { Table aSMOTable = new Table(DwDb, aDsDwTableMap.DwTableName, aDsDwTableMap.DwSchemaName); //Add columns to the table foreach (DsDwColumnMap_M aDsDwColumnMap in aDsDwTableMap.DsDwColumnMapList) { if (aDsDwColumnMap != null && aDsDwColumnMap.Include) { Column aNewColumn; switch (aDsDwColumnMap.Transformation) { case DsDwColumnTransformation.SurrogateKey: aNewColumn = new Column(aSMOTable, aDsDwColumnMap.DwColumn.Name, aDsDwColumnMap.DwColumn.DataType); aNewColumn.Nullable = false; aNewColumn.Identity = true; aNewColumn.IdentityIncrement = 1; aNewColumn.IdentitySeed = 1; aSMOTable.Columns.Add(aNewColumn); break; case DsDwColumnTransformation.BusinesKey: aNewColumn = new Column(aSMOTable, aDsDwColumnMap.DwColumn.Name, aDsDwColumnMap.DwColumn.DataType); aNewColumn.Nullable = false; aNewColumn.Collation = aDsDwColumnMap.DwColumn.Collation; aSMOTable.Columns.Add(aNewColumn); break; default: aNewColumn = new Column(aSMOTable, aDsDwColumnMap.DwColumn.Name, aDsDwColumnMap.DwColumn.DataType); aNewColumn.Collation = aDsDwColumnMap.DwColumn.Collation; aNewColumn.Nullable = true; aSMOTable.Columns.Add(aNewColumn); break; } } } aSMOTable.Create(); //Add indexes and primary key the table foreach (DsDwColumnMap_M aDsDwColumnMap in aDsDwTableMap.DsDwColumnMapList) { if (aDsDwColumnMap != null && aDsDwColumnMap.Include) { switch (aDsDwColumnMap.Transformation) { case DsDwColumnTransformation.SurrogateKey: Index primaryKey = new Index(aSMOTable, "PK_" + aDsDwTableMap.DwTableName); IndexedColumn indexedColumn = new IndexedColumn(primaryKey, aDsDwColumnMap.DwColumn.Name); primaryKey.IndexedColumns.Add(indexedColumn); primaryKey.IndexKeyType = IndexKeyType.DriPrimaryKey; primaryKey.Create(); break; case DsDwColumnTransformation.BusinesKey: // Define an Index object variable by providing the parent table and index name in the constructor. Index idx; idx = new Index(aSMOTable, "IX_" + aDsDwTableMap.DwTableName + "_" + aDsDwColumnMap.DwColumn.Name); // Add indexed columns to the index. IndexedColumn icol1; icol1 = new IndexedColumn(idx, aDsDwColumnMap.DwColumn.Name, true); idx.IndexedColumns.Add(icol1); // Set the index properties. idx.IndexKeyType = IndexKeyType.None; idx.IsClustered = false; idx.FillFactor = 70; // Create the index on the instance of SQL Server. idx.Create(); break; } } } } }
public void IntializeDatabase() { if (Logger != null) { Logger.Log(LogLevel.Information, $"Initializing Database {DatabaseName}"); } var database = (SqlServerDatabase)GetDatabase(); if (!database.Schemas.Any(s => s.Name == _VersionSchema)) { var schema = new Schema(database.SmoDatabase, "version"); if (Logger != null) { Logger.Log(LogLevel.Information, $"Creating version Schema in {DatabaseName}"); } schema.Create(); if (Logger != null) { Logger.Log(LogLevel.Debug, $"Created version Schema in {DatabaseName}"); } } if (!database.Tables.Any(t => t.Name == _VersionTable && ((SqlServerTable)t).Schema == _VersionSchema)) { var table = new Table(database.SmoDatabase, "Log", "version"); if (Logger != null) { Logger.Log(LogLevel.Information, $"Creating version.Log Table in {DatabaseName}"); } var scriptColumnName = "Script"; var fileNameColumnName = "FileName"; var deployDateColumnName = "DeployDate"; var scriptColumn = new Column(table, scriptColumnName, DataType.NVarChar(2000)); scriptColumn.Nullable = false; table.Columns.Add(scriptColumn); var fileNameColumn = new Column(table, fileNameColumnName, DataType.NVarCharMax); fileNameColumn.Nullable = false; table.Columns.Add(fileNameColumn); var deployDateColumn = new Column(table, deployDateColumnName, DataType.DateTimeOffset(7)); deployDateColumn.Nullable = false; table.Columns.Add(deployDateColumn); table.Create(); if (Logger != null) { Logger.Log(LogLevel.Debug, $"Created version.Log Table in {DatabaseName}"); } // Define Index object on the table by supplying the Table1 as the parent table and the primary key name in the constructor. if (Logger != null) { Logger.Log(LogLevel.Information, $"Creating {table.Schema}_{table.Name}_PK Primary Key in {DatabaseName}"); } Index pk = new Index(table, $"{table.Schema}_{table.Name}_PK"); pk.IndexKeyType = IndexKeyType.DriPrimaryKey; // Add Col1 as the Index Column IndexedColumn idxCol1 = new IndexedColumn(pk, "Script"); pk.IndexedColumns.Add(idxCol1); // Create the Primary Key pk.Create(); if (Logger != null) { Logger.Log(LogLevel.Debug, $"Created {table.Schema}_{table.Name}_PK Primary Key in {DatabaseName}"); } } if (Logger != null) { Logger.Log(LogLevel.Information, $"Database {DatabaseName} has been initialized."); } }