R/safeSQLiteConnect.R

In jbrzusto/motusServer: Operate a server to process data for http://motus.org

#' open a 'safe' connection to an sqlite database
#'
#' In this package, all connections to sqlite database files are created
#' by this function.
#'
#' This avoids the locking issue with the typical use of
#' \code{dbConnect(RSQLite::SQLite(), ...)}, which connects and immediately
#' tries to set synchronous mode, unless \code{synchronous=NULL} is specified.
#' If the database is locked, the connection fails \emph{before \code{pragma busy_timeout}}
#' can be used to set a timeout handler.
#'
#' @param path path to database
#'
#' @param create should database be created if it doesn't already exist; default: FALSE
#'
#' @param busyTimeout value for busy_timout handler, in seconds;
#'     default: 300.  A positive value causes the function to wait
#'     for another process to unlock the database, rather than returning
#'     immediately with an error.
#'
#' @param cacheSize size of sqlite page cache to use, in MBytes.
#'    default: 200
#'
#' @return a DBI:dbConnection to the sqlite database, or NULL on failure
#'
#' @note parameters, return value, and semantics are identical to
#' \code{\link{dplyr::src_sqlite}} except that a locked sqlite database
#' will be handled gracefully with retries.
#'
#' @export
#'
#' @author minor changes from dplyr::src_sqlite by John Brzustowski

safeSQLiteConnect = function (path, create = FALSE, busyTimeout=300, cacheSize=200)
{
    con = NULL
    if (create || file.exists(path)) {
        try({
            con = DBI::dbConnect(RSQLite::SQLite(), path, synchronous=NULL)
            DBI::dbExecute(con, sprintf("pragma busy_timeout=%d", busyTimeout * 1000))
            DBI::dbExecute(con, "pragma synchronous=off")
            ## set the page cache size; negative args are in kb, positive in units of page size
            DBI::dbExecute(con, sprintf("pragma cache_size=%f", - cacheSize * 1024))
        }, silent=TRUE)
    }
    return(con)
}