Fix SQL Upsert Compatibility Issue for Android 10 and Lower

[brianabdl]

Using upsert when inserting non-existing chapters the app shows an error. When I change it to insert, it works just fine. I don't really understand SQL, but this fixes the issue.

I've also collected some logs, if my changes are not a desired behavior please give some advice xD

ExceptionString(e=android.database.sqlite.SQLiteException: near "ON": syntax error (code 1 SQLITE_ERROR): , 
while compiling: INSERT INTO chapter(_id,book_id,url,name,scanlator,read,bookmark, last_page_read,chapter_number,source_order,date_fetch,date_upload,content,type)
VALUES (?, ?, ?,?,?,?,?,?,?,?,?,?,?,?)
ON CONFLICT(_id)
DO UPDATE
SET
url = ?,
read = ?,
bookmark = ?,
last_page_read = ?,
content = ?,
type = ?

WHERE _id IS ?)

[kazemcodes]

I use upsert in order to update chapters when it exist and if it doesn't exist then just insert a new chapter into db

[kazemcodes]

when this error happens and what is your phone model?

[brianabdl]

Got the idea, but somehow when it try insert a new chapters, it shows an error message

[kazemcodes]

I tried to insert chapter using a samsung device with android 11 and then nothing happed, there wasn't an error , even app doesn't crashed

[brianabdl]

I'm using Android 10, maybe the SQLite version on the device is causing this issue, As mentioned in Android SQLite Javadoc, Android 9-10 is still using an SQLite version 3.22

The phrase "ON CONFLICT" is also part of UPSERT, which is an extension to INSERT added in version 3.24.0 (2018-06-04).

[brianabdl]

Found the solution, we can use INSERT OR REPLACE to fix compatibility issues.

[kazemcodes]

Insert or Replace are very different from using Insert ..... Do Update Set

[kazemcodes]

it's better to not change the current database queries

[kazemcodes]

Insert or Replace are very different from using Insert ..... Do Update Set

when operation use replace, a completely new object is created in database that is completely unrelated to the previous one and because of that is cause a very serious issue in lazyColumn widget

[brianabdl]

Hmm, it's true, maybe use a conditional statement to imitate upsert behavior?

[kazemcodes]

I tried on android 7 device and there was not an issue or break in app functionality? which android device do you have problems with

[brianabdl]

Android 10, Poco X3 NFC

[brianabdl]

Just tested the lastest release(v0.1.38), seems fine. I only get SQL error when I try to build the project by myself, I'm pretty sure everything is correct. Don't know what's wrong with it.