R/db.R

In toygur/R2Tooltip: RiskActive Toolkit Functions

##########################################################################################################
#' DB connection list check
#'
#' @export
R2.dbConnectionList.check <- function(dbConnectionList){
  if(length(dbConnectionList)==0){ res <- FALSE }
  else { res <- TRUE}
  return(res)
}

##########################################################################################################
#' DB connection
#'
#' @export
R2.dbConnect <- function(connection){
  conn <- odbcConnect(dsn=connection$dsn, uid=connection$uid, pwd=connection$pwd)
  return(conn)
}

##########################################################################################################
#' DB send query
#'
#' @export
R2.dbSendQuery <- function(conn,sql) {

  conn.result <- try(R2.dbConnectionInfo(conn))

  if(!inherits(conn.result, "try-error")){
    res <-  suppressWarnings(sqlQuery(conn, sql,stringsAsFactors = FALSE))
    return(res)
  } else {
    stop("no current connection")
  }
}

##########################################################################################################
#' DB close
#'
#' @export
R2.dbClose <- function(conn,dbConnectionList){

  conn.result <- try(R2.dbConnectionInfo(conn))

  if(length(dbConnectionList)!=0 & !inherits(conn.result, "try-error")){
    for(i in 1:length(dbConnectionList)){
      R2.dbSendQuery(conn, paste0("KILL ",dbConnectionList[[i]]))
      dbConnectionList <- list()
      return(dbConnectionList)
    }
  }
}

##########################################################################################################
#' DB connection info
#'
#' @export
R2.dbConnectionInfo <- function(conn){
  res <- try(sqlQuery(conn, "select connection_id()"))
  if(is.data.frame(res)){
    res <- res$connection_id
    return(res)
  } else { stop("no current connection") }
}

##########################################################################################################
#' DB connection
#'
#' @export
R2.dbConnection <- function(dbConnectionList,connection,attempt=0){

  #Bağlantı başarılı olmazsa maksimum deneme sayısı kadar bağlantı denenecektir.
  maxattempt <- 5

  #dbConnectionList tablosunda kayıt (bağlantı) olup-olmadığı kontrol ediliyor.
  checkList <- R2.dbConnectionList.check(dbConnectionList)

  if(checkList == FALSE) {
    #Eğer checklist boşsa, yani herhangi bir bağlantı yoksa,
    conn <- try(suppressWarnings(R2.dbConnect(connection)))
    if (conn==-1) {
      #Eğer bağlantıda hata olduysa
      attempt <- attempt + 1
      if(attempt==maxattempt) { stop("too many connection attempts") } else { try(suppressWarnings(R2.dbConnection(dbConnectionList,connection,attempt))) }

    } else {
      #Eğer bağlantıda hata yoksa dbConnectionList'e yeni ID yazılır. Return olarak conn ve güncel dbConnectionList gönderilir.
      dbConnectionList[[length(dbConnectionList)+1]] <- R2.dbConnectionInfo(conn)
      res <- list("conn"=conn, "dbConnectionList"=dbConnectionList)
      return(res)

    }

  } else {
    #Eğer checklist dolu ise, yani geçerli en az bir bağlantı varsa, ek yeni bir bağlantı açılıp tüm bağlantılar temizlenecek ve dbConnectionList boşaltılacaktır.
    conn <- try(R2.dbConnect(connection))
    dbConnectionList[[length(dbConnectionList)+1]] <- suppressWarnings(R2.dbConnectionInfo(conn))

    for(i in 1:length(dbConnectionList)){
      try(R2.dbSendQuery(conn, paste0("KILL ",dbConnectionList[[i]])))
    }
    dbConnectionList <- list()

    try(R2.dbConnection(dbConnectionList,connection,attempt=0))

  }

}

##########################################################################################################
#' DB query
#'
#' @export
R2.dbQuery <- function(sql,dbConnectionList,connection){

  conn.obj <- try(suppressWarnings(R2.dbConnection(dbConnectionList,connection)))

  if(!inherits(conn.obj, "try-error")) {
    #Bağlantıda yapıldıysa
    conn <- conn.obj$conn
    dbConnectionList <- conn.obj$dbConnectionList

    sql.res <- try(suppressWarnings(R2.dbSendQuery(conn,sql)))

    dbConnectionList <- try(suppressWarnings(R2.dbClose(conn,dbConnectionList)))

    res <- list("res"=sql.res, "dbConnectionList"=dbConnectionList)
    return(res)

  } else {
    #Bağlantı maksimum deneme sayısında da bağlantı kuramıyorsa sonlandırılır
    stop("too many connection attempts")
  }
}

##########################################################################################################
#' DB save
#'
#' @export
R2.dbSave <- function(df,tablename,dbConnectionList,connection){

  conn.obj <- try(R2.dbConnection(dbConnectionList,connection))

  if(!inherits(conn.obj, "try-error")) {
    #Bağlantıda yapıldıysa
    conn <- conn.obj$conn
    dbConnectionList <- conn.obj$dbConnectionList

    sql.res <- try(sqlSave(conn, df, tablename = tablename, rownames=FALSE, append = TRUE))

    dbConnectionList <- try(R2.dbClose(conn,dbConnectionList))

    res <- list("res"=sql.res, "dbConnectionList"=dbConnectionList)
    return(res)

  } else {
    #Bağlantı maksimum deneme sayısında da bağlantı kuramıyorsa sonlandırılır
    stop("too many connection attempts")
  }
}