Better Android sqlite database library

[brodybits]

The built-in Android database library had some major deficiencies in the past and has gone through some updates. Unfortunately, some people have reported some issues with locking and also losing data (see #193), for which a workaround has been published. The workaround is basically a real kludge that simply closes and reopens the database whenever a transaction is committed (not provided for the case of db.executeSql()). But someone was still having problems even with the workaround in place. I find this condition is absolutely unacceptable!

Another workaround is to use the version for sqlcipher (with no password key specified) at: https://github.com/brodysoft/Cordova-sqlcipher-adaptor

A better solution would be to include and use sqlite4java (https://code.google.com/p/sqlite4java/), which seems to be much thinner. Unfortunately Google Code will be shut down, so I just raised https://code.google.com/p/sqlite4java/issues/detail?id=75 to migrate that project and also made a clone here: https://sourceforge.net/projects/brody-sqlite4java/

UPDATE: sqlite4java Javadoc here: http://almworks.com/sqlite4java/javadoc/com/almworks/sqlite4java/package-summary.html

[brodybits]

NOTE: unfortunately the sqlite4java project does not have ICU-UNICODE string manipulation support integrated, unlike the newer versions of the standard Android SQLite and SQLCipher for Android libraries.

[MetaMemoryT]

The ultimate android sqlite library would be this one:

https://github.com/moneymanagerex/SQLite-Android-Bindings

because it builds a custom sqlite with no limitations

You can even compile your own custom sqlite extensions into it.

[mladenp]

I am confused with all this different SQLite libraries.
What is the main difference when using sqlite4java and using default device sqlite?

[brodybits]

Some feedback from http://ost.io/@litehelpers/Cordova-sqlite-storage/topics/4 - sqlite4java issue on some Android tablets:

Here is some feedback about my experience using io.litehelpers.cordova.sqlite 0.7.7 with the Cordova CLI and Android devices. I was able to build & ship an app for Android phones and tablets, great! Not yet tried it on iOS, will later.

Please note that I had problems using sqlite4java so I reverted to the "androidDatabaseImplementation: 2" option as described in the documentation. Thank you for documenting this option.

The doc says:

"By default, this plugin uses sqlite4java which is more efficient than the built-in Android database classes. The sqlite4java library consists of a Java part and a NDK part. Unfortunately some app developers have encountered problems using sqlite4java with Ionic and Crosswalk. To use the built-in Android database classes instead:
var db = window.sqlitePlugin.openDatabase({name: "my.db", androidDatabaseImplementation: 2});"

I am not using Ionic and I am not using Crosswalk.

Actually I did not need the "androidDatabaseImplementation: 2" option until I tested on more recent models of the Acer Iconia One 7. So here is the score about that:

Acer Iconia One 7 model B1-730: works without androidDatabaseImplementation: 2
Acer Iconia One 7 model B1-750: requires androidDatabaseImplementation: 2, otherwise db.transaction never calls back the function.

Can't say what fails because I did not trace it in the Java code, but I thought it might be useful to share this info.

Btw, thanks for the good work on this plugin!

NOTE (UPDATE): adding the bug label since I am not 100% happy with the results of using sqlite4java. I am planning to make a lightweight wrapper for the Android version using https://github.com/sqlg/SQLiteGlue-core, which will hopefully solve these integration issues.

[brodybits]

The ultimate android sqlite library would be this one:
https://github.com/moneymanagerex/SQLite-Android-Bindings
because it builds a custom sqlite with no limitations

Thanks @MetaMemoryT, agreed but there are two major problems:

• The Android sqlite database API, which is provided @moneymanagerex/SQLite-Android-Bindings, is not a very good fit for the needs of this plugin:
  • Android db API provides OO model capabilities which is not needed by this plugin;
  • raw query needed to get the total changes (number of rows affected) see Multiple rowsAffected for INSERT (Android) #136
• Lack of an explicit license statement (the main @moneymanagerex is using GPL)

I am confused with all this different SQLite libraries.
What is the main difference when using sqlite4java and using default device sqlite?

@mladenp sorry about the confusion. The built-in Android sqlite database library is not a very good fit for the needs of this plugin as described above. Also, as reported in #193 sometimes people were losing data in newer versions of Android. The sqlite4java library is a lighter-weight library that is a better fit for the needs of this project and is not affected by the problem reported in #193. But since some people also had problems with the sqlite4java library that is why I have made it optional.

If you have any more questions please do not hesitate to ask. You can also ask privately by e-mail to: info@litehelpers.net

[mladenp]

Hey Brody thanks for clearing this. Too bad i use Ionic and plan to implement Crosswalk.
Do you know where can i find info about bugs that can happen in sqlite4java?
Thanks

[brodybits]

NOTE: I heard (at an Amazon AWS conference) that the Amazon Fire-OS should be able to work with the NDK. Needs further research and testing.

[brodybits]

I have now replaced sqlite4java with Android-sqlite-connector which is extremely lightweight. Android-sqlite-connector is written in pure-Java and uses either Android-sqlite-native-driver or Android-sqlcipher-native-driver to get the native sqlite (or sqlcipher) access. It now passes the existing test suite, except for dealing with Unicode NULL (\u0000) characters which should be extremely rare. I wonder whether or not this will solve the problems with Ionic and Crosswalk.

NOTE: I was able to put the native libraries in a jar, with a trick that they have to be in the lib directory and not libs. For reference:

• http://grokbase.com/t/gg/android-ndk/11cpxqnhfy/put-native-so-file-into-jar
• https://code.google.com/p/android/issues/detail?id=17861