Using SQLite in a Metro style app

At the “Developing Windows 8 Metro style apps with C++” event that happened on 18-May-2012, we saw and heard some very interesting things.  If you were watching live then hopefully you didn’t see how I tried to work through my presentation while my disk was suspiciously guzzling every last byte until it eventually ran out of space!  But I digress…

During the keynote presentation by Herb Sutter, we brought up several customers that are well-known in the native code world to talk about their experiences with Metro style apps and C++/Cx.  In particular hopefully this one caught your eye:

That’s right, the team for SQLite was there to discuss how they were able to take their existing Win32 codebase and ensure that it worked well on Windows 8 as well as for Metro style apps.

SQLite is a in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is currently found in more applications than we can count, including several high-profile projects.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen().– Source: http://www.sqlite.org/about.html

Dr. Richard Hipp, the founder of SQLite, was on hand to announce the availability of the experimental branch they’ve been working on as well as that when the Release Preview of Windows 8 is made public that he will merge this code to the main trunk for SQLite, making it supported by them.  This is a really great thing for developers as SQLite has been a proven embedded database for numerous platforms and many, many customers.  The team prides themselves on testing and has millions of test cases that are validated each release.

As a Windows (and perhaps more specifically .NET) developer, you may not have had to build any lib from Open Source before of this type (i.e., native code) and since a binary is not being provided yet until Release Preview for Windows 8, I thought I’d share my tips on building the experimental bits, adding them to your projects and then using them with a client library.

UPDATE: I created a HOWTO video demonstrating the following steps of building and using from a C# Metro style app.

Building SQLite from source

If you are looking for the sqlite3.dll with this WinRT support anywhere on the sqlite.org site, you won’t find it.  You will have to build the source yourself. UPDATE:Since the origination of this post the SQLite team has released a version already compiled for 32/64-bit.  I highly recommend you get the code from the source rather than from any third party site.  Microsoft has worked with the team at SQLite to ensure compatibility and store certification.  For most .NET developers who have never grabbed native code source from an Open Source project and had to build it before, the maze of knowing what you should do can be confusing.  I’ve put together a cheat sheet on building SQLite from source for a Windows (and .NET developer) and put it on my SkyDrive: Building SQLite from source.  The OneNote I have has the details you need for the tools that will be required. 

In a nutshell you’ll need:

• Visual Studio (easiest way to get the C++ compiler)
• ActiveTcl
• Update for gawk.exe
• Fossil (the source control system used by SQLite)

Once you have these, you are ready to build SQLite.  Again, I’ll defer to my instructions on the details of setup.  Once your setup is complete, from a developer command prompt you’d run:

   1: nmake -f Makefile.msc sqlite3.dll FOR_WINRT=1

The result of this will give you basically 4 files that are of likely most importance to you: sqlite3.c, sqlite3.h, sqlite3.dll, sqlite3.pdb.

NOTE: The resulting pdb/dll that is built will be architecture specific.  If you used an x86 command prompt then that is what you have.  Be aware of this (as noted later in this post).

At a minimum you’ll want sqlite3.dll if you are a .NET developer, but as a native code developer you will likely be more interested in the others as well.  After this step, you now have a Windows Store compliant version of SQLite to include in your applications.

Runtime versus client access

Now at this point is where I’ve seen some confusion.  Folks are asking How do I include this, don’t I need a WinMD file to reference?  Let me diverge a bit and explain a few things.

The result of compiling the binary above produces primarily one thing…which I will call the “Engine” in this post.  This is the SQLite runtime and logic required to create/interact with SQLite database files.  This is NOT, however, an access library, which I will call the “Client” in this post.  If you are a managed code or JavaScript developer, at this point, all you have is the Engine, the database runtime.  You have no Client to access it.

Now, if you are a C++ developer you are probably okay at this point and don’t care much about what I have to say.  You have the header and are likely saying I’ve got the header, get out of my way.  And that is okay.  For C++ developers I think you’ll likely be accessing the database more directly through the SQLite APIs provided in the header.

I call out this distinction because this step provides you with the database engine you need to create a database and have it be store-compliant.  So if you are a JavaScript or .NET developer, what are you to do?  Stay tuned…let’s first get the Engine included in our app package.

Including SQLite in your app package

As I noted above, as a native code developer, having the header, lib and c file you may be okay and don’t care to read this.  I  personally think, however that I’d always just want the binary from my vendor rather than always include source in my files.  That said, the SQLite build process does product the amalgamation (sqlite3.c) you can just include in your native code app.

If you choose to go the binary file route (sqlite3.dll) then you need to simply follow a few principles to ensure that it is included in your package when you build your app/package.  These principles are simple:

• include the architecture-specific binary
• ensure the sqlite3.dll is marked as Content in your project
• ensure you note that you now have a native code dependency (not needed if you are already a C++ Metro style app)

These two items will ensure that when you build (even for debug via F5) or when you package for the store, that the Engine is included in your package.  Marking as content is simply ensuring that after you add the file to your project, ensure the file properties note that it is content.  In .NET apps this is making the Build Action property Content.  In JavaScript applications ensure the Package Action is marked Content.

Declaring the native code dependency means you simply add a reference to the Microsoft C++ Runtime Library via the Add Reference mechanisms in .NET and JavaScript applications.  By doing this (and again, this is a requirement of including SQLite in your app) you now cannot be architecture-neutral. This means no more AnyCPU for .NET.  When producing a package you’ll have to produce architecture-specific packages before uploading to the store.  Not to worry though as Visual Studio makes this incredibly easy.  The one thing you’ll have to remember though is that you’ll have to change the sqlite3.dll before building the packages as the DLL is architecture-specific itself.

Now this all should be easier right?  Wouldn’t it be nice if you could just Add Reference to the Engine?  I personally think so.  I’ll be working with the SQLite team to see if they will adopt this method to make it as easy as this:

In doing so, you as a developer would just add a reference to the Engine and then during build time Visual Studio (well MSBuild actually) will do all the right things in picking up the architecture-specific binary for your app.  No fiddling on your part.  This method also makes it easier for C++ developers as well as a props file would automatically be merged to include the .lib for linking and the header file for development.  This method uses the Visual Studio Extension SDK mechanism that was introduced in Visual Studio 11.

NOTE: Again note that as a managed (.NET) app I’d also have to ensure that my package includes the Microsoft C++ Runtime package in order for this to work and pass store certification.

Native code developers may scoff at this approach, but I could get started in 2 steps: Add Reference, #include.  No tweaking of my project files at all because the Extension SDK mechanism in VS does all this for me behind the scenes.

So why don’t I just give you the VSIX to install and accomplish the above?  Well simply, because SQLite is not my product and we’ve had a good relationship with their team and I want to make sure they understand the benefits of this method before jumping right in.  I hope that they will like it as I think it makes it *really* simple for developers.

Accessing the Engine from your app

Great, you’ve compiled the bits, you’ve understood how to ensure sqlite3.dll gets packaged in your application…now how do you use it!!!  Here’s the assessment of where we are at for Metro style apps as of the writing of this post.

C++ app developers: I think most C++ developers will get the header file (sqlite3.h) and be okay on their own with SQLite.  At this stage for them there doesn’t appear to be a real huge benefit of a WinRT wrapper to use the Engine.

.NET developers: I’ve messed around with a few libraries and believe the sqlite-net project to be the most favorable for what I believe most use cases will be for SQLite and Metro style apps.  This is a .NET-only library (not WinRT) but is basically a “LINQ to SQLite” approach.  The Mono team uses this one as well.  The necessary .NET 4.5 Core changes are already included in the project on github.  So you just need to get the SQLite.cs file and include it in your project.  Using this library allows you to write code like this:

   1: public sealed partial class BlankPage : Page

   2: {

   3:     public BlankPage()

   4:     {

   5:         this.InitializeComponent();

   6:  

   7:         string dbRootPath = Windows.Storage.ApplicationData.Current.LocalFolder.Path;

   8:         using (SQLiteConnection db = new SQLiteConnection(Path.Combine(dbRootPath, "mypeople.sqlite")))

   9:         {

  10:             db.CreateTable<Person>();

  11:  

  12:             db.RunInTransaction(() =>

  13:                 {

  14:                     for (int i = 0; i < 10; i++)

  15:                     {

  16:                         db.Insert(new Person() { FullName = "Person " + i.ToString() });

  17:                     }

  18:                 });

  19:         }

  20:     }

  21: }

  22:  

  23: public class Person

  24: {

  25:     [AutoIncrement, PrimaryKey]

  26:     public int ID { get; set; }

  27:     public string FullName { get; set; }

  28:     public string EmailAddress { get; set; }

  29:     public double Salary { get; set; }

  30: }

This is clearly just a sample, but demonstrates the simplicity of the library. 

NOTE: In the snippet above you do want to make sure you are creating your database in a path that is accessible from the AppContainer.  The best place is in the app’s ApplicationData location.  When specifying a path to SQLite in Open() for creation, give an explicit path always to ensure you aren’t relying on a temp file creation.

Some may ask about System.Data.Sqlite and this cannot be used because of the dependency of ADO.NET which is not a part of the .NET Framework Core profile.

Now this leads us to JavaScript developers.  Currently, there is not easy way for you to access this.  The Developer and Platform Evangelism team are working on some samples that are not quite complete yet.  JavaScript developers will need a WinRT library in order to access/create/query the Engine.  There are some out there (I haven’t played around with any of these) that would be good to see if they meet your needs.  Here are some pointers:

• sqlite-winrt
• SQLite3-WinRT

At the C++ event we talked with the SQLite team about a WinRT client library and will continue to talk with them to see if this is something of interest.  SQLite has a great history of working with the community and have a desire to continue this.  In the meantime, there are options for you to get started.  Also note, that since these are WinRT libraries they could also be used from C++ and .NET in Metro style apps.  At this point though it is my personal opinion that existing .NET libraries for .NET offer more value (i.e. LINQ) than how these WinRT ones exist.

Summary

This was a great announcement that the SQLite team made for Metro style app developers.  WinRT provides some existing local storage mechanisms which you should explore as well, however none that have structured storage with a query processor on top of it.  I’m really glad that the SQLite team was able to make a few diff’s to their code to accommodate a few store compliance areas and continue to offer their great product to this new class of applications.  It is very simple to get started by ensuring you have the Engine and picking your Client of your choice and write your app using SQLite for some local/structured storage!

Hope this helps and stay tuned for the release preview of Windows 8!