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