Record storage rank & map via export

[FedorSteeman]

Issue

Storage locations in specimen table in app database are of mixed rank which makes importing to Specify difficult

• [x] Fix SQL recreate predefined data inserts to include rankname
• [x] Change specimen table to accomdate storagerankname
• [x] Change date entry to save values to column storagerankname

Acceptable solution

See comments below.

[jlegind]

I see the following ranks in Specify 7: Room Aisle Cabinet Shelf Box Rack Cryo box Vial

In that order. We can even have a column restrictions on the storage table like this: rank enum( "Room" , "Aisle" , "Cabinet", ...)

[FedorSteeman]

Using the Storage.sql script for each collection under editions, we need to adjust the storage table in the local app database as follows:

1. Add rankname column
2. Add lines for parent levels: institution, site/building, collection (and other levels if need be)

[jlegind]

With the storagetreedefitem table copied to SQLite I was able to make this SQL statement pulling the storage rank into the storage table:

SELECT t1.id, t1.spid, t1.name, t1.fullname, t1.parentfullname, t1.collectionid, t1.treedefid, sr.title as storagerank FROM (SELECT *, substr(name, 1, pos-1) as storagetitle FROM (SELECT *, instr(name, ' ') as pos FROM storage ))t1 LEFT JOIN storage_rank sr ON t1.storagetitle = sr.Title;

SQLite does not have a built in string split function so that is why the quirky subquery is needed.

I am now considering creating a script that produces a file similar to the editions/NHMD/tracheophyta/Storage.sql file, but with the storage_rank column added.

[jlegind]

Solution proposed for Specify DB:

SELECT CONCAT_WS(' | ', '(' s1.institution, s1.building, s1.collection, s1.unit, '(SELECT id FROM collection WHERE spid = 688130 AND institutionid = 1)),') FROM (SELECT 'Natural History Museum of Denmark' AS institution, t1.Name AS building, t2.name AS collection, CASE WHEN t2.Name = 'Herbarium C: Danish Vascular Plant Collection' then CONCAT('Box ', t3.Name)
ELSE CONCAT('Shelf ', t3.Name) END AS unit FROM storage AS t1 LEFT JOIN storage AS t2 ON t2.ParentID = t1.storageID LEFT JOIN storage AS t3 ON t3.parentID = t2.storageID WHERE t1.Name IN ( 'Priorparken') AND t2.Name LIKE ('Herbarium%'))s1;

[jlegind]

The above SQL which is for the Specify which runs on MySQL won't work due to aliases in the CONCAT_WS function! Use this instead:

SELECT CONCAT_WS(' | ', t1.stor, t1.unit, '(SELECT id FROM collection WHERE spid = 688130 AND institutionid = 1),') AS storage_  FROM
(
SELECT CONCAT_WS(' | ', 'Natural History Museum of Denmark' , t1.Name , t2.name) AS stor,
case when t2.Name = 'Herbarium C: Danish Vascular Plant Collection' then CONCAT('Box ', t3.Name)
ELSE CONCAT('Shelf ', t3.Name)
END AS unit
FROM storage AS t1  
LEFT JOIN storage AS t2 ON t2.ParentID = t1.storageID
LEFT JOIN storage AS t3 ON t3.parentID = t2.storageID
WHERE t1.Name IN ( 'Priorparken') AND t2.Name LIKE ('Herbarium%')
)t1;

[FedorSteeman]

Reopening since the rankname column still needs to be added to the app db and Storage.sql files.

Also the storagerankname should be recorded into the specimen table during digitization.

[jlegind]

Minor update - start and end parantheses in top SELECT statement.

SELECT CONCAT_WS(' | ', '(', t1.stor, t1.unit, '(SELECT id FROM collection WHERE spid = 688130 AND institutionid = 1)),') AS storage_  FROM
(
SELECT CONCAT_WS(' | ', 'Natural History Museum of Denmark' , t1.Name , t2.name) AS stor,
case when t2.Name = 'Herbarium C: Danish Vascular Plant Collection' then CONCAT('Box ', t3.Name)
ELSE CONCAT('Shelf ', t3.Name)
END AS unit
FROM storage AS t1  
LEFT JOIN storage AS t2 ON t2.ParentID = t1.storageID
LEFT JOIN storage AS t3 ON t3.parentID = t2.storageID
WHERE t1.Name IN ( 'Priorparken') AND t2.Name LIKE ('Herbarium%')
)t1;

[jlegind]

Final update, now with rank at index 4 in the concatenated column. The output need to be pasted in to a sql file with this line at the top: INSERT INTO storage (name, fullname, collectionid) VALUES Like this:

The SQL statement is this:

SELECT CONCAT_WS(' | ', '(', t1.stor, t1.unit, t1.rank_name, '(SELECT id FROM collection WHERE spid = 688130 AND institutionid = 1)),') AS storage_  FROM
(
SELECT CONCAT_WS(' | ', 'Natural History Museum of Denmark' , t1.Name , t2.name ) AS stor, stdi.name AS rank_name,
case when t2.Name = 'Herbarium C: Danish Vascular Plant Collection' then CONCAT('Box ', t3.Name)
ELSE CONCAT('Shelf ', t3.Name)
END AS unit, stdi.Name AS rankname, t3.name AS index_
FROM storage AS t1
LEFT JOIN storage AS t2 ON t2.ParentID = t1.storageID
LEFT JOIN storage AS t3 ON t3.parentID = t2.storageID
LEFT JOIN storagetreedefitem stdi ON t3.RankID = stdi.RankID
WHERE t1.Name IN ( 'Priorparken') AND t2.Name LIKE ('Herbarium%')
)t1;

[jlegind]

Currently March 2023, the policy is to run the SQL above at the time of a new release and pasting the output into a SQL file.

• Replace the existing Storage.sql file in ...Documents\dassco\MassDigitizer\sql\editions\NHMD\tracheophyta\Storage.sql with the new file.
• Run the prepare-db.bat bash file as described here https://github.com/NHMDenmark/Mass-Digitizer/blob/main/README.md .

[FedorSteeman]

OK. Will you add the rankname to the storage table as well as the INSERT statements, so we get this?

INSERT INTO storage (name, fullname, rankname, collectionid) VALUES

The next thing would then be adding the storagerankname to the specimen table and making the app record the value there.