R/sql.r

In rwebsterav/rarsons: r implementation of parson, and other things i find useful

#' Takes a .sql file and assigns it to a variable so it can be used for querying redshift
#' @export

get_SQL <- function(filepath){
  con = file(filepath, "r",encoding = "UTF-8")
  sql.string <- ""

  while (TRUE){
    line <- readLines(con, n = 1)

    if ( length(line) == 0 ){
      break
    }

    line <- gsub("\\t", " ", line)

    if(grepl("--",line) == TRUE){
      line <- paste(sub("--","/*",line),"*/")
    }

    sql.string <- paste(sql.string, line)
  }

  close(con)
  return(sql.string)
}


#' Set's a global variable called c_string which holds all the info you need to open up a database
#' @export

cstring_gen <- function(ODBC_DRIVER
                     ,ODBC_SERVER
                     ,ODBC_DATABASE
                     ,ODCB_ID
                     ,ODBC_PW
                     ,ODCB_PORT){
  c_string = paste("Driver={",ODBC_DRIVER
                   ,"};Driver={",ODBC_DRIVER
                   ,"};\nServer=",ODBC_SERVER
                   ,";\nDatabase=",ODBC_DATABASE
                   ,";\nUID=",ODCB_ID
                   ,";\nPWD=",ODBC_PW
                   ,"; Port=",ODCB_PORT
                   ,sep="")
  c_string <<- c_string
}


#' Checks to make sure that there is a connection to the database, and connects if there isn't.
#' Then runs the query provided
#' @export

db_query <- function(query){

  if(exists("con") == FALSE) {
    con <- odbc::dbConnect(odbc::odbc()
                           ,.connection_string = c_string
                           ,timeout = 10)}

  results <- DBI:dbGetQuery(con,query)
  return(results)
}

#' Checks to make sure that there is a connection to the database, and connects if there isn't.
#' Then sends the table
#' @export

db_send <- function(df,schema,table_name) {
  if(exists("con") == FALSE) {
    con <- odbc::dbConnect(odbc::odbc()
                           ,.connection_string = c_string
                           ,timeout = 10)}
  target <- paste(schema,table_name,sep=".")
  dbWriteTable(con
               ,SQL(target)
               ,df)
}