DIADROMOUS DATABASE

1 EXECUTIVE SUMMARY

The current poor status of several diadromous fish species, most notably the Atlantic salmon and the European eel, highlights the urgent need for effective management and scientifically robust assessment procedures. Achieving this requires high‑quality, coherent and accessible data, as conservation and management decisions can only be as reliable as the information on which they rely. Yet, despite the ecological and socio‑economic importance of these species, the data required to monitor their populations and pressures remain highly fragmented, scattered across institutions, stored in heterogeneous formats, and often maintained in locally developed systems. Such dispersion poses major limitations for transparency, reproducibility, long‑term maintenance, and ultimately for the provision of sound scientific advice. Atlantic salmon and European eel share life cycles that span both continental and marine ecosystems, inside and outside Europe. Although populations behave as relatively independent units in freshwater, they are biologically mixed during their marine phases, which requires coordination at regional and international scales for data collection, assessment, and management. Moreover, these species face multiple anthropogenic impacts, including fisheries, habitat degradation, and fragmentation caused by dams and hydropower plants, further emphasizing the need for robust and harmonized data systems.

In this context, the International Council for the Exploration of the Sea (ICES) plays a central role in providing independent, high‑quality scientific advice for fisheries management, coordinating international expert groups that evaluate stock status and develop methodologies to support evidence‑based decision making. However, current ICES working groups (WGEEL, WGNAS and WGBAST) rely on locally maintained, heterogeneous databases and spreadsheets, an approach that hampers operability, transparency and long‑term sustainability, and is incompatible with the future Transparent Assessment Framework (TAF) aspirations (an ICES methodology and platform developed by to ensure that scientific assessments of fishery resources are fully transparent, reproducible, and auditable). To address these challenges, DIASPARA has developed the DIASPARA Fish Stock Database (StockDB), a new standardized and centralized database designed to support the assessment of diadromous fish species. StockDB consolidates biological data, fisheries information, habitat descriptors, and impact metrics (including dam‑related pressures), integrating datasets from legacy ICES databases.

To do so, our work was carried out in different steps.

• The first was to review the databases and working procedures of three ICES expert groups related to diadromous species were analyzed: WGNAS, WGBAST and WGTRUTTA. These analyses, carried out after contacting and holding meetings with the experts involved in these groups, resulted in three dedicated reports:WGNAS, WGBAST, WGTRUTTA. An analysis of the WGEEL database was not required, as the leaders of this work package have been directly involved in its development and maintenance, and therefore already have an in‑depth understanding of its structure and functioning.

• The second phase consisted of finding a database structure suiting the needs of all expert groups. ICES could only maintain one common structure for all diadromous working group, so we needed to merge all datasets into one database. The main structure of the database was presented and discussed, including potential improvements, during an online DIASPARA meeting : database structure. This structure enables the efficient storage, harmonisation and retrieval of the data needed to run international stock assessment models used by the working groups responsible for producing advice or scientific reports on diadromous species. Its design builds on, and remains consistent with, the architectures previously developed for the WGEEL and WGNAS databases. The objective here was to remain as close to possible to the current working format of the different working groups, so as to be able to continue operating with the minimal changes on scripts and tools.

• The third phase consisted in coding the whole database structure. It included referential tables (a set of standardized and controlled vocabularies) that would be compatible with vocabularies currently used in ICES. This part was done in constant contact with ICES data center to ensure the compatibility with ICES standards. Step by step we also tested that we could import all data into the stock database and ensuring that the resulting structure was fully operational and usable across all working groups, thereby complementing the earlier work of designing the database structure by validating it with real datasets and practical workflows. It’s only when trying to practically import the data that missing elements were found, and that the final database structure was validated.

• At the end of this process, the diadromous fish database stores raw data (biometry, time series of abundance), preprocessed data used by models (e.g. landings at various scales, biomass …), model parameters and outputs.

For ease of use, the report is structured with four main parts.

• The first describes some principle about the database and it’s structure.
• The second creates all the vocabularies (standard set of code alongside with their description used as reference for stage, species). Think of them as “dictionaries” of possible words and that the database would only be able to use those words as standard and not invent new ones.
• The third refers to the Stock and holds values aggregated at the scale of the assessment unit, or stock unit.
• The fourth is the Series and Trait and allows to have time series of data, group and individual life history trait data. This was previously coded in a separate report which has been merged into this main DIADROMOUS DATABASE report at the end of the project for ease of reading.

Additionally, to accommodate both structures, a habitat database was created using a hierarchical structure of the habitat of the migratory fishes (see habitat report). The script to create this report can be found in diaspara github code of the report, the SQL codes are found in the following link SQL code.

All the code (R, SQL), reports (Quarto), and presentations (Quarto) were produced in DIASPARA migDB github repository

The code to this report is available here

The SQL code is here

All presentation, reports produced by WP3 are accessible online at the diaspara website for WP3

2 HIERARCHICAL STRUCTURE OF THE DATABASE

Tables are first created in a schema containing all tables, called ref or dat. From these tables, when necessary, working group specific tables will be created. They inherit from the mother table in the dat or ref schema. Querying a table in the refbast, or refnas schema will only return the data from the WGBAST, or WGNAS working group. But querying a “mother” table in the ref or dat schema will return data from WGNAS or WGBAST. Inheritance is a powerful to aggregate data, but requires caution when building tables, as the foreign keys (format links to other tables) need to be repeated across all inherited tables. A similar output will be obtained in SQL server in the future database by replacing the tables with views or partitioned views.

Code for SQL Server

SQL server used by ICES does not use inheritance, the same structure (schema) can be followed and the mother table will correspond to a view either as a UNION or JOIN of the tables

2.1 Referential tables (vocabularies)

ICES vocabularies refer to the controlled lists of codes, reference terms, and standardized definitions maintained by the International Council for the Exploration of the Sea to ensure consistency and interoperability across all datasets submitted to and managed by ICES. These vocabularies include reference codes for sampling stations, species metadata, and other key data elements used in marine research and assessment. They are hosted on the ICES Vocabulary Server, which acts as the central library linking all codes used in ICES data services and provides tools such as the Code Request application for users needing new reference codes. By enforcing standardized terminology and structured metadata, ICES vocabularies support high‑quality data integration, quality assurance, and harmonization across national and international monitoring programmes.

Within the project, referential tables are created for each data type, and sourced from ICES vocabularies. Checks are made to ensure that the existing vocabularies cover the needs of the working group and the different diadromous species.

The structure might be either a full vocabulary, common to all working groups, or a more complex structure, ensuring that different items are used by different working groups (for instance life stages are very different between salmon and eel and cannot be shared). In the latter case, having working-group-specific tables allows to easily set up foreign key to restrict the values used within each working group.

2.2 Unicity constraints

Another important aspect to consider is the implementation of unique constraints, especially since some fields may contain null values. To preserve data integrity, several levels of unique constraints—supported by indexes—should be defined. These may include combinations such as:

• (year, age, area, parameter)

• (year, age, parameter)

• (year, area, parameter)

• (year, parameter)

This flexibility is necessary because age is used in WGNAS and WGBAST but not in WGEEL, and because WGBAST includes two additional fields not present in other databases: period (e.g., month, half‑year) along with the associated value and the estimation method. Using different structures in the inherited tables allows each working group to manage its specific requirements while still maintaining a largely shared and consistent core schema. Within WGNAS, the presence of area × area matrices means that the area field may appear twice. This situation can be handled by introducing an additional column, needed for both WGNAS and WGBAST. In such cases, it may also be necessary to implement a trigger to enforce uniqueness on combinations such as (year, area, area, parameter) whenever duplicated area fields occur.

2.3 Convention for creating the diaspara database

Along this document, the database will be named DiadromousDB. The database is created with postgresSQL.

Some rules

• By default, values are capitalised for the first letter e.g. Public for the code in dataaccess.

• Codes are capitalized, e.g working group names WGEEL, WGNAS, or country codes.

• Units are lowercase e.g. g, mm …

• Column naming: all integer used for primary keys are called xxx_id all columns containing text used for primary keys are called xxx_code, the column with definition, or description is always called xxx_description where xxx is the three letter code for the table.

• All tables end with a 3 letter summary which allows to identify the source of one column so the table dataaccess will be called tr_dataaccess_dta. And the column for code will be named dta_something

• Referential tables or dictionaries are called tr_(sometable), tables build in conjunction from other tables and not in the dictionaries are called t_(sometable).

• Foreign keys are used instead of check constraints as check constraint might not ensure the integrity of data after their integration (only when new rows are created or modified).

• Foreign keys are name fk_columnname where the column name is the name in the table

• Primary keys are names tablename_pkey (with the constraint possibly refering to more than one column).

• Other constraints are check constraints ck_columnname and unique constraints uk_columnname

• All tables and columns have a definition, we will ask the working groups to check those.

• Use of “snake_case”: Column and table name are ALWAYS LOWERCASE, the underscore is only used to separate type of table and table shortcode t_table_abc. In column is separates table code abc_def_code (table abc will reference the column def_code in table def). Some exceptions to this rule are made when the table was imported straight from ICES

2.4 Code to create the diaspara database

The database can be created an run in localhost, check the wp3_habitat repository for code to set up access to the database. Two roles are created, diaspara_admin and diaspara_read and users are given specific rights.

# dbExecute(con_diaspara_admin, "DROP schema if exists ref CASCADE;");
# dbExecute(con_diaspara_admin, "CREATE schema ref;")
# dbExecute(con_diaspara_admin, "GRANT ALL PRIVILEGES ON SCHEMA ref TO diaspara_admin ;")
# dbExecute(con_diaspara_admin, "GRANT ALL PRIVILEGES ON SCHEMA public TO diaspara_read ;")
# dbExecute(con_diaspara_admin, paste0("GRANT CONNECT ON DATABASE ",cred$dbnamediaspara," TO diaspara_read;"))
# dbExecute(con_diaspara_admin, paste0("ALTER DATABASE ",cred$dbnamediaspara," OWNER TO diaspara_admin;"))
# dbExecute(con_diaspara_admin, "DROP schema if exists refeel CASCADE;");
# dbExecute(con_diaspara_admin, "CREATE SCHEMA refeel;")
# dbExecute(con_diaspara_admin, "ALTER SCHEMA refeel OWNER TO diaspara_admin;")
# dbExecute(con_diaspara_admin, "DROP schema if exists refnas CASCADE;");
# dbExecute(con_diaspara_admin, "CREATE SCHEMA refnas;")
# dbExecute(con_diaspara_admin, "ALTER SCHEMA refnas OWNER TO diaspara_admin;")
# dbExecute(con_diaspara_admin, "DROP schema if exists refbast CASCADE;");
# dbExecute(con_diaspara_admin, "CREATE SCHEMA refbast;")
# dbExecute(con_diaspara_admin, "ALTER SCHEMA refbast OWNER TO diaspara_admin;")
# dbExecute(con_diaspara_admin, "DROP schema if exists reftrutta CASCADE;");
# dbExecute(con_diaspara_admin, "CREATE SCHEMA reftrutta;")
# dbExecute(con_diaspara_admin, "ALTER SCHEMA reftrutta OWNER TO diaspara_admin;")
# 
# # Create foreign data wrapper to wgeel database
# 
# dbExecute(con_diaspara_admin, "CREATE EXTENSION IF NOT EXISTS postgres_fdw;")
# 
# dbExecute(con_diaspara_admin,"
# CREATE SERVER wgeel_data_wrapper
#   FOREIGN DATA WRAPPER postgres_fdw
#   OPTIONS (host 'localhost', port '5432', dbname 'wgeel');")
# dbExecute(con_diaspara_admin,"
# CREATE SERVER wgnas_data_wrapper
#   FOREIGN DATA WRAPPER postgres_fdw
#   OPTIONS (host 'localhost', port '5432', dbname 'salmoglob');")
# dbExecute(con_diaspara_admin,"
# CREATE USER MAPPING FOR USER
#   SERVER wgeel_data_wrapper
#   OPTIONS (user 'postgres', password 'postgres');")
# dbExecute(con_diaspara_admin,"  
# CREATE SCHEMA refwgeel;")
# dbExecute(con_diaspara_admin,"IMPORT FOREIGN SCHEMA ref    
#     FROM SERVER wgeel_data_wrapper
#     INTO refwgeel;")
# 
# dbExecute(con_diaspara_admin, paste0("COMMENT ON DATABASE ",cred$dbnamediaspara," IS 'This database is named Frankenstein :-)'"))    
# dbExecute(con_diaspara_admin,
#           "GRANT ALL PRIVILEGES ON SCHEMA refwgeel TO diaspara_admin;")

SQL code to additional data schema

DROP SCHEMA IF EXISTS dat CASCADE;
CREATE SCHEMA dat;
ALTER SCHEMA dat OWNER TO diaspara_admin;
COMMENT ON SCHEMA dat IS 'SCHEMA common to all migratory fish, filled by inheritance';

DROP SCHEMA IF EXISTS datang CASCADE;
CREATE SCHEMA datang;
ALTER SCHEMA datang OWNER TO diaspara_admin;
COMMENT ON SCHEMA datang IS 'SCHEMA for WGEEL';

DROP SCHEMA IF EXISTS datnas CASCADE;
CREATE SCHEMA datnas;
ALTER SCHEMA datnas OWNER TO diaspara_admin;
COMMENT ON SCHEMA datnas IS 'SCHEMA for WGNAS';

DROP SCHEMA IF EXISTS datbast CASCADE;
CREATE SCHEMA datbast;
ALTER SCHEMA datbast OWNER TO diaspara_admin;
COMMENT ON SCHEMA datbast IS 'SCHEMA for WGBAST';


DROP SCHEMA IF EXISTS dattrutta CASCADE;
CREATE SCHEMA dattrutta;
ALTER SCHEMA dattrutta OWNER TO diaspara_admin;
COMMENT ON SCHEMA dattrutta IS 'SCHEMA for WKTRUTTA';


ALTER TABLE area."ref-countries-2024-01m � CNTR_RG_01M_2024_4326" RENAME TO "ref-countries-2024-01m-4326";

Figure 1: Conceptual schema of the diadromous database

The database has been created with different schemas (Figure 1). The main schema for dictionaries is ref, and a schema is created per working group for specific referential tables. The schema dat is the common schema for all data. For each working group, schema datbast, dateel, datnas are created. The tables are created in dat and later similar or a bit more complex tables (with some more columns) will be created using the INHERIT FROM syntax, which will allow to have a hierarchical structure in the db, and maintain the structure in a table common to all fishes. Note that dat should not containt any data, but will hold all the views and inherited tables coming from the different schema.

3 CREATING REFERENTIALS

This script holds all referentials necessary for both the trait and the stock.

3.1 species (tr_species_spe)

The first thing is to create a referential table for species. At the moment the structure doesn’t integrate schema for Alosa and Lamprey, but the species have been created. There are no codes in ICES vocab for Alosa alosa, Alosa fallax, Petromyzon marinus, Lampetra fluviatilis.

sp <- getCodeList("IC_species")

#The following lines show that there is no code in IC_species 
#grep("Lampetra", sp$description) # nothing
#grep("Petromyzon", sp$description) # nothing
#grep("Alosa",  sp$description) # nothing

bind_rows(
  ele <- getCodeDetail("IC_species","ELE")$detail,
  sal <- getCodeDetail("IC_species","SAL")$detail,
  trs <- getCodeDetail("IC_species","TRS")$detail) |>
  knitr::kable(caption = "Codes for migratory species in ICES, no code found for other species (Lamprey, Alosa ...)") |> kable_styling(bootstrap_options = c("striped", "hover", "condensed"))
if (file.exists("data/tr_species_spe_temp.Rdata")) {
  load("data/tr_species_spe_temp.Rdata") } else {
    species_list <- tibble(
      spe_code = c("127186", "126281", "127187", "126413", "126415", "101174", "101172"),
      spe_icspecieskey = c("SAL", "ELE", "TRS", NA,NA,NA,NA),
      spe_commonname = c("Atlantic salmon", "European eel", "Sea trout", "Twait shad", "Allis shad", "Sea lamprey", "European river lamprey"),
      spe_scientificname = c("Salmo salar", "Anguilla anguilla", "Salmo trutta", "Alosa alosa", "Alosa fallax", "Petromyzon marinus", "Lampetra fluviatilis")
    )
    tr_species_spe_temp <- species_list |>
      rowwise() |>
      mutate(
        spe_codeaphia = findAphia(spe_scientificname, latin = TRUE)
      ) |>
      ungroup()
    save(tr_species_spe_temp, file = "data/tr_species_spe_temp.Rdata")
  }
knitr::kable(tr_species_spe_temp) |> kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 1: species in ICES

(a) Code found in IC_species

Id	Guid	Key	Description	LongDescription	Modified
77905	fea31ebb-fc69-4562-af1b-3fec866e7e58	ELE	Anguilla anguilla		2019-04-15T12:39:51.97
77934	810c0c92-d333-4b16-ab8c-dfe63a7c1a20	SAL	Salmo salar		2019-04-15T13:19:13.723
77943	b0de7924-ee6c-483e-a2e3-91c80ca033c2	TRS	Salmo trutta		2019-04-15T13:23:25.033

(b) Three letter code for species. Should we use ang instead of ele ?

spe_code	spe_commonname	spe_scientificname	spe_codeaphia
SAL	Atlantic salmon	Salmo salar	127186
ELE	European eel	Anguilla anguilla	126281
TRT	Sea trout	Salmo trutta	127187
ALA	Twait shad	Alosa alosa	126413
ALF	Allis shad	Alosa fallax	126415
SLP	Sea lamprey	Petromyzon marinus	101174
RLP	European river lamprey	Lampetra fluviatilis	101172

SQL code to create table tr_species_spe

--DROP TABLE IF EXISTS ref.tr_species_spe;
CREATE TABLE ref.tr_species_spe (
     spe_code CHARACTER VARYING(3) PRIMARY KEY,
     spe_commonnname TEXT,
     spe_scientificname TEXT,
     spe_codeaphia numeric NOT NULL,
     spe_description TEXT);
COMMENT ON TABLE ref.tr_species_spe IS 'Table of species code';
GRANT ALL ON TABLE ref.tr_species_spe to diaspara_admin;
GRANT SELECT ON TABLE ref.tr_species_spe to diaspara_read;

-- 19/12/2025 finally we need TO use AFIAID
-- we also remove TRT as a species 

DELETE FROM "ref".tr_species_spe  WHERE spe_code='TRT';


ALTER TABLE ref.tr_species_spe ALTER column spe_code type TEXT;


ALTER TABLE "ref"."tr_lifestage_lfs" ALTER COLUMN lfs_spe_code TYPE TEXT;

-- 19/12/2025 finally we need TO use AFIAID
-- we also remove TRT as a species 

ALTER TABLE dat.t_metadata_met ALTER COLUMN met_spe_code TYPE TEXT;

ALTER TABLE dat.t_stock_sto ALTER COLUMN sto_spe_code TYPE TEXT;

 ALTER TABLE datnas.t_metadata_met DROP
    CONSTRAINT ck_met_spe_code;
 
 ALTER TABLE datnas.t_stock_sto DROP CONSTRAINT 
ck_spe_code;

ALTER TABLE datbast.t_metadata_met DROP CONSTRAINT ck_met_spe_code;

ALTER TABLE dateel.t_metadata_met DROP CONSTRAINT ck_met_spe_code;

ALTER TABLE datnas.t_stock_sto DROP CONSTRAINT ck_spe_code;

ALTER TABLE dateel.t_stock_sto DROP CONSTRAINT ck_spe_code;

ALTER TABLE datbast.t_stock_sto DROP CONSTRAINT ck_spe_code;

UPDATE ref.tr_species_spe SET spe_code = spe_codeaphia; 


ALTER TABLE datnas.t_metadata_met ADD
    CONSTRAINT ck_met_spe_code   
    CHECK (met_spe_code='127186'); 

ALTER TABLE datnas.t_stock_sto ADD CONSTRAINT 
ck_sto_spe_code CHECK (sto_spe_code='127186');
  
ALTER TABLE datbast.t_metadata_met 
ADD CONSTRAINT ck_met_spe_code CHECK 
 (met_spe_code='127186' OR met_spe_code='127187');
 
 ALTER TABLE datbast.t_stock_sto ADD CONSTRAINT 
ck_sto_spe_code CHECK  (sto_spe_code='127186' OR sto_spe_code='127187');
 
ALTER TABLE dateel.t_metadata_met 
ADD CONSTRAINT ck_met_spe_code CHECK (met_spe_code='126281');

ALTER TABLE datbast.t_stock_sto 
ADD CONSTRAINT ck_spe_code 
CHECK (sto_spe_code='127186' OR sto_spe_code='127187');
 
COMMENT ON COLUMN datbast.t_metadata_met.met_spe_code 
IS 'Species aphiaID, text ''127186'' salmo salar OR ''127187'' for Salmo trutta primary key on both met_spe_code and met_var.';

COMMENT ON COLUMN dateel.t_metadata_met.met_spe_code 
IS 'Species, ''126281'' primary key on both met_spe_code and met_var.';

ALTER TABLE dateel.t_stock_sto ALTER COLUMN sto_spe_code SET DEFAULT '126281';
ALTER TABLE datnas.t_stock_sto ALTER COLUMN sto_spe_code SET DEFAULT '127186';
-- no default for datbast

COMMENT ON TABLE  ref.tr_species_spe IS 
'Table of fish species, spe_code using AphiaID as the reference with 
reference to ICES vocabularies.'

dbWriteTable(conn=con_diaspara, name = "tr_species_spe_temp", value = tr_species_spe_temp, overwrite = TRUE)
dbExecute(con_diaspara,"INSERT INTO ref.tr_species_spe SELECT * FROM tr_species_spe_temp")#7
dbExecute(con_diaspara,"DROP TABLE tr_species_spe_temp")
dbExecute(con_diaspara_admin, "COMMENT ON TABLE  ref.tr_species_spe IS 
'Table of fish species, spe_code using AphiaID as the reference with 
reference to ICES vocabularies.'")

Table 2: Working groups table in the diaspara DB

spe_code	spe_commonnname	spe_scientificname	spe_codeaphia	spe_description
127186	Atlantic salmon	Salmo salar	127186	NA
126413	Twait shad	Alosa alosa	126413	NA
126415	Allis shad	Alosa fallax	126415	NA
101174	Sea lamprey	Petromyzon marinus	101174	NA
101172	European river lamprey	Lampetra fluviatilis	101172	NA
126281	European eel	Anguilla anguilla	126281	NA
127187	Sea trout	Salmo trutta	127187	NA

Figure 2: diagram of tr_species_spe

3.2 Working group (tr_icworkinggroup_wkg)

Species is necessary to separate data within the same working group (WGBAST works on both Trout and Salmon). Furthermore, two working groups might be working on the same species. For this reason we need to have a “working group” entry in most of the tables. There is already a table for working group. The proposed table is Table 3.

NoteNote

WGEEL name needs to be updated to JOINT EIFAAC/ICES/GFCM WORKING GROUP ON EEL and WGTRUTTA is an expert group, and, therefore, needs to be added to the lit separately.

The working groups might change over time, referencing a working group there is probably not the best. > Added stockkeylabel, this table is necessary to aggregate data, as species is not enough.

SQL code to create table tr_icworkinggroup_wkg

--DROP TABLE IF EXISTS ref.tr_icworkinggroup_wkg CASCADE;

CREATE TABLE ref.tr_icworkinggroup_wkg (
wkg_code TEXT PRIMARY KEY,
wkg_description TEXT,
wkg_icesguid uuid,
wkg_stockkeylabel TEXT
);

COMMENT ON TABLE ref.tr_icworkinggroup_wkg 
IS 'Table corresponding to the IC_WorkingGroup referential;';
COMMENT ON COLUMN ref.tr_icworkinggroup_wkg.wkg_code IS 
'Working group code uppercase, WGEEL, WGNAS, WGBAST, WGTRUTTA';


GRANT ALL ON ref.tr_icworkinggroup_wkg TO diaspara_admin;
GRANT SELECT ON ref.tr_icworkinggroup_wkg TO diaspara_read;

# Using the jsonlite to download the guid also
tbl <- jsonlite::fromJSON(
  "https://vocab.ices.dk/services/api/Code/3f6fb38a-a3c5-4f5c-bf31-2045e12536ee")



temp_tr_icworkinggroup_wkg <- tbl |>
  select(key,description,guid) |>
  rename(wkg_code = key,
         wkg_description = description,
         wkg_icesguid = guid)|>
  filter(wkg_code %in% c("WGEEL", "WGNAS", "WGBAST"))
temp_tr_icworkinggroup_wkg <- bind_rows(temp_tr_icworkinggroup_wkg,
                                        data.frame(wkg_code="WKTRUTTA"))
temp_tr_icworkinggroup_wkg$wkg_stockkeylabel <-
  c("sal.27.22–31","ele.2737.nea","sal.neac.all",NA)
dbWriteTable(con_diaspara_admin, 
             "temp_tr_icworkinggroup_wkg", 
             temp_tr_icworkinggroup_wkg,
             overwrite = TRUE)

dbExecute(con_diaspara_admin, "INSERT INTO ref.tr_icworkinggroup_wkg 
          SELECT 
          wkg_code,
          wkg_description,
          wkg_icesguid::uuid,
          WKG_stockkeylabel
         FROM temp_tr_icworkinggroup_wkg;") #4
dbExecute(con_diaspara_admin, "DROP TABLE temp_tr_icworkinggroup_wkg;")

Table 3: Working groups table in the diaspara DB

wkg_code	wkg_description	wkg_icesguid
WGBAST	Baltic Salmon and Trout Assessment Working Group	2cac261d-c837-459a-961b-e63e36cc19ec
WGEEL	Joint EIFAAC/ICES Working Group on Eels	7c13a79e-7855-4d0b-b567-21300bcaaf9a
WGNAS	Working Group in North Atlantic Salmon	b5fd158e-b153-4e2e-a6da-c4b0536d684e
WKTRUTTA	NA	NA

Figure 3: diagram of tr_icworkinggroup_wkg, arrows indicate inheritance

3.3 Country (tr_country_cou)

Countries are taken from the WGEEL database, and streamlined with ICES to further integrate North American countries. The shapefiles have been downloaded from https://gisco-services.ec.europa.eu/distribution/v2/countries/download/#countries source EuroGeographics and UN-FAO. Countries (Table 4) are ordered from North to South starting from the Baltic and ending in the Mediterranean, with American number being the highest in order.

SQL code to create table tr_country_cou

DROP TABLE IF EXISTS ref.tr_country_cou;
CREATE TABLE ref.tr_country_cou (
    cou_code character varying(2) NOT NULL,
    cou_country text NOT NULL,
    cou_order integer NOT NULL,
    geom public.geometry,
    cou_iso3code character varying(3)
);
COMMENT ON TABLE ref.tr_country_cou IS
          'Table of country codes source EuroGeographics and UN-FAO.';
ALTER TABLE ref.tr_country_cou 
          OWNER TO diaspara_admin;
GRANT SELECT ON TABLE ref.tr_country_cou TO diaspara_read;

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_country_cou 
          SELECT * FROM refwgeel.tr_country_cou;") #40
# Add some constraints
dbExecute(con_diaspara_admin, "ALTER TABLE ref.tr_country_cou 
          ADD CONSTRAINT t_country_cou_pkey PRIMARY KEY (cou_code);")
dbExecute(con_diaspara_admin, "ALTER TABLE ref.tr_country_cou 
          ADD CONSTRAINT uk_cou_iso3code UNIQUE (cou_iso3code);")

# missing values from America downloaded from https://gisco-services.ec.europa.eu/distribution/v2/nuts/download/ref-nuts-2024-01m.gdb.zip
# uploaded to postgres

# the tables

# ref-countries-2024-01m — CNTR_RG_01M_2024_4326
# have been copied to folder area ref-countries was renamed
# ALTER TABLE area."ref-countries-2024-01m — CNTR_RG_01M_2024_4326" 
# RENAME TO "ref-countries-2024-01m-4326";


dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_country_cou ( cou_code,
    cou_country,    
    cou_iso3code,
    geom, 
    cou_order)
SELECT \"CNTR_ID\" AS cou_code, \"NAME_ENGL\" AS cou_country,  \"ISO3_CODE\" 
AS cou_isocode, geom,
CASE WHEN \"CNTR_ID\" = 'GL' THEN 47
     WHEN \"CNTR_ID\" = 'CA' THEN 48
     ELSE 49 END AS cou_order
FROM  area.\"ref-countries-2024-01m-4326\"
WHERE \"CNTR_ID\" IN ('GL', 'CA', 'US');") #3

# Svalbard et Jan Mayen SJM NO Territory    
dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_country_cou ( cou_code,
    cou_country,    
    cou_iso3code,
    geom, 
    cou_order)
SELECT \"CNTR_ID\" AS cou_code, \"NAME_ENGL\" AS cou_country,  \"ISO3_CODE\" 
AS cou_isocode, geom,
CASE WHEN \"CNTR_ID\" = 'GL' THEN 47
     WHEN \"CNTR_ID\" = 'CA' THEN 48
     ELSE 49 END AS cou_order
FROM  area.\"ref-countries-2024-01m-4326\"
WHERE \"CNTR_ID\" IN ('SJ');") #3

dbExecute(con_diaspara_admin,
          "UPDATE ref.tr_country_cou 
SET geom = nuts.geom  
FROM  area.\"ref-countries-2024-01m-4326\" nuts 
WHERE nuts.\"CNTR_ID\" = tr_country_cou.cou_code;") # 40

tr_country_cou <- dbGetQuery(con_diaspara, "SELECT cou_code,cou_country,cou_order, cou_iso3code FROM ref.tr_country_cou order by cou_order")
knitr::kable(tr_country_cou) |> kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 4: Country table in the diaspara DB

cou_code	cou_country	cou_order	cou_iso3code
IS	Iceland	0	ISL
NO	Norway	1	NOR
SE	Sweden	2	SWE
AX	Åland	3	ALA
FI	Finland	4	FIN
EE	Estonia	5	EST
LV	Latvia	6	LVA
LT	Lithuania	7	LTU
RU	Russia	8	RUS
PL	Poland	9	POL
CZ	Czech republic	10	CZE
DE	Germany	11	DEU
DK	Denmark	12	DNK
NL	Netherlands	13	NLD
BE	Belgium	14	BEL
LU	Luxembourg	15	LUX
IE	Ireland	16	IRL
GB	Great Britain	17	GBR
FR	France	18	FRA
ES	Spain	19	ESP
PT	Portugal	20	PRT
IT	Italy	21	ITA
MT	Malta	22	MLT
SI	Slovenia	23	SVN
HR	Croatia	24	HRV
BA	Bosnia-Herzegovina	25	BIH
ME	Montenegro	26	MNE
AL	Albania	27	ALB
GR	Greece	28	GRC
TR	Turkey	34	TUR
CY	Cyprus	35	CYP
SY	Syria	36	SYR
LB	Lebanon	37	LBN
IL	Israel	38	ISR
EG	Egypt	39	EGY
LY	Libya	40	LBY
TN	Tunisia	41	TUN
DZ	Algeria	42	DZA
MA	Morocco	43	MAR
VA	Vattican	46	VAT
GL	Greenland	47	GRL
CA	Canada	48	CAN
SJ	Svalbard and Jan Mayen	49	SJM
US	United States	49	USA

if (file.exists("data/country_sf.Rdata")) load("data/country_sf.Rdata") else {
  country_sf <- sf::st_read(con_diaspara,
                            query = "SELECT cou_code, ST_MakeValid(geom) 
                          from ref.tr_country_cou") |>
    sf::st_transform(4326) 
  save(country_sf, file="data/country_sf.Rdata")
}
#see here : https://stackoverflow.com/questions/70756215/
#plot-geodata-on-the-globe-perspective-in-r
# Note there is a problem of geometry for some of the polygons, and this require 
# ST_Makevalid before intersection

# projection string used for the polygons & ocean background
crs_string <- "+proj=ortho +lon_0=-30 +lat_0=30"

# background for the globe - center buffered by earth radius
ocean <- sf::st_point(x = c(0,0)) |>
  sf::st_buffer(dist = 6371000) |>
  sf::st_sfc(crs = crs_string)
country_sf2 <-  country_sf |> 
  sf::st_intersection(ocean |> sf::st_transform(4326)) |> 
  # select visible area only
  sf::st_transform(crs = crs_string) # reproject to ortho
# now the action!
g <- ggplot(data = country_sf2) +
  geom_sf(data = ocean, fill = "aliceblue", color = NA) + # background first
  geom_sf(aes(fill = cou_code), lwd = .1) + # now land over the oceans
  scale_fill_discrete(guide = "none") +
  theme_void()

# this part is used to avoid long computations
png(filename="images/fig-country.png", bg="transparent")
print(g)
dev.off()

Figure 4: Map of countries in the diaspara DB © EuroGeographics

3.4 Unit (tr_units_uni)

SQL code to create tables

--DROP TABLE IF EXISTS ref.tr_units_uni CASCADE;

CREATE TABLE ref.tr_units_uni (
  uni_code varchar(20) NOT NULL,
  uni_description text NOT NULL,
  uni_icesvalue character varying(4),  
  uni_icesguid uuid,
  uni_icestablesource text,
  CONSTRAINT t_units_uni_pkey PRIMARY KEY (uni_code),
  CONSTRAINT uk_uni_description UNIQUE (uni_description),
  CONSTRAINT uk_uni_icesguid UNIQUE (uni_icesguid),
  CONSTRAINT uk_uni_icesvalue UNIQUE (uni_icesvalue)
);
GRANT ALL ON ref.tr_units_uni TO diaspara_admin;
GRANT SELECT ON ref.tr_units_uni TO diaspara_read; 
-- I don't add definitions this is an ICES vocab

Creating the unit from wgeel and checking ICES code

To create the units table, we started from the existing WGEEL table, and we later added some elements for WGBAST. In addition, to standardize our table, all codes where checked againt several existing ICES vocabularies. We used the p06 as the most common source. The source code in ICES table, its id, and the source table are presented in the table.

dbExecute(con_diaspara_admin,"INSERT INTO ref.tr_units_uni (
uni_code, uni_description)
SELECT * FROM refwgeel.tr_units_uni;")#25

dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set uni_icesvalue='KGXX' 
          where uni_code = 'kg';") 
dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set uni_icesvalue='MTON'
          where uni_code = 't';") 
dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set uni_icesvalue='UCNT' 
          where uni_code = 'nr';") 
dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set uni_icesvalue='UGRM' 
          where uni_code = 'g';")
dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set uni_icesvalue='UPMS'
          where uni_code = 'nr/m2';")
dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set uni_icesvalue='UPMM' 
          where uni_code = 'nr/m3';")
dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set uni_icesvalue='UYRS' 
          where uni_code = 'nr year';")
dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set uni_icesvalue='UXMM' 
          where uni_code = 'mm';")
dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set uni_icesvalue='NGPG' 
          where uni_code = 'ng/g';")
dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set uni_icesvalue='HCTR' 
          where uni_code = 'ha';")
dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set uni_icesvalue='UTAA' 
          where uni_code = 'nr day';")
dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set uni_icesvalue='NOPH'
          where uni_code = 'nr/h';")
dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set uni_icesvalue='NGPG'
          where uni_code = 'ng/g';")
dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set uni_icesvalue='UPCT'
          where uni_code = 'percent';")
dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set 
          (uni_icesvalue, uni_description)=
          ('XXXX', 'Not applicable (without unit)')
          where uni_code = 'wo';")          

dbExecute(con_diaspara_admin, "INSERT INTO ref.tr_units_uni 
          VALUES ('year-1', 'Per year', 'XXPY');")          
dbExecute(con_diaspara_admin, "INSERT INTO ref.tr_units_uni 
          VALUES ('s', 'Seconds', 'UTBB');")



p06 <- icesVocab::getCodeList('p06')
SamplingUnit <- icesVocab::getCodeList('SamplingUnit')
MUNIT <- icesVocab::getCodeList('MUNIT')
uni <- dbGetQuery(con_diaspara_admin, "SELECT * FROM ref.tr_units_uni;")
tempuni <- inner_join(uni, p06, by=join_by(uni_icesvalue==Key))
dbWriteTable(con_diaspara_admin, "tempuni", tempuni, overwrite=TRUE)
dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni 
set uni_icesguid = \"GUID\"::uuid
          FROM tempuni 
          where tempuni.uni_icesvalue=tr_units_uni.uni_icesvalue;") #16
dbExecute(con_diaspara_admin, "DROP TABLE tempuni;")

dbExecute(con_diaspara_admin,
          "UPDATE ref.tr_units_uni set uni_icestablesource = 'p06' where uni_icesvalue 
IS NOT NULL AND
uni_icestablesource IS NULL;") # 16

query <- sprintf("INSERT INTO ref.tr_units_uni (uni_code,uni_description, uni_icesvalue, uni_icestablesource,uni_icesguid) VALUES ('%s','%s','%s','%s','%s'::uuid);", 
                 "gd", 
                 "Gear days for fyke/trap nets",
                 "gd", 
                 "MUNIT",
                 "bf0570b7-45f2-41c7-9a46-de912a2b9ad4")              
dbExecute(con_diaspara_admin,  query)


dbExecute(con_diaspara_admin, "UPDATE ref.tr_units_uni set uni_icesvalue='idx', 
          uni_icestablesource = 'MUNIT',
          uni_icesguid ='87a9cf7f-fff4-4712-b693-76eec1403254'::uuid
          where uni_code = 'index';")

# p06[grep('Ton',p06$Description),c("Description","Key")] 
# p06[grep('Without',tolower(p06$Description)),c("Description","Key")] 
# p06[grep('nanogram',tolower(p06$Description)),c("Description","Key")]
# p06[grep('index',tolower(p06$Description)),c("Description","Key")]
# p06[grep('hour',tolower(p06$Description)),c("Description","Key")]
# p06[grep('kilogram',tolower(p06$Description)),c("Description","Key")]
# p06[grep('nanogram',tolower(p06$Description)),c("Description","Key")]
# p06[grep('haul',tolower(p06$Description)),c("Description","Key")]

dbExecute(con_diaspara_admin, "COMMENT ON TABLE ref.tr_units_uni IS 
'Table of units, values from tables MUNIT and p06 have corresponding ICES code.'")
dbExecute(con_diaspara_admin, "COMMENT ON COLUMN ref.tr_units_uni.uni_code IS 
'Unit code, lowercase, nr number, otherwise standard units.'")
dbExecute(con_diaspara_admin, "COMMENT ON COLUMN ref.tr_units_uni.uni_description
 IS 'Unit code, lowercase, nr number, otherwise standard units.'")
dbExecute(con_diaspara_admin, "COMMENT ON COLUMN ref.tr_units_uni.uni_icesvalue IS 
'ICES code standard from the British Oceanographic Data Centre (p06) or MUNIT 
table.';") 
dbExecute(con_diaspara_admin, 
          "COMMENT ON COLUMN ref.tr_units_uni.uni_icestablesource IS 
'Table source in ICES.';") 
dbExecute(con_diaspara_admin, 
          "COMMENT ON COLUMN ref.tr_units_uni.uni_icesguid IS 
'GUID, type https://vocab.ices.dk/?codetypeguid=<guidcode> to get access to the 
vocab in ICES.';") 
dbExecute(con_diaspara_admin, "GRANT ALL ON TABLE ref.tr_units_uni 
          to diaspara_admin;")
dbExecute(con_diaspara_admin, "GRANT SELECT ON TABLE ref.tr_units_uni 
          to diaspara_read;")
#for WGBAST
#
query <- sprintf("INSERT INTO ref.tr_units_uni (uni_code,uni_description, uni_icesvalue, uni_icestablesource,uni_icesguid) VALUES ('%s','%s','%s','%s','%s'::uuid);", 
                 "nd", 
                 "Net-days (fisheries)",
                 "nd", 
                 "MUNIT",
                 "f2783f1c-defa-4551-a9e3-1cfa173a0b9f")              
dbExecute(con_diaspara_admin,  query)

dbGetQuery(con_diaspara, "SELECT * FROM ref.tr_units_uni;")|> 
knitr::kable() |>
 kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 5: tr_units_uni table, check missing values currently not found in ICES Vocab

uni_code	uni_description	uni_icesvalue	uni_icesguid	uni_icestablesource
kg/d	kilogramme per day	NA	NA	NA
kg/boat/d	kilogramme per boat per day	NA	NA	NA
nr haul	number of haul	NA	NA	NA
nr electrofishing	number of electrofishing campain in the year to collect the recruitment index	NA	NA	NA
nr/haul	number per haul	NA	NA	NA
kg/ha	weight in kilogrammes per surface in hectare	NA	NA	NA
nr net.night	number of net and night	NA	NA	NA
nr fyke.day	number of fyke and day	NA	NA	NA
nr site	number of site	NA	NA	NA
nr/net/day	number per net and day	NA	NA	NA
kg	weight in kilogrammes	KGXX	c1361a50-86e2-412f-b41b-5f1a006fccb1	p06
t	weight in tonnes	MTON	4fc26258-9a01-48f9-8b17-3e20f7a38faa	p06
nr	number	UCNT	24eb2d51-8ee3-4e2e-a054-54d18db2d161	p06
g	gram	UGRM	f0f3c481-168b-4426-b63f-92614d325782	p06
nr/m2	number per square meter	UPMS	d482b028-598d-4266-a9df-81f565297734	p06
nr/m3	number per cubic meter	UPMM	a3314231-4d85-47c8-9615-20f7283f0389	p06
nr year	number of years	UYRS	b5da453a-51f3-46bb-bff2-0ae5a7a0db42	p06
mm	milimeter	UXMM	85d197e0-324c-4343-8314-2c934eca5315	p06
ha	Surface	HCTR	4d1a5474-67a8-46fe-98f9-82c405e025de	p06
nr day	number of days	UTAA	5571062f-f40f-49d9-8b13-8cdd8f9ed6d1	p06
nr/h	number per hour	NOPH	f9095fc1-bfee-40df-b289-e2850000dd26	p06
ng/g	nanogram per gram	NGPG	5ffd54a7-bc8d-4d00-9e60-e1554d57bd89	p06
percent	percentage	UPCT	355bd386-001b-40e8-bb7e-8515b7b737f4	p06
wo	Not applicable (without unit)	XXXX	896e5f8e-f467-49b5-97f3-f6df093a5293	p06
year-1	Per year	XXPY	a69c5e6b-789d-411c-86b3-70c3b89e8884	p06
s	Seconds	UTBB	9bc370c3-ff97-4dde-9e5c-3eba6074d224	p06
gd	Gear days for fyke/trap nets	gd	bf0570b7-45f2-41c7-9a46-de912a2b9ad4	MUNIT
index	calculated value following a specified protocol	idx	87a9cf7f-fff4-4712-b693-76eec1403254	MUNIT
nd	Net-days (fisheries)	nd	f2783f1c-defa-4551-a9e3-1cfa173a0b9f	MUNIT

ICES is modifying the code

Work is currently in progress to integrate the codes for unit in ICES. An issue is open and discussed in the ICES git Data Information Group. WGEEL: New MUNIT codes #737

Figure 5: diagram of tr_units_uni, arrows indicate inheritance

3.5 Parameters

We need to store input or output parameters of the stock assessment models, these are often multidimensional arrays. To do so we use variables names that store variables, reduced to their lower level (e.g. 3 dimensional arrays with dimensions [area, year, stage] will be translated as many lines with the corresponding values in columns area, year, and stage), and the identifier of the variable will be used for all the lines necessary to store this dataset. When arrays have only two dimensions, then only two columns are used.

The values in some columns can also be left empty if it does not correspond to the actual dimension of the data, for instance some arrays have nothing to do with age, and in that case there is nothing to store. The parameters will be described by their metadata as illustrated in Figure 6

Figure 6: Mind map of the metadata structure

We can have a look at the metadata in the analysis done on the WGNAS database “Salmoglob” WGNAS description.

In Salmoglob, we had a problem with some columns holding data corresponding to different data types . For instance a column could hold either year, or age. Another issue was that some arrays integrated in the database have twice the same column reported (e.g. a matrix holding transition from an area to another area. This was solved by using an additional column where data can be of several types (see tg_additional_add in Section 4.1.5). The description could be used within a type array. But as SQL server does not work with arrays, and the SQL database will be stored in ICES, we chose not to use those arrays.

Checking stock codes using icesASD and icesSD packages

Table 6: Access to the advice using icesAdvice

# install.packages('icesAdvice', repos = c('https://ices-tools-prod.r-universe.dev', 'https://cloud.r-project.org'))
# install.packages("icesSD", repos = c("https://ices-tools-prod.r-universe.dev", "https://cloud.r-project.org"))

library('icesAdvice')
library('icesSAG')
library('icesSD')
# this does not give the 
advice <- getAdviceViewRecord()
advice[grepl('ele',advice$stockCode),
       c('adviceDOI', 'stockCode','assessmentyear')] |> kable
sd <- mapply(getSD, year= 2020:2024, SIMPLIFY=FALSE)
sd <- do.call(rbind,sd)
ww <- grepl('ele',sd$StockKeyLabel) | grepl('Salmo',sd$speciesScientificName)
sd[ww,] |> kable()

3.6 Object dimension (tr_objectdimension_odi)

Parameters can have different dimensions :

• single value (scalar)
• vector (1D)
• matrix (2D)
• arrays (3D)

These dimensions are specified in table Table 7, and later in this report used in metadata.

SQL code to create tables

DROP TABLE IF EXISTS ref.tr_objectdimension_odi CASCADE;
CREATE TABLE ref.tr_objectdimension_odi (
odi_code TEXT PRIMARY KEY,
odi_description TEXT
);

INSERT INTO ref.tr_objectdimension_odi VALUES ('Single_value', 'Single value');
INSERT INTO ref.tr_objectdimension_odi VALUES ('Vector', 'One dimension vector');
INSERT INTO ref.tr_objectdimension_odi VALUES ('Matrix', 'Two dimensions matrix');
INSERT INTO ref.tr_objectdimension_odi VALUES ('Array', 'Three dimensions array');

COMMENT ON TABLE ref.tr_objectdimension_odi IS 
'Table indicating the dimensions of the object stored in the model, 
single value, vector, matrix, array';

COMMENT ON COLUMN ref.tr_objectdimension_odi.odi_code IS 
'code of the object dimension, single_value, vector, ...';

COMMENT ON COLUMN ref.tr_objectdimension_odi.odi_code IS 'description of the object type';
GRANT ALL ON ref.tr_objectdimension_odi TO diaspara_admin;
GRANT SELECT ON ref.tr_objectdimension_odi TO diaspara_read;

/* fix from Hilaire's review


ALTER TABLE "ref".tr_objecttype_oty RENAME TO tr_objectdimension_odi;
ALTER TABLE "ref".tr_objectdimension_odi RENAME COLUMN oty_code TO odi_code;
ALTER TABLE "ref".tr_objectdimension_odi RENAME COLUMN oty_description TO odi_description;
ALTER TABLE "ref".tr_objectdimension_odi RENAME CONSTRAINT tr_objecttype_oty_pkey TO tr_objectdimension_odi_pkey;
ALTER TABLE dat.t_metadata_met RENAME COLUMN met_oty_code TO met_odi_code;
ALTER TABLE dat.t_metadata_met RENAME CONSTRAINT fk_met_oty_code TO fk_met_odi_code;
 */

Table 7: Object type

odi_code	odi_description
Single_value	Single value
Vector	One dimension vector
Matrix	Two dimensions matrix
Array	Three dimensions array

3.7 Type of parm / data (tr_bayestype_bty)

The stock model use parameters as inputs and produce parameter outputs. Some paramaters are constants, some are considered as data entry, some are outputs, and some others are not necessarily used in the model itself but necessary for comparision or graphical purpose (e.g. Conservation Limits). In the salmoglob metadata (WGNAS), two tables hold similar information status and nimble. These entries have been grouped in the tr_bayestype_bty table.

SQL code to create tables

--nimble

DROP TABLE IF EXISTS ref.tr_bayestype_bty CASCADE;
CREATE TABLE ref.tr_bayestype_bty (
nim_code TEXT PRIMARY KEY,
nim_description TEXT
);

COMMENT ON TABLE ref.tr_bayestype_bty IS 
'Indicate the type of data, parameter constant, parameter estimate, output, other ...';
-- Note this is a mix of nimble and status, which mean the same....

INSERT INTO ref.tr_bayestype_bty VALUES ('Data', 'Data entry to the model');
INSERT INTO ref.tr_bayestype_bty 
VALUES ('Parameter constant', 'Parameter input to the model');
INSERT INTO ref.tr_bayestype_bty 
VALUES ('Parameter estimate', 'Parameter input to the model');
INSERT INTO ref.tr_bayestype_bty 
VALUES ('Output', 'Output from the model, derived quantity');
-- Do we want another type here ?
--INSERT INTO ref.tr_bayestype_bty VALUES ('observation', 'Observation not used in the model');
INSERT INTO ref.tr_bayestype_bty 
VALUES ('Other', 'Applies currently to conservation limits');
GRANT ALL ON ref.tr_bayestype_bty TO diaspara_admin;
GRANT SELECT ON ref.tr_bayestype_bty TO diaspara_read;


/*
Hilaire review : remove "nimble" ...
ALTER TABLE "ref".tr_nimble_nim RENAME TO tr_bayestype_bty
ALTER TABLE "ref".tr_nimble_nim RENAME COLUMN nim_code TO bty_code;
ALTER TABLE "ref".tr_nimble_nim RENAME COLUMN nim_description TO bty_description;
ALTER TABLE "ref".tr_nimble_nim RENAME CONSTRAINT tr_nimble_nim_pkey TO tr_bayestype_bty_pkey;
ALTER INDEX "ref".tr_nimble_nim_pkey RENAME TO tr_bayestype_bty_pkey;
ALTER TABLE dat.t_metadata_met RENAME COLUMN met_nim_code TO met_bty_code;
ALTER TABLE dat.t_metadata_met RENAME CONSTRAINT fk_met_nim_code TO fk_met_bty_code;
ALTER TABLE dateel.t_metadata_met RENAME CONSTRAINT fk_met_nim_code TO fk_met_bty_code;
ALTER TABLE datnas.t_metadata_met RENAME CONSTRAINT fk_met_nim_code TO fk_met_bty_code;
ALTER TABLE datbast.t_metadata_met RENAME CONSTRAINT fk_met_nim_code TO fk_met_bty_code;
*/

Table 8: Nimble

bty_code	bty_description
Data	Data entry to the model
Parameter constant	Parameter input to the model
Parameter estimate	Parameter input to the model
Output	Output from the model, derived quantity
Other	Applies currently to conservation limits

3.8 Version (tr_version_ver)

Currently in the salmoglob information about version only correspond to different versions of the same parameter see WGNAS analysis report. The database only contains the variables used to run the salmoglob model, at the time of the working group, any updated value is copied to the database_archive which then contains historical values. From this, it might be possible to rebuild historical states of the database but it is not straightforward, as the archive database can hold, for the same year, multiple values of the same variable, if the corrections or updates were made several times. The dates (column date_time) used in the database and database_archive give information about the latest update. An analysis with Pierre Yves Hernwann on unique values for tupples [version, year, type, age, area, location, metric, var_mod] shows that some duplicates are present in the archive database in 2021 and 2022, so by using the latest date in the year it is possible to reproduce the state of the database at the moment of the working group. Still it was agreed that a clear versioning would ease up the work (as in WGEEL). It was also agreed that metadata should also contain information about historical variables For instance we create a new variable, so we know when it was introduced. So the version column was added to we added to both metadata (t_metadata_met) table and stock table (t_stock_sto). Some variables might get deprecated over time.

TODO ICES

The DB will be held in ICES server. We will need a procedure to save the database at each working group to be able to run past versions of the model. This means keeping a database_archive for each working groups. It’s a straightforward copy of the t_stock_sto for each working group.

SQL code to create tables

-- It seems to me that metadata should contain information about historical 
-- variables, so I'm moving this from the main table and adding to metadata
-- some variables might get deprecated in time. 
-- Unless they get a new version this might not change
-- I have removed reference to stockkey as there are several stock keys
-- for the work of WGNAS
DROP TABLE IF EXISTS ref.tr_version_ver CASCADE;
CREATE TABLE ref.tr_version_ver(
ver_code TEXT PRIMARY KEY,
ver_year INTEGER NOT NULL,
ver_spe_code CHARACTER VARYING(3),
/*CONSTRAINT fk_ver_spe_code FOREIGN KEY (ver_spe_code) 
REFERENCES ref.tr_species_spe(spe_code)
ON UPDATE CASCADE ON DELETE RESTRICT,*/
ver_wkg_code TEXT NOT NULL,
CONSTRAINT fk_ver_wkg_code  FOREIGN KEY (ver_wkg_code)
REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
ON UPDATE CASCADE ON DELETE RESTRICT,
--ver_stockkey INTEGER NOT NULL, 
ver_stockkeylabel TEXT,
---ver_stockadvicedoi TEXT NOT NULL,
ver_datacalldoi TEXT NULL,
ver_version INTEGER NOT NULL,
ver_description TEXT
);
COMMENT ON TABLE ref.tr_version_ver
IS 'Table of data or variable version, essentially one datacall or advice.';
COMMENT ON COLUMN ref.tr_version_ver.ver_code 
IS 'Version code, stockkey-year-version.';
COMMENT ON COLUMN ref.tr_version_ver.ver_year 
IS 'Year of assessement.';
COMMENT ON COLUMN ref.tr_version_ver.ver_spe_code 
IS 'Species code e.g. ''Anguilla anguilla'' or ''Salmo salar,Salmo trutta'' the reference name should be in tr_species_spe, comma separated';
COMMENT ON COLUMN ref.tr_version_ver.ver_wkg_code 
IS 'Code of the working group, one of WGBAST, WGEEL, WGNAS, WKTRUTTA';
--COMMENT ON COLUMN ref.tr_version_ver.ver_stockkey 
--IS 'Stockkey (integer) from the stock database.';
COMMENT ON COLUMN ref.tr_version_ver.ver_stockkeylabel 
IS 'Ver_stockkeylabel e.g. ele.2737.nea.';
--COMMENT ON COLUMN ref.tr_version_ver.ver_stockadvicedoi 
--IS 'Advice DOI corresponding to column adviceDOI 
--when using icesASD::getAdviceViewRecord().';
COMMENT ON COLUMN ref.tr_version_ver.ver_datacalldoi 
IS 'Data call DOI, find a way to retreive that information 
and update this comment';
COMMENT ON COLUMN ref.tr_version_ver.ver_version 
IS 'Version code in original database, eg 2,4 for wgnas, dc_2020 for wgeel.';
COMMENT ON COLUMN ref.tr_version_ver.ver_description 
IS 'Description of the data call / version.';

GRANT ALL ON ref.tr_version_ver TO diaspara_admin;
GRANT SELECT ON ref.tr_version_ver TO diaspara_read;


-- we need to allow several species separated by a comma (several species for a working group)
-- the 
ALTER TABLE ref.tr_version_ver DROP CONSTRAINT fk_ver_spe_code;
ALTER TABLE refnas.tr_version_ver DROP CONSTRAINT fk_ver_spe_code;
ALTER TABLE refeel.tr_version_ver DROP CONSTRAINT fk_ver_spe_code;
ALTER TABLE refbast.tr_version_ver DROP CONSTRAINT fk_ver_spe_code;
ALTER TABLE ref.tr_version_ver ALTER COLUMN ver_spe_code type TEXT;

#sd <-do.call(rbind,mapply(icesSD::getSD, year= 2020:2024, SIMPLIFY=FALSE))
#sd[grepl('Working Group on North Atlantic Salmon',sd$ExpertGroupDescription),]



tr_version_ver <- data.frame(
  ver_code = paste0("WGNAS-",2020:2024,"-1"),
  ver_year = 2020:2024,
  ver_spe_code = "127186",
  ver_wkg_code = "WGNAS",
  ver_datacalldoi=c(NA,NA,NA,NA,"https://doi.org/10.17895/ices.pub.25071005.v3"), 
  ver_stockkeylabel =c("sal.neac.all"), # sugested by Hilaire. 
  # TODO FIND other DOI (mail sent to ICES)
  ver_version=c(1,1,1,1,1), # TODO WGNAS check that there is just one version per year
  ver_description=c(NA,NA,NA,NA,NA)) # TODO WGNAS provide model description

DBI::dbWriteTable(con_diaspara_admin, "temp_tr_version_ver", tr_version_ver, 
                  overwrite = TRUE)
dbExecute(con_diaspara_admin, "INSERT INTO refnas.tr_version_ver(ver_code, ver_year, ver_spe_code, ver_stockkeylabel, ver_datacalldoi, ver_version, ver_description, ver_wkg_code) SELECT ver_code, ver_year, ver_spe_code, ver_stockkeylabel, ver_datacalldoi, ver_version::integer, ver_description, ver_wkg_code FROM temp_tr_version_ver;") # 5
DBI::dbExecute(con_diaspara_admin, "DROP TABLE temp_tr_version_ver;")

Table 9: Version

ver_code	ver_year	ver_spe_code	ver_stockkeylabel	ver_datacalldoi	ver_version	ver_description	ver_wkg_code
WGNAS-2020-1	2020	Salmo salar	sal.neac.all		1	NA	WGNAS
WGNAS-2021-1	2021	Salmo salar	sal.neac.all		1	NA	WGNAS
WGNAS-2022-1	2022	Salmo salar	sal.neac.all		1	NA	WGNAS
WGNAS-2023-1	2023	Salmo salar	sal.neac.all		1	NA	WGNAS
WGNAS-2024-1	2024	Salmo salar	sal.neac.all		1	NA	WGNAS
DIASPARA-2025-1	2025	Salmo salar	NA	NA	1	WP2 data call for diaspara on LHT	WGNAS
WGEEL-2023-1	2023	Anguilla anguilla	ele		1	Joint EIFAAC/GFCM/ICES Eel Data Call 2023	WGEEL
WGEEL-2024-1	2024	Anguilla anguilla	ele	https://doi.org/10.17895/ices.pub.25816738.v2	1	Joint EIFAAC/GFCM/ICES Eel Data Call 2024	WGEEL
WGEEL-2025-1	2025	Anguilla anguilla	ele	https://doi.org/10.17895/ices.pub.25816738.v2	1	WKEMP 2025 special request	WGEEL
WGEEL-2025-2	2025	Anguilla anguilla	ele	https://doi.org/10.17895/ices.pub.29254589	2	WGEEL Data call 2025: Joint ICES/GFCM/EIFAAC eel data call	WGEEL
WGEEL-2016-1	2016	Anguilla anguilla	ele		1	Joint EIFAAC/GFCM/ICES Eel Data Call 2016	WGEEL
WGEEL-2017-2	2017	Anguilla anguilla	ele		2	Joint ICES, EIFAAC and GFCM Data Call: Data submission for advice for European eel under WGEEL – Part 2: 2018	WGEEL
WGEEL-2017-1	2017	Anguilla anguilla	ele		1	Data provided by wgeel 2016	WGEEL
WGEEL-2018-1	2018	Anguilla anguilla	ele		1	Data provided by wgeel 2017	WGEEL
WGEEL-2019-1	2019	Anguilla anguilla	ele		1	Joint EIFAAC/GFCM/ICES Eel Data Call 2019	WGEEL
WGEEL-2020-1	2020	Anguilla anguilla	ele		1	Joint EIFAAC/GFCM/ICES Eel Data Call 2020	WGEEL
WGEEL-2021-1	2021	Anguilla anguilla	ele		1	Joint EIFAAC/GFCM/ICES Eel Data Call 2021	WGEEL
WGEEL-2022-1	2022	Anguilla anguilla	ele		1	Joint EIFAAC/GFCM/ICES Eel Data Call 2022	WGEEL
WGBAST-2024-1	2024	Salmo salar, Salmo trutta	sal.27.22–31	https://doi.org/10.17895/ices.pub.25071005.v3	1	Joint ICES Fisheries Data call for landings, discards, biological and effort data and other supporting information in support of the ICES fisheries advice in 2024.	WGBAST
WGBAST-2025-1	2025	Salmo salar, Salmo trutta	sal.27.22–31	https://doi.org/10.17895/ices.pub.28218932.v2	1	Combined ICES Fisheries Data call for landings, discards, biological and effort data and other supporting information in support of the ICES fisheries advice in 2025.	WGBAST

TODO ICES

datacall (see link in ICES webpage) are not yet part of a vocabulary accessible to external users in ICES. A demand had been made to make them public

3.9 Statistic (or metric) (tr_statistic_sta)

This table describes the type of statistic (or metric) returned by the parameter. The name metric has not been used to avoid confusion with the “metric DB”.

SQL code to create tables

-- metric 

DROP TABLE IF EXISTS  ref.tr_statistic_sta CASCADE;
CREATE TABLE ref.tr_statistic_sta(
sta_code TEXT PRIMARY KEY,
sta_description TEXT
);


INSERT INTO ref.tr_statistic_sta VALUES
('Estimate' , 'Estimate');
INSERT INTO ref.tr_statistic_sta VALUES
('Index', 'Index');
INSERT INTO ref.tr_statistic_sta VALUES
('Bound', 'Either min or max');
INSERT INTO ref.tr_statistic_sta VALUES
('Hyperparameter', 'Hyperparameter (prior)');
INSERT INTO ref.tr_statistic_sta VALUES
('SD', 'Standard deviation');
INSERT INTO ref.tr_statistic_sta VALUES
('CV', 'Coefficient of variation');
INSERT INTO ref.tr_statistic_sta VALUES
('Precision', 'Inverse of variance');
INSERT INTO ref.tr_statistic_sta VALUES
('Mean', 'Mean');
INSERT INTO ref.tr_statistic_sta VALUES 
('Min','Minimum');
INSERT INTO ref.tr_statistic_sta VALUES 
('Max','Maximum');

GRANT ALL ON ref.tr_statistic_sta TO diaspara_admin;
GRANT SELECT ON ref.tr_statistic_sta TO diaspara_read;
COMMENT ON TABLE ref.tr_statistic_sta IS 
'Table metric describe the type of statistic described by the parameter,  Index, Bound ...';

/*
ALTER TABLE "ref".tr_metric_mtr RENAME TO tr_statistic_sta;
ALTER TABLE "ref".tr_statistic_sta RENAME COLUMN mtr_code TO sta_code;
ALTER TABLE "ref".tr_statistic_sta RENAME COLUMN mtr_description TO sta_description;
ALTER TABLE "ref".tr_statistic_sta RENAME CONSTRAINT tr_metric_mtr_pkey TO tr_statistic_sta_pkey;

Table 10: Statistic (metric) possible value of parameters

sta_code	sta_description
Estimate	Estimate
Index	Index
Bound	Either min or max
Hyperparameter	Hyperparameter (prior)
SD	Standard deviation
CV	Coefficient of variation
Precision	Inverse of variance
Mean	Mean
Min	Minimum
Max	Maximum

NOTE

This list currenly correspond to the needs of both WGNAS and WGBAST. But the statistic can be NULL, for instance in case of a number of fish released, none of the above (Table 10) would apply.

3.10 Category (tr_category_cat)

Categories Table 11 were in the salmoglob metadata, here they were simplified to be able to get groups of parameters, for instance all parameters dealing with catch.

SQL code to create tables

-- tr_category_cat

DROP TABLE IF EXISTS ref.tr_category_cat CASCADE;
CREATE TABLE ref.tr_category_cat (
cat_code TEXT PRIMARY KEY,
cat_description TEXT
);

INSERT INTO ref.tr_category_cat VALUES 
('Catch', 'Catch, including recreational and commercial catch.');
INSERT INTO ref.tr_category_cat VALUES (
'Effort', 'Parameter measuring fishing effort.');
INSERT INTO ref.tr_category_cat VALUES (
'Biomass', 'Biomass of fish either in number or weight.');
INSERT INTO ref.tr_category_cat VALUES (
'Mortality', 'Mortality either expressed in year-1 (instantaneous rate) 
as F in exp(-FY) but can also be harvest rate.');
INSERT INTO ref.tr_category_cat VALUES (
'Release', 'Release or restocking.');
INSERT INTO ref.tr_category_cat VALUES (
'Density', 'Fish density.');
INSERT INTO ref.tr_category_cat VALUES (
'Count', 'Count or abundance or number of fish.');
INSERT INTO ref.tr_category_cat VALUES (
'Conservation limit', 'Limit of conservation in Number or Number of eggs.');
INSERT INTO ref.tr_category_cat VALUES (
'Life trait', 'Life trait parameterized in model, e.g. growth parameter, 
fecundity rate ...');
INSERT INTO ref.tr_category_cat VALUES (
'Other', 'Other variable/ parameter used in the model other than the previous categories, 
origin distribution of catches, proportions, parameters setting the beginning and ending dates....');
COMMENT ON TABLE ref.tr_category_cat IS 
'Broad category of data or parameter, catch, effort, biomass, mortality, count ...,
 more details in the table ref.tr_parameter_parm e.g. commercial catch,
recreational catch are found in the parameter value and definition and unit, 
this list is intended to be short.';

GRANT ALL ON ref.tr_category_cat TO diaspara_admin;
GRANT SELECT ON ref.tr_category_cat TO diaspara_read;

Table 11: category of parameters

cat_code	cat_description
Catch	Catch, including recreational and commercial catch.
Effort	Parameter measuring fishing effort.
Biomass	Biomass of fish either in number or weight.
Mortality	Mortality either expressed in year-1 (instantaneous rate) as F in exp(-FY) but can also be harvest rate.
Release	Release or restocking.
Density	Fish density.
Count	Count or abundance or number of fish.
Conservation limit	Limit of conservation in Number or Number of eggs.
Life trait	Life trait parameterized in model, e.g. growth parameter, fecundity rate ...
Other	Other variable/ parameter used in the model other than the previous categories, origin distribution of catches, proportions, parameters setting the beginning and ending dates...

3.11 Destination (tr_destination_dest)

This table was added for WGBAST. The idea is “what becomes of this fish”. It allows to integrate discards, releases and seal damages.

SQL code to create tables

-- table ref.tr_destination_des

DROP TABLE IF EXISTS ref.tr_destination_des CASCADE;
CREATE TABLE ref.tr_destination_des (
des_code TEXT PRIMARY KEY,
des_description TEXT
);

COMMENT ON TABLE ref.tr_destination_des IS 
'Table of fish destination. When dealing with fish, e.g. in landings,what is the future of the fish, e.g. Released (alive), Seal damage, 
Removed (from the environment)'; 
INSERT INTO ref.tr_destination_des VALUES 
('Removed', 'Removed from the environment, e.g. caught and kept');
INSERT INTO ref.tr_destination_des VALUES (
'Seal damaged', 'Seal damage');
INSERT INTO ref.tr_destination_des VALUES (
'Discarded', 'Discards');
INSERT INTO ref.tr_destination_des VALUES (
'Released', 'Released alive');
INSERT INTO ref.tr_destination_des VALUES (
'Released ', 'Released alive');

GRANT ALL ON ref.tr_destination_des TO diaspara_admin;
GRANT SELECT ON ref.tr_destination_des TO diaspara_read;

Table 12: category of parameters

des_code	des_description
Removed	Removed from the environment, e.g. caught and kept
Seal damaged	Seal damage
Discarded	Discards
Released	Released alive

3.12 Habitat level (tr_habitatlevel_lev)

In the habitat database created by DIASPARA, various levels of regional aggregation have been used. They are embedded within each other like russian dolls. The habitat level correspond to the level of grouping, from stock(s) to river. Depending on the species habitat levels and their hierarchy can differ. An example for this difference is that for Salmon the stock is at the level of a river while the eel is a single stock distributed all over its geographic range. The levels used in the habitat database are described in table Table 13.

This table has been created in the habitat report.

SQL code to create tables

--DROP TABLE IF EXISTS ref.tr_habitatlevel_lev CASCADE;

CREATE TABLE ref.tr_habitatlevel_lev(
   lev_code TEXT PRIMARY KEY,
   lev_description TEXT  
);

COMMENT ON TABLE ref.tr_habitatlevel_lev 
IS 'Table of geographic levels stock, complex, country, region, basin, river,
the specific order depend according to working groups.';

GRANT ALL ON ref.tr_habitatlevel_lev TO diaspara_admin;
GRANT SELECT ON ref.tr_habitatlevel_lev TO diaspara_read; 

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_habitatlevel_lev VALUES( 
  'Panpopulation',
  'This is the highest geographic level for assessement.'  
  );")

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_habitatlevel_lev VALUES( 
  'Complex',
  'Corresponds to large sublevels at which the Panpopulation is assessed, e.g.
  NAC NEC for WGNAST, Gulf of Bothnia for WGBAST, Mediterranean for WGEEL.'  
  );")

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_habitatlevel_lev VALUES( 
  'Stock',
  'Correspond to stock units for which advices are provided in ICES, this can be the level of the panpopulation,
  or another level e.g. .'  
  );")

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_habitatlevel_lev VALUES( 
  'Country',
  'Corresponds to one or more units, but in almost all stocks
  this level is relevant to split data.'
  );")


dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_habitatlevel_lev VALUES( 
  'EMU',
  'Administrative unit for eel, the hierarchical next level is country.'
  );")

# note this can be unit or Asssessment unit it can have two meanings ...
dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_habitatlevel_lev VALUES( 
  'Assessment_unit',
  'Corresponds to an assessment unit in the Baltic sea, and area for  
  WGNAS, and EMU for WGEEL.'
  );")

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_habitatlevel_lev VALUES( 
  'Regional',
  'Corresponds to subunits of stock assessment units or 
  basins grouping several river. Although it is not used yet for
  some models, regional genetic difference or difference in stock
  dynamic support collecting a regional level.'
  );")

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_habitatlevel_lev VALUES( 
  'River',
  'One river is a unit corresponding practically almost always to a watershed.'
  );")

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_habitatlevel_lev VALUES( 
  'River_section',
  'Section of river, only a part of a basin, for instance to separate between
  wild and mixed river category in the Baltic.'
  );")

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_habitatlevel_lev VALUES( 
  'Major',
  'Major fishing areas from ICES.'
  );")

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_habitatlevel_lev VALUES( 
  'Subarea',
  'Subarea from ICES, FAO and NAFO'
  );")

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_habitatlevel_lev VALUES( 
  'Division',
  'Division from ICES, GFCM and NAFO'
  );")

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_habitatlevel_lev VALUES( 
  'Subdivision',
  'Subdivision level from ICES, GFCM and NAFO'
  );")

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_habitatlevel_lev VALUES( 
  'Lagoons',
  'Shallow body of water seperated from a larger body of water by a narrow landform'
  );")

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_habitatlevel_lev VALUES( 
  'Subdivision_grouping',
  'Groups of subdivision from ICES used in the Baltic'
  );")

dbExecute(con_diaspara_admin,"UPDATE ref.tr_habitatlevel_lev
    SET lev_description='Corresponds to an assessment unit in the Baltic sea, and area for WGNAS, and EMU for WGEEL.'
    WHERE lev_code='Assessment_unit'"); # remove spaces ...

Table 13: Geographical level tr_habitatlevel_lev

lev_code	lev_description
River	One river is a unit corresponding practically almost always to a watershed.
Major	Major fishing areas from ICES.
Subarea	Subarea from ICES, FAO and NAFO
Division	Division from ICES, GFCM and NAFO
Subdivision	Subdivision level from ICES, GFCM and NAFO
Fisheries	Specific fisheries area used by some working groups (WGNAS), e.g. FAR fishery, GLD fishery, LB fishery, LB/SPM/swNF fishery, neNF fishery
EMU	Administrative unit for eel, the hierarchical next level is country.
River_section	Section of river, only a part of a basin, for instance to separate between wild and mixed river category in the Baltic.
Subdivision_grouping	Groups of subdivision from ICES used in the Baltic
Lagoons	Shallow body of water seperated from a larger body of water by a narrow landform
Stock(s)	This is the highest geographic level for assessement, stock level or pan Population, it correspond to a single stock for eel.
Complex	Corresponds to large sublevels at which the stock is assessed, e.g. NAC NEC for WGNAST, Gulf of Bothnia for WGBAST, Mediterranean for WGEEL.
Country	Corresponds to one or more units, but in almost all stocks this level is relevant to split data.
Assessment_unit	Corresponds to an assessment unit in the Baltic sea, and area for WGNAS, and EMU for WGEEL.
Regional	Corresponds to subunits of stock assessment units (substock) or basins grouping several river. Although it is not used yet for some models, regional genetic difference or difference in stock dynamic support collecting a regional level
Post_smolt_area	Postsmolt areas from Olmos et al. (2021)
Substock	Sub component of the stock, assessed in separate models as they are geographically isolated during part of their lifecycle

3.12.1 Area (tr_area_are)

This table has been created in the diadromous database as we needed reference for various geographical grouping used thoughout the different databases. However, most of the work was done in the habitat report for the full habitat referential creation.

SQL code to create tables

--DROP TABLE IF EXISTS ref.tr_area_are CASCADE;
CREATE TABLE ref.tr_area_are (
   are_id INTEGER PRIMARY KEY,
   are_are_id INTEGER,
   are_code  TEXT,
   are_lev_code TEXT,
   are_wkg_code TEXT,
   are_ismarine BOOLEAN,
   are_name TEXT,
   geom_polygon geometry(MULTIPOLYGON, 4326),
   geom_line geometry(MULTILINESTRING, 4326),
  CONSTRAINT fk_are_are_id FOREIGN KEY (are_are_id) 
  REFERENCES ref.tr_area_are (are_id) ON DELETE CASCADE
  ON UPDATE CASCADE,
  CONSTRAINT uk_are_code UNIQUE (are_code),
  CONSTRAINT fk_area_lev_code FOREIGN KEY (are_lev_code) REFERENCES
  ref.tr_habitatlevel_lev(lev_code) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_area_wkg_code FOREIGN KEY (are_wkg_code) REFERENCES
  ref.tr_icworkinggroup_wkg(wkg_code) ON UPDATE CASCADE ON DELETE CASCADE
);

GRANT ALL ON ref.tr_area_are TO diaspara_admin;
GRANT SELECT ON ref.tr_area_are TO diaspara_read;

COMMENT ON TABLE ref.tr_area_are IS 'Table corresponding to different geographic levels, from stock 
to river section.');

-- we need to rename the column (fix 16/10/2025)
ALTER TABLE "ref".tr_area_are RENAME COLUMN are_rivername TO are_name;

Table 14: Geographic areas

are_id	are_are_id	are_code	are_lev_code	are_wkg_code	are_ismarine
306	17	2120027350	River	WGBAST	FALSE
3099	174	20127557	River_section	WGBAST	FALSE
3086	174	20127414	River_section	WGBAST	FALSE
3088	174	20127330	River_section	WGBAST	FALSE
15	3	3 Bothnian Sea	Assessment_unit	WGBAST	FALSE
14	3	2 Western Bothnian Bay	Assessment_unit	WGBAST	FALSE
17	3	5 Eastern Main Basin	Assessment_unit	WGBAST	FALSE
18	3	6 Gulf of Finland	Assessment_unit	WGBAST	FALSE
2	1	Baltic marine	Stock(s)	WGBAST	TRUE
13	3	1 Northeastern Bothnian Bay	Assessment_unit	WGBAST	FALSE

Figure 7: First concepts of the hierarchy, see habitat report for final hierarchies in each working group

NOTE DIASPARA

Areas are specific to each working group (see Figure 11)

Figure 8: WGBAST

Figure 9: WGNAS

Figure 10: WGEEL

Figure 11: diagram of tr_area_are, arrows indicate inheritance

3.13 Data access (tr_dataaccess_dta)

Type of data Public, or Restricted

SQL code to create tables

--DROP TABLE IF EXISTS ref.tr_dataaccess_dta CASCADE;
CREATE TABLE ref.tr_dataaccess_dta(
   dta_code TEXT PRIMARY KEY,
   dta_description TEXT  
);
GRANT ALL ON ref.tr_dataaccess_dta  TO diaspara_admin;
GRANT SELECT ON ref.tr_dataaccess_dta TO diaspara_read;
COMMENT ON TABLE ref.tr_dataaccess_dta 
IS 'Table with two values, Public or Restricted access.';

tr_dataaccess_dta <- dbGetQuery(con_diaspara_admin, 
                                "SELECT * FROM refwgeel.tr_dataaccess_dta")

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_dataaccess_dta 
  SELECT * FROM refwgeel.tr_dataaccess_dta
  ;")#2

Table 15: Data access

dta_code	dta_description
Public	Public access according to ICES Data Policy
Restricted	Restricted access (wgeel find a definition)

Figure 12: diagram of tr_dataaccess_dta, arrows indicate inheritance

3.14 Missing data (tr_missvalueqal_mis)

SQL code to create tables

--DROP TABLE IF EXISTS ref.tr_missvalueqal_mis CASCADE;

CREATE TABLE ref.tr_missvalueqal_mis(
   mis_code TEXT PRIMARY KEY,
   mis_description TEXT NOT NULL,  
   mis_definition TEXT);
   
GRANT ALL ON ref.tr_missvalueqal_mis TO diaspara_admin;
GRANT SELECT ON ref.tr_missvalueqal_mis TO diaspara_read;

COMMENT ON TABLE ref.tr_missvalueqal_mis IS 'Table showing the qualification when value is missing, NC, NP, NR.';

dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_missvalueqal_mis 
SELECT
'NR',
'Not reported', 
'Data or activity exist but numbers are not reported to authorities (for example for commercial confidentiality reasons).';")
dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_missvalueqal_mis 
SELECT
'NC',   
'Not collected',    
'Activity / habitat exists but data are not collected by authorities (for example where a fishery exists but the catch data are not collected at the relevant level or at all).';")
dbExecute(con_diaspara_admin,
          "INSERT INTO ref.tr_missvalueqal_mis 
SELECT
'NP',   
'Not pertinent',
'Where the question asked does not apply to the individual case (for example where catch data are absent as there is no fishery or where a habitat type does not exist in a stock unit).';")

Table 16: Code for missing values

mis_code	mis_description	mis_definition
NR	Not reported	Data or activity exist but numbers are not reported to authorities (for example for commercial confidentiality reasons).
NC	Not collected	Activity / habitat exists but data are not collected by authorities (for example where a fishery exists but the catch data are not collected at the relevant level or at all).
NP	Not pertinent	Where the question asked does not apply to the individual case (for example where catch data are absent as there is no fishery or where a habitat type does not exist in a stock unit).

TODO ICES

This is used in the t_stock_sto table by both WGEEL and WGBAST. Either a value is provided or this field has to be provided (conditional mandatory in the format).

3.15 Life stages (tr_lifestage_lfs)

Life stages cannot easily be shared among all species, they are species specific. Often similar between Sea trout and Salmon, but there is a large gap between a leptocephalus and a parr.

3.15.1 Considerations about the database structure

For life stage, unlike in other referentials, using working-group-specific life stages would lead to confusion. WGBAST and WGNAS would share the same stages for salmon. So unlike in many other tables, the referentials will not use inheritance (see paragraph Section 2 for more details on inheritance). This means that we will create a table grouping all life stages and then we will only select the relevant ones at working group levels. For instance currently WGEEL does not use the Egg or Parr stages. It will be listed in the ref.tr_lifestage_lfs table but not in the refeel.tr_lifestage_lfs table. So the working group referentials, refnas, refbast, refeel … will have tr_lifestage_lfs tables with a foreign key to ref.tr_lifestage_lfs, and a subset of values used by the working group.

3.15.2 The lifestages in working group databases

Stages use are described in WGNAS metadata paragraph life stage of the WGNAS description report and WGBAST reports (though for the WGBAST’s “young fish” -data this concept is mixed with age) paragraph life stage of the WGBAST description report. So they are not used as a separate column to describe the data. But in the eel database they are and so we will need to add this dimension to the table structure.

3.15.3 Simplifying the stage column in WGNAS

The work was initially done with the stage column in the WGNAS salmoglob database. In this column, the information relies on a combination of life stage and spatial location (e.g; post smolt and location at sea, returning adult in seawater, in freshwater…) ICES (2024b). To deal with these spatio-temporal elements that are not stages, we simplified the stages table, removed elements which are not stages. Parameters still hold the spatio temporal information in metadata. Here we are focusing on the stock DB which will group information, but the individual metric database might require more details that the simple adult stage. For this reason, we will add the maturity scale from ICES in the DB.

3.15.4 Some stages are a mixture of several stages.

In some cases, a mixture of stages are used. Many fyke net fisheries for eel will not distinguish between yellow and silver eel stage and WGEEL uses the YS stage. Some historical trap series did not distinguish between small yellow eels and glass eel, or the glass eel ascending are in a late stage. In that case the GYstage is used to count the recruits.

Stages OG and QG for WGEEL

Ongrown eel (OG) and quarantined glass eel (QG) are used to describe the stages of glass eel or small yellow eels as they are released. They will be marked as deprecated for the stock, but they will probably be used in the release database that needs to be created for EDA.

3.15.5 Code to create the stage table

The code for creating tr_lifestage_lfs is shown below, it also includes the import of the WGEEL stage table.

SQL code to create table tr_lifestage_lfs

-- Table for lifestage
-- there is one table for all working groups 
-- so this table is not inherited (otherwise two wkg could create referential for the same species)

DROP TABLE IF EXISTS ref.tr_lifestage_lfs CASCADE;
CREATE TABLE ref.tr_lifestage_lfs (
  lfs_id SERIAL PRIMARY KEY,
  lfs_code TEXT NOT NULL UNIQUE,
  lfs_name TEXT NOT NULL,
  lfs_spe_code character varying(3) NOT  NULL,    
  lfs_description TEXT,
  lfs_icesvalue character varying(4),  
  lfs_icesguid uuid,
  lfs_icestablesource text,
CONSTRAINT fk_lfs_spe_code FOREIGN KEY (lfs_spe_code)
  REFERENCES ref.tr_species_spe(spe_code) 
  ON DELETE CASCADE
  ON UPDATE CASCADE,
CONSTRAINT uk_lfs UNIQUE (lfs_code, lfs_spe_code)
);

COMMENT ON TABLE ref.tr_lifestage_lfs IS 'Table of lifestages';
COMMENT ON COLUMN ref.tr_lifestage_lfs.lfs_id IS 'Integer, primary key of the table';
COMMENT ON COLUMN ref.tr_lifestage_lfs.lfs_code IS 'The code of lifestage';
COMMENT ON COLUMN ref.tr_lifestage_lfs.lfs_name IS 'The english name of lifestage';
COMMENT ON COLUMN ref.tr_lifestage_lfs.lfs_spe_code IS 'The code of the species referenced from
tr_species_spe : use aphiaID eg ''126281'' for eel ';
COMMENT ON COLUMN ref.tr_lifestage_lfs.lfs_description IS 'Definition of the lifestage';
COMMENT ON COLUMN ref.tr_lifestage_lfs.lfs_icesvalue IS 'Code for the lifestage in the ICES database';
COMMENT ON COLUMN ref.tr_lifestage_lfs.lfs_icesguid IS 'GUID in the ICES database';
COMMENT ON COLUMN ref.tr_lifestage_lfs.lfs_icestablesource IS 'Source table in ICES vocab';


GRANT ALL ON ref.tr_lifestage_lfs TO diaspara_admin;
GRANT SELECT ON ref.tr_lifestage_lfs TO diaspara_read;

3.15.6 Existing stages in ICES dictionaries

You can run the following code to see the candidate tables in ICES, in summary: none fitted and this part is skipped to shorten the report.

Table 17: ICES vocabularies for life stages

types <- icesVocab::getCodeTypeList()
types[grep('stage', tolower(types$Description)),]|> kable()
TS_MATURITY <- icesVocab::getCodeList('TS_MATURITY')
TS_DevStage <- icesVocab::getCodeList('TS_DevStage')
# Devstage is 1 to 15 => no use
DevScale <- icesVocab::getCodeList('DevScale')
# At the present the codetypes target Eggs and Larvae surveys
# This is a description of scales types using different publications => not for use()
kable(TS_MATURITY, caption = "TS_MATURITY") |> kable_styling(bootstrap_options = c("striped", "hover", "condensed")) 
kable(TS_DevStage, caption = "Devstage scale, this seems to be about shrimp (berried seems to an egg which looks at you with eyes in shrimps....)") |> kable_styling(bootstrap_options = c("striped", "hover", "condensed")) 

3.15.7 Importing the lifestages for Salmon

Some of the definitions come from Ontology portal.

# below the definition of Smolt, post-smolt and Adult provided by Etienne.
lfs <- tribble(
  ~lfs_code, ~lfs_name, ~lfs_spe_code, ~lfs_description, 
  ~lfs_icesvalue, ~lfs_icesguid, ~lfs_icestablesource,
  "E",   "Egg", "127186" , "In fish, the term egg usually refers to female haploid gametes.", 
  "E",   "0424ae90-03aa-4e73-8cda-e8745d0b8158", "TS_DevStage",
  "EE",   "Eyed egg", "127186" , "Eyed eggs are fertilized eggs that have developed to the stage where the eyes of the fish can easily be seen with naked eyes through the translucent egg shell. They might be used for stocking purpose.", 
  NA,  NA , NA,
  "ALV",   "Alevin with the yolk sac", "127186" , "Larval salmon that have hatched but have not yet completely absorbed their yolk sacs and usually have not yet emerged from the gravel.  http://purl.dataone.org/odo/SALMON_00000403", 
  NA,  NA , NA,
  "FR",   "Fry", "127186" , "A young salmonid at the post-larval stage who has resorbed the yolk sac but remains buried in the gravel.  The stage starts at the end of dependence on the yolk sac as the primary source of nutrition to dispersal from the redd.", 
  NA,  NA , NA,
  "P", "Parr", "127186", 
  "A young salmonid with parr-marks before migration to the sea and after dispersal from the redd.  http://purl.dataone.org/odo/SALMON_00000649",
  NA, NA, NA,
  "SM", "Smolt", "127186", "A young salmonid which has undergone the transformation to adapt to salt water, has developed silvery coloring on its sides, obscuring the parr marks, and is about to migrate or has just migrated into the sea.",
  NA, NA, NA,
  "PS", "Post Smolt", "127186", "A salmonid at sea, after its migration to the sea as smolt. For salmon it usually refer to fishes during their between the smolt migration in spring and the first winter at sea.",
  NA, NA, NA,
  "A", "Adult", "127186", " Salmonids that have fully developed morphological and meristic characters and that have attained sexual maturity. For salmon this might refer to fishes during their migration back to coastal waters for the reproduction, or to spawning adults in freshwater. More details can be given on the sexual maturity of the fish using the maturity scale.", NA, NA, NA,
  "AL", "All stages", "127186", "All life stages are concerned.", NA, NA, NA,
  "_", "No life stage", "127186", "Reserved when the life stage makes no sense for the variable stored in the database, e.g. a parameter setting the number of years in the model", NA, NA, NA
)
dbWriteTable(con_diaspara_admin, "temp_lfs", lfs, overwrite = TRUE)
dbExecute(con_diaspara_admin, "DELETE FROM ref.tr_lifestage_lfs;")#24
dbExecute(con_diaspara_admin, "INSERT INTO ref.tr_lifestage_lfs 
    ( lfs_code, lfs_name, lfs_spe_code, lfs_description, lfs_icesvalue, lfs_icesguid, lfs_icestablesource)
    SELECT 
     lfs_code, 
     lfs_name, 
     lfs_spe_code,
     lfs_description,
     lfs_icesvalue, 
     lfs_icesguid::uuid, 
     lfs_icestablesource
     FROM temp_lfs;")
dbExecute(con_diaspara_admin, "DROP TABLE temp_lfs")

3.15.8 Import lifestages for eel

dbExecute(con_diaspara_admin,"DELETE FROM ref.tr_lifestage_lfs WHERE lfs_spe_code ='126281';")
dbExecute(con_diaspara_admin, "INSERT INTO ref.tr_lifestage_lfs (lfs_code,lfs_name,lfs_description, lfs_spe_code)
SELECT lfs_code,initcap(lfs_name),lfs_definition, '126281' 
FROM refwgeel.tr_lifestage_lfs ;") # 8

3.15.9 Import lifestages for trutta

dbExecute(con_diaspara_admin, "INSERT INTO ref.tr_lifestage_lfs 
    (lfs_code, lfs_name, lfs_spe_code, lfs_description, lfs_icesvalue, lfs_icesguid, lfs_icestablesource)
    SELECT 
    lfs_code, 
    lfs_name, 
    '127187' AS lfs_spe_code,
    lfs_description,
    lfs_icesvalue, 
    lfs_icesguid::uuid, 
    lfs_icestablesource
    FROM ref.tr_lifestage_lfs WHERE lfs_spe_code = '127186';")

3.15.10 Content of the tr_lifestage_lfs table

dbGetQuery(con_diaspara, "SELECT * FROM ref.tr_lifestage_lfs;") |> 
DT::datatable(rownames = FALSE,
  options = list(
    pageLength = 7,        
    scrollX= TRUE   
  ))

Tip

Follow up in ICES vocab

This issue has been created in ICES data governance : DIASPARA: Diadromous fish life stages #885