Make Upsert distinguish between Unique key constraint and foreign key constraint [243039555]

Fixed

Bug

Status Update

No update yet.

Description

pi...@google.com

created issue #1

Aug 18, 2022 06:33PM

In EntityUpsertionAdapter add if-statement to check error message catch. When the message is FK violation, throw a new FK violation error. When the message is UK violation, update the database.

Comments

ap...@google.com <ap...@google.com> #2Aug 26, 2022 03:32PM

Project: platform/frameworks/support
Branch: androidx-main

commit 11a3cb30e7c9067a27eb10bf96e89afdc283ba2e
Author: pingxuanli <pingxuanli@google.com>
Date: Mon Aug 22 13:03:03 2022

Adding error message check in EntityUpsertionAdapter.

Bug: 243039555
Test: Tests are in EntityUpsertionAdapterTest file. By using toy, can check for foreign key constraint. (FK violation is commented out to prevent crash but the errors were thrown correctly)
Relnote: restrict the minimum version that supports Upsert to at least 16
Change-Id: I5f67f2ab5d41a313c48dac42f485f8345f74c1b1

M room/room-common/api/current.txt
M room/room-runtime/api/restricted_current.txt
M room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/test/DaoConflictStrategyTest.java
M room/room-common/src/main/java/androidx/room/Upsert.kt
M room/room-common/api/public_plus_experimental_current.txt
M room/room-runtime/api/current.txt
M room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/test/LiveDataQueryTest.java
M room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/test/EntityUpsertionAdapterTest.java
M room/room-runtime/api/public_plus_experimental_current.txt
M room/room-runtime/src/main/java/androidx/room/EntityUpsertionAdapter.kt
M room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/vo/Toy.java
M room/room-common/api/restricted_current.txt

https://android-review.googlesource.com/2191947

is...@google.com <is...@google.com> Sep 20, 2022 08:58AM

Status: New

da...@google.com <da...@google.com> Sep 20, 2022 12:39PM

Marked as fixed, reassigned to da...@google.com.

na...@google.com <na...@google.com> #3Oct 5, 2022 05:14PM

This bug was linked in a change in the following release(s):

androidx.room:room-common:2.5.0-beta01
androidx.room:room-runtime:2.5.0-beta01

pe...@gmail.com <pe...@gmail.com> #4Jan 5, 2023 05:03PM

This fix is also preventing the update when there is a uniqueness conflict on an index (code 2067 SQLITE_CONTRAINT_UNIQUE). In this case `checkUniquenessException(ex)` rethrows the exception. Is it expected ?

da...@google.com <da...@google.com> #5Jan 18, 2023 02:55PM

Status: Assigned (reopened)

Thanks for the comment, indeed checkUniquenessException should not throw for SQLITE_CONSTRAINT_UNIQUE as per the SQLite docs:

The special UPSERT processing happens only for uniqueness constraint on the table that is receiving the INSERT. A "uniqueness constraint" is an explicit UNIQUE or PRIMARY KEY constraint within the CREATE TABLE statement, or a unique index. UPSERT does not intervene for failed NOT NULL or foreign key constraints or for constraints that are implemented using triggers.

In https://www.sqlite.org/lang_UPSERT.html

We'll make a fix to includes SQLITE_CONSTRAINT_UNIQUE as part of the 'uniqueness constraint' that will lead to an UPDATE.

pe...@gmail.com <pe...@gmail.com> #6Jan 20, 2023 07:01PM

Thanks for the update, really appreciate it !

mi...@gmail.com <mi...@gmail.com> #7Jul 31, 2023 08:03AM

Hello,

I've just hit this issue and I would like to know what's the status on handling 2067 SQLITE_CONSTRAINT_UNIQUE the same way and proceed to an update. I was very surprised upsert wasn't working.

Thanks

el...@google.com <el...@google.com> #8Jul 31, 2023 12:21PM

Thanks for the bump, we'll resolve this issue asap.

ap...@google.com <ap...@google.com> #9Aug 7, 2023 08:47PM

Project: platform/frameworks/support
Branch: androidx-main

commit 01002bedc549e0264a5f920dc554ed7dd7785cfe
Author: elifbilgin <elifbilgin@google.com>
Date: Tue Aug 01 05:53:50 2023

Handle special case SQLite exception during upsert.

When 2067 SQLITE_CONSTRAINT_UNIQUE is thrown during an upsert, upsert should perform an update.

Bug: 243039555
Test: EntityUpsertionAdapterTest.java
Change-Id: If28499c59443f590ac456924eff03b18f1a87e4f

M room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/test/EntityUpsertionAdapterTest.java
M room/room-runtime/src/main/java/androidx/room/EntityUpsertionAdapter.kt

https://android-review.googlesource.com/2686153

el...@google.com <el...@google.com> Aug 7, 2023 09:51PM

Marked as fixed.