Ordinary-claude-skills sql-translation

Guide for adding SQL function translations to dbplyr backends. Use when implementing new database-specific R-to-SQL translations for functions like string manipulation, date/time, aggregates, or window functions.

install

source · Clone the upstream repo

git clone https://github.com/Microck/ordinary-claude-skills

Claude Code · Install into ~/.claude/skills/

T=$(mktemp -d) && git clone --depth=1 https://github.com/Microck/ordinary-claude-skills "$T" && mkdir -p ~/.claude/skills && cp -r "$T/skills_all/sql-translation" ~/.claude/skills/microck-ordinary-claude-skills-sql-translation && rm -rf "$T"

manifest: skills_all/sql-translation/SKILL.md

source content

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

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