support #$ interpolation for dynamic sql such as table names, order clause, etc

[rbanikaz]

Love relate and the SqlResult parsing is much cleaner than anorm. One thing missing is #$ interpolation which is supported by anorm and slick. Its helpful for use cases like dynamic table names, order clauses, etc:

val accountId = 2
val orderCol = "foo"
val orderDir = "desc"
sql"select name, sum(foo) as foo, sum(bar) as bar from table where account_id = $account_id group by name order by #$orderCol #$orderDir" 

I have a quick and dirty commit which unblocks me for now, would remove this for official version if/when supported: https://github.com/xadrnd/relate/commit/40e176617280e836ab5a6c6a7babbef3e04a47e5

Thanks!

[rfranco]

I did a simple Helper to solve it

package com.lucidchart.open.relate.interp

object Helper {
  implicit def fromInterpolatedQuery(value: InterpolatedQuery): InterpolatedQueryParameter = new InterpolatedQueryParameter(value)

  class InterpolatedQuery(value: InterpolatedQuery) extends Parameter {
    def appendPlaceholders(stringBuilder: StringBuilder): Unit = stringBuilder ++= value.parsedQuery
    def parameterize(statement: PreparedStatement, i: Int): Int = value.parameterize(statement, i)
  }
}

And you can use InterpolatedQuery as Parameter for other InterpolatedQuery

import com.lucidchart.open.relate.interp.Helper._

val accountId = 2
val whereQuery = sql"where id = ${accountId}"
val query = sql"select * from users ${whereQuery}"

I hope it help you

[pauldraper]

@rfranco, that is actually already implemented ;) InterpolatedQuery is a MultiParameter, which is a Parameter.

---

@rbanikaz, see https://github.com/lucidsoftware/relate/wiki/Query-Interpolation#query-composition.

// interpolate (my preference)
val sql1 = sql"SELECT * FROM users"
val sql2 = sql"$sql1 LIMIT 5"

// concatenate
val sql3 = sql"SELECT * FROM users"
val sql4 = sql" LIMIT 5"
val sql5 = sql3 + sql4

(FYI, if for some reason you aren't constructing your queries with literal strings, an arbitrary string can be converted to a query by calling .toSql on it. Of course, you should only do this with trusted data.)

I like this approach to interpolation, as it encodes the semantics of the data -- text data, or SQL query -- in the type. It avoids the "String as universal data type" smell, and it makes accidental SQL injection more difficult.

Let me know if you find this reasonable.

[rbanikaz]

@pauldraper Unfortunately, it doesnt work for dynamic table names or order by clauses.
For example the below:

sql"select name, sum(foo) as foo, sum(bar) as bar from table where account_id = $account_id group by name order by $orderCol $orderDir" 

Will return a InterpolatedQuery which looks like this:

select name, sum(foo) as foo, sum(bar) as bar from table where account_id = ? group by name order by ? ?

which is not right. Thats why anorm/slick support #$ interpolation.

Another example is table names, suppose I have time series tables which are formatted like follows:

tablename_YYYYMMDD

I will need to construct the table name as a string and pass it literally to the query but not as a parameter.

Please let me know if I missed something and above 2 use cases are supported?

[pauldraper]

For dynamic (rather than literal) strings, there is .toSql.

sql"""
  select name, sum(foo) as foo, sum(bar) as bar
  from table where account_id = $account_id
  group by name
  order by ${orderCol.toSql} ${orderDir.toSql}"""

Similarly,

sql"select * from tablename_${format.format(date).toSql}"

If these are done frequently, you can define helpers.

val tablename = s"tablename_${format.format(date)}".toSql

sql"select * from $tablename"

---

This latter form results in less verbose queries, and the interpolation behavior is based off of type, just as it is for everything else: Int, Double, Seq[Long], etc. You don't need to reiterate the type of the variable when interpolating the variable.

But I agree that in some cases, depending on the particular use, #$ may be simpler. If you like the #$, make a pull request.

[rbanikaz]

Cool thanks! I didn't know about the toSql, that's helpful at least it covers the use case.

I do think the #$ is nice, I didn't spend much time going through your codebase, but I will go ahead and submit the PR, it will be great if you take a look I will be happy to respond to comments...

Cheers!