inst/prev_pkg_isdb/R/class_dbconnect.R

In bfatemi/isdata: DOCUMENTATION NEEDED

#' Make connection to ISI DB
#'
#' Class that represents a connection object specific to an ISI configured database.
#'
#' @import RODBC
#' @import R6
#' @import data.table
#'
#' @importFrom stringr str_c
#'
#' @name isi_connect
isi_connect <- R6::R6Class(
  classname = "isi_connect",


  # PUBLIC FIELDS AND METHODS -----------------------------------------------

  public = list(
    Driver                 = NULL,
    Server                 = NULL,
    Database               = NULL,
    Uid                    = NULL,
    Pwd                    = NULL,
    Encrypt                = NULL,
    TrustServerCertificate = NULL,
    `Connection Timeout`   = NULL,
    Authentication         = NULL,
    trusted_connection     = NULL,

    ###
    ### INIT: Ensure network checks out and set Driver
    ###
    initialize = function(DB = NULL, err = TRUE){
      check <- system("ping corp.intusurg.com -n 1 -w 1000")
      if(check > 0)
        stop("ISI network not detected")
      print_stamp("Network Verified", "*")
      self$Driver <- "{ODBC Driver 13 for SQL Server}"

      if(private$check_db(DB, err)){
        private$DB <- DB
        print_stamp("Database Name Verified", "*")
      }
    },

    ###
    ### OPEN: set parameters for configured connection and open
    ###
    open = function(err = TRUE){
      if(private$status == "active"){
        if(err){
          stop("Connection already active", call. = FALSE)
        }else{
          warning("Connection already active", call. = FALSE)
          return(FALSE)
        }
      }

      private$status <- "active"

      if(private$DB == "EDW"){
        self$Server                 <- "tcp:isrgdedw01.database.windows.net,1433"
        self$Database               <- "edwdsqldw01"
        self$Uid                    <- "{bobby.fatemi@intusurg.com}"
        self$Pwd                    <- "{Newyork6}"
        self$Encrypt                <- "yes"
        self$TrustServerCertificate <- "no"
        self$`Connection Timeout`   <- "30"
        self$Authentication         <- "ActiveDirectoryPassword"
        self$trusted_connection     <- NULL
      }else if(private$DB == "QTI"){
        self$Server                 <- "AZCWDA0008"
        self$trusted_connection     <- "yes"
        self$Database               <- private$dbnames[, get(private$DB)]
      }else{
        self$Server                 <- "172.16.5.34,1515"
        self$trusted_connection     <- "yes"
        self$Database               <- private$dbnames[, get(private$DB)]
      }
      private$conn_env$conn <- RODBC::odbcDriverConnect(self$connString())

      assign(private$DB, private$conn_env$conn, envir = isdb_env)
      return(TRUE)
    },

    ###
    ### CLOSE: close current connection
    ###
    close = function(){
      cn <- private$conn_env$conn
      private$status <- "inactive"

      tryCatch(RODBC::odbcClose(cn),
               error = function(c){
                 stop("Connection already closed", call. = FALSE)
               })
      private$conn_env$conn <- NULL
      return(TRUE)
    },

    ###
    ### Get Connection
    ###
    getConn = function(){
      if(!private$check_status()) return(NULL)

      cn <- private$conn_env$conn
      tryCatch(RODBC::odbcGetInfo(cn),
               error = function(c){
                 private$conn_env$conn <- NULL
                 private$status <- "inactive"
                 stop("Connection closed. Open again", call. = FALSE)
               })
      return(cn)
    },

    ###
    ### CHECK AVAILABLE DB
    ###
    availabledb = function(){
      return(private$dbnames)
    },

    ###
    ### GET CONFIGURED CONNECTION PARAMS AS LIST
    ###
    connList = function(){
      if(!private$check_status()) return(NULL)

      args <- names(isi_connect$public_fields)
      tmp  <- sapply(args, function(i) do.call(`$`, list(self, i)))
      cnll <- tmp[!sapply(tmp, is.null)]
      return(cnll)
    },

    ###
    ### GET CONNECTION PARAMS AS STRING FOR RODBC
    ###
    connString = function(){
      if(!private$check_status()) return(NULL)

      tmp <- self$connList()
      return(stringr::str_c(names(tmp), "=", tmp, collapse = ";"))
    },

    connStatus = function() private$check_status()
  ),


  # PRIVATE FIELDS AND METHODS ----------------------------------------------
  private = list(
    status = "inactive",
    DB = NULL,
    dbnames = data.table(RFE = "RemoteFE",
                         MORPH = "Morpheus20",
                         MFG = "ManufacturingMetrics",
                         SAP = "SAP",
                         EDW = "edwdsqldw01",
                         QTI = "CUSTOM_QTI"),

    conn_env = new.env(),

    check_db = function(DB=NULL, err = TRUE){
      ckdb <- names(private$dbnames)

      if(is.null(DB)){
        if(err)
          stop("No DB argument provided...The following are available: ", paste0(ckdb, collapse = ", "), call. = FALSE)
        return(FALSE)
      }

      if(!DB %in% ckdb){
        if(err)
          stop("Unkown DB provided...The following are available: ", paste0(ckdb, collapse = ", "), call. = FALSE)
        return(FALSE)
      }
      return(TRUE)
    },

    check_status = function(){
      if(private$status == "inactive"){
        message("No connection configured")
        return(FALSE)
      }else{
        return(TRUE)
      }
    }
  )
)