Build Android version with -DSQLITE_TEMP_STORE=3

[Former user]

Originally reported on Google Code with ID 76

While attempting to integrate sqlite4java with my Cordova sqlite adapter project, I
encountered an issue with certain UPDATE statements which will be fixed by adding -DSQLITE_TEMP_STORE=3
to LOCAL_CFLAGS in ant/Android.mk.template. In addition, I see no need for the following
option (and verified using grep): -DTEMP_STORE=1

The following test code reproduces the issue and verifies the fix on Android:

  SQLiteConnection connection = database; // given by the test framework

  try {
    connection.open();

    connection.exec("begin immediate");

    SQLiteStatement st1 = connection.prepare("DROP TABLE IF EXISTS Task");
    st1.step();
    st1.dispose();

    SQLiteStatement st2 = connection.prepare("CREATE TABLE IF NOT EXISTS Task (id primary
key, subject)");
    st2.step();
    st2.dispose();

    SQLiteStatement st3 = connection.prepare("INSERT INTO Task VALUES ('928238b3-a227-418f-aa15-12bb1943c1f2','test1')");
    st3.step();
    st3.dispose();

    SQLiteStatement st4 = connection.prepare("INSERT INTO Task VALUES ('511e3fb7-5aed-4c1a-b1b7-96bf9c5012e2',
'test2')");
    st4.step();
    st4.dispose();

  } catch(Exception e) {
    Log.e("SQLiteUpdateTest", "errr", e);
    return false;
  }

  try {
    long lastTotal1 = connection.getTotalChanges();
    Log.v("SQLiteUpdateTest", "lastTotal1: " + lastTotal1);
    SQLiteStatement st5 = connection.prepare("UPDATE Task SET subject='Send reminder',
id='928238b3-a227-418f-aa15-12bb1943c1f2' WHERE id = '928238b3-a227-418f-aa15-12bb1943c1f2'");
    Log.v("SQLiteUpdateTest", "next statement prepared");
    boolean hasRows1 = st5.step();
    st5.dispose();
    long newTotal1 = connection.getTotalChanges();
    Log.v("SQLiteUpdateTest", "newTotal1: " + newTotal1);
    Log.v("SQLiteUpdateTest", "finished");
    return (lastTotal1+1 == newTotal1);

  } catch(Exception e) {
    Log.e("SQLiteUpdateTest", "errr", e);
    return false;
  }

Here is the proposed change to ant/Android.mk.template:

--- ant/Android.mk.template (revision 399)
+++ ant/Android.mk.template (working copy)
@@ -20,6 +20,6 @@
 LOCAL_LDLIBS:=-llog
 LOCAL_MODULE       := sqlite4java-android
 LOCAL_SRC_FILES    := $(BASE)/sqlite/sqlite3.c $(BASE)/build/swig/sqlite_wrap.c $(BASE)/native/sqlite3_wrap_manual.c
$(BASE)/native/intarray.c
-LOCAL_CFLAGS       := -O2 -DNDEBUG -Dfdatasync=fsync -fno-omit-frame-pointer -fno-strict-aliasing
-static-libgcc -I../../../sqlite -I../../../native -DSQLITE_ENABLE_COLUMN_METADATA
-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_MEMORY_MANAGEMENT
-DSQLITE_ENABLE_STAT2 -DHAVE_READLINE=0 -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1
-DTEMP_STORE=1  -DSQLITE_OMIT_DEPRECATED -DSQLITE_OS_UNIX=1 -DSQLITE_ENABLE_RTREE=1
-DHAVE_STRCHRNUL=0
+LOCAL_CFLAGS       := -O2 -DNDEBUG -Dfdatasync=fsync -fno-omit-frame-pointer -fno-strict-aliasing
-static-libgcc -I../../../sqlite -I../../../native -DSQLITE_ENABLE_COLUMN_METADATA
-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_MEMORY_MANAGEMENT
-DSQLITE_ENABLE_STAT2 -DHAVE_READLINE=0 -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1
-DSQLITE_TEMP_STORE=3 -DSQLITE_OMIT_DEPRECATED -DSQLITE_OS_UNIX=1 -DSQLITE_ENABLE_RTREE=1
-DHAVE_STRCHRNUL=0

Reported by Chris.Brody on 2015-04-08 16:49:21

[Former user]

Hi Chris,

Thanks for providing a test and a patch. (By the way, you can replace "conn.prepare(),
st.step(); st.dispose();" with "conn.exec()" if you don't have any parameters.)

I wonder how this parameter affects this test. Did you dig into the details?

Kind regards,
Igor

Reported by [e-mail] on 2015-04-16 23:19:23

[Former user]

Hi Igor,

Unfortunately I only dug deep enough to figure out what kind of error was reported
by sqlite3.c. I noticed that both https://github.com/sqlcipher/android-database-sqlcipher
and https://github.com/android/platform_external_sqlite are using -DSQLITE_TEMP_STORE=3

I cannot promise when I will get a chance to dig for a deeper explanation.

Also, I highly recommend we consider getting rid of -DTEMP_STORE=1 since it has no
meaning and can become a bit confusing.

Reported by Chris.Brody on 2015-04-20 18:19:02

[Former user]

P.S. Agreed it is better to use conn.exec() since my test is not using any parameters.

Reported by Chris.Brody on 2015-04-20 18:20:03

[Former user]

Chris, thanks for your input - will do!

Reported by [e-mail] on 2015-04-26 22:40:11 - Status changed: Accepted