Test driving Microsoft.Database.Isam

Oct 2, 2014 at 2:47 PM
Hi, I'm new to esent so I might be doing something stupid but I can't figure what is wrong with the following code to not work.
            using (IsamInstance inst = new IsamInstance(@"c:\tmp\"))
            {
                using (var session = inst.CreateSession())
                {
                    session.CreateDatabase(@"c:\tmp\test4esent.db");
                    session.AttachDatabase(@"c:\tmp\test4esent.db");
                    Database db = session.OpenDatabase(@"c:\tmp\test4esent.db");
                    var tDef = new TableDefinition("test4table");
                    tDef.Columns.Add(new ColumnDefinition("col1")
                    {
                        Type = typeof(int),
                    });
                    tDef.Columns.Add(new ColumnDefinition("col2")
                    {
                        Type = typeof(int)
                    });
                    //var idx = new IndexDefinition("idx1");
                    //idx.KeyColumns.Add(new KeyColumn("col1", true));
                    //tDef.Indices.Add(idx);
                    db.CreateTable(tDef);

                    using (var cursor = db.OpenCursor("test4table"))
                    {
                        cursor.BeginEditForInsert();
                        cursor.EditRecord["col1"] = 1;//here it goes key not found when it tries to retrieve columndefinition from the db
                        cursor.EditRecord["col2"] = 2;// 
                        cursor.AcceptChanges();
                    }
                }
            }
Is it supposed to be used like this?
Oct 3, 2014 at 9:55 AM
I've found it doesn't return the correct tableName and columnname in the Api.JetGetColumnInfo when it goes through JetGetColumnInfoW (SupportsVistaFeatures == true).
Any idea why and how can I workaround this?
Developer
Oct 3, 2014 at 5:18 PM
Thanks for trying it out!
Which OS are you running on? Is it XP, or something newer?

BTW, thanks for the repro; I was pretty busy yesterday and didn't get to take a look.

-martin
Oct 6, 2014 at 6:55 AM
You're wellcome, I like it cause it hides the Api. verbosity.

It's Vista Ultimate (32bit) with sp2 and most of its updates. It's my development workstation and still works though it is a little old ;)

esent.dll file version 6.0.6002.18005 from 10.4.2009
Oct 6, 2014 at 1:31 PM
I have another question regarding reading records from db.
Is it supposed to be used like this?
But the use of cursor.MoveNext() after cursor.FindRecords() moves to the first record in the index and not the one "found" with FindRecords.
        public void Test4()
        {
            using (IsamInstance inst = new IsamInstance(@"c:\tmp\"))
            {
                using (var session = inst.CreateSession())
                {
                    if (!session.Exists(@"c:\tmp\test4esent.db"))
                    {
                        session.CreateDatabase(@"c:\tmp\test4esent.db");
                    }
                    session.AttachDatabase(@"c:\tmp\test4esent.db");
                    Database db = session.OpenDatabase(@"c:\tmp\test4esent.db");
                    if (!db.Exists("test4table"))
                    {
                        var tDef = new TableDefinition("test4table");
                        tDef.Columns.Add(new ColumnDefinition("col1")
                        {
                            Type = typeof(int)
                        });
                        tDef.Columns.Add(new ColumnDefinition("col2")
                        {
                            Type = typeof(int)
                        });
                        var idx = new IndexDefinition("primary");
                        idx.KeyColumns.Add(
                            new KeyColumn("col1", true) { });
                        tDef.Indices.Add(idx);
                        db.CreateTable(tDef);
                    }

                    using (var cursor = db.OpenCursor("test4table"))
                    {
                        for (int i = 0; i < 1000; i++)
                        {
                            cursor.BeginEditForInsert();
                            cursor.EditRecord["col1"] = i;
                            cursor.EditRecord["col2"] = i;
                            cursor.AcceptChanges();
                        }

                        cursor.SetCurrentIndex("primary");

                        for (int i = 0; i < 1000; i++)
                        {
                            var esKey = Key.Compose(i);
                            //cursor.MoveBeforeFirst();
                            cursor.FindRecords(MatchCriteria.EqualTo, esKey);//this one places itself before the seek found key, but...
                            // the call to .MoveNext() loses it and goes to the first record in the index(not the one filtered)
                            foreach (var item in cursor)
                            {
                                var rKey = item["col1"][0];
                                var rVal = item["col2"][0];
                                if (rKey.Equals(i) && rVal.Equals(i)) 
                                {
                                    Console.WriteLine("ok");
                                }
                            }
                        }
                    }                    
                }
            }
        }
Developer
Oct 6, 2014 at 11:51 PM
Thanks:
  1. (JetGetColumnInfoW) It works fine on Windows 8.1. I'll have to do further investigation on why it should fail on Vista.
  2. (cursor.FindRecords) I am looking at it. Thanks for the simple code.
I realize we don't have good sample code. We have internal testing code that is in a monolithic executable.. I am trying to break it up to be more legible, and hope to push it out. I don't have an ETA, though.

-martin
Oct 7, 2014 at 1:55 PM
Thanks for looking at it. Todays remark is:

this code performs less than expected:
                        for (int i = 0; i < elmtsNum; i++)
                        {
                            cursor.BeginEditForInsert();
                            cursor.EditRecord["col1"] = i;
                            cursor.EditRecord["col2"] = i;
                            cursor.AcceptChanges();
                        }
since ColumnAccessor goes for GetColumnDefinition every time?

Robert
Developer
Oct 14, 2014 at 12:52 AM
Phew, sorry about the late response. Last week was crazy-busy! Try caching the Columnid's outside the loop. That should avoid a bunch of (string -> columnid) lookups in the middle of the loop.
Columnid colidCol1 = cursor.TableDefinition.Columns["col1"].Columnid;
for (...)
{
     // ...
     cursor.EditRecord[colidCol1] = i;
}
-martin
Oct 14, 2014 at 7:12 AM
Edited Oct 15, 2014 at 7:16 AM
That made it better, thanks.
As for the FindRecords I've found it "resets" the index position when it goes into MoveBeforeFirst and after the Api.TryMovePrevious is on the correct position before the first found record. After the
                // we are initially outside of the current range
                this.outOfRange = true;
it looses its position. I don't know why though.
Oct 15, 2014 at 9:47 AM
Forget the last remark cause I think it is wrong the Cursor.Move(int rows). Shouldn't this part
                // setup our index range
                if (rows < 0)
                {
                    this.SetUpperLimit();
                }

                if (rows > 0)
                {
                    this.SetLowerLimit();
                }
be the opposite? When moving forward setting the upper limit and vice versa?
Nov 8, 2014 at 9:49 AM
Edited Nov 8, 2014 at 9:52 AM
Hello Martin

Great Work!!!

Regarding Arkej's initial problem, i have the same problem on Windows 7 (64Bit).

Furthermore i found out that maybe there seems to be a marshalling problem. The JetGetColumnInfoW with the NATIVE_COLUMNBASE_WIDE structure returns szBaseTableName as Ansi (which is treated as Unicode) and szBaseColumnName is an empty string.

Therfore at ...
cursor.EditRecord["col1"] = 1;//here it goes key not found
... an EsentRecordNotFoundException("The key was not found") is raised. Furthermore this problem only exists with Columns which are "user" created because JetGetColumnInfoW seems to work correctly whith system Tables. I also verified the correct name of the columns with ESEDatabaseView (http://www.nirsoft.net) in the database, so i think JetAddColumn() API works correctly.

In the meentime I changed the source of EsentInterop/JetApi.cs ...
public int JetGetColumnInfo(
                JET_SESID sesid,
                JET_DBID dbid,
                string tablename,
                JET_COLUMNID columnid,
                out JET_COLUMNBASE columnbase)
.. to always use the Ansi NativeMethods.JetGetColumnInfo() call, which seems to work pretty for me.

Maybe this information gives you a hint.

Kind regards
Tom
Developer
Nov 12, 2014 at 2:45 AM
Tom, thanks. Another fix is to change jetapi.cs:
        // Technically, this should have worked in Vista. But there was a bug, and
        // it was fixed after Windows 7.
        if (this.Capabilities.SupportsWindows8Features)
I'll be making this change.

Arkej, I haven't had a chance to investigate the FindRecords() change yet. (I've just bought a house, and that has eaten up all my spare time. :)

-martin
Nov 12, 2014 at 7:34 PM
Edited Nov 16, 2014 at 2:54 PM
Thank you, Martin!
It seems you have the insights ... ;-)

Meanwhile I have investigated another problem. There seems to be a bug in the case of empty strings ("" e. g. string.Empty) and empty byte arrays (byte[]).
Microsoft.Database.Isam does not really distinguish empty strings/byte arrays and null values. If you store such empty values you get back DBNull.Value. Furthermore if you have defined ColumnFlags.NonNull, you get an EsentNullInvalidException("Null not valid").
As the Microsoft.Isam.Esent.Interop.SetColumnHelpers.SetColumn overloads do in the case of string and byte[] datatypes Microsoft.Database.Isam.ColumnAccessor should implement a similar handling.
I have modified the ColumnAccessor. SetColumn() method the following way …
...
byte[] bytes = Converter.BytesFromObject(coltyp, isAscii, obj);
int bytesLength = bytes == null ? 0 : bytes.Length;

//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// in the case of zero length strings and byte[] arrays set SetColumnGrbit.ZeroLength
//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
if (bytes != null && bytes.Length == 0)
{
    grbitSet = grbitSet | SetColumnGrbit.ZeroLength;
}
//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Api.JetSetColumn(this.isamSession.Sesid, this.tableid, columnid, bytes, bytesLength, grbitSet, setinfo);

this.updateID++;
...
... which solves the problem.
Nov 13, 2014 at 2:41 PM
I have a probrem on FindRecords(), too.
Is there any walkarounds for temporary?
Nov 14, 2014 at 1:34 PM
Edited Nov 14, 2014 at 1:39 PM
Hallo

1. FindRecords()
I also have the FindRecords() Problem and as Arkej found out, it could be fixed by changing ...
                // setup our index range
                if (rows < 0)
                {
                    this.SetUpperLimit();
                }

                if (rows > 0)
                {
                    this.SetLowerLimit();
                }
... to ...
                // setup our index range
                if (rows < 0)
                {
                    this.SetLowerLimit();
                }

                if (rows > 0)
                {
                    this.SetUpperLimit();
                }
... of Move(int rows) function in Cursor.cs.

2. EditRecord Access after FindRecords()
Furthermore there is a minor Problem that it is possible to access the EditRecord, allthough the position of the cursor is out of range after a FindRecord() call. To fix the Problem I have to make some change here and there.

At first i added a private bool inserting field (like the exisiting updating, which keeps track if the cursor is in insert or update mode) which I only set at BeginEditForInsert() to know if the cursor is in insert mode and which is set to false, nearly everywhere where updating is set to false except in Delete() because the "updating = false" there is unnecessary, I think.
Than I changed Cursor.Checkrecord() to ...
        /// <summary>
        /// Checks the record.
        /// </summary>
        /// <param name="grbit">The optional grbit.</param>
        /// <exception cref="EsentNoCurrentRecordException">
        /// Thrown when the cursor is not on a record if not an insert of EditRecord.
        /// </exception>
        internal void CheckRecord(RetrieveColumnGrbit grbit = RetrieveColumnGrbit.None)
        {
            lock (this.isamSession)
            {
                if ((grbit & RetrieveColumnGrbit.RetrieveCopy) == 0 || ((grbit & RetrieveColumnGrbit.RetrieveCopy) != 0 & !inserting))
                {
                    if (this.outOfRange)
                    {
                        throw new EsentNoCurrentRecordException();
                    }
                }
            }
        }
At last I had to do the following changes in ColumnAccessor:

SizeOf(Columnid columnid, int index):
and
RetrieveColumn(JET_COLUMNID columnid, JET_coltyp coltyp, bool isAscii, int index)
                //if ((this.grbit & RetrieveColumnGrbit.RetrieveCopy) == 0)
                //{
                //    this.cursor.CheckRecord();
                //}
                this.cursor.CheckRecord(this.grbit);
SetColumn(JET_COLUMNID columnid, JET_coltyp coltyp, bool isAscii, int index, object obj)
                this.cursor.CheckRecord(this.grbit);
                if ((this.grbit & RetrieveColumnGrbit.RetrieveCopy) == 0)
                {
                    //this.cursor.CheckRecord();
                    throw new InvalidOperationException("You may only update fields through Cursor.EditRecord.");
                }
3. BeginEditForUpdate after FindRecords()
For consistency there should also be a CheckRecord() call in Cursor.BeginEditForUpdate() as in GetFields(), Position and Location. For now it is possible to issue BeginEditForUpdate() without an Exception after a FindRecord() call, allthough "out of range" at the "before first" position.

4. SystemParameters.AlternateDatabaseRecoveryPath
Martin, maybe ist is possible to add the AlternateDatabaseRecoveryPath to SystemParameters to allow to open a relocated (moved/copied) ESE database!?
        /// <summary>
        /// This parameter can be used to force crash recovery or a restore operation to look for the databases referenced in the transaction log in the specified folder. 
        /// </summary>
        public string AlternateDatabaseRecoveryPath
        {
            get
            {
                int ignored = 0;
                string val;
                Api.JetGetSystemParameter(this.instance.Inst, JET_SESID.Nil, Server2003Param.AlternateDatabaseRecoveryPath, ref ignored, out val, 1024);
                return val;
            }

            set
            {
                Api.JetSetSystemParameter(this.instance.Inst, JET_SESID.Nil, Server2003Param.AlternateDatabaseRecoveryPath, 0, value);
            }
        }
Kind Regards,
Tom
Developer
Nov 15, 2014 at 12:42 AM
Hi Tom. Lots of different topics here. It may get confusing. In the future, it may be easier if you open a new Discussion Topic for each topic. I don't want to drop anything unintentionally!
  1. I actually didn't write the first version of Isam. :) The person who did used DBNull instead of Nullable Types. I wonder if that would have been cleaner.
  2. Yes, that is correct!
  3. Are you saying that the problem is after every EditRecord after a FindRecord? Or only if it's at the end of the desired range? I'm trying to build up a suite of tests, and want to make sure your scenario is covered.
  4. I'll also have to add a test for that.
  5. We've done some restructuring with system parameters to try and keep Isam simple, and also share some common coding with PersistentDictionary. Both of them will use a new class called 'IsamSystemParameters'. Unfortunately, they still don't have AlternateDatabaseRecoveryPath...
    I'm debating whether this is a common enough scenario to justify adding yet another parameter to IsamSystemParameters. It's a balance between simplicity and control.
Thanks for the great feedback everyone!


-martin