In fishvice/xe: dplyr backend to the XE Oracle database

knitr::opts_chunk$set(
  message = FALSE, 
  warning = FALSE,
  collapse = TRUE,
  error = TRUE,
  comment = "#>"
)

Preamble

{xe} allows seamless connection to the MRI Oracle XE-database via R. Unlike the mar-database the XE-database resides on personal computers and is what the software hafvog communicates with. The XE-database has at minimum one schema - hafvog. This schema contains all cruises that are visible in the hafvog-software.

The {xe] is supposed to mimic a proportion of the functional calls that reside in {mar}

Installing:

remotes::install_github("fishvice/xe",  dependencies = FALSE, args='--no-multiarch')

Some small print: The Oracle XE-database runs on Oracle 11g and thus one can not use {dbplyr} versions >2.0. Hence we need to install and older version:

remotes::install_github("tidyverse/dbplyr@v1.4.4", force = TRUE)

Basic functionality

Once an R session is started the connection to xe is done via:

library(dplyr)
library(ROracle)
library(xe)
con <- connect_xe()
# you should now have something like this:
con

To get an overview of what is available (e.g. in schema hafvog):

xe_tables(con, "hafvog")
xe_views(con, "hafvog") %>% collect()

We have a bit of an issue here, some of the tables and views are no longer used in the latest Havog (>4), but still retained in case revertion to Hafvog <4 is needed.

To access any of the tables or views one simply does:

tbl_xe(con, "hafvog.lengdir")

For this view a convenient function, "lesa_lengdir" is available:

lesa_lengdir(con)

(synis_id is here negative, just to ensure that there is no clash with older data that we may want the merge with the recently collected data).

We have these main convenient functions:

# not run
lesa_stodvar(con) %>% glimpse()
lesa_lengdir(con)
lesa_numer(con)   %>% glimpse()
lesa_kvarnir(con) %>% glimpse()

The above are actually views of the data. The actual data are stored in table hafvog.skraning

tbl_xe(con, "hafvog.skraning")  %>% glimpse()

If we want to get measurements taken in a particular cruise one could do:

lesa_stodvar(con) %>% 
  filter(leidangur == "TB1-2022") %>% 
  select(synis_id, stod, reitur, tognumer) %>% 
  # need to fix this
  left_join(lesa_skraning(con) %>% mutate(synis_id = -synis_id))