Support merge statement

[vlsi]

Hi,

I see there's "replace" statement which is related, yet it might be a bit different from MERGE.

I've implemented a small function for MERGE for Oracle DB (I can contribute it or something like that).
I use it instead of batchInsert as it enables me to automatically insert or update the rows.

The caveat is that MERGE in Oracle does not support "return generated keys" (there's no syntax for RETURNING), and I see your batchReplace uses return generated keys by default.

WDYT? (the code below can be used under Apache 2.0 if you wonder)

fun <T : Table, E> T.batchMerge(
    data: Iterable<E>,
    whenMatched: Boolean = true,
    whenNotMatched: Boolean = true,
    body: BatchMergeStatement.(E) -> Unit
) {
    if (!data.iterator().hasNext()) {
        return
    }
    val statement = BatchMergeStatement(this, whenMatched, whenNotMatched)
    data.forEach {
        statement.body(it)
        statement.addBatch()
    }
    statement.execute(TransactionManager.current())
}

class BatchMergeStatement(
    val table: Table,
    val whenMatched: Boolean,
    val whenNotMatched: Boolean,
) : UpdateStatement(table, limit = null, where = null) {
    val data = ArrayList<Map<Column<*>, Any?>>()
    override val firstDataSet: List<Pair<Column<*>, Any?>> get() = data.first().toList()

    override fun arguments(): Iterable<Iterable<Pair<IColumnType, Any?>>> {
        return data.map { it.map { (k, v) -> k.columnType to v } }
    }

    fun addBatch() {
        data.add(values.toMutableMap())
        values.clear()
    }

    override fun PreparedStatementApi.executeInternal(transaction: Transaction): Int =
        if (data.size > 1 || isAlwaysBatch) executeBatch().sum() else executeUpdate()

    override fun prepareSQL(transaction: Transaction): String {
        return QueryBuilder(true).run {
            +"MERGE INTO ${transaction.identity(table)} t"
            +" USING ("
            firstDataSet.appendTo(
                this,
                prefix = "SELECT ",
                postfix = " FROM DUAL"
            ) { (col, value) ->
                registerArgument(col, value)
                +" "
                +transaction.identity(col)
            }
            +") n"
            +" ON ("
            table.primaryKey!!.columns.appendTo(separator = " AND ") { col ->
                val columnName = transaction.identity(col)
                +"t.$columnName = n.$columnName"
            }
            +")"
            val allColumns = firstDataSet.map { it.first }
            if (whenMatched) {
                val updateColumns = allColumns.filterNot { it in table.primaryKey!!.columns }
                updateColumns.appendTo(prefix = " WHEN MATCHED THEN UPDATE SET ") { col ->
                    val columnName = transaction.identity(col)
                    +"t.$columnName = n.$columnName"
                }
            }
            if (whenNotMatched) {
                allColumns.appendTo(
                    prefix = " WHEN NOT MATCHED THEN INSERT(",
                    postfix = ")"
                ) { col ->
                    +transaction.identity(col)
                }
                allColumns.appendTo(prefix = " VALUES(", postfix = ")") { col ->
                    +"n."
                    +transaction.identity(col)
                }
            }
            toString()
        }
    }
}