Notes on using SQLite database resources for .Net, Java, Mac OS X

Using Sqlite in .Net and Java

Notes on using the open source Sqlite database on Windows, Mac, and with Java. By Tim Anderson.

Using Sqlite in .Net and Java

Sqlite is a small C database library. I like it because it is lightweight, fast, cross-platform, and supports a very decent subset of SQL. It reminds me in some ways of Codebase, another excellent database library that runs everywhere. The problem with Codebase is that it is at heart Xbase and not SQL. You can use SQL via its ODBC driver, but frankly ODBC should not be necessary for an embedded database. So if you use the Codebase API and then later want to upscale to SQL Server or Oracle or DB2, everything will need to be redone. A SQL API is more portable, despite the dreaded implementation differences. Sqlite strikes me as nearly ideal for applications that need fast and robust access to local data.

I decided to investigate the feasibility of using Sqlite as an alternative to Microsoft's JET (the Access database engine) in a .Net application. I was also interested in the possibility of porting the application to Java in order to run on Mac OS X and Linux, the two main "other" desktop platforms. What follows is a few notes on how I've got on so far. I've also included some downloads for two things I found tricky - using the Mono Sqlite data provider with Microsoft.Net, and using the Java Sqlite wrapper on the Mac.

Note that while there's no doubting the qualiity of the Sqlite database library itself (kudos to D. Richard Hipp. the main author), the same does not apply to all the wrappers out there. These range from finished code to various states of pre-alpha, alpha and beta. Some will no doubt never be finished. Fortunately many of the wrappers are themselves very small, so there is not too much to go wrong and you should be able to fix problems that arise.

Sqlite in Java

When it comes to Java, there is only one that I know of, and it is here: http://www.ch-werner.de/javaSqlite/. It's a good project, and works exactly as expected on Linux:

./configure
./make
./make install

Now, one of the problems with Sqlite wrappers is that many of them either include or modify the Sqlite code itself. This makes them forks, albeit usually of a very minor kind, and you have the inherent fork problem of what to do when the main branch is updated. Christian Werner's configure script allows for this nicely. You simply put the latest (or your preferred) Sqlite source in a subdirectory of his source, or specify the location of the source with an argument, and his JNI project will use that source. Obviously it may fail if the Sqlite API changes, but so far all is good.

The first problem I had came about when building the JNI library on Windows. Windows doesn't have GNU autoconf or automake, so Christian Werner supplies a makefile for Visual C++. This failed at first, because a few source files have been added to Sqlite since the wrapper was last updated, so I added them in. This worked but I was puzzled to fnd that various functions were disabled although I knew they worked in the Sqlite source i was using (2.8.9). Because of this, even the test.java supplied with the project failed. The reason is that the supplied Windows makefile is ultra-cautious about what is enabled. I revised it to enable the following defines:

HAVE_Sqlite_TRACE
HAVE_Sqlite_COMPILE
HAVE_Sqlite_SET_AUTHORIZER
HAVE_Sqlite_FUNCTION_TYPE

After that it worked fine and all was well, on Windows at least.

Sqlite for Java on Mac OS X

Mac OS X was a different story again. The Mac does use gcc and has autoconf, but clearly the author had not anticipated Mac usage. The configure script actually fell over with an error, but I found I could get it to run by specifying the location of the Java SDK and the Sqlite source. The resultant makefile still wouldn't build. I found out that some had reported problems with dynamic libraries on Mac OS X, so I rebuilt both Sqlite and the JNI wrapper with --disable-shared. I now had a library but it didn't work. I looked up Apple's notes on JNI, and found the library had to have an extension of .jnilib. It still did not work. I looked again at Apple's notes and found that I had to create a bundle. I modified the makefile to do this as an additional step. Joy - a working JNI wrapper on Mac OS X. I've put my build up for download; it works on OS X Panther and hopefully on other versions as well. (Note: the Panther build uses Sqlite version 2.8.11. I've also added a build for Sqlite 2.8.16 and 3.2.7 combined, for OS X 10.4).

.Net and the Mono Sqlite provider

My next adventure was with the .Net provider that comes with Mono. There are a number of .Net providers out there, but if you download them you'll find that all the Windows versions are written with managed C++, rather than C# or VB.Net. The Mono client on the other hand is pure C#. I prefer this, and I also like the fact that it will load a standard Sqlite dynamic library at runtime rather than compiling in a particular set of slightly modified Sqlite source files, as is the case with several of the other .Net providers. However, although the Mono Sqlite client works fine with Mono on Linux (I've not tried Mono on Windows), it does not work with Microsoft .Net.

The main reason for this is that C# does not let you declare a delegate that uses the Cdecl as opposed to Stdcall calling convention. Thus you either have to modify or extend the Sqlite library (which is bad), or use Managed C++. However, it turns out that you can overcome this C# limitation. What you have to do is to disassemble the compiled DLL to IL with ILDASM, modify it to add the correct attribute, and re-assemble it with ILASM. Not much fun if you are doing frequent builds, but fortunately it can be automated. Even better, there is a Perl script you can use. So you can automate this with Make or else simply run the script after building. I got this interesting information from a newsgroup posting by Microsoft's Jan Kotas. You can find it on Google; search for "Callback function using C calling convention". I wasn't quite home and dry. I also found it necessary to change the declaration of the callback function:

internal unsafe int SqliteCallback (IntPtr o, int argc, sbyte **argv, sbyte **colnames)

I also made the callback delegate a class variable rather than giving it local scope, to discourage the garbage collector from disposing of it before Sqlite has finished with it. It works fine now (so far), and I've provided a binary build so that anyone can try it.

Avoiding the callback

I found it interesting that you can overcome this limation of C# and use a Cdecl delegate, but it turns out that you don't have to. Sqlite provides a non-callback interface that you can use instead. It requires a small change to a few lines of code in the Mono .Net provider - and I thank Marcellino Tanumihardja for sending these to me. It makes more sense to do this that to mess around hacking the compiled IL, fun though it is.

Using the Mono data provider

Please note that this is a very basic provider. You can excecute SQL statements and return results through the ExecuteReader method. However there's no support for the DataAdapter yet, so the provider won't work with the VS.Net wizards. I'm hoping to add a few features soon, and will also make the revised source available for download.

Migrating from Access

Another little task concerns migrating data from Access to Sqlite. It turns out that there is a freeware tool out there that has a wizard for this, DBTools DB Manager. This will both generate the database structure and copy the data. Very useful, but I found it extraordinarily slow so I looked for another solution.

Access doesn't have any direct way to generate an SQL script defining a database, but there are some indirect ways.I used the SQL Server upsizing wizard, generated an SQL script from SQL Server, and modified this to work with Sqlite. Not too difficult. That gets you the structure, and the next task is to populate it. I now have a utility written in Delphi that does this; it's not ready to share yet but I may be able to do so at some point. It uses the limited but simple and fast Delphi wrapper by Pablo Pissanetzky.

Unlike my Access MDB, I can use the resultant Sqlite database on all sorts of platforms. Mono on Linux; Java on Windows, Linux and the Mac, and Delphi or C++ on Windows without worrying about MDAC, Microsoft's complex set of data access components.

When I was fighting with JNI on the Mac, I wondered if I should use something like Hsqldb, an excellent 100% Java database library. That's an OK approach, but you are then cutting yourself off from native code applications. It's a trade-off, because JNI is inconvenient, but I like being able to use Java, but not having to use it.