Build Android version with -DSQLITE_TEMP_STORE=3

[GoogleCodeExporter]

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

Original issue reported on code.google.com by Chris.Br...@gmail.com on 8 Apr 2015 at 4:49

[GoogleCodeExporter]

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

Original comment by ser...@almworks.com on 16 Apr 2015 at 11:19

[GoogleCodeExporter]

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.

Original comment by Chris.Br...@gmail.com on 20 Apr 2015 at 6:19

[GoogleCodeExporter]

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

Original comment by Chris.Br...@gmail.com on 20 Apr 2015 at 6:20

[GoogleCodeExporter]

Chris, thanks for your input - will do!

Original comment by ser...@almworks.com on 26 Apr 2015 at 10:40

• Changed state: Accepted

[GoogleCodeExporter]

Due to Google closing Google Code, we have moved sqlite4java project to 
BitBucket. New project home page: 
https://bitbucket.org/almworks/sqlite4java/overview

All issues have been transferred. Unfortunately, it wasn't possible to transfer 
Reporter, Votes and Watches. Please check the issues you are interested in at 
https://bitbucket.org/almworks/sqlite4java/issues?status=new&status=open

Note that the issue tracker on Google Code is no longer tracked and any further 
comments or new issues posted here will be lost when Google Code shuts down.

I apologize for the inconvenience.
Igor Sereda

Original comment by ser...@almworks.com on 28 Jun 2015 at 4:21