R/update_status_db.R

In vjcitn/BiocBBSpack: support for build system

#' add a dataframe, optionally purging related obsolete records, to a SQLite database
#' @import RSQLite
#' @param sqlite_filename character(1) filename of SQLite database; database must exist
#' @param tablename character(1) name of table to operate on
#' @param dropfield character(1) or NULL, the field used to select records to delete
#' @param dropvalue character(1) only used if `dropfield` is not NULL, value used to
#' select records for deletion
#' @param newdf data.frame conforming to the table in `tablename` if such exists
#' @note Connects to db with flag SQLITE_RW and disconnects on exit.
#' @export 
update_status_db = function (sqlite_filename, tablename, dropfield = NULL, dropvalue, 
    newdf) 
{
    wcon = RSQLite::dbConnect(SQLite(), sqlite_filename)
    on.exit(dbDisconnect(wcon))
    allt = dbListTables(wcon)
    if (length(allt) > 0) {
        if (tablename %in% allt) {
            chk = dbGetQuery(wcon, sprintf("SELECT * from %s limit 1", 
                tablename))
            nn = names(newdf)
            stopifnot(all(sort(nn) == sort(names(chk))))
        }
        if (!is.null(dropfield)) {
            newq = sprintf("DELETE from %s where %s = '%s'", 
                tablename, dropfield, dropvalue)
            dbExecute(wcon, newq)
        }
    }
    dbWriteTable(wcon, tablename, newdf, append = TRUE)
}

update_status_db_old = function(sqlite_filename, tablename, dropfield=NULL, dropvalue, newdf ) {
 stopifnot(file.exists(sqlite_filename))
 wcon = dbConnect(SQLite(), sqlite_filename, flags=RSQLite::SQLITE_RW)
 on.exit(dbDisconnect(wcon))
 allt = dbListTables(wcon)
 if (tablename %in% allt) {   # check if altering existing table
    chk = dbGetQuery(wcon, sprintf("SELECT * from %s limit 1", tablename))
    nn = names(newdf)
    stopifnot(all(sort(nn) == sort(names(chk))))
    }
 if (!is.null(dropfield)) {
   newq = sprintf("DELETE from %s where %s = '%s'", tablename, dropfield, dropvalue)
   dbExecute(wcon, newq)
   }
 dbWriteTable(wcon, tablename, newdf, append=TRUE)
}