tests/testthat/test-sendUpdates.R

In DatabaseConnector: Connecting to Various Database Platforms

library(testthat)

test_that("Send updates to server", {
  sql <- "CREATE TABLE #temp (x INT);
    INSERT INTO #temp (x) SELECT 123;
    DELETE FROM #temp WHERE x = 123;
    DROP TABLE #temp;"

  # Postgresql --------------------------------------------------
  details <- createConnectionDetails(
    dbms = "postgresql",
    user = Sys.getenv("CDM5_POSTGRESQL_USER"),
    password = URLdecode(Sys.getenv("CDM5_POSTGRESQL_PASSWORD")),
    server = Sys.getenv("CDM5_POSTGRESQL_SERVER")
  )
  connection <- connect(details)

  expect_null(renderTranslateExecuteSql(connection, sql))

  expect_true(sum(renderTranslateExecuteSql(connection, sql, runAsBatch = TRUE)) > 0)

  disconnect(connection)

  # SQL Server --------------------------------------------------
  details <- createConnectionDetails(
    dbms = "sql server",
    user = Sys.getenv("CDM5_SQL_SERVER_USER"),
    password = URLdecode(Sys.getenv("CDM5_SQL_SERVER_PASSWORD")),
    server = Sys.getenv("CDM5_SQL_SERVER_SERVER")
  )
  connection <- connect(details)

  expect_null(renderTranslateExecuteSql(connection, sql))

  expect_true(sum(renderTranslateExecuteSql(connection, sql, runAsBatch = TRUE)) > 0)

  disconnect(connection)

  # Oracle --------------------------------------------------
  details <- createConnectionDetails(
    dbms = "oracle",
    user = Sys.getenv("CDM5_ORACLE_USER"),
    password = URLdecode(Sys.getenv("CDM5_ORACLE_PASSWORD")),
    server = Sys.getenv("CDM5_ORACLE_SERVER")
  )
  connection <- connect(details)

  expect_null(renderTranslateExecuteSql(connection, sql))

  expect_true(sum(renderTranslateExecuteSql(connection, sql, runAsBatch = TRUE)) > 0)

  disconnect(connection)

  # RedShift --------------------------------------------------
  details <- createConnectionDetails(
    dbms = "redshift",
    user = Sys.getenv("CDM5_REDSHIFT_USER"),
    password = URLdecode(Sys.getenv("CDM5_REDSHIFT_PASSWORD")),
    server = Sys.getenv("CDM5_REDSHIFT_SERVER")
  )
  connection <- connect(details)

  expect_null(renderTranslateExecuteSql(connection, sql))

  expect_true(sum(renderTranslateExecuteSql(connection, sql, runAsBatch = TRUE)) > 0)

  disconnect(connection)
})