---
title: "Publish every dataset to ERDDAP"
subtitle: "One config row per dataset_key over the core, served from DuckDB views"
author: "CalCOFI"
date: today
format:
html:
toc: true
toc-depth: 3
code-fold: true
code-tools: true
df-print: kable
calcofi:
target_name: publish_to_erddap
workflow_type: publish
dependency:
- release_database
output: data/erddap/datasets_calcofi.xml
workflow_url: https://calcofi.io/workflows/publish_to-erddap.html
description: >
Generates the ERDDAP configuration for every dataset in the frozen release,
discovered from each dataset_key's presence in the core rather than a
hand-maintained table list. Serves observations, sampling events, size/stage
frequencies and the pre-thinning full series through DuckDB views, and executes
every view against the real release before writing its config.
editor_options:
chunk_output_type: console
---
## Overview
Generate the ERDDAP configuration for **every** dataset in the frozen release,
discovered from `dataset_key`'s presence in the core schema rather than from a
hand-maintained table list.
This replaces `publish_calcofi_to_erddap.qmd`, which was **already broken and
already prescribed this design**. Its own callout said every `parquet` path in it
pointed at a per-dataset table the ingests no longer publish, that it "will fail
until the config is repointed", and that:
> `sample` carries `time`/`latitude`/`longitude`/`depth_min_m`/`depth_max_m`
> directly β¦ **one config row per `dataset_key` over `sample`/`obs` replaces the
> per-table list.**
Two things changed besides the repointing:
**1 Β· `EDDTableFromDatabase` over DuckDB, not `EDDTableFromParquetFiles`.** ERDDAP
streams filtered results from DuckDB β predicate pushdown, partition pruning, disk
spill β instead of loading whole Parquet files into the JVM heap. That is what
fixed the OOM which killed `ctd_wide` at 4, 5 **and** 6 GB container sizes; see
[the serving benchmark](https://calcofi.io/workflows/bench_erddap_ctd.html).
**2 Β· Every view is executed locally before its XML is written.** The old config's
failure mode was silent: paths that no longer existed, discovered only when ERDDAP
logged "Bad line(s)". Here each view is run against the real release, its row count
and coordinate coverage recorded, and **a view that returns nothing does not get a
dataset block**.
::: {.callout-note title="What gets served"}
Per `dataset_key`, up to four ERDDAP datasets, each only when it has rows:
| datasetID | grain | source |
|---|---|---|
| `{dataset_key}` | one row per observation (occurrence Γ measurement) | `obs_bio`/`obs_env` + `taxon` + `sample` (falls back to `obs` + `taxon` + `sample` for a release cut before D-S1) |
| `{dataset_key}_sample` | one row per sampling event, effort widened onto it | `sample` + `sample_measurement` |
| `{dataset_key}_attribute` | sub-occurrence detail (length/stage bins) | `obs_attribute` + `sample` |
| `{dataset_key}_full` | the full series *before* thinning | a supplemental table |
The old datasetIDs (`calcofi_ctd`, `calcofi_casts`, `calcofi_ctd_thin`, β¦) are
**not** preserved β these are `dataset_key`-based and self-describing. Deploy is
manual and selective, so the changeover is a decision made at splice time, not
here.
Since the pre-release plan D-S1/D-S3 (2026-09), `{dataset_key}` also carries the
observation's own sample gear and effort (`tow_type`, `std_haul_factor`,
`prop_sorted`, `volume_sampled_m3`) and the two canonical densities
(`density_per_10m2`, `density_per_1000m3`, `effort_class`) inline β no join to
`sample_measurement` at query time β plus `units` and `qual_ok`. Which table is
read is decided **through the release catalog**, never a hand-built
`releases/{v}/parquet` path: see the `obs-pair` chunk below.
:::
## Setup
```{r}
#| label: setup
#| message: false
#| warning: false
librarian::shelf(DBI, duckdb, dplyr, fs, glue, jsonlite, purrr, readr, stringr,
tibble, tidyr, here, knitr, xml2, quiet = TRUE)
here <- here::here
options(readr.show_col_types = FALSE)
devtools::load_all(here::here("../calcofi4db"))
source(here("libs/erddap.R"))
source(here("libs/erddap_duckdb.R"))
source(here("libs/publish_netcdf.R")) # cc_release_version(), cc_release_parquet()
RELEASE <- cc_release_version()
dir_erddap <- here("data/erddap"); dir_create(dir_erddap)
# LOCAL release parquet β the same bytes as the promoted release, read from disk so
# every view below can actually be executed before its config is written.
#
# Under the staging root, not the repo: release_database.qmd writes bulk parquet to
# cc_stage_path("releases", {version}, "parquet") and keeps only the JSON sidecars
# in data/releases/{version}/. Reading it from the old in-repo location failed here
# with "local release parquet is required to validate the views".
PQ_LOCAL <- cc_stage_path("releases", RELEASE, "parquet")
# The path ERDDAP will see. A DuckDB view binds its parquet paths LITERALLY, so the
# deployed .db must be built against the server's path, not this machine's β an
# identity mount. Two .db files are therefore produced: one with local paths for
# validation, one with server paths for deploy.
# Matches the live server's existing convention, not a guess: the running
# calcofi_ctd_thin dataset binds
# jdbc:duckdb:/share/data/erddap-duckdb/duckdb/calcofi_ctd.db over parquet under
# /share/data/erddap-duckdb/datasets/. (/share/erddap/data is ERDDAP's own
# bigParentDirectory β cache, flags, logs β and is not where data belongs.)
ERDDAP_ROOT <- Sys.getenv("CALCOFI_ERDDAP_ROOT", "/share/data/erddap-duckdb")
ERDDAP_FLAG <- Sys.getenv("CALCOFI_ERDDAP_FLAG", "/share/erddap/data/flag")
ERDDAP_DB <- glue("{ERDDAP_ROOT}/duckdb/calcofi.db")
# BOTH must sit under `datasets/`: the erddap container bind-mounts only
# {datasets,duckdb,tmp} from ERDDAP_ROOT (identity mounts), so a path anywhere else
# resolves on the host and is invisible to ERDDAP's DuckDB β the views build and
# count rows fine from the rstudio container, then every query 500s with
# "No files found that match the pattern".
PQ_SERVER <- glue("{ERDDAP_ROOT}/datasets/release/{RELEASE}/parquet")
ING_SERVER <- glue("{ERDDAP_ROOT}/datasets/ingest")
ING_LOCAL <- cc_stage_path("parquet") # bulk parquet stages outside the repo
cat(glue("release : {RELEASE}\n"))
cat(glue("local parquet : {PQ_LOCAL} ({ifelse(dir_exists(PQ_LOCAL), 'present', 'MISSING')})\n"))
cat(glue("server parquet: {PQ_SERVER}\n"))
cat(glue("output : {dir_erddap}\n"))
stopifnot("local release parquet is required to validate the views" = dir_exists(PQ_LOCAL))
```
```{r}
#| label: connect
con <- dbConnect(duckdb())
for (s in c("SET memory_limit='8GB'", "SET enable_progress_bar=false"))
try(dbExecute(con, s), silent = TRUE)
q <- function(sql, ...) dbGetQuery(con, glue(sql, ..., .envir = parent.frame()))
pq <- function(root, tbl) if (dir_exists(file.path(PQ_LOCAL, tbl)))
glue("{root}/{tbl}/**/*.parquet") else glue("{root}/{tbl}.parquet")
```
```{r}
#| label: obs-pair
#| message: false
# D-S1 (pre-release plan, calcofi4db >= 3.31.0 / calcofi4r >= 1.17.0): the release
# ships the observation grain as obs_bio + obs_env, with `obs` a deprecated view
# over them. Checked through the release CATALOG (cc_release_catalog(), the same
# resolver libs/publish_netcdf.R's other cc_release_*() helpers use; never a
# hand-built releases/{v}/parquet path) rather than guessed from file presence β
# the promoted v2026.08.25 predates D-S1 and has no obs_bio/obs_env, so this must
# fall back cleanly rather than error.
release_catalog <- tryCatch(cc_release_catalog(RELEASE), error = function(e) NULL)
HAS_OBS_PAIR <- FALSE
if (!is.null(release_catalog)) {
HAS_OBS_PAIR <- tryCatch({
calcofi4r::cc_release_sources(release_catalog, "obs_bio")
calcofi4r::cc_release_sources(release_catalog, "obs_env")
TRUE
}, error = function(e) FALSE)
}
cat(if (HAS_OBS_PAIR) glue(
"{RELEASE} catalog carries obs_bio + obs_env: the {{dataset_key}} grain reads ",
"the bifurcated pair (adds tow_type, std_haul_factor, prop_sorted, ",
"volume_sampled_m3, density_per_10m2, density_per_1000m3, effort_class, units, ",
"qual_ok to the existing columns).\n") else glue(
"{RELEASE} catalog does not carry obs_bio + obs_env (a pre-D-S1 release, or no ",
"catalog reachable): the {{dataset_key}} grain falls back to the deprecated ",
"`obs` objects β no effort/density columns for this run.\n"))
```
## Discover what the core holds per dataset
```{r}
#| label: discover
core_tbls <- c("sample", "obs_attribute", "sample_measurement",
if (HAS_OBS_PAIR) c("obs_bio", "obs_env") else "obs")
presence <- bind_rows(lapply(core_tbls, function(t) {
src <- pq(PQ_LOCAL, t)
q("SELECT '{t}' AS tbl, dataset_key, count(*) AS n
FROM read_parquet('{src}', hive_partitioning = true, union_by_name = true)
GROUP BY 1, 2")
}))
pres_wide <- presence |>
tidyr::pivot_wider(names_from = tbl, values_from = n, values_fill = 0) |>
arrange(dataset_key)
kable(pres_wide, caption = glue("Core rows per dataset in {RELEASE}"))
```
```{r}
#| label: discover-supplemental
# A `_full` variant is whatever an ingest declares `supplemental: true` in its
# `tables_owned` β the same YAML the netCDF publisher reads. Declared is not the
# same as usable, so each one is checked below rather than trusted.
iy <- read_ingest_yaml(here())
supp <- bind_rows(lapply(names(iy), function(ds) {
to <- iy[[ds]]$tables_owned
if (is.null(to)) return(NULL)
hit <- Filter(function(t) isTRUE(t$supplemental), to)
if (!length(hit)) return(NULL)
tibble(dataset_key = ds, table = vapply(hit, function(t) t$table, character(1)),
note = vapply(hit, function(t) t$note %||% "", character(1)))
}))
supp <- supp |>
mutate(
in_release = dir_exists(file.path(PQ_LOCAL, table)) |
file_exists(file.path(PQ_LOCAL, glue("{table}.parquet"))),
in_ingest = dir_exists(file.path(ING_LOCAL, dataset_key, table)) |
file_exists(file.path(ING_LOCAL, dataset_key, glue("{table}.parquet"))))
kable(supp, caption = "Supplemental (pre-thinning) tables declared by the ingests")
```
```{r}
#| label: supplemental-usability
# ERDDAP is a tabular server keyed on time/latitude/longitude. A supplemental table
# that carries neither its own coordinates nor a resolvable link to an event that
# has them cannot be served, no matter that it is published.
supp_check <- bind_rows(lapply(seq_len(nrow(supp)), function(i) {
r <- supp[i, ]
root <- if (r$in_release) PQ_LOCAL else file.path(ING_LOCAL, r$dataset_key)
src <- if (dir_exists(file.path(root, r$table)))
glue("{root}/{r$table}/**/*.parquet") else glue("{root}/{r$table}.parquet")
if (!r$in_release && !r$in_ingest)
return(tibble(dataset_key = r$dataset_key, table = r$table, rows = NA_real_,
has_coords = NA, servable = FALSE, why = "not found locally"))
cols <- q("DESCRIBE SELECT * FROM read_parquet('{src}', hive_partitioning = true,
union_by_name = true)")$column_name
has_coords <- all(c("latitude", "longitude", "datetime") %in% cols)
n <- q("SELECT count(*) AS n FROM read_parquet('{src}', hive_partitioning = true,
union_by_name = true)")$n
# no coordinates of its own: can the rows reach an event that has them?
linked <- NA_real_
if (!has_coords) {
fk <- grep("_uuid$|^sample_key$", cols, value = TRUE)
fk <- setdiff(fk, grep(glue("^{r$table}"), fk, value = TRUE))
if (length(fk)) {
st <- (iy[[r$dataset_key]]$netcdf$sample_type %||%
q("SELECT sample_type FROM read_parquet('{pq(PQ_LOCAL,\"sample\")}')
WHERE dataset_key = '{r$dataset_key}' LIMIT 1")$sample_type)
linked <- q("
SELECT count(s.sample_key) AS n FROM (
SELECT DISTINCT {fk[1]} AS k FROM read_parquet('{src}',
hive_partitioning = true, union_by_name = true)) m
LEFT JOIN (SELECT sample_key FROM read_parquet('{pq(PQ_LOCAL,\"sample\")}')
WHERE dataset_key = '{r$dataset_key}') s
ON s.sample_key = '{r$dataset_key}:{st}:' || m.k")$n
tot <- q("SELECT count(DISTINCT {fk[1]}) AS n FROM read_parquet('{src}',
hive_partitioning = true, union_by_name = true)")$n
linked <- linked / tot
}
}
tibble(dataset_key = r$dataset_key, table = r$table, rows = as.numeric(n),
has_coords = has_coords,
servable = has_coords || (!is.na(linked) && linked > 0.99),
why = if (has_coords) "carries its own time/lat/lon"
else if (is.na(linked)) "no coordinates and no resolvable event link"
else glue("{round(100*linked, 1)}% of its events resolve in `sample`"))
}))
kable(supp_check, caption = "Can each supplemental table be served?")
```
::: {.callout-warning title="`mets_measurement` is published but not servable"}
The full ~1-minute METS series carries **no coordinates of its own** β only
`mets_sample_uuid`, `measurement_type`, `measurement_value`, `cruise_key` β and
its event table was never published: the mets ingest emits
`sample`/`obs`/`mets_measurement`, where `sample` holds only the **thinned**
events. So of its **2,366,547** distinct underway events, just **77,795 (3.3%)**
resolve to a `sample` row.
A `calcofi_mets_full` dataset would therefore serve 20.6 M measurements of which
96.7% had no time or position. It is **excluded**, and the fix is upstream: the
mets ingest needs to publish the full underway event table (positions and times
for all 2.37 M records), not only the thinned subset. Filed as a finding rather
than worked around here, because inventing coordinates is worse than serving less.
`obs_ctd_full` has the opposite property β it carries `latitude`/`longitude`/
`datetime`/`cruise_key` denormalized on every row β so `calcofi_ctd-cast_full`
serves fine.
:::
## Build the view definitions
```{r}
#| label: view-sql
# One SQL builder per grain. `root` is substituted twice over: once with local
# paths to VALIDATE, once with the server's paths to DEPLOY.
mt_units <- {
mt <- read_measurement_type(here("metadata/measurement_type.csv"))
setNames(as.list(mt$units), mt$measurement_type)
}
sql_obs <- function(ds, root) glue("
SELECT o.sample_key, o.cruise_key, o.grid_key, s.site_key, s.sample_type,
o.datetime AS time,
o.latitude::DOUBLE AS latitude,
o.longitude::DOUBLE AS longitude,
o.depth_min_m::DOUBLE AS depth,
o.depth_max_m::DOUBLE AS depth_max_m,
o.taxon_key, t.scientific_name, o.life_stage,
o.measurement_type,
o.measurement_value::DOUBLE AS measurement_value,
o.measurement_qual
FROM read_parquet('{root}/obs/dataset_key={ds}/*.parquet') o
LEFT JOIN read_parquet('{root}/taxon.parquet') t USING (taxon_key)
LEFT JOIN (SELECT sample_key, site_key, sample_type
FROM read_parquet('{root}/sample.parquet')) s USING (sample_key)")
# D-S1/D-S3: the {dataset_key} grain reads obs_bio for a bio dataset, obs_env for
# an env dataset (each dataset_key is cleanly one realm β measured on staging
# v2026.08.28: every dataset_key has rows in exactly one of the pair), keeping
# every column of sql_obs() above and adding the sample's own gear + effort +
# the two canonical densities, already computed onto the pair at release time
# (calcofi4db::build_obs_slim()) so no join to sample_measurement is needed here.
.obs_pair_src <- function(root, realm) if (identical(realm, "bio"))
glue("read_parquet('{root}/obs_bio.parquet')") else
glue("read_parquet('{root}/obs_env/**/*.parquet', hive_partitioning = true)")
sql_obs_pair <- function(ds, root, realm = c("bio", "env")) {
realm <- match.arg(realm)
glue("
SELECT o.sample_key, o.cruise_key, o.grid_key, s.site_key, s.sample_type,
o.datetime AS time,
o.latitude::DOUBLE AS latitude,
o.longitude::DOUBLE AS longitude,
o.depth_min_m::DOUBLE AS depth,
o.depth_max_m::DOUBLE AS depth_max_m,
o.taxon_key, t.scientific_name, o.life_stage,
o.measurement_type,
o.value::DOUBLE AS measurement_value,
o.measurement_qual,
o.tow_type,
o.std_haul_factor::DOUBLE AS std_haul_factor,
o.prop_sorted::DOUBLE AS prop_sorted,
o.volume_sampled_m3::DOUBLE AS volume_sampled_m3,
o.density_per_10m2::DOUBLE AS density_per_10m2,
o.density_per_1000m3::DOUBLE AS density_per_1000m3,
o.effort_class, o.units, o.qual_ok
FROM {.obs_pair_src(root, realm)} o
LEFT JOIN read_parquet('{root}/taxon.parquet') t USING (taxon_key)
LEFT JOIN (SELECT sample_key, site_key, sample_type
FROM read_parquet('{root}/sample.parquet')) s USING (sample_key)
WHERE o.dataset_key = '{ds}'")
}
sql_sample <- function(ds, root, eff = character()) {
# effort widened onto the event: one column per sample_measurement type is far
# more usable than a long table with no coordinates of its own
piv <- if (length(eff)) paste0(",\n ", paste(glue(
"m.\"{eff}\""), collapse = ",\n ")) else ""
jn <- if (length(eff)) {
sel <- paste(glue("MAX(measurement_value) FILTER (WHERE measurement_type = '{eff}')",
"::DOUBLE AS \"{eff}\""), collapse = ",\n ")
# paste0 the leading break on AFTER glue: glue's .trim strips a leading newline
# and indentation, which welded this clause onto the preceding alias
# (`... ) sLEFT JOIN ...`) and made both effort-bearing datasets fail to bind
paste0("\n ", glue("
LEFT JOIN (SELECT sample_key,
{sel}
FROM read_parquet('{root}/sample_measurement.parquet')
WHERE dataset_key = '{ds}' GROUP BY sample_key) m USING (sample_key)"))
} else ""
glue("
SELECT s.sample_key, s.sample_type, s.parent_sample_key, s.cruise_key,
s.grid_key, s.site_key, s.order_occ,
s.datetime AS time,
s.latitude::DOUBLE AS latitude,
s.longitude::DOUBLE AS longitude,
s.depth_min_m::DOUBLE AS depth,
s.depth_max_m::DOUBLE AS depth_max_m,
s.tow_type{piv}
FROM (SELECT sample_key, sample_type, parent_sample_key, cruise_key, grid_key,
site_key, order_occ, datetime, latitude, longitude, depth_min_m,
depth_max_m, tow_type
FROM read_parquet('{root}/sample.parquet')
WHERE dataset_key = '{ds}') s{jn}")
}
sql_attribute <- function(ds, root) glue("
SELECT a.sample_key, a.taxon_key, t.scientific_name, a.life_stage,
a.measurement_type, a.bin_value, a.bin_label, a.count, a.measurement_qual,
s.cruise_key, s.grid_key, s.site_key,
s.datetime AS time,
s.latitude::DOUBLE AS latitude,
s.longitude::DOUBLE AS longitude,
s.depth_min_m::DOUBLE AS depth
FROM (SELECT * FROM read_parquet('{root}/obs_attribute.parquet')
WHERE dataset_key = '{ds}') a
LEFT JOIN read_parquet('{root}/taxon.parquet') t USING (taxon_key)
LEFT JOIN (SELECT sample_key, cruise_key, grid_key, site_key, datetime,
latitude, longitude, depth_min_m
FROM read_parquet('{root}/sample.parquet')) s USING (sample_key)")
sql_full <- function(ds, tbl, root) glue("
SELECT f.sample_key, f.cruise_key, f.grid_key,
f.datetime AS time,
f.latitude::DOUBLE AS latitude,
f.longitude::DOUBLE AS longitude,
f.depth_min_m::DOUBLE AS depth,
f.measurement_type,
f.measurement_value::DOUBLE AS measurement_value,
f.measurement_qual
FROM read_parquet('{root}/{tbl}/**/*.parquet', hive_partitioning = true) f
WHERE f.dataset_key = '{ds}'")
```
## Variable attributes for the bio/env grain (D-S3)
```{r}
#| label: obs-pair-attrs
#| message: false
# units_lookup (mt_units, above) is keyed by measurement_type name, which already
# covers std_haul_factor / prop_sorted (registered dimensionless types) for free β
# the pivoted `_sample` columns AND the pair's plain columns share that name. Only
# the derived columns need their own entries (volume_sampled_m3's registered name
# is "volume_sampled"; the densities and qual_ok/effort_class/scientific_name are
# not measurement types at all).
obs_pair_units <- list(
volume_sampled_m3 = "m3",
density_per_10m2 = "count/10m2",
density_per_1000m3 = "count/1000m3")
obs_pair_longname <- list(
scientific_name = "Scientific name",
tow_type = "Net/gear tow type code",
std_haul_factor = "Standard haul factor",
prop_sorted = "Proportion of the catch sorted",
volume_sampled_m3 = "Volume of water sampled",
density_per_10m2 = "Areal density",
density_per_1000m3 = "Volumetric density",
effort_class = "How density was derived for this row",
units = "Units of measurement_value",
qual_ok = "Whether measurement_qual marks this row usable")
# wording mirrors calcofi4r::cc_density_sql()'s own roxygen, not a re-derivation
obs_pair_comment <- list(
density_per_10m2 = paste(
"Depth-integrated areal density. For an oblique/vertical net tow (gear C1,",
"CB, CV, PV) reporting units = 'count': measurement_value * std_haul_factor /",
"prop_sorted. For a value the source already publishes as an area density",
"(count/m2, numberPerMeterSquared): measurement_value * 10. NULL otherwise.",
"See calcofi4r::cc_density_sql()."),
density_per_1000m3 = paste(
"Volumetric density. For a tow reporting a sampled volume with units =",
"'count': measurement_value / prop_sorted / volume_sampled_m3 * 1000. For a",
"value already published as a volumetric density (count/1000m3): passed",
"through unchanged. NULL otherwise. See calcofi4r::cc_density_sql()."),
effort_class = paste(
"count_with_effort (a count converted using this row's own gear effort),",
"raw_count_no_effort (a bare count with no effort available),",
"density_as_published (the source already reports a density), or",
"other_unit (neither a count nor a recognized density unit).",
"See calcofi4r::cc_density_sql()."),
qual_ok = paste(
"TRUE unless measurement_qual is one of this dataset's own suspect/bad/missing",
"codes; NULL/unflagged rows are kept. See calcofi4r::cc_qual_ok_sql()."),
scientific_name = "Resolved from taxon_key against the release's shared taxon reference (WoRMS or ITIS).",
units = "Units of measurement_value, from the canonical measurement_type registry (metadata/measurement_type.csv).")
# sdn_parameter_urn (NERC P01): H2 (pre-release plan D-S2) adds `nerc_p01` to
# measurement_type.csv on an exact vocabulary match; nothing here invents one.
# Keyed by measurement_type NAME, same as mt_units, so it only ever lands on a
# column that IS one quantity (a `_sample` grain's pivoted effort column) β never
# on a long measurement_type/measurement_value pair, which mixes quantities and
# cannot carry a single URN.
mt_nerc_p01 <- {
hdr <- names(readr::read_csv(here("metadata/measurement_type.csv"), n_max = 0,
show_col_types = FALSE))
if ("nerc_p01" %in% hdr) {
v <- setNames(as.list(mt$nerc_p01), mt$measurement_type)
v[!vapply(v, function(x) is.null(x) || is.na(x) || !nzchar(x), TRUE)]
} else list()
}
cat(glue("nerc_p01 in metadata/measurement_type.csv: {length(mt_nerc_p01)} of ",
"{nrow(mt)} types ({if (length(mt_nerc_p01) == 0) 'H2 not yet landed β ' else ''}",
"sdn_parameter_urn set only for those).\n"))
# measurement_qual flag_values/flag_meanings: metadata/measurement_qual.csv's
# `code_set` is not yet keyed to dataset_key (only "bottle" and "ctd" exist, per
# their own two documented vocabularies) β matched by substring against
# dataset_key, the same relationship the notebooks already assume elsewhere
# (e.g. `calcofi_bottle`, `calcofi_ctd-cast`). No match, no flag attributes: a
# dataset's own vocabulary is never invented here.
mq <- readr::read_csv(here("metadata/measurement_qual.csv"), show_col_types = FALSE)
qual_flag_atts <- function(ds) {
cs <- unique(mq$code_set)
hit <- cs[vapply(cs, function(c) grepl(c, ds, fixed = TRUE), logical(1))]
if (!length(hit)) return(NULL)
rows <- mq[mq$code_set == hit[1], ] |> dplyr::arrange(qual_code)
list(values = paste(rows$qual_code, collapse = " "),
meanings = paste(rows$label, collapse = " "))
}
```
```{r}
#| label: plan
ds_all <- sort(unique(presence$dataset_key))
n_of <- function(ds, tbl) {
v <- pres_wide[[tbl]][pres_wide$dataset_key == ds]
if (!length(v)) 0 else v
}
eff_of <- function(ds) q("
SELECT DISTINCT measurement_type FROM read_parquet('{pq(PQ_LOCAL,\"sample_measurement\")}')
WHERE dataset_key = '{ds}' ORDER BY 1")$measurement_type
# obs_src_n()/realm_of(): each dataset_key carries rows in exactly one of the
# pair (measured on staging v2026.08.28 β no dataset splits bio/env), so "which
# table" is a lookup, not a per-row decision.
obs_src_n <- function(ds) if (HAS_OBS_PAIR) n_of(ds, "obs_bio") + n_of(ds, "obs_env") else n_of(ds, "obs")
realm_of <- function(ds) if (n_of(ds, "obs_bio") > 0) "bio" else "env"
cfg <- bind_rows(lapply(ds_all, function(ds) {
rows <- list()
if (obs_src_n(ds) > 0)
rows <- c(rows, list(tibble(dataset_id = ds, grain = "obs",
src_rows = obs_src_n(ds))))
if (n_of(ds, "sample") > 0)
rows <- c(rows, list(tibble(dataset_id = glue("{ds}_sample"), grain = "sample",
src_rows = n_of(ds, "sample"))))
if (n_of(ds, "obs_attribute") > 0)
rows <- c(rows, list(tibble(dataset_id = glue("{ds}_attribute"),
grain = "obs_attribute",
src_rows = n_of(ds, "obs_attribute"))))
if (!length(rows)) return(NULL)
bind_rows(rows) |> mutate(dataset_key = ds, .before = 1)
}))
# servable supplemental tables only
cfg <- bind_rows(cfg, supp_check |>
filter(servable) |>
transmute(dataset_key, dataset_id = glue("{dataset_key}_full"), grain = "full",
src_rows = rows, supp_table = table))
# ERDDAP writes `FROM <tableName>` UNQUOTED (only column names honour
# <columnNameQuotes>), so a datasetID containing a hyphen β cce-lter_zooscan,
# calcofi_ctd-cast, sio_mesopelagic-fish β produces SQL DuckDB parses as a
# subtraction: `syntax error at or near "-"`. The datasetID keeps the hyphen (it is
# the public identity, and dataset_key has one); the VIEW gets a SQL-safe name.
cfg <- cfg |>
arrange(dataset_key, grain) |>
mutate(view_name = gsub("[^A-Za-z0-9_]", "_", dataset_id))
kable(cfg, caption = glue("{nrow(cfg)} ERDDAP datasets planned"))
```
## Validate every view against the real release
```{r}
#| label: validate
# `root` is the release parquet; `ing_root` is where an ingest's own outputs live.
# A supplemental table reads from whichever actually holds it, named explicitly
# rather than derived by path arithmetic.
build_sql <- function(r, root, ing_root = ING_LOCAL) {
supp_root <- function(tbl, ds) {
in_rel <- any(supp$in_release[supp$table == tbl], na.rm = TRUE)
if (in_rel) root else glue("{ing_root}/{ds}")
}
switch(r$grain,
obs = if (HAS_OBS_PAIR) sql_obs_pair(r$dataset_key, root, realm_of(r$dataset_key))
else sql_obs(r$dataset_key, root),
sample = sql_sample(r$dataset_key, root, eff_of(r$dataset_key)),
obs_attribute = sql_attribute(r$dataset_key, root),
full = sql_full(r$dataset_key, r$supp_table,
supp_root(r$supp_table, r$dataset_key)))
}
val <- bind_rows(lapply(seq_len(nrow(cfg)), function(i) {
r <- cfg[i, ]
# the `full` grain reads a partitioned multi-GB table; count from a LIMIT-free
# aggregate but probe coordinates from a sample, so validation stays cheap
s <- build_sql(r, PQ_LOCAL)
out <- tryCatch({
d <- q("SELECT count(*) AS n, count(time) AS n_time, count(latitude) AS n_lat,
min(epoch(time)) AS t0, max(epoch(time)) AS t1
FROM ({s})")
tibble(dataset_id = r$dataset_id, rows = as.numeric(d$n),
pct_time = round(100 * d$n_time / pmax(d$n, 1), 1),
pct_coord = round(100 * d$n_lat / pmax(d$n, 1), 1),
t_start = as.character(as.POSIXct(d$t0, origin = "1970-01-01", tz = "UTC")),
t_end = as.character(as.POSIXct(d$t1, origin = "1970-01-01", tz = "UTC")),
ok = d$n > 0, err = NA_character_)
}, error = function(e) tibble(
dataset_id = r$dataset_id, rows = NA_real_, pct_time = NA_real_,
pct_coord = NA_real_, t_start = NA_character_, t_end = NA_character_,
ok = FALSE, err = conditionMessage(e)))
out
}))
kable(val, caption = "Every view executed against the local release parquet")
# A view that returns nothing, or that errors, must not become a dataset block β
# that is exactly how the previous config came to point at tables that no longer
# existed and was only discovered from ERDDAP's own error log.
if (any(!val$ok)) {
cat("\nEXCLUDED (empty or failing):\n")
for (i in which(!val$ok))
cat(glue(" - {val$dataset_id[i]}: {val$err[i] %||% 'zero rows'}\n"))
}
cfg <- cfg |> semi_join(val |> filter(ok) |> select(dataset_id), by = "dataset_id")
```
```{r}
#| label: coord-warning
low <- val |> filter(ok, pct_coord < 99 | pct_time < 99)
if (nrow(low)) {
kable(low |> select(dataset_id, rows, pct_time, pct_coord),
caption = "Served, but with incomplete time or position β ERDDAP will drop these rows from spatial/temporal queries")
}
```
## Build the DuckDB view database
```{r}
#| label: build-db
# DuckDB validates a `read_parquet()` path when the VIEW IS CREATED β it needs the
# schema β so a view bound to the server's path CANNOT be created on this machine.
# That is why `libs/erddap_duckdb.R` documents an identity mount: the .db has to be
# built where the data already sits at the path the views name.
#
# So this produces two artifacts instead of one impossible file:
# calcofi_local.db β built and validated here, against the local release
# build_erddap_db.R β the same view SQL, to run ON THE SERVER after the sync
build_db <- function(db_path, root, ing_root) {
if (file_exists(db_path)) file_delete(db_path)
cx <- dbConnect(duckdb(), dbdir = db_path)
on.exit(dbDisconnect(cx, shutdown = TRUE))
for (i in seq_len(nrow(cfg))) {
r <- cfg[i, ]
dbExecute(cx, glue("CREATE OR REPLACE VIEW \"{r$view_name}\" AS
{build_sql(r, root, ing_root)}"))
}
writeLines(as.character(dbGetQuery(cx, "SELECT version()")[[1]]),
file.path(dirname(db_path), "BUILD_VERSION.txt"))
dbGetQuery(cx, "SELECT view_name FROM duckdb_views() WHERE NOT internal")$view_name
}
v_local <- build_db(file.path(dir_erddap, "calcofi_local.db"), PQ_LOCAL, ING_LOCAL)
cat(glue("validated views: {length(v_local)}\n"))
cat(glue("duckdb engine : {readLines(file.path(dir_erddap, 'BUILD_VERSION.txt'))[1]}\n"))
stopifnot(setequal(v_local, cfg$view_name))
```
```{r}
#| label: emit-server-script
# The server script carries the SQL verbatim, so the deployed views are the ones
# validated above rather than a hand-reimplementation of them.
view_sql <- vapply(seq_len(nrow(cfg)), function(i)
build_sql(cfg[i, ], "{{PQ}}", "{{ING}}"), character(1))
script <- c(
"#!/usr/bin/env Rscript",
"# build_erddap_db.R β GENERATED by publish_to-erddap.qmd. Do not hand-edit.",
"#",
glue("# release: {RELEASE} datasets: {nrow(cfg)}"),
"#",
"# Run this ON THE CalCOFI SERVER, after the release parquet has been synced to",
"# the path below. DuckDB binds a view's parquet paths literally and validates",
"# them at CREATE time, so the .db must be built where the data actually is.",
"#",
"# docker exec -i rstudio bash -lc \\",
"# 'cd /share/erddap/data && Rscript build_erddap_db.R'",
"",
"suppressMessages({library(DBI); library(duckdb)})",
glue("PQ <- Sys.getenv('CALCOFI_ERDDAP_PQ', '{PQ_SERVER}')"),
glue("ING <- Sys.getenv('CALCOFI_ERDDAP_ING', '{ING_SERVER}')"),
glue("DB <- Sys.getenv('CALCOFI_ERDDAP_DB', '{ERDDAP_DB}')"),
"stopifnot('release parquet not found' = dir.exists(PQ))",
"if (file.exists(DB)) file.remove(DB)",
"con <- dbConnect(duckdb(), dbdir = DB)",
"views <- list(",
paste0(" `", cfg$view_name, "` = ",
vapply(view_sql, function(x) paste0("r\"(", x, ")\""), character(1)),
c(rep(",", nrow(cfg) - 1), "")),
")",
"for (nm in names(views)) {",
" sql <- gsub('{{PQ}}', PQ, views[[nm]], fixed = TRUE)",
" sql <- gsub('{{ING}}', ING, sql, fixed = TRUE)",
" dbExecute(con, sprintf('CREATE OR REPLACE VIEW \"%s\" AS %s', nm, sql))",
" n <- dbGetQuery(con, sprintf('SELECT count(*) n FROM \"%s\"', nm))$n",
" cat(sprintf('%-42s %12s rows\\n', nm, format(n, big.mark = ',')))",
"}",
"dbDisconnect(con, shutdown = TRUE)",
"cat('wrote ', DB, '\\n', sep = '')")
script_path <- file.path(dir_erddap, "build_erddap_db.R")
writeLines(script, script_path)
Sys.chmod(script_path, "0755")
# a generated script that does not parse is worse than none
invisible(parse(script_path))
cat(glue("wrote {basename(script_path)} ({length(script)} lines, parses clean)\n"))
```
## Generate `datasets.xml`
```{r}
#| label: xml
#| results: asis
# Title and summary come from each ingest's `dataset_meta` YAML, which is
# authoritative for dataset metadata (it is what the release `dataset` table is
# built from), so ERDDAP, the database and the schema site cannot disagree.
iy_meta <- function(ds) iy[[ds]]$dataset_meta %||% list()
# The dataset-catalog page (plan 2026-09-05 D-4/D-5(4)) β built from the key
# alone, never a hard-coded per-dataset list.
dataset_page_url <- function(dataset_key) glue("https://calcofi.io/datasets/{dataset_key}/")
# The dataset registries the record itself reads (calcofi4db >= 4.1.0):
# provider display name/URL for the creator_*/institution globals when a
# dataset states no contact or creator of its own, and the license registry so
# a dataset's own `license` id resolves to its full name/URL rather than being
# guessed. Read once, here, at render time.
CATALOG_REGISTRIES <- calcofi4db::read_catalog_registries(here("metadata"))
.s0 <- function(x) if (is.null(x) || length(x) == 0 || is.na(x[1])) "" else trimws(as.character(x)[1])
provider_of <- function(slug) {
row <- CATALOG_REGISTRIES$provider[CATALOG_REGISTRIES$provider$provider == slug, ]
if (!nrow(row)) return(list(name = NA_character_, url = NA_character_))
list(name = row$provider_name[1], url = row$url[1])
}
license_of <- function(license_id) {
license_id <- .s0(license_id)
if (!nzchar(license_id)) return(NA_character_)
row <- CATALOG_REGISTRIES$license[CATALOG_REGISTRIES$license$license == license_id, ]
if (nrow(row) && nzchar(.s0(row$name[1]))) row$name[1] else license_id
}
# creator_name/_type/_email/_url + institution: I-13's WS-P2 fix (D-5(4)) β
# ERDDAP's own globals should say what the record says, not a fixed program
# name. Order: the sidecar's own contact/pi_names, else the provider
# organization (never invented) β the same fallback chain WS-E1's build_eml()
# uses for the EML `creator`/`contact` elements, so the two documents agree.
creator_fields_of <- function(dataset_key) {
dm <- iy_meta(dataset_key)
org <- provider_of(iy[[dataset_key]]$provider %||% "")
pis <- trimws(unlist(strsplit(.s0(dm$pi_names), ";")))
pis <- pis[nzchar(pis)]
name <- if (length(pis)) paste(pis, collapse = "; ") else (org$name %||% "CalCOFI")
ctype <- if (length(pis)) "person" else "institution"
email <- .s0(dm$contact)
email <- if (grepl("^https?://", email)) NA_character_ else if (nzchar(email)) email else NA_character_
url <- if (grepl("^https?://", .s0(dm$contact))) dm$contact else (dm$link_calcofi_org %||% org$url %||% "https://calcofi.org")
list(creator_name = name, creator_type = ctype, creator_email = email %||% NA_character_,
creator_url = url, institution = org$name %||% "CalCOFI")
}
# keywords: the sidecar's GCMD Science Keywords (declared for all 16 datasets
# by WS-R1) joined comma-separated, ERDDAP's own convention for this global.
keywords_of <- function(dataset_key) {
kw <- unlist(iy_meta(dataset_key)$keywords_gcmd)
if (!length(kw)) return(NA_character_)
paste(kw, collapse = ", ")
}
# I-13 (this WS-P2's fix, at the source): an em dash in a title survives this
# notebook as real UTF-8, but erddap.calcofi.io's OWN `allDatasets` metadata
# re-serializes it as the literal 6-character string "β" β verified on
# the live server 2026-09-05, decoded downstream since calcofi4db 4.2.x
# (WS-M1's observe_distributions()). The durable fix is not to hand ERDDAP a
# character its own metadata layer mishandles: a plain hyphen reads the same
# and survives every consumer.
title_of <- function(r) {
dm <- iy_meta(r$dataset_key)
base <- dm$dataset_name %||% r$dataset_key
switch(r$grain,
obs = glue("{base} - observations"),
sample = glue("{base} - sampling events"),
obs_attribute = glue("{base} - length/stage frequency"),
full = glue("{base} - full resolution (pre-thinning)"))
}
summary_of <- function(r) {
dm <- iy_meta(r$dataset_key)
desc <- gsub("[[:space:]]+", " ", trimws(dm$description %||% ""))
grain_txt <- switch(r$grain,
obs = paste(Filter(nzchar, c(
"One row per observation: a measurement of one quantity at one",
"event, taxon and depth. `measurement_type` names the quantity and",
"`measurement_value` holds it, so filter on measurement_type.",
if (HAS_OBS_PAIR) paste(
"Each row also carries its own sample's gear and effort",
"(tow_type, std_haul_factor, prop_sorted, volume_sampled_m3) and",
"the two canonical densities (density_per_10m2,",
"density_per_1000m3, effort_class) β see the column comments β",
"and qual_ok, a pre-computed pass/fail on measurement_qual.") else ""))),
sample = paste("One row per sampling event, with event-level effort widened",
"into its own columns. Use this for effort and station",
"counting β summing effort over the observation table",
"double-counts it."),
obs_attribute = paste("Sub-occurrence detail: length- and stage-frequency bins",
"(`bin_value`, `bin_label`, `count`) under each",
"occurrence. Coordinates are inherited from the event."),
full = paste("The full series before adaptive thinning. Much larger than the",
"headline table and provided for scan-level work; most analyses",
"want the thinned table."))
paste(c(desc, grain_txt, glue("Source: CalCOFI integrated database release {RELEASE}."),
if (nzchar(dm$citation_main %||% "")) glue("Citation: {dm$citation_main}.")),
collapse = " ")
}
# every view is a long table of independent rows carrying their own time and
# position, which is ERDDAP's "Point"; the vertical/trajectory structure is
# expressed in the netCDF products, not here
CDM <- "Point"
blocks <- character(0)
for (i in seq_len(nrow(cfg))) {
r <- cfg[i, ]
cols <- q("DESCRIBE SELECT * FROM ({build_sql(r, PQ_LOCAL)})") |>
transmute(column = column_name, duckdb_type = column_type)
qflags <- if ("measurement_qual" %in% cols$column) qual_flag_atts(r$dataset_key) else NULL
xml <- erddap_duckdb_dataset_xml(
staged = as.data.frame(cols),
dataset_id = r$dataset_id,
title = title_of(r),
summary = summary_of(r),
source_url = glue("jdbc:duckdb:{ERDDAP_DB}"),
table_name = r$view_name,
# keyed by column name: mt_units/mt_nerc_p01 land on the `{ds}_sample` views'
# widened effort columns (which ARE measurement_type names) and on the
# bio/env grain's plain std_haul_factor/prop_sorted columns (same names by
# construction); obs_pair_* only exist as columns on the bio/env grain, so
# both are safe to pass to every dataset unconditionally
units_lookup = modifyList(mt_units, obs_pair_units),
longname_lookup = obs_pair_longname,
comment_lookup = obs_pair_comment,
urn_lookup = mt_nerc_p01,
flagvalues_lookup = if (!is.null(qflags)) list(measurement_qual = qflags$values) else list(),
flagmeanings_lookup = if (!is.null(qflags)) list(measurement_qual = qflags$meanings) else list(),
cdm_data_type = CDM,
# infoUrl -> the dataset page (built from the key, D-5(4)); creator_*/
# institution/keywords from the record's own sidecar + provider registry,
# never a fixed program-wide default (I-13's fix travels with these, since
# both read the same iy_meta()). A field the record does not state is
# OMITTED, never written as the literal string "NA" β an ERDDAP global
# that is absent is a fact, not a guess.
global_atts = local({
cr <- creator_fields_of(r$dataset_key)
atts <- list(
license = license_of(iy_meta(r$dataset_key)$license) %||% "not specified",
infoUrl = dataset_page_url(r$dataset_key),
creator_name = cr$creator_name,
creator_type = cr$creator_type,
creator_email = cr$creator_email,
creator_url = cr$creator_url,
institution = cr$institution,
keywords = keywords_of(r$dataset_key),
db_release = RELEASE, dataset_key = r$dataset_key,
references = "https://calcofi.io/workflows/publish_to-erddap.html")
Filter(function(x) !is.null(x) && !(length(x) == 1 && is.na(x)), atts)
}))
dir_create(file.path(dir_erddap, r$dataset_id))
writeLines(xml, file.path(dir_erddap, r$dataset_id, glue("{r$dataset_id}.xml")))
blocks <- c(blocks, xml)
cat(glue("\n- `{r$dataset_id}`: {ncol(cols)} cols, ",
"{val$rows[val$dataset_id == r$dataset_id]} rows\n"))
}
```
```{r}
#| label: assemble
xml_all <- paste0(
"<!-- CalCOFI ERDDAP datasets β generated by publish_to-erddap.qmd\n",
glue(" release {RELEASE}, {length(blocks)} datasets, ",
"{format(Sys.time(), '%Y-%m-%d')}\n"),
" EDDTableFromDatabase over DuckDB views; see the notebook for deploy. -->\n",
paste(blocks, collapse = "\n\n"))
writeLines(xml_all, file.path(dir_erddap, "datasets_calcofi.xml"))
write_csv(cfg |> left_join(val, by = "dataset_id"),
file.path(dir_erddap, "erddap_plan.csv"), na = "")
cat(glue("wrote {length(blocks)} dataset blocks to data/erddap/datasets_calcofi.xml\n"))
```
## Deploy
```{r}
#| label: deploy
#| message: false
source(here("libs/erddap_deploy.R"))
# Gated, like CALCOFI_PUBLISH=true in the netCDF publisher: `tar_make()` keeps
# GENERATING config on every release, but pushing to a public production service
# stays a deliberate act. Set CALCOFI_ERDDAP_DEPLOY=true to actually deploy.
# Deploying is the DEFAULT, matching deploy_consumers.qmd: a release consumers
# never receive is not a release. CALCOFI_DEPLOY=false turns the whole
# post-release chain into a dry run; CALCOFI_ERDDAP_DEPLOY=false skips only this
# leg, which is worth having because it is the slowest (a ~1.6 GB server-side
# parquet pull).
DEPLOY <- !identical(tolower(Sys.getenv("CALCOFI_DEPLOY", "true")), "false") &&
!identical(tolower(Sys.getenv("CALCOFI_ERDDAP_DEPLOY", "true")), "false")
SSH_HOST <- Sys.getenv("CALCOFI_SSH_HOST", "calcofi")
ERDDAP_REPO <- Sys.getenv("CALCOFI_ERDDAP_REPO", here("../erddap"))
ERDDAP_REMOTE_REPO <- Sys.getenv("CALCOFI_ERDDAP_REMOTE_REPO",
"/share/github/CalCOFI/erddap")
ERDDAP_URL <- Sys.getenv("CALCOFI_ERDDAP_URL", "https://erddap.calcofi.io")
cat(glue("deploy: {DEPLOY} -> {ERDDAP_URL} (default; CALCOFI_DEPLOY=false for a dry run, CALCOFI_ERDDAP_DEPLOY=false to skip just this leg)\n"))
```
```{r}
#| label: deploy-run
#| eval: !expr DEPLOY
#| message: false
n_pq <- erddap_sync_parquet(SSH_HOST, ERDDAP_ROOT, RELEASE)
cat(glue("1. synced {n_pq} parquet files to {ERDDAP_ROOT}/datasets/release/{RELEASE}\n"))
built <- erddap_build_db(SSH_HOST, ERDDAP_ROOT, script_path)
cat(glue("2. built view db ({sum(grepl('rows$', built))} views, none empty)\n"))
ids <- erddap_splice_config(ERDDAP_REPO, file.path(dir_erddap, "datasets_calcofi.xml"))
cat(glue("3. spliced {length(ids$all)} generated + {length(ids$kept_outside)} hand-maintained; ",
"added {length(ids$added)}, retiring {length(ids$retired)}\n"))
if (length(ids$retired))
cat(glue(" RETIRING (public URLs stop resolving): {paste(ids$retired, collapse = ', ')}\n"))
erddap_push_config(SSH_HOST, ERDDAP_REPO, ERDDAP_REMOTE_REPO, RELEASE)
erddap_flag(SSH_HOST, ERDDAP_FLAG, ids$all, ids$retired)
cat("4. flagged for reload\n")
# ERDDAP reloads asynchronously, so give it a moment before asking.
Sys.sleep(30)
ver <- erddap_verify(ERDDAP_URL, expect_present = ids$all, expect_absent = ids$retired)
kable(ver, caption = "5. Live verification (the flag directory is not a reliable signal)")
stopifnot("live ERDDAP does not match the deployed config" = all(ver$ok))
```
::: {.callout-note title="What deploying does, and the traps it encodes"}
The five steps are in `libs/erddap_deploy.R`, one function each: sync the release
parquet server-side, build the DuckDB views **on the server** (DuckDB validates a
view's parquet paths at `CREATE` time, so they cannot be built here), splice this
config into `CalCOFI/erddap`, flag every dataset for reload, and verify against
the live service.
Four failure modes are handled there because every one of them fails *silently*:
- **`sudo git pull` cannot work** β root has no GitHub credentials. The fetch runs
as the ssh user, the merge as root. A bare `git pull` prints `Updating <a>..<b>`
and then dies on the write, so its first line reads like success.
- **Ownership is captured and restored.** The checkout is owned by uids that are
not the ssh user; a root merge re-owns the tree and breaks the *next*
unprivileged fetch.
- **The flag directory proves nothing.** It is `drwxrws---` under a group the ssh
user is not in, so an unprivileged `ls` reports zero either way, and ERDDAP
deletes each flag as it consumes it. Verification reads the live service.
- **Splicing at the wrong marker duplicates everything.** `datasets.xml` carries
both an `add dataset definitions below` marker and a `BEGIN/END generated`
pair; only replacing between BEGIN/END is idempotent. Appending at the former
would add a second copy of all 34 datasets.
:::
::: {.callout-warning title="Retiring datasetIDs changes public URLs"}
Datasets are keyed on `dataset_key`, so a provider-slug correction renames a
**public** ERDDAP endpoint. Anything present in the previous generated block but
absent from this one is reported as `retired` above and hard-flagged, so ERDDAP
forgets it and its URL 404s.
Old ids are retired rather than kept as aliases: they are no longer generated, so
leaving them would freeze them at the previous release's definitions while the
data underneath moved on β a duplicate that silently drifts is worse than a clean
404. Datasets **outside** the generated block (`calcofi_casts`, `calcofi_ctd`,
`calcofi_ctd_thin`, `calcofi_ctd_measurement`, `calcofi_euphausiids`,
`calcofi_zooplankton`, and the `_old` pair) are hand-maintained and untouched.
`v2026.08.04` retired six: `calcofi_bird_mammal_census{,_attribute,_sample}` β
`farallon_bird-mammal*`, `ucsd_sio_mesopelagic-fish{,_sample}` β
`sio_mesopelagic-fish*`, and `pic_zooplankton_sample` β
`sio_pic-zooplankton_sample`.
:::
**Requirements.** The DuckDB JDBC driver must be in ERDDAP's `WEB-INF/lib`
(custom image: `CalCOFI/server/erddap/Dockerfile`), and its engine version must be
>= the engine that wrote the views.
```{r}
#| label: cleanup
dbDisconnect(con, shutdown = TRUE)
```
