R/gpkg-sqlite.R

In gpkg: Utilities for the Open Geospatial Consortium 'GeoPackage' Format

# gpkg sqlite tools

#' Update a Table by Name
#'
#' For a given table, set column `updatecol` to scalar `updatevalue` where
#' column `wherecol` is in vector `wherevector`.
#'
#' @param x A _geopackage_ object, path to a GeoPackage or an
#'   _SQLiteConnection_.
#' @param table_name _character_. Table name.
#' @param updatecol _character_. Column to update.
#' @param updatevalue _character_, _numeric_, etc.; A scalar value to set. `NA`
#'   and `NaN` are converted to SQL NULL.
#' @param wherecol _character_. Column used to constrain update.
#' @param wherevector _character_, _numeric_, etc.; Vector of values where
#'   update should be made.
#' @param query_string _logical_. Return SQLite query rather than executing it?
#'   Default: `FALSE`
#' @return _integer_. Number of rows updated by executing `UPDATE` query. Or
#'   `character` SQL query string if `query_string=TRUE`.
#' @export
gpkg_update_table <- function(x,
                              table_name,
                              updatecol,
                              updatevalue,
                              wherecol = NULL,
                              wherevector = NULL,
                              query_string = FALSE) {
  if (length(updatevalue) != 1) {
    stop("updatevalue must be a scalar", call. = FALSE)
  }
  
  if (is.na(updatevalue)) {
    val <- "NULL"
  } else if (is.character(updatevalue)) {
    val <- paste0("'", updatevalue, "'")
  } else {
    val <- updatevalue
  }

  q <- sprintf(
    "UPDATE %s SET %s = %s %s",
    table_name,
    updatecol,
    val,
    ifelse(
      !is.null(wherecol),
      sprintf("WHERE %s IN %s", wherecol, paste0("(", paste0(
        paste0("'", wherevector, "'"), collapse = ","
      ), ")")),
      ""
    )
  )
  
  if (query_string) {
    return(q)
  }
  
  con <- .gpkg_connection_from_x(x)
  
  res <- RSQLite::dbExecute(con, q)
  
  if (attr(con, 'disconnect')) {
    gpkg_disconnect(con)
  }
  
  res
}