R/sqlite.R

In D4TAlink.light: GDP - Workflow Management

## =======================================================================
#' Save R data frame to SQLite file.
#' This function saves a data frame to an SQLite database file. It can create
#' indices on specified columns for faster querying. The database file is stored
#' in the task's binary directory.
#' @param object data.frame to serialize.
#' @param index character vector of column names to create indices on.
#' @param tableName name of the table in the SQLite database.
#' @param overwrite if \code{TRUE}, overwrite existing table.
#' @param append if \code{TRUE}, append to existing table.
#' @param row.names if \code{TRUE}, save row names as a column.
#' @inheritParams D4TAlink-common-args
#' @returns The file name invisibly.
#' @importFrom DBI dbConnect dbDisconnect dbWriteTable dbExecute
#' @importFrom RSQLite SQLite
#' @examples
#' \dontrun{
#' task <- loadTask("myproject","mypackage","mytask")
#' # create a sample data frame
#' df <- data.frame(a = 1:5, b = letters[1:5])
#' # save it to SQLite
#' fn <- saveSQLite(df, task, "testdb",tableName="table1",
#'                  index = c("a"))
#' # read it back
#' df_loaded <- readSQLite(task,"testdb",tableName="table1")
#' # print the loaded data frame
#' print(df_loaded)
#' # add second data frame
#' df2 <- data.frame(a = 6:10, b = letters[6:10])
#' saveSQLite(df2, task,"testdb",tableName="table2")
#' # query the data
#' query_result <- querySQLite("SELECT * FROM table2 WHERE a > 8",
#'                             task,"testdb")
#' print(query_result)                           
#' #' # append to the first table
#' saveSQLite(df2, task,"testdb",tableName="table1",append=TRUE)
#' # read the updated table
#' df_loaded <- readSQLite(task,"testdb",tableName="table1")
#' # print the updated data frame
#' print(df_loaded)
#' # cleanup
#' unlink(fn)
#' } 
#' @export
saveSQLite <- function(object,task,type,subdir=NULL,dirCreate=TRUE,
                       index=NULL,tableName="data",
                       overwrite=FALSE,append=FALSE,row.names=FALSE) {
  fn <- binaryFn(task,type,ext="db",subdir=subdir,dirCreate=dirCreate)
  if(!inherits(object,"data.frame")) 
    stop("Object to save must be a data frame.")
  ## ==========
  conn <- DBI::dbConnect(RSQLite::SQLite(),fn)
  on.exit(DBI::dbDisconnect(conn),add=TRUE)
  ## ==========
  DBI::dbWriteTable(conn,tableName,object,
                    overwrite=overwrite,append=append,
                    row.names=row.names)
  ## ==========
  for (i in index) {
    if (!is.character(i)||length(i)!=1||!i%in%names(object)) {
      stop("Indeces must be a character vector of column names.")
    }
    DBI::dbExecute(conn,
      paste0('CREATE INDEX IF NOT EXISTS "idx_',tableName,'_',i,
             '" ON "',tableName,'"("',i,'");'))
  }
  ## ==========
  invisible(fn)
}
## =======================================================================

## =======================================================================
#' Load data frame from SQLite.
#' This function reads a data frame from an SQLite database file created with \code{saveSQLite}.
#' @param tableName name of the table in the SQLite database.
#' @param n number of rows to read (if NULL, all rows are read).
#' @param offset number of rows to skip before reading (default is 0).
#' @inheritParams D4TAlink-common-args
#' @importFrom DBI dbConnect dbDisconnect dbListTables
#' @importFrom RSQLite SQLite
#' @returns Data frame with requested table.
#' @export
readSQLite <- function(task,type,subdir=NULL,dirCreate=FALSE,
                       tableName="data",n=NULL,offset=0) {
  fn <- binaryFn(task,type,ext="db",subdir=subdir,dirCreate=dirCreate)
  if (!file.exists(fn)) 
    stop(paste0("SQLite file '",fn,"' does not exist."))
  ## ==========
  conn <- DBI::dbConnect(RSQLite::SQLite(),fn)
  on.exit(DBI::dbDisconnect(conn),add=TRUE)
  if(!tableName%in%DBI::dbListTables(conn))
    stop(paste0("Table '",tableName,"' does not exist in the SQLite database."))
  ## ==========
  query <- paste0('SELECT * FROM "',tableName,'"')
  if (!is.null(n)) query <- paste0(query, " LIMIT ", n)
  if (!is.null(offset)&&(offset>0)) query <- paste0(query, " OFFSET ", offset)
  ## ==========
  querySQLite(query,task,type,subdir=subdir,dirCreate=dirCreate,conn=conn)
}
## =======================================================================

## =======================================================================
#' Load data frame by querying an SQLite database.
#' This function executes a SQL query on an SQLite database file created with \code{saveSQLite}.
#' @param query SQL query to execute.
#' @param conn SQLite connection object (optional).
#' @inheritParams D4TAlink-common-args
#' @importFrom DBI dbConnect dbDisconnect dbGetQuery
#' @importFrom RSQLite SQLite
#' @returns Data frame with query results.
#' @export
querySQLite <- function(query,task,type,subdir=NULL,dirCreate=FALSE,
                        conn=NULL) {
  if(is.null(conn)) {
    fn <- binaryFn(task,type,ext="db",subdir=subdir,dirCreate=dirCreate)
    if (!file.exists(fn)) 
      stop(paste0("SQLite file '",fn,"' does not exist."))
    ## ==
    conn <- DBI::dbConnect(RSQLite::SQLite(),fn)
    on.exit(DBI::dbDisconnect(conn),add=TRUE)
  }
  if(!grepl("; *$",query)) query <- paste0(query,";")
  ## ==========
  DBI::dbGetQuery(conn,query)
}
## =======================================================================