public void VerifyXpThrowsExceptionOnJetOpenTemporaryTable() { var sesid = new JET_SESID(); var temporarytable = new JET_OPENTEMPORARYTABLE(); VistaApi.JetOpenTemporaryTable(sesid, temporarytable); }
public void JetOpentemporarytableToString() { var value = new JET_OPENTEMPORARYTABLE { grbit = TempTableGrbit.Indexed }; Assert.AreEqual("JET_OPENTEMPORARYTABLE(Indexed, 0 columns)", value.ToString()); }
// create a temp table and insert some records private void CreateTempTable() { Console.WriteLine("\tTemporary Table"); Api.JetBeginTransaction(this.sesid); var ci = new CultureInfo("en-us"); var tt = new JET_OPENTEMPORARYTABLE(); tt.prgcolumndef = new JET_COLUMNDEF[2]; tt.ccolumn = 2; tt.pidxunicode = new JET_UNICODEINDEX(); tt.pidxunicode.lcid = ci.LCID; tt.pidxunicode.dwMapFlags = Conversions.LCMapFlagsFromCompareOptions(CompareOptions.IgnoreCase); tt.grbit = TempTableGrbit.Indexed; tt.prgcolumndef[0] = new JET_COLUMNDEF { coltyp = JET_coltyp.Long, grbit = ColumndefGrbit.TTKey }; tt.prgcolumndef[1] = new JET_COLUMNDEF { coltyp = JET_coltyp.LongText, cp = JET_CP.Unicode, grbit = ColumndefGrbit.TTKey }; tt.prgcolumnid = new JET_COLUMNID[tt.prgcolumndef.Length]; VistaApi.JetOpenTemporaryTable(this.sesid, tt); JET_TABLEID tableid = tt.tableid; for (int i = 0; i <= 5; ++i) { int key = 5 - i; string s = string.Format("Record {0}", i); Api.JetPrepareUpdate(this.sesid, tableid, JET_prep.Insert); Api.SetColumn(this.sesid, tableid, tt.prgcolumnid[0], key); Api.SetColumn(this.sesid, tableid, tt.prgcolumnid[1], s, Encoding.Unicode); Api.JetUpdate(this.sesid, tableid); } int expectedKey = 0; Api.MoveBeforeFirst(this.sesid, tableid); while (Api.TryMoveNext(this.sesid, tableid)) { int actualKey = Api.RetrieveColumnAsInt32(this.sesid, tableid, tt.prgcolumnid[0]).Value; BasicClass.Assert( expectedKey == actualKey, string.Format("Temp table isn't sorted correctly (expected = {0}, actual = {1})", expectedKey, actualKey)); expectedKey++; } Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush); Api.JetCloseTable(this.sesid, tableid); }
public void Setup() { this.managed = new JET_OPENTEMPORARYTABLE() { ccolumn = 2, grbit = TempTableGrbit.SortNullsHigh, cbKeyMost = 3, cbVarSegMac = 4, }; this.native = this.managed.GetNativeOpenTemporaryTable(); }
public void SortDataDifferentLocalesWithJetOpenTemporaryTable2() { if (!EsentVersion.SupportsWindows8Features) { return; } CultureInfo cultureInfo = new CultureInfo("es-ES_tradnl"); string localeName = cultureInfo.CompareInfo.Name; Assert.AreEqual("es-ES_tradnl", localeName); var columns = new[] { new JET_COLUMNDEF { coltyp = JET_coltyp.Text, cp = JET_CP.Unicode, grbit = ColumndefGrbit.TTKey }, }; var columnids = new JET_COLUMNID[columns.Length]; var idxunicode = new JET_UNICODEINDEX { szLocaleName = localeName, }; var opentemporarytable = new JET_OPENTEMPORARYTABLE { cbKeyMost = SystemParameters.KeyMost, ccolumn = columns.Length, grbit = TempTableGrbit.Scrollable, pidxunicode = idxunicode, prgcolumndef = columns, prgcolumnid = columnids, }; Windows8Api.JetOpenTemporaryTable2(this.sesid, opentemporarytable); // Note that es-ES_tradnl sorts differently than English. var data = new[] { "canary", "cocoa", "chicken", "bad!" }; foreach (string s in data) { using (var update = new Update(this.sesid, opentemporarytable.tableid, JET_prep.Insert)) { Api.SetColumn(this.sesid, opentemporarytable.tableid, columnids[0], s, Encoding.Unicode); update.Save(); } } Array.Sort(data, new CultureInfo(localeName).CompareInfo.Compare); CollectionAssert.AreEqual(data, this.RetrieveAllRecordsAsString(opentemporarytable.tableid, columnids[0]).ToArray()); Api.JetCloseTable(this.sesid, opentemporarytable.tableid); }
public void SortDataCaseSensitiveWithJetOpenTemporaryTable3() { if (!EsentVersion.SupportsWindows8Features) { return; } const string LocaleName = "pt-BR"; var columns = new[] { new JET_COLUMNDEF { coltyp = JET_coltyp.Text, cp = JET_CP.Unicode, grbit = ColumndefGrbit.TTKey }, }; var columnids = new JET_COLUMNID[columns.Length]; var idxunicode = new JET_UNICODEINDEX { dwMapFlags = Conversions.LCMapFlagsFromCompareOptions(CompareOptions.None), szLocaleName = LocaleName, }; var opentemporarytable = new JET_OPENTEMPORARYTABLE { cbKeyMost = SystemParameters.KeyMost, ccolumn = columns.Length, grbit = TempTableGrbit.Scrollable, pidxunicode = idxunicode, prgcolumndef = columns, prgcolumnid = columnids, }; Windows8Api.JetOpenTemporaryTable2(this.session, opentemporarytable); var data = new[] { "g", "a", "A", "aa", "x", "b", "X" }; foreach (string s in data) { using (var update = new Update(this.session, opentemporarytable.tableid, JET_prep.Insert)) { Api.SetColumn(this.session, opentemporarytable.tableid, columnids[0], s, Encoding.Unicode); update.Save(); } } Array.Sort(data, new CultureInfo(LocaleName).CompareInfo.Compare); CollectionAssert.AreEqual( data, this.RetrieveAllRecordsAsString(opentemporarytable.tableid, columnids[0]).ToArray()); Api.JetCloseTable(this.session, opentemporarytable.tableid); }
/// <summary> /// Sort data with a temp table. /// </summary> /// <param name="sesid"> /// The session to use. /// </param> /// <param name="data"> /// The data to sort. /// </param> /// <returns> /// An enumeration of the sorted data. /// </returns> private static IEnumerable <int> SortWithTempTable(JET_SESID sesid, IEnumerable <int> data) { var ci = new CultureInfo("en-us"); var tt = new JET_OPENTEMPORARYTABLE { prgcolumndef = new[] { new JET_COLUMNDEF { coltyp = JET_coltyp.Long, grbit = ColumndefGrbit.TTKey, } }, ccolumn = 1, prgcolumnid = new JET_COLUMNID[1], pidxunicode = new JET_UNICODEINDEX { lcid = ci.LCID, dwMapFlags = Conversions.LCMapFlagsFromCompareOptions(CompareOptions.IgnoreCase), }, grbit = TempTableGrbit.None, }; VistaApi.JetOpenTemporaryTable(sesid, tt); foreach (int i in data) { Api.JetPrepareUpdate(sesid, tt.tableid, JET_prep.Insert); Api.SetColumn(sesid, tt.tableid, tt.prgcolumnid[0], i); Api.JetUpdate(sesid, tt.tableid); } try { Api.JetMove(sesid, tt.tableid, JET_Move.First, MoveGrbit.None); do { yield return(Api.RetrieveColumnAsInt32(sesid, tt.tableid, tt.prgcolumnid[0]).Value); }while (Api.TryMoveNext(sesid, tt.tableid)); } finally { Api.JetCloseTable(sesid, tt.tableid); } }
/// <summary> /// Creates a temporary table with a single index. A temporary table /// stores and retrieves records just like an ordinary table created /// using JetCreateTableColumnIndex. However, temporary tables are /// much faster than ordinary tables due to their volatile nature. /// They can also be used to very quickly sort and perform duplicate /// removal on record sets when accessed in a purely sequential manner. /// </summary> /// <param name="sesid">The session to use.</param> /// <param name="temporarytable"> /// Description of the temporary table to create on input. After a /// successful call, the structure contains the handle to the temporary /// table and column identifications. /// </param> /// <returns>An error code.</returns> public int JetOpenTemporaryTable2(JET_SESID sesid, JET_OPENTEMPORARYTABLE temporarytable) { TraceFunctionCall(); this.CheckSupportsWindows8Features("JetOpenTemporaryTable2"); CheckNotNull(temporarytable, "temporarytable"); NATIVE_OPENTEMPORARYTABLE2 nativetemporarytable = temporarytable.GetNativeOpenTemporaryTable2(); var nativecolumnids = new uint[nativetemporarytable.ccolumn]; NATIVE_COLUMNDEF[] nativecolumndefs = GetNativecolumndefs(temporarytable.prgcolumndef, temporarytable.ccolumn); unsafe { using (var gchandlecollection = new GCHandleCollection()) { // Pin memory nativetemporarytable.prgcolumndef = (NATIVE_COLUMNDEF *)gchandlecollection.Add(nativecolumndefs); nativetemporarytable.rgcolumnid = (uint *)gchandlecollection.Add(nativecolumnids); if (null != temporarytable.pidxunicode) { NATIVE_UNICODEINDEX2 unicode = temporarytable.pidxunicode.GetNativeUnicodeIndex2(); unicode.szLocaleName = gchandlecollection.Add(Util.ConvertToNullTerminatedUnicodeByteArray(temporarytable.pidxunicode.GetEffectiveLocaleName())); nativetemporarytable.pidxunicode = (NATIVE_UNICODEINDEX2 *)gchandlecollection.Add(unicode); } // Call the interop method int err = Err(NativeMethods.JetOpenTemporaryTable2(sesid.Value, ref nativetemporarytable)); // Convert the return values SetColumnids(temporarytable.prgcolumndef, temporarytable.prgcolumnid, nativecolumnids, temporarytable.ccolumn); temporarytable.tableid = new JET_TABLEID { Value = nativetemporarytable.tableid }; return(err); } } }
/// <summary> /// Creates a temp table with GUID column and tests .Net sort order. /// </summary> private void TestTempTableWithGuidDotNetSortOrder() { // check temp table logic EseInteropTestHelper.ConsoleWriteLine("Create temp table on GUID column."); var columns = new[] { new JET_COLUMNDEF { coltyp = VistaColtyp.GUID, cp = JET_CP.Unicode, grbit = ColumndefGrbit.TTKey }, }; var columnids = new JET_COLUMNID[columns.Length]; var idxunicode = new JET_UNICODEINDEX { dwMapFlags = Conversions.LCMapFlagsFromCompareOptions(CompareOptions.None), szLocaleName = "pt-br", }; var opentemporarytable = new JET_OPENTEMPORARYTABLE { cbKeyMost = SystemParameters.KeyMost, ccolumn = columns.Length, grbit = TempTableGrbit.Scrollable | Windows8Grbits.TTDotNetGuid, pidxunicode = idxunicode, prgcolumndef = columns, prgcolumnid = columnids, }; Windows8Api.JetOpenTemporaryTable2(this.sesId, opentemporarytable); Guid g = System.Guid.NewGuid(); EseInteropTestHelper.ConsoleWriteLine("Insert values in temp table."); for (int i = 0; i < 10000; i++) { if ((i % 2000) == 0) { EseInteropTestHelper.ConsoleWriteLine("Added another 2000 Guids."); } using (var update = new Update(this.sesId, opentemporarytable.tableid, JET_prep.Insert)) { Api.SetColumn(this.sesId, opentemporarytable.tableid, columnids[0], g); update.Save(); } g = System.Guid.NewGuid(); } EseInteropTestHelper.ConsoleWriteLine("Finished inserting values in temp table."); // validate order after having closed the database and restarted Api.JetMove(this.sesId, opentemporarytable.tableid, JET_Move.First, MoveGrbit.None); int bytesRead; byte[] data = new byte[16]; Api.JetRetrieveColumn(this.sesId, opentemporarytable.tableid, columnids[0], data, data.Length, out bytesRead, 0, null); Guid guidPrev = new System.Guid(data); EseInteropTestHelper.ConsoleWriteLine("Retrieved first value from temp table."); Guid guidCur; for (int i = 1; i < 10000; i++) { Api.JetMove(this.sesId, opentemporarytable.tableid, JET_Move.Next, MoveGrbit.None); Api.JetRetrieveColumn(this.sesId, opentemporarytable.tableid, columnids[0], data, data.Length, out bytesRead, 0, null); guidCur = new System.Guid(data); Assert.IsTrue(guidCur.CompareTo(guidPrev) > 0); guidPrev = guidCur; } EseInteropTestHelper.ConsoleWriteLine("Validated temp table order."); }
/// <summary> /// Creates a temporary table with a single index. A temporary table /// stores and retrieves records just like an ordinary table created /// using JetCreateTableColumnIndex. However, temporary tables are /// much faster than ordinary tables due to their volatile nature. /// They can also be used to very quickly sort and perform duplicate /// removal on record sets when accessed in a purely sequential manner. /// Also see /// <seealso cref="Api.JetOpenTempTable"/>, /// <seealso cref="Api.JetOpenTempTable3"/>. /// </summary> /// <remarks> /// Introduced in Windows Vista. Use <see cref="Api.JetOpenTempTable3"/> /// for earlier versions of Esent. /// </remarks> /// <param name="sesid">The session to use.</param> /// <param name="temporarytable"> /// Description of the temporary table to create on input. After a /// successful call, the structure contains the handle to the temporary /// table and column identifications. Use <see cref="Api.JetCloseTable"/> /// to free the temporary table when finished. /// </param> public static void JetOpenTemporaryTable(JET_SESID sesid, JET_OPENTEMPORARYTABLE temporarytable) { Api.Check(Api.Impl.JetOpenTemporaryTable(sesid, temporarytable)); }
/// <summary> /// Creates a temporary table with a single index. A temporary table /// stores and retrieves records just like an ordinary table created /// using JetCreateTableColumnIndex. However, temporary tables are /// much faster than ordinary tables due to their volatile nature. /// They can also be used to very quickly sort and perform duplicate /// removal on record sets when accessed in a purely sequential manner. /// Also see /// <seealso cref="Api.JetOpenTempTable"/>, /// <seealso cref="Api.JetOpenTempTable2"/>, /// <seealso cref="Api.JetOpenTempTable3"/>. /// </summary> /// <remarks> /// Introduced in Windows Vista. Use <see cref="Api.JetOpenTempTable3"/> /// for earlier versions of Esent. /// </remarks> /// <param name="sesid">The session to use.</param> /// <param name="temporarytable"> /// Description of the temporary table to create on input. After a /// successful call, the structure contains the handle to the temporary /// table and column identifications. Use <see cref="Api.JetCloseTable"/> /// to free the temporary table when finished. /// </param> public static void JetOpenTemporaryTable(JET_SESID sesid, JET_OPENTEMPORARYTABLE temporarytable) { Api.Check(Api.Impl.JetOpenTemporaryTable(sesid, temporarytable)); }
/// <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); }
/// <summary> /// Creates a <see cref="TableDefinition"/> object from a <see cref="JET_OPENTEMPORARYTABLE"/> /// object, suitable for caching. /// </summary> /// <param name="tableDefinition">The table definition.</param> /// <param name="openTemporaryTable">The open temporary table.</param> /// <returns>A <see cref="TableDefinition"/> object suitable for caching.</returns> private static TableDefinition MakeTableDefinitionToCache( TableDefinition tableDefinition, JET_OPENTEMPORARYTABLE openTemporaryTable) { // set the new table properties TableDefinition tableDefinitionToCache = new TableDefinition(tableDefinition.Name, tableDefinition.Type); // add the columns complete with the columnids generated when the // TT was created // // NOTE: this processing loop has to mirror the loop used to generate // the columndefs in MakeOpenTemporaryTable int currentColumndef = 0; foreach (IndexDefinition indexDefinition in tableDefinition.Indices) { foreach (KeyColumn keyColumn in indexDefinition.KeyColumns) { ColumnDefinition columnDefinition = tableDefinition.Columns[keyColumn.Name]; Columnid columnid = new Columnid( columnDefinition.Name, openTemporaryTable.prgcolumnid[currentColumndef], DatabaseCommon.ColtypFromColumnDefinition(columnDefinition), columnDefinition.IsAscii); ColumnDefinition columnDefinitionToCache = new ColumnDefinition(columnid); columnDefinitionToCache.Flags = columnDefinition.Flags; columnDefinitionToCache.MaxLength = columnDefinition.MaxLength; columnDefinitionToCache.ReadOnly = true; tableDefinitionToCache.Columns.Add(columnDefinitionToCache); 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) { Columnid columnid = new Columnid( columnDefinition.Name, openTemporaryTable.prgcolumnid[currentColumndef], DatabaseCommon.ColtypFromColumnDefinition(columnDefinition), columnDefinition.IsAscii); ColumnDefinition columnDefinitionToCache = new ColumnDefinition(columnid); columnDefinitionToCache.Flags = columnDefinition.Flags; columnDefinitionToCache.MaxLength = columnDefinition.MaxLength; columnDefinitionToCache.ReadOnly = true; tableDefinitionToCache.Columns.Add(columnDefinitionToCache); currentColumndef++; } } tableDefinitionToCache.Columns.ReadOnly = true; // add the indices foreach (IndexDefinition indexDefinition in tableDefinition.Indices) { IndexDefinition indexDefinitionToCache = new IndexDefinition(indexDefinition.Name); indexDefinitionToCache.Flags = indexDefinition.Flags; indexDefinitionToCache.Density = 100; indexDefinitionToCache.CultureInfo = indexDefinition.CultureInfo; indexDefinitionToCache.CompareOptions = indexDefinition.CompareOptions; indexDefinitionToCache.MaxKeyLength = indexDefinition.MaxKeyLength; foreach (KeyColumn keyColumn in indexDefinition.KeyColumns) { Columnid columnid = tableDefinitionToCache.Columns[keyColumn.Name].Columnid; KeyColumn keyColumnToCache = new KeyColumn(columnid, keyColumn.IsAscending); indexDefinitionToCache.KeyColumns.Add(keyColumnToCache); } indexDefinitionToCache.KeyColumns.ReadOnly = true; indexDefinitionToCache.ReadOnly = true; tableDefinitionToCache.Indices.Add(indexDefinitionToCache); } tableDefinitionToCache.Indices.ReadOnly = true; // return the table definition return(tableDefinitionToCache); }
/// <summary> /// Creates a single table with the specified definition in the database /// </summary> /// <param name="tableDefinition">The table definition.</param> /// <exception cref="EsentTableDuplicateException"> /// Thrown when the table definition overlaps with an already existing table. /// </exception> /// <exception cref="System.ArgumentException">A MaxKeyLength > 255 is not supported for indices over a temporary table on this version of the database engine.;tableDefinition</exception> public override void CreateTable(TableDefinition tableDefinition) { lock (this.IsamSession) { this.CheckDisposed(); // validate the table definition for creating a TT this.ValidateTableDefinition(tableDefinition); // convert the given table definition into an JET_OPENTEMPORARYTABLE // struct that we will use to create the TT JET_OPENTEMPORARYTABLE openTemporaryTable = this.MakeOpenTemporaryTable(tableDefinition); // check if the TT already exists if (this.Exists(tableDefinition.Name)) { throw new EsentTableDuplicateException(); } // do not allow the TT to be created if the session is in a // transaction. we disallow this to sidestep the problem where // JET will automatically close (and destroy) the TT if the // current level of the transaction is aborted if (this.IsamSession.TransactionLevel > 0) { // NOTE: i'm thinking that this requirement is pretty lame, // especially since it only hits us on an abort. I am going // to allow this for now and see what happens // throw new ArgumentException( "We do not currently allow you to create temp tables while inside of a transaction." ); } // create the TT JET_TABLEID tableid = new JET_TABLEID(); if (DatabaseCommon.CheckEngineVersion( this.IsamSession, DatabaseCommon.ESENTVersion(6, 0, 6000, 0), DatabaseCommon.ESEVersion(8, 0, 685, 0))) { VistaApi.JetOpenTemporaryTable(this.IsamSession.Sesid, openTemporaryTable); tableid = openTemporaryTable.tableid; } else { if (openTemporaryTable.cbKeyMost > 255) { throw new ArgumentException("A MaxKeyLength > 255 is not supported for indices over a temporary table on this version of the database engine.", "tableDefinition"); } Api.JetOpenTempTable2( this.IsamSession.Sesid, openTemporaryTable.prgcolumndef, openTemporaryTable.prgcolumndef.Length, openTemporaryTable.pidxunicode.lcid, openTemporaryTable.grbit, out tableid, openTemporaryTable.prgcolumnid); } // re-create the TT's schema to reflect the created TT TableDefinition tableDefinitionToCache = MakeTableDefinitionToCache(tableDefinition, openTemporaryTable); // cache the TT and its handle TempTableHandle tempTableHandle = new TempTableHandle( tableDefinitionToCache.Name, this.IsamSession.Sesid, tableid, tableDefinitionToCache.Type == TableType.Sort || tableDefinitionToCache.Type == TableType.PreSortTemporary); this.Tables.Add(tableDefinitionToCache); this.TempTableHandles.Add(tempTableHandle); this.IsamSession.IsamInstance.TempTableHandles.Add(tempTableHandle); } }
/// <summary> /// Run the test. /// </summary> public void Run() { JET_SESID sesid; Api.JetBeginSession(this.instance, out sesid, null, null); Console.WriteLine("Temporary table tests"); Api.JetBeginTransaction(sesid); var ci = new CultureInfo("en-us"); var tt = new JET_OPENTEMPORARYTABLE { prgcolumndef = (from coltyp in this.coltyps select new JET_COLUMNDEF { coltyp = coltyp, cp = JET_CP.Unicode }). ToArray(), pidxunicode = new JET_UNICODEINDEX { lcid = ci.LCID, dwMapFlags = Conversions.LCMapFlagsFromCompareOptions(CompareOptions.IgnoreCase) }, grbit = TempTableGrbit.Indexed, }; tt.ccolumn = tt.prgcolumndef.Length; tt.prgcolumnid = new JET_COLUMNID[tt.prgcolumndef.Length]; tt.prgcolumndef[0].grbit = ColumndefGrbit.TTKey; tt.prgcolumndef[1].grbit = ColumndefGrbit.TTKey | ColumndefGrbit.TTDescending; tt.prgcolumndef[2].grbit = ColumndefGrbit.TTKey; tt.prgcolumndef[3].grbit = ColumndefGrbit.TTKey | ColumndefGrbit.TTDescending; VistaApi.JetOpenTemporaryTable(sesid, tt); int numrecords = 500; var rand = new Random(); Stopwatch stopwatch = Stopwatch.StartNew(); foreach (int i in Randomize(Enumerable.Range(0, numrecords))) { using (var update = new Update(sesid, tt.tableid, JET_prep.Insert)) { for (int j = 0; j < tt.prgcolumndef.Length; ++j) { Api.SetColumn( sesid, tt.tableid, tt.prgcolumnid[j], DataGenerator.GetRandomColumnData(tt.prgcolumndef[j].coltyp, tt.prgcolumndef[j].cp, rand)); } // overwrite the first column, which is an integer key. this will be used to validate // the sorting of the objects Api.SetColumn(sesid, tt.tableid, tt.prgcolumnid[0], BitConverter.GetBytes(i)); update.Save(); } } stopwatch.Stop(); Console.WriteLine("\tInserted {0} records in {1}", numrecords, stopwatch.Elapsed); // iterate over the table to force materialization stopwatch = Stopwatch.StartNew(); BasicClass.Assert( Enumerable.Range(0, numrecords).SequenceEqual(GetColumns(sesid, tt.tableid, tt.prgcolumnid[0])), "Didn't get expected keys"); stopwatch.Stop(); Console.WriteLine("\tRetrieved {0} records in {1}", numrecords, stopwatch.Elapsed); numrecords = 10000; stopwatch = Stopwatch.StartNew(); IEnumerable <int> sortedData = Enumerable.Range(0, numrecords); BasicClass.Assert( sortedData.SequenceEqual(SortWithTempTable(sesid, Randomize(sortedData))), "Data isn't sorted"); stopwatch.Stop(); Console.WriteLine("\tSorted {0} numbers in {1}", numrecords, stopwatch.Elapsed); Console.WriteLine("\tSeeking"); SeekWithTempTable(sesid); Api.JetCommitTransaction(sesid, CommitTransactionGrbit.LazyFlush); Api.JetCloseTable(sesid, tt.tableid); Api.JetEndSession(sesid, EndSessionGrbit.None); }
/// <summary> /// The seek with temp table. /// </summary> /// <param name="sesid"> /// The sesid. /// </param> private static void SeekWithTempTable(JET_SESID sesid) { var tt = new JET_OPENTEMPORARYTABLE { ccolumn = 1, prgcolumndef = new[] { new JET_COLUMNDEF { coltyp = JET_coltyp.Long, grbit = ColumndefGrbit.TTKey } }, prgcolumnid = new JET_COLUMNID[1], grbit = TempTableGrbit.Indexed, }; VistaApi.JetOpenTemporaryTable(sesid, tt); // Insert records 0, 10, 20, 30, ... 90); foreach (int i in Enumerable.Range(0, 10)) { Api.JetPrepareUpdate(sesid, tt.tableid, JET_prep.Insert); Api.SetColumn(sesid, tt.tableid, tt.prgcolumnid[0], i * 10); Api.JetUpdate(sesid, tt.tableid); } try { // Boundary: before start of table ////VerifySeekFails(sesid, tt.tableid, -1, SeekGrbit.SeekLT); ////VerifySeekFails(sesid, tt.tableid, -1, SeekGrbit.SeekLE); VerifySeekFails(sesid, tt.tableid, -1, SeekGrbit.SeekEQ); VerifySeekFindRecord(sesid, tt.tableid, -1, SeekGrbit.SeekGE, tt.prgcolumnid[0], 0); VerifySeekFindRecord(sesid, tt.tableid, -1, SeekGrbit.SeekGT, tt.prgcolumnid[0], 0); // Boundary: at start of table VerifySeekFails(sesid, tt.tableid, 0, SeekGrbit.SeekLT); VerifySeekFindRecord(sesid, tt.tableid, 0, SeekGrbit.SeekLE, tt.prgcolumnid[0], 0); VerifySeekFindRecord(sesid, tt.tableid, 0, SeekGrbit.SeekEQ, tt.prgcolumnid[0], 0); ////VerifySeekFindRecord(sesid, tt.tableid, 0, SeekGrbit.SeekGE, tt.prgcolumnid[0], 0); VerifySeekFindRecord(sesid, tt.tableid, 0, SeekGrbit.SeekGT, tt.prgcolumnid[0], 10); // Normal case: middle of table, key exists VerifySeekFindRecord(sesid, tt.tableid, 50, SeekGrbit.SeekLT, tt.prgcolumnid[0], 40); VerifySeekFindRecord(sesid, tt.tableid, 50, SeekGrbit.SeekLE, tt.prgcolumnid[0], 50); VerifySeekFindRecord(sesid, tt.tableid, 50, SeekGrbit.SeekEQ, tt.prgcolumnid[0], 50); ////VerifySeekFindRecord(sesid, tt.tableid, 50, SeekGrbit.SeekGE, tt.prgcolumnid[0], 50); VerifySeekFindRecord(sesid, tt.tableid, 50, SeekGrbit.SeekGT, tt.prgcolumnid[0], 60); // Normal case: middle of table, key doesn't exist ////VerifySeekFindRecord(sesid, tt.tableid, 75, SeekGrbit.SeekLT, tt.prgcolumnid[0], 70); ////VerifySeekFindRecord(sesid, tt.tableid, 75, SeekGrbit.SeekLE, tt.prgcolumnid[0], 70); VerifySeekFails(sesid, tt.tableid, 75, SeekGrbit.SeekEQ); VerifySeekFindRecord(sesid, tt.tableid, 75, SeekGrbit.SeekGE, tt.prgcolumnid[0], 80); VerifySeekFindRecord(sesid, tt.tableid, 75, SeekGrbit.SeekGT, tt.prgcolumnid[0], 80); // Boundary: at end of table VerifySeekFindRecord(sesid, tt.tableid, 90, SeekGrbit.SeekLT, tt.prgcolumnid[0], 80); VerifySeekFindRecord(sesid, tt.tableid, 90, SeekGrbit.SeekLE, tt.prgcolumnid[0], 90); VerifySeekFindRecord(sesid, tt.tableid, 90, SeekGrbit.SeekEQ, tt.prgcolumnid[0], 90); ////VerifySeekFindRecord(sesid, tt.tableid, 90, SeekGrbit.SeekGE, tt.prgcolumnid[0], 90); VerifySeekFails(sesid, tt.tableid, 90, SeekGrbit.SeekGT); // Boundary: past end of table ////VerifySeekFindRecord(sesid, tt.tableid, 99, SeekGrbit.SeekLT, tt.prgcolumnid[0], 90); ////VerifySeekFindRecord(sesid, tt.tableid, 99, SeekGrbit.SeekLE, tt.prgcolumnid[0], 90); VerifySeekFails(sesid, tt.tableid, 99, SeekGrbit.SeekEQ); VerifySeekFails(sesid, tt.tableid, 99, SeekGrbit.SeekGE); VerifySeekFails(sesid, tt.tableid, 99, SeekGrbit.SeekGT); } finally { Api.JetCloseTable(sesid, tt.tableid); } }