#' removeDuplicates
#'
#' @param con con
#' @param table table
#' @param key key. default to 'pk'.
#'
#' @export
#' @examples \dontrun{
#' con = dbcon('mihai', host = '127.0.0.1', db = 'tests')
#' removeDuplicates(con, 'n1', 'id')
#'}
removeDuplicates <- function(con, table, key = 'pk') {
t0 = Sys.time()
n0 = dbq(con, paste("select count(*) n from ", table),enhance= FALSE )$n
cols = dbq(con, paste("SELECT * FROM", table, "WHERE FALSE" ) )%>% names %>% setdiff(. , key) %>% paste(collapse = '`,`')
cols = paste0('`', cols, '`')
dbExecute(con, "DROP TABLE IF EXISTS temp" )
dbExecute(con, paste("CREATE TABLE temp like", table) )
n1 = dbExecute(con, paste("INSERT INTO temp(", cols, ") SELECT DISTINCT", cols, "FROM", table) )
o = dbExecute(con, paste("RENAME TABLE", table, "TO temp2, temp TO", table) )
if(n1 <= n0)
dbExecute(con, "DROP TABLE temp2")
data.frame(nrows = n0 - n1, mins_run = difftime( Sys.time(), t0) %>% as.numeric)
}
#' string_is_mysql_date
#'
#' @param x A string
#'
#' @export
string_is_mysql_date <- function(x) {
o = str_detect(x, pattern = '(\\d{2}|\\d{4})(?:\\-)?([0]{1}\\d{1}|[1]{1}[0-2]{1})(?:\\-)?([0-2]{1}\\d{1}|[3]{1}[0-1]{1})(?:\\s)?([0-1]{1}\\d{1}|[2]{1}[0-3]{1})(?::)?([0-5]{1}\\d{1})(?::)?([0-5]{1}\\d{1})')
if ( all(is.na(o)) ) o = FALSE else o = all(o, na.rm= TRUE)
o
}
#' Probe a db server
#' @description Fast probe a db server see if it can be reached through a given port. Default to scidb.mpio.orn.mpg.de and port 3306
#' @param probe if FALSE, will not probe the db and return ''127.0.0.1'. Default to TRUE.
#' @param name default to scidb.mpio.orn.mpg.de
#' @param port default to 3306 (mariadb's default port )
#' @return when reachable return the name of probed server else falls back to localhost (127.0.0.1)
#' @export
#' @examples \dontrun{
#' probeDB()
#'
#'}
probeDB <- function(probe = TRUE, name = 'scidb.mpio.orn.mpg.de', port = 3306) {
if(probe) {
noBash = inherits(try(system('bash', intern = TRUE, ignore.stderr = TRUE), silent = TRUE) ,'try-error')
if(noBash) {
warning(paste('bash is not available so I cannot probe', name, '; host is set to localhost.') )
host = '127.0.0.1'
} else {
syscall = paste0("timeout 0.5 bash -c 'cat < /dev/null > /dev/tcp/", name , "/", port, "'")
ans = suppressWarnings( attributes( system(syscall, intern = TRUE,ignore.stderr = TRUE) ) )
host = if( is.null(ans) ) name else '127.0.0.1'
}
}
if (name %in% c('localhost', '127.0.0.1') | (!probe) ) host = '127.0.0.1'
host
}
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.