Be careful with SQLiteDatabase.insert
Tinkering with databases in android stumbled upon a very unpleasant thing: SQLiteDatabase methods for inserting data do not work exactly as written in the documentation. The task was simple: insert a record and get its key for use in another table. In case the necessary record is already available, I want to know the key of this old record. It turned out that you can get anything except this oldest key.
To understand this, I had to run several experiments on cats, but now something has become clear.
First, let's see what the android promises us in SQLiteDatabase:
')
insert () is the easiest method. Should return the key just inserted, or -1 if something went wrong.
insertOrThrow () - the description exactly matches the previous one, but added that throws a SQLException. Judging by the name, you can expect a SQLException to be thrown instead of returning -1, but for now there is no certainty.
insertWithOnConflict () - this is the most interesting. We are promised that the method will return the key of a fresh record OR the key of an existing record if the IGNORE, OR -1 algorithm was used for conflicts. Remember also that nothing is said about SQLException.
Now let's see what happens in practice. Let's create a simple table and try to set different conflict resolution algorithms for it (including none). I did not check rollback and abort - their behavior in this case seems to be similar to fail, and I was most interested in ignore.
Insert a few unique values into the table, and then try to add a conflicting one. Let's see how all these methods handle conflicts.
If the conflict resolution algorithm is not specified, a simple insert () returns -1 (as promised), and insertOrThrow () and insertWithOnConflict () both throw a SQLiteConstraintException. And if for insertOrThrow () this is correct, then you could expect -1 from insertWithOnConflict (). The same thing happens with the 'fail' algorithm.
The 'replace' algorithm all methods worked fine - they deleted the old entry, inserted a new one and returned its key.
But with such a necessary 'ignore', it turned out to be a complete bummer. No method gave an error (-1 or SQLiteConstraintException). All together returned some number - presumably the key of the last record inserted in the table. Of course, this is not what we were promised.
In general, the documentation can not be trusted. Any method can either return the most unexpected values or throw an exception. But the desired result was not achieved - no method could, in the event of a conflict, return the key of the existing record.
To understand this, I had to run several experiments on cats, but now something has become clear.
First, let's see what the android promises us in SQLiteDatabase:
')
insert () is the easiest method. Should return the key just inserted, or -1 if something went wrong.
insertOrThrow () - the description exactly matches the previous one, but added that throws a SQLException. Judging by the name, you can expect a SQLException to be thrown instead of returning -1, but for now there is no certainty.
insertWithOnConflict () - this is the most interesting. We are promised that the method will return the key of a fresh record OR the key of an existing record if the IGNORE, OR -1 algorithm was used for conflicts. Remember also that nothing is said about SQLException.
Now let's see what happens in practice. Let's create a simple table and try to set different conflict resolution algorithms for it (including none). I did not check rollback and abort - their behavior in this case seems to be similar to fail, and I was most interested in ignore.
Insert a few unique values into the table, and then try to add a conflicting one. Let's see how all these methods handle conflicts.
If the conflict resolution algorithm is not specified, a simple insert () returns -1 (as promised), and insertOrThrow () and insertWithOnConflict () both throw a SQLiteConstraintException. And if for insertOrThrow () this is correct, then you could expect -1 from insertWithOnConflict (). The same thing happens with the 'fail' algorithm.
The 'replace' algorithm all methods worked fine - they deleted the old entry, inserted a new one and returned its key.
But with such a necessary 'ignore', it turned out to be a complete bummer. No method gave an error (-1 or SQLiteConstraintException). All together returned some number - presumably the key of the last record inserted in the table. Of course, this is not what we were promised.
In general, the documentation can not be trusted. Any method can either return the most unexpected values or throw an exception. But the desired result was not achieved - no method could, in the event of a conflict, return the key of the existing record.
Source: https://habr.com/ru/post/119243/
All Articles