R/database-utilities-general.R

Defines functions dim_table check_if_table_has_foreign_key get_full_table_name check_if_table_exists check_if_schema_exists run_query close_conn_to_mysql_server default_conn_to_mysql_server

Documented in check_if_schema_exists check_if_table_exists close_conn_to_mysql_server default_conn_to_mysql_server dim_table run_query

# General database utilities

#' @title Create default connection to mysql server
#'
#' @description Connects the r-user to the mysql server based on their IP
#'
#' @param user The user attempting to connect to the server
#' @param host The ip of the host server (eg. "localhost" for local connections)
#' @param password  The password used by the user
#' 
#' @details Default conn will check the user's IP and use the correct host depending
#' on the user's location.
#' 
#' @return A mysql \code{con} used for all r to sql interactions
#' 
#' @export
#' 
default_conn_to_mysql_server <- function (user = "r-user", host = NULL, password = NULL) {
  args_to_connect <- list(drv = RMySQL::MySQL())
  if (!is.null(user)) args_to_connect$user <- user
  if (!is.null(password))  args_to_connect$password  <- password
  if (is.null(host)) args_to_connect$host <-
      ifelse(get_most_recent_ip_from_file() == get_public_ip()
             && check_if_mysql_is_running(),
             "localhost", get_most_recent_ip_from_file())
  return (do.call(DBI::dbConnect, args_to_connect))
}

#' @title Close a MySQL connection
#'
#' @param conn The connection to be closed
#' 
#' @description Close a MySQL connection
#' 
#' @export
#' 
close_conn_to_mysql_server <- function (conn) {
  return (DBI::dbDisconnect(conn))
}

#' @title Run a query
#'
#' @description Runs sql queries given a connection
#'
#' @param query A query or list of queries to be run
#' @param conn  Connection to run the query
#' 
#' @details Sends, fetches, and clears the queries given to the connection. Will loop
#' through all queries if given a list.
#' 
#' @return The output of the \code{query} that was run
#' 
run_query <- function (query, conn = default_conn_to_mysql_server()) {
  if (substitute(conn) == "default_conn_to_mysql_server()") {
    on.exit(close_conn_to_mysql_server(conn))
  }
  if (is.null(names(query))) names(query) <- paste0("query", 1:length(query))
  d <- list()
  for (i in 1:length(query)) {
    q <- query[[i]]
    r <- DBI::dbSendQuery(conn = conn, statement = q)
    d[[names(query)[i]]] <- DBI::dbFetch(r, n = -1)
    DBI::dbHasCompleted(r)
    DBI::dbClearResult(r)
  }
  rv <- if (length(d) == 1) d[[1]] else d
  return (rv)
}

#' @title Check if a schema exists
#'
#' @param schema_name A character representing a schema name
#' @param conn Connection to run the query
#' 
#' @description Calls \code{run_query} and \code{get_all_schema_names}
#' 
#' @return Boolean depending on existance of schema
#' 
check_if_schema_exists <- function (schema_name, conn = default_conn_to_mysql_server()) {
  if (substitute(conn) == "default_conn_to_mysql_server()") {
    on.exit(close_conn_to_mysql_server(conn))
  }
  all_schemas <- get_all_schema_names(conn)
  return (tolower(schema_name) %in% all_schemas)
}

#' @title Check if a table exists
#'
#' @param table_name A character representing a table name
#' @param conn Connection to run the query
#' 
#' @description Calls \code{run_query} and \code{get_table_schema_names}
#' 
#' @return Boolean depending on existance of table
#' 
check_if_table_exists <- function (table_name, conn = default_conn_to_mysql_server()) {
  if (substitute(conn) == "default_conn_to_mysql_server()") {
    on.exit(close_conn_to_mysql_server(conn))
  }
  return (table_name %in% get_all_table_names(conn))
}

# Will be a method for getting a full table name from a btclearn_table object
get_full_table_name <- function (database_table) {
  full_table_name <- paste0(database_table@database_name, ".",
                            database_table@table_name)
  return (full_table_name)
}

# Will be a method for determining if a table has a foreign key
check_if_table_has_foreign_key <- function (database_table) {
  return (length(database_table@foreign_keys) > 0)
}

#' @title Get the size of a table
#' 
#' @param table_name A table name
#' @param conn Connection to run the query
#'
#' @description The function works in the same way as R's \code{dim}
#'
#' @return A numeric vector, length 2, with nrow and ncol 
#' 
dim_table <- function (table_name, conn = default_conn_to_mysql_server()) {
  if (substitute(conn) == "default_conn_to_mysql_server()") {
    on.exit(close_conn_to_mysql_server(conn))
  }
  
  # First verify that the table exists
  if (!(table_name %in% get_all_table_names(conn))) {
    warning("Table '", table_name, "' does not exist")
    return (NA)
  }
  
  # Build the queries to check table size and run them
  nrows <- run_query(paste0("SELECT COUNT(*) AS n_row FROM ", table_name, ";"), conn)$n_row
  ncols <- ncol(run_query(paste0("SELECT * FROM ", table_name, " LIMIT 1;"), conn))
  return (c(nrows, ncols))
}
kyleengel/btclearn documentation built on June 7, 2018, 12:26 a.m.