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):

r
1sql_translator(.parent = base_scalar,
2  # Simple function name mapping
3  log10 = \(x) sql_glue("LOG({x}) / LOG(10)"),
4
5  # Function with different arguments
6  round = function(x, digits = 0L) {
7    digits <- as.integer(digits)
8    sql_glue("ROUND(CAST({x} AS NUMERIC), {.val digits})")
9  },
10
11  # Infix operators
12  paste0 = sql_paste_infix("", "||"),
13
14  # Complex logic
15  grepl = function(pattern, x, ignore.case = FALSE) {
16    if (ignore.case) {
17      sql_glue("{x} ~* {pattern}")
18    } else {
19      sql_glue("{x} ~ {pattern}")
20    }
21  }
22)

Aggregate translations (for summarise):

r
1sql_translator(.parent = base_agg,
2  sd = sql_aggregate("STDEV", "sd"),
3  median = sql_aggregate("MEDIAN"),
4  quantile = sql_not_supported("quantile")
5)

Window translations (for mutate with groups):

r
1sql_translator(.parent = base_win,
2  sd = win_aggregate("STDEV"),
3  median = win_absent("median"),
4  quantile = sql_not_supported("quantile")
5)

4. Helper functions

Common translation patterns:

• sql_glue() - Build SQL expressions with {x} for interpolation
• {.val x} - Interpolate literal R values (not SQL expressions)
• sql_cast(type) - Type casting (e.g., sql_cast("REAL"))
• sql_aggregate(sql_name, r_name) - Simple aggregates
• sql_paste_infix(sep, op) - String concatenation with infix operator
• 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:

r
1Rscript -e "devtools::load_all(); library(dplyr, warn.conflicts = FALSE);
2  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:

r
1test_that("backend_name translates function_name correctly", {
2  lf <- lazy_frame(x = 1, con = simulate_backend())
3
4  expect_snapshot(
5    lf |> mutate(y = your_function(x))
6  )
7})

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:

r
1#' Backend: PostgreSQL
2#'
3#' @description
4#' See `vignette("translation-function")` and `vignette("translation-verb")` for
5#' details of overall translation technology. Key differences for this backend
6#' are:
7#'
8#' * Many stringr functions
9#' * lubridate date-time extraction functions
10#' * Your new translation

7. Format and check

bash
1# Format code
2air format .
3
4# Run relevant tests
5Rscript -e "devtools::test(filter = 'backend-name', reporter = 'llm')"
6
7# Check documentation
8Rscript -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_glue() to build SQL with string interpolation
• Use {x} to interpolate SQL expressions (function arguments)
• Use {.val x} to interpolate literal R values
• Use {sql x} to interpolate raw SQL strings

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