check observation based on current species name

[bpasquer]

restore exclusion of superseded species

[bpasquer]

An auto test case? To test I ran :

with a as(
select
    oi.observable_item_id as species_id,
    oi.observable_item_name as recorded_species_name,
    coalesce(oi.superseded_by, oi.observable_item_name) as species_name,
    oi.taxon,
    oi.reporting_name,
    oi.phylum,
    oi.class,
    oi."order",
    oi.family,
    oi.genus,
    oi.common_name,
    oi.range,
    oi.frequency,
    oi.abundance,
    oi.max_length,
    common_family_name,
    common_class_name,
    common_phylum_name,
    null as geom,
    oi.superseded_ids,
    oi.superseded_names,
    oi.mapped_id
from nrmn.ep_observable_items oi
where oi.obs_item_type_name in ('Species', 'Undescribed Species')
and exists (select 1 from nrmn.observation obs
         join nrmn.observable_item_ref oir on obs.observable_item_id = oir.observable_item_id
    where observable_item_name = coalesce(oir.superseded_by, oir.observable_item_name))
and oi.superseded_by is NULL)
select * from a where a.species_name='Morwong fuscus';

which should return one row only (currently without the join nrmn.observable_item_ref oir on obs.observable_item_id = oir.observable_item_id you get no results)

[utas-raymondng]

An auto test case? To test I ran :

with a as(
select
  oi.observable_item_id as species_id,
  oi.observable_item_name as recorded_species_name,
  coalesce(oi.superseded_by, oi.observable_item_name) as species_name,
  oi.taxon,
  oi.reporting_name,
  oi.phylum,
  oi.class,
  oi."order",
  oi.family,
  oi.genus,
  oi.common_name,
  oi.range,
  oi.frequency,
  oi.abundance,
  oi.max_length,
  common_family_name,
  common_class_name,
  common_phylum_name,
  null as geom,
  oi.superseded_ids,
  oi.superseded_names,
  oi.mapped_id
from nrmn.ep_observable_items oi
where oi.obs_item_type_name in ('Species', 'Undescribed Species')
and exists (select 1 from nrmn.observation obs
         join nrmn.observable_item_ref oir on obs.observable_item_id = oir.observable_item_id
    where observable_item_name = coalesce(oir.superseded_by, oir.observable_item_name))
and oi.superseded_by is NULL)
select * from a where a.species_name='Morwong fuscus';

which should return one row only (currently without the join nrmn.observable_item_ref oir on obs.observable_item_id = oir.observable_item_id you get no results)

I mean when the program build, test case will run together to make sure changes do not impact existing code behavior. So it would be like some insert statement to create sample data, run the sql script to create view and then execute it then check the return result is the same as expected.

If nothing created like this before, you can give me the sample data in the db, and the expected result and some negative sample for negative result and pass this ticket to me in the upcoming iteration.

[bpasquer]

@utas-raymondng no, we never created auto test / unit tests for DB update, just ensured scripts were running with no error.
The test is to verify that the querying the view returns a results for a superseding species with no observation attached ('Ostorhinchus doederleini'). The result should not return a superseded species('Apogon doederleini' ).

The test is :

select
    oi.observable_item_id as species_id,
    oi.observable_item_name as recorded_species_name,
    coalesce(oi.superseded_by, oi.observable_item_name) as species_name,
    oi.taxon,
    oi.reporting_name,
    oi.phylum,
    oi.class,
    oi."order",
    oi.family,
    oi.genus,
    oi.common_name,
    oi.range,
    oi.frequency,
    oi.abundance,
    oi.max_length,
    common_family_name,
    common_class_name,
    common_phylum_name,
    null as geom,
    oi.superseded_ids,
    oi.superseded_names,
    oi.mapped_id
from nrmn.ep_observable_items oi
where oi.obs_item_type_name in ('Species', 'Undescribed Species')
and exists (select 1 from nrmn.observation obs
         join nrmn.observable_item_ref oir on obs.observable_item_id = oir.observable_item_id
    where observable_item_name = coalesce(oir.superseded_by, oir.observable_item_name))
and oi.superseded_by is NULL)

select * from a where a.species_name in ('Ostorhinchus doederleini', 'Apogon doederleini')  ;

The expected result is:	species_id	rcorded_species_name	species_name
8114	Ostorhinchus doederleini	Ostorhinchus doederleini

Does that work for you this way?

[utas-raymondng]

@utas-raymondng no, we never created auto test / unit tests for DB update, just ensured scripts were running with no error. The test is to verify that the querying the view returns a results for a superseding species with no observation attached ('Ostorhinchus doederleini'). The result should not return a superseded species('Apogon doederleini' ).

The test is :

select
  oi.observable_item_id as species_id,
  oi.observable_item_name as recorded_species_name,
  coalesce(oi.superseded_by, oi.observable_item_name) as species_name,
  oi.taxon,
  oi.reporting_name,
  oi.phylum,
  oi.class,
  oi."order",
  oi.family,
  oi.genus,
  oi.common_name,
  oi.range,
  oi.frequency,
  oi.abundance,
  oi.max_length,
  common_family_name,
  common_class_name,
  common_phylum_name,
  null as geom,
  oi.superseded_ids,
  oi.superseded_names,
  oi.mapped_id
from nrmn.ep_observable_items oi
where oi.obs_item_type_name in ('Species', 'Undescribed Species')
and exists (select 1 from nrmn.observation obs
         join nrmn.observable_item_ref oir on obs.observable_item_id = oir.observable_item_id
    where observable_item_name = coalesce(oir.superseded_by, oir.observable_item_name))
and oi.superseded_by is NULL)

select * from a where a.species_name in ('Ostorhinchus doederleini', 'Apogon doederleini')  ;

The expected result is: species_id rcorded_species_name species_name 8114 Ostorhinchus doederleini Ostorhinchus doederleini

Does that work for you this way?

I need the value in the table that this query use for example values in table

ep_observable_items, obs_item_type_name etc

Other than this positive result, it would be great if we have another test of values such that we can produce result, for cases without superseded, and / or without attributes

[bpasquer]

@utas-raymondng Ok, sorry I didn't quite understand how you wanted me to give the data. I would be interested to see how you build your test. Here is data for :

• a superseding species with no observation : Ostorhinchus doederleini
• a superseding species with observation: Acanthostracion polygonius
• a superseded species with observation: Apogon doederleini
• a superseded species with no observation: Cypraea annulus
• a species with no superseding, not superseded, with observation: Arenigobius frenatus
• a species with no superseding, not superseded, no observation: Paciocinebrina lurida

observable_item_id	observable_item_name	superseded_by	obs_item_type_name	taxon	reporting_name	phylum	class	order	family	genus	common_name	range	frequency	abundance	max_length	superseded_ids	superseded_names	mapped_id	common_family_name	common_class_name	common_phylum_name	geom
8114	Ostorhinchus doederleini	null	Species	Ostorhinchus doederleini	Ostorhinchus doederleini	Chordata	Actinopterygii	Perciformes	Apogonidae	Ostorhinchus	Four lined cardinalfish	2458.04	11.95	21.91	null	810	Apogon doederleini	99908114	Cardinalfishes	Ray-finned fishes	Chordates	null
3762	Acanthostracion polygonius	null	Species	Acanthostracion polygonius	Acanthostracion polygonius	Chordata	Actinopterygii	Tetraodontiformes	Ostraciidae	Acanthostracion	Honeycomb cowfish	802.97	19.67	1.17	50	4765	Acanthostracion polygonia	4584	Boxfishes	Ray-finned fishes	Chordates	null
810	Apogon doederleini	Ostorhinchus doederleini	Species	Ostorhinchus doederleini	Ostorhinchus doederleini	Chordata	Actinopterygii	Perciformes	Apogonidae	Apogon	Four-line cardinalfish	null	null	bull	14	null	null	1438	Cardinalfishes	Ray-finned fishes	Chordates	null
2367	Arenigobius frenatus	null	Species	Arenigobius frenatus	Arenigobius frenatus	Chordata	Actinopterygii	Perciformes	Gobiidae	Arenigobius	Halfbridled Goby	10	100	1	18	null	null	3182	Gobies	Ray-finned fishes	Chordates	null
8119	Paciocinebrina lurida	null	Species	Paciocinebrina lurida	Paciocinebrina lurida	Mollusca	Gastropoda	Neogastropoda	Muricidae	Paciocinebrina	Lurid Rocksnail	null	null	null	null	null	null	99908119	null	Sea snails and slugs	Molluscs	null
4935	Cypraea annulus	Monetaria annulus	Species	Monetaria annulus	Monetaria annulus	Mollusca	Gastropoda	Littorinimorpha	Cypraeidae	null	null	null	null	null	null	null	null	5861	null	Sea snails and slugs	Molluscs	null

The following query:

select species_name from nrmn.ep_species_list where species_name in ('Ostorhinchus doederleini', 'Apogon doederleini','Acanthostracion polygonius','Arenigobius frenatus','Paciocinebrina lurida','Cypraea annulus');

should return this expected result:

Acanthostracion polygonius Ostorhinchus doederleini Arenigobius frenatus

Now the query I suggested above does not return the expected results have tried to fix it but haven't manage to resolve it so far

[bpasquer]

I have rectified the query, but now I need to update some of the auto test cause new data have been ingested.

[bpasquer]

The revised auto data for the auto test is attached, we have:

a superseding species with no observation : Capnella gaboensis (but the superseded id has observations) a superseding species with observation: Acanthostracion polygonius a superseded species with observation: Rhodymenia australis a superseded species with no observation: Cypraea annulus a species with no superseding, not superseded, with observation: Arenigobius frenatus a species with no superseding, not superseded, no observation:Tripneustes kermadecensis

the test should return: Capnella gaboensis Arenigobius frenatus Acanthostracion polygonius

DataForAutoTest.csv

[bpasquer]

Endpoint changes done and tested in https://github.com/aodn/nrmn-application/pull/1292 closing