adbi: 'DBI' Compliant Database Access Using 'ADBC'

Description

In order to make Arrow Database Connectivity ('ADBC' https://arrow.apache.org/adbc/) accessible from R, an interface compliant with the 'DBI' package is provided, using driver back-ends that are implemented in the 'adbcdrivermanager' framework. This enables interacting with database systems using the Arrow data format, thereby offering an efficient alternative to 'ODBC' for analytical applications.

Author(s)

Maintainer: Nicolas Bennett nicolas@cynkra.com

Other contributors:

• Voltron Data [funder]

See Also

Useful links:

• https://adbi.r-dbi.org

• https://github.com/r-dbi/adbi

• https://arrow.apache.org/adbc/

• Report bugs at https://github.com/r-dbi/adbi/issues

Class AdbiConnection (and methods)

Description

AdbiConnection objects are created by passing adbi() as first argument to DBI::dbConnect(). They are a superclass of the DBIConnection class. The "Usage" section lists the class methods overridden by adbi.

Usage

## S4 method for signature 'AdbiConnection'
dbAppendTable(conn, name, value, ..., row.names = NULL)

## S4 method for signature 'AdbiConnection'
dbBegin(conn, ...)

## S4 method for signature 'AdbiConnection'
dbCommit(conn, ...)

## S4 method for signature 'AdbiConnection'
dbDataType(dbObj, obj, ...)

## S4 method for signature 'AdbiConnection,Id'
dbExistsTable(conn, name, ...)

## S4 method for signature 'AdbiConnection,SQL'
dbExistsTable(conn, name, ...)

## S4 method for signature 'AdbiConnection,character'
dbExistsTable(conn, name, ...)

## S4 method for signature 'AdbiConnection'
dbGetInfo(dbObj, ...)

## S4 method for signature 'AdbiConnection'
dbIsValid(dbObj, ...)

## S4 method for signature 'AdbiConnection,Id'
dbListFields(conn, name, ...)

## S4 method for signature 'AdbiConnection,SQL'
dbListFields(conn, name, ...)

## S4 method for signature 'AdbiConnection,character'
dbListFields(conn, name, ...)

## S4 method for signature 'AdbiConnection'
dbListTables(conn, ...)

## S4 method for signature 'AdbiConnection,character'
dbQuoteIdentifier(conn, x, ...)

## S4 method for signature 'AdbiConnection,character'
dbQuoteLiteral(conn, x, ...)

## S4 method for signature 'AdbiConnection,character'
dbQuoteString(conn, x, ...)

## S4 method for signature 'AdbiConnection,character'
dbRemoveTable(conn, name, ..., temporary = FALSE, fail_if_missing = TRUE)

## S4 method for signature 'AdbiConnection,Id'
dbRemoveTable(conn, name, ..., temporary = FALSE, fail_if_missing = TRUE)

## S4 method for signature 'AdbiConnection'
dbRollback(conn, ...)

## S4 method for signature 'AdbiConnection'
dbUnquoteIdentifier(conn, x, ...)

## S4 method for signature 'AdbiConnection,Id,data.frame'
dbWriteTable(
  conn,
  name,
  value,
  overwrite = FALSE,
  append = FALSE,
  ...,
  field.types = NULL,
  row.names = NULL,
  temporary = FALSE
)

## S4 method for signature 'AdbiConnection,SQL,data.frame'
dbWriteTable(conn, name, value, ...)

## S4 method for signature 'AdbiConnection,character,data.frame'
dbWriteTable(conn, name, value, ...)

## S4 method for signature 'AdbiConnection'
show(object)

Arguments

See Also

The corresponding generic functions DBI::dbSendQuery(), DBI::dbGetQuery(), DBI::dbSendStatement(), DBI::dbExecute(), DBI::dbExistsTable(), DBI::dbListTables(), DBI::dbListFields(), DBI::dbRemoveTable(), and DBI::sqlData().

Class AdbiDriver (and methods)

Description

AdbiDriver objects are created by adbi(), and used to select the correct method in dbConnect(). They are a superclass of the DBIDriver class, and used purely for dispatch. The "Usage" section lists the class methods overridden by adbi.

Usage

## S4 method for signature 'AdbiDriver'
dbDataType(dbObj, obj, ...)

## S4 method for signature 'AdbiDriver'
dbGetInfo(dbObj, ...)

## S4 method for signature 'AdbiDriver'
dbIsValid(dbObj, ...)

## S4 method for signature 'AdbiDriver'
show(object)

Arguments

Class AdbiResult (and methods)

Description

AdbiResult objects are created by DBI::dbSendQuery() or DBI::dbSendStatement(), and encapsulate the result of an SQL statement (either SELECT or not). They are a superclass of the DBIResult class. The "Usage" section lists the class methods overridden by adbi.

Usage

## S4 method for signature 'AdbiResult'
dbBindArrow(res, params, ...)

## S4 method for signature 'AdbiResult'
dbBind(res, params, ...)

## S4 method for signature 'AdbiResult'
dbClearResult(res, ...)

## S4 method for signature 'AdbiResult'
dbColumnInfo(res, ...)

## S4 method for signature 'AdbiResult'
dbGetRowCount(res, ...)

## S4 method for signature 'AdbiResult'
dbGetRowsAffected(res, ...)

## S4 method for signature 'AdbiResult'
dbGetStatement(res, ...)

## S4 method for signature 'AdbiResult'
dbHasCompleted(res, ...)

## S4 method for signature 'AdbiResult'
dbIsValid(dbObj, ...)

## S4 method for signature 'AdbiResult'
show(object)

Arguments

See Also

The corresponding generic functions DBI::dbFetch(), DBI::dbClearResult(), DBI::dbBind(), DBI::dbColumnInfo(), DBI::dbGetRowsAffected(), DBI::dbGetRowCount(), DBI::dbHasCompleted(), and DBI::dbGetStatement().

Class AdbiResultArrow (and methods)

Description

AdbiResultArrow objects are created by DBI::dbSendQueryArrow(), and encapsulate the result of an SQL query (a SELECT statement). They are a superclass of the DBIResultArrow class. The "Usage" section lists the class methods overridden by adbi.

Usage

## S4 method for signature 'AdbiResultArrow'
dbBindArrow(res, params, ...)

## S4 method for signature 'AdbiResultArrow'
dbBind(res, params, ...)

## S4 method for signature 'AdbiResultArrow'
dbClearResult(res, ...)

## S4 method for signature 'AdbiResultArrow'
dbColumnInfo(res, ...)

## S4 method for signature 'AdbiResultArrow'
dbFetchArrowChunk(res, ...)

## S4 method for signature 'AdbiResultArrow'
dbFetchArrow(res, ...)

## S4 method for signature 'AdbiResultArrow'
dbGetRowCount(res, ...)

## S4 method for signature 'AdbiResultArrow'
dbGetRowsAffected(res, ...)

## S4 method for signature 'AdbiResultArrow'
dbGetStatement(res, ...)

## S4 method for signature 'AdbiResultArrow'
dbHasCompleted(res, ...)

## S4 method for signature 'AdbiResultArrow'
dbIsValid(dbObj, ...)

## S4 method for signature 'AdbiResultArrow'
show(object)

Arguments

See Also

The corresponding generic functions DBI::dbFetchArrow(), DBI::dbFetchArrowChunk(), DBI::dbClearResult(), DBI::dbBind(), DBI::dbColumnInfo(), DBI::dbGetRowsAffected(), DBI::dbGetRowCount(), DBI::dbHasCompleted(), and DBI::dbGetStatement().

Adbi driver

Description

In order to open a database connection, DBI::dbConnect() dispatches on a driver object, which can be instantiated by calling adbi().

Usage

adbi(driver = NA_character_)

## S4 method for signature 'AdbiDriver'
dbConnect(drv, ..., bigint = NULL)

## S4 method for signature 'AdbiConnection'
dbDisconnect(conn, force = getOption("adbi.force_close_results", FALSE), ...)

Arguments

Details

To specify the type of adbc driver, adbi accepts as driver argument

• an object inheriting from adbc_driver,

• a function that can be evaluated with no arguments and returns an object inheriting from adbc_driver,

• a string of the form pkg::fun (where ⁠pkg::⁠ is optional and defaults to fun), which can be used to look up such a function.

As default, an adbcdrivermanager::adbc_driver_monkey() object is created.

Value

A connection object (S4 class AdbiCOnnection, inheriting from DBIConnection) is returned by dbConnect(), while dbDisconnect() returns TRUE invisibly.

Examples

adbi()
if (requireNamespace("adbcsqlite")) {
  adbi("adbcsqlite")
}
library(DBI)
con <- dbConnect(adbi())
dbIsValid(con)
dbDisconnect(con)
dbIsValid(con)

Fetch result sets

Description

When fetching results using dbFetch(), the argument n can be specified to control chunk size per fetching operation. The default value of -1 corresponds to retrieving the entire result set at once, while a positive integer will try returning as many rows (as long as n does not exceed the available number of rows), in line with standard DBI expectations. As data transfer is mediated by Arrow data structures, which are retrieved as array chunks, the underlying chunk size can be used by passing an n value NA.

Usage

## S4 method for signature 'AdbiResult'
dbFetch(res, n = -1, ...)

Arguments

Value

A data.frame with the requested number of rows (or zero rows if dbFetch() is called on a result set with no more remaining rows).

Examples

if (requireNamespace("adbcsqlite")) {
  library(DBI)
  con <- dbConnect(adbi::adbi("adbcsqlite"), uri = ":memory:")
  dbWriteTable(con, "swiss", swiss)
  res <- dbSendQuery(con, "SELECT * from swiss WHERE Agriculture < 30")
  dbFetch(res)
  dbClearResult(res)
  dbDisconnect(con)
}

Create result sets

Description

Creating result sets using dbSendQuery() (and by extension using dbGetQuery()) mostly follows DBI specification. One way where adbi deviates from DBI mechanisms is how the bigint setting is not only per connection, but the per-connection setting can be overridden on a result set basis. As default, the connection setting is applied, but passing one of the accepted values as bigint when creating a result set will subsequently use that setting for all fetches using this result set.

Usage

## S4 method for signature 'AdbiConnection'
dbSendQueryArrow(
  conn,
  statement,
  ...,
  params = NULL,
  immediate = NULL,
  bigint = NULL
)

## S4 method for signature 'AdbiConnection,character'
dbSendQuery(
  conn,
  statement,
  ...,
  params = NULL,
  immediate = NULL,
  bigint = NULL
)

## S4 method for signature 'AdbiConnection,character'
dbSendStatement(
  conn,
  statement,
  ...,
  params = NULL,
  immediate = NULL,
  bigint = NULL
)

Arguments

Details

Multiple open result sets per connection are supported and support can be disabled by setting options(adbi.allow_multiple_results = FALSE). If not enabled, creating a new result will finalize potential other results and throw a warning.

Value

An S4 class AdbiResult (inheriting from DBIResult).

See Also

adbi-driver

Examples

if (requireNamespace("adbcsqlite")) {
  library(DBI)
  con <- dbConnect(adbi::adbi("adbcsqlite"), uri = ":memory:")
  dbWriteTable(con, "swiss", swiss)
  str(
    dbGetQuery(con, "SELECT Examination from swiss WHERE Agriculture < 30")
  )
  str(
    dbGetQuery(con, "SELECT Examination from swiss WHERE Agriculture < 30",
      bigint = "integer")
  )
  dbDisconnect(con)
}

Objects exported from other packages

Description

These objects are imported from other packages. Follow the links below to see their documentation.

DBI

dbCanConnect, Id