Nothing
#' Backend: MS Access
#'
#' @description
#' See `vignette("translation-function")` and `vignette("translation-verb")` for
#' details of overall translation technology. Key differences for this backend
#' are:
#'
#' * `SELECT` uses `TOP`, not `LIMIT`
#' * Non-standard types and mathematical functions
#' * String concatenation uses `&`
#' * No `ANALYZE` equivalent
#' * `TRUE` and `FALSE` converted to 1 and 0
#'
#' Use `simulate_access()` with `lazy_frame()` to see simulated SQL without
#' converting to live access database.
#'
#' @name backend-access
#' @aliases NULL
#' @examples
#' library(dplyr, warn.conflicts = FALSE)
#' lf <- lazy_frame(x = 1, y = 2, z = "a", con = simulate_access())
#'
#' lf %>% head()
#' lf %>% mutate(y = as.numeric(y), z = sqrt(x^2 + 10))
#' lf %>% mutate(a = paste0(z, " times"))
NULL
#' @export
#' @rdname backend-access
simulate_access <- function() simulate_dbi("ACCESS")
#' @export
dbplyr_edition.ACCESS <- function(con) {
2L
}
# sql_ generics --------------------------------------------
#' @export
sql_query_select.ACCESS <- function(con,
select,
from,
where = NULL,
group_by = NULL,
having = NULL,
window = NULL,
order_by = NULL,
limit = NULL,
distinct = FALSE,
...,
subquery = FALSE,
lvl = 0) {
sql_select_clauses(con,
select = sql_clause_select(con, select, distinct, top = limit),
from = sql_clause_from(from),
where = sql_clause_where(where),
group_by = sql_clause_group_by(group_by),
having = sql_clause_having(having),
window = sql_clause_window(window),
order_by = sql_clause_order_by(order_by, subquery, limit),
lvl = lvl
)
}
#' @export
sql_translation.ACCESS <- function(con) {
sql_variant(
sql_translator(.parent = base_scalar,
# Much of this translation comes from: https://www.techonthenet.com/access/functions/
# Conversion
as.numeric = sql_prefix("CDBL"),
as.double = sql_prefix("CDBL"),
# as.integer() always rounds down. CInt does not, but Int does
as.integer = sql_prefix("INT"),
as.logical = sql_prefix("CBOOL"),
as.character = sql_prefix("CSTR"),
as.Date = sql_prefix("CDATE"),
# Math
exp = sql_prefix("EXP"),
log = sql_prefix("LOG"),
log10 = function(x) {
sql_expr(log(!!x) / log(10L))
},
sqrt = sql_prefix("SQR"),
sign = sql_prefix("SGN"),
floor = sql_prefix("INT"),
# Nearly add 1, then drop off the decimal. This results in the equivalent to ceiling()
ceiling = function(x) {
sql_expr(int(!!x + .9999999999))
},
ceil = function(x) {
sql_expr(int(!!x + .9999999999))
},
# There is no POWER function in Access. It uses ^ instead
`^` = function(x, y) {
sql_expr((!!x) ^ (!!y))
},
# Strings
nchar = sql_prefix("LEN"),
tolower = sql_prefix("LCASE"),
toupper = sql_prefix("UCASE"),
# Pull `left` chars from the left, then `right` chars from the right to replicate substr
substr = function(x, start, stop){
right <- stop - start + 1
left <- stop
sql_expr(right(left(!!x, !!left), !!right))
},
trimws = sql_prefix("TRIM"),
# No support for CONCAT in Access
paste = sql_paste_infix(" ", "&", function(x) sql_expr(CStr(!!x))),
paste0 = sql_paste_infix("", "&", function(x) sql_expr(CStr(!!x))),
# Logic
# Access always returns -1 for True and 0 for False
is.null = sql_prefix("ISNULL"),
is.na = sql_prefix("ISNULL"),
# IIF() is like ifelse()
ifelse = function(test, yes, no){
sql_expr(iif(!!test, !!yes, !!no))
},
# Access uses <> for inequality
`!=` = sql_infix("<>"),
# Coalesce doesn't exist in Access.
# NZ() only works while in Access, not with the Access driver
# IIF(ISNULL()) is the best way to construct this
coalesce = function(x, y) {
sql_expr(iif(isnull(!!x), !!y, !!x))
},
# pmin/pmax for 2 columns
pmin = function(x, y) {
sql_expr(iif(!!x <= !!y, !!x, !!y))
},
pmax = function(x, y) {
sql_expr(iif(!!x <= !!y, !!y, !!x))
},
# Dates
Sys.Date = sql_prefix("DATE")
),
sql_translator(.parent = base_agg,
sd = sql_aggregate("STDEV"),
var = sql_aggregate("VAR"),
cor = sql_not_supported("cor"),
cov = sql_not_supported("cov"),
# Count(Distinct *) does not work in Access
# This would work but we don't know the table name when translating:
# SELECT Count(*) FROM (SELECT DISTINCT * FROM table_name) AS T
n_distinct = sql_not_supported("n_distinct"),
),
# Window functions not supported in Access
sql_translator(.parent = base_no_win)
)
}
# db_ generics -----------------------------------
#' @export
sql_table_analyze.ACCESS <- function(con, table, ...) {
# Do nothing. Access doesn't support an analyze / update statistics function
NULL # nocov
}
# Util -------------------------------------------
#' @export
sql_escape_logical.ACCESS <- function(con, x) {
# Access uses a convention of -1 as True and 0 as False
y <- ifelse(x, -1, 0)
y[is.na(x)] <- "NULL"
y
}
#' @export
sql_escape_date.ACCESS <- function(con, x) {
# Access delimits dates using octothorpes, and uses YYYY-MM-DD
y <- format(x, "#%Y-%m-%d#")
y[is.na(x)] <- "NULL"
y
}
#' @export
sql_escape_datetime.ACCESS <- function(con, x) {
# Access delimits datetimes using octothorpes, and uses YYYY-MM-DD HH:MM:SS
# Timezones are not supported in Access
y <- format(x, "#%Y-%m-%d %H:%M:%S#")
y[is.na(x)] <- "NULL"
y
}
#' @export
supports_window_clause.ACCESS <- function(con) {
TRUE
}
utils::globalVariables(c("CStr", "iif", "isnull"))
Any scripts or data that you put into this service are public.
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.