An F# newbie using SQLite

Like I said in a tweet on Friday, I'm guessing everybody's first 10,000 lines of F# are crap. That's a lot of bad code I need to write, so I figure maybe I better get started.

This blog entry is a journal of my first attempts at using F# to do some SQLite stuff. I'm using SQLitePCL.raw, which is a Portable Class Library wrapper (written in C#) allowing .NET developers to call the native SQLite library.

My program has five "stanzas":

  • ONE: Open a SQLite database and create a table with two integer columns called a and b.

  • TWO: Insert 16 rows with a going from 1 through 16.

  • THREE: Set column b equal to a squared, and lookup the value of b for a=7.

  • FOUR: Loop over all the rows where b<120 and sum the a values.

  • FIVE: Close the database and print the two results.

I've got three implementations of this program to show you -- one in C# and two in F#.


Here's the C# version I started with:

using System;
using System.IO;

using SQLitePCL;

public class foo
    // helper functions to check SQLite result codes and throw

    private static bool is_error(int rc)
        return (
                (rc != raw.SQLITE_OK)
                && (rc != raw.SQLITE_ROW)
                && (rc != raw.SQLITE_DONE)

    private static void check(int rc)
        if (is_error(rc))
            throw new Exception(string.Format("{0}", rc));

    private static void check(sqlite3 conn, int rc)
        if (is_error(rc))
            throw new Exception(raw.sqlite3_errmsg(conn));

    private static int checkthru(sqlite3 conn, int rc)
        if (is_error(rc))
            throw new Exception(raw.sqlite3_errmsg(conn));
            return rc;

    // MAIN program

    public static void Main()
        sqlite3 conn = null;

        // ONE: open the db and create the table

        check(raw.sqlite3_open(":memory:", out conn));

        check(conn, raw.sqlite3_exec(conn, "CREATE TABLE foo (a int, b int)"));

        // TWO: insert 16 rows

        for (int i=1; i<=16; i++)
            string sql = string.Format("INSERT INTO foo (a) VALUES ({0})", i);
            check(conn, raw.sqlite3_exec(conn, sql));

        // THREE: set b = a squared and find b for a=7

        check(conn, raw.sqlite3_exec(conn, "UPDATE foo SET b = a * a"));

        sqlite3_stmt stmt = null;
        check(conn, raw.sqlite3_prepare_v2(conn, "SELECT b FROM foo WHERE a=?", out stmt));

        check(conn, raw.sqlite3_bind_int(stmt, 1, 7));
        check(conn, raw.sqlite3_step(stmt));
        int vsq = raw.sqlite3_column_int(stmt, 0);
        check(conn, raw.sqlite3_finalize(stmt));
        stmt = null;

        // FOUR: fetch sum(a) for all rows where b < 120

        check(conn, raw.sqlite3_prepare_v2(conn, "SELECT a FROM foo WHERE b<120", out stmt));

        int vsum = 0;

        while (raw.SQLITE_ROW == (checkthru(conn, raw.sqlite3_step(stmt))))
            vsum += raw.sqlite3_column_int(stmt, 0);
        check(conn, raw.sqlite3_finalize(stmt));
        stmt = null;

        // FIVE: close and print the results

        conn = null;

        Console.WriteLine("val: {0}", vsq);
        Console.WriteLine("sum: {0}", vsum);


  • I'm coding against the 'raw' SQLite API, which returns integer error codes rather than throwing exceptions. So I've written some little check functions which throw on any result code that signifies an error condition.

  • In the first stanza, I'm opening ":memory:" rather than an actual file on disk so that I can be sure the db starts clean.

  • In the second stanza, I'm constructing the SQL string rather than using parameter substitution. This is a bad idea for two reasons. First, parameter substitution eliminates SQL injection attacks. Second, forcing SQLite to compile a SQL statement inside a loop is going to cause poor performance.

  • In the third stanza, I'm going out of my way to do this more properly, using prepare/bind/step/finalize. Ironically, this is the case where it doesn't matter as much, since I'm not looping.

  • In the fourth stanza, I specifically want to loop over the rows in C# even though I could easily just do the sum in SQL.

F#, first attempt

OK, now here's a painfully direct translation of this code to F#:

open SQLitePCL

// helper functions to check SQLite result codes and throw

let is_error rc = 
    (rc <> raw.SQLITE_OK) 
    && (rc <> raw.SQLITE_ROW) 
    && (rc <> raw.SQLITE_DONE)

let check1 rc = 
    if (is_error rc) 
    then failwith (sprintf "%d" rc) 
    else ()

let check2 conn rc = 
    if (is_error rc) 
    then failwith (raw.sqlite3_errmsg(conn)) 
    else ()

let checkthru conn rc = 
    if (is_error rc) 
    then failwith (raw.sqlite3_errmsg(conn)) 
    else rc

// MAIN program

// ONE: open the db and create the table

let (rc,conn) = raw.sqlite3_open(":memory:") 
check1 rc

check2 conn (raw.sqlite3_exec (conn, "CREATE TABLE foo (a int, b int)"))

// TWO: insert 16 rows

for i = 1 to 16 do 
    let sql = (sprintf "INSERT INTO foo (a) VALUES (%d)" i)
    check2 conn (raw.sqlite3_exec (conn, sql ))

// THREE: set b = a squared and find b for a=7

check2 conn (raw.sqlite3_exec (conn, "UPDATE foo SET b = a * a"))

let rc2,stmt = raw.sqlite3_prepare_v2(conn, "SELECT b FROM foo WHERE a=?")
check2 conn rc2

check2 conn (raw.sqlite3_bind_int(stmt, 1, 7))
check2 conn (raw.sqlite3_step(stmt))
let vsq = raw.sqlite3_column_int(stmt, 0)
check2 conn (raw.sqlite3_finalize(stmt))

// FOUR: fetch sum(a) for all rows where b < 120

let rc3,stmt2 = raw.sqlite3_prepare_v2(conn, "SELECT a FROM foo WHERE b<120")
check2 conn rc3

let mutable vsum = 0

while raw.SQLITE_ROW = ( checkthru conn (raw.sqlite3_step(stmt2)) ) do 
    vsum <- vsum + (raw.sqlite3_column_int(stmt2, 0))

check2 conn (raw.sqlite3_finalize(stmt2))

// FIVE: close and print the results

check1 (raw.sqlite3_close(conn))

printfn "val: %d" vsq
printfn "sum: %d" vsum


  • The is_error function actually looks kind of elegant to me in this form. Note that != is spelled <>. Also there is no return keyword, as the value of the last expression just becomes the return value of the function.

  • The F# way is to use type inference. For example, in the is_error function, the rc parameter is strongly typed as an integer even though I haven't declared it that way. The F# compiler looks at the function and sees that I am comparing the parameter against raw.SQLITE_OK, which is an integer, therefore rc must be an integer as well. F# does have a syntax for declaring the type explicitly, but this is considered bad practice.

  • The check2 and checkthru functions are identical except that one returns the unit type (which is kind of like void) and the other passes the integer argument through. In C# this wouldn't matter and I could have just had the check functions return their argument when they don't throw. But F# gives warnings ("This expression should have type 'unit', but has type...") for any expression whose values is not used.

  • In C#, I overloaded check() so that I could sometimes call it with the sqlite3 connection handle and sometimes without. F# doesn't do function overloading, so I did two versions of the function called check1 and check2.

  • Since raw.sqlite3_open() has an out parameter, F# automatically converts this to return a tuple with two items (the actual return value is first, and the value in the out parameter is second). It took me a little while to figure out the right syntax to get the two parts into separate variables.

  • It took me even longer to figure out that calling a .NET method in F# uses a different syntax than calling a regular F# function. I was just getting used to the idea that F# wants functions to be called without parens and with the parameters separated by spaces instead of commas. But .NET methods are not F# functions. The syntax for calling a .NET method is, well, just like in C#. Parens and commas.

  • Here's another way that method calls are different in F#: When a method call is a parameter to a regular F# function, you have to enclose it in parens. That's why the call to sqlite3_exec() in the first stanza is parenthesized when I pass it to check2.

  • BTW, one of the first things I did was try to call raw.sqlite3_Open(), just to verify that F# is case-sensitive. It is.

  • F# programmers seem to pride themselves on how much they can do in a single line of code, regardless of how long it is. I originally wrote the second stanza in a single line. I only split it up so it would look better here in my blog article.

  • In the third stanza, F# wouldn't let me reuse rc ("Duplicate definition of value 'rc'") so I had to introduce rc2.

  • In the fourth stanza, I have tried to exactly mimic the behavior of the C# code, and I think I've succeeded so thoroughly that any real F# programmer will be tempted to gouge their eyes out when they see it. I've used mutable and while/do, both of which are considered a very un-functional way of doing things.

  • Bottom line: This code works and it does exactly what the original C# does. But I named the file fsharp_dreadful.fs because I think that in terms of what is considered best practices in the F# world, it's probably about as bad as it can be while still being correct.

F#, somewhat less csharpy

Here's an F# version I called fsharp_less_bad.fs. It's still not very good, but I've made an attempt to do some things in a more F#-ish way.

open SQLitePCL

// helper functions to check SQLite result codes and throw

let is_error rc = 
    match rc with
    | raw.SQLITE_OK -> false
    | raw.SQLITE_ROW -> false
    | raw.SQLITE_DONE -> false
    | _ -> true

let check1 rc = 
    if (is_error rc) 
    then failwith (sprintf "%d" rc) 
    else ()

let check2 conn rc = 
    if (is_error rc) 
    then failwith (raw.sqlite3_errmsg(conn)) 
    else rc

let checkpair1 pair =
    let rc,result = pair
    check1 rc |> ignore

let checkpair2 conn pair =
    let rc,result = pair
    check2 conn rc |> ignore

// helper functions to wrap method calls in F# functions

let sqlite3_open name = checkpair1 (raw.sqlite3_open(name))
let sqlite3_exec conn sql = check2 conn (raw.sqlite3_exec (conn, sql)) |> ignore
let sqlite3_prepare_v2 conn sql = checkpair2 conn (raw.sqlite3_prepare_v2(conn, sql))
let sqlite3_bind_int conn stmt ndx v = check2 conn (raw.sqlite3_bind_int(stmt, ndx, v)) |> ignore
let sqlite3_step conn stmt = check2 conn (raw.sqlite3_step(stmt)) |> ignore
let sqlite3_finalize conn stmt = check2 conn (raw.sqlite3_finalize(stmt)) |> ignore
let sqlite3_close conn = check1 (raw.sqlite3_close(conn))
let sqlite3_column_int stmt ndx = raw.sqlite3_column_int(stmt, ndx)

// MAIN program

// ONE: open the db and create the table

let conn = sqlite3_open(":memory:")

// use partial application to create an exec function that already 
// has the conn parameter baked in

let exec = sqlite3_exec conn

exec "CREATE TABLE foo (a int, b int)"

// TWO: insert 16 rows

let ins x = 
    exec (sprintf "INSERT INTO foo (a) VALUES (%d)" x)

[1 .. 16] |> List.iter ins

// THREE: set b = a squared and find b for a=7

exec "UPDATE foo SET b = a * a"

let stmt = sqlite3_prepare_v2 conn "SELECT b FROM foo WHERE a=?"
sqlite3_bind_int conn stmt 1 7
sqlite3_step conn stmt
let vsq = sqlite3_column_int stmt 0
sqlite3_finalize conn stmt

// FOUR: fetch sum(a) for all rows where b < 120

let stmt2 = sqlite3_prepare_v2 conn "SELECT a FROM foo WHERE b<120"

let vsum = List.sum [ while 
    raw.SQLITE_ROW = ( check2 conn (raw.sqlite3_step(stmt2)) ) do 
        yield sqlite3_column_int stmt2 0 

sqlite3_finalize conn stmt2

// FIVE: close and print the results

sqlite3_close conn

printfn "val: %d" vsq
printfn "sum: %d" vsum


  • I changed is_error to use pattern matching. For this very simple situation, I'm not sure this is an improvement over the simple boolean expression I had before.

  • I get the impression that typical doctrine in functional programming church is to not use exceptions, but I'm not tackling that problem here.

  • I got rid of checkthru and just made check2 return its rc paraemter when it doesn't throw. This means most of the times I call check2 I have to ignore the result or else I get a warning.

  • I added a couple of checkpair functions. These are designed to take a tuple, such as the one that comes from a .NET method with an out parameter, like sqlite3_open() or sqlite3_prepare_v2(). The checkpair function does the appropriate check function on the first part of the tuple (the integer return code) and then returns the second part. The sort-of clever thing here is that checkpair does not care what type the second part of the tuple is. I get the impression that this sort of "things are generic by default" philosophy is a pillar of functuonal programming.

  • I added several functions which wrap raw.sqlite3_whatever() as a more F#-like function that looks less cluttered.

  • In the first stanza, after I get the connection open, I define an exec function using the F# partial application feature. The exec function is basically just the sqlite3_exec function except that the conn parameter has already been baked in. This allows me to use very readable syntax like exec "whatever". I considered doing this for all the functions, but I'm not really sure this design is a good idea. I just found this hammer called "partial application" so I was looking for a nail.

  • In the second stanza, I've eliminated the for loop in favor of a list operation. I defined a function called ins which inserts one row. The [1 .. 16] syntax produces a range, which is piped into List.iter.

  • The third stanza looks a lot cleaner with all the .NET method calls hidden away.

  • In the fourth stanza, I still have a while loop, but I was able to get rid of mutable. The syntax I'm using here is (I think) something called a computation expression. Basically, the stuff inside the square brackets is constructing a list with a while loop. Then List.sum is called on that list, resulting in the number I want.

Other notes

I did all this using the command-line F# tools and Mono on a Mac. I've got a command called fsharpc on my system. I'm not sure how it got there, but it probably happened when I installed Xamarin.

Since I'm not using msbuild or NuGet, I just harvested SQLitePCL.raw.dll from the SQLitePCL.raw NuGet package. The net45 version is compatible with Mono, and on a Mac, it will simply P/Invoke from the SQLite library that comes preinstalled with MacOS X.

So the bash commands to setup my environment for this blog entry looked something like this:

mkdir fs_sqlite
cd fs_sqlite
mkdir unzipped
cd unzipped
unzip ~/Downloads/sqlitepcl.raw_needy.0.5.0.nupkg 
cd ..
cp unzipped/lib/net45/SQLitePCL.raw.dll .

Here are the build commands I used:

fs_sqlite eric$ gmcs -r:SQLitePCL.raw.dll -sdk:4.5 csharp.cs

fs_sqlite eric$ fsharpc -r:SQLitePCL.raw.dll fsharp_dreadful.fs
F# Compiler for F# 3.1 (Open Source Edition)
Freely distributed under the Apache 2.0 Open Source License

fs_sqlite eric$ fsharpc -r:SQLitePCL.raw.dll fsharp_less_bad.fs
F# Compiler for F# 3.1 (Open Source Edition)
Freely distributed under the Apache 2.0 Open Source License

fs_sqlite eric$ ls -l *.exe
-rwxr-xr-x  1 eric  staff   4608 Sep  8 15:30 csharp.exe
-rwxr-xr-x  1 eric  staff   8192 Sep  8 15:30 fsharp_dreadful.exe
-rwxr-xr-x  1 eric  staff  11776 Sep  8 15:31 fsharp_less_bad.exe

fs_sqlite eric$ mono csharp.exe
val: 49
sum: 55

fs_sqlite eric$ mono fsharp_dreadful.exe 
val: 49
sum: 55

fs_sqlite eric$ mono fsharp_less_bad.exe 
val: 49
sum: 55

BTW, I noticed that compiling F# (fsharpc) is a LOT slower than compiling C# (gmcs).

Note that the command-line flag to reference (-r:) an assembly is the same for F# as it is for C#.

Note that fsharp_dreadful.exe is bigger than csharp.exe, and the "less_bad" exe is even bigger. I suspect that generalizing these observations would be extrapolating from too little data.

C# fans may notice that I [attempted to] put more effort into the F# code. This was intentional. Making the C# version beautiful was not the point of this blog entry.

So far, my favorite site for learning F# has been



Xamarin.Forms with Zumero

I am a Xamarin fanboy, so my excitement about Xamarin.Forms is perhaps unsurprising. But I see an awful lot of potential for this technology. I want to show you some of the stuff we've been doing with Xamarin.Forms here at Zumero.

Andrew Jackson in Two Minutes

First I am going to race through this demo very quickly. Then I'll circle back around and explain things.

STEP ONE: Download ZAG and run it

Visit and download the ZAG application. For this demo, I'm using ZAG on Mac OS X (but you could choose Windows or Linux) and I am targetting iOS (but you could choose Android or Windows Phone).

When you run the app, you should see something like this:

STEP TWO: New Database

Under the "File" menu, choose "New Database...". You should see this dialog:

Fill in the Server URL and DBFile exactly as shown in the screen shot (Server URL: "". DBFile: "demo"). Click the OK button. You should see a dialog asking you where to save the local copy of the database:

Click OK. You should see "Syncing with the ZSS Server":

And when the sync is complete, at the bottom of the ZAG window, you should see "Sync result: 0 (success)".

STEP THREE: Generate

Under the "Generate" menu, find and choose the item labeled "Xamarin.Forms C#":

You will be asked three questions, and you should be able to just click OK on all three.

STEP FOUR: Open the sln file

You can quit the ZAG application now. It should have generated a folder somewhere like /Users/eric/Documents/demo.zssdemo/. Under that folder you should find a Xamarin solution tree that looks something like this:

Double-click the demo.sln file to open it in Xamarin Studio. You should see four C# projects: a Portable Class Library called "demo.Shared", plus one app target each for iOS, Android, and Windows Phone 8:

STEP FIVE: Build and run the app

If you build and run the demo.iOS app in the iPhone simulator, you should see something like this:


Click the "Sync" button in the upper right. You should see:

The defaults should be fine. Just tap the "Sync Now" button. When the sync is completed, you should see a list of tables:

STEP SEVEN: Andrew Jackson

Tap the "presidents" table. In an iPad instance of the simulator, you would see this:

And tap the seventh item. You should see Andrew Jackson, the only U.S. president ever to kill someone in a duel:

What is ZAG?

ZAG is short for "ZSS App Generator". It's a desktop app which generates ready-to-build source code and build scripts for mobile apps that sync using ZSS.

We think of ZAG as a way for getting people started faster. Many people come to our product without much experience in mobile app development. ZAG can be used to give them a starting point, sort of like sample code that is customized for their data.

What is Zumero for SQL Server?

Zumero for SQL Server (ZSS) is a solution for data sync between SQL Server and mobile devices.

More info from Dan Bricklin about offline in mobile apps:

More info about Zumero on our website:

More info about Zumero from my previous blog entry: here

What is

This is a publicly accessible ZSS server provided so that folks can play with Zumero clients more easily. It contains some basic sample data such as U.S. presidents and the periodic table of the elements.

In real-world scenarios, a customer would use ZAG to generate their starter app after they have completed setup of their ZSS server.

What is Xamarin?

Xamarin is (IMHO) a great solution for building mobile apps.

One of the main benefits of the Xamarin platform is the ability to write the non-UI parts of your iOS/Android/WP8 apps in cross-platform code while implementing a native user interface for each mobile environment.

But I would use Xamarin even for a single-platform app, simply to get the benefits of working in .NET/C#.

More info on the Xamarin website:

What is Xamarin.Forms?

Xamarin.Forms is Xamarin's solution for making [most of] your UI code cross-platform as well, while retaining fully native performance and feel.

In a nutshell, the coolness of Xamarin.Forms lies in the fact that in the solution generated by ZAG above, the demo.Shared project is a Portable Class Library even though it contains the entire user interface for the app.

More info on the Xamarin website:

What is a Portable Class Library (PCL)?

A PCL is a .NET class library that is annotated with information about which platforms it should support. This metadata allows the tooling to enforce portability rules in both the development and the consumption of the library.

More info on Scott Hanselman's blog:

More info on the Xamarin website:

Why does ZAG generate separate projects for iOS, Android, and WinPhone?

Xamarin.Forms can make most of your UI code portable, but not all of it. The actual building of the mobile app is specific to each platform. But if you look in the code for each of those platform-specific projects, you'll see that there isn't much there.

What dependencies does the ZAG-generated app have?

The following NuGet packages will need to be retrieved:

  • Xamarin.Forms

  • SQLite-net PCL

  • SQLitePCL.raw_basic

  • Zumero

What is the "SQLite-net PCL" NuGet package?

This is the Portable Class Library (PCL) version of SQLite-net, the popular lightweight SQLite ORM by Frank Krueger (@praeclarum).

More info on GitHub:

More info on the NuGet website:

What is the "SQLitePCL.raw_basic" NuGet package?

SQLitePCL.raw is my Portable Class Library for accessing SQLite from C#.

More info on Github:

More info on the NuGet website:

What is SQLite?

SQLite is the most popular SQLite database for mobile devices.

More info on the SQLite website:

What is the "Zumero" NuGet package?

This is the Zumero Client SDK in the form of a Portable Class Library in a NuGet package.

More info on the Zumero website:

More info on the NuGet website:

What do Zumero's client-side SQLite files look like?

As much as possible, they look exactly like they looked in SQL Server.

  • Table and column names are the same.

  • All data values are the same (whenever possible).

  • Foreign keys in SQL Server are reconstructed as foreign keys in SQLite.

  • Since SQLite does not perform type checking, Zumero adds constraints to do so.

And so on...

What's happening in step two?

ZAG is acting as a Zumero client and synchronizing the data on the server into a local SQLite file. This file is used to obtain information about the tables and columns necessary to generate the mobile app.

The same sync is happening in step six, except then it is the mobile app performing the sync instead of ZAG.

What were those three questions in step three?

The first one is the project name:

Then ZAG wants to know the settings for your sync server. These should already be filled in with the ones you entered earlier:

Finally, ZAG is asking you where to save the source code and project files for the app to be generated:

Does a ZAG-generated app allow modifications to the data?

For the Xamarin.Forms C# target, yes. On the item detail page, you should be able to enter new values in text fields and 'Save' the changes.

But you should get a permission denied error if you try to sync those changes to our public demo server. :-)

Is ZAG generating UI stuff as XAML or as C# code?

Currently, it's XAML. You'll find the files in the 'xaml' folder in the demo.Shared project.

Does ZAG generate polished ready-to-use apps?

Oh definitely not. :-)

The output of ZAG should build and run with no errors (if it doesn't, it's a bug, and please let us know), but it's just a starting point for further development.



Billions without Buzz

I've been thinking a lot lately about the distorted perspective I get when I extrapolate from my daily sources of content.

Using Twitter buzz to sip from a firehose

I currently follow 304 people on Twitter. These people are a primary means for me to hear about stuff that is (1) happening, and (2) important to me.

I rely more on echoes than voices. For example, I don't follow Satya Nadella (@satyanadella). But whenever he tweets something I would find important, I hear about it anyway, because a dozen people I do follow are talking about it.

Twitter for me is all about conversations. It's about buzz. I'm interested in what people are talking about. And I like having the option of participating in the chatter.

And the people I choose to follow pretty much cover everything important. I don't miss anything that I need to know.

Or do I?

What am I missing?

Obviously, my method is designed to exclude information. Sometimes my buzz filter works exactly as I want. I rarely see any tweets about Miley Cyrus. That is "by design".

I also like the fact that buzz is naturally slanted toward things that are new even though lots of very important things are old. (As long as I don't get confused. If I extrapolate from buzz with a black-and-white mentality, I might believe that COBOL, SQL, and Subversion are dead because everybody has switched to Java, Mongo, and Git. Older technologies have little or no buzz, but tons of people are still using them everyday.)

The problem is the stuff that is new and important (to me) but has very little buzz.

The boring wave of B2B apps

I am absolutely convinced that the B2B wave of mobile apps has barely started.

As Benedict Evans (@BenedictEvans) says, mobile is eating the world. But so far, most of the action is with consumers, not businesses.

And this whole wave has been very high-buzz, mostly because everything had to get bigger, and the stuff that could not was, er, "disrupted".

Add two billion mobile devices to the world and lots of people are forced to think about scale in a whole new way:

  • Manufacturing. Foxconn is enormous. What was the biggest manufacturing facility on earth before the iPhone?

  • Venture Capital. I remember when $100M was an exit. Now it's a series A.

  • Servers. Mobile is accelerating the growth of the cloud, served by data centers that make Soldier Field look small.

  • Databases. When you have 500 million users, you can't afford unique constraints and foreign keys in the db layer anymore.

All this big-ness is causing a lot of disruption. And buzz.

Meanwhile, the majority of the corporate world is still trying to figure out what to do about mobile.

Consider this in terms of the classic marketing bell curve:

  • In the consumer world, mobile is in the conservatives, and starting to sell to the laggards.

  • In the corporate world, mobile is in the early adopters. It's pre-chasm.

Companies who still need high growth to justify their stock price (read: Apple) are distressed about the notion that everybody on the planet who can afford a smart phone already has one. Marketing reseach firms are still doing surveys asking corporate IT about when they are going to dive into mobile even as my Mom has two tablets.

Has there ever been a technology wave where the consumers got so far ahead of business?

I was one of those people in the early 80s who had a computer before they were prevalent in the business world. But I was a hobbyist and a nerd. In terms of volume and sheer revenue, adoption of PCs was driven by companies, not consumers. They wanted to run Lotus 1-2-3, so they led the way. It was a long time before computers solved real problems for consumers in the way that they solved real problems for business. In fact, I'd argue this didn't happen until around 1995 when the Web came along.

In mobile devices, consumers have gotten so far ahead that they have provided the client side of the infrastructure that Corporate IT will use. Going forward, it's going to be BYOD (Bring Your Own Device). Big companies are not going to buy 10,000 BlackBerries for their workforce when everybody already has a smart phone. Instead, they have to figure out how they're going to securely integrate all these different devices into their corporate systems (which is why cross-platform is getting even more important in the mobile space, which is contributing to Xamarin's pursuit of world domination). This is not the kind of policy to which Corporate IT is accustomed, and that is slowing them down even more.

But this is going to happen. A lot of enterprise apps are going to get written.

And this wave will be very low-buzz compared to the "Angry Birds and Candy Crush" wave. People won't be talking about it (and even when they do, the sound will get drowned out by the buzz over the Internet of Things wave).

Quite simply, the B2B apps wave is not interesting enough to get serious buzz:

  • It won't be as disruptive.

  • It won't re-challenge our notions of scale. (Walmart's 2.2 million employees would be a small user base by today's standards.)

  • It's going to be built on, and integrated with, technologies that are old. (Corporate IT will prefer to add mobile incrementally, with as little change to existing systems as possible.)

Compared to my first computer(s), an iPhone is a supercomputer. We are moving into a wave where companies like Procter & Gamble are going to gain operational efficiency because all their employees carry supercomputers in their pocket. And this will be considered boring.

But despite all the yawns, this wave is going to involve a lot of money. Many billions of dollars are going to move around as all these enterprise apps get written.

In other words, this is going to be boring in the same way that IBM has been boring for the last decade. This week, my Twitter feed is dominated by people talking about the Google I/O conference. Nobody is talking about IBM, even though they have almost twice as much revenue as Google. That's how buzz works.

If I want to closely follow something that doesn't have much buzz, I'm gonna have to work harder.

Let's talk about Alex Bratton

I am currently reading a book called Billion Dollar Apps, by Alex Bratton (@alexbratton), CEO of Lextech, a custom mobile app dev shop in Chicago.

My buzz filter certainly didn't find this book for me:

  • @alexbratton has even fewer Twitter followers than I do.

  • @LextechApps has fewer followers than some high school students I know.

  • Almost all of Bratton's followers are people I don't know.

  • Google searches reveal no apparent connection between Lextech and Xamarin.

  • Twitter searches suggest that Bratton and Lextech have seldom or never been mentioned by Scott Hansleman (@shanselman).

There just aren't many connections between Lextech's world and mine. In fact, it looks like maybe I am the only occupant of the overlapping portion of the Venn diagram.

Maybe Lextech just doesn't have much buzz. Or maybe Lextech has the kind of buzz that happens mostly off Twitter. Maybe the CIOs of Fortune 500 companies share rides on their private jets where they sip Macallan 25 and talk about how great Lextech is.

Whatever. I've been following Alex Bratton on Twitter largely because he and I were students at UIUC around the same time, and I sort-of vaguely remember him. Were it not for this very thin college connection, I might never have heard of him or his company or his book.

And that would be sad, because Lextech's activities land squarely in an area of interest for me. Bratton's company may be low-buzz, but they're building B2B mobile apps for banner-name companies including GE, Fidelity, John Deere, Blue Cross, and H&R Block. They're riding the front of the wave. I think that's pretty darn cool.

So I am halfway through the book, and two things are already quite clear:

  • The book is really good.

  • I find it boring.

Basically, I'm not enjoying this book because it was clearly not written for people like me. I enjoy fiction books about crime investigation and books with lots of curly braces and semicolons. Billion Dollar Apps is neither of these. It's written for CxOs at big companies who have to make big decisions about mobile technology.

But I'm forcing myself to read this book for the same reason I force myself to eat certain unappealing vegetables. I don't like the taste, but I think it's good for me.

Thinking about Lextech and its customers is forcing me to widen my perspective. It is reminding me that the boredom goes both ways:

  • Most developers don't care about the details of how John Deere is using mobile to make its business run better. They tend to focus more on the technology than on solutions to problems.

  • Probably nobody at John Deere is spending their time critiquing the design of Xamarin.Forms or figuring out best practices for PCLs. They see mobile technology as a solution to their problems.

This book is good because it speaks to its intended audience, and that group of people doesn't care about the details of why certain .NET things are incompatible with the AOT compiler in Xamarin.iOS. Billion Dollar Apps is a book that talks about sensibly applying mobile technology to make regular non-geek businesses work better.

Like most other geeks, I am prone to getting lost in technology for its own sake. But I'm not just a developer. I'm an entrepreneur. I need to keep a sense of balance.

So, I don't particularly want to read this book, but I need to read it.

Bratton's book is about the beginning of a big boring bazaar where beaucoup billions bounce bereft of buzz.

(Sorry, the forced alliteration was shameful, but I couldn't resist.)

Bottom line

I've wandered around a bit, so let me close with a summary of key points you might take away from this blog entry:

  • Consider reading Alex Bratton's book. If you are a CxO of a Fortune 500 company (and you probably are not, because you're reading my blog), you'll probably like it. If you are a developer (and you probably are, because you're reading my blog), you probably won't like it any more than I do. Eat your brussel sprouts.

  • Widening your perspective is always good advice. If you're like me, there's a really good chance that your daily information flow has boundaries. Those boundaries make you efficient, but they also constantly protect you from seeing the perspective of people who are not like you. Look outside your boundaries.

  • If you are interested in the boring B2B wave of mobile apps, so am I. Maybe we should talk. Maybe I should be following you on Twitter. Maybe we should see if we can create a little buzz.



SQLitePCL.raw NuGet package, 0.2.0-alpha

I have pushed up the second release of the NuGet package(s) for SQLitePCL.raw. There are no API changes, and all tests are passing on all my target platforms (Xamarin.iOS, Xamarin.Android, .NET 4.5, WinRT 8, WinRT 8.1, Windows Phone 8, Windows Phone 8.1 RT, and Windows Phone 8.1 Silverlight).

Changes in this release

  • Tweak the net45 platform assembly to [hopefully] make it compatible with Mono on non-Windows platforms

  • Better error messages for build configuration problems

  • Bundled builds of the sqlite3 library are now compiled with foreign keys defaulting to on

  • SQLitePCL.tests, a separate NuGet package containing my test suite in source code form

  • SQLitePCL.ugly, a separate NuGet package containing the "not-in-C#-style" API which I use for writing the test suite

Changes still coming [soon]

  • A release which is polished enough for real use

  • Release notes in the nuspec instead of here in a blog entry

  • Hopefully, automatic insertion of the Visual C++ Runtime SDK reference on RT-ish platforms

  • Support for sqlite-net in a NuGet package. I've been coordinating with @praeclarum and @timheuer on this. He/they accepted my pull request with the necessary changes (thanks!). And my build system is generating a csproj/nuspec for proof of concept purposes. Almost there.

  • SQLitePCL.kitchensink, a NuGet package for advanced users, containing all of the configurations

  • Documentation

  • Better support for SQLCipher



First NuGet package for SQLitePCL.raw


I have pushed up the first NuGet package for SQLitePCL.raw.

Is this ready for use on mission-critical applications?


I gave this a version number of "0.1.0-alpha".

By including the -alpha part, I signal to NuGet that this is a pre-release package.

By using 0.1.0 as the version number, I signal to human beings that if you use this package, everything in your life will go wrong. Your dog will leave you for someone else. Your favorite TV show will get canceled. A really dumb VC will send you a term sheet.

This is the first NuGet package, not the last. I gotta start somewhere.

So this package doesn't work at all?

Well, actually, no, it shouldn't be that bad. Underpromise and overdeliver.

I have run my test suite against this package for all of the following environments:

  • Xamarin.iOS (simulator)

  • Xamarin.Android (emulator, API level 15)

  • WinRT 8.1 (x86, on Windows 8.1)

  • Windows Phone 8.0 (in the emulator)

  • Windows Phone 8.1 (RT flavor, in the emulator)

  • Windows Phone 8.1 (Silverlight flavor, in the emulator)

  • .NET 4.5 (on Windows 8.1)

On all the Windows platforms, the tests all pass. For iOS and Android, the only failures are the expected ones.

Why do some of your tests fail on iOS and Android?

Because the version of SQLite which is preinstalled by Apple/Google is a bit old, and some of my tests are explicitly exercising newer SQLite features.

So is this NuGet package ready for testing?

Yes, please.

Eric, I am much smarter than you. Can I give you some constructive feedback?

Yes, please.

Why is the word "basic" in the name of this package?

I think it likely that I may end up with more than one NuGet package for SQLitePCL.raw. Different packages for different use cases. So I need them to have different names.

This one is "basic" in the sense that it tries to be the package that most people will want to use. All of the other contemplated packages would have some sort of less-appealing word in the name, designed to scare people away. The label for the next one might be "broccoli".

The main tradeoff is the issue of where your app is getting SQLite itself. For [much] more detail on this, see my recent blog entry on The Multiple SQLite Problem.

Anyway, for this "basic" package, the iOS and Android assemblies use the SQLite library which is part of the mobile OS, and all of the Windows assemblies bundle a sqlite3.dll.

Which version of SQLite is bundled on the Windows platforms?

How was the bundled SQLite library compiled?





Can I rely on all future versions of this package having the SQLite library compiled with exactly those options?


How do I find this package?

Direct link:

And, it comes up for me if I just search for "SQLitePCL" on NuGet.

Hey, there are TWO results of that search? What's the deal?

Mine is the one called SQLitePCL.raw. The other one is by MSOpenTech.

And actually, my work is a [hopefully friendly] fork of theirs. Thanks to those folks (whoever they are) for a solid starting point. I am available to collaborate with them if there is interest.

Why did you fork the other one?

See the README on GitHub for some info on this.

Any caveats when trying to use this on iOS?

AFAIK, no. For me, this Just Works.

Any caveats when trying to use this on Android?

AFAIK, no. For me, this Just Works.

Any caveats when trying to use this on .NET 4.5?

You need to compile for a specific CPU (x86, x64), not "Any CPU". In Visual Studio, right-click on your solution and choose Configuration Manager.

Any caveats when trying to use this on Windows Phone 8.0?

Not really. Just make sure you are building for x86 for the emulator or ARM for an actual device. In Visual Studio, right-click on your solution and choose Configuration Manager.

Any caveats when trying to use this on Windows RT or Windows Phone 8.1?

Two issues:

  • Build failure: You need to compile for a specific CPU (x86, x64, ARM), not "Any CPU". In Visual Studio, right-click on your solution and choose Configuration Manager.

  • Runtime failure, file not found: You need to add a reference to the Visual C++ 2013 Runtime. Hopefully a future version of this package will automatically add this reference for you.

What other forms of this package are you planning?

I'm considering one that doesn't bundle any SQLite instances at all. For use with cases where somebody wants to have their own build of SQLite. Or for people who want to use the SQLite vsix SDK builds on

Why are some of the platform assemblies in the build directory instead of the lib directory?

Android and iOS are in lib.

All the Windowsy ones are in build, because they're all CPU-specific, so they need more help than lib can provide. An MSBuild .targets file is used to inject the appropriate reference.



Ever have a day like this one?

  • Check email and notice a message from somebody having trouble using SQLitePCL.raw on Windows Phone 8.1. Realize that I haven't run the test suite since I started working on the new build scripts. Assume that I broke something.

  • Hook up the automated test project to the output of the new build system. Sure enough, the tests fail.

  • Notice that the error message is different from the one in the user's email.

  • Realize that the user is actually using the old build system, not the new one. Wonder how that could have broken.

  • Bring up the old build system, run the tests. Yep, they fail here too. Must be something in the actual code.

  • Dig around for a while and try to find what changed.

  • Use git to go back to the last commit before I started the new build system stuff. Rebuild all. Run the tests. They pass. Good. Now I just have to diff and figure out which change caused the breakage.

  • git my working directory back to the current version of the code. Rebuild all and run the tests again to watch them fail again. BUT NOW THEY PASS.

  • Wonder if perhaps Visual Studio is less frustrating for people who drink Scotch in the mornings.

  • Decide that maybe something was flaky in my machine. The tests are passing again, so there's no problem.

  • Realize that the user wasn't actually running the test suite. He was trying to reference from his own project. And he had to do that manually, because I haven't published the nuget package yet. Maybe he just screwed up the reference or didn't copy all the necessary pieces.

  • Run the tests in the new build system to watch them pass there as well. But here they STILL FAIL.

  • Decide to take the build system out of the equation and just finish getting things working right with nuget. Build the unit test package separately in its own solution. Add a reference to the nuget package and start working out the issues.

  • Run the tests. Everything throws because the reference got added to the "bait" version of the PCL instead of the to the WP81 platform assembly. Oh well. This is what I need to be fixing anyway.

  • Notice that the .targets file didn't get properly imported into the test project when the package was installed. Wonder why. But that's gotta be why the platform assembly didn't get referenced.

  • Realize that the bait assembly somehow got referenced. Wonder why.

  • What is Scotch anyway? Go read several articles about single malt whiskey.

  • Decide to take nuget out of the equation and focus on why the new build system is producing dlls that won't load.

  • Google the error message "Package failed updates, dependency or conflict validation". I need to know exactly what was the cause of the failure.

  • Realize that the default search engine or IE is Bing. Do the same search in Google. Get different results.

  • Become annoyed when co-worker interrupts me to tell me that there is a new trailer for Guardians of the Galaxy.

  • Read a web page on the Microsoft website which explains how to get the actual details of that error message. Spend time wandering around Event Viewer until I see the right stuff.

  • Realize that the web page is actually talking about WinRT on the desktop, not Windows Phone.

  • Try to find a way to get developer-grade error messages in the Windows Phone emulator. Fail.

  • Notice that below the error message, Visual Studio's suggested resolution is to instead use a unit test project that is targeted for Windows Phone, even thought IT ALREADY IS.

  • Blame Steve Ballmer FOR EVERYTHING.

  • Wonder if WP81 is the only thing that broke. Run the tests for WinRT. They fail as well.

  • Get annoyed because the only way Visual Studio can run the unit tests for just one project is to unload all the others.

  • Get upset because the Visual Studio Reload Project command doesn't work like the way it did a week or two ago. Now it reloads all the projects instead of just the one I wanted. Did the installation of the Xamarin Visual Studio integration break it?

  • Go back to the very basics. Run the unit tests for plain old .NET 4.5. They pass.

  • Re-run the unit tests for WinRT to watch them fail again. NOW THEY PASS.

  • Realize the co-worker is absolutely right. The most important thing is to watch the Guardians of the Galaxy trailer.

  • Get annoyed because the sound on my MBP isn't working. Watch the whole trailer anyway, without sound.

  • Review all my project settings in the Visual Studio dialogs, just to see if I notice anything odd.

  • Go back to my web browser. Realize that the world of Scotch whiskey might actually be more complicated than Visual Studio.

  • Go home. Discover that the annual spring invasion of ants in our kitchen is proceeding nicely.

  • Fight some more with Visual Studio. Give up. Go to bed.

  • Wake up the next morning. Discover that the teenager's contribution to our war against the ants was to leave unrinsed plates by the sink. Thousands of ants feasting on cheesecake debris and syrup.

  • Open the laptop. Run diff to compare the csproj and vcxproj files from the old build system against the new one. See that there are no differences that should make any difference.

  • Change them all anyway. Update every setting to exactly match the old build system. One at a time. Run the test suite after each tweak so I can figure out exactly which of the seeminlgy-harmless changes caused the breakage.

  • Wait. My kid had cheesecake and waffles FOR DINNER?

  • Become seriously annoyed that Visual Studio changes the Output pane from "Tests" to "Build" EVERY SINGLE TIME I run the tests.

  • Finish getting all the settings to match. The tests still don't pass.

  • Try to remember if I ever done anything successfully. Anything at all. Distinctly recall that when I was mowing the lawn this weekend, the grass got shorter. Focus on that accomplishment. Build on that success.

  • Realize that the old build system works and the new one doesn't. There has to be a difference that I'm missing. I just have to find it.

  • Go back to the old build system. Rebuild all. Run the tests so I can watch them pass and start over from there. BUT NOW THEY'RE FAILING AGAIN.

  • Go do something else.



The Multiple SQLite Problem

Eric, why the #$%! is your SQLite PCL taking so long?

It's Google's fault. And Apple's fault.


No. Yes. Kinda. Not really.

The Multiple SQLite Problem, In a Nutshell

If your app makes use of two separate instances of the SQLite library, you can end up with a corrupted SQLite data file.

From the horse's mouth

On the SQLite website, section 2.2.1 of How to Corrupt an SQLite Database File is entitled "Multiple copies of SQLite linked into the same application", and says:

As pointed out in the previous paragraph, SQLite takes steps to work around the quirks of POSIX advisory locking. Part of that work-around involves keeping a global list (mutex protected) of open SQLite database files. But, if multiple copies of SQLite are linked into the same application, then there will be multiple instances of this global list. Database connections opened using one copy of the SQLite library will be unaware of database connections opened using the other copy, and will be unable to work around the POSIX advisory locking quirks. A close() operation on one connection might unknowingly clear the locks on a different database connection, leading to database corruption.
The scenario above sounds far-fetched. But the SQLite developers are aware of at least one commercial product that was released with exactly this bug. The vendor came to the SQLite developers seeking help in tracking down some infrequent database corruption issues they were seeing on Linux and Mac. The problem was eventually traced to the fact that the application was linking against two separate copies of SQLite. The solution was to change the application build procedures to link against just one copy of SQLite instead of two.

At its core, SQLite is written in C. It is plain-old-fashioned native/umanaged code. If you are accessing SQLite using C#, you are doing so through some kind of a wrapper. That wrapper is loading the SQLite library from somewhere. You may not know where. You probably don't [want to] care.

This is an abstraction. And it can leak. C# is putting some distance between you and the reality of what SQLite really is. That distance can somewhat increase the likelihood of you accidentally having two instances of the SQLite library without even knowing it.

SQLite as part of the mobile OS

Both iOS and Android contain an instance of SQLite as part of the basic operating system. This is a blessing. And a curse.

Built-in SQLite is nice because your app doesn't have to include it. This makes the size of your app smaller. It avoids the need to compile SQLite as part of your build process.

But the problem is that the OS has contributed one instance of the SQLite library that you can't eliminate. It's always there. The multiple SQLite problem cannot happen if only one SQLite is available to your app. Anybody or anything which adds one is risking a plurality.

If SQLite is always in the OS, why not always use it?

Because Apple and Google do a terrible job of keeping it current.

  • iOS 7 ships with SQLite 3.7.13. That shipped in June of 2012.

  • Android ships with SQLite 3.7.11. That shipped in March of 2012.

  • Since Android users never update their devices, a large number of them are still running SQLite 3.7.4, which shipped in December of 2010. (Yes, I know the sweeping generalization in the previous sentence is unfair. I like Android a lot, but I think Google's management of the Android world has been bad enough that I'm entitled to a little crabbiness.)

If you are targeting Android or iOS and using the built-in SQLite library, you are missing out on at least TWO YEARS of excellent development work by DRH and his team. Current versions of SQLite are significantly faster, with many bug fixes, and lots of insanely cool new features. This is just one of the excellent reasons to bundle a current version of SQLite into your app instead of using the one in the OS.

And as soon as you do that, there are two instances in play. You and Apple/Google have collaborated to introduce the risk of database corruption.


AFAIK, no version of Windows includes a SQLite library. This is a blessing. And a curse. For all of the opposite reasons discussed above.

In general, building a mobile app for Windows (Phone or RT or whatever) means you have to include SQLite as part of the app. And when doing so, it certainly makes sense to just use the latest version.

And that introduces another reason somebody might want to use an application-private version of SQLite instead of the one built-in to iOS or Android. If you're building a cross-platform app, you probably want all your platforms using the same version of SQLite. Have fun explaining to your QA people that your app is built on SQLite 3.8.4 on Windows and 3.7.11 on Android and 3.7.13 on iOS.

BTW, it's not clear how or if Windows platforms suffer from the data corruption risk of the multiple SQLite problem. Given that the DRH explanation talks about workarounds for quirks in POSIX file locking, it seems likely that the situation on Windows is different in significant ways. Nonetheless, even if using multiple SQLite instances on Windows platforms is safe, it is still wasteful. And sad.

SQLCipher or SEE

Mobile devices get lost or stolen. A significant portion of mobile app developers want their data encrypted on the device. And the SQLite instance built-in to iOS and Android is plain, with no support for encryption.

The usual solution to this problem is to use SQLCipher (open source, from Zetetic) or SEE (proprietary, from the authors of SQLite). Both of these are drop-in replacements for SQLite.

In other words, this is yet another reason the OS-provided SQLite library might not be sufficient.

SQLite compilation options

SQLite can be compiled in a lot of different ways. Do you want the full-text-search feature? Do you want foreign keys to be default on or off? What do you want the default thread-safety mode to be? Do you need the column metadata feature? Do you need ICU for full Unicode support in collations? The list goes on and on.

Did Apple or Google compile SQLite with the exact set of build options your app needs? Maybe. Or maybe your app just needs to have its own.

Adding a SQLite instance without knowing it

Another way to get two SQLite instances is to add a component or library which includes one. Even if you don't know.

For example, the client side of Zumero (our mobile SQL sync product) needs to call SQLite. Should it bundle a SQLite library? Or should it always call the one in the mobile OS (when available)?

Some earlier versions of the Zumero client SDK included a SQLite instance in our Xamarin component builds. Because, why on earth would we want our code running against the archaic version of SQLite provided by Apple and Google?

And then we had a customer run into this exact problem. They called Zumero for sync. And they used Mono.Data.Sqlite for building their app.

Now we ship builds which contain no SQLite library instance, because it minimizes the likelihood of this kind of accident happening.

There are all kinds of libraries and components and SDKs out there which build on SQLite. Are they calling the instance provided by the OS? Or are they bundling one? Do you even know?

So maybe app developers should just be more careful

Knee-jerk reaction: Yes, absolutely.

Better answer: Certainly not.

App developers don't want to think about this stuff. It's a bit of esoterica that nobody cares about. Most people who started reading this blog entry gave up several paragraphs ago. The ones that are still here (both of you) are wondering why you are still reading when right now there are seven cable channels showing a rerun of Law and Order.

An increasingly easy accident

The multiple SQLite scenario is sounding less far-fetched all the time. SQLite is now one of the most widely deployed pieces of software in history. It is incredibly ubiquitous, and still growing. And people love to build abstractions on top of it.

This problem is going to get more and more common.

And it can have very significant consequences for end users.

Think of it this way

The following requirements are very typical:

  • App developers want to be using a current version of SQLite (because DRH has actually been working for the last two years).

  • App developers want their SQLite data on the mobile device to be encrypted (because even grown-ups lose mobile devices).

  • App developers want to be using the same version of SQLite on all of their mobile app platforms (because it simplifies testing).

  • App developers want no risk of data corruption (because end users don't like that kind of thing).

  • App developers want to work with abstractions, also-known-as ORMs and sync tools, also-known-as things that makes their lives easier (because writing mobile apps is insanely expensive and it is important to reduce development costs).

  • App developers want to NOT have to think about anything in this blog entry (because they are paid to focus on their actual business, which is medicine or rental cars or construction, and it's 2014, so they shouldn't have to spend any time on the ramifications of quirky POSIX file locking).

Those requirements are not just typical, they are reasonable. To ask app developers to give up any of these things would be absurd.

And right now, there is NO WAY to satisfy all the requirements above. In the terminology of high school math, this is a system of equations with no solution.

To be fair

The last several weeks of "the NuGet package is almost ready" are also due to some reasons I can't blame Apple or Google or POSIX for.

When I started working on SQLitePCL.raw, I didn't know nearly enough about MSBuild or NuGet. Anything involving native code with NuGet is pretty tricky. I've spent time climbing the learning curve. My particular way of learning new technologies is to write the code three times. The commit history on GitHub contains the whole story.

Ramifications for SQLitePCL.raw

I want users of my SQLite PCL to have a great experience, so I'm spending [perhaps too much] time trying to find the sweetest subsets of the requirements above.

For example: C++/CX is actually pretty cool. I can build a single WP8 component DLL which is visible to C# while statically building SQLite itself inside. Fewer pieces. Fewer dependencies. Nice. But if anything else in the app needs direct access to SQLite, they'll have to include another instance of the library. Yuck.

Another example: I see [at least] three reasonable choices for iOS:

  • Use the SQLite provided by iOS. It's a shared library. Access it with P/Invoke, DllImport("sqlite3").

  • Bundle the latest SQLite. DllImport("__Internal"), and embed a sqlite3.a as a resource and use the MonoTouch LinkWith attribute.

  • Use the Xamarin SQLCipher component. DllImport("__Internal"), but don't bundle anything, relying on the presence of the SQLCipher component to make the link succeed.

Which one should the NuGet package assume that people want? How do people that prefer the others get a path that Just Works?

So, Eric, when will the SQLitePCL.raw NuGet package be ready

Soon. ;-)

Bottom line

"I don't know the key to success, but the key to failure is trying to please everybody." -- Bill Cosby



Thoughts on the Bait and Switch PCL Trick

This blog entry started its life as comments on The Bait and Switch PCL Trick, an excellent blog entry by Paul Betts.

A little background

I've been working on a PCL for SQLite. It's on GitHub:

Concerns about the bait-and-switch approach

(I assume you've read the Paul Betts blog entry as well as the Daniel Plaisted blog entry linked therein, so I'm not going to re-describe the Bait and Switch concept from square 1.)

I first heard about "the PCL pattern now known as Bait and Switch" several weeks ago in tweets by Miguel de Icaza which mentioned the efforts of Xamarin guy James Montemagno.

And then the Paul Betts blog entry showed up, which named the pattern Bait and Switch and proclaimed it to be The Right Way.

Initially, I had concerns. The technique feels fragile.

(The words "Bait and Switch" don't really contribute to this feeling (at least for me), especially after seeing Daniel Plaisted on Twitter saying that "the bait-and-switch concept is fundamental to PCLs, because mscorlib is different everywhere". Makes sense.)

But the technique still seems deserving of the word "Trick". The reference assembly (the actual PCL assembly, the one with a PCL profile) and the platform assemblies don't have much tying them together. It seems like the trick is something that just happens to work, almost by accident, and therefore might stop working later, even though I have no reason to believe that it will.

I'm setting this worry aside because the other PCL approach (dependency injection) contains a trick which feels just as fragile. The standard hack is for the PCL assembly to use reflection to locate and load and instantiate the platform assembly. This approach doesn't exactly ooze with robustness either. And in fact, the Bait and Switch technique seems to work fine on Xamarin whereas the reflection-load technique does not. So, both of these approaches might seem fragile, but one of them definitely is.

Two kinds of PCLs

If we accept the idea that the Bait and Switch pattern is The Right Way, then there are several corollaries that follow.

There are two kinds of a PCLs:

  1. A PCL which contains only non-portable code.

  2. A PCL which contains only portable code.

There are no PCLs which contain a mixture of the two.

(To be more precise, I am defining "portable code" as code which is compiled under a PCL profile, and "non-portable code" is code which is, er, not compiled under a PCL profile. I am not claiming that a PCL of type (1) is prohibited from using things like System.String.Length. :-) )

I find it helpful put some separation between these two very different kinds of PCLs and to explain them as follows. Perhaps others will find these explanations helpful as well.

Wrapper PCLs

For the purpose of this note, I will refer to PCL type (1) as a Wrapper PCL.

A Wrapper PCL is a PCL which exists to provide a portable wrapper around something that is not portable.

A Wrapper PCL always uses the Bait and Switch pattern.

We can refer to a Wrapper PCL in the singular, but it is actually multiple assemblies. There is the PCL assembly itself (the one that claims to be portable according to some profile). And there are platform assemblies, non-portable assemblies which play the role of the PCL assembly in real life scenarios.

Any consumer of a Wrapper PCL would need to reference exactly one of its assemblies. A library can reference the PCL assembly itself. An app must reference one of the platform assemblies.

With a Wrapper PCL, the PCL assembly itself exists only so that libraries can have something to reference. It is nothing more than a placeholder.

With a Wrapper PCL, the PCL assembly itself contains only stub functions. Those stub functions never get executed. They might do nothing. They might throw. They might launch nethack. Nobody knows. Nobody cares.

A Wrapper PCL, despite the presence of "Portable Class Library" in its name, contains no portable code that ever gets used.

A Wrapper PCL claims a PCL profile (such as profile 78, for example), but that profile claim is mostly just a list of which platform assemblies are supposed to be present in the package.

With a Wrapper PCL, we don't care at all about the fact that our compiler and tooling can gripe about the use of non-portable things (except of course to complain if somebody actually does try to launch nethack from a stub). There is no portable code being written, so we don't need the compiler evaluating our ability to write things that are portable.

There is no special tooling to help ensure that all of the assemblies within a Wrapper PCL actually do implement the same interface, although it's not hard to figure out ways of making this happen by code sharing across all the assemblies.

Feature PCL

As for PCL type (2) in the list above, I will refer to this as a Feature PCL.

A Feature PCL is a library of portable code. I'll generously assume that it has some reason to exist. It therefore provides some sort of functionality. Or a feature. So I call it a Feature PCL.

A Feature PCL does not use Bait and Switch. Or Dependency Injection. It has no need for such things.

A Feature PCL is not allowed to have non-portable code in it. If it did, it would not be a PCL. Or it would violate The Right Way, so its offending non-portable portions should be abstracted out into a separate PCL using the Bait and Switch pattern.

A Feature PCL is allowed to use things that are allowed by its profile. Or it is allowed to reference other PCLs that are compatible with that profile. It is not allowed to reference anything else. The compiler and tooling help ensure this.

A Feature PCL is [conceptually] just one assembly. It might be implemented using multiple assemblies. That's fine. What is does NOT have are a set of assemblies that are designed to be alternatives.

Bottom line

I'm still exploring and experimenting. Feedback welcome, but my blog software doesn't support comments. I'm @eric_sink on Twitter. I'll also keep an eye on the Paul Betts blog entry for any further discussion that happens there.




(This entry is part of a series. The audience: SQL Server developers. The topic: SQLite on mobile devices.)

Well, Actually

First, go read this blog entry by Miguel de Icaza. Right now. I'll wait.

Welcome back. Now let me apologize. I don't want to be a pedantic jerk who quibbles about minor details. But the topic here is Unicode, so there really is no other way.

All Unicode, all the time

The relevant difference with SQLite is easy to describe:

  • In the world of Windows and SQL Server, you have all kinds of possible code pages.

  • In SQLite, everything is Unicode.

But if you don't have much context on these issues, I haven't really told you much. Let's go further.

But let's not go too far

I don't want to rewrite articles that have already been written quite well. So you should probably also go read this blog entry by Joel Spolsky. I'll be here when you get back.

OK, now let's get started

SQL Server has two basic ways you store text:

  • You can use the char/varchar types, which can be used with one of several collations, each of which implies a specific code page, which implies a specific character encoding.

  • Or you can use the nchar/nvarchar types (note the extra 'n'), which are Unicode.

SQLite has no such distinction. All text in SQLite is Unicode.

What the heck is Unicode again?

It's a character set: a collection of characters, each with a number that can be used to refer to it.

More specifically, it's the only character set which is [trying to be] complete. If you choose any character set or encoding which is not Unicode, there will be characters you cannot use.

And what's an encoding?

Saying that SQLite uses Unicode doesn't tell you how the text is actually represented. Unicode is not an encoding. It is more abstract than that. There are lots of different ways of representing Unicode as bytes.

Microsoft's sad history with Unicode

In the Windows and SQL Server world, there is a long history of encoding Unicode in 16-bit-ish ways. Originally, this was UCS-2, a simple encoding which represents each character as a 16-bit number. But then the Unicode consortium realized that 16 bits are not enough, so they expanded the space to 32 bits. This left Microsoft in an awkward spot. UCS-2 is a fundamentally defective encoding of Unicode, since there are many characters in Unicode that simply cannot be represented.

If the goal of Unicode is to be complete, it is reasonable to say that, well actually, UCS-2 is not Unicode.

The conceptual replacement for UCS-2 is to use 32 bits for every character. This encoding is called UCS-4 or UTF-32. But now the wasted space for storing a simple English string is getting out of hand. Switching the complete works of Arthur Conan Doyle from ASCII (which is also an encoding) to UCS-4 would take four times as much space.

Gradually, the world seems to be adopting UTF-8 as the most popular Unicode encoding. This is a variable width encoding. Sometimes a single character is represented with just one byte. Sometimes it needs more. That's very unfortunate, but the only fixed width alternative is UCS-4, which is also very unfortunate. Choose which problem you prefer, but keep in mind that almost everybody has chosen to accept the problems of UTF-8.

But Microsoft has so much history with UCS-2 that transitioning everything to UTF-8 would be really hard. So they have been moving from UCS-2 to UTF-16, which is basically a variable width encoding built around a 16-bit unit instead of an 8-bit unit. UTF-16 is approximately the worst correct way of representing Unicode, unless you have invested billions of dollars in the fundamentally broken UCS-2, in which case UTF-16 is a pretty awesome way out of the mess you ended up in.

Just remember that if you're going out tonight to a club for pedantic nerds and you want to impress someone, you've got to keep the terminology straight:

  • Unicode is an abstraction, not an encoding, not a code page, not a data format, and not a font.

  • Saying your text is Unicode says nothing about how it is represented. It might be UTF-8. It might UTF-16. It might be code point numbers handwritten on Post-It notes stuck on the wall. All of these are valid representations of Unicode.

  • If you ever say anything to suggest that you think Unicode is 16 bits per character, you will be identified as clueless.

  • If you say that your text is stored in Unicode, you are not entirely incorrect, but people will wonder about whether you really know the difference between Unicode and the encodings of same.


SQLite always uses Unicode to represent text.

(Hopefully you are now screaming at me saying, "Yeah, but which encoding?!?")

The best answer to this question is: SQLite uses UTF-8. Forget about everything else.

A more correct answer to this question is: SQLite uses UTF-8 but also supports UTF-16.

Either way, there is no distinction between char and nchar. There is no way to save storage space in a column by realizing that you only use lower case english characters so it's safe to use char instead of nchar. There are no code pages. There is no way to move your Shift JIS data into SQLite without converting it to Unicode (or storing it as blobs, I suppose).


Microsoft has done a lot of things right, but its history with Unicode is very unfortunate. And it's not entirely their fault. They adopted Unicode early and it changed underneath them.

With respect to its emphasis on UTF-8, SQLite is far more typical of most non-Microsoft software today.



Data Types

(This entry is part of a series. The audience: SQL Server developers. The topic: SQLite on mobile devices.)

Different types of, er, types

At the SQL language level, the biggest difference with SQLite is the way it deals with data types. There are three main differences to be aware of:

  1. There are only a few types

  2. And types are dynamic

  3. (But not entirely, because they have affinity)

  4. And type declarations are weird

Okay, so actually that's FOUR things, not three. But the third one doesn't really count, so I'm not feeling terribly obligated to cursor all the way back up to the top just to fix the word "three". Let's keep moving.

Only a few types

SQLite values can be one of the following types:


  • REAL

  • TEXT

  • BLOB

The following table shows roughly how these compare to SQL Server types:

SQL Server SQLite Notes
INTEGER In SQLite, all integers are up to 64 bits wide (like bigint), but smaller values are stored more efficiently.
REAL In SQLite, all floating point numbers are 64 bits wide.
TEXT In SQLite, all strings are Unicode, and it doesn't care about widths on TEXT columns.
BLOB Width doesn't matter here either.
INTEGER ? These are problematic, as SQLite 3 does not have a fixed point type. (In Zumero, we handle synchronization of these by mapping them to INTEGER and handling the scaling.)
(your choice) SQLite has no data types for dates or times. However, it does have a rich set of built-in functions for manipulating date/time values represented as text (ISO-8601 format), integer (unix time) or real (Julian day).

Types are dynamic

In SQL Server, the columns in a table are strictly typed. If you define a column to be of type smallint, then every value in that column must be a 16 bit signed integer.

In contrast, SQLite's approach might be called "dynamic typing". Quoting from its own documentation: "In SQLite, the datatype of a value is associated with the value itself, not with its container."

For example, the following code will fail on SQL Server:

CREATE TABLE [foo] (a smallint);
INSERT INTO [foo] (a) VALUES (3);
INSERT INTO [foo] (a) VALUES (3.14);
INSERT INTO [foo] (a) VALUES ('pi');

But on SQLite, it will succeed. The value in the first row is an INTEGER. The value in the second row is a REAL. The value in the third row is a TEXT string.

sqlite> SELECT a, typeof(a) FROM foo;

The column [a] is a container that simply doesn't care what you place in it.

Type affinity

Well, actually, it does care. A little.

A SQLite column does not have a type requirement, but it does have a type preference, called an affinity. I'm not going to reiterate the type affinity rules from the SQLite website here. Suffice it to say that sometimes SQLite will change the type of a value to fit match the affinity of the column, but you probably don't need to know this, because:

  • If you declare of column of type TEXT and always insert TEXT into it, nothing weird will happen.

  • If you declare of column of type INTEGER and always insert INTEGER into it, nothing weird will happen.

  • If you declare of column of type REAL and always insert REAL into it, nothing weird will happen.

In other words, just store values of the type that matches the column. This is the way you usually do things anyway.

Type declarations are weird

In a column declaration, SQLite has a rather funky set of rules for how it parses the type. It uses these rules to try its very best to Do The Right Thing when somebody ports SQL code from another database.

For example, all of the columns in the following table end up with TEXT affinity, which is probably what was intended:

[a] varchar(50),
[b] char(5),
[c] nchar,
[d] nvarchar(5),
[e] nvarchar(max),
[f] text

But in some cases, the rules are funky. Here are more declarations which all end up with TEXT affinity, even though none of them look right:

[a] characters,
[b] textish,
[c] charbroiled,
[d] context

And if you want to be absurd, SQLite will let you. Here's an example of a declaration of a column with INTEGER affinity:

[d] My wife and I went to Copenhagen a couple weeks ago
    to celebrate our wedding anniversary 
    and I also attended SQL Saturday while I there
    and by the way we saw
    Captain America The Winter Soldier 
    there as well which means I got to see it 
    before all my friends back here in Illinois 
    and the main reason this blog entry is late is 
    because I spent most of the following week gloating

SQLite will accept nearly anything as a type name. Column [d] ends up being an INTEGER because its ridiculously long type name contains the characters "INT" (in "Winter Soldier").

Perhaps we can agree that this "feature" could be easily abused.

There are only four types anyway. Pick a name for each type and stick to it. Once again, the official names are:


  • REAL

  • TEXT

  • BLOB

(If you want a little more latitude, you can use INT for INTEGER. Or VARCHAR for TEXT. But don't stray very far, mkay?)

Pretend like these are the only four things that SQLite will allow, and then it will never surprise you.


SQLite handles types very differently from SQL Server, but its approach is mostly a superset of your existing habits. The differences explained above might look like a big deal, but in practice, they probably won't affect you all that much.