2016-06-15 12:00:00
SQLite and Android N
TLDR
The upcoming release of Android N is going to cause problems for many apps that use SQLite. In some cases, these problems include an increased risk of data corruption.
History
SQLite is an awesome and massively popular database library. It is used every day by billions of people. If you are keeping a list of the Top Ten Coolest Software Projects Ever, SQLite should be on the list.
Many mobile apps use SQLite in one fashion or another. Maybe the developers of the app used the SQLite library directly. Or maybe they used another component or library that builds on SQLite.
SQLite is a library, so the traditional way to use it is to just link it into your application. For example, on a platform like Windows Phone 8.1, the app developer simply bundles the SQLite library as part of their app.
But iOS and Android have a SQLite library built-in to the platform. This is convenient, because developers do not need to bundle a SQLite library with their software.
However
The SQLite library that comes with Android is actually not intended to be used except through the android.database.sqlite Java classes. If you are accessing this library directly, you are actually breaking the rules.
And the problem is
Beginning with Android N, these rules are going to be enforced.
If your app is using the system SQLite library without using the Java wrapper, it will not be compatible with Android N.
Does your app have this problem?
If your app is breaking the rules, you *probably* know it. But you might not.
I suppose most Android developers use Java. Any app which is only using android.database.sqlite should be fine.
But if you are using Xamarin, it is rather more likely that your app is breaking the rules. Many folks in the Xamarin community tend to assume that "SQLite is part of the platform, so you can just call it".
Xamarin.Android 6.1 includes a fix for this problem for Mono.Data.Sqlite (see their release notes).
However, that is not the only way of accessing SQLite in the .NET/Xamarin world. In fact, I daresay it is one of the less common ways.
Perhaps the most popular SQLite wrapper is sqlite-net (GitHub). If you are using this library on Android and not taking the extra steps to bundle a SQLite library, your app will break on Android N.
Are you using Akavache? Or Couchbase Lite? Both of these libraries use SQLite under the hood (by way of SQLitePCL.raw, which I maintain), so your app will need to be updated to work on Android N.
There are probably dozens of other examples. GitHub says the sqlite-net library has 857 forks. Are you using one of those? Do you use the MvvmCross SQLite plugin? Do any of the components or libraries in your app make use of SQLite without you being aware of it?
And the Xamarin community is obviously not the whole story. There are dozens of other ways to build mobile apps. I can think of PhoneGap/Cordova, Alpha Anywhere, Telerik NativeScript, and Corona, just off the top of my head. How many of these environments (or their surrounding ecosystems) provide (perhaps accidentally) a rule-breaking way to access the Android system SQLite? I don't know.
What I *do* know is that even Java developers might have a problem.
It's even worse than that
Above, I said: "Any app which is only using android.database.sqlite should be fine." The key word here is "only". If you are using the Java classes but also have other code (perhaps some other library) that accesses the system SQLite, then you have the problems described above. But you also have another problem.
To fix this, you are going to have to modify that "other code" to stop accessing the system SQLite library directly. One way to do this is to change the other code to call through android.database.sqlite. But that might be a lot of work. Or that other code might be a 3rd party library that you do not maintain. So you are probably interested in an easier solution.
Why not just bundle another instance of the SQLite library into your app? This is what people who use sqlite-net on Xamarin will need to do, so it should make sense in this case too, right? Unfortunately, no.
What will happen here is that your android.database.sqlite code will continue using the system SQLite library, and your "other code" will use the second instance of the SQLite library that you bundled with your app. So your app will have two instances of the SQLite library. And this is Very Bad.
The Multiple SQLite Problem
Basically, having multiple copies of SQLite linked into the same appliication can cause data corruption. For more info, see this page on sqlite.org. And also the related blog entry I wrote back in 2014.
You really, really do not want to have two instances of the SQLite library in your app.
Zumero
One example of a library which is going to have this problem is our own Zumero Client SDK. The early versions of our sync library bundled a copy of the SQLite library, to follow the rules. But later, to avoid possible data corruption from The Multiple SQLite Problem, we changed it to call the system SQLite directly. So, although I might like to claim we did it for a decent reason, our library breaks the rules, and we did it knowingly. All Android apps using Zumero will need to be updated for Android N. A new release of the Zumero Client SDK, containing a solution to this problem, is under development and will be released soon-ish.
Informed consent?
I really cannot recommend that you have two instances of the SQLite library in your app. The possibility of corruption is quite real. One of our developers created an example project to demonstrate this.
But for the sake of completeness, I will mention that it might be possible to prevent the corruption by ensuring that only one instance of the SQLite library is accessing a SQLite file at any given time. In other words, you could build your own layer of locking on top of any code that uses SQLite.
Only you can decide if this risk is worth it. I cannot feel good about sending anyone down that path.
Stop using android.database.sqlite?
It also makes this blog entry somewhat more complete for me to mention that changing your "other code" to go through android.database.sqlite is not your only option. You might prefer to leave your "other code" unchanged and rewrite the stuff that uses android.database.sqlite, ending up with both sets of code using one single instance of SQLite that is bundled with your app.
A Lament
Life was better when there were two kinds of platforms, those that include SQLite, and those that do not. Instead, we now have this third category of platforms that "previously included SQLite, but now they don't, but they kinda still do, but not really".
An open letter to somebody at Google
It is so tempting to blame you for this, but that that would be unfair. I fully admit that those of us who broke the rules have no moral high ground at all.
But it also true that because of the multiple SQLite problem, and the sheer quantity of apps that use the Android system SQLite directly, enforcing the rules now is the best way to maximize the possibility of Android apps that break or experience data corruption.
Would it really be so bad to include libsqlite in the NDK?