How to Get Berkeley DB SQL API into the .NET System.Data.SQLite Provider

My last post covered getting Berkeley DB up and running with .NET. Now it’s time to take it one step further and build the open source System.Data.SQLite ADO.NET library, replacing the SQLite 3 engine with Oracle’s version of the SQLite.Interop C library that gets embedded into the .NET System.Data.SQLite assembly.

In other words, when you complete the steps below, you’ll have a System.Data.SQLite library that can, supposedly, drop into your .NET projects that currently use the ADO.NET library found on the sqlite.org site. The real difference is that instead of SQLite with single threaded writes, or file locking for writes rather, you will be using the latest Berkeley DB storage engine which supports page level locking to allow more writers, assuming the writers are writing to different pages.

For more information on Oracle’s implementation of the SQLite API which they call the Berkeley DB SQL API and to learn where they differ, you should read the whitepaper: Oracle Berkeley DB SQL API vs. SQLite API.

  1. Download the Berkeley DB dbsql-adodotnet-5.2.28.zip source.
  2. Unlock the zip file (Right-click and select Properties and click the Unlock button.)
  3. Extract the contents to an empty directory of your choice.
  4. Open the SQLite.NET.2010.sln solution file.
  5. Choose Release and then build.
  6. Optionally show all files in the System.Data.SQLite.2010 project and open the Assembly.cs file and change the AssemblyProduct string to something like “System.Data.SQLite-BDB” so that it will show up in file Properties Details tab. This is the only way you will know that this assembly has the Berkeley DB interop engine built into rather than the SQLite engine.

If you’re curious, do a diff between the SQLite.Interop project files in the Oracle version and the original sqlite.org version. Clearly very different animals. Now the only thing that is left is to write up a nice little test in C# to compare the two libraries. The subject of a future post.

UPDATE #1 (9/5/2011): Tests today show that this “new” System.SQLite.Data library DOES NOT create a Berkeley DB database. At least as far as I can tell. So while there is a libdb_sql50.dll in the main Berkeley DB build that implements the sqlite3.dll API, there is no way that I can find so far to use that library from C#. More experimentation to come.

UPDATE #2 (9/5/2011): Modify the UnsafeNativeMethods.cs with line 34 as

private const string SQLITE_DLL = “libdb_sql52.dll”;

and then change the solution’s projects conditional compilation symbols to “SQLITE_STANDARD” and then build. This prevents the SQLite interop embedded DLL from being used. Now when you run your test app, you’ll need to copy to the bin directory of your test app the new built DLLs (see previous post):

libdb_sql52.dll
libdb_stl52.dll
libdb52.dll

I’ll post more on my tests later. So far, I’ve got the basics working but when I push the limits, I get some nasty crashes, so I’m skeptical of this scheme to use the SQLite API over the Berkeley DB engine.

How to Get Berkeley DB 5.2.28 and .NET Examples Working in Visual Studio 2010 SP1

Over the years, I’ve looked into using Berkeley DB for my own C# applications because it has a solid reputation as a very fast, reliable key/value database. Every time I’ve walked away disappointed with the .NET API wrapper—until now. My interest was renewed a few days ago when I noticed an item on StackOverflow comparing SQLite performance to Berkeley DB.  Since acquiring Berkeley DB, Oracle has been busy making it better, including adding better support for the .NET development community.

WARNING: read and understand the license terms and conditions for Berkeley DB before you choose to use it.

I began this most recent review by downloading the MSI Windows installer. DO NOT DO THIS! When I tried to compile the VS2010 solutions (see below), I got all kinds of errors. Next I tried downloading the 45MB zip file. This worked like a charm, except the .NET examples projects had a broken reference which was easily fixed. Follow these steps and you’ll be up and running your .NET app using Berkeley DB in no time.

  1. Download db-5.2.28.zip file.
  2. Right click the zip file and select Properties and click the "Unlock" button. This will unlock and make usable all the files in the zip file for your local machine. DO NOT SKIP this step.
  3. Extract the contents of the zip file to a directory, e.g. C:\Code so that you will have a C:\Code\db-5.2.28 folder.
  4. In that db-5.2.28 folder, you will find a build_windows folder. This will be your home for the next steps. Be sure to follow them in order.
  5. Open Berkeley_DB_vs2010.sln
    1. build debug win32 and x64
    2. build release win32 and x64
  6. Open Berkeley_DB_examples_vs2010.sln
    1. build debug win32 and x64
    2. build release win32 and x64
  7. Open BDB_dotNet_vs2010.sln (allow default conversion)
    1. build debug win32 and x64
    2. build release win32 and x64
  8. Open BDB_dotNet_examples_vs2010.sln
    1. In each project, delete the missing reference to "db_dotnet" and add reference to ..\AnyCPU\Release\libdb_dotnet52.dll
    2. build debug win32 and x64
    3. build release win32 and x64

That’s it. Now you can run the example projects and you have a .NET library and x86 (Win32) and x64 binary engines for that library to use. Enjoy!

Why I Would Work for Eric Sink

As I sit here nursing a head plagued with a migraine, I read with great interest Eric Sink’s latest in my RSS reader about his experiences learning Scrum, a paper submitted for the proceedings of Agile 2011. Here’s my favorite part:

“I have come to think of our daily standup as being similar to a security guard at a bank. Most security guards stand around for their entire career without ever firing their weapon. It's probably a boring job. But the consistent presence of that security guard probably prevents some big problems from ever happening. Our daily standup is the same way.  Nothing exciting ever really happens. But we can confidently assume that many big problems have been avoided because we regularly take the time to get synced up.

“The culture of Scrum teams seems to be built on working together in shared spaces. In contrast, our company has always placed a high value on each person having a private office.

“We are aware that there are tradeoffs here. A private office gives each person a quiet place to work, but it also creates the opportunity for people to get isolated. So even as we provide private offices, we create ways to drag people out of them, including soda in the kitchen, lunch together on Wednesdays, a pool table, and a video game room.”

For this I would consider relocation to Illinois. And I always tell recruiters I’m not interested in relocating.