Reading via a non-unique index

Jun 16, 2013 at 10:15 AM
Hello everyone, I've just returned to ESENT coding after almost 2 years absence. There is a fundamental technique that I've forgotten how to code: how to efficiently read all records with a specific value in a non-unique index.

For a classic example, if I have a non-unique index over a person's surname in a table, what is the correct way of retrieving all rows with value 'Smith'? The code must of course deal with the case of no matching records.

Cheers, Greg K
Jun 19, 2013 at 2:27 AM
After searching back through old code I eventually found the trick I was looking for. It goes like this:
Api.SetCurrentIndex(session, table, "SurnameIndex");
Api.MakeKey(session, table, "Smith", MakeKeyGrbit.NewKey);
if (Api.TrySeek(session, table, SeekGrbit.SeekEQ | SeekGrbit.SetIndexRange))
{
    do
    {
        // retrieve from the rows with "Smith"
    }
    whle (Api.TryMoveNext(session, table));
}
The subtle change is the addition of the SetIndexRange bit. This limits movement though the index to those matching "Smith". Upon returning to this sort of code after so long I find that working with indexes correctly is really tricky as there are so many combinations of bits and calls that must be done EXACTLY right, and it's easy to forget how. I'd love to see a little "cookbook" somewhere of snippets on how to do various common things. Perhaps there is one? If not I'll make one when I get spare time.

Cheers, Greg K
Jun 19, 2013 at 3:08 AM
Hi Greg,
I'm glad you've found the solution.
Also, I'm sorry I haven't responded to your initial post.

However, I'd like to note it's more complex than that.
Please take a look at the following source file in my "ESENT serialize" library
http://esentserialize.codeplex.com/SourceControl/latest#EsentSerialization/Esent/RecordsetFilters.cs
and look at the implementation of the "SearchFilterEqual.iRecordsetFilter.ApplyFilter()" method.

As you see, it's a little more complex than your code:

JetSetCurrentIndex
MakeKey
TrySeek(SeekGE) // this moves the cursor to the first matching record
MakeKey
TrySetIndexRange(RangeUpperLimit | SetIndexRangeGrbit.RangeInclusive) // this limits the cursor to the last mathing record
Jun 23, 2013 at 9:16 AM
Hi Const_me, thanks for the reply as it took me back in time. I remember a few years ago when I first discovered the ManagedEsent project I was browsing around the web and I found your project where Attribute annotated classes are stored in an ESE database, which I thought was an interesting and helpful idea. Over the years though, I forgot about your project, and if you have a look here you'll see how my memory lapse caused me to accidentally reproduce some of your functionality in my own project in a slightly different way:

http://www.orthogonal.com.au/computers/simpledb

Due to personal needs I created a "simple database" library which stores arbitrary objects and their properties. Unlike your idea of using attributes, I just "shred" incoming objects apart and store the properties as strings via TypeConverters. One of my backing storage implementations uses ManagedEsent, and as a result inherits all of the incredible power of the esent.dll engine.

Cheers, Greg

P.S. I plan to experiment with the various API indexing flags over the coming weeks of hobby time to be sure how they are all supposed to be used. If I find anything interesting I'll post it here.
Coordinator
Jul 22, 2013 at 8:40 PM
Greg, as far as 'cookbook' samples go, you should try looking through the code for the unit tests. There is a file called HowDoI.cs with some common examples. There are also samples for most of the other APIs and grbits. I hope that helps.

-martin