How to make an INSERT IGNORE query with Slick?

Question

Due to the nature of the subject data I use in a project of mine, duplicate records are frequent to be met in the input sets (some times it means duplicate records in the same input set, and this can be easily handled by pre-insert filtering, but usually the input set can contain records that are duplicates of records already in the database).

MySQL and SQLite INSERT IGNORE feature helps greatly in handling this.

So the questions I've got are:

1. How to cause Slick to use INSERT IGNORE instead of bare INSERT when inserting data into a MySQL or SQLite database?

2. What is the optimal way to emulate INSERT IGNORE functionality when using Slick with MS SQL Server which doesn't support it natively?

Answer 1

I have no experience with Slick. From the source code I gathered that in order to cause Slick to be able to use INSERT IGNORE you would need to perform a few steps:

• Extends the driver of the desired database
• Override the default InsertBuilder which is created through def createInsertBuilder(node: Node): InsertBuilder. Extend it and add a specialized method that is similar to the buildInsert method.
• Override the default InsertInvoker which is created through def createCountingInsertInvoker[T, U](u: ShapedValue[T, U]). Extend it and add insertIgnore which should be similar to def insert[TT](query: Query[TT, U])(implicit session: Session): RetQuery from FullInsertInvoker

As for your second question. The implementation of a workaround for INSERT IGNORE when it's not supported is database dependent, google will help you find the different implementations. Since the above method of adding INSERT IGNORE is driver independent, you could use the same structure to add the functionality for any database.