De-couple class from sql translation

[hadley]

To avoid having to duplicate so many methods when a database has multiple R packages or when a package supports multiple databases.

Will probably need to implement a sql_dialect() generic, and then some how dispatch from that rather than the S3 class (probably using some S3 hackery for backward compatibility).

[hadley]

Probably want to create a sql_dialect() object and have some way of recording quote character so we can also solve #1464.

[hadley]

Plan co-developed with claude:

Goal

Implement a sql_dialect() system to reduce method duplication across database backends by:

1. Creating a dialect object that acts as an (optional) intermediary in between the database connection and SQL translation.
2. Allowing multiple connection classes to share a single dialect.
3. Maintaining backward compatibility for user-facing APIs

Current Problems

• PostgreSQL has both PostgreSQL and PqConnection classes
• MySQL has MySQL, MySQLConnection, and MariaDBConnection
• Each requires duplicate method definitions:

  sql_translation.PqConnection <- function(...) { ... }
  sql_translation.PostgreSQL <- sql_translation.PqConnection

• ~60 alias definitions across all backends

There's no easy way to support backends that talk to multiple different datasets (e.g. odbc, adbc).

Proposed Architecture

1. SQL Dialect Object Structure

Introduce new dialect object used for dispatch:

new_sql_dialect <- function(dialect, supports_window_clause = FALSE, supports_table_alias_with_as = TRUE) {
  check_string(dialect)
  check_bool(supports_window_clause)
  check_bool(supports_table_alias_with_as)

  structure(
    list(
      supports_window_clause = supports_window_clause,
      supports_table_alias_with_as = supports_table_alias_with_as
    ),
    class = c(paste0("sql_dialect_", dialect), "sql_dialect"))
}

Key decisions:

• Translations remain separate in sql_translation() methods
• Inheritance handled at translation level (not in dialect object)
• Dialect now stores support booleans rather than using generics.
  (This change only affects the dittodb package.)

2. Dispatch Mechanism

Use iterated dispatch: connection → dialect, then dialect → method.

#' Get SQL dialect for a connection
#' @export
sql_dialect <- function(con) {
  UseMethod("sql_dialect")
}

#' @export
sql_dialect.DBIConnection <- function(con) {
  # For backward compatibility
  con
}

Then each backend defines its dialect:

# In backend-postgres.R
#' @export
sql_dialect.PqConnection <- function(con) {
  dialect_postgres()
}

#' @export
sql_dialect.PostgreSQL <- function(con) {
  dialect_postgres()
}

# Not strictly necessary to have a dialect helper now, but will be
# useful in the future if we add additional information (e.g. quoting info)
dialect_postgres <- function() {
  new_sql_dialect("postgres")
}

3. SQL generation generics call sql_dialect()

All SQL generation generics first call sql_dialect() and then dispatch on that. Note that due to the limitations of UseMethod() this requires a special helper.

#' @export
#' @rdname db-sql
sql_translation <- function(con) {
  dialect <- sql_dialect(con)
  sql_translation_(dialect)
}
sql_translation_ <- function(dialect) {
  UseMethod("sql_translation_")
}

This extra layer in conjunction with sql_dialect.DBIConnection above, means that sql_dialect() is opt-in and existing extensions will continue to work.

Which generics need this pattern?

The following SQL generation generics should use iterated dispatch:

• sql_translation() - returns translation environment
• sql_expr_matches() - generates SQL for equality with NULL matching
• sql_query_*() - all query generation functions
• sql_table_*() - table manipulation functions

The support boolean generics (supports_window_clause() and db_supports_table_alias_with_as()) will be replaced by accessing dialect fields directly (see Section 6 below).

4. Update existing methods

Next we need to update each sql_* method to instead dispatch on the dialect.

Important: Backend implementers write methods directly on the dialect class (e.g., sql_expr_matches.sql_dialect_postgres). The iterated dispatch wrapper is only needed in the user-facing generic (handled in Section 3).

# Before
sql_expr_matches.PqConnection <- function(con, x, y, ...) {
  # https://www.postgresql.org/docs/current/functions-comparison.html
  glue_sql2(con, "{x} IS NOT DISTINCT FROM {y}")
}
sql_expr_matches.PostgreSQL <- sql_expr_matches.PqConnection

# After
sql_expr_matches.sql_dialect_postgres <- function(con, x, y, ...) {
  # https://www.postgresql.org/docs/current/functions-comparison.html
  glue_sql2(con, "{x} IS NOT DISTINCT FROM {y}")
}

Note: The first argument is still named con for consistency, but it will receive the dialect object when called through the iterated dispatch system.

5. Replace "simulate_" with "dialect_" functions

Old simulate functions should be deprecated in favor of exporting the new dialect_ functions.

# simulate_postgres() continues to return a mock connection for testing
# This ensures backward compatibility for existing tests
simulate_postgres <- function() simulate_dbi("PqConnection")

# dialect_postgres() returns the dialect object
# This is exported for extension authors
#' @export
dialect_postgres <- function() {
  new_sql_dialect(
    "postgres",
    supports_window_clause = TRUE,
    supports_table_alias_with_as = TRUE
  )
}

The simulate_*() functions remain unchanged and continue to return mock connection objects for testing purposes. The new dialect_*() functions are separate and return dialect objects directly.

6. Accessing dialect properties

The dialect object stores support booleans as fields rather than using generics. Code that needs to check these properties should access them directly from the dialect.

# Old approach - using generics
if (supports_window_clause(con)) {
  # generate WINDOW clause
}

# New approach - accessing dialect fields
dialect <- sql_dialect(con)
if (dialect$supports_window_clause) {
  # generate WINDOW clause
}

The old supports_window_clause() and db_supports_table_alias_with_as() can then be deleted as they are not used outside of dbplyr.``

This maintains the existing API while using the new dialect system internally. Eventually, internal dbplyr code can migrate to accessing dialect fields directly for efficiency.

7. Supporting ODBC and ADBC

A key benefit of the dialect system is supporting backends that connect to multiple database types (like ODBC and ADBC). These backends can inspect the connection and return the appropriate dialect:

#' @export
sql_dialect.OdbcConnection <- function(con) {
  # Dispatch based on the actual database driver
  dbms <- con@info$dbms.name

  switch(dbms,
    PostgreSQL = dialect_postgres(),
    MySQL = dialect_mysql(),
    "Microsoft SQL Server" = dialect_mssql(),
    SQLite = dialect_sqlite(),
    # Add more as needed
    dialect_odbc_generic()  # fallback for unknown databases
  )
}

# Fallback dialect for unknown ODBC databases
dialect_odbc_generic <- function() {
  new_sql_dialect("odbc_generic")
}

This allows ODBC connections to automatically use the correct SQL dialect for the underlying database, eliminating the need for separate backend implementations for each ODBC driver.

8. Migration strategy

Backends can migrate incrementally:

1. Add dialect definition: Create sql_dialect.ClassName() method and dialect_name() helper
2. Migrate methods one at a time: Update sql_*.ClassName() methods to sql_*.sql_dialect_name()
3. Delete aliases: Once a method is migrated, delete the duplicate definitions for other connection classes
4. Delete old support methods: Once fully migrated, remove old supports_*() method definitions

The sql_dialect.DBIConnection() default ensures backward compatibility - existing backends that haven't migrated yet will continue to work because their connection object will be passed through unchanged.

Migration can happen one backend at a time. There's no need to migrate all backends simultaneously. Mixed states are fully supported during the transition period.

9. Exports and documentation

What to export:

• sql_dialect() - user-facing generic (already exported via @export in db-sql.R)
• new_sql_dialect() - constructor for extension authors
• dialect_*() - dialect helpers for each backend (e.g., dialect_postgres(), dialect_mysql())

What NOT to export:

• sql_*_() helpers (internal use only, underscore indicates private)
• sql_dialect_* classes (implicit through S3 dispatch, no need for explicit export)

Documentation updates needed:

1. Update ?db-sql help topic to explain the dialect system and iterated dispatch
2. Add new ?sql_dialect help topic documenting the generic and how to implement it
3. Add ?new_sql_dialect topic for extension authors
4. Update vignette("new-backend") to show the dialect approach
5. Add dialect functions to _pkgdown.yml reference index

10. Testing approach

For existing tests:

• No changes needed. Tests using simulate_postgres() will continue to work because:
  • simulate_postgres() returns a mock connection object
  • SQL methods will dispatch through sql_dialect()
  • sql_dialect.DBIConnection() returns the connection unchanged
  • Methods fall back to connection-based dispatch

For new tests:

• Tests can optionally use dialect objects directly:

  test_that("postgres translation works", {
    dialect <- dialect_postgres()
    expect_equal(
      sql_expr_matches(dialect, "x", "y"),
      sql("x IS NOT DISTINCT FROM y")
    )
  })

Testing the dialect system itself:

• Add tests verifying that sql_dialect() returns the correct dialect for each connection type
• Test that dialect properties (like supports_window_clause) are correctly set
• Test ODBC/ADBC dynamic dialect selection

[krlmlr]

I like the initiative, only minor remarks:

• supports_table_alias_with_as and supports_window_clause : I don't understand what's special about these, compared to, e.g., the translations. Why not also store the translations directly in the dialect object? Conversely, if no custom sql_dialect() method is defined for a connection and we query these properties directly from the connection object disguised as a dialect, at some point con$supports_table_alias_with_as might be queried, which would lead to an error.

• Perhaps:

  #' @export
  sql_dialect.DBIConnection <- function(con) {
    # Lifecycle/edition warnings
    new_sql_dialect("dbi", con = con)
  }
  
  #' @export
  sql_expr_matches.sql_dialect_dbi <- function(con, x, y, ...) {
    # Explicit fallback path
    sql_expr_matches(con$con, x, y, ...)
  }

  This might open the door for pulling many more details into the dialect object over time.

• I understand that we can migrate backend after backend. Do we agree that for each backend it's either "in" or "out"? Can we provide search-and-replace or AI agent instructions to make this transition as smooth as possible?

• Can we assume the dialect to be stable over the lifetime of a connection? Is it even stable across all instances of the same connection class? How would we go about caching, in particular if the dialect object becomes richer?

[hadley]

• supports_table_alias_with_as and supports_window_clause are simple generics which aren't used by many packages, so we can change without breaking backward compatibility. I originally had the same thought of you as including the translations directly in the dialect, but I can't see how to do that without breaking existing code.
• Maaaaaaaybe. It just takes so long to deprecate this sort of stuff and I think the benefit is fairly small.
• Yeah, I think that's a good idea
• Hmmm, yes, I think that's a good argument for why the dialect object should be cheap to compute.