Skip to contents

Find DB table name using regex search.

find_db_tablename.Rd

Some Rgemini functions internally query DB tables. The table names cannot be hard-coded in those functions since HPC datacuts sometimes have slightly different table names (e.g., admdad is called admdad_subset in some datacuts). This function uses a simple regex search to identify the full table name in a given DB that matches the DRM (Data Reference Model) table name of interest.

Currently, the function only supports a subset of table names (see below) and expects the relevant tables in all databases to only differ based on their suffix (e.g., "ipintervention" vs. "ipintervention_subset"). For some tables, the function uses grepl("^tablename", drm_table) to look for table names that start with the same name as specified in DRM (e.g., any that start with "ipintervention"). For other tables, the function uses a stricter search to avoid finding multiple matches: Specifically, for "admdad", "lab", "transfusion", and "radiology" the function tries to identify tables with the exact same name (i.e., "admdad/lab/transfusion") or the corresponding table name with a "_subset" suffix (for HPC datacuts).

Usage

find_db_tablename(dbcon, drm_table, verbose = TRUE)

Arguments

dbcon

(DBIConnection)
A database connection to any GEMINI database.

drm_table

(character)
Table name to be searched, based on the DRM. Currently only accepts the following inputs (which have been verified to work across different DBs/datacuts):

  • "admdad"

  • "ipdiagnosis"

  • "ipintervention"

  • "ipcmg"

  • "transfusion"

  • "lab"

  • "radiology"

Users need to specify the full DRM table name (e.g., "admdad" instead of "adm") to avoid potential confusion with other tables.

verbose

(logical)
Whether or not to show a message indicating which DB table was found.

Value

(character)
Returns the full name of the relevant DB table as a character.

Examples

if (FALSE) { # \dontrun{
drv <- dbDriver("PostgreSQL")
dbcon <- DBI::dbConnect(drv,
  dbname = "db",
  host = "domain_name.ca",
  port = 1234,
  user = getPass("Enter user:"),
  password = getPass("password")
)

admdad_name <- find_db_tablename(dbcon, "admdad")

# query identified table
admdad <- dbGetQuery(dbcon, paste0("select * from ", admdad_name, ";"))
} # }