R/dbConnect_SQLiteDriver.R

In rstats-db/RSQLite: SQLite Interface for R

# read/write + create + url
#' @param drv,conn An objected generated by [SQLite()], or an existing
#'   \code{\linkS4class{SQLiteConnection}}. If an connection, the connection
#'   will be cloned.
#' @param dbname The path to the database file. SQLite keeps each database
#'   instance in one single file. The name of the database \emph{is} the file
#'   name, thus database names should be legal file names in the running
#'   platform. There are two exceptions:
#'
#'   \itemize{
#'   \item `""` will create a temporary on-disk database. The file
#'     will be deleted when the connection is closed.
#'   \item `":memory:"` or `"file::memory:"` will create a temporary
#'     in-memory database.
#'   }
#' @param cache_size Advanced option. A positive integer to change the maximum
#'   number of disk pages that SQLite holds in memory (SQLite's default is
#'   2000 pages). See \url{https://www.sqlite.org/pragma.html#pragma_cache_size}
#'   for details.
#' @param synchronous Advanced options. Possible values for `synchronous`
#'   are "off" (the default), "normal", or  "full".  Users have reported
#'   significant speed ups using `sychronous = "off"`, and the SQLite
#'   documentation itself implies considerable improved performance at the very
#'   modest risk of database corruption in the unlikely case of the operating
#'   system (\emph{not} the R application) crashing. See
#'   \url{https://www.sqlite.org/pragma.html#pragma_synchronous} for details.
#' @param flags `SQLITE_RWC`: open the database in read/write mode
#'   and create the database file if it does not already exist;
#'   `SQLITE_RW`: open the database in read/write mode. Raise an error
#'   if the file does not already exist; `SQLITE_RO`: open the database in
#'   read only mode.  Raise an error if the file does not already exist
#' @param loadable.extensions When `TRUE` (default) SQLite3
#'   loadable extensions are enabled. Setting this value to `FALSE`
#'   prevents extensions from being loaded.
#' @param default.extensions When `TRUE` (default) the [initExtension()]
#'   function will be called on the new connection.Setting this value to `FALSE`
#'   requires calling `initExtension()` manually.
#' @param vfs Select the SQLite3 OS interface. See
#'   https://www.sqlite.org/vfs.html for details. Allowed values are
#'   `"unix-posix"`, `"unix-unix-afp"`,
#'   `"unix-unix-flock"`, `"unix-dotfile"`, and
#'   `"unix-none"`.
#' @param bigint The R type that 64-bit integer types should be mapped to,
#'   default is [bit64::integer64], which allows the full range of 64 bit
#'   integers.
#' @param extended_types When `TRUE` columns of type `DATE`, `DATETIME` /
#' `TIMESTAMP`, and `TIME` are mapped to corresponding R-classes, c.f. below
#' for details. Defaults to `FALSE`.
#'
#' @return `dbConnect()` returns an object of class [SQLiteConnection-class].
#'
#' @section 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:
#'
#' | *Column type* | *Value is numeric* | *Value is Text* | *R-class* |
#' | ------------- | ------------------ | --------------- | --------- |
#' | DATE | Count of days since 1970-01-01 | YMD formatted string (e.g. 2020-01-23) | `Date` |
#' | TIME | Count of (fractional) seconds | HMS formatted string (e.g. 12:34:56) | `hms` (and `difftime`) |
#' | DATETIME / TIMESTAMP | Count of (fractional) seconds since midnight 1970-01-01 UTC | DATE and TIME as above separated by a space | `POSIXct` with time zone UTC |
#'
#' If a value cannot be mapped an `NA` is returned in its place with a warning.
#'
#' @aliases SQLITE_RWC SQLITE_RW SQLITE_RO
#' @rdname SQLite
#' @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)
#' @usage NULL
dbConnect_SQLiteDriver <- function(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) {
  stopifnot(length(dbname) == 1, !is.na(dbname))

  if (!is_url_or_special_filename(dbname)) {
    dbname <- normalizePath(dbname, mustWork = FALSE)
  }

  dbname <- enc2utf8(dbname)

  vfs <- check_vfs(vfs)
  stopifnot(is.integer(flags), length(flags) == 1)

  bigint <- match.arg(bigint)

  extended_types <- isTRUE(extended_types)
  if (extended_types) {
    check_suggested("hms", "dbConnect")
  }
  conn <- new("SQLiteConnection",
    ptr = connection_connect(dbname, loadable.extensions, flags, vfs, extended_types),
    dbname = dbname,
    flags = flags,
    vfs = vfs,
    loadable.extensions = loadable.extensions,
    ref = new.env(parent = emptyenv()),
    bigint = bigint,
    extended_types = extended_types
  )

  ## experimental PRAGMAs
  if (!is.null(cache_size)) {
    cache_size <- as.integer(cache_size)
    tryCatch(
      dbExecute(conn, sprintf("PRAGMA cache_size=%d", cache_size)),
      error = function(e) {
        warning("Couldn't set cache size: ", conditionMessage(e), "\n",
          "Use `cache_size` = NULL to turn off this warning.",
          call. = FALSE
        )
      }
    )
  }

  if (!is.null(synchronous)) {
    synchronous <- match.arg(synchronous, c("off", "normal", "full"))
    tryCatch(
      dbExecute(conn, sprintf("PRAGMA synchronous=%s", synchronous)),
      error = function(e) {
        warning("Couldn't set synchronous mode: ", conditionMessage(e), "\n",
          "Use `synchronous` = NULL to turn off this warning.",
          call. = FALSE
        )
      }
    )
  }

  if (default.extensions) {
    initExtension(conn)
  }

  reg.finalizer(
    conn@ptr,
    function(x) {
      if (dbIsValid(conn)) {
        warning_once("call dbDisconnect() when finished working with a connection")
      }
    }
  )

  conn
}
#' @rdname SQLite
#' @export
setMethod("dbConnect", "SQLiteDriver", dbConnect_SQLiteDriver)