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