Package 'RSQLite'

Description

This database is bundled with the package, and contains all data frames in the datasets package.

Usage

datasetsDb()

Examples

library(DBI)
db <- RSQLite::datasetsDb()
dbListTables(db)

dbReadTable(db, "CO2")
dbGetQuery(db, "SELECT * FROM CO2 WHERE conc < 100")

dbDisconnect(db)

Description

By default, SQLite is in auto-commit mode. dbBegin() starts a SQLite transaction and turns auto-commit off. dbCommit() and dbRollback() commit and rollback the transaction, respectively and turn auto-commit on. DBI::dbWithTransaction() is a convenient wrapper that makes sure that dbCommit() or dbRollback() is called. A helper function sqliteIsTransacting() is available to check the current transaction status of the connection.

Usage

## S4 method for signature 'SQLiteConnection'
dbBegin(conn, .name = NULL, ..., name = NULL)

## S4 method for signature 'SQLiteConnection'
dbCommit(conn, .name = NULL, ..., name = NULL)

## S4 method for signature 'SQLiteConnection'
dbRollback(conn, .name = NULL, ..., name = NULL)

sqliteIsTransacting(conn)

Arguments

See Also

The corresponding generic functions DBI::dbBegin(), DBI::dbCommit(), and DBI::dbRollback().

Examples

library(DBI)
con <- dbConnect(SQLite(), ":memory:")
dbWriteTable(con, "arrests", datasets::USArrests)
dbGetQuery(con, "select count(*) from arrests")

dbBegin(con)
rs <- dbSendStatement(con, "DELETE from arrests WHERE Murder > 1")
dbGetRowsAffected(rs)
dbClearResult(rs)

dbGetQuery(con, "select count(*) from arrests")

dbRollback(con)
dbGetQuery(con, "select count(*) from arrests")[1, ]

dbBegin(con)
rs <- dbSendStatement(con, "DELETE FROM arrests WHERE Murder > 5")
dbClearResult(rs)
dbCommit(con)
dbGetQuery(con, "SELECT count(*) FROM arrests")[1, ]

# Named savepoints can be nested --------------------------------------------
dbBegin(con, name = "a")
dbBegin(con, name = "b")
sqliteIsTransacting(con)
dbRollback(con, name = "b")
dbCommit(con, name = "a")

dbDisconnect(con)

Description

Returns the contents of a database table given by name as a data frame.

Usage

## S4 method for signature 'SQLiteConnection,character'
dbReadTable(
  conn,
  name,
  ...,
  row.names = pkgconfig::get_config("RSQLite::row.names.table", FALSE),
  check.names = TRUE,
  select.cols = NULL
)

Arguments

Details

Note that the data frame returned by dbReadTable() only has primitive data, e.g., it does not coerce character data to factors.

Value

A data frame.

See Also

The corresponding generic function DBI::dbReadTable().

Examples

library(DBI)
db <- RSQLite::datasetsDb()
dbReadTable(db, "mtcars")
dbReadTable(db, "mtcars", row.names = FALSE)
dbDisconnect(db)

Description

Functions for writing data frames or delimiter-separated files to database tables.

Usage

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

## S4 method for signature 'SQLiteConnection,character,data.frame'
dbWriteTable(
  conn,
  name,
  value,
  ...,
  row.names = pkgconfig::get_config("RSQLite::row.names.table", FALSE),
  overwrite = FALSE,
  append = FALSE,
  field.types = NULL,
  temporary = FALSE
)

Arguments

Details

In a primary key column qualified with AUTOINCREMENT, missing values will be assigned the next largest positive integer, while nonmissing elements/cells retain their value. If the autoincrement column exists in the data frame passed to the value argument, the NA elements are overwritten. Similarly, if the key column is not present in the data frame, all elements are automatically assigned a value.

See Also

The corresponding generic function DBI::dbWriteTable().

Examples

con <- dbConnect(SQLite())
dbWriteTable(con, "mtcars", mtcars)
dbReadTable(con, "mtcars")

# A zero row data frame just creates a table definition.
dbWriteTable(con, "mtcars2", mtcars[0, ])
dbReadTable(con, "mtcars2")

dbDisconnect(con)

Description

Several extension functions are included in the RSQLite package. When enabled via initExtension(), these extension functions can be used in SQL queries. Extensions must be enabled separately for each connection.

Usage

initExtension(
  db,
  extension = c("math", "regexp", "series", "csv", "uuid", "http")
)

Arguments

Details

The "math" extension functions are written by Liam Healy and made available through the SQLite website (https://www.sqlite.org/src/ext/contrib). This package contains a slightly modified version of the original code. See the section "Available functions in the math extension" for details.

The "regexp" extension provides a regular-expression matcher for POSIX extended regular expressions, as available through the SQLite source code repository (https://sqlite.org/src/file?filename=ext/misc/regexp.c). SQLite will then implement the ⁠A regexp B⁠ operator, where A is the string to be matched and B is the regular expression.

The "series" extension loads the table-valued function generate_series(), as available through the SQLite source code repository (https://sqlite.org/src/file?filename=ext/misc/series.c).

The "csv" extension loads the function csv() that can be used to create virtual tables, as available through the SQLite source code repository (https://sqlite.org/src/file?filename=ext/misc/csv.c).

The "http" extension registers an HTTP/HTTPS virtual file system (VFS) that allows opening remote databases via URI filenames, e.g., "file:https://host/path/db.sqlite?vfs=http&immutable=1". This implementation is experimental and not an official SQLite extension; it fetches pages on demand using HTTP Range requests and serves reads from an in-memory page cache, with an optional full-download fallback depending on server support and configuration. It is primarily intended for read-only access to small, immutable databases; see sqliteHttpConfig() and sqliteRemote() for configuration options and usage examples. Building this extension may require libcurl and is optional in RSQLite.

The "uuid" extension loads the functions uuid(), uuid_str(X) and uuid_blob(X) that can be used to create universally unique identifiers, as available through the SQLite source code repository (https://sqlite.org/src/file?filename=ext/misc/uuid.c).

Available functions in the math extension

Math functions

acos, acosh, asin, asinh, atan, atan2, atanh, atn2, ceil, cos, cosh, cot, coth, degrees, difference, exp, floor, log, log10, pi, power, radians, sign, sin, sinh, sqrt, square, tan, tanh

String functions

charindex, leftstr, ltrim, padc, padl, padr, proper, replace, replicate, reverse, rightstr, rtrim, strfilter, trim

Aggregate functions

stdev, variance, mode, median, lower_quartile, upper_quartile

Examples

library(DBI)
db <- RSQLite::datasetsDb()

# math
RSQLite::initExtension(db)
dbGetQuery(db, "SELECT stdev(mpg) FROM mtcars")
sd(mtcars$mpg)

# regexp
RSQLite::initExtension(db, "regexp")
dbGetQuery(db, "SELECT * FROM mtcars WHERE carb REGEXP '[12]'")

# series
RSQLite::initExtension(db, "series")
dbGetQuery(db, "SELECT value FROM generate_series(0, 20, 5);")

dbDisconnect(db)

# csv
db <- dbConnect(RSQLite::SQLite())
RSQLite::initExtension(db, "csv")
# use the filename argument to mount CSV files from disk
sql <- paste0(
  "CREATE VIRTUAL TABLE tbl USING ",
  "csv(data='1,2', schema='CREATE TABLE x(a INT, b INT)')"
)
dbExecute(db, sql)
dbGetQuery(db, "SELECT * FROM tbl")

# uuid
db <- dbConnect(RSQLite::SQLite())
RSQLite::initExtension(db, "uuid")
dbGetQuery(db, "SELECT uuid();")
dbDisconnect(db)

Description

Return the version of RSQLite.

Usage

rsqliteVersion()

Value

A character vector containing header and library versions of RSQLite.

Examples

RSQLite::rsqliteVersion()

Description

Together, SQLite() and dbConnect() allow you to connect to a SQLite database file. See DBI::dbSendQuery() for how to issue queries and receive results.

Usage

SQLite(...)

## S4 method for signature 'SQLiteConnection'
dbConnect(drv, ...)

## S4 method for signature 'SQLiteDriver'
dbConnect(
  drv,
  dbname = "",
  ...,
  loadable.extensions = TRUE,
  default.extensions = loadable.extensions,
  cache_size = NULL,
  synchronous = "off",
  flags = SQLITE_RWC,
  vfs = NULL,
  bigint = c("integer64", "integer", "numeric", "character"),
  extended_types = FALSE
)

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

Arguments

Details

Connections are automatically cleaned-up after they're deleted and reclaimed by the GC. You can use DBI::dbDisconnect() to terminate the connection early, but it will not actually close until all open result sets have been closed (and you'll get a warning message to this effect).

Value

SQLite() returns an object of class SQLiteDriver.

dbConnect() returns an object of class SQLiteConnection.

Extended Types

When parameter extended_types = TRUE date and time columns are directly mapped to corresponding R-types. How exactly depends on whether the actual value is a number or a string:

If a value cannot be mapped an NA is returned in its place with a warning.

See Also

The corresponding generic functions DBI::dbConnect() and DBI::dbDisconnect().

Examples

library(DBI)
# Initialize a temporary in memory database and copy a data.frame into it
con <- dbConnect(RSQLite::SQLite(), ":memory:")
data(USArrests)
dbWriteTable(con, "USArrests", USArrests)
dbListTables(con)

# Fetch all query results into a data frame:
dbGetQuery(con, "SELECT * FROM USArrests")

# Or do it in batches
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)

# clean up
dbDisconnect(con)

Description

Copies a database connection to a file or to another database connection. It can be used to save an in-memory database (created using dbname = ":memory:" or dbname = "file::memory:") to a file or to create an in-memory database a copy of another database.

Usage

sqliteCopyDatabase(from, to)

Arguments

Author(s)

Seth Falcon

References

https://www.sqlite.org/backup.html

Examples

library(DBI)
# Copy the built in databaseDb() to an in-memory database
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbListTables(con)

db <- RSQLite::datasetsDb()
RSQLite::sqliteCopyDatabase(db, con)
dbDisconnect(db)
dbListTables(con)

dbDisconnect(con)

Description

Capability query for optional HTTP VFS support.

Usage

sqliteHasHttpVFS()

Description

lifecycle::badge("experimental")

Set conservative, process-wide options for the experimental HTTP/HTTPS VFS. These values are read on first open of a remote database in the current process.

Usage

sqliteHttpConfig(
  cache_size_mb = NULL,
  prefetch_pages = NULL,
  fallback_full_download = NULL
)

Arguments

Details

Options are stored in environment variables so they also affect C-level code:

• RSQLITE_HTTP_CACHE_MB: In-memory page cache size in megabytes (default 4).

• RSQLITE_HTTP_PREFETCH_PAGES: Prefetch this many pages ahead (default 0).

• RSQLITE_HTTP_FALLBACK_FULLDL: If TRUE (default), fall back to full download when the server does not support HTTP Range; if FALSE, open will fail.

Value

A named list of previous values (in R types).

Examples

old <- sqliteHttpConfig(cache_size_mb = 8, prefetch_pages = 1, fallback_full_download = TRUE)
on.exit(do.call(sqliteHttpConfig, old), add = TRUE)
# ... connect with sqliteRemote() ...

Description

Returns counters collected for HTTP VFS operations in the current process. These values are best-effort and may currently report zeros until global aggregation is implemented.

Usage

sqliteHttpStats()

Details

Statistics are maintained per R process and reset when the process terminates. Values:

• bytes_fetched: Total bytes transferred via HTTP GET/Range requests.

• range_requests: Count of HTTP Range requests performed.

• full_download: Logical flag; TRUE if a fallback full download occurred for any open in this process.

If the HTTP VFS was not compiled in, all zeros (and FALSE) are returned.

Value

A list with elements bytes_fetched, range_requests, full_download.

See Also

sqliteHttpConfig(), sqliteRemote(), sqliteHasHttpVFS()

Examples

if (sqliteHasHttpVFS()) {
  # Hypothetical remote DB (replace with a real URL for actual use):
  # old <- sqliteHttpConfig(cache_size_mb = 8, prefetch_pages = 1)
  # on.exit(do.call(sqliteHttpConfig, old), add = TRUE)
  # con <- sqliteRemote("https://example.org/db.sqlite")
  # dbGetQuery(con, "SELECT name FROM sqlite_master WHERE type='table'")
  stats <- sqliteHttpStats()
  str(stats)
}

Description

Returns TRUE if the experimental HTTP/HTTPS virtual file system was compiled in (libcurl detected at build time), FALSE otherwise. This is a compile-time indicator; even if TRUE the VFS might still fail to register at runtime on unusual platforms, in which case sqliteHasHttpVFS() is the definitive runtime capability probe.

Usage

sqliteHttpVfsCompiled()

Details

sqliteHasHttpVFS() performs a lazy registration attempt when the VFS is compiled in but not yet registered. If the HTTP VFS was not compiled in, calls to sqliteRemote() will error.

Value

A logical scalar.

Examples

sqliteHttpVfsCompiled()

Description

lifecycle::badge("experimental")

Constructs a URI filename and sets the appropriate VFS and immutable flags. Requires the optional http extension to be available; see sqliteHasHttpVFS().

Usage

sqliteRemote(url, immutable = TRUE, ...)

Arguments

Value

A SQLiteConnection object.

Examples

if (sqliteHasHttpVFS()) {
  con <- sqliteRemote("https://example.org/db.sqlite")
  dbGetQuery(con, "SELECT name FROM sqlite_master WHERE type='table'")
  dbDisconnect(con)
}

Description

When a transaction cannot lock the database, because it is already locked by another one, SQLite by default throws an error: ⁠database is locked⁠. This behavior is usually not appropriate when concurrent access is needed, typically when multiple processes write to the same database.

sqliteSetBusyHandler() lets you set a timeout or a handler for these events. When setting a timeout, SQLite will try the transaction multiple times within this timeout. To set a timeout, pass an integer scalar to sqliteSetBusyHandler().

Another way to set a timeout is to use a PRAGMA, e.g. the SQL query

PRAGMA busy_timeout=3000

sets the busy timeout to three seconds.

Usage

sqliteSetBusyHandler(dbObj, handler)

Arguments

Details

Note that SQLite currently does not schedule concurrent transactions fairly. If multiple transactions are waiting on the same database, any one of them can be granted access next. Moreover, SQLite does not currently ensure that access is granted as soon as the database is available. Make sure that you set the busy timeout to a high enough value for applications with high concurrency and many writes.

If the handler argument is a function, then it is used as a callback function. When the database is locked, this will be called with a single integer, which is the number of calls for same locking event. The callback function must return an integer scalar. If it returns 0L, then no additional attempts are made to access the database, and an error is thrown. Otherwise another attempt is made to access the database and the cycle repeats.

Handler callbacks are useful for debugging concurrent behavior, or to implement a more sophisticated busy algorithm. The latter is currently considered experimental in RSQLite. If the callback function fails, then RSQLite will print a warning, and the transaction is aborted with a "database is locked" error.

Note that every database connection has its own busy timeout or handler function.

Calling sqliteSetBusyHandler() on a connection that is not connected is an error.

Value

Invisible NULL.

See Also

https://www.sqlite.org/c3ref/busy_handler.html