activer007/sql-translation

SQL Translation Skill

Use this skill when adding new SQL function translations for a specific database backend.

Overview

This skill guides you through adding SQL translations to dbplyr. SQL translations convert R functions to their SQL equivalents for different database backends.

Workflow

1. Research SQL (CRITICAL - ALWAYS FIRST)

Before implementing any SQL translation, you MUST research the SQL syntax and behavior using the sql-research skill. See that skill for the complete research workflow.

Quick summary:

• Search official documentation for "{dialect} {function}"
• Document findings in research/{dialect}-{function}.md
• Include all source URLs
• Only proceed to implementation after completing research

2. Identify the backend file

SQL translations are defined in backend-specific files:

• R/backend-sqlite.R - SQLite
• R/backend-postgres.R - PostgreSQL
• R/backend-mysql.R - MySQL
• R/backend-mssql.R - MS SQL Server
• etc.

3. Add translation

Translations are added to the sql_translation() method for the connection class. This method returns a sql_variant() with three components:

Scalar translations (for mutate/filter):

sql_translator(.parent = base_scalar,
  # Simple function name mapping
  log10 = function(x) sql_expr(log(!!x)),

  # Function with different arguments
  round = function(x, digits = 0L) {
    digits <- as.integer(digits)
    sql_expr(round(((!!x)) %::% numeric, !!digits))
  },

  # Infix operators
  paste0 = sql_paste(""),

  # Complex logic
  grepl = function(pattern, x, ignore.case = FALSE) {
    if (ignore.case) {
      sql_expr(((!!x)) %~*% ((!!pattern)))
    } else {
      sql_expr(((!!x)) %~% ((!!pattern)))
    }
  }
)

Aggregate translations (for summarise):

sql_translator(.parent = base_agg,
  sd = sql_aggregate("STDEV", "sd"),
  median = sql_aggregate("MEDIAN"),
  quantile = sql_not_supported("quantile")
)

Window translations (for mutate with groups):

sql_translator(.parent = base_win,
  sd = win_aggregate("STDEV"),
  median = win_absent("median"),
  quantile = sql_not_supported("quantile")
)

4. Helper functions

Common translation patterns:

• sql_expr() - Build SQL expressions with !! for interpolation
• sql_cast(type) - Type casting (e.g., sql_cast("REAL"))
• sql_aggregate(sql_name, r_name) - Simple aggregates
• sql_paste(sep) - String concatenation
• sql_not_supported(name) - Mark unsupported functions
• win_aggregate(sql_name) - Window aggregates
• win_absent(name) - Window functions not supported

5. Test the translation

Interactive testing:

Rscript -e "devtools::load_all(); library(dplyr, warn.conflicts = FALSE);
  translate_sql(your_function(x), con = simulate_yourdb())"

Write tests:

• Tests for R/{name}.R go in tests/testthat/test-{name}.R
• Place new tests next to similar existing tests
• Keep tests minimal with few comments

Example test:

test_that("backend_name translates function_name correctly", {
  lf <- lazy_frame(x = 1, con = simulate_backend())

  expect_snapshot(
    lf |> mutate(y = your_function(x))
  )
})

6. Document the translation

Update backend documentation:

• Edit the @description section in the backend file (e.g., R/backend-postgres.R)
• List key translation differences
• Add examples to @examples if helpful

Example:

#' Backend: PostgreSQL
#'
#' @description
#' See `vignette("translation-function")` and `vignette("translation-verb")` for
#' details of overall translation technology. Key differences for this backend
#' are:
#'
#' * Many stringr functions
#' * lubridate date-time extraction functions
#' * Your new translation

7. Format and check

# Format code
air format .

# Run relevant tests
Rscript -e "devtools::test(filter = 'backend-name', reporter = 'llm')"

# Check documentation
Rscript -e "devtools::document()"

Key concepts

Parent translators:

• base_scalar - Common scalar functions (math, string, logical)
• base_agg - Common aggregates (sum, mean, min, max)
• base_win - Common window functions

SQL expression building:

• Use sql_expr() to build SQL
• Use !! to interpolate R variables
• Use %as% for AS, %::% for ::, etc.

Argument handling:

• Check arguments with check_bool(), check_unsupported_arg()
• Convert R types appropriately (e.g., as.integer())
• Handle optional arguments with defaults

Resources

See also:

• vignette("translation-function") - Function translation overview
• vignette("new-backend") - Creating new backends
• Existing backend files for examples

Checklist

Before completing a SQL translation:

• [ ] Researched SQL syntax in official documentation
• [ ] Created research file in research/{dialect}-{function}.md
• [ ] Added translation to appropriate sql_translator() section
• [ ] Tested translation interactively
• [ ] Added/updated tests
• [ ] Updated backend documentation
• [ ] Ran air format .
• [ ] Verified tests pass