change escape character `translate_sql()` from ` to '

[josiahparry]

The default escape character for column names when using translate_sql() is ` I would like for it to be '. I cannot figure out how to accomplish this without casting to a character vector and manually replacing.

Currenty behavior:

library(dbplyr)
translate_sql(var > 100)

#>  <SQL> `var` > 100.0

Desired

#> <SQL> 'var' > 100.0`

[edgararuiz]

Is using the following valid in your case?

dbplyr::translate_sql("var" > 10)

That will return:

<SQL> 'var' > 10.0

[josiahparry]

Unfortunately not.
I'm trying to implement a filter.myclass that does basic translation for a where clause. It's not a real database back end but rather a list of metadata that will be used to send a query to a rest api.

A filter method like

filter.myclass <- function(x, ...) {
   dbplyr::partial_eval(rlang::expr(...), vars = x[["cols"]][["names"]])
   dbplyr::translate_sql(...)
}

get's 99% of the way there.

This will get all the way there for my purposes, so I'll settle on this.

filter.FeatureLayer <- function(x, ...) {
  dbplyr::partial_eval(rlang::expr(...), vars = x[["cols"]][["names"]])
  gsub("`", "", dbplyr::translate_sql(...))
}

I tried doing something like sql_escape_ident.myclass <- function(con, x) sql_quote(x, "") but that didn't work like I anticipated.

[mgirlich]

You were nearly there. You just have to use the con argument of translate_sql(). Either you supply the actual connection to a database or if there is none you can create a dummy connection that inherits from:

• a custom class so that you can overwrite sql_escape_ident()
• from DBIConnection so that the translation actually works

e.g.

library(dbplyr)

sql_escape_ident.myclass <- function(con, x) sql_quote(x, "'")

dummy_connection <- structure(
  list(),
  class = c("myclass", "DBIConnection")
)
translate_sql(var > 100, con = dummy_connection)
#> Warning: <myclass> uses an old dbplyr interface
#> ℹ Please install a newer version of the package or contact the maintainer
#> This warning is displayed once every 8 hours.
#> <SQL> 'var' > 100.0

^{Created on 2022-12-13 with reprex v2.0.2}