inst/template/Package/extras/copyOhdsiResultsFromRemoteToLocal.R

In gowthamrao/StudyManagement: An OHDSI R package to conduct reproducible research.

# if (dir.exists(serverToWriteLocation)) {unlink(serverToWriteLocation, recursive = TRUE, force = TRUE)}


remoteServerConnection <- DatabaseConnector::connect(connectionDetails = remoteServerConnectionDetails)

for (i in (1:nrow(cdmSources))) {#i = 1
  remoteServerResultsSchema <-  cdmSources$resultsDatabaseSchema[[i]]
  localServer <- paste0(localServerPath, '/', remoteServerResultsSchema, '.sqlite')
  localServerConnectionDetails <- DatabaseConnector::createConnectionDetails(dbms = localServerDbms,
                                                                  server = localServer,
                                                                  port = localServerPort)
  localServerConnection = DatabaseConnector::connect(connectionDetails = localServerConnectionDetails)
  print(paste0("Reading from: Server: ", remoteServer, " Schema: ", remoteServerResultsSchema))
  print(paste0("  Writing to: Server: ",  paste0('../', remoteServerResultsSchema, '.sqlite')))

  for (j in (1:length(ohdsiResultTables))) {# j = 1
    ohdsiResultTable <- ohdsiResultTables %>% dplyr::slice(j)

    print(paste0("  Reading table: ", ohdsiResultTable$table, " (", ohdsiResultTable$category, ")"))

    sql <- ohdsiResultTable$sql
    sql <- SqlRender::render(sql = sql, warnOnMissingParameters = TRUE, databaseSchema = remoteServerResultsSchema)
    sql <- SqlRender::translate(sql = sql, targetDialect = dbms)
    data <- DatabaseConnector::querySql(connection = remoteServerConnection, sql = sql)

    DatabaseConnector::insertTable(connection = localServerConnection,
                                   tableName = ohdsiResultTable$table,
                                   data = data,
                                   dropTableIfExists = TRUE,
                                   createTable = TRUE,
                                   tempTable = FALSE,
                                   useMppBulkLoad = useMppBulkLoad)
  }
  DatabaseConnector::disconnect(connection = localServerConnection)
}
DatabaseConnector::disconnect(connection = remoteServerConnection)