InsertAll and ON Conflict strategy

ServiceStack.OrmLite
1

We’re using the new? InsertAll() method. The Intelsense shows that it performs the Insert using a Transaction. Performance is much better. As this article documents, performance went from 85 rows a second to over 23000 per second.

But… we are also using a Compound unique Index for the purpose of rejecting duplicate rows.

The issue is: when combining these two techniques, the transaction can be aborted when a duplicate occurs. SqlLite has the ability to define a strategy for what happens when you get a conflict when performing this in a transaction.

http://www.sqlite.org/lang_conflict.html

We would like to use the IGNORE option -

When an applicable constraint violation occurs, the IGNORE resolution algorithm skips the one row that contains the constraint violation and continues processing subsequent rows of the SQL statement as if nothing went wrong. Other rows before and after the row that contained the constraint violation are inserted or updated normally. No error is returned when the IGNORE conflict resolution algorithm is used.

Is there any way to accomplish this with the existing InsertAll() method?

If not, I would be in your debt if this could be included in the next release. :slight_smile:

My suggestion would be to have an overload of InsertAll that would allow the caller to provide a transaction handle and have the conflict resolution strategy.

e.g.

public enum SQLConflictResolution { Rollback,Abort,Fail,Ignore,Replace };

public static void InsertAll<T>(this IDbConnection dbConn, IEnumerable<T> objs,IDBTransaction trans,SQLConflictResolution conStategy);

Just in case we’ve not said this in the past… We LOVE ServiceStack, both the code and your way of doing things… Really looking forward to 5.0!!!

2

OrmLite only creates a transaction if there isn’t one already so if it’s being called within your own Transaction it will use that instead.

You can use the InsertFilter to customize the the query that’s executed, with something like:

OrmLiteConfig.InsertFilter = (dbCmd, obj) => {
    if (dbCmd.CommandText.StartsWith("INSERT INTO")) {
        dbCmd.CommandText = "INSERT ON CONFLICT IGNORE " 
            + dbCmd.CommandText.Substring("INSERT".Length);
    }
};

Our preference is to have generic filters instead of a complicated API surface with multiple permutations.

BTW v5 was released earlier this week, will publish a summary announcement soon.

3

Many thanks… That will work!

4

Q: Do we need to undo the InsertFilter after we run this query?

5

Yeah it’s a static property. I’ll look at creating an InsertAllFilter that only gets called once which you may want to leave configured.

6

FYI I’ve just added an optional commandFilter argument to Insert, InsertAll and Update APIs in this commit and added a typed extension method for applying conflict resolutions which you can check out examples of in ConflictResolutionTests.

Of all SQLite’s conflict resolution options only IGNORE is supported on MySql, PostgreSQL as well so that’s the only constant that’s added in ConflictResolution.Ignore along with a typed OnConflictIgnore() extension method that you can use with:

db.Insert(row, dbCmd => dbCmd.OnConflictIgnore());
db.InsertAll(rows, dbCmd => dbCmd.OnConflictIgnore());

Which is just a wrapper around:

db.InsertAll(rows, dbCmd => dbCmd.OnConflict(ConflictResolution.Ignore));

Which you can also use to utilize SQLite’s other conflict resolution options, e.g:

db.InsertAll(rows, dbCmd => dbCmd.OnConflict("FAIL"));

This change is available from v5.0.1 that’s now available on MyGet.