dustymc commented 1 year ago

Initial Request

Goal: Describe what you're trying to accomplish. This is the only necessary step to start this process. The Committee is available to assist with all other steps. Please clearly indicate any uncertainty or desired guidance if you proceed beyond this step.

SNV results should be merged into

all with values of virus: Orthohantavirus [ link ]

Data:

temp_snv.csv.zip

Summary:

arctosprod@arctos>> select guid_prefix,count(*) from temp_snv group by guid_prefix;
 guid_prefix | count 
-------------+-------
 DGR:Mamm    |   126
 DMNS:Mamm   |     1
 MSB:Mamm    | 11391
(3 rows)

Users:

@campmlc @AdrienneRaniszewski @acdoll @jldunnum @jrdemboski

Context: Describe why this new value is necessary and existing values are not.

We have two ways of doing the same thing.

Priority: Please describe the urgency and/or choose a priority-label to the right. You should expect a response within two working days, and may utilize Arctos Contacts if you feel response is lacking.

Code table cleanup should always be highest priority.

Project: Add the issue to the Code Table Management Project.

Discussion: Please reach out to anyone who might be affected by this change. Leave a comment or add this to the Committee agenda if you believe more focused conversation is necessary.

@ArctosDB/arctos-code-table-administrators

Approval

All of the following must be checked before this may proceed.

_The How-To Document should be followed. Pay particular attention to terminology (with emphasis on consistency) and documentation (with emphasis on functionality)._

[x] Code Table Administrator[1] - check and initial, comment, or thumbs-up to indicate that the request complies with the how-to documentation and has your approval
[ ] Code Table Administrator[2] - check and initial, comment, or thumbs-up to indicate that the request complies with the how-to documentation and has your approval
[x] DBA - The request is functionally acceptable. The term is not a functional duplicate, and is compatible with existing data and code.
[x] DBA - Appropriate code or handlers are in place as necessary. (ID_References, Media Relationships, Encumbrances, etc. require particular attention)

Rejection

If you believe this request should not proceed, explain why here. Suggest any changes that would make the change acceptable, alternate (usually existing) paths to the same goals, etc.

Can a suitable solution be found here? If not, proceed to (2)
Can a suitable solution be found by Code Table Committee discussion? If not, proceed to (3)
Take the discussion to a monthly Arctos Working Group meeting for final resolution.

Implementation

Once all of the Approval Checklist is appropriately checked and there are no Rejection comments, or in special circumstances by decree of the Arctos Working Group, the change may be made.

Review everything one last time. Ensure the How-To has been followed. Ensure all checks have been made by appropriate personnel.

Make changes as described above. Ensure the URL of this Issue is included in the definition.

Close this Issue.

DO NOT modify Arctos Authorities in any way before all points in this Issue have been fully addressed; data loss may result.

Special Exemptions

In very specific cases and by prior approval of The Committee, the approval process may be skipped, and implementation requirements may be slightly altered. Please note here if you are proceeding under one of these use cases.

Adding an existing term to additional collection types may proceed immediately and without discussion, but doing so may also subject users to future cleanup efforts. If time allows, please review the term and definition as part of this step.
The Committee may grant special access on particular tables to particular users. This should be exercised with great caution only after several smooth test cases, and generally limited to "taxonomy-like" data such as International Commission on Stratigraphy terminology.

jldunnum commented 1 year ago

The issue with just merging everything at once is that there will actually be a range of different methods, dates and agents that we could enhance the current yes/no SNV Results data with. Could they be added to the new attributes without those for now and then we go in and add as we can?

dustymc commented 1 year ago

I'm happy to help in any way I can to do whatever's necessary, as long as it involves some path to normalized data.

Generally, removing and adding is easier and less confusing than modifying. There are UI tools to remove and add. I'm still happy to help with or do WHATEVER.

If they were my data, I think I'd just migrate them with a method of 'Migrated from SNV results. + {oldmethod}' or something of the sort, with the (eventually....) goal of adding a new attribute with correct metadata and removing the 'Migrated...' one. That'd quickly get everything into one system and leave a way of finding things that need help whenever there's time. (And of course reality might stomp all over the specifics of that, but I'd still try to find some way to get out of the old system and not stuck in between, where little things tend to grow and fester.)

jldunnum commented 1 year ago

Agreed that sounds like a good route to take, can you make that happen?

Jonathan L. Dunnum Ph.D. (he, him, his) Senior Collection Manager Division of Mammals, Museum of Southwestern Biology University of New Mexico Albuquerque, NM 87131 (505) 277-9262 Fax (505) 277-1351

Chair, Systematic Collections Committee, American Society of Mammalogists Latin American Fellowship Committee, ASM

MSB Mammals website: http://www.msb.unm.edu/mammals/index.html Facebook: http://www.facebook.com/MSBDivisionofMammals

Shipping Address: Museum of Southwestern Biology Division of Mammals University of New Mexico CERIA Bldg 83, Room 204 Albuquerque, NM 87131

From: dustymc @.> Sent: Wednesday, May 3, 2023 10:10 AM To: ArctosDB/arctos @.> Cc: Jonathan Dunnum @.>; Mention @.> Subject: Re: [ArctosDB/arctos] Code Table Request - REMOVE SNV results (Issue #6219)

[EXTERNAL]

I'm happy to help in any way I can to do whatever's necessary, as long as it involves some path to normalized data.

Generally, removing and adding is easier and less confusing than modifying. There are UI tools to remove and add. I'm still happy to help with or do WHATEVER.

If they were my data, I think I'd just migrate them with a method of 'Migrated from SNV results. + {oldmethod}' or something of the sort, with the (eventually....) goal of adding a new attribute with correct metadata and removing the 'Migrated...' one. That'd quickly get everything into one system and leave a way of finding things that need help whenever there's time. (And of course reality might stomp all over the specifics of that, but I'd still try to find some way to get out of the old system and not stuck in between, where little things tend to grow and fester.)

— Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/6219#issuecomment-1533321099, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AED2PA2773BGO76XRYOER6LXEJ7OFANCNFSM6AAAAAAXUPOIX4. You are receiving this because you were mentioned.Message ID: @.***>

dustymc commented 1 year ago

Yep, on it. @acdoll @jrdemboski I'll include the one DMNS record as well, just let me know if I need to do something else.

acdoll commented 1 year ago

The one DMNS record appears to have been entered in error. I have removed the SNV attribute.

dustymc commented 1 year ago

@jldunnum (and anyone else willing to help!) - please take a look at this, make sure I haven't mucked anything up.

5 steps:

temp_snv_examinedfor is one record for every existing 'SNV results' attribute - I'll ADD it to attributes
temp_snv_detected is filtered for "attribute_value='yes'" - I'll ADD it to attributes
temp_snv_notdetected is filtered for "attribute_value='no'" - I'll ADD it to attributes
I will then REMOVE all 'SNV results' attributes
I will then DELETE 'SNV results' from the code table.

temp_snv_notdetected.csv.zip temp_snv_detected.csv.zip temp_snv_examinedfor.csv.zip

jldunnum commented 1 year ago

Steps look good, will try to look at the attached files soon

Jonathan L. Dunnum Ph.D. (he, him, his) Senior Collection Manager Division of Mammals, Museum of Southwestern Biology University of New Mexico Albuquerque, NM 87131 (505) 277-9262 Fax (505) 277-1351

Chair, Systematic Collections Committee, American Society of Mammalogists Latin American Fellowship Committee, ASM

MSB Mammals website: http://www.msb.unm.edu/mammals/index.html Facebook: http://www.facebook.com/MSBDivisionofMammals

Shipping Address: Museum of Southwestern Biology Division of Mammals University of New Mexico CERIA Bldg 83, Room 204 Albuquerque, NM 87131

From: dustymc @.> Sent: Wednesday, May 3, 2023 12:42 PM To: ArctosDB/arctos @.> Cc: Jonathan Dunnum @.>; Mention @.> Subject: Re: [ArctosDB/arctos] Code Table Request - REMOVE SNV results (Issue #6219)

[EXTERNAL]

@jldunnumhttps://github.com/jldunnum (and anyone else willing to help!) - please take a look at this, make sure I haven't mucked anything up.

5 steps:

temp_snv_examinedfor is one record for every existing 'SNV results' attribute - I'll ADD it to attributes
temp_snv_detected is filtered for "attribute_value='yes'" - I'll ADD it to attributes
temp_snv_notdetected is filtered for "attribute_value='no'" - I'll ADD it to attributes
I will then REMOVE all 'SNV results' attributes
I will then DELETE 'SNV results' from the code table.

temp_snv_notdetected.csv.ziphttps://github.com/ArctosDB/arctos/files/11389846/temp_snv_notdetected.csv.zip temp_snv_detected.csv.ziphttps://github.com/ArctosDB/arctos/files/11389847/temp_snv_detected.csv.zip temp_snv_examinedfor.csv.ziphttps://github.com/ArctosDB/arctos/files/11389848/temp_snv_examinedfor.csv.zip

— Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/6219#issuecomment-1533527239, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AED2PA6DU6GQHZY2WRSL2FDXEKRJLANCNFSM6AAAAAAXUPOIX4. You are receiving this because you were mentioned.Message ID: @.***>

dustymc commented 1 year ago

ping @jldunnum

jldunnum commented 1 year ago

I have another issue with accessing the virus:orthohantavirus serology data. There are no "examined for" or "detected", "not detected", virus:orthohantavirus options in the customize results. I can now search on those attributes and get the records that have them but I can't actually see them in my results. Super frustrating!

dustymc commented 1 year ago

https://github.com/ArctosDB/arctos/issues/6111

campmlc commented 1 year ago

We need this In the search results table, please, as researchers need to download along with locality and other data.

6270

Jegelewicz commented 1 year ago

I think we are going to hit a roadblock here and I also think that serious researchers are going to end up downloading more than one table. Ideally, the locality stuff would come from the search results download, but the attributes will come from the attribute table download. These data will need to be used together to answer serious questions. See also https://github.com/ArctosDB/arctos/issues/6256

As the data become more and more complex, it is just not possible to download things in a flat file.

dustymc commented 1 year ago

locality

That REALLY doesn't flatten well, and I'm not sure there's a great solution (for those not into JSON, anyway) at the moment.

campmlc commented 1 year ago

Is it possible to add the values to the main search results table, regardless? We need to be able to create a search profile with those specific values for Los Alamos for the Picante grant.

On Tue, May 9, 2023, 5:34 PM dustymc @.***> wrote:

[EXTERNAL]*

locality

That REALLY doesn't flatten well, and I'm not sure there's a great solution (for those not into JSON, anyway) at the moment.

— Reply to this email directly, view it on GitHub https://github.com/ArctosDB/arctos/issues/6219#issuecomment-1541019133, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADQ7JBEJM3XLSLKR7VZBD5TXFLH67ANCNFSM6AAAAAAXUPOIX4 . You are receiving this because you were mentioned.Message ID: @.***>

campmlc commented 1 year ago

If it helps, we should be removing the following as soon as all this is shown to be functional: Examined for parasites Examined for ectoparasites Examined for endoparasites Parasites detected Ectoparasites detected Endoparasites detected So we are actually gaining space in the table. Please make this happen.

jldunnum commented 1 year ago

Attribute download table just doesn’t do what we need in terms of having the other critical fields like species, locality, etc. we need to figure how to get back to the functionality we used to have. Sorry but this is no longer a functional collection database. Almost every function I attempt I hit a snag or full on roadblock. My frustration level has never been higher. I just saw the suggestion for Chris Conroy to use the attribute downloader and then go in and manage his data with excel. Wow, it that where we are now at?

Get Outlook for iOShttps://aka.ms/o0ukef

From: Mariel Campbell @.> Sent: Tuesday, May 9, 2023 5:40:38 PM To: ArctosDB/arctos @.> Cc: Jonathan Dunnum @.>; Mention @.> Subject: Re: [ArctosDB/arctos] Code Table Request - REMOVE SNV results (Issue #6219)

[EXTERNAL]

If it helps, we should be removing the following as soon as all this is shown to be functional: Examined for parasites Examined for ectoparasites Examined for endoparasites Parasites detected Ectoparasites detected Endoparasites detected So we are actually gaining space in the table. Please make this happen.

— Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/6219#issuecomment-1541023562, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AED2PAZDBSLJ5ELIA5RRBWDXFLIXNANCNFSM6AAAAAAXUPOIX4. You are receiving this because you were mentioned.Message ID: @.***>

dustymc commented 1 year ago

@jldunnum I can't find words that begin to express how distressing it is to hear that. Can we schedule a zoom or voice call, or would you like to recommend some other way in which we might communicate? I don't think we're doing that here.

jldunnum commented 1 year ago

Hey Dusty, I know and I apologize for just throwing that out there. That wasn’t fair to you and all the others putting in so much time and effort on Arctos. It’s just gotten so hard to do my normal job that my frustration is boiling over. It just seems like we are all continually having to look for work arounds or modifications just to do the simple things we used to do routinely. The new developments are great and will pay off down the road. It’s the simple useability at the moment that is truly affecting my ability to do my job. Yes let’s do a zoom and work though this. Thanks, jon

Get Outlook for iOShttps://aka.ms/o0ukef

From: dustymc @.> Sent: Tuesday, May 9, 2023 7:54:16 PM To: ArctosDB/arctos @.> Cc: Jonathan Dunnum @.>; Mention @.> Subject: Re: [ArctosDB/arctos] Code Table Request - REMOVE SNV results (Issue #6219)

[EXTERNAL]

@jldunnumhttps://github.com/jldunnum I can't find words that begin to express how distressing it is to hear that. Can we schedule a zoom or voice call, or would you like to recommend some other way in which we might communicate? I don't think we're doing that here.

— Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/6219#issuecomment-1541177481, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AED2PA3P33OBXNBZZ6AVOB3XFLYMRANCNFSM6AAAAAAXUPOIX4. You are receiving this because you were mentioned.Message ID: @.***>

Jegelewicz commented 1 year ago

Just my opinion, but the things requested are far beyond what should be expected of a collection management database. The capability to use the data in Arctos for research will increasingly require skills beyond the ability to use the user interface and download a single flat file. The data is very complex and that requires effort to record, maintain, and use it. As we discussed with @campmlc using SQL and the API would likely be skills that the Los Alamos researchers have? @dustymc has provided one example of how this is done and I bet he would be happy to help. Perhaps what you dream of is possible, but not with the resources currently at our disposal. Include me on a zoom if you think I can contribute.

jldunnum commented 1 year ago

I guess I'm having a hard time seeing why these very basic queries and the ability to view our search results with their attributes included are considered "far beyond what should be expected of a collection management database". How do I actually work with my collection if I can only view tiny subsets of data at a time? In order to do any work with our records we need to be able to see them (in all their glory). Doing attribute downloads and then merging files in other applications isn't a solution because we have already left Arctos at that point. What are the resources required to return to the functionality of 6 months ago when we could pretty much do this? Yes it timed out from time to time but we could actually work with large numbers of records and the vast majority of a records data. Is this simply a we aren't in Oracle anymore issue?

Jonathan L. Dunnum Ph.D. (he, him, his) Senior Collection Manager Division of Mammals, Museum of Southwestern Biology University of New Mexico Albuquerque, NM 87131 (505) 277-9262 Fax (505) 277-1351

Chair, Systematic Collections Committee, American Society of Mammalogists Latin American Fellowship Committee, ASM

MSB Mammals website: http://www.msb.unm.edu/mammals/index.html Facebook: http://www.facebook.com/MSBDivisionofMammals

Shipping Address: Museum of Southwestern Biology Division of Mammals University of New Mexico CERIA Bldg 83, Room 204 Albuquerque, NM 87131

From: Teresa Mayfield-Meyer @.> Sent: Wednesday, May 10, 2023 7:01 AM To: ArctosDB/arctos @.> Cc: Jonathan Dunnum @.>; Mention @.> Subject: Re: [ArctosDB/arctos] Code Table Request - REMOVE SNV results (Issue #6219)

[EXTERNAL]

Just my opinion, but the things requested are far beyond what should be expected of a collection management database. The capability to use the data in Arctos for research will increasingly require skills beyond the ability to use the user interface and download a single flat file. The data is very complex and that requires effort to record, maintain, and use it. As we discussed with @campmlchttps://github.com/campmlc using SQL and the API would likely be skills that the Los Alamos researchers have? @dustymchttps://github.com/dustymc has provided one example of how this is done and I bet he would be happy to help. Perhaps what you dream of is possible, but not with the resources currently at our disposal. Include me on a zoom if you think I can contribute.

— Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/6219#issuecomment-1542170639, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AED2PA6GOTXDJTVT6RNQVYDXFOGRPANCNFSM6AAAAAAXUPOIX4. You are receiving this because you were mentioned.Message ID: @.***>

dustymc commented 1 year ago

@jldunnum do you have time to discuss this week?

https://github.com/ArctosDB/arctos/issues/6111 is my big-picture plea for help regarding what we should do with attributes in tabular views. I think the salient points that most apply to this are:

There can be LOTS of attributes. https://github.com/ArctosDB/arctos/issues/6261 plus https://arctos.database.museum/info/ctDocumentation.cfm?table=ctexamined_detected plus https://github.com/ArctosDB/arctos/labels/Function-CodeTables is ~two dozen attributes for every record.
Many of those will have identical types, which is what we've previously derived table column headings from when we've done that sort of thing. Duplicate column names is not technically possible.
I keep heading that things like method and determiner are important (and I believe it!), and previous "column-ization" of attributes has just glossed over that.

I'm up for most anything that our resources can support, but I need specific guidance.

jldunnum commented 1 year ago

Hey Dusty, yes need to make time for it, just haven't been able to yet. Maybe tomorrow or Thursday. Thanks, Jon

Get Outlook for iOShttps://aka.ms/o0ukef

From: dustymc @.> Sent: Monday, May 15, 2023 10:46:12 AM To: ArctosDB/arctos @.> Cc: Jonathan Dunnum @.>; Mention @.> Subject: Re: [ArctosDB/arctos] Code Table Request - REMOVE SNV results (Issue #6219)

[EXTERNAL]

@jldunnumhttps://github.com/jldunnum do you have time to discuss this week?

6111https://github.com/ArctosDB/arctos/issues/6111 is my big-picture plea for help regarding what we should do with attributes in tabular views. I think the salient points that most apply to this are:

There can be LOTS of attributes. #6261https://github.com/ArctosDB/arctos/issues/6261 plus https://arctos.database.museum/info/ctDocumentation.cfm?table=ctexamined_detected plus Function-CodeTableshttps://github.com/ArctosDB/arctos/labels/Function-CodeTables is ~two dozen attributes for every record.
Many of those will have identical types, which is what we've previously derived table column headings from when we've done that sort of thing. Duplicate column names is not technically possible.
I keep heading that things like method and determiner are important (and I believe it!), and previous "column-ization" of attributes has just glossed over that.

I'm up for most anything that our resources can support, but I need specific guidance.

— Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/6219#issuecomment-1548201076, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AED2PAYJE3TA4NKB5C3SCJ3XGJMVJANCNFSM6AAAAAAXUPOIX4. You are receiving this because you were mentioned.Message ID: @.***>

dustymc commented 1 year ago

@jldunnum please let me know what you need

dustymc commented 1 year ago

From https://github.com/ArctosDB/arctos/issues/6111#issuecomment-1555238150

this attribute can be removed from all Panama specimens because I added the new values already.. All other instances of SNV results will need to be replaced with examined for and detected or not detected virus:Orthohantavirus based on the yes or no

dustymc commented 1 year ago

Here's all existing SNV results with country (from FLAT) added.


create table temp_snv as
select
    attributes.collection_object_id,
    guid_prefix,
    guid,
    country,
    attribute_type,
    attribute_value,
    attribute_units,
    getPreferredAgentName(determined_by_agent_id) determiner,
    determined_by_agent_id,
    attribute_remark,
    determination_method,
    determined_date
from
    attributes
    inner join flat on attributes.collection_object_id=flat.collection_object_id
where
    attribute_type ='SNV results'
;

temp_snv(2).csv.zip

Here's non-Panama detected:


create table temp_snv_yes as
select
    collection_object_id,
    guid_prefix,
    guid,
    'detected' as attribute_type,
    'virus: Orthohantavirus' as attribute_value,
    null as attribute_units,
    determined_by_agent_id,
    determiner,
    attribute_remark,
    determination_method,
    determined_date
from
    temp_snv
where
    attribute_value='yes' and
    country != 'Panama'
;

temp_snv_yes.csv.zip

I inserted these records. INSERT 0 514

Here's non-Panama not detected:


create table temp_snv_no as
select
    collection_object_id,
    guid_prefix,
    guid,
    'not detected' as attribute_type,
    'virus: Orthohantavirus' as attribute_value,
    null as attribute_units,
    determined_by_agent_id,
    determiner,
    attribute_remark,
    determination_method,
    determined_date
from
    temp_snv
where
    attribute_value='no' and
    country != 'Panama'
;

temp_snv_no.csv.zip

I inserted these records. INSERT 0 7608

and then I removed all of the old.

Done/closing.

jldunnum commented 1 year ago

sweet,thanks!

Get Outlook for iOShttps://aka.ms/o0ukef

From: dustymc @.> Sent: Friday, May 19, 2023 6:08:24 PM To: ArctosDB/arctos @.> Cc: Jonathan Dunnum @.>; Mention @.> Subject: Re: [ArctosDB/arctos] Code Table Request - REMOVE SNV results (Issue #6219)

[EXTERNAL]

Here's all existing SNV results with country (from FLAT) added.

create table temp_snv as select attributes.collection_object_id, guid_prefix, guid, country, attribute_type, attribute_value, attribute_units, getPreferredAgentName(determined_by_agent_id) determiner, determined_by_agent_id, attribute_remark, determination_method, determined_date from attributes inner join flat on attributes.collection_object_id=flat.collection_object_id where attribute_type ='SNV results' ;

temp_snv(2).csv.ziphttps://github.com/ArctosDB/arctos/files/11521022/temp_snv.2.csv.zip

Here's non-Panama detected:

create table temp_snv_yes as select collection_object_id, guid_prefix, guid, 'detected' as attribute_type, 'virus: Orthohantavirus' as attribute_value, null as attribute_units, determined_by_agent_id, determiner, attribute_remark, determination_method, determined_date from temp_snv where attribute_value='yes' and country != 'Panama' ;

temp_snv_yes.csv.ziphttps://github.com/ArctosDB/arctos/files/11521024/temp_snv_yes.csv.zip

I inserted these records. INSERT 0 514

Here's non-Panama not detected:

create table temp_snv_no as select collection_object_id, guid_prefix, guid, 'not detected' as attribute_type, 'virus: Orthohantavirus' as attribute_value, null as attribute_units, determined_by_agent_id, determiner, attribute_remark, determination_method, determined_date from temp_snv where attribute_value='no' and country != 'Panama' ;

temp_snv_no.csv.ziphttps://github.com/ArctosDB/arctos/files/11521030/temp_snv_no.csv.zip

I inserted these records. INSERT 0 7608

and then I removed all of the old.

Done/closing.

— Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/6219#issuecomment-1555387234, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AED2PA232IFUQWBZ7OKHI53XHADPRANCNFSM6AAAAAAXUPOIX4. You are receiving this because you were mentioned.Message ID: @.***>

ArctosDB / arctos

Code Table Request - REMOVE SNV results #6219

Initial Request

Approval

Rejection

Implementation

Special Exemptions

6270

6111https://github.com/ArctosDB/arctos/issues/6111 is my big-picture plea for help regarding what we should do with attributes in tabular views. I think the salient points that most apply to this are: