BNTI env setup, full-automation

BNTi-components.md

Components (TODO: regenerate this table from a list of SHA/tags/releases)

repo	version	URL
nsl-domain-plugin	1.10	N/A
services	1.0204	https://dev42-icn.oztaxa.com/nxl/services/
nsl-editor	1.65	https://dev42-icn.oztaxa.com/nxl/editor/
mapper	1.0022	https://dev42-icn.oztaxa.com/nsl/mapper/

BNTi-icn_env.csv

BNTi-icn_load-data.sh

#!/bin/bash

# BEFORE running this script: sudo systemctl stop tomcat.service
#
# TODO: - this assumes /tmp/tblbiota_20180717.csv
#       - replace all hard-coded 'tblbiota_20180717.csv' with at least 'tblbiota.csv'
#       - parametrize/templatize the hard-coded hostname (for example: dev50-icn.oztaxa.com)
#       - etc

dropdb nsl && createdb nsl
psql -d nsl -c "create schema mapper;"

psql -f dawr_ddl.sql -d nsl

psql -f dawr_import.sql -d nsl

psql -f mapper-ddl.sql -d nsl
psql -d nsl -c "INSERT INTO mapper.db_version (id, version) VALUES (1, 5);"
psql -d nsl -c "INSERT INTO mapper.host (id, host_name, preferred) VALUES (1, 'dev51-icn.oztaxa.com/nxl/mapper', true);"

BNTi-testing.md

TOC

• check/verify all links
• TODO

check/verify all links

curl -s -L 'https://dev55-icn.oztaxa.com/nxl/services/search?product=BPNI&tree.id=&name=&inc._scientific=&inc.scientific=on&inc._cultivar=&inc._other=&max=7500&display=apni&search=true' > dev55-all-records_test.html

grep 'https://dev55-icn.oztaxa.com' ./dev55-all-records_test.html | wc -l
7222

# and the main test/verification, for each https://dev55-icn.oztaxa.com linke do:
curl -s -I -L 'https://dev55-icn.oztaxa.com/nxl/editor/search?query=id:40003844&query_field=name-instances&query_on=instance'
HTTP/2 302 
date: Tue, 07 Aug 2018 07:44:01 GMT
content-type: text/html;charset=utf-8
location: https://dev55-icn.oztaxa.com/nxl/editor/sign_in
x-frame-options: SAMEORIGIN
x-xss-protection: 1; mode=block
x-content-type-options: nosniff
cache-control: no-cache
set-cookie: _nsl_editor_session_test=WkY0WnFFbXFhcVdhSkdNWnRoNi9KZm1FQTVJK0pybnozYitiaWRwUG9FSjdEY1JScm50Q2hmOFF6YTdlMFFSeHVoMkdYUVUxa3daSDlaMkl6R3VUT3pZNGJJNFZQblJBYXdHbEZHVzkwdUorUzQ1NmpwY0NxWHdwWHBORi8vUDNvRG4xQzcxb0xpNHJHd05PUmxveWp5V3dGY2hyT2JjSXpRUkFScWowR2E5bytqRnBQbjFMYmgya2pFeUkwYVNYeDRqY2IzRm5sS3lPZXozaWdna3ZaZ2hsMzVOK0NLcDV4Nk9MOGFZSlNSUmNuclRjaUR0NldMQk9JTHJaYlRUUkNnK1NZeW9EV25lVkZteU13b1F0RFVwOEVKMVR0MTIwQWpEYTZSMEh0K2tJZkkyR3ZJNUtTcVduTEh6OFlEMHkxSlphVEI4enJ1aE9rUnBjMGlPendBPT0tLUdCc0xLQzV5MzRvckcwWHJ1M1lCeGc9PQ%3D%3D--15d000c5e933f2eb7f7495e5a848a25a15a46104; path=/; expires=Tue, 07 Aug 2018 10:44:01 -0000; HttpOnly
x-request-id: 4035fb74-79cf-418c-8278-a7dd8d60d719
x-runtime: 0.026098

HTTP/2 200 
date: Tue, 07 Aug 2018 07:44:02 GMT
content-type: text/html;charset=utf-8
x-frame-options: SAMEORIGIN
x-xss-protection: 1; mode=block
x-content-type-options: nosniff
etag: W/"260f5c0e17b8c07fb449fa080e60db0a"
cache-control: max-age=0, private, must-revalidate
set-cookie: _nsl_editor_session_test=RkNNVCtpMnNSNWdaMTJvc256SHJLR3FsMHY1dDdkYlpvN0hPWFpoK2tQZm5Pc0hpTEZsNTRadTBmM2JzQTRreUV0YnVtQTlHNVR4NGNyazdER0lPaS9YNlQ5RUdleHBZNnZqSllmQUVPQytZalpVaDJzdXVVZW0zcUNUNm9sRC9XUlNnMW8yMGp4YU9PSzRFRXN0T0RBPT0tLVJaZEdFdGltR0hhSkwxVm4wanBjb2c9PQ%3D%3D--e21144a4523975258be6f2a74f3e39759fa5895b; path=/; expires=Tue, 07 Aug 2018 10:44:02 -0000; HttpOnly
x-request-id: 36a581c2-3451-4407-9c3b-3bb274b93bdc
x-runtime: 0.105420

TODO

BNTI.md

THIS IS WORK IN PROGRESS

BNTi env setup

Intro

• java version: ORACLE jdk1.8.0_171 (use latest stable in 1.8)
• jruby: 9.1.13.0
• tomcat version: 8.5.13 (use latest stable in 8.5)
• postgresql version: 9.5.13 see locale WARNING/notes bellow

  initdb --locale=en_AU
  

• apacheds: 2.0.0-M17

summary of fixes

build fixes

ANBG do build and upload their artifacts into a "private" maven repo that is not accessible to the outside world, and the build of nsl-domain-plugin fails; FIX:

diff --git a/grails-app/conf/BuildConfig.groovy b/grails-app/conf/BuildConfig.groovy
index ec5c446..5c5187e 100644
--- a/grails-app/conf/BuildConfig.groovy
+++ b/grails-app/conf/BuildConfig.groovy
@@ -46,12 +46,8 @@ grails.project.dependency.resolution = {
     log "warn" // log level of Ivy resolver, either 'error', 'warn', 'info', 'debug' or 'verbose'
 
     repositories {
-
-        mavenLocal()
-        mavenRepo(id: "anbg", url: "http://appsdev1-ibis.it.csiro.au:8085/repository/maven-central/")
-        mavenRepo(id: "anbg", url: "http://appsdev1-ibis.it.csiro.au:8085/repository/grails-plugins/")
-        grailsHome()
-        mavenCentral()
+        mavenRepo(url: "https://repo.grails.org/grails/plugins")
+        mavenRepo(url: "https://repo1.maven.org/maven2")

The plugin will be (for now) installed in the maven repo/cache:

mbohun@linux-khr1:~> find ~/.m2/repository -name "*.zip" | grep nsl-domain
/home/mbohun/.m2/repository/au/org/biodiversity/grails/plugins/nsl-domain-plugin/1.11-SNAPSHOT/nsl-domain-plugin-1.11-SNAPSHOT.zip
/home/mbohun/.m2/repository/au/org/biodiversity/grails/plugins/nsl-domain-plugin/1.10/nsl-domain-plugin-1.10.zip

config/installation fixes

• add/fix this in the jenkins build

  cp nsl#mapper##1.0022.war nxl#mapper##1.0022.war
  

• sudo systemctl stop tomcat.service

• ./BNTi-icn_load-data.sh > /tmp/BNTi-icn_load-data.log 2>&1

• sudo systemctl start tomcat.service

• login to https://dev51-icn.oztaxa.com/nxl/services/ go to admin -> "Reconstruct name strings"

• psql -f dawr_tree_import.sql -d nsl > /tmp/dawr_tree_import.log 2>&1

• psql -f Add_the_mapper_IDs_for_the_tree_elements_created.sql -d nsl > /tmp/add_mapper_ids.log 2>&1

• more sql fixes/mods:

  # 2018-08-01
  
  # update/adjust tree host_name
  update tree set host_name='https://dev51-icn.oztaxa.com/nxl/mapper';
  
  # mapper host_name update
  update  mapper.host set host_name='dev51-icn.oztaxa.com/nxl/mapper';
  
  # name_space adjustment (this will be templetized)
  update mapper.identifier set name_space ='bpni' where name_space ='apni';
  
  # update all tree-s group_name to 'treebuilder'  
  update tree set group_name='treebuilder';
  
  # updating tree mapper links
  update tree_element set instance_link = regexp_replace(instance_link, 'https://dev51-icn.oztaxa.com', 'https://dev51-icn.oztaxa.com/nxl/mapper'), name_link = regexp_replace(name_link, 'https://dev51-icn.oztaxa.com', 'https://dev51-icn.oztaxa.com/nxl/mapper');

BNTi-icn_load-data.sh BASH script REPLACES (ALMOST) ALL BELLOW THIS POINT

---

This BASH script represent-s the outstanding/missing step that had to be executed manually after the end of the installation:

#!/bin/bash

# NOTE/TODO: - we are cleaning this up
#            - for now this script assumes that you did:
#              1. install and start tomcat
#              2. deploy the 4 war files
#                 - /opt/tomcat/webapps/nsl#mapper##1.0022.war
#                 - /opt/tomcat/webapps/nxl#editor##1.65.war
#                 - /opt/tomcat/webapps/nxl#services##1.0204.war
#                 the war-s / webapps will throw exceptions and fail to start, BUT this step creates/installs:
#                 /opt/apache-tomcat-8.5.13/webapps/nxl#services##1.0204/plugins/nsl-domain-plugin-1.10/sql
#                 with .sql script-s (update-to-25.sql) we have to run further bellow.
#              3. stop tomcat (sudo systemctl restart tomcat)
#
# NOTE: if you do 'sudo su postgress' then you do *NOT* need to pass 
#       '-h localhost -p 5432 -U nsldev -d nsl' to psql
#
sudo su postgress

psql -h localhost -p 5432 -U nsldev -d nsl -f /tmp/icn-nsl-ddl.sql

cd /opt/apache-tomcat-8.5.13/webapps/nxl#services##1.0204/plugins/nsl-domain-plugin-1.10/sql
psql -h localhost -p 5432 -U nsldev -d nsl -f ./update-to-25.sql

# FIX: mapper-ddl-pulled_form_prod.sql is not in git, given to us by ANBG dev-s on #Slack 
#        
psql -h localhost -p 5432 -U nsldev -d nsl -f /tmp/mapper-ddl-pulled_form_prod.sql 
psql -h localhost -p 5432 -U nsldev -d nsl -f /tmp/mapper-ddl.sql 

# FIX: mapper.db_version table created, but empty
psql -d nsl -c "INSERT INTO mapper.db_version (id, version) VALUES (1, 5);"

# FIX: always make sure the mapper points to the localhost mapper
#      from: psql -d nsl -c "update mapper.host set host_name = 'localhost:7070/nsl-mapper' where preferred = true;"
psql -d nsl -c "INSERT INTO mapper.host (id, host_name, preferred) VALUES (1, 'localhost:7070/nsl-mapper', true);"

# back to linux user ubuntu
exit

# for changes to files owned/used by linux user tomcat
sudo su tomcat

# FIX: editor, add missing property
#      TODO: this same property (value) was just set/written into the DB mapper.host table, do we really insist on
#            reading the same property from multiple places?
echo 'Rails.configuration.nsl_linker = "http://localhost:7070/nsl-mapper/"' >> $CATALINA_HOME/.nsl/editor-config.rb

# exit as user tomcat to become ubuntu again (default)
exit

# as user ubuntu:
sudo systemctl restart tomcat

• replace dev42 in *.sql 4 entries

  postgres@ip-192-168-1-237:/home/ubuntu/2017-07-30_BNTi-icn-data_load$ ls -lahF
  total 8.3M
  drwxrwxr-x 3 ubuntu ubuntu 4.0K Jul 31 11:10 ./
  drwxr-xr-x 6 ubuntu ubuntu 4.0K Jul 31 11:10 ../
  -rw-rw-r-- 1 ubuntu ubuntu 232K Jul 23 13:05 dawr_ddl.sql
  -rw-rw-r-- 1 ubuntu ubuntu  21K Jul 31 11:04 dawr_import.sql
  -rw-rw-r-- 1 ubuntu ubuntu 1.8K Jul 24 14:25 dawr_readme.md
  -rw-rw-r-- 1 ubuntu ubuntu 3.9K Jul 31 11:04 dawr_tree_import.sql
  -rw-rw-r-- 1 ubuntu ubuntu 4.0K Jul 31 11:09 mapper-ddl.sql
  drwxrwxr-x 8 ubuntu ubuntu 4.0K Jul 31 11:08 nsl-infra/
  -rw-r--r-- 1 ubuntu ubuntu 1.7M Jul 30 11:18 services.zip
  -rw-rw-r-- 1 ubuntu ubuntu 6.4M Jul 23 14:11 tblBiota_20180717.csv
  

details of fixes

mapper fixes:

- name: Create nsl.public tables, views etc
  become: true
  become_user: postgres
  command: psql -f /tmp/"{{ shard_nsl_ddl_file }}" nsl

- name: Create nsl.mapper tables, views etc
  become: true
  become_user: postgres
  command: psql -f /tmp/mapper-ddl.sql nsl

This psql line came from ANBG's bin/start-nsl-dev.sh

mbohun@linux-khr1:~/src/nsl-dev-env> cat bin/start-nsl-dev.sh 
#! /bin/bash

~/apps/apacheds-2.0.0-M17/bin/apacheds.sh start

# always make sure the mapper points to the localhost mapper
psql -d nsl -c "update mapper.host set host_name = 'localhost:7070/nsl-mapper' where preferred = true;"

cd ~/devel/anbg/nsl/apache-tomcat-8.5.13
bin/startup.sh
tail -f logs/catalina.out

# always make sure the mapper points to the localhost mapper
psql  -h localhost -p 5432 -U nsldev -d nsl -c "update mapper.host set host_name = 'localhost:7070/nsl-mapper' where preferred = true;"

This was added manually, after the DB was created:

nsl=# \dt mapper.
                List of relations
 Schema |         Name          | Type  | Owner
--------+-----------------------+-------+--------
 mapper | db_version            | table | nsldev
 mapper | host                  | table | nsldev
 mapper | identifier            | table | nsldev
 mapper | identifier_identities | table | nsldev
 mapper | match                 | table | nsldev
 mapper | match_host            | table | nsldev
(6 rows)

nsl=# select * from mapper.db_version;
 id | version
----+---------
(0 rows)

nsl=# INSERT INTO mapper.db_version (id, version) VALUES (1, 5);
INSERT 0 1
nsl=# select * from mapper.db_version;
 id | version
----+---------
  1 |       5
(1 row)

nsl=# select * from mapper.host
nsl-# ;
 id | host_name | preferred
----+-----------+-----------
(0 rows)

nsl=# update mapper.host set host_name = 'localhost:7070/nsl-mapper' where preferred = true;
UPDATE 0
nsl=# select * from mapper.host                                                             
;
 id | host_name | preferred 
----+-----------+-----------
(0 rows)

nsl=# INSERT INTO mapper.host (id, host_name, preferred) VALUES (0, 'localhost:7070/nsl-mapper', true);                        
INSERT 0 1
nsl=# select * from mapper.host                                                                        
;
 id |         host_name         | preferred 
----+---------------------------+-----------
  0 | localhost:7070/nsl-mapper | t
(1 row)

nsl-services fixes:

The services startup at the moment crashes/throws-exception:

2018-07-16 13:13:58.118 [DEBUG] grails.app.services.au.org.biodiversity.nsl.ConfigService:128  - read config: classification tree key: APC
2018-07-16 13:13:58.210 [ INFO] grails.app.services.au.org.biodiversity.nsl.NslDomainService:152  - updating to version 25
2018-07-16 13:13:58.215 [ INFO] grails.app.services.au.org.biodiversity.nsl.NslDomainService:152  - found nslDomainPlugin with path /plugins/nsl-domain-plugin-1.10
2018-07-16 13:13:58.216 [ INFO] grails.app.services.au.org.biodiversity.nsl.NslDomainService:152  - nsl-ddl.sql file path /opt/apache-tomcat-8.5.13/webapps/nxl#services##1.0204/plugins/nsl-domain-plugin-1.10/sql/update-to-25.sql
2018-07-16 13:13:58.323 [ERROR] org.codehaus.groovy.grails.web.context.GrailsContextLoaderListener:213  - Error initializing the application: No such property: scheme for class: SimpleTemplateScript1
groovy.lang.MissingPropertyException: No such property: scheme for class: SimpleTemplateScript1
        at SimpleTemplateScript1.run(SimpleTemplateScript1.groovy:1)
        at au.org.biodiversity.nsl.NslDomainService.replaceParams(NslDomainService.groovy:111)
        at au.org.biodiversity.nsl.NslDomainService.updateToCurrentVersion(NslDomainService.groovy:63)
        at BootStrap$_closure1.doCall(BootStrap.groovy:37)
        at grails.util.Environment.evaluateEnvironmentSpecificBlock(Environment.java:327)
        at grails.util.Environment.executeForEnvironment(Environment.java:320)
        at grails.util.Environment.executeForCurrentEnvironment(Environment.java:296)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
2018-07-16 13:13:58.325 [ERROR] org.codehaus.groovy.grails.web.context.GrailsContextLoaderListener:213  - Error initializing Grails: No such property: scheme for class: SimpleTemplateScript1
groovy.lang.MissingPropertyException: No such property: scheme for class: SimpleTemplateScript1
        at SimpleTemplateScript1.run(SimpleTemplateScript1.groovy:1)
        at au.org.biodiversity.nsl.NslDomainService.replaceParams(NslDomainService.groovy:111)
        at au.org.biodiversity.nsl.NslDomainService.updateToCurrentVersion(NslDomainService.groovy:63)
        at BootStrap$_closure1.doCall(BootStrap.groovy:37)
        at grails.util.Environment.evaluateEnvironmentSpecificBlock(Environment.java:327)
        at grails.util.Environment.executeForEnvironment(Environment.java:320)
        at grails.util.Environment.executeForCurrentEnvironment(Environment.java:296)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
2018-07-16 13:13:58.331 [ WARN] org.codehaus.groovy.grails.lifecycle.ShutdownOperations:189  - Error occurred running shutdown operation: BeanFactory not initialized or already closed - call 'refresh' before accessing beans via the ApplicationContext
java.lang.IllegalStateException: BeanFactory not initialized or already closed - call 'refresh' before accessing beans via the ApplicationContext
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
2018-07-16 13:13:58.332 [ INFO] org.codehaus.groovy.grails.web.context.GrailsContextLoaderListener:152  - Destroying Spring parent WebApplicationContext Root WebApplicationContext

• I ran manually ./update-to-25.sql (do I need/have to run ./nsl-ddl.sql BEFORE ./update-to-25.sql) see the log/output/errors bellow ?

  cd /opt/apache-tomcat-8.5.13/webapps/nxl#services##1.0204/plugins/nsl-domain-plugin-1.10/sql
  psql -h localhost -p 5432 -U nsldev -d nsl -f ./update-to-25.sql
  

• sudo systemctl restart tomcat
• After this nsl-services started automatically without reporting any error-s/exception-s (./update-to-26.sql)

nsl-editor fixes

• $CATALINA_HOME/.nsl/editor-config.rb needs to be added:

  Rails.configuration.nsl_linker = "http://localhost:7070/nsl-mapper/"

• plus the whole installer has to be searched/checked for typo/wrong hostname icn.dev42.oztaxa.com (it should be dev42-icn.oztaxa.com)
• nginx needs HTTP -> HTTPS

---

NOTES:

locale WARNING

BAD

postgres@ip-192-168-1-237:/home/ubuntu/2017-07-30_BNTi-icn-data_load$ locale
LANG=en_US.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

GOOD This is what it should be (affects date format/interpretation, and psql sorting, etc):

[pmcneil@nerdbrick ~]$ locale
LANG=en_AU.utf8
LC_CTYPE="en_AU.utf8"
LC_NUMERIC="en_AU.utf8"
LC_TIME="en_AU.utf8"
LC_COLLATE="en_AU.utf8"
LC_MONETARY="en_AU.utf8"
LC_MESSAGES="en_AU.utf8"
LC_PAPER="en_AU.utf8"
LC_NAME="en_AU.utf8"
LC_ADDRESS="en_AU.utf8"
LC_TELEPHONE="en_AU.utf8"
LC_MEASUREMENT="en_AU.utf8"
LC_IDENTIFICATION="en_AU.utf8"
LC_ALL=

env details:

tomcat@ip-192-168-1-236:/opt$ ls -lahF
total 9.0M
drwxr-xr-x  4 root   root   4.0K Jul 16 11:30 ./
drwxr-xr-x 23 root   root   4.0K Jul 16 11:42 ../
drwxr-xr-x 11 tomcat tomcat 4.0K Jul 16 11:59 apache-tomcat-8.5.13/
-rw-r--r--  1 root   root   9.0M Jul 10 15:09 apache-tomcat-8.5.13.tar.gz
lrwxrwxrwx  1 root   root     12 Jul 16 11:30 jdk -> jdk1.8.0_171/
drwxr-xr-x  8 root   root   4.0K Mar 29 11:18 jdk1.8.0_171/

tomcat@ip-192-168-1-236:/opt$ cat apache-tomcat-8.5.13/bin/setenv.sh

#!/bin/bash

JAVA_HOME=/opt/jdk
JRE_HOME=$JAVA_HOME/jre

export PATH=$JAVA_HOME/bin:$PATH

export SECRET_KEY_BASE="[REMOVED]"

tomcat@ip-192-168-1-236:/opt$ which java
/opt/jdk/bin/java

tomcat@ip-192-168-1-236:/opt$ which javac
/opt/jdk/bin/javac

tomcat@ip-192-168-1-236:/opt$ echo $JAVA_HOME
/opt/jdk

tomcat@ip-192-168-1-236:/opt$ java -version
java version "1.8.0_171"
Java(TM) SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.171-b11, mixed mode)

tomcat@ip-192-168-1-236:/opt$ javac -version
javac 1.8.0_171

postgres@ip-192-168-1-237:/home/ubuntu/2017-07-30_BNTi-icn-data_load$ psql
psql (9.5.13)
Type "help" for help.

postgres=# select '25/03/03 12:54' :: timestamp with time zone;
ERROR:  date/time field value out of range: "25/03/03 12:54"
LINE 1: select '25/03/03 12:54' :: timestamp with time zone;
               ^
HINT:  Perhaps you need a different "datestyle" setting.
postgres=# select '03/25/03 12:54' :: timestamp with time zone;
      timestamptz       
------------------------
 2003-03-25 12:54:00+11
(1 row)

postgres=#

BNTi_intro.md

input: tblBiota.csv (NAQS)

From the bootstrap_db jenkins job we see that ATM (2018-08-29) it is using:

• https://github.com/ess-acppo/nsl-infra/tree/flex-deploy
• jenkins/bootstrap_db.gvy

@Grab('com.xlson.groovycsv:groovycsv:1.3')
import static com.xlson.groovycsv.CsvParser.parseCsv

def final tbl_biota_csv_file = this.args[0]
println "tbl_biota_csv_file: ${tbl_biota_csv_file}"

def final map_per_line = parseCsv(new File(tbl_biota_csv_file).getText('utf-8'))
def final set_chrKingdomCode = [] as Set;
map_per_line.each {
    def final kc = it["chrKingdomCode"];
    if (kc) {
        set_chrKingdomCode << kc;
    } else {
        // kc == null
        println "ERROR (record with NO chrKingdomCode set): ${it}";
    }
}

def final NAQS_KINGDOM_NAMES = [
    "A": "Animalia",
    "B": "Bacteria",
    "F": "Fungi",
    "L": "Algae",
    "P": "Plantae",
    "V": "Viruses"
]

println "FOUND NAQS KINGDOMS:"
set_chrKingdomCode.each {
    def final kingdom_symbol = it
    def final kingdom_name =  NAQS_KINGDOM_NAMES[kingdom_symbol]
    println "${kingdom_symbol} (${kingdom_name})"
}

mbohun@linux-cr70:~/src/NAQS_tblBiota.git> groovy ./extract_kingdom_names.groovy /tmp/tblBiota_dbs.csv
tbl_biota_csv_file: /tmp/tblBiota_dbs.csv
ERROR (record with NO chrKingdomCode set): intBiotaID: 112104, intParentID: 57986, vchrEpithet: apicalis, vchrFullName: Taylorilygus apicalis (Fieber, 1861), vchrYearOfPub: 1861, vchrAuthor: Fieber, vchrNameQualifier: , chrElemType: SP, vchrRank: , chrKingdomCode: , intOrder: 0, vchrParentage: \1\106786\6\100975\12\52080\77742\101031\52235\111360\111374\57986\112104, bitChangedComb: TRUE, bitShadowed: FALSE, bitUnplaced: FALSE, bitUnverified: FALSE, bitAvailableName: FALSE, bitLiteratureName: FALSE, dtDateCreated: 2016-09-06 15:12, vchrWhoCreated: NAQSTaxaTree, dtDateLastUpdated: 2016-10-11 14:31, vchrWhoLastUpdated: AGDAFF\Teakle Graham, txtDistQual: , GUID: {84C90B4D-C400-4FA4-B1CC-E1E9A3ED4B4F}
FOUND NAQS KINGDOMS:
A (Animalia)
P (Plantae)
L (Algae)
F (Fungi)
B (Bacteria)
V (Viruses)

dawr_readme.md

Importing Data

This is part is scripted in BNTi-icn_load-data.sh

To import data from a CSV file like tblbiota_20180717.csv

• Make sure you have the appropriate NSL users set up on your postgresql database.
• drop and create your test database (don't drop if you don't have it...)

dropdb dtest && createdb dtest

• create the new schema and default data from the dawr_ddl.sql (note there are some modifications to the nsl.ddl in this file, mainly the shard config and the ID range for the global nsl sequence.) PLEASE DON'T CHANGE THE SEQUENCE RANGE as this makes it easier if we need to detangle data at a later date.

psql -f dawr_ddl.sql -d dtest

• clean up your CSV file by making all the ychryearofpub data integers by removing stray ) and ranges like 1882/3 and removing trailing spaces (Hint: use a regex to replace *, with ,)
• copy your cleaned up CSV file to /tmp then run the import:

psql -f dawr_import.sql -d dtest

• Run up your mapper, LDAP and services.

curl -s -L "http://localhost:8080/nxl/services/admin/reconstruct-names?apiKey=${YOUR_API_KEY_HERE}" Log into the services, go to the admin page, click the reconstruct all names. Watch the log output and wait for it to complete. once finished the services will run name checks via notifications in the background.

• example:

  curl -s -I -L "http://localhost:8080/nxl/services/admin/reconstruct-names?apiKey=${YOUR_API_KEY_HERE}"
  HTTP/1.1 302
  Set-Cookie: rememberMe=deleteMe; Path=/nxl/services; Max-Age=0; Expires=Mon, 27-Aug-2018 04:10:41 GMT
  Set-Cookie: JSESSIONID=0B42438E17E68AA11D0A1B641A99C59E; Path=/nxl/services; HttpOnly
  Location: https://dev56-icn.oztaxa.com/nxl/services/admin/index
  X-UA-Compatible: IE=Edge
  Content-Length: 0
  Date: Tue, 28 Aug 2018 04:10:41 GMT
  
  HTTP/1.1 302
  Date: Tue, 28 Aug 2018 04:10:41 GMT
  Content-Length: 0
  Connection: keep-alive
  Location: https://dev56-icn.oztaxa.com/nxl/services/auth/login?targetUri=%2Fadmin%2Findex
  X-UA-Compatible: IE=Edge
  
  HTTP/1.1 200
  Date: Tue, 28 Aug 2018 04:10:41 GMT
  Content-Type: text/html;charset=UTF-8
  Content-Length: 5737
  Connection: keep-alive
  X-UA-Compatible: IE=Edge
  Content-Language: en-AU
  

• Since this may take some time and has no real use here you may just delete the notifications using sql delete from notification;.

• Finally run the tree import sql.

psql -f dawr_tree_import.sql -d dtest

• if you now go to the classifications page and click on BPC you should see your tree in draft. You can publish it if you like.

NOTES

• This installs to a database named dtest change that name in the commands above to the database name you wish to use.
• you can edit the details in the shard_config table inserted in the dawr_ddl.sql file, but make sure the namespace matches what you have in the import files.

DB-name_group.csv

errors.md

These are runtime erros/exceptions notes, if cofirmed/relevant/etc. we can officially create issues for them in their github repos.

assuming a list is never empty?

2018-07-18 16:14:29.807 [DEBUG] grails.app.services.au.org.biodiversity.nsl.ConfigService:128  - read config: classification tree key: APC                                                                                                                                      
2018-07-18 16:14:29.848 [DEBUG] grails.app.controllers.au.org.biodiversity.nsl.api.SearchController:128  - doing search                                                                                                                                                         
2018-07-18 16:14:29.856 [DEBUG] grails.app.services.au.org.biodiversity.nsl.SearchService:128  - select distinct(n), n.sortName, n.nameRank.sortOrder from Name n where n.instances.size > 0 and (n.nameType.scientific = true) order by n.sortName, n.nameRank.sortOrder asc   
2018-07-18 16:14:29.857 [DEBUG] grails.app.services.au.org.biodiversity.nsl.SearchService:128  - {}                                                                                                                                                                             
2018-07-18 16:14:30.027 [DEBUG] grails.app.services.au.org.biodiversity.nsl.SearchService:128  - query took 167ms                                                                                                                                                               
2018-07-18 16:14:30.589 [ERROR] org.codehaus.groovy.grails.web.errors.GrailsExceptionResolver:213  - NoSuchElementException occurred when processing request: [GET] /nxl/services/search                                                                                        
Cannot access first() element from an empty List. Stacktrace follows:                                                                                                                                                                                                           
org.codehaus.groovy.grails.web.pages.exceptions.GroovyPagesException: Error processing GroovyPageView: Error executing tag <st:randomName>: Cannot access first() element from an empty List                                                                                    
        at grails.plugin.cache.web.filter.PageFragmentCachingFilter.doFilter(PageFragmentCachingFilter.java:198)                                                                                                                                                                
        at grails.plugin.cache.web.filter.AbstractFilter.doFilter(AbstractFilter.java:63)                                                                                                                                                                                       
        at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449)                                                                                                                                                                          
        at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365)                                                                                                                                                                                
        at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90)                                                                                                                                                                                     
        at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83)                                                                                                                                                                                       
        at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383)                                                                                                                                                                               
        at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362)                                                                                                                                                                      
        at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)                                                                                                                                                                            
        at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82)                                                                                                                                                                                                           
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)                                                                                                                                                                                      
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)                                                                                                                                                                                      
        at java.lang.Thread.run(Thread.java:748)                                                                                                                                                                                                                                
Caused by: org.codehaus.groovy.grails.web.taglib.exceptions.GrailsTagException: Error executing tag <st:randomName>: Cannot access first() element from an empty List                                                                                                           
        at gsp_services_searchsearch_gsp$_run_closure2.doCall(gsp_services_searchsearch_gsp.groovy:136)                                                                                                                                                                         
        at gsp_services_searchsearch_gsp.run(gsp_services_searchsearch_gsp.groovy:160)                                                                                                                                                                                          
        ... 13 more                                                                                                                                                                                                                                                             
Caused by: java.util.NoSuchElementException: Cannot access first() element from an empty List                                                                                                                                                                                   
        at services.ServiceTagLib$_closure26.doCall(ServiceTagLib.groovy:353)                                                                                                                                                                                                   
        ... 15 more                                                                                                                                                                                                                                                             
2018-07-18 16:14:30.609 [DEBUG] grails.app.controllers.au.org.biodiversity.nsl.api.ErrorController:128  - Error controller: error is org.codehaus.groovy.grails.web.pages.exceptions.GroovyPagesException: Error processing GroovyPageView: Error executing tag <st:randomName>:
 Cannot access first() element from an empty List

HTML URLs inconsitent?

This looks like an BUG/un-finished customization:

1. although the source/template of the editor HTML does support a customizable window.relative_url_root through setting the Rails.application.config.relative_url_root property:

   <!DOCTYPE html>
   <html lang="en">
   <head>
       <meta charset="utf-8">
       <meta http-equiv="X-UA-Compatible" content="IE=edge">
       <meta name="viewport" content="width=device-width, initial-scale=1">
       <meta name="description" content="">
       <meta name="author" content="">
   
       <title> <%= page_title %> </title>
   
       <script>
           window.relative_url_root = "<%= Rails.application.config.relative_url_root %>";
           window.NG_TEMPLATE_URL="<%= @ng_template_path %>";
           taxoTree = "<%= ShardConfig.classification_tree_key %>";
       </script>
   <% if Rails.env == "development" || params[:debug] == 'true' %>
       <script> debugSwitch = true; </script>
   <% else %>
       <script> debugSwitch = false; </script>
   <% end %>
   
       <%= stylesheet_link_tag    'application', media: 'all', 'data-turbolinks-track' => false %>
       <%= javascript_include_tag 'application', 'data-turbolinks-track' => false %>
       <%= csrf_meta_tags %>
       <%= favicon_link_tag 'favicon.png?v=1', :rel => 'shortcut icon' %>
   <% if development? %>

2. The URLs of CSS, and .js files right bellow are NOT constructed with window.relative_url_root resulting in generating HTML source with URLs with diff/inconsistent relative root, for example href="/nsl/editor/assets/application-15121e9f759d8c1bbe071d54fc09fe3f.css":

   <!DOCTYPE html>
   <html lang="en">
       <head>
           <meta charset="utf-8">
           <meta http-equiv="X-UA-Compatible" content="IE=edge">
           <meta name="viewport" content="width=device-width, initial-scale=1">
           <meta name="description" content="">
           <meta name="author" content="">
   
           <title> NSL Editor </title>
   
           <script>
               window.relative_url_root = "/nxl/editor";
               window.NG_TEMPLATE_URL="";
               taxoTree = "APC";
           </script>
           <script> debugSwitch = false; </script>
   
           <link rel="stylesheet" media="all" href="/nsl/editor/assets/application-15121e9f759d8c1bbe071d54fc09fe3f.css" data-turbolinks-track="false" />
           <script src="/nsl/editor/assets/application-4614ecd9e3edfc6fd419aa215019264d.js" data-turbolinks-track="false"></script>
           <meta name="csrf-param" content="authenticity_token" />
           <meta name="csrf-token" content="1XAUjxGMEpwZAcklSIimKHhMU7qzGGEGuqwzG9uF/jwWK3UeRC+GQDcGGJRDJoHYrEcquXgfYpF5bCwSRzuoXg==" />
           <link rel="shortcut icon" type="image/x-icon" href="/nsl/editor/assets/favicon-2c74ce22698b130a7e669effa4699004.png?v=1" />

notes-icn.md

icn

REFERENCES:

• https://www.iapt-taxon.org/nomen/main.php

notes-icn_DB-name_type.csv

notes-icnp.md

icnp

Sample names Bacteria:

1. The citation of a name of a taxon should include both the name of the author(s) who first published the name and the year of publication. If there are more than two authors of the name, the citation includes only the first author followed by "et al." and the year.
2. Taxa below the rank of subspecies are not covered by the Rules of the Bacteriological Code.
3. Need to get pathovar identification from james but he is offline. We can add a type entry where users need to pick pathovar or not while creating entries using the editor

• Xanthomonas arboricola Vauterin et al. 1995
• Xanthomonas alfalfae subsp. citrumelonis Schaad et al. 2007
• Xanthomonas maltophilia (Hugh 1981) Swings et al. 1983
• Xanthomonas hyacinthi (ex Wakker 1883) Vauterin et al. 1995
• Xanthomonas translucens pv. pistaciae Giblot-Ducray et al. 2009
• Xanthomonas dyei pv. laureliae (Dye 1963) Young et al. 2010
• TODO: provide example-s WITHOUT et al.

TODO/NOTE; THIS APPLIES TO ALL FOUR BNTi SHARDS (ICN, ICZN, ICNP, ICVCN): provide example-s WITHOUT et al. (NOTE: the more, well ideally ALL known/possible - the more combinations we cover the better; in fact: the easiest/best from the consistency point of view each BNTi shard documentation/notes should in this section simply list/include the very same set of input-s and expected output-s we want to use for the regression test-s of NameConstructor-s.

REFERENCES:

• ICNP Names Alt Source: http://www.bacterio.net/

notes-icnp_DB-name_type.csv

notes-icvcn.md

icvcn

Name Construction Rules: Virus:

Critical:

1. No Genus name needs to be included in virus name.
2. there should not be any ‘-’ in the name. Should be replaced by ‘ ’.
3. Virus names end with the virus in the end
4. No need to include author names in species names of viruses
5. Only capitalize first letter of the first word. All ICTV approved names are italicized.

Tobacco mosaic virus

Plum pox virus

6.Unless there are proper nouns in the name like "Valley"

Murray Valley encephalitis virus

Grapevine Syrah virus 1

7.Also capitalize single letter alphabet

Grapevine virus A

8.All higher classification names are printed in italics and the first letter of the names are capitalized:

•orders (e.g. Tymovirales),

•families (e.g. Betaflexiviridae),

•subfamilies (e.g. Trivirinae), and

•genera (e.g. Vitivirus)

Non-Critical:

9.ICTV has recently approved the genus Orthotospovirus to replace Tospovirus and has incorporated the genus name ‘orthotospovirus’ into the virus species name. So:

Tomato spotted wilt virus -> Tomato spotted wilt orthotospovirus

10.For consistency in the group PRA, all non-ICTV approved species are italicised, but do not contain ‘orthotospovirus’. Strictly speaking, only ICTV approved virus species names should be italicized. For example: Alstroemeria necrotic streak virus is incorrect. And, Alstroemeria necrotic steak orthotospovirus is also incorrect. But, “alstroemeria necrotic streak virus” is correct.

Name Samples: Virus (ICVCN):

“Cucumber Mosaic Price” should be: Cucumber mosaic virus (genus Cucumovirus)

Tomato spotted wilt virus should be: Tomato spotted wilt orthotospovirus

Cucumber mosaic virus should be: Cucumber mosaic cucumovirus

Tobacco mosaic virus

Plum pox virus

Grapevine Syrah virus 1

Grapevine virus A

Murray Valley encephalitis virus

alstroemeria necrotic streak virus

REFERENCES:

• https://talk.ictvonline.org/
• Changes to taxonomy and the International Code of Virus Classification and Nomenclature ratified by the International Committee on Taxonomy of Viruses (2018).
• https://www.ncbi.nlm.nih.gov/pubmed/29458533
• https://www.ncbi.nlm.nih.gov/pubmed/30039318
• https://www.ncbi.nlm.nih.gov/pubmed/28134265
• https://www.ncbi.nlm.nih.gov/pubmed/28434098
• https://www.ncbi.nlm.nih.gov/pubmed/27424026
• https://www.ncbi.nlm.nih.gov/pubmed/20204430
• https://www.ncbi.nlm.nih.gov/pubmed/16713373
• https://www.ncbi.nlm.nih.gov/pmc/articles/PMC3322749/

notes-icvcn_DB-name_type.csv

notes-iczn.md

iczn

REFERENCES:

• https://en.wikipedia.org/wiki/International_Commission_on_Zoological_Nomenclature

notes-iczn_DB-name_type.csv

notes-NameService.md

ANBG

• ICN: https://github.com/ess-acppo/services/blob/BNTi-customize_name_construction/grails-app/services/au/org/biodiversity/nsl/IcnNameConstructionService.groovy
• ICZN: https://github.com/ess-acppo/services/blob/BNTi-customize_name_construction/grails-app/services/au/org/biodiversity/nsl/IcznNameConstructionService.groovy (this seems to be just a "dummy" implementation - a simple copy of IcnNameConstructionService.groovy and renamed to IcznNameConstructionService.groovy) - so we (DAWR) have to provide proper implementation

DAWR

• ICNP: DAWR to implement
• ICVCN: DAWR to implement

3rd party name parser-s

• https://github.com/GlobalNamesArchitecture/gnparser
• https://github.com/gbif/name-parser

  <dependency>

    org.gbif          name-parser          2.24    

### DAWR additions/extensions/customization
- **NOTE:** ANBG are **NOT** using grails `./grails-app/conf/spring/resources.groovy` to instantiate/select/assign a specific implementation of the [NameConstructor](https://github.com/ess-acppo/services/blob/BNTi-customize_name_construction/grails-app/services/au/org/biodiversity/nsl/NameConstructor.groovy) interface. 
- `./grails-app/conf/spring/resources.groovy`
  ```groovy
  
  // given a shard configuration loaded from some external config (properties, DB, etc.) 
  shard = [
      'icn':   ['nameConstructor': 'IcnNameConstructionService'],
      'iczn':  ['nameConstructor': 'IcznNameConstructionService'],
      'icnp':  ['nameConstructor': 'IcnpNameConstructionService'],
      'icvcn': ['nameConstructor': 'IcvcnNameConstructionService']
  ]
  
  shard_type = 'icnp'
  
  beans = {
      // ...
      
      // shard['icnp']['nameConstructor'] ===> IcnpNameConstructionService
      springConfig.addAlias 'nameConstructor', shard[shard_type]['nameConstructor']

  }
  ```
- `./grails-app/services/au/org/biodiversity/nsl/NameConstructionService.groovy`
  ```groovy
  class NameConstructionService {

      def nameConstructor

      Map constructName(Name name) {
          return nameConstructor.constructName(name)
      }
      
      String constructAuthor(Name name) {
          return nameConstructor.constructAuthor(name)
      }
  }
  ```
- at-the-moment/in-the-first-implementation all of the four NameConstructor-s (icn, iczn, icnp, icvcn) are located in `grails-app/services/au/org/biodiversity/nsl` (and built into the services war file); obviously these could-be/should-be configurable and ideally build into a separate lib/jar file.
- obviously (and most likely safer/easier) is to have/use only one/single NameConstructionService/NameConstructor per-BNTi-shard

#### ~~TEMPORARY WORK-AROUD (2018-09-18)~~ NOT required (2018-09-19)
Because there seem to be some problem/problems when **adding** ICNP, ICVCN, ICZN specific properties/configuration  to the existing DB (DB that is already populated with ICN specific properties/configuration), an alternative approach is to modify/adjust/replace the existing ICN properties/configuration with ICNP, ICVCN, ICZN respectively,
as follows:
1. **DO NOT CHANGE** the `name_group` DB table (you can change/adjust ONLY the `description_html` value)
```sql
nsl=# select * from name_group;

    id    | lock_version |          description_html          |    name    |   rdf_id   
----------+--------------+------------------------------------+------------+------------
 40000484 |            0 | (description of <b>[unknown]</b>)  | [unknown]  | unknown
 40000485 |            0 | (description of <b>[n/a]</b>)      | [n/a]      | n-a
 40000486 |            0 | (description of <b>botanical</b>)  | botanical  | botanical
 40000487 |            0 | (description of <b>zoological</b>) | zoological | zoological
(4 rows)

2. For each of the shards do the following:

• ICNP branch / ICNP shard (In the name_type DB table remove all the ICN specific fields and add the ICNP specific fields); and modify the NameConstructionService class as follows:

  class NameConstructionService {
  
      def icnpNameConstructionService
      
      Map constructName(Name name) {
          if (!name) {
              throw new NullPointerException("Name can't be null.")
          }
  
          if (name.nameType.nameGroup.name == 'botanical') {
              return icnpNameConstructionService.constructName(name)
          }

• ICVCN branch / ICVCN shard (In the name_type DB table remove all the ICN specific fields and add the ICVCN specific fields); and modify the NameConstructionService class as follows:

  class NameConstructionService {
  
      def icvcnNameConstructionService
      
      Map constructName(Name name) {
          if (!name) {
              throw new NullPointerException("Name can't be null.")
          }
  
          if (name.nameType.nameGroup.name == 'botanical') {
              return icvnpNameConstructionService.constructName(name)
          }

• ICZN branch / ICZN shard (In the name_type DB table remove all the ICN specific fields and add the ICZN specific fields); and modify the NameConstructionService class as follows:

  class NameConstructionService {
  
      def icznNameConstructionService
      
      Map constructName(Name name) {
          if (!name) {
              throw new NullPointerException("Name can't be null.")
          }
  
          if (name.nameType.nameGroup.name == 'botanical') {
              return icznpNameConstructionService.constructName(name)
          }

• ICN branch / ICN shard (already implemented; no changes required)

• INSTEAD ANBG has the diff available implementations of NameConstructor hard-coded inside NameConstructionService.groovy

  class NameConstructionService {
  
      // Existing
      def icnNameConstructionService
      def icznNameConstructionService
      
      // DAWR adding:
      def icnpNameConstructionService
      def icvcnNameConstructionService
      
      Map constructName(Name name) {
          if (!name) {
              throw new NullPointerException("Name can't be null.")
          }
  
          // Existing:
          if (name.nameType.nameGroup.name == 'botanical') {
              return icnNameConstructionService.constructName(name)
          }
  
          if (name.nameType.nameGroup.name == 'zoological') {
              return icznNameConstructionService.constructName(name)
          }
          
          // DAWR adding:
          if (name.nameType.nameGroup.name == 'icnp') {
              return icnpNameConstructionService.constructName(name)
          }
  
          if (name.nameType.nameGroup.name == 'icvcn') {
              return icvcnNameConstructionService.constructName(name)
          }
          
          throw new UnsupportedNomCode("Unsupported Nomenclatural code for name construction $name.nameType.nameGroup.name")
      }

• The different name.nameType.nameGroup.name are set/configured via the following DB tables:

  nsl=# select * from name_group;

      id    | lock_version |          description_html          |    name    |   rdf_id   
  ----------+--------------+------------------------------------+------------+------------
   40000484 |            0 | (description of <b>[unknown]</b>)  | [unknown]  | unknown
   40000485 |            0 | (description of <b>[n/a]</b>)      | [n/a]      | n-a
   40000486 |            0 | (description of <b>botanical</b>)  | botanical  | botanical
   40000487 |            0 | (description of <b>zoological</b>) | zoological | zoological
  (4 rows)
  

  • name_group table needs to be extended/added two new name_group-s one for ICNP, and one for ICVCN:

        id    | lock_version |          description_html          |    name    |   rdf_id   
    ----------+--------------+------------------------------------+------------+------------
     40000484 |            0 | (description of <b>[unknown]</b>)  | [unknown]  | unknown
     40000485 |            0 | (description of <b>[n/a]</b>)      | [n/a]      | n-a
     40000486 |            0 | (description of <b>botanical</b>)  | botanical  | botanical
     40000487 |            0 | (description of <b>zoological</b>) | zoological | zoological
     40000488 |            0 | (description of <b>icnp</b>)       | icnp       | icnp
     40000489 |            0 | (description of <b>icvcn</b>)      | icvcn      | icvcn     
    (4 rows)
    

    NOTE: I am kinda worried about all these "synonyms" (those strings that are being used as "keys" in the grails app; not the "descriptions" obviously):
    • botanical vs icn
    • zoological vs iczn
    • bacterial?/prokaryotic? vs icnp
    • viral? vs icvcn

• The detail-s/properties of each name_type are configured in the name_type DB table:

  nsl=# select * from name_type;

      id    | lock_version | autonym | connector | cultivar | deprecated |                     description_html                      | formula | hybrid |               name                | name_category_id | name_group_id |              rdf_id               | scientific | sort_order 
  ----------+--------------+---------+-----------+----------+------------+-----------------------------------------------------------+---------+--------+-----------------------------------+------------------+---------------+-----------------------------------+------------+------------
   40000559 |            0 | f       |           | f        | f          | (description of <b>[default]</b>)                         | f       | f      | [default]                         |         40000479 |      40000485 | default                           | f          |          1
   40000560 |            0 | f       |           | f        | f          | (description of <b>[unknown]</b>)                         | f       | f      | [unknown]                         |         40000479 |      40000485 | unknown                           | f          |          2
   40000561 |            0 | f       |           | f        | f          | (description of <b>[n/a]</b>)                             | f       | f      | [n/a]                             |         40000479 |      40000485 | n-a                               | f          |          3
   40000562 |            0 | f       |           | f        | f          | (description of <b>scientific</b>)                        | f       | f      | scientific                        |         40000480 |      40000486 | scientific                        | t          |          4
   40000563 |            0 | f       |           | f        | f          | (description of <b>sanctioned</b>)                        | f       | f      | sanctioned                        |         40000480 |      40000486 | sanctioned                        | t          |          5
   40000564 |            0 | f       |           | f        | f          | (description of <b>phrase name</b>)                       | f       | f      | phrase name                       |         40000480 |      40000486 | phrase-name                       | t          |          6
   40000565 |            0 | f       | x         | f        | f          | (description of <b>hybrid formula parents known</b>)      | t       | t      | hybrid formula parents known      |         40000480 |      40000486 | hybrid-formula-parents-known      | t          |          7
   40000566 |            0 | f       | x         | f        | f          | (description of <b>hybrid formula unknown 2nd parent</b>) | t       | t      | hybrid formula unknown 2nd parent |         40000480 |      40000486 | hybrid-formula-unknown-2nd-parent | t          |          8
   40000567 |            0 | f       | x         | f        | f          | (description of <b>named hybrid</b>)                      | f       | t      | named hybrid                      |         40000480 |      40000486 | named-hybrid                      | t          |          9
   40000568 |            0 | t       | x         | f        | f          | (description of <b>named hybrid autonym</b>)              | f       | t      | named hybrid autonym              |         40000480 |      40000486 | named-hybrid-autonym              | t          |         10
   40000569 |            0 | t       | x         | f        | f          | (description of <b>hybrid autonym</b>)                    | f       | t      | hybrid autonym                    |         40000480 |      40000486 | hybrid-autonym                    | t          |         11
   40000570 |            0 | f       | -         | f        | f          | (description of <b>intergrade</b>)                        | t       | t      | intergrade                        |         40000480 |      40000486 | intergrade                        | t          |         12
   40000571 |            0 | t       |           | f        | f          | (description of <b>autonym</b>)                           | f       | f      | autonym                           |         40000480 |      40000486 | autonym                           | t          |         13
   40000572 |            0 | f       |           | t        | f          | (description of <b>cultivar</b>)                          | f       | f      | cultivar                          |         40000481 |      40000486 | cultivar                          | f          |         17
   40000573 |            0 | f       |           | t        | f          | (description of <b>cultivar hybrid</b>)                   | f       | t      | cultivar hybrid                   |         40000481 |      40000486 | cultivar-hybrid                   | f          |         18
   40000574 |            0 | f       |           | f        | f          | (description of <b>informal</b>)                          | f       | f      | informal                          |         40000482 |      40000486 | informal                          | f          |         26
   40000575 |            0 | f       |           | f        | f          | (description of <b>common</b>)                            | f       | f      | common                            |         40000483 |      40000486 | common                            | f          |         15
   40000576 |            0 | f       |           | f        | f          | (description of <b>vernacular</b>)                        | f       | f      | vernacular                        |         40000483 |      40000486 | vernacular                        | f          |         16
   40000577 |            0 | f       | +         | t        | f          | (description of <b>graft / chimera</b>)                   | t       | f      | graft/chimera                     |         40000481 |      40000486 | graft-chimera                     | f          |         25
   40000578 |            0 | f       |           | t        | t          | (description of <b>acra</b>)                              | f       | f      | acra                              |         40000481 |      40000486 | acra                              | f          |         20
   40000579 |            0 | f       |           | t        | t          | (description of <b>acra hybrid</b>)                       | f       | t      | acra hybrid                       |         40000481 |      40000486 | acra-hybrid                       | f          |         21
   40000580 |            0 | f       |           | t        | t          | (description of <b>pbr</b>)                               | f       | f      | pbr                               |         40000481 |      40000486 | pbr                               | f          |         22
   40000581 |            0 | f       |           | t        | t          | (description of <b>pbr hybrid</b>)                        | f       | t      | pbr hybrid                        |         40000481 |      40000486 | pbr-hybrid                        | f          |         23
   40000582 |            0 | f       |           | t        | t          | (description of <b>trade</b>)                             | f       | f      | trade                             |         40000481 |      40000486 | trade                             | f          |         24
   40000583 |            0 | f       |           | t        | t          | (description of <b>trade hybrid</b>)                      | f       | t      | trade hybrid                      |         40000481 |      40000486 | trade-hybrid                      | f          |         25
   40000584 |            0 | f       | x         | t        | f          | (description of <b>cultivar hybrid formula</b>)           | t       | t      | cultivar hybrid formula           |         40000481 |      40000486 | cultivar-hybrid-formula           | f          |         19
  

  • The name_type table needs to be added the detail-s/properties specific to each of the following 3 name_groups:
    • zoological
    • icnp
    • icvcn

DB additions/extensions

id,lock_version,description_html,name,rdf_id
40000484,0,(description of <b>[unknown]</b>),[unknown],unknown
40000485,0,(description of <b>[n/a]</b>),[n/a],n-a
40000486,0,(description of <b>botanical</b>),botanical,botanical
40000487,0,(description of <b>zoological</b>),zoological,zoological

id,lock_version,autonym,connector,cultivar,deprecated,description_html,formula,hybrid,name,name_category_id,name_group_id,rdf_id,scientific,sort_order
40000559,0,f,,f,f,(description of <b>[default]</b>),f,f,[default],40000479,40000485,default,f,1
40000560,0,f,,f,f,(description of <b>[unknown]</b>),f,f,[unknown],40000479,40000485,unknown,f,2
40000561,0,f,,f,f,(description of <b>[n/a]</b>),f,f,[n/a],40000479,40000485,n-a,f,3
40000562,0,f,,f,f,(description of <b>scientific</b>),f,f,scientific,40000480,40000486,scientific,t,4
40000563,0,f,,f,f,(description of <b>sanctioned</b>),f,f,sanctioned,40000480,40000486,sanctioned,t,5
40000564,0,f,,f,f,(description of <b>phrase name</b>),f,f,phrase name,40000480,40000486,phrase-name,t,6
40000565,0,f,x,f,f,(description of <b>hybrid formula parents known</b>),t,t,hybrid formula parents known,40000480,40000486,hybrid-formula-parents-known,t,7
40000566,0,f,x,f,f,(description of <b>hybrid formula unknown 2nd parent</b>),t,t,hybrid formula unknown 2nd parent,40000480,40000486,hybrid-formula-unknown-2nd-parent,t,8
40000567,0,f,x,f,f,(description of <b>named hybrid</b>),f,t,named hybrid,40000480,40000486,named-hybrid,t,9
40000568,0,t,x,f,f,(description of <b>named hybrid autonym</b>),f,t,named hybrid autonym,40000480,40000486,named-hybrid-autonym,t,10
40000569,0,t,x,f,f,(description of <b>hybrid autonym</b>),f,t,hybrid autonym,40000480,40000486,hybrid-autonym,t,11
40000570,0,f,-,f,f,(description of <b>intergrade</b>),t,t,intergrade,40000480,40000486,intergrade,t,12
40000571,0,t,,f,f,(description of <b>autonym</b>),f,f,autonym,40000480,40000486,autonym,t,13
40000572,0,f,,t,f,(description of <b>cultivar</b>),f,f,cultivar,40000481,40000486,cultivar,f,17
40000573,0,f,,t,f,(description of <b>cultivar hybrid</b>),f,t,cultivar hybrid,40000481,40000486,cultivar-hybrid,f,18
40000574,0,f,,f,f,(description of <b>informal</b>),f,f,informal,40000482,40000486,informal,f,26
40000575,0,f,,f,f,(description of <b>common</b>),f,f,common,40000483,40000486,common,f,15
40000576,0,f,,f,f,(description of <b>vernacular</b>),f,f,vernacular,40000483,40000486,vernacular,f,16
40000577,0,f,+,t,f,(description of <b>graft / chimera</b>),t,f,graft/chimera,40000481,40000486,graft-chimera,f,25
40000578,0,f,,t,t,(description of <b>acra</b>),f,f,acra,40000481,40000486,acra,f,20
40000579,0,f,,t,t,(description of <b>acra hybrid</b>),f,t,acra hybrid,40000481,40000486,acra-hybrid,f,21
40000580,0,f,,t,t,(description of <b>pbr</b>),f,f,pbr,40000481,40000486,pbr,f,22
40000581,0,f,,t,t,(description of <b>pbr hybrid</b>),f,t,pbr hybrid,40000481,40000486,pbr-hybrid,f,23
40000582,0,f,,t,t,(description of <b>trade</b>),f,f,trade,40000481,40000486,trade,f,24
40000583,0,f,,t,t,(description of <b>trade hybrid</b>),f,t,trade hybrid,40000481,40000486,trade-hybrid,f,25
40000584,0,f,x,t,f,(description of <b>cultivar hybrid formula</b>),t,t,cultivar hybrid formula,40000481,40000486,cultivar-hybrid-formula,f,19