README.Rmd

---
output:
  github_document:
    html_preview: true
editor_options:
  chunk_output_type: console
---

```{r, include = FALSE}
knitr::opts_chunk$set(fig.path = "man/figures/README-")
suppressPackageStartupMessages(library(tidyverse))
```


# BioticExplorerServer
**R Package for Downloading Server-Side Data for BioticExplorer**

[![Website](https://img.shields.io/badge/pkgdown-website-blue)](https://deepwaterimr.github.io/BioticExplorerServer/)

This package downloads and compiles the IMR Biotic survey database into a [DuckDB](https://duckdb.org/docs/api/r.html) database, providing programmatic access to all of the institute's Biotic data from R. It serves as the data backend for the [BioticExplorer](https://github.com/DeepWaterIMR/BioticExplorer) Shiny application.

See the [package website](https://deepwaterimr.github.io/BioticExplorerServer/) for documentation and function reference.

## Installation

```{r, eval = FALSE}
remotes::install_github("DeepWaterIMR/BioticExplorerServer")
```

## Usage

### Download the IMR biotic database

The BioticExplorerServer package (BES) downloads and compiles the IMR Biotic database into a [duckdb](https://cran.r-project.org/package=duckdb) database. The download requires stable intranet access (VPN, cable within the institute web or HI-adm WiFi). The **database requires more than 2 Gb of disk space**. Make sure to modify the `dbPath` argument to choose an appropriate location for the database. **Do not store it in a folder that is synced to the cloud** due to its size, and be mindful of the [institute's data policy](https://www.hi.no/resources/Data-policy-HI.pdf). While most of the data is licensed under NLOD (the governmental version of CCBY), **some external data within the database must not be shared outside the institute**. **By using this package, you accept the responsibility of handling the IMR Biotic data according to the licenses and regulations**. It is advisable to run the download command in a separate R session or in a screen session in the terminal on Unix machines, as downloading the database takes several hours and requires a stable internet connection. If the connection is unstable, the function may return an error. In such cases, ensure that the connection is stable and rerun the command. The function should continue downloading from where it left off.

```{r, eval = FALSE}
library(BioticExplorerServer)
compileDatabase() # writes to defaultDbPath(); pass dbPath to choose another folder
```

**Where the database goes.** The default `dbPath` is `~/IMR_biotic_BES_database` on macOS and Linux. On Windows R expands `~` through the Documents special folder, which OneDrive's Known Folder Move often redirects into a synchronized `OneDrive - <Organization>\Documents` folder — the last place a multi-gigabyte database should live. Since version 0.8.7 the default therefore resolves to `%USERPROFILE%\IMR_biotic_BES_database` on Windows, matching the convention documented by [BAIT](https://github.com/DeepWaterIMR/BAIT). Call `defaultDbPath()` to see the resolved path, and `findDatabase()` to locate a database that an earlier version installed under Documents.

### Update the database

Once a database has been compiled, update it without downloading unchanged years:

```{r, eval = FALSE}
library(BioticExplorerServer)
updateDatabase()
```

`updateDatabase()` checks metadata for each API delivery and transactionally replaces only
years containing changed, added, or removed deliveries. If the database was built with an
incompatible BioticExplorerServer schema, it uses `compileDatabase()` to build and validate
a complete sibling database before safely swapping it into place. To deliberately re-download
particular years without running the metadata check, use:

```{r, eval = FALSE}
compileDatabase(years = 2024:2026, overwrite = TRUE)
```

### Uninstall the database

If you want to remove the database from your computer, simply delete the folder at the specified `dbPath`. Remember to empty your trash bin as well.

### Control the database through R

Once the database has been downloaded and saved to a [duckdb](https://cran.r-project.org/package=duckdb) database, you can use standard [DBI](https://cran.r-project.org/package=DBI) or [dplyr](https://cran.r-project.org/package=dplyr) functions to access it:

```{r, message = FALSE}
# Packages required to replicate the example:
packages <- c("tidyverse", "data.table", "DBI", "duckdb")

# Install packages not yet installed
installed_packages <- packages %in% rownames(installed.packages())
if (any(installed_packages == FALSE)) {
  install.packages(packages[!installed_packages])
}

# Load the packages
invisible(lapply(packages, library, character.only = TRUE, quietly = TRUE))

# Connect to the database (assuming you used standard dbPath and name)
con_db <- BioticExplorerServer::findDatabase() %>% 
  duckdb::duckdb(read_only = TRUE) %>% 
  DBI::dbConnect()

## Create the data objects

stnall <- dplyr::tbl(con_db, "stnall") # station-based data
indall <- dplyr::tbl(con_db, "indall") # individual-based data
ageall <- dplyr::tbl(con_db, "ageall") # age data
mission <- dplyr::tbl(con_db, "mission") # information on
meta <- dplyr::tbl(con_db, "metadata") %>% # time of download
  collect() %>%
  mutate(across(c(timestart, timeend), as.POSIXct))
csindex <- dplyr::tbl(con_db, "csindex") # cruise series index
gearlist <- dplyr::tbl(con_db, "gearindex") %>% collect() # gear index
taxalist <- dplyr::tbl(con_db, "taxaindex") %>% collect() # taxa index
```

These data objects can now be used in R:

```{r}
mission %>%
  arrange(startyear, platformname, missionnumber) %>%
  head() %>%
  collect()
```

The [dplyr package can also be used with databases](https://solutions.posit.co/connections/db/r-packages/dplyr/). The only difference from normal use is that you'll need to [`collect()`](https://dbplyr.tidyverse.org/reference/collapse.tbl_sql.html) the data from the database after filtering. Note that you are handling large amounts of data, and using the collect function incorrectly may cause your computer to crash due to insufficient RAM. Therefore, always filter before collecting and consider using [`compute()`](https://dbplyr.tidyverse.org/reference/collapse.tbl_sql.html) or use the [data.table](https://cran.r-project.org/web/packages/data.table/index.html) package, if you'll need to handle very large proportions of the IMR Biotic database. Stations with no catch (i.e., where `commonname` is NA) can now be found with `stnall |> filter(is.na(commonname))` (Figure 1). 

```{r, fig.cap = "Figure 1. Number of IMR Biotic sampling stations per year from around 1914 to present. Zero catch stations (is.na(commonname)) are shown in red, while stations with positive catches are shown in grey.", fig.alt = "Figure 1. Number of IMR Biotic sampling stations per year from around 1914 to present. Zero catch stations (is.na(commonname)) are shown in red, while stations with positive catches are shown in grey."}
stnall %>%
  filter(!is.na(cruise)) %>%
  collect() %>%
  group_by(startyear) %>%
  reframe(
    n = length(unique(paste(cruise, platformname, serialnumber))),
    zero_catch = length(unique(paste(cruise, platformname, serialnumber)[is.na(
      commonname
    )]))
  ) %>%
  mutate(n = n - zero_catch) %>%
  rename(Positive = n, Zero = zero_catch) %>%
  pivot_longer(cols = c(Positive, Zero), names_to = "type", values_to = "n") %>%
  ggplot() +
  geom_col(aes(x = startyear, y = n, fill = type)) +
  scale_fill_manual(values = c("Positive" = "grey", "Zero" = "#C99A9A")) +
  scale_x_continuous(breaks = seq(1910, 2020, by = 10), expand = c(0, 0)) +
  scale_y_continuous(expand = expansion(mult = c(0, 0.05))) +
  labs(
    x = "Year",
    y = "Number of stations",
    title = "Number of sampling stations in IMR survey data over years",
    fill = "Catch type"
  ) +
  theme_classic() +
  theme(
    legend.position = "bottom",
    panel.grid.major.y = element_line(color = "grey80", linewidth = 0.5),
    panel.grid.minor.y = element_line(color = "grey90", linewidth = 0.25),
    panel.grid.major.x = element_line(color = "grey80", linewidth = 0.25),
    panel.grid.minor.x = element_line(color = "grey90", linewidth = 0.1)
  )
```

The duckdb database contains following data tables:

```{r}
DBI::dbListTables(con_db)
```

`source_manifest`, created by `updateDatabase()`, stores metadata-only change signals used to identify which years need refreshing.

### Explore the database using Biotic Explorer shiny app

Once downloaded, you can also use the database through the [Biotic Explorer](https://github.com/DeepWaterIMR/BioticExplorer) shiny app. 

### Human and AI-assisted workflows

For reusable, privacy-conscious workflows for people and AI agents working with IMR Biotic data, see [BAIT](https://deepwaterimr.github.io/BAIT/).

## Troubleshooting

If you get an error something like:

> Error:
> ! error in evaluating the argument 'drv' in selecting a method for function 'dbConnect':
> rapi_startup: Failed to open database: {"exception_type":"IO","exception_message":"Could not set lock on file
> ...

You likely have database connection with write privileges open elsewhere within your R session. DuckDB supports only one write connection, but multiple simultaneus read only connections can be established. Close the connections to the database:

```{r}
DBI::dbDisconnect(con_db)
```

and connect to the database in read only mode: `duckdb::duckdb(read_only = TRUE)`.