Support ON CONFLICT DO UPDATE ... EXCLUDED

[tschuchortdev]

Currently, the onConflictUpdate function is defined as follows:

def onConflictUpdate(c: (Q => Column[?])*)(c2: (Q => Column.Assignment[?])*)

From the examples:

Buyer.insert
  .columns(
    _.name := "test buyer",
    _.dateOfBirth := LocalDate.parse("2023-09-09"),
    _.id := 1 // This should cause a primary key conflict
  )
  .onConflictUpdate(_.id)(_.name := "TEST BUYER CONFLICT")

As we can see, the c2 parameter assigns the values that should be SET in the UPDATE and takes only one paramater Q which is the currently inserted row. In postgres at least it is also possible to access the conflicting row through EXCLUDED. onConflictUpdate should thus have two Q parameters, if the database supports it. (See implementation in Quill for example: https://zio.dev/zio-quill/writing-queries/#update-on-conflict)

[tschuchortdev]

Some ideas for implementing this feature:

• The second parameter for EXCLUDED can be created as follows:

  def excluded[V[_[_]]](query: Returning.InsertBase[V[Column]])(using tableMeta: Table.Metadata[V], dialect: Dialect) =
    val excludedTableRef = new TableRef(new Table.Base {
      override def tableName: String = "EXCLUDED"
      override def schemaName: String = ""
      override def tableLabels: Seq[String] = Table.labels(query.table.value)
      override def escape: Boolean = false
    })
    tableMeta.vExpr(excludedTableRef, dialect) 
  
  

This probably has to happen within OnConflictOps because it is not known in OnConflict that Q =:= V[Column].

• Unfortunately, this alone is not enough. The EXCLUDED tableName will be printed as SCALA_SQL_MISSING_TABLE_EXCLUDED because of some nasty almost-global state in Column.renderToSql0:

def renderToSql0(implicit ctx: Context) = {
    val suffix = SqlStr.raw(ctx.config.columnNameMapper(name))
    ctx.fromNaming.get(tableRef) match {
      case Some("") => suffix
      case Some(s) => SqlStr.raw(s) + sql".$suffix"
      case None =>
        sql"SCALASQL_MISSING_TABLE_${SqlStr.raw(Table.name(tableRef.value))}.$suffix"
    }
  }

We need to get EXCLUDED into the context somehow. I think the correct way to do this would be to use the Context.compute function from within OnConflict.Update.renderSql to amend the context locally:

 private[scalasql] def renderSql(ctx: Context) = {
      implicit val implicitCtx = Context.compute(ctx, Nil, Seq(table, /* ---> */ excludedTableRef /* <--- */))

I would also change renderSql to be public. Currently, it is not possible at all to implement this functionality outside of library code because renderSql is private and it is the only place where we can amend the context.