
SQLite and Android N (2016) - luu
http://ericsink.com/entries/sqlite_android_n.html
======
izacus
Ugh, if you were linking against system sqlite you had problems RIGHT NOW
before Android N.

sqlite.so isn't part of public Android API surface, which means the OEMs will
regularly replace it, break it, compile it with strange options and deal a lot
of pain to you and your users.

So if you weren't shipping your own native SQLite in NDK/Xamarin you already
had problems, you just maybe didn't notice them.

Android N is now actually actively preventing linking against system libraries
which aren't part of public API surface, which is good - it'll result in less
developers doing dumb things which break on minor updates.

~~~
pawadu
> it'll result in less developers doing dumb things which break on minor
> updates.

You can never stop the Facebook developers doing dumb things on Android.

Google have tried for years, but Facebook always manages to find new ways to
fuck up.

~~~
oblio
They're not dumb, they're challenges! Preferably challenges solved by creating
new VMs, compilers and other interesting development projects ;)

~~~
pawadu
Yeah, challenges Facebook themselves created by writing the most bloat app
ever written

[https://www.facebook.com/notes/facebook-engineering/under-
th...](https://www.facebook.com/notes/facebook-engineering/under-the-hood-
dalvik-patch-for-facebook-for-android/10151345597798920)

~~~
lxgr
I've noticed they are now doing a similar thing now for push notifications.

Instead of using the platform-provided, battery efficient GCM (now FCM), they
insist on using their own MQTT push solution.

Not only does this require a constantly running background service and a TCP
socket which has to be reopened on every connectivity change (e.g. a switch
from wi-fi to mobile data): They do it for every single one of their apps.

My phone has at least three of them (Instagram, Facebook, Messsenger), and I'm
pretty sure that this is a major reason for battery life problems on Android.

~~~
yegle
You can at least uninstall Facebook.app: use m.facebook.com in chrome, you can
even get push notification via Chrome.

~~~
j_s
Also: [https://mbasic.facebook.com](https://mbasic.facebook.com)

------
iainmerrick
The article finishes by asking: _Would it really be so bad to include
libsqlite in the NDK?_ Good question!

Why is the NDK so underpowered? Why are they so reluctant (or unable) to
publish some useful, stable native APIs? It's missing things like curl,
sqlite, icu, OpenSSL. Most of these are built into the OS but not accessible
via the NDK. (OpenSSL isn't a great library, I know, but it's patchable. And
they could start now with something better.)

The _only_ useful native third-party libraries you get are: zlib, OpenGL ES,
OpenSL ES.

Apart from that, there are a bunch of wrappers for a small subset of Java APIs
(AssetManager, Bitmap and so on). These are tedious to use, and seem to have
been written by hand so in some cases they're buggy -- for example, incorrect
string handling in AStorageManager:
[https://code.google.com/p/android/issues/detail?id=41983](https://code.google.com/p/android/issues/detail?id=41983)

~~~
izacus
Most likely Google (and OEMs) want to keep the flexibility of changing
implementations without having to code and patch those libraries you listed
forever.

NDK team doesn't look like it's expansive or prioritized at all, so I doubt
they have resources or will to do that. TBH I'd rather they focus at improving
the tooling and debugging, we can ship our own libraries with our own options
compiled in. Heck even on iOS we have to ship a custom SQLite to get all the
needed capability.

~~~
corysama
Can anybody speak to the current size of the NDK team? I met with one of them
many years ago when the team had only two (2) full-time engineers.

Google has one of the largest software engineering populations of any company
in the world. It supports one of the largest developer ecosystems in the
world. Games are the largest catagory in their ecosystem. 2 engineers
supporting the NDK...

~~~
iainmerrick
Yeah, that's exactly what I was getting at. They don't seem to take the NDK
seriously. It's very weird.

~~~
pjmlp
It is not weird if you think they would rather not have it at all.

It wasn't there until 2.2, only got added due to community pressure and they
always did the minimum amount of work.

When the migration to Android Studio was announced, we got left with no
migration plan.

Only after JET Brains announced CLion, almost 2 years later, did they announce
the migration to Studio.

Likewise they moved into Gradle without plan for C NDK support and it took
three generations of build tools until they decided to settle on CMake.

~~~
iainmerrick
Yes! But why?

As an earlier commenter mentioned, games are one of the biggest app categories
and many games rely on the NDK. Pro audio _could_ be another big category, but
I think Apple has the market sewn up there because Android is still way
behind.

~~~
pjmlp
I guess, because a few key figures come from Sun's Java team and WebOS, with
politics having a big role.

------
unsoundInput
Kind of related: There is an outstanding request [0] on the Android issue
tracker to offer an official support/compat library for Android's sqlite
bindings, that would bridge Android's API (in a different java package) to a
sqlite binary that you provide.

There are already implementations [1][2] of this, but because it is not
provided by the Android team it is hardly supported by ORMs, libraries, etc.

[0]
[https://code.google.com/p/android/issues/detail?id=202658](https://code.google.com/p/android/issues/detail?id=202658)

[1]
[https://www.sqlite.org/android/doc/trunk/www/index.wiki](https://www.sqlite.org/android/doc/trunk/www/index.wiki)

[2] [https://github.com/requery/sqlite-
android](https://github.com/requery/sqlite-android)

~~~
kiallmacinnes
Does this not just hit the "The Multiple SQLite Problem" described in the
article?

~~~
unsoundInput
I don't think so but I might also interpret the article wrong.

The problem - from what I understand - is that you used to be able to use the
systems sqlite.so to access databases owned by your app from the native
environment. With Android N you are no longer able to do this, you need to
ship your own sqlite binary.

This can lead to problems when you want to access the same sqlite files from
both the Android platform APIs _and_ native (e.g. use it from native for
business logic, debug it with a tool like Stetho [0] that uses
android.database.sqlite) because of a version mismatch.

Would Google offer (a copy of) the Android API with the ability to plug in a
different sqlite-compatible binaries, it would very likely find broad
adoption. Then the problem stated by the article could be solved by shipping
the app with a sqlite build that is used by both the native code and plugged
into the compat library.

[0] [https://facebook.github.io/stetho/](https://facebook.github.io/stetho/)

------
j_s
Android and SQLite have had some adventures: pre-Honeycomb (v3/API 11/2011),
corrupted databases were automagically deleted by the OS API used to open
them!

SQLite was also shaking out a (very) few bugs out during this time period;
pretty sure this auto-delete implementation wiped any chance of semi-easily
recovering my first phone's SMS db.

[https://stackoverflow.com/questions/7764943/what-can-be-
done...](https://stackoverflow.com/questions/7764943/what-can-be-done-about-
the-fact-that-android-automatically-deletes-corrupt-sqlit)

"8\. Bugs in SQLite" (2013)

[https://www.sqlite.org/howtocorrupt.html](https://www.sqlite.org/howtocorrupt.html)

------
amluto
One big danger seems to be that POSIX locks don't like having two fds for the
same file in the same process. But Linux has a newish kind of lock that is
backwards-compatible and works better. I would love for SQLite to use it.

Does SQLite accept patches?

~~~
kiallmacinnes
Patches are accepted, though.. SQLite is, by all accounts I've read, a very
well written and tested piece of software, and I'd be surprised if there was
an easy and safe win like this which hasn't already been proposed /
considered.

Though, my guess shouldn't stop you looking for previous suggestions to
implement this, and if there are none, submitting it yourself :)

~~~
stuaxo
It's amazing how many quick wins are to be had in open source if you have the
time to patch yourself.

------
verytrivial
Wouldn't symbol versioning[1] allow more than one SQLite in a Java process,
even if it is just to munge the system shipped symbols into an Android
specific subspace? (I do this, but with a large C++ application so I might be
missing some finer details).

1\. [https://refspecs.linuxfoundation.org/LSB_4.0.0/LSB-Core-
gene...](https://refspecs.linuxfoundation.org/LSB_4.0.0/LSB-Core-generic/LSB-
Core-generic/symversion.html)

------
jeremy_zumero
Here's the Android bug logged, which includes a repro app.

[https://code.google.com/p/android/issues/detail?id=213433](https://code.google.com/p/android/issues/detail?id=213433)

Although I mentioned using dlopen and dlsym to find the SQLite functions in
the current process, our actual production solution was to use JNI to call the
android.database.sqlite classes.

------
donatj
Having had Nougat for a while now I have not seen any issues as an app
consumer.

~~~
BoorishBears
Because using the NDK only solves a very specific set of problems (and brings
it's own can of worms in the wild), not that many apps will directly be
affected by this.

------
vadiml
I would suggest another approach: 1) Import the source android.database.sqlite
package while renaming it to my.database.sqlite 2) modify it trivially to load
your own instance of sqlite....

------
Zigurd
How many of these accesses of sqlite.so are the result of using cross platform
tools? All of them? Is there any reason to do this otherwise?

~~~
gcp
If you have native code that wants to access a database, not round trip though
Java, and not incur the size hit of shipping a separate copy OR (and this is
key) want your Java code to be able to read the same database without writing
your own Java->JNI SQLite wrapper.

~~~
Zigurd
Unless you are hammering on a jni in an inner loop, you won't notice.

~~~
iainmerrick
Another problem is explained in the feature request that another commenter
linked to:
[https://code.google.com/p/android/issues/detail?id=202658](https://code.google.com/p/android/issues/detail?id=202658)

You have no idea what version of sqlite the OS might be using, so you can't
rely on consistent behavior. In many cases the vendor will have tinkered with
it and messed it up. Comprehensively testing on all Android devices is
basically impossible because there are so many models out there.

~~~
Zigurd
That sounds like an argument against direct access to sqlite.so.

~~~
iainmerrick
Hmm, yeah, but only partly--

The reason they can tinker with it is that it's not public; if it _were_
public it would (hopefully) be better-defined, locked down and stable, part of
the Android compatibility test suite.

That feature request would let people supply their own sqlite.so, and also
redirect the Java API to use it. Seems like a very promising approach.

