R/DBI_wrapper_funcs.R

In blex-max/ectosearchR: Simple Wrapper Package around DBI for SQL Querying Van Der Linde 2018 Ectomycorrhizal Dataset

library(processx)
library(httr)
library(DBI)
library(RPostgres)


.db_env <- new.env(parent = emptyenv())


#' Connecting to the Database
#' 
#' Open a connection to Van Der Linde 2018 PostgreSQL database with basic credentials. The database is hosted on Heroku. The resulting connection can then be interacted with using normal DBI functions, or any other applicable SQL R package. A wrapper around DBI::dbConnect() that avoids having to remember credentials. Uses RPostgres::Postgres() as engine driver.
#' @return 
#' Per dbConnect, returns an S4 object that inherits from DBIConnection. This object is used to communicate with the database engine.
#' @examples 
#' db <- ecto_superconnect()
#' @export
ecto_superconnect <- function() {

  #config <- processx::run("heroku", c("config:get", "DATABASE_URL", "-a", "euro-ecto-db")) not presently working
  
  pg <- httr::parse_url('postgres://cdjchijvscjcfn:3f3c12ccc03ac9e7ef7e35d50fd0c52dc70061c94cc5f0e2abc75fdff1e62fec@ec2-54-74-77-126.eu-west-1.compute.amazonaws.com:5432/d3kggorji1nvui') #if above was working, would be config$stdout. This is a very lazy temp fix (see this in 2 years...)
  
  con <- dbConnect(RPostgres::Postgres(),
            dbname = trimws(pg$path),
            host = pg$hostname,
            port = pg$port,
            user = pg$username,
            password = pg$password,
            sslmode = "require"
  )
  
  return(con)
}

#' Connecting to the Database
#' 
#' Invisibly connect to Van Der Linde 2018 PostgreSQL database hosted Heroku with basic credentials. This function provides a wrapper around DBI dbConnect to allow interaction with the database through the functions included in this package, while limiting direct user access.
#' @examples 
#' ecto_connect()
#' @export
ecto_connect <- function() {
  
  #config <- processx::run("heroku", c("config:get", "DATABASE_URL", "-a", "euro-ecto-db"))
  
  pg <- httr::parse_url('postgres://cdjchijvscjcfn:3f3c12ccc03ac9e7ef7e35d50fd0c52dc70061c94cc5f0e2abc75fdff1e62fec@ec2-54-74-77-126.eu-west-1.compute.amazonaws.com:5432/d3kggorji1nvui')
  
  assign(
    'ecto_db',
    
    dbConnect(RPostgres::Postgres(),
                     dbname = trimws(pg$path),
                     host = pg$hostname,
                     port = pg$port,
                     user = pg$username,
                     password = pg$password,
                     sslmode = "require"),
    
    envir = .db_env
  )
  
}

#' Disconnecting from the Database
#' 
#' A wrapper function around dbDisconnect to be called when you have finished working with the database in a given R script. Frees up memory, resources, etc.
#' @examples 
#' ecto_disconnect()
#' @export
ecto_disconnect <- function() {
  
  dbDisconnect(.db_env$ecto_db)
  
}

# simplified functions to interact with db

#' Listing Data Tables
#' 
#' List all tables available for SQL querying in the Van Der Linde 2018 database. A wrapper around DBI dbListTables.
#' @return A character vector containing all table names available in the database.
#' @export
ecto_tables <- function() {
  
  t <- dbListTables(conn = .db_env$ecto_db)
  
  return(t)
}

#' Listing Fields (Columns) in a Data Table
#' 
#' List all fields in a given table in the Van Der Linde 2018 database. A wrapper around DBI dbListFields
#' @param tablename A string corresponding to the name of a table in the database.
#' @return A character vector containing all fields (columns) in the given table.
#' @export
ecto_fields <- function(tablename) {
  
  f <- dbListFields(conn = .db_env$ecto_db, name = tablename)
  
  return(f)
}

#' Querying the Database
#' 
#' Query the Van Der Linde 2018 database and return results. Uses Postgres SQL.
#' @param sql A string corresponding to a postgres SQL query statement, which will be executed on the database.
#' @return A dataframe containing the results of the executed SQL query
#' @examples
#' french_seqs <- ecto_query('SELECT sample_ID, sample_seq FROM seq_data  WHERE ICP_country_code = 1;');
#' all_country_codes <- ecto_query('SELECT DISTINCT Country, code_country FROM plot_metadata;');
#' @export 
ecto_query <- function(sql) {
  
  q <- dbGetQuery(conn = .db_env$ecto_db, statement = sql)
  
  return(q)
} 


# finally, a function to convert strings to DNAbins

#' Strings to DNAbin
#' 
#' Convert a string or vector of strings to an ape DNAbin object.
#' @param dna_strings a string or vector of strings corresponding to a DNA sequence in standard IUPAC notation.
#' @return a DNAbin object containing the sequences, per the package ape
#' @examples 
#' french_seq_data <- ecto_query('SELECT sample_ID, sample_seq FROM seq_data  WHERE ICP_country_code = 1;');
#' seqs <- str2dna(french_seq_data[,2])
#' names(seqs) <- french_seq_data[,1]
#' @export
str2dna <- function(dna_strings) {
  dna <- ape::as.DNAbin(strsplit(dna_strings, ""))
  return(dna)
}