R/access_dbconnection.R

In OB7-IRD/furdeb: FUnctions for R DEvelopment and Beyond!

#' @name access_dbconnection
#' @title Connection with Access database
#' @description Generate a common connection with an Access database using a JDBC driver.
#' @param driver_name {\link[base]{character}} expected. By default "u_can_access" (free driver). Access driver name. You can also choose the driver "access_jdbc42" (paid driver).
#' @param access_database_file_path {\link[base]{character}} expected. file path of the Access database (.mdb or .accdb expected).
#' @param access_jdbc42_driver_file_path {\link[base]{character}} expected. By default NULL. File path of the access_jdbc42 driver (.jar file expected). Mandatory if "access_jdbc42" is selected in the argument "driver_name".
#' @return The function return a R object with Access database identification of connection.
#' @details
#' Difference between drivers "u_can_access" and "access_jdbc42":
#' \itemize{
#'  \item{"u_can_access": }{the main advantage is it's a free java JDBC driver. To understand briefly the process behind, the Access database is converted in HSQLDB system. This conversion could take a long time, especially if the database if large. Furthermore, after the conversion, queries should be faster than if there run on an Access database.}
#'  \item{"access_jdbc42": }{this driver is paying. In opposition with the "u_can_access" driver, the Access database is not converted. The connection should be faster but the queries could be longer.}
#' }
#' @importFrom RJDBC JDBC dbConnect
#' @importFrom codama r_type_checking file_path_checking
#' @export
access_dbconnection <- function(driver_name = "u_can_access",
                                access_database_file_path,
                                access_jdbc42_driver_file_path = NULL) {
  # 1 - Arguments verification ----
  # driver_name argument
  if (codama::r_type_checking(r_object = driver_name,
                              type = "character",
                              length = 1L,
                              allowed_value = c("u_can_access",
                                                "access_jdbc42"),
                              output = "logical") != TRUE) {
    return(codama::r_type_checking(r_object = driver_name,
                                   type = "character",
                                   length = 1L,
                                   allowed_value = c("u_can_access",
                                                     "access_jdbc42"),
                                   output = "message"))
  }
  # access_database_file_path argument
  if (codama::r_type_checking(r_object = access_database_file_path,
                              type = "character",
                              length = 1L,
                              output = "logical") != TRUE) {
    return(codama::r_type_checking(r_object = access_database_file_path,
                                   type = "character",
                                   length = 1L,
                                   output = "message"))
  }
  if (codama::file_path_checking(file_path = access_database_file_path,
                                 extension = c("mdb",
                                               "accdb"),
                                 output = "logical") != TRUE) {
    return(codama::file_path_checking(file_path = access_database_file_path,
                                      extension = c("mdb",
                                                    "accdb"),
                                      output = "message"))
  }
  # access_jdbc42_driver_file_path
  if (driver_name == "access_jdbc42") {
    if (codama::r_type_checking(r_object = access_jdbc42_driver_file_path,
                                type = "character",
                                length = 1L,
                                output = "logical") != TRUE) {
      return(codama::r_type_checking(r_object = access_jdbc42_driver_file_path,
                                     type = "character",
                                     length = 1L,
                                     output = "message"))
    }
    if (codama::file_path_checking(file_path = access_jdbc42_driver_file_path,
                                   extension = "jar",
                                   output = "logical") != TRUE) {
      return(codama::file_path_checking(file_path = access_jdbc42_driver_file_path,
                                        extension = "jar",
                                        output = "message"))
    }
  }
  # 2 - Global process ----
  if (driver_name == "access_jdbc42") {
    # initializing access_jdbc42 JDBC driver
    access_jdbc_driver <- RJDBC::JDBC(driverClass = "com.hxtt.sql.access.AccessDriver",
                                      classPath = access_jdbc42_driver_file_path)
    # connection to access database
    access_jdbc_connection <- RJDBC::dbConnect(access_jdbc_driver,
                                               paste0("jdbc:access:/",
                                                      access_database_file_path))
  } else {
    # function for  driver
    # initializing "u_can_access" JDBC driver
    class_path <- c(system.file("u_can_access",
                                "jackcess-3.0.1.jar",
                                package = "furdeb"),
                    system.file("u_can_access",
                                "hsqldb-2.5.0.jar",
                                package = "furdeb"),
                    system.file("u_can_access",
                                "commons-logging-1.2.jar",
                                package = "furdeb"),
                    system.file("u_can_access",
                                "commons-lang3-3.8.1.jar",
                                package = "furdeb"),
                    system.file("u_can_access",
                                "ucanaccess-5.0.1.jar",
                                package = "furdeb"))
    access_jdbc_driver <- RJDBC::JDBC(driverClass = "net.ucanaccess.jdbc.UcanaccessDriver",
                                      classPath = class_path)
    # connection to access database
    access_jdbc_connection <- RJDBC::dbConnect(access_jdbc_driver,
                                               paste0("jdbc:ucanaccess://",
                                                      access_database_file_path))
  }
  return(list("database_name" <- "access_database",
              "connection_information" <- access_jdbc_connection))
}