diaspara database creation script

The main structure of the database has been proposed during the online diaspara meeting : database structure. This stucture would allow to store the data necessary to run the international stock models for the different working groups working on diadromous fishes. The structure of the proposed database and the structure of the wgeel database are very similar. This documents creates the diaspara database, first creating referential table for the different types proposed, and then populates the database with the contents of the wgeel and wgnas database. All chunks are run sequentially to run the database, but some are later marked with eval = FALSE to speed up the process of running the quarto document.

Hierarchical structure of the database

The database could start with the simplest structure, with a basic table corresponding to all data. Then three tables could be created, one per species. This is to allow querying the different tables independently. From these species tables, three different tables could be produced, one for data, one for parameters outputs, one for parameters priors. Since SQL server does not handle inheritance, once the table built, some of those will have to be replaced with views or partitioned views.

From these three table on could envisage the creation of specific table for working groups. Meaning one table for eel (wgeel), one table for salmon (wgnas), one table for salmon (wgbast), and one table for trutta (wgtrutta).

Referential tables

[TO BE EDITED LATER WHEN WE KNOW EXACTLY WHAT WE HAVE DONE]

Similarly, referential tables could be created with a mother table from which specific (or wg specific) tables would inherit. All mother table will be held in a schema called ref. Having working group specific tables make the setting up of consistent foreign key more easy. For instance wgbast could reference different age class than wgnas, and the stage would be completely different between wgeel and wgnas reference daughter tables. Some of these referential table would be common between species (e.g. source from ICES vocab, which corresponds to working group or accession events (datacalls)).

Unicity constraints

Another important point to add (at least to the salmoglob database) is unicity constraint. As some values would be null, creating unicity constraints with indexes would be necessary. These allow to have different levels of constraints for instance the unicity would be defined for : (year, age, area, parameter) (year, age, parameter) (year, area, parameter) (year, parameter)

One of the table will have to contain twice the area it will have to be treated separately. (year, area, area, parameter)

Creating the diaspara database

All along this document, the database will be named diaspara. The database is created with postgres.

** Some rules **

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

• Code ELE, ANG, TRT are capitalized, as are the working group names WGEEL, WGNAS, or country codes.

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

• All integer used for primary keys are called id all text used for primary keys are called code.

• 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

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

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

• Foreign key are name c_fk_columnname where the column name is the name in the table

• Primary keys are names _pk_columnnames (with column name having possibly more than one column).

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

• 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).

This is run in localhost, check the wp3_habitat repository for code to set up access to the database. In the future we will grant diaspara_admin and diaspara_read to specific users for example with a used named trout GRANT diaspara_admin TO trout ;

DIASPARA Note to self

need to edit the pb_hba.conf on the server if not in localhost to allow access to diaspara.

dbExecute(con_diaspara_admin, paste0("COMMENT ON DATABASE ",cred$dbnamediaspara," IS 'This database is named Frankenstein :-)'"))
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,
"GRANT ALL PRIVILEGES ON SCHEMA refwgeel TO diaspara_admin;")

SQL code to additional data schema

Listing 1: Building additional schema

--| echo: TRUE
--| eval: FALSE


-- this one is straight into sql ... 

DROP SCHEMA IF EXISTS dat CASCADE;
CREATE SCHEMA dat;
GRANT SELECT ON SCHEMA dat TO diaspara_read;
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;
GRANT SELECT ON SCHEMA datang TO diaspara_read;
ALTER SCHEMA datang OWNER TO diaspara_admin;
COMMENT ON SCHEMA datang IS 'SCHEMA for WGEEL;'

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

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


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

Now the database has been created with different schema (Figure 1). The main schema for dictionaries is ref, and a schema is created per working group for specific referential tables. The Schema refwgeel is a schema created with a foreign data wrapper to get the data from wgeel, the same schema exists for wgnas. We’ll see later for wgbast and wgtrutta.

Figure 1: Structure of the schema in diaspara. Dashed arrow indicate an import of data from existing

Now the database has been created with different schema (Figure 1). The main schema for dictionaries is ref, and a schema is created per working group for specific referential tables. The Schema refwgeel has been filled in with a foreign data wrapper to get the data from wgeel, the same schema exists for wgnas. We’ll see later for wgbast and wgtrutta. The schema dat is the common schema for all data. For each working group, schema datbast, dateel, datnas are created. The tables will be 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 thatdat should not containt any data, but will hold all the views and inherited tables comming from the different schema.

Creating referentials

Species

The first thing is to create a referential table for species. Anyways, we searched a bit for other species, even if we don’t plan to start storing data on Alosa and Lamprey it’s good to prepare the database. There are no code in ICES vocal for Alosa alosa, Alosa fallax, Petromyzon marinus, Lampetra fluviatilis.

Note Cédric : Thinking later about this, I chose to get rid of ELE and select ANG. This column with species will be everywhere. We want to avoid generations of researchers to avoid what ELE is (apart from elephants). For the others I’ve taken as close as possible to the latin name.

QUESTION ICES: species code

ANG, ALA, ALF, PET, LAM are these internal code OK ? Should we use SpecWoRMS or is Aphia OK ?

Creating a referential table for species - code and queries to ICES

# No code for Lampetra, Alosa, petromyzon
sp <- getCodeList("IC_Species")
grep("Lampetra", sp$description)

integer(0)

grep("Petromyzon", sp$description)

integer(0)

grep("Alosa",  sp$description)

integer(0)

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("SAL", "ANG", "TRT", "ALA", "ALF", "PET", "LAM"),
    spe_icspecieskey = c("SAL", "ELE", "TRT", 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

(a) Code found in IC_Species

Key	Description	LongDescription	Modified	Deprecated
ELE	Anguilla anguilla	NA	2019-04-15	FALSE
SAL	Salmo salar	NA	2019-04-15	FALSE
TRS	Salmo trutta	NA	2019-04-15	FALSE

(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

dbExecute(con_diaspara_admin, "DROP TABLE IF EXISTS ref.tr_species_spe;")
dbExecute(con_diaspara_admin,"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)");
dbExecute(con_diaspara_admin, "GRANT ALL ON TABLE ref.tr_species_spe to diaspara_admin")
dbExecute(con_diaspara_admin, "GRANT SELECT ON TABLE ref.tr_species_spe to diaspara_read")
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 SAL, ANG, TRT, ALA, ALF, PET, LAM, with 
reference to ICES vocabularies.'")

Working group

Species is not enough. Data will be split by working groups. Several working groups working on the same species. There is a table for working group. Proposed table is Table 2 but need confirmation for WKTRUTTA2.

QUESTION ICES/ WGTRUTTA/ DIASPARA

what is the latest working group for WGTRUTTA, I found a WKTRUTTA2 but that is quite old. Do we want to refer to other groups on diadromous fishes there ? The name of the WGEEL is wrong in the referential, needs to reference GFCM… JOINT EIFAAC/ICES/GFCM WORKING GROUP ON EEL.

dbExecute(con_diaspara_admin,
"DROP TABLE IF EXISTS ref.tr_icworkinggroup_wkg CASCADE;")
dbExecute(con_diaspara_admin,
"CREATE TABLE ref.tr_icworkinggroup_wkg (
wkg_code TEXT PRIMARY KEY,
wkg_description TEXT,
wkg_icesguid uuid
);")

dbExecute(con_diaspara_admin, "COMMENT ON TABLE ref.tr_icworkinggroup_wkg 
IS 'Table corresponding to the IC_WorkingGroup referential;';")
# 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"))

dbWriteTable(con_diaspara_admin, 
             "temp_tr_icworkinggroup_wkg", 
             temp_tr_icworkinggroup_wkg)

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

dbExecute(con_diaspara_admin, "COMMENT ON COLUMN 
ref.tr_icworkinggroup_wkg.wkg_code IS 
'Working group code uppercase, WGEEL, WGNAS, WGBAST, WGTRUTTA';")


dbExecute(con_diaspara_admin, "GRANT ALL ON ref.tr_icworkinggroup_wkg 
          TO diaspara_admin;")
dbExecute(con_diaspara_admin, "GRANT SELECT ON ref.tr_icworkinggroup_wkg 
          TO diaspara_read;")

tr_icworkinggroup_wkg <- dbGetQuery(con_diaspara, "SELECT * FROM ref.tr_icworkinggroup_wkg")
knitr::kable(tr_icworkinggroup_wkg) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 2: 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

Country

Countries are mostly OK in the wgeel database but we need to add american countries.

Code to create table from wgeel and NUTS

dbExecute(con_diaspara_admin, "DROP TABLE IF EXISTS ref.tr_country_cou;")
dbExecute(con_diaspara_admin, "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)
);")


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 c_pk_cou_code PRIMARY KEY (cou_code);")
dbExecute(con_diaspara_admin, "ALTER TABLE ref.tr_country_cou 
          ADD CONSTRAINT c_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
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  public.\"ref-countries-2024-01m — CNTR_RG_01M_2024_4326\" 
WHERE \"CNTR_ID\" IN ('GL', 'CA', 'US');")

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

dbExecute(con_diaspara_admin,"COMMENT ON TABLE ref.tr_country_cou IS
          'Table of country codes source EuroGeographics and UN-FAO.';")
dbExecute(con_diaspara_admin,"ALTER TABLE ref.tr_country_cou 
          OWNER TO diaspara_admin;")
dbExecute(con_diaspara_admin,
          "GRANT SELECT ON TABLE ref.tr_country_cou TO diaspara_read;")

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 3: 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
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!
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()

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

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

Unit

Creating the unit from wgeel and checking ICES code

First we import from wgeel

Then we standarize using ICES codes, it takes a while to scroll through the vocab. Sometimes several vocab are available for the same thing. We used the p06 as the most common source. Hopefully that was the right choices ?

dbExecute(con_diaspara_admin, "DROP TABLE IF EXISTS ref.tr_units_uni CASCADE;")

dbExecute(con_diaspara_admin,
"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 pk_uni 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)
);")

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')
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)
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, "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;")

<>

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

Table 4: 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

Some of the values are missing from ICES vocab (Table Table 4).

QUESTION ICES: missing values for units, what do we do ?

What do we do with units without correspondance? These come from FAO (if I remember well). Do we try to search for those in the wgeel database, and then remove if not existing or try to change existing values ?

• Kg/day there is a kg/hour do we need to change to that type and convert existing series ?

• Nr haul There is a definition of haul in the ICES vocab but it seems very related to sampling box, basket. And it’s not the number of haul.

• Before working any further I would like your opinion there.

Parameters

Parameters are a simple way to reduce the complexity of data. It will correspond to all nimble 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 identifyer of the variable will be used for all the lines necessary to store this dataset. In practise, parameters also correspond to input data, and output data in the model. The parameters will be described by their metadata as illustrated in Figure 3

Figure 3: Mind map of the metadata structure

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

There is a problem in the “order or the dimensions which need to be aligned. For instance a column can hold year, or age. This is not good. The description could be used within a type array. SQL server does not work with array so it’s not a good idea to use those.

Checking stock codes using icesASD and icesSD packages

# install.packages('icesASD', 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('icesASD')
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()

Table 5: Access to the advice using icesASD

	adviceDOI	stockCode	assessmentYear
16	https://doi.org/10.17895/ices.advice.4825	ele.2737.nea	2019
799	https://doi.org/10.17895/ices.advice.19772374.v1	ele.2737.nea	2022
866	https://doi.org/10.17895/ices.pub.3440	ele.2737.nea	2017
975	https://doi.org/10.17895/ices.advice.7752	ele.2737.nea	2021
1014	https://doi.org/10.17895/ices.advice.5898	ele.2737.nea	2020
1048	https://doi.org/10.17895/ices.pub.4601	ele.2737.nea	2018
1312	https://doi.org/10.17895/ices.advice.21907860.v2	ele.2737.nea	2023

	StockDatabaseID	StockKey	StockKeyLabel	StockKeyDescription	PreviousStockKey	PreviousStockKeyLabel	ActiveYear	SpeciesScientificName	SpeciesCommonName	EcoRegion	ExpertGroup	ExpertGroupDescription	AdviceDraftingGroup	AdviceDraftingGroupDescription	DataCategory	YearOfLastAssessment	AssessmentFrequency	YearOfNextAssessment	AssessmentType	AdviceReleaseDate	AdviceCategory	AdviceType	UseOfDiscardsInAdvice	PABufferApplied	TrophicGuild	FisheriesGuild	SizeGuild	Published	GeneratedOn	SectionNumber	AssessmentKey	ModifiedDate	ModelType	ModelName
149	2600	169241	sal.27.22-31	Salmon (Salmo salar) in subdivisions 22-31 (Baltic Sea, excluding the Gulf of Finland)	136678	sal-2431	2020	Salmo salar	Salmon	Baltic Sea	WGBAST	Assessment Working Group on Baltic Salmon and Trout	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2019	1	2020	Analytical model, Bayesian state-space model for a majority of rivers in AUs 1–4	NA	MSY	Catches	Assessment	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:31.99+01:00	8.3.15	13726	2019-05-29T11:25:06.9+01:00	O	Unknown
186	2590	169095	ele.2737.nea	European eel (Anguilla anguilla) throughout its natural range	136527	ele-nea	2020	Anguilla anguilla	Eel	Arctic Ocean, Azores, Bay of Biscay and the Iberian Coast, Barents Sea, Baltic Sea, Celtic Seas, Faroes, Greenland Sea, Icelandic Waters, Greater North Sea, Norwegian Sea, Oceanic Northeast Atlantic	WGEEL	Joint EIFAAC/ICES/GFCM Working Group on Eels	ADGEEL	Eel Advice Drafting GRoup	3.14	2020	1	2021	Trends-based assessment	NA	PA	No directed fisheries	Not used	No	demersal benthivore	Demersal	medium demersal	TRUE	2025-01-02T18:29:31.99+01:00	9.3.13	14025	2020-11-12T15:09:35.93+01:00	NA	NA
187	2601	169242	sal.27.32	Salmon (Salmo salar) in Subdivision 32 (Gulf of Finland)	136679	sal-32	2020	Salmo salar	Salmon	Baltic Sea	WGBAST	Assessment Working Group on Baltic Salmon and Trout	RG/ADGSalmon	Salmon Review and Advice Drafting Group	3	2020	1	2021	Qualitative assessment based on monitored parr densities and expert judgement.	NA	PA	Catches	Topped up	No	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:31.99+01:00	8.3.16	13453	2020-11-12T12:12:48.513+01:00	O	Unknown
236	2512	169291	trs.27.22-32	Sea trout (Salmo trutta) in subdivisions 22-32 (Baltic Sea)	136729	trt-bal	2020	Salmo trutta	Sea trout	Baltic Sea	WGBAST	Assessment Working Group on Baltic Salmon and Trout	NA	NA	4	2019	2	2021	Evaluation of the relative status of stocks by comparing the observed parr densities to model-predicted maximal parr densities.	NA	PA	Reduction	Not used	No	demersal piscivore	Pelagic	large pelagic	TRUE	2025-01-02T18:29:31.99+01:00	8.3.18	NA	2019-05-29T11:34:00.447+01:00	NA	NA
259	2652	199793	sal.neac.all	Salmon (Salmo salar) in Northeast Atlantic and Arctic Ocean	NA	NA	2020	Salmo salar	Salmon	Arctic Ocean, Azores, Bay of Biscay and the Iberian Coast, Barents Sea, Celtic Seas, Faroes, Greenland Sea, Icelandic Waters, Greater North Sea, Norwegian Sea, Oceanic Northeast Atlantic	WGNAS	Working Group on North Atlantic Salmon	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2018	3	2021	Analytical model, run-reconstruction models and Bayesian forecasts, taking into account uncertainties in data and process error (results presented in a risk analysis framework)	NA	MSY/PA	Catches	Not used	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:31.99+01:00	10.3.3	NA	2020-11-12T13:18:52.573+01:00	O	Unknown
260	2650	199794	sal.nac.all	Salmon (Salmo salar) from North America	NA	NA	2020	Salmo salar	Salmon	Greenland Sea, Oceanic Northeast Atlantic	WGNAS	Working Group on North Atlantic Salmon	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2019	2	2021	Analytical model, run-reconstruction models and Bayesian forecasts, taking into account uncertainties in the data	NA	MSY/PA	Catches	Not used	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:31.99+01:00	10.3.1	NA	2021-01-11T10:35:02.487+01:00	O	Unknown
261	2651	199795	sal.wgc.all	Salmon (Salmo salar) in Subarea 14 and NAFO division 1 (east and west of Greenland)	NA	NA	2020	Salmo salar	Salmon	Greenland Sea, Oceanic Northeast Atlantic	WGNAS	Working Group on North Atlantic Salmon	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2019	2	2021	Analytical model, run reconstruction models and Bayesian forecasts, taking into account uncertainties in the data	NA	MSY/PA	Catches	Not used	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:31.99+01:00	10.3.2	NA	2020-12-09T11:49:52.74+01:00	O	Unknown
412	2819	169241	sal.27.22-31	Salmon (Salmo salar) in subdivisions 22-31 (Baltic Sea, excluding the Gulf of Finland)	136678	sal-2431	2021	Salmo salar	Salmon	Baltic Sea	WGBAST	Assessment Working Group on Baltic Salmon and Trout	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2021	1	2022	Analytical model, Bayesian state-space model for a majority of rivers in AUs 1–4	NA	MSY	Catches	Assessment	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:32.917+01:00	8.3.15	NA	2021-10-29T11:41:04.66+01:00	O	Unknown
448	2856	169095	ele.2737.nea	European eel (Anguilla anguilla) throughout its natural range	136527	ele-nea	2021	Anguilla anguilla	Eel	Arctic Ocean, Azores, Bay of Biscay and the Iberian Coast, Barents Sea, Baltic Sea, Celtic Seas, Faroes, Greenland Sea, Icelandic Waters, Greater North Sea, Norwegian Sea, Oceanic Northeast Atlantic	WGEEL	Joint EIFAAC/ICES/GFCM Working Group on Eels	ADGEEL	Eel Advice Drafting GRoup	3.14	2021	1	2022	Trends-based assessment	NA	PA	No directed fisheries	Not used	No	demersal benthivore	Demersal	medium demersal	TRUE	2025-01-02T18:29:32.917+01:00	9.3.13	16876	2021-11-11T11:41:40.003+01:00	NA	NA
449	2857	169242	sal.27.32	Salmon (Salmo salar) in Subdivision 32 (Gulf of Finland)	136679	sal-32	2021	Salmo salar	Salmon	Baltic Sea	WGBAST	Assessment Working Group on Baltic Salmon and Trout	RG/ADGSalmon	Salmon Review and Advice Drafting Group	3	2021	1	2022	Qualitative assessment based on monitored parr densities and expert judgement.	NA	PA	Catches	Topped up	No	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:32.917+01:00	8.3.16	NA	2021-12-10T11:13:59.86+01:00	O	Unknown
498	2906	169291	trs.27.22-32	Sea trout (Salmo trutta) in subdivisions 22-32 (Baltic Sea)	136729	trt-bal	2021	Salmo trutta	Sea trout	Baltic Sea	WGBAST	Assessment Working Group on Baltic Salmon and Trout	NA	NA	4	2021	2	2023	Evaluation of the relative status of stocks by comparing the observed parr densities to model-predicted maximal parr densities.	NA	PA	Reduction	Not used	No	demersal piscivore	Pelagic	large pelagic	TRUE	2025-01-02T18:29:32.917+01:00	8.3.18	NA	2021-12-10T11:12:39.54+01:00	NA	NA
520	2929	199793	sal.neac.all	Salmon (Salmo salar) in Northeast Atlantic and Arctic Ocean	NA	NA	2021	Salmo salar	Salmon	Arctic Ocean, Azores, Bay of Biscay and the Iberian Coast, Barents Sea, Celtic Seas, Faroes, Greenland Sea, Icelandic Waters, Greater North Sea, Norwegian Sea, Oceanic Northeast Atlantic	WGNAS	Working Group on North Atlantic Salmon	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2021	1	2022	Analytical model, run-reconstruction models and Bayesian forecasts, taking into account uncertainties in data and process error (results presented in a risk analysis framework)	NA	MSY	Catches	Not used	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:32.917+01:00	10.3.3	NA	2021-10-29T11:46:05.567+01:00	O	Unknown
521	2930	199794	sal.nac.all	Salmon (Salmo salar) from North America	NA	NA	2021	Salmo salar	Salmon	Greenland Sea, Oceanic Northeast Atlantic	WGNAS	Working Group on North Atlantic Salmon	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2021	1	2022	Analytical model, run-reconstruction models and Bayesian forecasts, taking into account uncertainties in the data	NA	MSY/PA	Catches	Not used	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:32.917+01:00	10.3.1	NA	2021-10-29T11:44:11.297+01:00	O	Unknown
522	2931	199795	sal.wgc.all	Salmon (Salmo salar) in Subarea 14 and NAFO division 1 (east and west of Greenland)	NA	NA	2021	Salmo salar	Salmon	Greenland Sea, Oceanic Northeast Atlantic	WGNAS	Working Group on North Atlantic Salmon	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2021	3	2024	Analytical model, run reconstruction models and Bayesian forecasts, taking into account uncertainties in the data	NA	MSY	Catches	Not used	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:32.917+01:00	10.3.2	NA	2021-10-29T11:47:18.467+01:00	O	Unknown
677	3058	169241	sal.27.22-31	Salmon (Salmo salar) in subdivisions 22-31 (Baltic Sea, excluding the Gulf of Finland)	136678	sal-2431	2022	Salmo salar	Salmon	Baltic Sea	WGBAST	Assessment Working Group on Baltic Salmon and Trout	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2021	1	2022	Analytical model, Bayesian state-space model for a majority of rivers in AUs 1–4	NA	MSY	Catches	Assessment	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:33.7+01:00	8.3.15	NA	2021-10-29T11:41:04.66+01:00	O	Unknown
713	3100	169095	ele.2737.nea	European eel (Anguilla anguilla) throughout its natural range	136527	ele-nea	2022	Anguilla anguilla	Eel	Arctic Ocean, Azores, Bay of Biscay and the Iberian Coast, Barents Sea, Baltic Sea, Celtic Seas, Faroes, Greenland Sea, Icelandic Waters, Greater North Sea, Norwegian Sea, Oceanic Northeast Atlantic	WGEEL	Joint EIFAAC/ICES/GFCM Working Group on Eels	ADGEEL	Eel Advice Drafting GRoup	3.14	2022	1	2023	Trends-based assessment	NA	PA	No directed fisheries	Not used	No	demersal benthivore	Demersal	medium demersal	TRUE	2025-01-02T18:29:33.7+01:00	9.3.13	17615	2023-01-03T10:52:52.937+01:00	NA	NA
714	3101	169242	sal.27.32	Salmon (Salmo salar) in Subdivision 32 (Gulf of Finland)	136679	sal-32	2022	Salmo salar	Salmon	Baltic Sea	WGBAST	Assessment Working Group on Baltic Salmon and Trout	RG/ADGSalmon	Salmon Review and Advice Drafting Group	3	2021	1	2022	Qualitative assessment based on monitored parr densities and expert judgement.	NA	PA	Catches	Topped up	No	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:33.7+01:00	8.3.16	NA	2021-12-10T11:13:59.86+01:00	O	Unknown
763	3144	169291	trs.27.22-32	Sea trout (Salmo trutta) in subdivisions 22-32 (Baltic Sea)	136729	trt-bal	2022	Salmo trutta	Sea trout	Baltic Sea	WGBAST	Assessment Working Group on Baltic Salmon and Trout	NA	NA	4	2021	2	2023	Evaluation of the relative status of stocks by comparing the observed parr densities to model-predicted maximal parr densities.	NA	PA	Reduction	Not used	No	demersal piscivore	Pelagic	large pelagic	TRUE	2025-01-02T18:29:33.7+01:00	8.3.18	NA	2023-01-05T10:54:51.217+01:00	NA	NA
785	3167	199793	sal.neac.all	Salmon (Salmo salar) in Northeast Atlantic and Arctic Ocean	NA	NA	2022	Salmo salar	Salmon	Arctic Ocean, Azores, Bay of Biscay and the Iberian Coast, Barents Sea, Celtic Seas, Faroes, Greenland Sea, Icelandic Waters, Greater North Sea, Norwegian Sea, Oceanic Northeast Atlantic	WGNAS	Working Group on North Atlantic Salmon	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2021	1	2022	Analytical model, run-reconstruction models and Bayesian forecasts, taking into account uncertainties in data and process error (results presented in a risk analysis framework)	NA	MSY	Catches	Not used	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:33.7+01:00	10.3.3	NA	2021-10-29T11:46:05.567+01:00	O	Unknown
786	3168	199794	sal.nac.all	Salmon (Salmo salar) from North America	NA	NA	2022	Salmo salar	Salmon	Greenland Sea, Oceanic Northeast Atlantic	WGNAS	Working Group on North Atlantic Salmon	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2021	1	2022	Analytical model, run-reconstruction models and Bayesian forecasts, taking into account uncertainties in the data	NA	MSY/PA	Catches	Not used	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:33.7+01:00	10.3.1	NA	2021-10-29T11:44:11.297+01:00	O	Unknown
787	3169	199795	sal.wgc.all	Salmon (Salmo salar) in Subarea 14 and NAFO division 1 (east and west of Greenland)	NA	NA	2022	Salmo salar	Salmon	Greenland Sea, Oceanic Northeast Atlantic	WGNAS	Working Group on North Atlantic Salmon	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2021	3	2024	Analytical model, run reconstruction models and Bayesian forecasts, taking into account uncertainties in the data	NA	MSY	Catches	Not used	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:33.7+01:00	10.3.2	NA	2021-10-29T11:47:18.467+01:00	O	Unknown
944	3279	169241	sal.27.22-31	Salmon (Salmo salar) in subdivisions 22-31 (Baltic Sea, excluding the Gulf of Finland)	136678	sal-2431	2023	Salmo salar	Salmon	Baltic Sea	WGBAST	Assessment Working Group on Baltic Salmon and Trout	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2023	1	2024	Analytical model, Bayesian state-space model for a majority of rivers in AUs 1–4	NA	MSY	Catches	Assessment	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:34.423+01:00	8.3.15	NA	2023-05-31T13:58:57.367+01:00	B	Unknown
980	3320	169095	ele.2737.nea	European eel (Anguilla anguilla) throughout its natural range	136527	ele-nea	2023	Anguilla anguilla	Eel	Arctic Ocean, Bay of Biscay and the Iberian Coast, Celtic Seas, Greater North Sea, Oceanic Northeast Atlantic	WGEEL	Joint EIFAAC/ICES/GFCM Working Group on Eels	ADGEEL	Eel Advice Drafting GRoup	3.14	2022	1	2023	Trends-based assessment	NA	PA	No directed fisheries	Not used	No	demersal benthivore	Demersal	medium demersal	TRUE	2025-01-02T18:29:34.423+01:00	9.3.13	18459	2023-11-14T09:05:55.627+01:00	NA	NA
981	3321	169242	sal.27.32	Salmon (Salmo salar) in Subdivision 32 (Gulf of Finland)	136679	sal-32	2023	Salmo salar	Salmon	Baltic Sea	WGBAST	Assessment Working Group on Baltic Salmon and Trout	RG/ADGSalmon	Salmon Review and Advice Drafting Group	3	2023	1	2024	Qualitative assessment based on monitored parr densities and expert judgement.	NA	PA	Catches	Topped up	No	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:34.423+01:00	8.3.16	NA	2023-11-14T09:59:06.097+01:00	O	Unknown
1029	3364	169291	trs.27.22-32	Sea trout (Salmo trutta) in subdivisions 22-32 (Baltic Sea)	136729	trt-bal	2023	Salmo trutta	Sea trout	Baltic Sea	WGBAST	Assessment Working Group on Baltic Salmon and Trout	NA	NA	4	2023	2	2025	Evaluation of the relative status of stocks by comparing the observed parr densities to model-predicted maximal parr densities.	NA	PA	Reduction	Not used	No	demersal piscivore	Pelagic	large pelagic	TRUE	2025-01-02T18:29:34.423+01:00	8.3.18	NA	2023-05-31T14:05:23.573+01:00	NA	NA
1051	3431	199793	sal.neac.all	Salmon (Salmo salar) in Northeast Atlantic and Arctic Ocean	NA	NA	2023	Salmo salar	Salmon	Arctic Ocean, Azores, Bay of Biscay and the Iberian Coast, Barents Sea, Celtic Seas, Faroes, Greenland Sea, Icelandic Waters, Greater North Sea, Norwegian Sea, Oceanic Northeast Atlantic	WGNAS	Working Group on North Atlantic Salmon	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2021	3	2024	Analytical model, run-reconstruction models and Bayesian forecasts, taking into account uncertainties in data and process error (results presented in a risk analysis framework)	NA	MSY	Catches	Not used	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:34.423+01:00	10.3.3	NA	2023-10-10T09:13:51.963+01:00	O	Unknown
1052	3432	199794	sal.nac.all	Salmon (Salmo salar) from North America	NA	NA	2023	Salmo salar	Salmon	Greenland Sea, Oceanic Northeast Atlantic	WGNAS	Working Group on North Atlantic Salmon	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2021	3	2024	Analytical model, run-reconstruction models and Bayesian forecasts, taking into account uncertainties in the data	NA	MSY	Catches	Not used	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:34.423+01:00	10.3.1	NA	2023-10-10T09:09:42.107+01:00	O	Unknown
1053	3433	199795	sal.wgc.all	Salmon (Salmo salar) in Subarea 14 and NAFO division 1 (east and west of Greenland)	NA	NA	2023	Salmo salar	Salmon	Greenland Sea, Oceanic Northeast Atlantic	WGNAS	Working Group on North Atlantic Salmon	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2021	3	2024	Analytical model, run reconstruction models and Bayesian forecasts, taking into account uncertainties in the data	NA	MSY	Catches	Not used	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:34.423+01:00	10.3.2	NA	2021-10-29T11:47:18.467+01:00	O	Unknown
1209	3561	169241	sal.27.22-31	Salmon (Salmo salar) in subdivisions 22-31 (Baltic Sea, excluding the Gulf of Finland)	136678	sal-2431	2024	Salmo salar	Salmon	Baltic Sea	WGBAST	Assessment Working Group on Baltic Salmon and Trout	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2023	1	2024	Analytical model, Bayesian state-space model for a majority of rivers in AUs 1–4	NA	MSY	Catches	Assessment	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:35.133+01:00	8.3.15	NA	2024-01-11T09:30:20.893+01:00	B	Unknown
1245	3601	169095	ele.2737.nea	European eel (Anguilla anguilla) throughout its natural range	136527	ele-nea	2024	Anguilla anguilla	Eel	Arctic Ocean, Bay of Biscay and the Iberian Coast, Celtic Seas, Greater North Sea, Oceanic Northeast Atlantic	WGEEL	Joint EIFAAC/ICES/GFCM Working Group on Eels	ADGEEL	Eel Advice Drafting GRoup	3.14	2022	1	2023	Trends-based assessment	NA	PA	No directed fisheries	Not used	No	demersal benthivore	Demersal	medium demersal	TRUE	2025-01-02T18:29:35.133+01:00	9.3.13	NA	2024-09-25T14:21:32.487+01:00	NA	NA
1246	3602	169242	sal.27.32	Salmon (Salmo salar) in Subdivision 32 (Gulf of Finland)	136679	sal-32	2024	Salmo salar	Salmon	Baltic Sea	WGBAST	Assessment Working Group on Baltic Salmon and Trout	RG/ADGSalmon	Salmon Review and Advice Drafting Group	3	2023	1	2024	Qualitative assessment based on monitored parr densities and expert judgement.	NA	PA	Catches	Topped up	No	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:35.133+01:00	8.3.16	19019	2024-01-11T10:26:17.923+01:00	O	Unknown
1294	3645	169291	trs.27.22-32	Sea trout (Salmo trutta) in subdivisions 22-32 (Baltic Sea)	136729	trt-bal	2024	Salmo trutta	Sea trout	Baltic Sea	WGBAST	Assessment Working Group on Baltic Salmon and Trout	NA	NA	4	2023	2	2025	Evaluation of the relative status of stocks by comparing the observed parr densities to model-predicted maximal parr densities.	NA	PA	Reduction	Not used	No	demersal piscivore	Pelagic	large pelagic	TRUE	2025-01-02T18:29:35.133+01:00	8.3.18	NA	2024-01-11T09:08:41.113+01:00	NA	NA
1317	3709	199793	sal.neac.all	Salmon (Salmo salar) in Northeast Atlantic and Arctic Ocean	NA	NA	2024	Salmo salar	Salmon	Arctic Ocean, Azores, Bay of Biscay and the Iberian Coast, Barents Sea, Celtic Seas, Faroes, Greenland Sea, Icelandic Waters, Greater North Sea, Norwegian Sea, Oceanic Northeast Atlantic	WGNAS	Working Group on North Atlantic Salmon	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2021	3	2024	Analytical model, run-reconstruction models and Bayesian forecasts, taking into account uncertainties in data and process error (results presented in a risk analysis framework)	NA	MSY	Catches	Not used	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:35.133+01:00	10.3.3	NA	2023-10-10T09:13:51.963+01:00	O	Unknown
1318	3710	199794	sal.nac.all	Salmon (Salmo salar) from North America	NA	NA	2024	Salmo salar	Salmon	Greenland Sea, Oceanic Northeast Atlantic	WGNAS	Working Group on North Atlantic Salmon	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2021	3	2024	Analytical model, run-reconstruction models and Bayesian forecasts, taking into account uncertainties in the data	NA	MSY	Catches	Not used	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:35.133+01:00	10.3.1	NA	2023-10-10T09:09:42.107+01:00	O	Unknown
1319	3711	199795	sal.wgc.all	Salmon (Salmo salar) in Subarea 14 and NAFO division 1 (east and west of Greenland)	NA	NA	2024	Salmo salar	Salmon	Greenland Sea, Oceanic Northeast Atlantic	WGNAS	Working Group on North Atlantic Salmon	RG/ADGSalmon	Salmon Review and Advice Drafting Group	1	2021	3	2024	Analytical model, run reconstruction models and Bayesian forecasts, taking into account uncertainties in the data	NA	MSY	Catches	Not used	NA	pelagic piscivore	Pelagic	large benthopelagic	TRUE	2025-01-02T18:29:35.133+01:00	10.3.2	NA	2021-10-29T11:47:18.467+01:00	O	Unknown

The code for creating metadata is listed below Listing 2

SQL code to create tables

Listing 2: The big metadata referential and small referential tables to support metadata building

--| echo: TRUE
--| eval: FALSE


-- Note this code is plain SQL because when there is a lot to code
-- it's faster. It cannot be executed from a quarto chunk so look
-- at SQL/tr_metadata_met.sql for executable sql code. Still we keep 
-- it there to show what we do.

DROP TABLE IF EXISTS ref.tr_objecttype_oty CASCADE;
CREATE TABLE ref.tr_objecttype_oty (
oty_code TEXT PRIMARY KEY,
oty_description TEXT
);

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

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

COMMENT ON COLUMN ref.tr_objecttype_oty.oty_code IS 
'code of the object type, single_value, vector, ...';

COMMENT ON COLUMN ref.tr_objecttype_oty.oty_code IS 'description of the object type';
GRANT ALL ON ref.tr_objecttype_oty TO diaspara_admin;
GRANT SELECT ON ref.tr_objecttype_oty TO diaspara_read;
--nimble

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

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

-- 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
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),
ver_stockkey INTEGER NOT NULL,
ver_stockkeylabel TEXT NOT NULL,
ver_stockadvicedoi TEXT NOT NULL,
ver_datacalldoi TEXT NULL,
ver_version INTEGER NOT NULL,
ver_description TEXT,
CONSTRAINT c_fk_ver_spe_code FOREIGN KEY (ver_spe_code) 
REFERENCES ref.tr_species_spe(spe_code)
ON UPDATE CASCADE ON DELETE CASCADE
);
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_version 
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. ''ele'' references tr_species_spe.';
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;
-- metric 

DROP TABLE IF EXISTS  ref.tr_metric_mtr CASCADE;
CREATE TABLE ref.tr_metric_mtr(
mtr_code TEXT PRIMARY KEY,
mtr_description TEXT
);


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

GRANT ALL ON ref.tr_metric_mtr TO diaspara_admin;
GRANT SELECT ON ref.tr_metric_mtr TO diaspara_read;
COMMENT ON TABLE ref.tr_metric_mtr IS 
'Table metric describe the possible values of parameters, Index, Bound ...';

-- 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 ...');

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;

DROP TABLE IF EXISTS ref.tr_outcome_oco CASCADE;
CREATE TABLE ref.tr_outcome_oco (
oco_code TEXT PRIMARY KEY,
oco_description TEXT
);

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


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





DROP TABLE IF EXISTS ref.tr_metadata_met CASCADE;
CREATE TABLE ref.tr_metadata_met (
  met_var TEXT NOT NULL,
  met_spe_code character varying(3) NOT  NULL,
  met_wkg_code TEXT NOT NULL,
  met_ver_code TEXT NULL,
  met_oty_code TEXT NOT NULL,
  met_nim_code TEXT NOT NULL,
  met_dim integer ARRAY,
  met_dimname TEXT ARRAY,
  met_modelstage TEXT NULL, 
  met_type TEXT NULL,
  met_location TEXT NULL,
  met_fishery TEXT NULL,
  met_mtr_code TEXT NULL,
  met_oco_code TEXT NULL,
  met_uni_code TEXT NULL,
  met_cat_code TEXT NULL,
  met_definition TEXT NULL, 
  met_deprecated BOOLEAN DEFAULT FALSE,
  CONSTRAINT c_pk_met_var_met_lfs_code PRIMARY KEY(met_var, met_spe_code),
  CONSTRAINT c_fk_met_spe_code FOREIGN KEY (met_spe_code)
  REFERENCES ref.tr_species_spe(spe_code) 
  ON DELETE CASCADE
  ON UPDATE CASCADE,
    CONSTRAINT c_fk_met_wkg_code FOREIGN KEY (met_wkg_code)
  REFERENCES ref.tr_icworkinggroup_wkg(wkg_code) 
  ON DELETE CASCADE
  ON UPDATE CASCADE,
  CONSTRAINT c_fk_met_ver_code FOREIGN KEY (met_ver_code)
  REFERENCES ref.tr_version_ver(ver_code) 
  ON DELETE CASCADE
  ON UPDATE CASCADE,
  CONSTRAINT c_fk_met_oty_code FOREIGN KEY (met_oty_code) 
  REFERENCES ref.tr_objecttype_oty (oty_code) ON DELETE CASCADE
  ON UPDATE CASCADE,
  CONSTRAINT c_fk_met_nim_code FOREIGN KEY (met_nim_code) 
  REFERENCES ref.tr_nimble_nim (nim_code) ON DELETE CASCADE
  ON UPDATE CASCADE,  
  CONSTRAINT c_fk_met_mtr_code FOREIGN KEY (met_mtr_code)
  REFERENCES ref.tr_metric_mtr(mtr_code)
  ON DELETE CASCADE
  ON UPDATE CASCADE,
  CONSTRAINT c_fk_met_uni_code FOREIGN KEY (met_uni_code)
  REFERENCES ref.tr_units_uni(uni_code)
  ON DELETE CASCADE
  ON UPDATE CASCADE,
  CONSTRAINT c_fk_met_cat_code FOREIGN KEY (met_cat_code)
  REFERENCES ref.tr_category_cat(cat_code)
  ON DELETE CASCADE
  ON UPDATE CASCADE,
  CONSTRAINT c_fk_met_oco_code FOREIGN KEY (met_oco_code)
  REFERENCES ref.tr_outcome_oco(oco_code)
  ON DELETE CASCADE
  ON UPDATE CASCADE
);
COMMENT ON TABLE ref.tr_metadata_met IS 
'Table (metadata) of each variable (parameter) in the database.';
COMMENT ON COLUMN ref.tr_metadata_met.met_var 
IS 'Variable code, primary key on both met_spe_code and met_var.';
COMMENT ON COLUMN ref.tr_metadata_met.met_spe_code 
IS 'Species, ANG, SAL, TRT ... primary key on both met_spe_code and met_var.';
COMMENT ON COLUMN ref.tr_metadata_met.met_ver_code 
IS 'Code on the version of the model, see table tr_version_ver.';
COMMENT ON COLUMN ref.tr_metadata_met.met_oty_code 
IS 'Object type, single_value, vector, matrix see table tr_objecttype_oty.';
COMMENT ON COLUMN ref.tr_metadata_met.met_nim_code 
IS 'Nimble type, one of data, constant, output, other.';
COMMENT ON COLUMN ref.tr_metadata_met.met_dim 
IS 'Dimension of the Nimble variable, use {10, 100, 100} 
to insert the description of an array(10,100,100).';
COMMENT ON COLUMN ref.tr_metadata_met.met_dimname 
IS 'Dimension of the variable in Nimble, use {''year'', ''stage'', ''area''}.';
COMMENT ON COLUMN ref.tr_metadata_met.met_modelstage 
IS 'Currently one of fit, other, First year.';
COMMENT ON COLUMN ref.tr_metadata_met.met_type 
IS 'Type of data in the variable, homewatercatches, InitialISation first year,
abundance ....';
COMMENT ON COLUMN ref.tr_metadata_met.met_location 
IS 'Describe process at sea, e.g. Btw. FAR - GLD fisheries, or Aft. Gld fISheries.';
COMMENT ON COLUMN ref.tr_metadata_met.met_fishery 
IS 'Description of the fishery.';
COMMENT ON COLUMN ref.tr_metadata_met.met_oco_code 
IS 'Outcome of the fish, e.g. Released (alive), Seal damage,
Removed (from the environment), references table tr_outcome_oco.';
COMMENT ON COLUMN ref.tr_metadata_met.met_uni_code 
IS 'Unit, references table tr_unit_uni.';
COMMENT ON COLUMN ref.tr_metadata_met.met_cat_code 
IS 'Broad category of data or parameter, 
catch, effort, biomass, mortality, count ...references table tr_category_cat.';
COMMENT ON COLUMN ref.tr_metadata_met.met_mtr_code 
IS 'Code of the metric, references tr_metric_mtr, Estimate, Bound, SD, CV ....';
COMMENT ON COLUMN ref.tr_metadata_met.met_definition 
IS 'Definition of the metric.';
COMMENT ON COLUMN ref.tr_metadata_met.met_deprecated
IS'Is the variable still used ?';

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

DIASPARA

Not yet completely sure about this …. Environment (sea, transition …), age, life stage and complex are in the metadata. But they are also in the main table. To start with, we remove them to reduce complexity and avoid errors. The complex will be derived from the spatial structure still in construction

DIASPARA

As in the diagram, added a category (data type in figure Figure 3). The idea is to be able to get quickly all parameters related to a type, e.g. catch, mortality, biomass. Please check and also check definitions.

DIASPARA

WGBAST, WGNAS, WGEEL, WGTRUTTA will have to check definitions in tr_outcome. Note this is just a column in tr_metadata_met not in the main table.

Object type (tr_objectype_oty)

This table (Table 6) is used in metadata

Table 6: Object type

oty_code	oty_description
single_value	Single value
vector	One dimension vector
matrix	Two dimensions matrix
array	Three dimensions array

Type of parm / data (tr_nimble_nim)

In the salmoglob db, this table (Table 7) corresponded to both tables status and nimble which most often contained the same information.

Table 7: Nimble

nim_code	nim_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

version (tr_version_ver)

Currently in the salmoglob metadata there is no information about version. The version number is in the table itself. We will keep it that way but it seems to me that metadata should also contain information about historical variables. For instance we create a new variable, so we know when it was introduced. So I’m moving this from the main table and adding to metadata Some variables might get deprecated over time. The year will be the year when the variable was introduced. The version (Table 8) contains both reference to the datacall (when data are loaded) and to the advice. The advice might still be null at the time the values will be entered into the database.

Table 8: Version

ver_code	ver_year	ver_spe_code	ver_stockkey	ver_stockkeylabel	ver_stockadvicedoi	ver_datacalldoi	ver_version	ver_description
NA	NA	NA	NA	NA	NA	NA	NA	NA
:--------	--------:	:------------	------------:	:-----------------	:------------------	:---------------	-----------:	:---------------

QUESTION ICES: What is the vocabulary for datacalls

I would like to access to this table : datacall (see link in ICES webpage). Currently we see the current year, this is nice, how do we access to historical data, is there a way to get it using a query ? We’ve found a link for advice or stocks but not data calls.

Metric (tr_metric_mtr)

Table 9: Metric, type of parm used in the model

mtr_code	mtr_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 is probably incomplete. But the metric can be NULL in case of a number of fish released, not of the above (Table 9) will apply.

Category (tr_category_cat)

categories Table 10 were in the salmoglob metadata, we have simplified to reduce the number of categories and be able to get for instance all parameters dealing with catch.

Table 10: 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 rat
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 ...

Outcome (tr_outcome_oco)

A bit cumbersome this table, the idea is “what becomes of this fish”. Different types of landings. But it’s just in metatdata and we need need in WGBAST

Table 11: category of parameters

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

Metadata (tr_metadata_met)

Table 12: metadata

met_var	met_spe_code	met_wkg_code	met_ver_code	met_oty_code	met_nim_code	met_dim	met_dimname	met_modelstage	met_type	met_location	met_fishery	met_mtr_code	met_oco_code	met_uni_code	met_cat_code	met_definition	met_deprecated
NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA
:-------	:------------	:------------	:------------	:------------	:------------	:-------	:-----------	:--------------	:--------	:------------	:-----------	:------------	:------------	:------------	:------------	:--------------	:--------------

Areas tr_area_are

Here I’m trying to build something consistent. I guess only the final result will tell but I have to start somewhere …

dbExecute(con_diaspara_admin,
"DROP TABLE IF EXISTS ref.tr_level_lev CASCADE;")

dbExecute(con_diaspara_admin,
"CREATE TABLE ref.tr_level_lev(
   lev_code TEXT PRIMARY KEY,
   lev_description TEXT  
);")

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

dbExecute(con_diaspara_admin,
  "INSERT INTO ref.tr_level_lev VALUES( 
  '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.'  
  );")

dbExecute(con_diaspara_admin,
  "INSERT INTO ref.tr_level_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_level_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_level_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_level_lev VALUES( 
  'river',
  'One river is a unit corresponding practically almost always to a watershed.'
  );")

dbExecute(con_diaspara_admin,
  "INSERT INTO ref.tr_level_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, "COMMENT ON TABLE ref.tr_level_lev 
IS 'Table of geographic levels stock, complex, country, region, basin, river,
the specific order depend according to working groups.';")

dbExecute(con_diaspara_admin, "GRANT ALL ON ref.tr_level_lev 
          TO diaspara_admin;")
dbExecute(con_diaspara_admin, "GRANT SELECT ON ref.tr_level_lev 
          TO diaspara_read;")


dbExecute(con_diaspara_admin,
"DROP TABLE IF EXISTS ref.tr_area_are CASCADE;")
dbExecute(con_diaspara_admin,
"CREATE TABLE ref.tr_area_are (
   are_id INTEGER PRIMARY KEY,
   are_are_id INTEGER,
   are_code  TEXT,
   are_lev_code TEXT,
   geom geometry(MULTIPOLYGON, 4326),
  CONSTRAINT c_fk_are_are_id FOREIGN KEY (are_are_id) 
  REFERENCES ref.tr_area_are (are_id) ON DELETE CASCADE
  ON UPDATE CASCADE,
  CONSTRAINT c_uk_are_code UNIQUE (are_code),
  CONSTRAINT c_cf_area_lev_code FOREIGN KEY (are_lev_code) REFERENCES
  ref.tr_level_lev(lev_code) ON UPDATE CASCADE ON DELETE CASCADE
);")

dbExecute(con_diaspara_admin, "GRANT ALL ON ref.tr_area_are 
          TO diaspara_admin;")
dbExecute(con_diaspara_admin, "GRANT SELECT ON ref.tr_area_are 
          TO diaspara_read;")

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

Table 13: Geographical level tr_level_lev

lev_code	lev_description
stock	This is the highest geographic level for assessement, stock level.
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 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 leve
river	One river is a unit corresponding practically almost always to a watershed.
river_section	Section of river, only a part of a basin, for instance to separate between wild and mixed river category in the Baltic.

Table 14: Geographic areas

are_id	are_are_id	are_code	are_lev_code	geom
NA	NA	NA	NA	NA
------:	----------:	:--------	:------------	:----

Figure 4: The nested structure layed out in table tr_area_area, dotted box indicate that the level is optional. This table will be created specifically for each group.

Data access (tr_dataaccess_dta)

Type of data Public, or Restricted

dbExecute(con_diaspara_admin,
"DROP TABLE IF EXISTS ref.tr_dataaccess_dta CASCADE;")

dbExecute(con_diaspara_admin,
"CREATE TABLE ref.tr_dataaccess_dta(
   dta_code TEXT PRIMARY KEY,
   dta_description TEXT  
);")


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

dbExecute(con_diaspara_admin, "GRANT ALL ON ref.tr_dataaccess_dta 
          TO diaspara_admin;")
dbExecute(con_diaspara_admin, "GRANT SELECT ON ref.tr_dataaccess_dta 
          TO diaspara_read;")

dbExecute(con_diaspara_admin, "COMMENT ON TABLE ref.tr_dataaccess_dta 
IS 'Table with two values, Public or Restricted access.';")

Missing data (tr_missvalueqal_mis)

This comes from wgeel.

dbExecute(con_diaspara_admin,
"DROP TABLE IF EXISTS ref.tr_missvalueqal_mis CASCADE;")

dbExecute(con_diaspara_admin,
"CREATE TABLE ref.tr_missvalueqal_mis(
   mis_code TEXT PRIMARY KEY,
   mis_description TEXT NOT NULL,  
   mis_definition TEXT);")

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).';")

dbExecute(con_diaspara_admin, "GRANT ALL ON ref.tr_missvalueqal_mis 
          TO diaspara_admin;")
dbExecute(con_diaspara_admin, "GRANT SELECT ON ref.tr_missvalueqal_mis 
          TO diaspara_read;")

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

Table 15: 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).

WORK IN PROGRESS….

This document is still in construction

Acknowledgements

• Data source : EuroGeographics and UN-FAO for countries