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

What?

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

Is this ready for use on mission-critical applications?

Hardly.

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?

3.8.4.3

How was the bundled SQLite library compiled?

With:

  • SQLITE_ENABLE_FTS4

  • SQLITE_ENABLE_FTS3_PARENTHESIS

  • SQLITE_ENABLE_COLUMN_METADATA

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

No.

How do I find this package?

Direct link: https://www.nuget.org/packages/SQLitePCL.raw_basic

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 visualstudiogallery.msdn.microsoft.com.

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.

Seriously?

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.

Windows

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:

https://github.com/ericsink/SQLitePCL.raw

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.

 

 

Unicode

(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

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).

Summary

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:

  • INTEGER

  • REAL

  • TEXT

  • BLOB

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

SQL Server SQLite Notes
tinyint
smallint
int
bigint
bit
INTEGER In SQLite, all integers are up to 64 bits wide (like bigint), but smaller values are stored more efficiently.
real
float
REAL In SQLite, all floating point numbers are 64 bits wide.
char
varchar
nchar
nvarchar
text
ntext
TEXT In SQLite, all strings are Unicode, and it doesn't care about widths on TEXT columns.
binary
varbinary
image
BLOB Width doesn't matter here either.
decimal
numeric
money
smallmoney
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.)
date
datetime
datetime2
datetimeoffset
smalldatetime
time
(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;
3|integer
3.14|real
pi|text

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:

CREATE TABLE [foo] 
(
[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:

CREATE TABLE [foo] 
(
[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:

CREATE TABLE [foo] 
(
[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:

  • INTEGER

  • 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.

Summary

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.

 

 

Foreign Keys

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


I can't use SQLite! It doesn't support foreign keys!

Actually, it does. Since version 3.6.19, released in October 2009. Before the first iPad. Windows 7 was not quite out yet. Miley Cyrus was still Hannah Montana.

You're wrong! This code should fail but doesn't!

CREATE TABLE [to] (pk int PRIMARY KEY);
CREATE TABLE [from] (other REFERENCES [to](pk) );
INSERT INTO [from] (other) VALUES (3);

Fair enough. It is easy to get the impression that SQLite still lacks support for foreign keys because the feature is [usually] turned off by default, to preserve backward compatibility.

How to turn SQLite foreign key support on

To enable the enforcement of foreign key constraints, execute the following SQL statement, shortly after you open your SQLite database connection:

PRAGMA foreign_keys = 1;

I always use SQLite with foreign keys. In fact, I always open SQLite connections with a helper function which (1) opens the SQLite connection and (2) immediately executes the PRAGMA above.

Differences

Once you've got the feature turned on, foreign key support in SQLite is generally similar to what a SQL Server dev would expect. Full documentation is available on the SQLite website, but there are a few things worth highlighting.

  • SQLite supports simple foreign keys (with one column referencing one column), or composite foreign keys (with multiple columns referencing multiple columns).

  • SQLite does support ON DELETE and ON CASCADE.

  • SQLite allows a foreign key constraint to be deferred, which means that its enforcement is postponed until the transaction is committed. This is a handy feature that I really wish SQL Server had.

Shameless, blatantly self-serving commercial remark

Zumero for SQL Server automatically syncs foreign key constraints. Microsoft Sync Framework does not.

So there.

:-)

Summary

PRAGMA foreign_keys = 1; Always.

 

 

UNIQUE NULL

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


Nullable columns in UNIQUE constraints

The following code will cause a unique constraint violation in SQL Server:

CREATE TABLE foo (pk int PRIMARY KEY IDENTITY, x int UNIQUE NULL);
INSERT INTO foo (x) VALUES (1);
INSERT INTO foo (x) VALUES (2);
INSERT INTO foo (x) VALUES (NULL);
INSERT INTO foo (x) VALUES (NULL);

SQL Server doesn't allow the second NULL value to be inserted for column [x].

But SQLite allows this. See Issues with sync and constraints for more discussion of why this kind of thing matters.

Boxes and Cats

The difference arises from the question of whether NULL is considered a value or the absence of a value.

Suppose you have a set of cardboard boxes. Each box can hold one cat. And the cats must be unique.

SQLite thinks of NULL as an empty box. Having multiple empty boxes does not violate the rule about cats being unique.

SQL Server thinks of NULL as the name of a specific cat. A box cannot be empty. And the NULL cat can only be in one box.

If you prefer an explanation with more technical detail and fewer cat pictures, see this page on the SQLite website, which (at the time of this writing) provides a chart of how 12 different SQL implementations handle 8 different situations with NULL values. An additional remark about the amibiguous language in the SQL92 standard appears in the SQLite FAQ.

Solution 1: Don't have the problem

One possible solution is to never have this problem in the first place. Obviously, this issue won't arise if you don't have any nullable columns in unique constraints.

Solution 2: Don't cause the problem

Another option is to just not have any bugs in your app. :-)

Hmmm. That was a bit too flippant to be helpful. Let me try to explain it a different way:

In many (or most?) situations, SQL constraints are used as a way to prevent bugs in upper layers from screwing up the data. In other words, if a constraint gets violated, that's a sign that something went wrong. A constraint violation is typically not used as control flow of the software under normal usage. So, if a constraint violation happens, it is usually a sign of a bug in an upper layer.

SQL constraints also provide guarantees which allow the code in upper layers to be simpler. Those guarantees are part of the contractual agreement between your application code and your database. If the database is promising that values in column [foo] will never be greater than 5, the application code can trust in that promise. It doesn't need to do range checking on every value it gets from the database.

If SQL constraints did not exist, we would simply have to enforce our data integrity rules in application code instead of at the database level. This is not "The SQL Way", but it is certainly possible. The NoSQL folks have to live this way all the time.

So, you have the option of simply accepting the fact that SQLite will not enforce SQL Server's rule about distinct NULLs. If your app never inserts a NULL into that column, you don't need to worry about it. Or you can make your application code do extra checking to make sure.

Solution 3: Use a trigger to make SQLite act like SQL Server

If you want SQLite to enforce distinct NULLs at the database level in the same way as SQL Server does, you can get that behavior by adding triggers to your SQLite tables. For example:

CREATE TRIGGER "uniqnull" BEFORE INSERT ON "foo" WHEN ( (NEW."x" IS NULL) )
BEGIN

SELECT RAISE(ABORT, 'constraint violation: unique null')
WHERE EXISTS (SELECT 1 FROM "foo" c WHERE ( (c."x" IS NEW."x") ));

END

The syntax of this trigger probably looks strange to you. I'll be writing about the differences between SQL Server triggers and SQLite triggers in another entry in this series.

This trigger will impact performance. You can decide if the tradeoff is worth it for your application. Measure it.

(When Zumero for SQL Server encounters a nullable column in a unique constraint, it automatically adds triggers like this one to the corresponding SQLite table on the client side. Those triggers include a comment about possible performance impact and suggesting that the trigger can simply be dropped if the other tradeoff is preferable.)

Summary

SQLite handles nullable columns in unique constraints differently from SQL Server. This distinction may never cause you problems, but if it does, you have several options for dealing with it.