R/nhs_Upload.R

In yikeshu0611/nhanesR: Tools for "NHANES" Database

#' add functions, schema and tables
#' create 13 funtions, shema mimic3 and 26 tables
#' @param conn connection, if missing, it will be get 'connectiontopsql' from global environment.
#'
#' @return 13 funtions, shema mimic3 and 26 tables in PostgreSQL.
#' @export
#'
nhs_Upload <- function(files,conn){
    if (missing(conn)) conn <- get('nhs_Connect',envir = .GlobalEnv)
    # add schema -------
    (years <- list.files(get_config_path()))
    for (i in years) {
        # message('add schema ',i)
        statement <- sprintf('CREATE SCHEMA IF NOT EXISTS "%s"',i)
        DBI::dbGetQuery(conn = conn$con,
                        statement = statement)

    }
    if (missing(files)) files <- nhs_tsv()
    if (length(files)==0){
        if (do::cnOS()) stop(get_config_path(),tmcn::toUTF8(" \u8DEF\u5F84\u4E0B\u6CA1\u6709tsv\u6587\u4EF6"))
        if (!do::cnOS()) stop(get_config_path(),' has no tsv files')
    }
    pb <- txtProgressBar(max = length(files),width = 30,style = 3)
    for (i in 1:length(files)) {
        filei <- files[i]
        yeari <- prepare_years(filei)
        (tbl <- paste0(prepare_items(filei),'---', do::Replace0(do::file.name(filei),'\\.tsv')))
        x <- data.table::fread(filei,check.names = FALSE,showProgress = FALSE)
        DBI::dbWriteTable(conn = conn$con,
                          name = c(yeari,tbl),
                          value = x,
                          overwrite=TRUE,
                          row.names=FALSE)
        # RPostgreSQL::dbWriteTable(conn = conn$con,
        #                           name = c(yeari,tbl),
        #                           value = x,
        #                           overwrite=TRUE,
        #                           row.names=FALSE)
        setTxtProgressBar(pb,i)
    }
    message('\nDONE !!! ')
}