insert if not exists and insert or update
Gray Watson
I can see the real definitions to be something like:
/* returns either the data parameter if it was inserted or a new data if it already existed */
T createIfNotExists(T data);
This code would most likely internally do a queryForId(idExtractedFromData) and if null, it will do a create. If not null then it would return the queried data.
/* returns some sort of status object, maybe an object with the number of rows changed and whether an insert or update was performed */
Status createOrUpdate(T data);
This code would most likely do an update, if 0 rows are updated then it would do a create.
In both of these cases, 2 database operations will be made for most database types but they won't involve catching an exception.
Comments?
gray
jc
>
> This code would most likely internally do a queryForId(idExtractedFromData) and if null, it will do a create. If not null then it would return the queried data.
>
> /* returns some sort of status object, maybe an object with the number of rows changed and whether an insert or update was performed */
> Status createOrUpdate(T data);
>
> This code would most likely do an update, if 0 rows are updated then it would do a create.
>
> In both of these cases, 2 database operations will be made for most database types but they won't involve catching an exception.
>
to replace my custom Dao implementation that uses #countForId() to
determine if the insert or update should be performed.
When we emailed about this, Gray brought up the case for objects that
do not have a valid ID field which would fail the update logic and
throw an exception in case anyone has expectations of different
behavior for that.
jc
>
as well in case it's something that we might want to handle. If
createOrUpdate gets called for an object that was not originally
queried from the database, it's possible for the update to overwrite
data. The same case would happen if you called update directly;
however, the createOrUpdate is more prone to this case since the state
of the original data object may not already be known.
If that isn't clear, here is a simple use case for data that is
sourced or synced from a remote system to a mobile device that
includes a local field that is not in the remote dataset:
- the remote data is pushed/pulled to a local table where
createOrUpdate is used and the object is inserted
- the application updates local field in the local database (think of
a status field or maybe a timestamp)
- the remote data gets pushed again during the next sync,
createOrUpdate gets called with a new object that doesn't have the
local field value so it gets reset
There are several ways to address this scenario but I'm considering
adding logic to first call queryForId to get any existing object. If
the object doesn't exist, insert it. When the object already exists,
*copy* the fields from the new object to the existing object but
ignore any values in the new object that are null or equal to the
default value which would result in a partial update.
I would be curious if anyone else who requested createOrUpdate has a
need for this type of partial update logic?
Gray Watson
> If createOrUpdate gets called for an object that was not originally
> queried from the database, it's possible for the update to overwrite data.
Certainly. I thought that was the whole point.
> The same case would happen if you called update directly;
> however, the createOrUpdate is more prone to this case since the state
> of the original data object may not already be known.
If you construct an object by hand with a valid id and pass it to update(), the same problem is going to happen. I don't see the difference.
> When the object already exists, *copy* the fields from the new object to the existing object but
> ignore any values in the new object that are null or equal to the
> default value which would result in a partial update.
Hrm. Sounds a bit like the queryForMatching() method in the DAO:
http://ormlite.com/javadoc/ormlite-core/com/j256/ormlite/dao/Dao.html#queryForMatching%28T%29
Not a bad idea for a new update method. So you could have update() and updateNonDefault() and createOrUpdate() and createOrUpdateNonDefault(). Something like that.
I'll add it to the TODO notes.
gray
Adam Brown
Would simplify a bunch of stuff for me.
coder.sam
Also, maybe it would be useful to expose "INSERT OR {ONCONFLICT}"
clause.
Because sometimes it is convenient just to ignore constraint
violations.
