/// <summary> /// Creates a single index with the specified definition in the table /// underlying this table definition /// </summary> /// <param name="indexDefinition">The index definition.</param> public void CreateIndex(IndexDefinition indexDefinition) { lock (this.database.IsamSession) { using (IsamTransaction trx = new IsamTransaction(this.database.IsamSession)) { // open the table JET_TABLEID tableid; Api.JetOpenTable( this.database.IsamSession.Sesid, this.database.Dbid, this.name, null, 0, OpenTableGrbit.None, out tableid); // add the new index to the table JET_INDEXCREATE[] indexcreates = new JET_INDEXCREATE[1] { new JET_INDEXCREATE() }; indexcreates[0].szIndexName = indexDefinition.Name; indexcreates[0].szKey = DatabaseCommon.IndexKeyFromIndexDefinition(indexDefinition); indexcreates[0].cbKey = indexcreates[0].szKey.Length; indexcreates[0].grbit = DatabaseCommon.GrbitFromIndexDefinition(indexDefinition); indexcreates[0].ulDensity = indexDefinition.Density; indexcreates[0].pidxUnicode = new JET_UNICODEINDEX(); indexcreates[0].pidxUnicode.lcid = indexDefinition.CultureInfo.LCID; indexcreates[0].pidxUnicode.dwMapFlags = Converter.MapFlagsFromUnicodeIndexFlags(Converter.UnicodeFlagsFromCompareOptions(indexDefinition.CompareOptions)); indexcreates[0].rgconditionalcolumn = DatabaseCommon.ConditionalColumnsFromIndexDefinition(indexDefinition); indexcreates[0].cConditionalColumn = indexcreates[0].rgconditionalcolumn.Length; indexcreates[0].cbKeyMost = indexDefinition.MaxKeyLength; Api.JetCreateIndex2(this.database.IsamSession.Sesid, tableid, indexcreates, indexcreates.Length); // commit our change Api.JetCloseTable(this.database.IsamSession.Sesid, tableid); trx.Commit(); DatabaseCommon.SchemaUpdateID++; } } }
/// <summary> /// Makes the <see cref="JET_OPENTEMPORARYTABLE"/> object to later open it. /// </summary> /// <param name="tableDefinition">The table definition.</param> /// <returns>The newly created <see cref="JET_OPENTEMPORARYTABLE"/> object.</returns> private JET_OPENTEMPORARYTABLE MakeOpenTemporaryTable(TableDefinition tableDefinition) { JET_OPENTEMPORARYTABLE openTemporaryTable = new JET_OPENTEMPORARYTABLE(); // allocate room for our columns int currentColumndef = 0; openTemporaryTable.ccolumn = tableDefinition.Columns.Count; openTemporaryTable.prgcolumndef = new JET_COLUMNDEF[openTemporaryTable.ccolumn]; openTemporaryTable.prgcolumnid = new JET_COLUMNID[openTemporaryTable.ccolumn]; for (int coldef = 0; coldef < openTemporaryTable.ccolumn; ++coldef) { openTemporaryTable.prgcolumndef[coldef] = new JET_COLUMNDEF(); } // first, collect all the key columns in order and put them as the // first columndefs. we have to do this to guarantee that the TT // is sorted properly foreach (IndexDefinition indexDefinition in tableDefinition.Indices) { foreach (KeyColumn keyColumn in indexDefinition.KeyColumns) { ColumnDefinition columnDefinition = tableDefinition.Columns[keyColumn.Name]; openTemporaryTable.prgcolumndef[currentColumndef].coltyp = DatabaseCommon.ColtypFromColumnDefinition(columnDefinition); openTemporaryTable.prgcolumndef[currentColumndef].cp = JET_CP.Unicode; openTemporaryTable.prgcolumndef[currentColumndef].cbMax = columnDefinition.MaxLength; openTemporaryTable.prgcolumndef[currentColumndef].grbit = (ColumndefGrbit)columnDefinition.Flags | ColumndefGrbit.TTKey | (keyColumn.IsAscending ? ColumndefGrbit.None : ColumndefGrbit.TTDescending); currentColumndef++; } } // next collect the rest of the columns and put them after the key // columns, skipping over the columns we already added foreach (ColumnDefinition columnDefinition in tableDefinition.Columns) { bool alreadyAdded = false; foreach (IndexDefinition indexDefinition in tableDefinition.Indices) { foreach (KeyColumn keyColumn in indexDefinition.KeyColumns) { if (keyColumn.Name.ToLower(CultureInfo.InvariantCulture) == columnDefinition.Name.ToLower(CultureInfo.InvariantCulture)) { alreadyAdded = true; } } } if (!alreadyAdded) { openTemporaryTable.prgcolumndef[currentColumndef].coltyp = DatabaseCommon.ColtypFromColumnDefinition(columnDefinition); openTemporaryTable.prgcolumndef[currentColumndef].cp = JET_CP.Unicode; openTemporaryTable.prgcolumndef[currentColumndef].cbMax = columnDefinition.MaxLength; openTemporaryTable.prgcolumndef[currentColumndef].grbit = Converter.ColumndefGrbitFromColumnFlags(columnDefinition.Flags); currentColumndef++; } } // set the index flags foreach (IndexDefinition indexDefinition in tableDefinition.Indices) { openTemporaryTable.pidxunicode = new JET_UNICODEINDEX(); openTemporaryTable.pidxunicode.lcid = indexDefinition.CultureInfo.LCID; UnicodeIndexFlags unicodeIndexFlags = Converter.UnicodeFlagsFromCompareOptions(indexDefinition.CompareOptions); openTemporaryTable.pidxunicode.dwMapFlags = Converter.MapFlagsFromUnicodeIndexFlags(unicodeIndexFlags); } // infer the TT mode of operation and set its grbits accordingly bool haveColumnWithLongValue = false; foreach (ColumnDefinition columnDefinition in tableDefinition.Columns) { JET_coltyp coltyp = DatabaseCommon.ColtypFromColumnDefinition(columnDefinition); if (coltyp == JET_coltyp.LongText || coltyp == JET_coltyp.LongBinary) { haveColumnWithLongValue = true; } } bool haveIndexWithSortNullsHigh = false; foreach (IndexDefinition indexDefinition in tableDefinition.Indices) { if ((indexDefinition.Flags & IndexFlags.SortNullsHigh) != 0) { haveIndexWithSortNullsHigh = true; } } if (tableDefinition.Type == TableType.Sort) { foreach (IndexDefinition indexDefinition in tableDefinition.Indices) { if ((indexDefinition.Flags & (IndexFlags.Unique | IndexFlags.Primary)) == 0) { // External Sort without duplicate removal openTemporaryTable.grbit = Server2003Grbits.ForwardOnly | (haveColumnWithLongValue ? Windows7Grbits.IntrinsicLVsOnly : TempTableGrbit.None) | (haveIndexWithSortNullsHigh ? TempTableGrbit.SortNullsHigh : TempTableGrbit.None); } else { // External Sort TT with deferred duplicate removal openTemporaryTable.grbit = TempTableGrbit.Unique | (haveColumnWithLongValue ? Windows7Grbits.IntrinsicLVsOnly : TempTableGrbit.None) | (haveIndexWithSortNullsHigh ? TempTableGrbit.SortNullsHigh : TempTableGrbit.None); } } } else if (tableDefinition.Type == TableType.PreSortTemporary) { // Pre-sorted B+ Tree TT with deferred duplicate removal openTemporaryTable.grbit = TempTableGrbit.Indexed | TempTableGrbit.Unique | TempTableGrbit.Updatable | TempTableGrbit.Scrollable | (haveColumnWithLongValue ? Windows7Grbits.IntrinsicLVsOnly : TempTableGrbit.None) | (haveIndexWithSortNullsHigh ? TempTableGrbit.SortNullsHigh : TempTableGrbit.None); } else if (tableDefinition.Type == TableType.Temporary) { if (tableDefinition.Indices.Count != 0) { // B+ Tree TT with immediate duplicate removal openTemporaryTable.grbit = TempTableGrbit.Indexed | TempTableGrbit.Unique | TempTableGrbit.Updatable | TempTableGrbit.Scrollable | TempTableGrbit.ForceMaterialization | (haveIndexWithSortNullsHigh ? TempTableGrbit.SortNullsHigh : TempTableGrbit.None); } else { // B+ Tree TT with a sequential index openTemporaryTable.grbit = TempTableGrbit.Updatable | TempTableGrbit.Scrollable; } } // set the key construction parameters for the TT foreach (IndexDefinition indexDefinition in tableDefinition.Indices) { openTemporaryTable.cbKeyMost = indexDefinition.MaxKeyLength; openTemporaryTable.cbVarSegMac = 0; } // return the constructed JET_OPENTEMPORARYTABLE (whew!) return(openTemporaryTable); }