Android SQLite R-Tree - How to install module?

Question

http://www.sqlite.org/rtree.html says that the r*tree is "included as part of the amalgamation but is disabled by default" and to enable it "simply compile with the SQLITE_ENABLE_RTREE C-preprocessor macro defined"

Well I want to use R-trees in my android app, but clearly SQLite is all pre-installed etc. Is there a way to enable it on a user's phone/device?

Alternatively, is it possible to use the NDK and the freely available source code for SQLite?

A.J.Bauer · Answer 1 · 2017-06-20T13:09:49.653

June 2017

Go to the: download page and grab sqlite-android-xxxxx.aar
Rename it to .aar instead of .zip
Create a simple project with Android Studio (no need for C++ support)
In the project tree right click app / new / Module select Import .JAR/.AAR Package
Select the renamed file, click finish
In Android Studio go to menu: File / Project structure. Click app (under Modules), select the Dependencies Tab, Add module dependency SQLite-android-xxxxx
Before using any SQLite functions, call: System.loadLibrary("sqliteX");
Replace SQlite imports to org.sqlite.database.sqlite.xxxxxx

In onCreate you can make a quick test with a memory database:

System.loadLibrary("sqliteX");

// get the SQLite version
String query = "select sqlite_version() AS sqlite_version";
SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(":memory:", null);
Cursor cursor = db.rawQuery(query, null);
String sqliteVersion = "";
if (cursor.moveToNext()) {
    sqliteVersion = cursor.getString(0);
}

// do some R*Tree things (this will fail for the standard SQLite)
db.execSQL("CREATE VIRTUAL TABLE demo_index USING rtree(id, minX, maxX, minY, maxY);");
db.execSQL("INSERT INTO demo_index VALUES(1,-80.7749, -80.7747, 35.3776, 35.3778);");
db.execSQL("INSERT INTO demo_index VALUES(2,-81.0, -79.6, 35.0, 36.2);");

cursor = db.rawQuery("SELECT id FROM demo_index WHERE minX>=-81.08 AND maxX<=-80.58 AND minY>=35.00  AND maxY<=35.44;", null);

int id = -1;
if (cursor.moveToFirst()) {
    do {
        id = cursor.getInt(0);
    } while (cursor.moveToNext());
}
db.close();

The links (for all of the above):

score 2 · Accepted Answer · answered Jun 01 '11 at 15:29

2

You can absolutely compile your own version of SQLite. We do this in order to enable the encryption/codec modules from wxSQLite. Take a look at the SQLite source in the Android Git repository. Basically it's as easy and creating a Android.mk with the options (such as SQLITE_ENABLE_RTREE) you'd like enabled. Of course, this will give you a native library. In order to use it you'll need to access it from the NDK or create a wrapper (again, you can look at the Android repository and Java/JNI wrappers to SQLite)

answered Jun 01 '11 at 15:29

NuSkooler

5,391
1
34
58

This is what I ended up doing, but still it seems silly to have SQlite installed twice on each user's device – James Coote Jun 02 '11 at 10:41
I have to ask... doing the same thing here. Built the sqlite3 native no issues there. You have any links to the JNI wrappers to SQLite? I am kinda hoping not to have to write the wrapper from scratch. – George Dec 14 '11 at 15:13
@George: We use SQLite in our native code with a C++ wrapper and never directly from Java -> JNI, sorry. – NuSkooler Dec 15 '11 at 22:06
@NuSkooler I realize you wrote this comment 10 months ago, but could you elaborate on how you distribute your SQLite build? Do users need root access? Can you just ship it with the rest of the app? – Jay Apr 21 '12 at 16:30
Basically we have a pre-built .db file shipped as a resource in the APK that is extracted at first run. From there we download and execute additional scripts (we have a fairly large data set). All DB access is in pure C++ (accessed via Java->JNI) No, users do not need root -- you have full access to your app's data directory. – NuSkooler Apr 21 '12 at 17:37
@JamesCoote i am facing the same problem " i want to enable r*tree module " can you help me with this i am newbie to android , or can you provide me with an article to help me ??please – user4o01 Dec 03 '12 at 14:15
@user4o01 It's probably not something you want to attempt as a newbie, but if you want to have a go, first get familiar with the NDK: http://developer.android.com/tools/sdk/ndk/index.html#GetStarted – James Coote Dec 03 '12 at 15:02

score 1 · Answer 3 · answered Dec 23 '14 at 21:23

https://www.sqlite.org/android/doc/trunk/www/index.wiki gives exactly the answer to your question and contains a step-by-step guide to install SQLite for your application with NDK/JNI.

Note that you have to add LOCAL_CFLAGS += -DSQLITE_ENABLE_RTREE in the jni/sqlite/Android.mk file as suggested by the documentation to compile with R-Tree Module enabled.

Don't forget to update your imports or the default SQLite database will be used.

score 1 · Answer 4 · answered Aug 02 '11 at 11:29

This worked for me, extract from Android.mk file. It is for spatialite, sqlite spatial extension.

include $(CLEAR_VARS)
# -DOMIT_GEOS=0
# ./configure --build=x86_64-pc-linux-gnu --host=arm-linux-eabi
LOCAL_MODULE    := spatialite
LOCAL_CFLAGS    := -D__ANDROID__ -Dfdatasync=fsync -DOMIT_GEOCALLBACKS -DSQLITE_ENABLE_RTREE
LOCAL_LDLIBS    := -llog 
LOCAL_C_INCLUDES := \
    libiconv-1.13.1/include \
    libiconv-1.13.1/libcharset/include \
    geos-3.2.2/source/headers \
    geos-3.2.2/capi \
    proj-4.6.1/src
LOCAL_SRC_FILES := \
    ./libspatialite-amalgamation-2.4.0/spatialite.c \
    ./libspatialite-amalgamation-2.4.0/empty.cpp \
    ./libspatialite-amalgamation-2.4.0/sqlite3.c
LOCAL_STATIC_LIBRARIES := iconv proj geos
include $(BUILD_STATIC_LIBRARY)

Android SQLite R-Tree - How to install module?

4 Answers4

Linked