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"))

Arguments

Details

The "math" extension functions are written by Liam Healy and made available through the SQLite website (https://www.sqlite.org/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 "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

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