August 2010

Volume 25 Number 08

The Working Programmer - Inside SQLite

By Ted Neward | August 2010

In keeping with the theme of this issue, it’s time to return to the roots of SQL and relational databases. Naturally, it would thus seem apropos to write something about SQL Server, something about its new feature set or performance improvements or whatnot, but that’s just not my style. Don’t get me wrong, SQL Server is a great database, and highly recommended for those “big iron” enterprise-class scenarios, but not every problem demands (as a friend once put it) a “big honkin’ centralized database.”

In fact, developers have long been using relational databases merely as a place to “put stuff for next time”—stuff such as configuration options, user settings, internationalization values and so on. Although it’s sometimes convenient to put these into a centralized SQL Server instance, in some cases, particularly in rich client scenarios (and especially Microsoft Silverlight or Windows Phone 7 rich client scenarios), maintaining a constant connection back to the SQL Server instance is infeasible at best, and often just flat-out impossible.

Developers don’t necessarily want to give up the power and flexibility of a relational database, but even SQL Server Express is sometimes too heavy an install. What’s to be done?

Go light, of course: SQLite, to be precise.

Introducing SQLite

As described by its Web site (sqlite.org), “SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.” The key elements in that statement revolve around the noun “library.” Unlike SQL Server, which uses a client-side assembly to send requests to a server for parsing and execution, SQLite lives entirely inside the client process, making it an “embedded” database. The running footprint of a SQLite database during use is a single file stored someplace on the client file system, and typically the install footprint is equally small.

For all that, the SQLite database is remarkably feature-rich, in that it supports the majority of the SQL-92 specification, minus a few things (described in more detail on the SQLite Web site) such as RIGHT and FULL OUTER JOIN, ALTER TABLE, some trigger support, GRANT/REVOKE and writing to VIEWs. What’s impressive is how much is supported, including transactions and a wide range of data types. Although it’s probably beyond credibility to expect that a SQL Server database schema will port to SQLite without modification, it’s reasonable to assume that a fairly straightforward (that is, not taking advantage of SQL Server-specific types or features) schema will port with minimal trouble. This makes SQLite ideal for scenarios where just a “lightweight SQL” is necessary.

In case there are concerns about its applicability or stability, SQLite is slowly making its way into a variety of “lightweight” environments—it already appears inside the Mozilla Firefox browser (for HTML 5 support), as well as the Symbian, iOS and Android environments, among others. In other words, this is how the “other half” of the development world (that is, non-­Microsoft-centric) does the lightweight database. SQLite continues to enjoy ongoing development and bug fixing, making it a pretty safe bet as a minimal SQL engine.

Of course, no database would be complete without some kind of administrator interface, and SQLite doesn’t disappoint. It has a command-line console tool for accessing and manipulating SQLite databases—but that may not impress your sysadmin all that much. Fortunately, the open source community has a number of SQLite tools available (a long list of them is on the SQLite Web site), but if you just need a quick Query Analyzer-like tool, try SQLite Administrator, a free tool available for download at sqliteadmin.orbmu2k.de.

Goin’ Native

SQLite was intended from the beginning to be a database for the native code developer, which is why it’s implemented as a native C/C++ DLL. This native flavor of SQLite is both a blessing and a curse: blessing, in that it cuts out a lot of the overhead (such as that of traversing the network to the server and back again) from the total time required to execute a given SQL statement; but curse in that, because the original SQLite database is a native C/C++ DLL, getting to it from a Microsoft .NET Framework-based application can be challenging.

Fortunately, the savvy .NET Framework developer realizes that accessing a native DLL is really just an exercise in P/Invoke declarations, and it’s relatively easy to create a wrapper class around the native declarations exposed in the SQLite DLL. In fact, for the basics, as with so many things in the open source community, it’s already been done; navigate to switchonthecode.com/tutorials/csharp-tutorial-writing-a-dotnet-wrapper-for-sqlite, and we find a working set of P/Invoke declarations already laid down, shown in Figure 1.

Figure 1 P/Invoke Declarations

namespace SQLiteWrapper
{
  public class SQLiteException : Exception
  {
    public SQLiteException(string message) :
      base(message)
      { }
  }
  public class SQLite
  {
    const int SQLITE_OK = 0;
    const int SQLITE_ROW = 100;
    const int SQLITE_DONE = 101;
    const int SQLITE_INTEGER = 1;
    const int SQLITE_FLOAT = 2;
    const int SQLITE_TEXT = 3;
    const int SQLITE_BLOB = 4;
    const int SQLITE_NULL = 5;
    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_open")]
      static extern int sqlite3_open(string filename, out IntPtr db);
    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_close")]
      static extern int sqlite3_close(IntPtr db);
    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_prepare_v2")]
      static extern int sqlite3_prepare_v2(IntPtr db, string zSql,
        int nByte, out IntPtr ppStmpt, IntPtr pzTail);
    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_step")]
      static extern int sqlite3_step(IntPtr stmHandle);
    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_finalize")]
      static extern int sqlite3_finalize(IntPtr stmHandle);
    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_errmsg")]
      static extern string sqlite3_errmsg(IntPtr db);
    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_count")]
      static extern int sqlite3_column_count(IntPtr stmHandle);
    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_origin_name")]
      static extern string sqlite3_column_origin_name(
        IntPtr stmHandle, int iCol);
    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_type")]
      static extern int sqlite3_column_type(IntPtr stmHandle, int iCol);
    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_int")]
      static extern int sqlite3_column_int(IntPtr stmHandle, int iCol);
    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_text")]
      static extern string sqlite3_column_text(IntPtr stmHandle, int iCol);
    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_double")]
      static extern double sqlite3_column_double(IntPtr stmHandle, int iCol);
  }
}

The high fidelity of P/Invoke to C/C++ APIs makes this a relatively simple process—the SQLite API uses a raw pointer to represent the database itself, which P/Invoke sees as a System.IntPtr, and every so often the SQLite API uses a pointer to an int as a parameter so it can modify the contents, described by P/Invoke with the C# “out” keyword. (For more on P/Invoke, see pinvoke.codeplex.com.)

I’ll refer you to the SQLite Web site for most of the details about how to use the SQLite API, but a quick glance at how to open a database, execute a query and then close the database would show you something like Figure 2.

Figure 2 Opening a Database, Executing a Query and Closing the Database

static void NativeMain()
 {
   // Open the database--db is our "handle" to it
   IntPtr db;
   if (SQLiteNative.sqlite3_open(@"cities.sqlite", out db) 
     == SQLiteNative.SQLITE_OK)
     {
       // Prepare a simple DDL "CREATE TABLE" statement
       string query = 
         "CREATE TABLE City " + 
         "(name TEXT, state TEXT, population INTEGER)";
       IntPtr stmHandle;
       if (SQLiteNative.sqlite3_prepare_v2(db, query, query.Length,
         out stmHandle, IntPtr.Zero) != SQLiteNative.SQLITE_OK)
       {
         // Something went wrong--find out what
         var err = SQLiteNative.sqlite3_errmsg(db);
       }
       if (SQLiteNative.sqlite3_step(stmHandle) != 
         SQLiteNative.SQLITE_DONE)
       {
         // Something went wrong--find out what
         var err = SQLiteNative.sqlite3_errmsg(db);
       }
       if (SQLiteNative.sqlite3_finalize(stmHandle) != 
         SQLiteNative.SQLITE_OK)
       {
         // Something went wrong--find out what
         var err = SQLiteNative.sqlite3_errmsg(db);
       }
     // ... Now that we've created a table, we can insert some
     // data, query it back and so on
     // Close the database back up
     SQLiteNative.sqlite3_close(db);
     }
  }

I’m Feelin’ Mighty Low

The most striking thing about this API is that it’s a bit on the low-level side. If you’re an old C++ hack like me, this may be a good thing, giving us a wonderful opportunity to reminisce about the Good Old Days, back when men were men, memory was managed by hand, and women swooned at cocktail parties over our scary stories of chasing down untamed pointers in the wilds of Windows 95 … but to the rest of these C# whipper-snappers, these Johnny-Come-Latelys who actually want to get productive work done, it’s a bit too low to the ground, so to speak. What’s needed is a good abstraction wrapper around that API to make it more manageable and cut down on the number of lines of code required to use it.

Wrapping this up into a single class isn’t that difficult, particularly because System.Data provides some good classes that can handle most of the user-API interaction. Showing the full details of that wrapper class, called SQLite, is a bit too lengthy to include here, but the declarations shown in Figure 3 give a pretty clear indication of how it’s supposed to be used.

Figure 3 Declarations of the SQLite Wrapper Class

public class SQLite : IDisposable
  {
    private IntPtr _db; //pointer to SQLite database
    private bool _open; //whether or not the database is open
    /// <summary>
    /// Opens or creates SQLite database with the specified path
    /// </summary>
    /// <param name="path">Path to SQLite database</param>
    public void OpenDatabase(string path);
    /// <summary>
    /// Closes the SQLite database
    /// </summary>
    public void CloseDatabase();
    /// <summary>
    /// Executes a query that returns no results
    /// </summary>
    /// <param name="query">SQL query to execute</param>
    public void ExecuteNonQuery(string query);
    /// <summary>
    /// Executes a query and stores the results in
    /// a DataTable
    /// </summary>
    /// <param name="query">SQL query to execute</param>
    /// <returns>DataTable of results</returns>
    public DataTable ExecuteQuery(string query);
  }

Using it, then, would look something like the example in Figure 4.

Figure 4 Using the SQLite Wrapper Class

static void NativeWrapperMain()
  {
    using (SQLite db = new SQLite("persons.sqlite"))
    {
      db.ExecuteNonQuery("CREATE Table Persons " +
        "(first TEXT, last TEXT, age INTEGER)");
      db.ExecuteNonQuery("INSERT INTO Persons (first, last, age) " +
        "VALUES ('Aaron', 'Erickson', 38)");
      db.ExecuteNonQuery("INSERT INTO Persons (first, last, age) " +
        "VALUES ('Rick', 'Minerich', 29)");
      db.ExecuteNonQuery("INSERT INTO Persons (first, last, age) " +
        "VALUES ('Talbott', 'Crowell', 35)");
      DataTable table = db.ExecuteQuery("SELECT * FROM Persons");
      foreach (DataRow row in table.Rows)
      {
        Console.WriteLine("{0} {1} {2}", row[0], row[1], row[2]);
      }
    }
  }

Clearly there are more operations that could be added to the SQLite wrapper class in Figure 4, but it’s already got the necessary barebones functionality, thanks in part to the wonderful database-agnostic nature of the core DataTable/DataRow/DataColumn classes in System.Data.

Abstractions, Abstractions

In some ways, the SQLite database’s advantage is its low-level design and implementation—being embeddable means the “friction” involved in using it is pretty light. Add the wrapper classes, make sure the SQLite DLL is somewhere accessible to the program (typically by putting it into the directory with the executable) and now you’re writing SQL statements like a champion. Assuming that’s what you want to do, of course.

But it’s likely that a significant majority of .NET Framework developers are either out of practice in managing a SQL database entirely “by hand” via console APIs, never knew how to do it or just want to leave that world behind. The modern .NET Framework environment provides such a wealth of tools for creating and managing relational schema that going back to this manual approach feels positively primitive and, more importantly, unproductive.

Furthermore, Microsoft has already spent effort creating an API that effectively describes most things a programmer wants to do against a relational database, and lots of those tools (LINQ to SQL, the Entity Framework and even the Visual Studio designer) are built on top of that API. I refer, of course, to ADO.NET and its provider model. Not having the ability to slide SQLite “underneath” ADO.NET means that all of that coolness is unavailable to the developer using SQLite, and that feels like a pretty significant shortcoming. The solution, then, is to build an ADO.NET provider for SQLite.

As we’ve already discovered, one of the nice things about the open source community is that there’s a really good chance that whatever you’re looking to do, somebody’s already done it, and this is no different. System.Data.SQLite, available for download at system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki, is a full ADO.NET 3.5 provider, meaning that everything a developer can do with a traditional client/server relational database provider is available to the SQLite developer, including all the Visual Studio designer support as well as LINQ and the Entity Framework.

Using System.Data.SQLite is pretty straightforward. Grab the download (either the source, so you can build it yourself and poke around in the code to see how everything works—this is a good example to work from to learn how to build an ADO.NET provider, if you’re curious—or grab just the binaries if you only want to get to “done” more quickly). Then drop the bits somewhere on your hard drive, reference System.Data.SQLite.dll from the project and life is good. Not surprisingly, the API classes live in System.Data.SQLite, and once they’re referenced, you can write good ol’ ADO.NET code against the database, as shown in Figure 5.

Figure 5 Using System.Data.SQLite

static void ManagedWrapperMain()
{
  var connStr = new SQLiteConnectionStringBuilder() 
    { DataSource = "persons.sqlite" };
  using (SQLiteConnection conn = new SQLiteConnection(connStr.ToString()))
  {
    conn.Open();
    SQLiteCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT COUNT(*) FROM Persons";
    var ct = cmd.ExecuteScalar();
    Console.WriteLine("Count = {0}", ct);
    cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM Persons";
    SQLiteDataReader reader = cmd.ExecuteReader();
    DataTable dt = new DataTable();
    dt.Load(reader);
    foreach (DataRow row in dt.Rows)
    {
      Console.WriteLine("{0} {1} {2}", row[0], row[1], row[2]);
    }
  }
}

So far, so good. When the code is run from a Visual Studio 2005 or 2008 project, everything works flawlessly. But when the code is executed from Visual Studio 2010, an error comes up, claiming “Unhandled Exception: System.IO.FileLoadException: Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.” A mixed-mode assembly, for those who haven’t heard the term before, is an assembly that contains both managed Microsoft Intermediate Language and native x86 assembly instructions. This, of course, is not good, on two levels—one, the obvious problem is we need to get the code to work, and two, if this is a mixed-mode assembly, it’s going to create some problems when using SQLite in other environments, such as ASP.NET.

The first problem is easily solved by adding an app.config file that tells the CLR 4.0 to load the mixed-mode assembly:

<?xml version="1.0"encoding="utf-8" ?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0"/>
  </startup>
</configuration>

A bigger problem is that a number of environments don’t support mixed-mode assemblies, and in any event, there’s a certain aesthetic involved here. For a variety of reasons, an all-managed solution would be preferable, but because the SQLite DLL is native code, that would be tricky. What would be nice is a port of the SQLite code base to C#, kept as close to the original C as possible.

All-Managed

Once again, the open source community provides when asked, and in this case, it provided a project called “C#-SQLite,” available at code.google.com/p/csharp-sqlite. It apparently started as “an exercise to learn the C# language” by porting the code over, and the associated wiki has some discussion of what the author did to manage the port, but the upshot is that we now have exactly what we needed: an all-managed version of SQLite.

Using it requires downloading the project sources, opening the project and kicking off a build. Like a number of open source projects, C#-SQLite consists of several projects, but each one is enclosed in its own solution file, so you may need to open more than one solution. (Or just kick off the builds from the command line with MSBuild—whatever works best.)

Once it’s built, add the C#-SQLite assembly (Community.C­­Sharp­SQLite) to the project, and for ADO.NET support, add the C#-SQLite Client assembly (Community.CsharpSqlite.SQLiteClient.dll) as well. Once again, the full capabilities of SQLite are available to us through an ADO.NET provider, such that we can rewrite almost the exact same code shown earlier (see Figure 6).

Figure 6 ;Using C#-SQLite

Static void AllManagedMain()
{
  SqliteConnection conn = 
    New SqliteConnection(@"Version=3,uri=file:persons.sqlite");
  conn.Open();
  try
  {
    SqliteCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT COUNT(*) FROM Persons";
    var ct = cmd.ExecuteScalar();
    Console.WriteLine("Count = {0}", ct);
    cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM Persons";
    SqliteDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
      Console.WriteLine("{0} {1} {2}", reader[0], reader[1], reader[2]);
    }
  }
  finally
  {
    conn.Close();
  }
}

Notice how the APIs are almost identical to the earlier mixed-mode version (only the class names have changed, and even then it’s really just a question of case: “SQLite” vs. “Sqlite” as a prefix, for example), but now we get all of the SQLite goodness without the potential security concerns (if any) of a native-mode DLL.

Limitations

Despite the wonderful nature of SQLite, it’s important to understand its limitations if the decision between using SQLite and SQL Server is to be made with any degree of sanity. SQLite is not going to provide all the features of SQL Server—far from it. The SQLite database doesn’t even want developers using it to use multiple threads, much less access it from multiple threads. In fact, it’s fair to say that if two programs want access to a SQLite database simultaneously, it’s probably time to upgrade to a SQL Server instance (Express or otherwise).

SQLite’s principal areas of “win” will be in many of the same areas that Access files used to occupy, with a near-complete SQL-92 syntax to back it, along with an ability to read database files used by other environments (Python, Perl and so on). Using it from Silverlight or phone clients is also a highly interesting area, particularly for local storage—sticking a SQLite database into Silverlight isolated storage would give developers a portable (in that it can travel with the Silverlight code) database in which to store local data, for example. Use it judiciously, and SQLite rounds out a relational database continuum of functionality-to-weight options.

Again, if there’s a particular topic you’d like to see explored, don’t hesitate to drop me a note. In a very real way, it’s your column, after all.

Happy coding!

Ted Neward  is a principal with Neward & Associates, an independent firm specializing in enterprise .NET Framework and Java platform systems. He has written more than 100 articles, is a C# MVP, INETA speaker and the author or coauthor of a dozen books, including the forthcoming “Professional F# 2.0” (Wrox). He consults and mentors regularly. Reach him at ted@tedneward.com and read his blog at blogs.tedneward.com.