The xe package

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()
#> Error in .oci.Connect(.oci.drv(), username = username, password = password, : ORA-12541: TNS:no listener
# you should now have something like this:
con
#> Error: object 'con' not found

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

xe_tables(con, "hafvog")
#> Error: object 'con' not found
xe_views(con, "hafvog") %>% collect()
#> Error: object 'con' not found

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")
#> Error: object 'con' not found

For this view a convenient function, “lesa_lengdir” is available:

lesa_lengdir(con)
#> Error: object 'con' not found

(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()
#> Error: object 'con' not found
lesa_lengdir(con)
#> Error: object 'con' not found
lesa_numer(con)   %>% glimpse()
#> Error: object 'con' not found
lesa_kvarnir(con) %>% glimpse()
#> Error: object 'con' not found

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

tbl_xe(con, "hafvog.skraning")  %>% glimpse()
#> Error: object 'con' not found

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))
#> Error: object 'con' not found