Title:	Database Interface and MariaDB Driver
Version:	1.3.4
Description:	Implements a DBI-compliant interface to MariaDB (https://mariadb.org/) and MySQL (https://www.mysql.com/) databases.
License:	MIT + file LICENSE
URL:	https://rmariadb.r-dbi.org, https://github.com/r-dbi/RMariaDB, https://downloads.mariadb.org/connector-c/
BugReports:	https://github.com/r-dbi/RMariaDB/issues
Depends:	R (≥ 2.8.0)
Imports:	bit64, blob, DBI (≥ 1.1.3), hms (≥ 0.5.0), lubridate, methods, rlang
Suggests:	DBItest (≥ 1.7.2.9001), decor, readr, rprojroot, testthat (≥ 3.0.0), withr
LinkingTo:	cpp11, plogr
Config/Needs/website:	r-dbi/dbitemplate
Config/autostyle/scope:	line_breaks
Config/autostyle/strict:	false
Config/testthat/edition:	3
Encoding:	UTF-8
NeedsCompilation:	yes
RoxygenNote:	7.3.2.9000
SystemRequirements:	libmariadb-client-lgpl-dev or libmariadb-dev or libmysqlclient-dev, with libssl-dev (deb), mariadb-connector-c-devel or mariadb-devel (rpm), mariadb-connector-c or mysql-connector-c (brew)
Collate:	'MariaDBConnection.R' 'MariaDBDriver.R' 'MariaDBResult.R' 'RMariaDB-package.R' 'coerce.R' 'compatRowNames.R' 'connect.R' 'cpp11.R' 'dbAppendTable_MariaDBConnection.R' 'dbBegin_MariaDBConnection.R' 'dbBind_MariaDBResult.R' 'dbClearResult_MariaDBResult.R' 'dbColumnInfo_MariaDBResult.R' 'dbCommit_MariaDBConnection.R' 'dbConnect_MariaDBDriver.R' 'dbDataType_MariaDBConnection.R' 'dbDataType_MariaDBDriver.R' 'dbDisconnect_MariaDBConnection.R' 'dbExistsTable_MariaDBConnection_character.R' 'dbFetch_MariaDBResult.R' 'dbGetInfo_MariaDBConnection.R' 'dbGetInfo_MariaDBDriver.R' 'dbGetRowCount_MariaDBResult.R' 'dbGetRowsAffected_MariaDBResult.R' 'dbGetStatement_MariaDBResult.R' 'dbHasCompleted_MariaDBResult.R' 'dbIsValid_MariaDBConnection.R' 'dbIsValid_MariaDBDriver.R' 'dbIsValid_MariaDBResult.R' 'dbListObjects_MariaDBConnection_ANY.R' 'dbListTables_MariaDBConnection.R' 'dbQuoteIdentifier_MariaDBConnection_Id.R' 'dbQuoteIdentifier_MariaDBConnection_SQL.R' 'dbQuoteIdentifier_MariaDBConnection_character.R' 'dbQuoteLiteral_MariaDBConnection.R' 'dbQuoteLiteral_MySQLConnection.R' 'dbQuoteString_MariaDBConnection_SQL.R' 'dbQuoteString_MariaDBConnection_character.R' 'dbReadTable_MariaDBConnection_character.R' 'dbRemoveTable_MariaDBConnection_character.R' 'dbRollback_MariaDBConnection.R' 'dbSendQuery_MariaDBConnection_character.R' 'dbSendStatement_MariaDBConnection_character.R' 'dbUnloadDriver_MariaDBDriver.R' 'dbUnquoteIdentifier_MariaDBConnection_SQL.R' 'dbWriteTable_MariaDBConnection_character_character.R' 'dbWriteTable_MariaDBConnection_character_data.frame.R' 'default.R' 'export.R' 'query.R' 'quote-mysql.R' 'quote.R' 'show_MariaDBConnection.R' 'sqlData_MariaDBConnection.R' 'table.R' 'transaction.R' 'utils.R' 'zzz.R'
Packaged:	2025-02-24 18:19:17 UTC; kirill
Author:	Kirill Müller [aut, cre], Jeroen Ooms [aut], David James [aut], Saikat DebRoy [aut], Hadley Wickham [aut], Jeffrey Horner [aut], R Consortium [fnd], RStudio [cph]
Maintainer:	Kirill Müller <kirill@cynkra.com>
Repository:	CRAN
Date/Publication:	2025-02-24 18:50:05 UTC

RMariaDB: Database Interface and MariaDB Driver

Description

Implements a DBI-compliant interface to MariaDB (https://mariadb.org/) and MySQL (https://www.mysql.com/) databases.

Author(s)

Maintainer: Kirill Müller kirill@cynkra.com (ORCID)

Authors:

• Jeroen Ooms (ORCID)

• David James

• Saikat DebRoy

• Hadley Wickham

• Jeffrey Horner

Other contributors:

• R Consortium [funder]

• RStudio [copyright holder]

See Also

Useful links:

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

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

• https://downloads.mariadb.org/connector-c/

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

Client flags

Description

Use for the client.flag argument to DBI::dbConnect(), multiple flags can be combined with + or bitwOr(). The flags are provided for completeness. To enforce SSL for the DB connection, add the flag CLIENT_SSL.

See Also

The flags argument at https://mariadb.com/kb/en/library/mysql_real_connect.

Examples

## Not run: 
library(DBI)
library(RMariaDB)
con1 <- dbConnect(MariaDB(), client.flag = CLIENT_COMPRESS)
con2 <- dbConnect(
  MariaDB(),
  client.flag = bitwOr(CLIENT_COMPRESS, CLIENT_SSL)
)

## End(Not run)

Connect/disconnect to a MariaDB DBMS

Description

These methods are straight-forward implementations of the corresponding generic functions.

Usage

MariaDB()

## S4 method for signature 'MariaDBDriver'
dbConnect(
  drv,
  dbname = NULL,
  username = NULL,
  password = NULL,
  host = NULL,
  unix.socket = NULL,
  port = 0,
  client.flag = 0,
  group = "rs-dbi",
  default.file = NULL,
  ssl.key = NULL,
  ssl.cert = NULL,
  ssl.ca = NULL,
  ssl.capath = NULL,
  ssl.cipher = NULL,
  ...,
  groups = NULL,
  load_data_local_infile = FALSE,
  bigint = c("integer64", "integer", "numeric", "character"),
  timeout = 10,
  timezone = "+00:00",
  timezone_out = NULL,
  reconnect = FALSE,
  mysql = NULL
)

Arguments

Time zones

MySQL and MariaDB support named time zones, they must be installed on the server. See https://dev.mysql.com/doc/mysql-g11n-excerpt/8.0/en/time-zone-support.html for more details. Without installation, time zone support is restricted to UTC offset, which cannot take into account DST offsets.

Secure passwords

Avoid storing passwords hard-coded in the code, use e.g. the keyring package to store and retrieve passwords in a secure way.

The MySQL client library (but not MariaDB) supports a .mylogin.cnf file that can be passed in the default.file argument. This file can contain an obfuscated password, which is not a secure way to store passwords but may be acceptable if the user is aware of the restrictions. The availability of this feature depends on the client library used for compiling the RMariaDB package. Windows and macOS binaries on CRAN are compiled against the MariaDB Connector/C client library which do not support this feature.

References

Configuration files: https://mariadb.com/kb/en/library/configuring-mariadb-with-mycnf/

Examples

if (mariadbHasDefault()) {
  # connect to a database and load some data
  con <- dbConnect(RMariaDB::MariaDB(), dbname = "test")
  dbWriteTable(con, "USArrests", datasets::USArrests, temporary = TRUE)

  # query
  rs <- dbSendQuery(con, "SELECT * FROM USArrests")
  d1 <- dbFetch(rs, n = 10)      # extract data in chunks of 10 rows
  dbHasCompleted(rs)
  d2 <- dbFetch(rs, n = -1)      # extract all remaining data
  dbHasCompleted(rs)
  dbClearResult(rs)
  dbListTables(con)

  # clean up
  dbDisconnect(con)
}
## Not run: 
# Connect to a MariaDB database running locally
con <- dbConnect(RMariaDB::MariaDB(), dbname = "mydb")
# Connect to a remote database with username and password
con <- dbConnect(RMariaDB::MariaDB(),
  host = "mydb.mycompany.com",
  user = "abc", password = "def"
)
# But instead of supplying the username and password in code, it's usually
# better to set up a group in your .my.cnf (usually located in your home
# directory). Then it's less likely you'll inadvertently share them.
con <- dbConnect(RMariaDB::MariaDB(), group = "test")

# To connect to a remote database and require the use of SSL
(and an example of using environment variables for your sensitive info)
con <- dbConnect(RMariaDB::MariaDB(),
  dbname = Sys.getenv('DB_NAME'),
  host = Sys.getenv('DB_HOST'),
  user = Sys.getenv('DB_USER'),
  password = Sys.getenv('DB_PASSWORD'),
  client.flag = CLIENT_SSL
)

# Always cleanup by disconnecting the database
dbDisconnect(con)

## End(Not run)

# All examples use the rs-dbi group by default.
if (mariadbHasDefault()) {
  con <- dbConnect(RMariaDB::MariaDB(), dbname = "test")
  con
  dbDisconnect(con)
}

Class MariaDBConnection.

Description

"MariaDBConnection" objects are usually created by DBI::dbConnect(). They represent a connection to a MariaDB or MySQL database.

Usage

## S3 method for class 'MariaDBConnection'
format(x, ...)

## S4 method for signature 'MariaDBConnection'
dbDisconnect(conn, ...)

## S4 method for signature 'MariaDBConnection'
dbGetInfo(dbObj, what = "", ...)

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

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

Details

The "MySQLConnection" class is a subclass of "MariaDBConnection". Objects of that class are created by dbConnect(MariaDB(), ..., mysql = TRUE) to indicate that the server is a MySQL server. The RMariaDB package supports both MariaDB and MySQL servers, but the SQL dialect and other details vary. The default is to detect the server type based on the version number.

The older RMySQL package also implements the "MySQLConnection" class. The S4 system is able to distinguish between RMariaDB and RMySQL objects even if both packages are loaded.

Class MariaDBDriver with constructor MariaDB.

Description

An MariaDB driver implementing the R database (DBI) API. This class should always be initialized with the MariaDB() function. It returns a singleton that allows you to connect to MariaDB.

Usage

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

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

## S4 method for signature 'MariaDBDriver'
dbUnloadDriver(drv, ...)

Class MariaDBResult

Description

MariaDB's query results class. This classes encapsulates the result of an SQL query or statement.

Usage

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

Determine the SQL Data Type of an S object

Description

This method is a straight-forward implementation of the corresponding generic function.

Usage

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

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

Arguments

Examples

dbDataType(RMariaDB::MariaDB(), "a")
dbDataType(RMariaDB::MariaDB(), 1:3)
dbDataType(RMariaDB::MariaDB(), 2.5)

Quote MariaDB strings and identifiers.

Description

In MariaDB, identifiers are enclosed in backticks, e.g. `x`.

Usage

## S4 method for signature 'MariaDBConnection,Id'
dbQuoteIdentifier(conn, x, ...)

## S4 method for signature 'MariaDBConnection,SQL'
dbQuoteIdentifier(conn, x, ...)

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

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

## S4 method for signature 'MariaDBConnection,SQL'
dbQuoteString(conn, x, ...)

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

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

Examples

if (mariadbHasDefault()) {
  con <- dbConnect(RMariaDB::MariaDB())
  dbQuoteIdentifier(con, c("a b", "a`b"))
  dbQuoteString(con, c("a b", "a'b"))
  dbDisconnect(con)
}

Read and write MariaDB tables.

Description

These methods read or write entire tables from a MariaDB database.

Usage

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

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

## S4 method for signature 'MariaDBConnection'
dbListObjects(conn, prefix = NULL, ...)

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

## S4 method for signature 'MariaDBConnection,character'
dbReadTable(conn, name, ..., row.names = FALSE, check.names = TRUE)

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

## S4 method for signature 'MariaDBConnection,character,character'
dbWriteTable(
  conn,
  name,
  value,
  field.types = NULL,
  overwrite = FALSE,
  append = FALSE,
  header = TRUE,
  row.names = FALSE,
  nrows = 50,
  sep = ",",
  eol = "\n",
  skip = 0,
  quote = "\"",
  temporary = FALSE,
  ...
)

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

Arguments

Details

When using load_data_local_infile = TRUE in DBI::dbConnect(), pass safe = FALSE to dbAppendTable() to avoid transactions. Because ⁠LOAD DATA INFILE⁠ is used internally, this means that rows violating primary key constraints are now silently ignored.

Value

A data.frame in the case of dbReadTable(); otherwise a logical indicating whether the operation was successful.

Note

The data.frame returned by dbReadTable() only has primitive data, e.g., it does not coerce character data to factors. Temporary tables are ignored for dbExistsTable() and dbListTables() due to limitations of the underlying C API. For this reason, a prior existence check is performed only before creating a regular persistent table; an attempt to create a temporary table with an already existing name will fail with a message from the database driver.

Examples

if (mariadbHasDefault()) {
  con <- dbConnect(RMariaDB::MariaDB(), dbname = "test")

  # By default, row names are written in a column to row_names, and
  # automatically read back into the row.names()
  dbWriteTable(con, "mtcars", mtcars[1:5, ], temporary = TRUE)
  dbReadTable(con, "mtcars")
  dbReadTable(con, "mtcars", row.names = FALSE)
}

MariaDB Check for Compiled Versus Loaded Client Library Versions

Description

This function prints out the compiled and loaded client library versions.

Usage

mariadbClientLibraryVersions()

Value

A named integer vector of length two, the first element representing the compiled library version and the second element representing the loaded client library version.

Examples

mariadbClientLibraryVersions()

Check if default database is available.

Description

RMariaDB examples and tests connect to a database defined by the rs-dbi group in ⁠~/.my.cnf⁠. This function checks if that database is available, and if not, displays an informative message. mariadbDefault() works similarly but throws a testthat skip condition on failure, making it suitable for use in tests.

Usage

mariadbHasDefault()

mariadbDefault()

Examples

if (mariadbHasDefault()) {
  db <- dbConnect(RMariaDB::MariaDB(), dbname = "test")
  dbListTables(db)
  dbDisconnect(db)
}

Quote MySQL strings and identifiers.

Description

In MySQL, identifiers are enclosed in backticks, e.g. `x`.

Usage

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

Execute a SQL statement on a database connection.

Description

To retrieve results a chunk at a time, use DBI::dbSendQuery(), DBI::dbFetch(), then DBI::dbClearResult(). Alternatively, if you want all the results (and they'll fit in memory) use DBI::dbGetQuery() which sends, fetches and clears for you. For data manipulation queries (i.e. queries that do not return data, such as UPDATE, DELETE, etc.), DBI::dbSendStatement() serves as a counterpart to DBI::dbSendQuery(), while DBI::dbExecute() corresponds to DBI::dbGetQuery().

Usage

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

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

## S4 method for signature 'MariaDBResult'
dbFetch(res, n = -1, ..., row.names = FALSE)

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

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

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

Arguments

Examples

if (mariadbHasDefault()) {
  con <- dbConnect(RMariaDB::MariaDB(), dbname = "test")
  dbWriteTable(con, "arrests", datasets::USArrests, temporary = TRUE)

  # Run query to get results as dataframe
  dbGetQuery(con, "SELECT * FROM arrests limit 3")

  # Send query to pull requests in batches
  res <- dbSendQuery(con, "SELECT * FROM arrests")
  data <- dbFetch(res, n = 2)
  data
  dbHasCompleted(res)

  dbClearResult(res)
  dbDisconnect(con)
}

Objects exported from other packages

Description

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

DBI

Id

Database interface meta-data.

Description

See documentation of generics for more details.

Usage

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

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

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

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

Arguments

Examples

if (mariadbHasDefault()) {
  con <- dbConnect(RMariaDB::MariaDB(), dbname = "test")
  dbWriteTable(con, "t1", datasets::USArrests, temporary = TRUE)

  rs <- dbSendQuery(con, "SELECT * FROM t1 WHERE UrbanPop >= 80")
  rs

  dbGetStatement(rs)
  dbHasCompleted(rs)
  dbColumnInfo(rs)

  dbFetch(rs)
  rs

  dbClearResult(rs)
  dbDisconnect(con)
}

DBMS Transaction Management

Description

Commits or roll backs the current transaction in an MariaDB connection. Note that in MariaDB DDL statements (e.g. ⁠CREATE TABLE⁠) cannot be rolled back.

Usage

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

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

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

Arguments

Examples

if (mariadbHasDefault()) {
  con <- dbConnect(RMariaDB::MariaDB(), dbname = "test")
  df <- data.frame(id = 1:5)

  dbWriteTable(con, "df", df, temporary = TRUE)
  dbBegin(con)
  dbExecute(con, "UPDATE df SET id = id * 10")
  dbGetQuery(con, "SELECT id FROM df")
  dbRollback(con)

  dbGetQuery(con, "SELECT id FROM df")

  dbDisconnect(con)
}