R mapping

John Mount

2021-06-10

rquery re-maps a number of symbols during SQL translation.

During expression parsing the internal rquery function tokenize_call_for_SQL() implements the following re-mappings from R idioms to SQL notation.

library("rquery")
library("wrapr")

show_translation <- function(strings) {
  vapply(strings,
         function(si) {
           format(rquery::tokenize_for_SQL(parse(text = si, keep.source = FALSE)[[1]], colnames = NULL)$parsed_toks)
         }, character(1))
}

mapping_table <- data.frame(
  example = c('!x', 'is.na(x)', 'ifelse(a, b, c)', 'a^b', 'a%%b', 
               'a==b', 'a&&b', 'a&b', 'a||b', 'a|b', 
              'pmin(a, b)', 'pmax(a, b)'),
  stringsAsFactors = FALSE)
mapping_table$translation <- show_translation(mapping_table$example)
knitr::kable(mapping_table)
example translation
!x ( NOT ( x ) )
is.na(x) ( ( x ) IS NULL )
ifelse(a, b, c) ( CASE WHEN ( a ) THEN ( b ) WHEN NOT ( a ) THEN ( c ) ELSE NULL END )
a^b POWER ( a , b )
a%%b MOD ( a , b )
a==b a = b
a&&b a AND b
a&b a AND b
a||b a OR b
a|b a OR b
pmin(a, b) ( CASE WHEN ( a ) IS NULL THEN ( b ) WHEN ( b ) IS NULL THEN ( a ) WHEN ( a ) <= ( b ) THEN ( a ) ELSE ( b ) END )
pmax(a, b) ( CASE WHEN ( a ) IS NULL THEN ( b ) WHEN ( b ) IS NULL THEN ( a ) WHEN ( a ) >= ( b ) THEN ( a ) ELSE ( b ) END )

Note: not all possible mappings are implemented. For example we currently do not re-map %in%, preferring the user to explicitly work with set_indicator() directly.

In addition to this the database connectors can specify additional re-mappings. This can be found by building a formal connector and inspecting the re-mappings.

have_RSQLite <- requireNamespace("RSQLite", quietly = TRUE)
raw_RSQLite_connection <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
RSQLite::initExtension(raw_RSQLite_connection)
db <- rquery_db_info(
  connection = raw_RSQLite_connection,
  is_dbi = TRUE,
  connection_options = rq_connection_tests(raw_RSQLite_connection))


fn_name_map <- db$connection_options[[paste0("rquery.", rq_connection_name(db), ".", "fn_name_map")]]
fn_name_map
## NULL

We see above that “mean” is re-mapped to “avg”.

In all cases we can see what re-mappings happen by examining a query.

d_local <- build_frame(
   "subjectID", "surveyCategory"     , "assessmentTotal", "irrelevantCol1", "irrelevantCol2" |
   1L         , "withdrawal behavior", 5                , "irrel1"        , "irrel2"         |
   1L         , "positive re-framing", 2                , "irrel1"        , "irrel2"         |
   3L         , "withdrawal behavior", 3                , "irrel1"        , "irrel2"         |
   3L         , "positive re-framing", 2                , "irrel1"        , "irrel2"         |
   3L         , "other"              , 1                , "irrel1"        , "irrel2"         )
table_handle <- rq_copy_to(db, 'd',
            d_local,
            temporary = TRUE, 
            overwrite = TRUE)
print(table_handle)
## [1] "mk_td(\"d\", c( \"subjectID\", \"surveyCategory\", \"assessmentTotal\", \"irrelevantCol1\", \"irrelevantCol2\"))"
##   subjectID      surveyCategory assessmentTotal irrelevantCol1 irrelevantCol2
## 1         1 withdrawal behavior               5         irrel1         irrel2
## 2         1 positive re-framing               2         irrel1         irrel2
## 3         3 withdrawal behavior               3         irrel1         irrel2
## 4         3 positive re-framing               2         irrel1         irrel2
## 5         3               other               1         irrel1         irrel2
ops <- table_handle %.>% 
  project(., 
          avg_total := avg(pmax(0, assessmentTotal)),
          groupby = "subjectID")

cat(to_sql(ops, db))
## SELECT `subjectID`, avg ( ( CASE WHEN ( 0 ) IS NULL THEN ( `assessmentTotal` ) WHEN ( `assessmentTotal` ) IS NULL THEN ( 0 ) WHEN ( 0 ) >= ( `assessmentTotal` ) THEN ( 0 ) ELSE ( `assessmentTotal` ) END ) ) AS `avg_total` FROM (
##  SELECT
##   `subjectID`,
##   `assessmentTotal`
##  FROM
##   `d`
##  ) tsql_47208226594582856777_0000000000
## GROUP BY
##  `subjectID`
ops %.>%
  execute(db, .) %.>%
  knitr::kable(.)
subjectID avg_total
1 3.5
3 2.0

The basic mappings are stored in database option structures, and depend on the database. For example MOD is re-mapped back to % for RSQLite.

rquery::rq_function_mappings(db) %.>%
  knitr::kable(.)
fn_name sql_mapping
as.Date to_date ( .(3) , ‘YYYY-MM-DD’ )
n COUNT ( 1 )
mean AVG ( .(3) )
cumsum SUM ( .(3) )
shift LAG ( .(3) )
MOD ( .(3) % .(5) )
rand ABS ( ( RANDOM ( ) % 268435456 ) / 268435455.0 )
ops <- table_handle %.>% 
  project(., groupby = "subjectID",
          n := 5, 
          count := n(),
          mean := mean(assessmentTotal)) %.>% 
  extend(., was_n := n)
                 
cat(to_sql(ops, db))
## SELECT
##  `subjectID`,
##  `n`,
##  `count`,
##  `mean`,
##  `n`  AS `was_n`
## FROM (
##  SELECT `subjectID`, 5 AS `n`, COUNT ( 1 ) AS `count`, AVG ( `assessmentTotal` ) AS `mean` FROM (
##   SELECT
##    `subjectID`,
##    `assessmentTotal`
##   FROM
##    `d`
##   ) tsql_15438122330756525169_0000000000
##  GROUP BY
##   `subjectID`
##  ) tsql_15438122330756525169_0000000001
ops %.>%
  execute(db, .) %.>%
  knitr::kable(.)
subjectID n count mean was_n
1 5 2 3.5 5
3 5 3 2.0 5

Additional function re-mappings can be specified by user code. One such example is re-writing MOD as % for RSQLite.

ops <- table_handle %.>% 
  extend(., z := 1 + subjectID %% 3) %.>%
  select_columns(., c("subjectID", "z"))
                 
cat(to_sql(ops, db))
## SELECT
##  `subjectID`,
##  `z`
## FROM (
##  SELECT
##   `subjectID`,
##   1 + ( `subjectID` % 3 )  AS `z`
##  FROM (
##   SELECT
##    `subjectID`
##   FROM
##    `d`
##   ) tsql_93440808473188090527_0000000000
## ) tsql_93440808473188090527_0000000001
ops %.>%
  execute(db, .) %.>%
  knitr::kable(.)
subjectID z
1 2
1 2
3 1
3 1
3 1

rqdatatable also supplies some re-mappings (described here). This can allow us to use a uniform notation for tasks such as random number generation to allow portable pipelines.

DBI::dbDisconnect(raw_RSQLite_connection)