Need for MS drivers

Users of operating systems other than Microsoft need to have the appropriate MS driver installed into their system. The driver installation process is documented in details in the install drivers vignette vignette.

Authentication

To read data from RDBMS and HIS, the user is expected to have, at least, read access to the system. To establish the connection to their system, users can call the login() function with the following arguments:

• from: The URL to the system of interest. For APIs, this must be the base URL (required).
• type: The source name (required). The current version of the package covers the following RDBMS and HIS types: “ms sql”, “mysql”, “postgresql”, “sqlite”, “dhis2”, “sormas”.
• user_name: The user name (optional).
• password: The user’s password (optional). When the password is not provided (set to NULL), the user will be prompt to enter the password.
• driver_name: The driver name (optional). This is only needed for connecting to RDBMS only.
• db_name: The database name (optional). This is only needed for connecting to RDBMS only.
• port: The port ID (optional). This is only needed for connecting to RDBMS only.

# CONNECT TO A DHIS2 INSTANCE
dhis2_login <- login(
  type = "dhis2",
  from  = "https://smc.moh.gm/dhis",
  user_name = "test",
  password  = "Gambia@123"
)

# CONNECT TO THE TEST MYSQL SERVER
rdbms_login <- login(
  type = "mysql",
  from = "mysql-rfam-public.ebi.ac.uk",
  user_name = "rfamro",
  password = "",
  driver_name = "",
  db_name = "Rfam",
  port = 4497
)

# CONNECT TO A SORMAS SYSTEM
dhis2_login <- login(
  type = "sormas",
  from  = "https://demo.sormas.org/sormas-rest",
  user_name = "SurvSup",
  password  = "Lk5R7JXeZSEc"
)

Reading data from RDBMS

Health related research data are usually stored in either relational databases or non-SQL databases. For example, at MRCG@LSHTM, projects data are stored in relational databases. A SQL-based database is run under a specific sever. The current version of the {readepi} package provides a function (read_rdbms()) for reading data from MS SQL, MySQL, PostgreSQL, and SQLite. The read_rdbms() function takes the following arguments:

• login: The connection object obtained from the login() function
• query: An SQL query or a list with the following elements:
  1. table: a string with the table name.
  2. fields: a vector of column names. When specified, only those columns will be returned. Default is NULL.
  3. filter: an expression or a vector of values used to filter the rows from the table of interest. This should be of the same length as the value for the ‘select’. Default is NULL.

# CONNECT TO THE TEST MYSQL SERVER
rdbms_login <- login(
  type = "mysql",
  from = "mysql-rfam-public.ebi.ac.uk",
  user_name = "rfamro",
  password = "",
  driver_name = "",
  db_name = "Rfam",
  port = 4497
)

# DISPLAY THE LIST OF TABLES FROM A DATABASE OF INTEREST
tables <- show_tables(login = rdbms_login)
head(tables)
#> [1] "_annotated_file"     "_family_file"        "_genome_data"       
#> [4] "_lock"               "_overlap"            "_overlap_membership"

# READING ALL FIELDS AND ALL RECORDS FROM ONE TABLE (`author`) USING AN SQL
# QUERY
dat <- read_rdbms(
  login = rdbms_login,
  query = "select * from author"
)

# READING ALL FIELDS AND ALL RECORDS FROM ONE TABLE (`author`) WHERE QUERY
# PARAMETERS ARE SPECIFIED AS A LIST
dat <- read_rdbms(
  login = rdbms_login,
  query = list(table = "author", fields = NULL, filter = NULL)
)

# SELECT FEW COLUMNS FROM ONE TABLE AND LEFT JOIN WITH ANOTHER TABLE
dat <- read_rdbms(
    login = rdbms_login,
    query = "select author.author_id, author.name,
  family_author.author_id from author left join family_author on
  author.author_id = family_author.author_id"
)