Fixed update always return 0 when sqlite WAL enabled

[WonShaw]

There is a bug in AndroidDatabaseConnection.update.

AndroidDatabaseConnection.update execute the update sql first, then execute SELECT CHANGES() to get the changed row number.

The update procedure is as follows:

1. acquire a sqlite connection A
2. use A connection to execute update sql
3. acquire a sqlite connection B
4. use B connectio to execute SELECT CHANGES()

However, it only works when update sql and SELECT CHANGES() are executed on same SQLiteConnection, in other words: connection A and connection B must be the same connection.

These code works fine before, because Android restricts sqlite connection pool size to 1 by defaullt. (see this).

Or more precisely, Android sqlite connection pool size == 1 when the journal mode of sqlite database is rollback journal.

If the connection pool size == 1, obviously, connection A == connection B.

However, when developer enable WAL mode of sqlite by calling setWriteAheadLoggingEnabled, the connection pool size will be 4 instead of 1.

Besides, Android introduced primary connection and non primary connection, if WAL mode is enabled, there will be only 1 primary connection that can write the sqlite database, and 3 non primary connection that can only read the sqlite. When execute readOnly sql, non primary connection will be used first, if all non primary connections are not available, will try acquire primary connection. When execute readWrite sql, only primary connection can be used.

So, look back to update procedure:

1. acquire a sqlite connection A
2. use A connection to execute update sql
3. acquire a sqlite connection B
4. use B connection to execute SELECT CHANGES()

Step.1 must acquire the primary connection then execute update sql in Step.2.

Step.3 will acquire one of the non primary connection in most cases, which is not the same connection as the primary connection

Step.4 always get 0 if the connection in Step.3 is different from the connection in Step.2. (Almost certainly)

So we must change update to use executeUpdateDelete directly, it will execute sqlite3_changes internally and return the changed row number.

[WonShaw]

There are other SELECT CHANGES usages in ormlite-android, I have no plan to modify them for now. Sorry.

[j256]

Wow, thanks for this @WonShaw . I assume that the executeUpdateDelete() method was added more recently. What version of the Android OS started the support for it?

[WonShaw]

executeUpdateDelete() was added in api level 11 (Android 3.0.x honeycomb)

Since android add setWriteAheadLoggingEnabled in api level 16, and the db connection poll size was restricted to 1 when using rollback journal, so it's ok to use the original implementation below api level 11.

I have changed my commit.

[j256]

For the record, I refactored this code a bit. See: https://github.com/j256/ormlite-android/commit/d9cf9cab3f76fa4335f0380a259712fae8420d55