// retrieve the key column with JET_bitRetrieveFromPrimaryBookmark
private void RetrieveFromPrimaryBookmark()
{
Console.WriteLine("\tRetrieveFromPrimaryBookmark");
JET_TABLEID tableid;
Api.JetOpenTable(this.sesid, this.dbid, this.table, null, 0, OpenTableGrbit.None, out tableid);
Api.JetBeginTransaction2(this.sesid, BeginTransactionGrbit.ReadOnly);
int expectedRecordID = 7;
this.SeekRecord(this.sesid, tableid, expectedRecordID);
JET_COLUMNID columnid =
(from column in this.columnInfos
where column.Name == "recordID"
select column.Columnid).Single();
byte[] columndata = Api.RetrieveColumn(
this.sesid, tableid, columnid, RetrieveColumnGrbit.RetrieveFromPrimaryBookmark, null);
int actualRecordID = BitConverter.ToInt32(columndata, 0);
BasicClass.Assert(
expectedRecordID == actualRecordID,
string.Format("RetrieveFromPrimaryBookmark: got {0}, expected {1}", actualRecordID, expectedRecordID));
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
Api.JetCloseTable(this.sesid, tableid);
}
// verifies that the data in the record matches the expected values
private void CheckColumns(JET_SESID sesid, JET_TABLEID tableid, byte[][] expectedData)
{
byte[][] actualData;
actualData = this.GetColumnsWithJetRetrieveColumn(sesid, tableid);
this.CompareColumns(actualData, expectedData);
actualData = this.GetColumnsWithJetRetrieveColumns(sesid, tableid);
this.CompareColumns(actualData, expectedData);
int totalsize = 0;
foreach (var columndata in actualData)
{
if (columndata != null)
{
totalsize += columndata.Length;
}
}
// BUGBUG: need to investigate why this gives a value which is larger than the sum of the column sizes
var recsize = new JET_RECSIZE();
VistaApi.JetGetRecordSize(sesid, tableid, ref recsize, GetRecordSizeGrbit.None);
BasicClass.Assert(
recsize.cbData + recsize.cbLongValueData >= totalsize,
string.Format("JetGetRecordSize returned {0} bytes, expected {1}", recsize.cbData + recsize.cbLongValueData, totalsize));
}
// verifies the set of records created by Run()
public void VerifyRecords()
{
Console.WriteLine("\tVerifying records");
Api.JetBeginTransaction2(this.sesid, BeginTransactionGrbit.ReadOnly);
JET_TABLEID tableid;
Api.JetOpenTable(this.sesid, this.dbid, this.table, null, 0, OpenTableGrbit.None, out tableid);
Api.JetSetTableSequential(this.sesid, tableid, SetTableSequentialGrbit.None);
Api.JetMove(this.sesid, tableid, JET_Move.First, MoveGrbit.None);
for (int recordID = 1; recordID <= NumRecords; ++recordID)
{
byte[][] data = this.GenerateColumnData(recordID, recordID + 100);
this.CheckColumns(this.sesid, tableid, data);
try
{
Api.JetMove(this.sesid, tableid, JET_Move.Next, MoveGrbit.None);
data = this.GetColumnsWithJetRetrieveColumn(this.sesid, tableid);
BasicClass.Assert(
NumRecords != recordID, "The last move should generate an EsentNoCurrentRecordException");
}
catch (EsentNoCurrentRecordException)
{
BasicClass.Assert(
NumRecords == recordID,
"Only the last move should generate an EsentNoCurrentRecordException");
}
}
Api.JetCloseTable(this.sesid, tableid);
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.WaitLastLevel0Commit);
}
/// <summary>
/// Seek for a key and make sure we don't find a record.
/// </summary>
/// <param name="sesid">The session to use.</param>
/// <param name="tableid">The table to seek on.</param>
/// <param name="key">The key to seek for.</param>
/// <param name="seekOption">The seek option.</param>
private static void VerifySeekFails(JET_SESID sesid, JET_TABLEID tableid, int key, SeekGrbit seekOption)
{
Console.WriteLine("\t\tSeek for {0} with {1}, expecting failure", key, seekOption);
Api.MakeKey(sesid, tableid, key, MakeKeyGrbit.NewKey);
bool result = Api.TrySeek(sesid, tableid, seekOption);
BasicClass.Assert(!result, "Found the record. Expected not found.");
}
// 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);
}
/// <summary>
/// Seek for a key and make sure we land on the expected record.
/// </summary>
/// <param name="sesid">The session to use.</param>
/// <param name="tableid">The table to seek on.</param>
/// <param name="key">The key to seek for.</param>
/// <param name="seekOption">The seek option.</param>
/// <param name="columnid">The columnid of the data to retrieve.</param>
/// <param name="expected">The expected data.</param>
private static void VerifySeekFindRecord(
JET_SESID sesid, JET_TABLEID tableid, int key, SeekGrbit seekOption, JET_COLUMNID columnid, int expected)
{
Console.WriteLine("\t\tSeek for {0} with {1}, expecting {2}", key, seekOption, expected);
Api.MakeKey(sesid, tableid, key, MakeKeyGrbit.NewKey);
Api.JetSeek(sesid, tableid, seekOption);
int actual = Api.RetrieveColumnAsInt32(sesid, tableid, columnid).Value;
BasicClass.Assert(expected == actual, String.Format("Expected {0}, got {1}. Seek is broken", expected, actual));
}
// replaces records [1..numRecords] with seed = recordID+100
private void Replace()
{
Console.WriteLine("\tReplace");
JET_TABLEID tableid;
Api.JetOpenTable(this.sesid, this.dbid, this.table, null, 0, OpenTableGrbit.None, out tableid);
Api.JetBeginTransaction(this.sesid);
for (int i = NumRecords; i > 0; --i)
{
byte[][] data = this.GenerateColumnData(i, 100 + i);
this.SeekRecord(this.sesid, tableid, i);
byte[][] originaldata = this.GetColumnsWithJetRetrieveColumn(this.sesid, tableid);
Api.JetPrepareUpdate(this.sesid, tableid, JET_prep.Replace);
this.SetColumns(this.sesid, tableid, data);
// the copy buffer should contain the new data
byte[][] actualdata = this.GetColumnsWithJetRetrieveColumn(
this.sesid, tableid, RetrieveColumnGrbit.RetrieveCopy);
this.CompareColumns(data, actualdata);
// we haven't called update yet so this should be the original data
actualdata = this.GetColumnsWithJetRetrieveColumn(this.sesid, tableid);
this.CompareColumns(originaldata, actualdata);
Api.JetUpdate(this.sesid, tableid);
}
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
Api.JetBeginTransaction2(this.sesid, BeginTransactionGrbit.ReadOnly);
Api.JetMove(this.sesid, tableid, JET_Move.Last, MoveGrbit.None);
for (int i = NumRecords; i > 0; --i)
{
byte[][] data = this.GenerateColumnData(i, 100 + i);
this.CheckColumns(this.sesid, tableid, data);
try
{
Api.JetMove(this.sesid, tableid, JET_Move.Previous, MoveGrbit.None);
BasicClass.Assert(1 != i, "The last move should generate an EsentNoCurrentRecordException");
}
catch (EsentNoCurrentRecordException)
{
BasicClass.Assert(1 == i, "Only the last move should generate an EsentNoCurrentRecordException");
}
}
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
Api.JetCloseTable(this.sesid, tableid);
}
private void SeekSecondaryIndex(JET_SESID sesid, JET_TABLEID tableid)
{
byte[][] data = this.GetColumnsWithJetRetrieveColumn(sesid, tableid);
byte[] bookmark = Api.GetBookmark(sesid, tableid);
for (int i = 0; i < this.columnInfos.Length; ++i)
{
string index = string.Format("index_{0}", this.columnInfos[i].Name);
Api.JetSetCurrentIndex(sesid, tableid, index);
string actualindex;
Api.JetGetCurrentIndex(sesid, tableid, out actualindex, SystemParameters.NameMost);
BasicClass.Assert(
string.Equals(actualindex, index, StringComparison.InvariantCultureIgnoreCase),
string.Format("Set index to {0}, JetGetCurrentIndex returns {1}", index, actualindex));
// create an index range that will contain the record we want
Api.MakeKey(sesid, tableid, data[i], MakeKeyGrbit.NewKey);
Api.JetSeek(sesid, tableid, SeekGrbit.SeekEQ);
Api.MakeKey(sesid, tableid, data[i], MakeKeyGrbit.StrLimit | MakeKeyGrbit.NewKey);
Api.JetSetIndexRange(sesid, tableid, SetIndexRangeGrbit.RangeUpperLimit);
// now move through the range until we find the record we want
bool foundrecord = false;
do
{
// verify we have the same column value. don't retrieve the entire column as we
// have to deal with key truncation
byte[] columndata = Api.RetrieveColumn(
sesid, tableid, this.columnInfos[i].Columnid, RetrieveColumnGrbit.RetrieveFromIndex, null);
BasicClass.Assert(
this.CompareByteArray(columndata, data[i], null == columndata ? 0 : columndata.Length),
"Unexpected column value. Did we go past the end of the index range?");
if (this.CompareByteArray(bookmark, Api.GetBookmark(sesid, tableid), bookmark.Length))
{
BasicClass.Assert(!foundrecord, "Found the record twice");
foundrecord = true;
}
}while (Api.TryMoveNext(sesid, tableid));
BasicClass.Assert(foundrecord, "Didn't find the record in the secondary index");
}
}
// insert numRecords records with recordID = [1..numRecords] and seed = recordID
private void Insert()
{
Console.WriteLine("\tInsert");
JET_TABLEID tableid;
Api.JetOpenTable(this.sesid, this.dbid, this.table, null, 0, OpenTableGrbit.None, out tableid);
// note that the recordIDs are 1-10 NOT 0-9
Api.JetBeginTransaction(this.sesid);
for (int recordID = 1; recordID <= NumRecords; ++recordID)
{
byte[][] data = this.GenerateColumnData(recordID, recordID);
Api.JetPrepareUpdate(this.sesid, tableid, JET_prep.Insert);
this.SetColumns(this.sesid, tableid, data);
Api.JetUpdate(this.sesid, tableid);
}
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
Api.JetBeginTransaction2(this.sesid, BeginTransactionGrbit.ReadOnly);
Api.JetMove(this.sesid, tableid, JET_Move.First, MoveGrbit.None);
for (int i = 1; i <= NumRecords; ++i)
{
byte[][] data = this.GenerateColumnData(i, i);
this.CheckColumns(this.sesid, tableid, data);
try
{
Api.JetMove(this.sesid, tableid, JET_Move.Next, MoveGrbit.None);
BasicClass.Assert(NumRecords != i, "The last move should generate an EsentNoCurrentRecordException");
}
catch (EsentNoCurrentRecordException)
{
BasicClass.Assert(
NumRecords == i, "Only the last move should generate an EsentNoCurrentRecordException");
}
}
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
Api.JetCloseTable(this.sesid, tableid);
}
private void JetCreateTable()
{
const string TableName = "tabletodelete";
Console.WriteLine("\tJetCreateTable()");
Api.JetBeginTransaction(this.sesid);
JET_TABLEID tableid;
BasicClass.Assert(
!Api.TryOpenTable(this.sesid, this.dbid, TableName, OpenTableGrbit.None, out tableid),
"Able to open non-existent table");
Api.JetCreateTable(this.sesid, this.dbid, TableName, 1, 100, out tableid);
var columndef = new JET_COLUMNDEF();
// Add a column and an index
columndef.coltyp = JET_coltyp.LongBinary;
JET_COLUMNID columnid;
Api.JetAddColumn(this.sesid, tableid, "column", columndef, null, 0, out columnid);
Api.JetCreateIndex(this.sesid, tableid, "primary", CreateIndexGrbit.IndexPrimary, "+column\0\0", 9, 100);
Api.JetCreateIndex(this.sesid, tableid, "secondary", CreateIndexGrbit.None, "-column\0\0", 9, 100);
// Insert a record to force creation of the LV tree
Api.JetPrepareUpdate(this.sesid, tableid, JET_prep.Insert);
Api.JetSetColumn(
this.sesid, tableid, columndef.columnid, new byte[2000], 2000, SetColumnGrbit.IntrinsicLV, null);
Api.JetUpdate(this.sesid, tableid);
Api.JetCloseTable(this.sesid, tableid);
BasicClass.Assert(
Api.TryOpenTable(this.sesid, this.dbid, TableName, OpenTableGrbit.None, out tableid),
"Unable to open table");
Api.JetCloseTable(this.sesid, tableid);
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
}
/// <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);
}
// insert a record and update its long-values
private void UpdateLongValues()
{
Console.WriteLine("\tUpdate Long-Values");
JET_TABLEID tableid;
Api.JetOpenTable(this.sesid, this.dbid, this.table, null, 0, OpenTableGrbit.None, out tableid);
int recordID = NumRecords + 17;
var rand = new Random(recordID);
var data = new byte[this.columnInfos.Length][];
Api.JetBeginTransaction(this.sesid);
// insert the record
using (var update = new Update(this.sesid, tableid, JET_prep.Insert))
{
for (int i = 0; i < this.columnInfos.Length; ++i)
{
data[i] = null;
if (string.Equals(
this.columnInfos[i].Name, "recordID", StringComparison.InvariantCultureIgnoreCase))
{
// this is the primary index column, set it to the recordID
data[i] = BitConverter.GetBytes(recordID);
}
else if (this.columnInfos[i].Coltyp == JET_coltyp.LongBinary ||
this.columnInfos[i].Coltyp == JET_coltyp.LongText)
{
data[i] = DataGenerator.GetRandomColumnData(
this.columnInfos[i].Coltyp, this.columnInfos[i].Cp, rand);
}
if (null != data[i])
{
Api.SetColumn(this.sesid, tableid, this.columnInfos[i].Columnid, data[i]);
}
}
update.SaveAndGotoBookmark();
}
this.CheckColumns(this.sesid, tableid, data);
// update the record
using (var update = new Update(this.sesid, tableid, JET_prep.Replace))
{
for (int i = 0; i < this.columnInfos.Length; ++i)
{
if (this.columnInfos[i].Coltyp == JET_coltyp.LongBinary ||
this.columnInfos[i].Coltyp == JET_coltyp.LongText)
{
int size = Api.RetrieveColumnSize(this.sesid, tableid, this.columnInfos[i].Columnid).Value;
BasicClass.Assert(size == data[i].Length, "Invalid column size");
var setinfo = new JET_SETINFO();
setinfo.ibLongValue = size / 2;
setinfo.itagSequence = 1;
// the data that will be added to the column
byte[] newdata = DataGenerator.GetRandomColumnData(
this.columnInfos[i].Coltyp, this.columnInfos[i].Cp, rand);
// what the final data should be
byte[] finaldata = null;
switch (rand.Next(2))
{
case 0: // append
Api.SetColumn(
this.sesid, tableid, this.columnInfos[i].Columnid, newdata, SetColumnGrbit.AppendLV);
finaldata = new byte[size + newdata.Length];
Array.Copy(data[i], finaldata, size);
Array.Copy(newdata, 0, finaldata, size, newdata.Length);
break;
case 1: // overwrite and set size
Api.JetSetColumn(
this.sesid,
tableid,
this.columnInfos[i].Columnid,
newdata,
newdata.Length,
SetColumnGrbit.SizeLV | SetColumnGrbit.OverwriteLV,
setinfo);
finaldata = new byte[setinfo.ibLongValue + newdata.Length];
Array.Copy(data[i], finaldata, setinfo.ibLongValue);
Array.Copy(newdata, 0, finaldata, setinfo.ibLongValue, newdata.Length);
break;
}
data[i] = finaldata;
}
}
update.SaveAndGotoBookmark();
}
this.CheckColumns(this.sesid, tableid, data);
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
Api.JetCloseTable(this.sesid, tableid);
}