Question: how to perform insert or update, based on a specific field

[AndroidDeveloperLB]

I've noticed there is a function "persistWithOnConflict", but I can't figure out how to set what a conflict should be.

In my case, I have this spec:

@TableModelSpec(className = "SomeSpec ", tableName = "someTable")
public class SomeSpec {
    public long time;
    public String name, number, key;
}

I need to add a new entity of it, but only if "number" doesn't exist for any other record in the db-table. If there is, I wish to replace it with the input entity data.

How can I do it?

[AndroidDeveloperLB]

The "number" is supposed to be unique. Is it possible perhaps to combine 2 fields into one that's the "primary key"?

[sbosley]

The conflict enum sets the SQLite conflict algorithm. I think you are looking for ON CONFLICT REPLACE: https://sqlite.org/lang_conflict.html. The behaviors of the conflict algorithms are also described briefly in the ConflictAlgorithm javadocs.

If you create a uniqueness constraint on number as described in #147, you can use persistWithOnConflict like so:

myDb.persistWithOnConflict(someItem, ConflictAlgorithm.REPLACE);

[AndroidDeveloperLB]

But how would it work using the unique field? If I put a new entity, how can it know that it is a conflict? Does it ignore the id, and look only on the unique field? Or, actually, it ignores the id because it's null, and check the rest of the constraints?

[sbosley]

It's basically the second thing you said. When the model object doesn't have an id, squidb assumes it needs to insert it, so it creates a SQL INSERT statement. At this point, squidb doesn't do anything else. SQLite takes over and processes the INSERT statement, using the conflict algorithm to resolve constraint violations.

The golden rule of SquiDB is that "it's just SQL". There's nothing fancy or any magic going on underneath here -- persist translates directly to an INSERT when no id is set, or an UPDATE using changed values when the model object does have an id.

[sbosley]

It seems like this question has been answered so I'm going to close the issue -- feel free to reply back if you have more questions about this one.

[AndroidDeveloperLB]

ok, thank you.

[AndroidDeveloperLB]

Using persistWithOnConflict, the entity will be filled with an id, in case it was an INSERT operation, right?

[sbosley]

It should, yes.

[AndroidDeveloperLB]

OK, thank you.