Feature Request - unique constraint on containers

[dustymc]

Is your feature request related to a problem? Please describe.

See https://github.com/ArctosDB/data-migration/issues/1812#issuecomment-1747385377

Describe what you're trying to accomplish

Figure out the scope in which barcodes must be unique, and how barcodes may be claimed.

Describe the solution you'd like

I believe there are three possibilities:

1. What we have now is by far the simplest system
   • Barcodes are globally unique.
   • Claims use "native" SQL.
2. We could expand claim capability, but doing so would introduce some potential fragility (are we SURE everyone counts the same way in base36?) and confusion (are you SURE this Xxxxx barcode is yours?).
   • Barcodes are globally unique.
   • Claims require some extension (at least to base36 counting to accommodate current data)
3. We could extend the key to (container.barcode, container.institution_acronym). This is by far the most robust system (I have no idea how we don't have a bunch of previously-barcoded institutions fighting over "1" already!), but would SOMEHOW involve needing to contextualize nonunique barcodes, and I don't know how that might work
   • Barcodes are unique within institutions.
   • Claims use "native" SQL.

Describe alternatives you've considered

I don't think there are any, even if the current situation can be resolved we'll probably encounter this again.

Additional context

I can't offer much help in the referenced Issue until this social problem is sorted out by The Community.

Priority

@andrew-hope can set this

[campmlc]

For option 3, what would be changed if we went this route? Would the barcode series change, or just the permissions key to include the institution acronym or guid prefix?

[Jegelewicz]

needing to contextualize nonunique barcodes

We don't have to share the container environment. I don't know how many of UAM things are scanned into MSB containers and the other way around, but is anyone else doing that?

Maybe an institution's container environment should just belong to them to do whatever?

[dustymc]

For option 3, what would be changed if we went this route?

That is my question. I'm telling you what I see as options and asking what affects implementing them (or not) might have.

don't have to share the container environment

But we don't prevent it.

• UserA who only has access to Institution1 collections scans 1 - no problem
• UserB who only has access to Institution2 collections scans 1 - no problem
• UserC who has access to Institution1 and Institution2 scans 1 - this has to result in an error, lacking some something that I can't see from here.

So that will prevent me from scanning 1 - which is fine, if I need to update I'll do it by keys.

don't know how many

select
    p.institution_acronym as parent,
    c.institution_acronym as child,
    count(*) c
from
    container c
    inner join container p on c.parent_container_id=p.container_id
where 
    p.institution_acronym != c.institution_acronym
group by 
    p.institution_acronym,
    c.institution_acronym 
order by
    p.institution_acronym,
    c.institution_acronym
;

 parent | child  |   c   
--------+--------+-------
 KNWR   | UAM    |   839
 MSB    | DGR    |   337
 MSB    | DMNS   |     2
 MSB    | NMMNHS |   165
 MSB    | NMU    |     2
 MSB    | UAM    |  2439
 MVZ    | UAM    |   733
 MVZ    | UCSC   |    23
 UAM    | ACBC   |     1
 UAM    | KNWR   |  1060
 UAM    | KWP    | 57465
 UAM    | MSB    |     6
 UAM    | MVZ    |   187

Some of those are messes (some of which might have been prevented by Option 3), but others are collaborations - KWP is purposefully in UAM containers, for example. So those two collections both having a barcode=1 would cause errors.

Small price to pay for the much more scalable solution??

institution's container

That's part of the - maybe not problem, but weird. An institution is a text string hanging awkwardly off collection, which gets joined to the institution hanging awkwardly off container for things like checking access. Not sure I have better ideas, but it's awkward and probably hard to understand and makes collaborating maybe more fragile than it has to be.

[campmlc]

I haven't been able to check the previous table yet, but I just found some more recent issues from June and July where students in MSB entered parasite records via data entry and apparently put the MSB:Mamm host catalog number in the part barcode field instead of the parasite vial base36 MSB barcode. The number series overlaps with the numeric UAM barcodes, so when these records were marked to load, the parasite parts loaded to UAM cryovials with that value. I thought at least that the current restrictions prevented this? We probably need to set up a call or task force to discuss.

[campmlc]

@megulbranson @AdrienneRaniszewski @jldunnum

[dustymc]

restrictions prevented this?

No, but that's why changing container type with entry isn't possible. That clearly isn't enough.

put the MSB:Mamm host catalog number in the part barcode field

I suggest we go ahead with (3) above, including at entry using enteredby. That will make some collaboration perhaps slightly more finicky, but I don't think these data allow for anything else.

select
    p.institution_acronym as parent,
    c.institution_acronym as child,
    count(*) c
from
    container c
    inner join container p on c.parent_container_id=p.container_id
where 
    p.institution_acronym != c.institution_acronym and
    c.container_type='collection object'
group by 
    p.institution_acronym,
    c.institution_acronym 
order by
    p.institution_acronym,
    c.institution_acronym
;


 parent | child  |   c   
--------+--------+-------
 KNWR   | UAM    |   328
 MSB    | DGR    |   337
 MSB    | DMNS   |     2
 MSB    | NMMNHS |   165
 MSB    | NMU    |     2
 MSB    | UAM    |   112
 MVZ    | UAM    |     1
 MVZ    | UCSC   |    23
 UAM    | ACBC   |     1
 UAM    | KNWR   |   744
 UAM    | KWP    | 57465
 UAM    | MSB    |     2

[campmlc]

Some of these are legitimate, as you have described - for example, MSB/DGR and some MSB/UAM, MSB/NMMNHS - these involve joint relationships, not necessarily something broken. We'll need to address these individually. One of the biggest is UAM tissues in MSB freezers - these do exist, three freezers worth, and this is going to involve a dedicated project to recatalog them as MSB records linked to UAM vouchers. But in the meantime, can we try out your option to limit students from accidentally scanning or entering the wrong barcode value? I'm not sure I understand exactly what is being proposed or how that will affect workflows - can you explain a bit more? @dusty @AdrienneRaniszewski

[campmlc]

And any chance I can get a csv of all the MSB + other institution records in your SQL above?

[andrew-hope]

All, Just to circle back to the original issue with the order I placed within the base-36 A**** series, Electronic Imaging apparently will replace the double diot barcodes that were partially duplicated with MSB, but not the ones that were not duplicated. They are pressing me for confirmation of how to move forward with the order. At this point, can I assume that I can ask them to print a new series for the ones that included duplicates, but using base-36 beginning with G****? If they will not reprint the other series, I guess I will need to know whether or not I can use them. I can hold off for a little while longer, but curation here is pretty much stalled until I can use barcodes. There are two issues ongoing that I feel are related, so not sure if this comment is best here or on the other issue.

[dustymc]

@andrew-hope sorry I don't think I can be much help. I've laid out what I see as the possibilities, I'm not sure what I can do from there without more directed feedback from the collections. We keep finding things that suggest (3) will eventually become hard to dodge, but we don't have to go there now, I'm not sure how that might affect workflows, and I think those points are mostly not from the folks who wander around collections with scanners (eg maybe we're wrong on all of it).

Very big picture, anything you can do to reduce confusion is probably worth doing - if it's possible to get barcodes which can't possibly conflict with or be confused (by humans or machines) with anything else then that's probably the thing to do, if you can't then - well, you tell me, it can certainly be my technical problem (eg, (3) above) but I also think that's likely to have social ripples, or ya'll can avoid those restrictions in some way, or ????

@Jegelewicz thoughts?

[Jegelewicz]

The 5 character starting with G base 36 for the remaining four barcodes are reserved for KSB and safe to order. The conflicting A barcodes are something that KSB and MSB need to resolve. The options I see include:

1. Figure out the A series at KSB and remove it from the reserved series that MSB has then reserve them for KSB
2. Send the A series at KSB to MSB, then let MSB order the same quantity of G barcodes for KSB.

Anything I am missing?

[andrew-hope]

Ok, thanks both. This all sounds good. I certainly will strive to minimize any issues going forward, and for now, I will proceed with ordering the G-series, and then I will discuss which of the two options Teresa mentioned will be best for MSB.

Appreciated. More soon once I can figure out the latter point.

[campmlc]

I agree with you ordering the 2 part circles reprinted at EIM with the Gseries. The other three part labels are more problematic for us to use only because of the potential confusion with the Kansas State University label. These are used in so many different ways across our multiple divisions that I don't think we can use them. But if we could figure out how to remove that series from MSB and assign them to KU, that would work - but I don't know how to do this and would need @dustymc to help.
I assume we would have to change our MSB series going forward to different values. And that still doesn't solve the problem of MSB barcodes KU has already used that we don't yet have a record of.
I don't think we have responded regarding option 3 above because I don't understand what that entails. Would it allow the same barcodes to be used at different institutions? What do you mean by setting the key to include institution? Perhaps we need a call to go over this.

[andrew-hope]

Ok, I'll go ahead with the re-order of the double dots. I also agree that ideally, we would not swap rolls of barcodes, as I think that introduces much more uncertainty for the future like we discussed on the previous zoom call. If there were a way to box off only that series for KSB, remove it from ever being used by MSB, and subsequently, we both stick to our own series (A-series for MSB and G-series for KSB) then perhaps we can keep this an isolated incident??

[dustymc]

emove that series from MSB and assign them to KU

That would be (2) - some different (and very likely much more complicated) way of claiming barcodes.

Would it allow the same barcodes to be used at different institutions?

Yes, see #6789 (comment)

[campmlc]

I'm not understanding (2) and how that affects what is proposed. That solution is the only one that would allow KU to keep the several thousand dollars worth of stickers they ordered in a series that MSB has claimed but not purchased/used. Can we schedule a call?

[Jegelewicz]

Can we schedule a call?

I probably cannot do Thursday. Friday between 11AM and 5PM MDT I might be able to work in.

The issue with #2 is that the base 36 codes aren't really a "series" that can be easily split - but depending on the actual barcodes that are held at KSB, we might be able to work something out or it might be extra complicated. The key to potentially using that solution is knowing what those barcodes are. I wouldn't think that would be super difficult to find out and report?

[campmlc]

I have a full list of the new barcodes that KSB ordered that could easily be transferred, including the first and last values. They are at the end of the series that we have ordered, but I don't know how those values translate into the SQL used to reserve a series in Arctos in order to adjust the values MSB has reserved.
This does not include the 1000 or so that @andrew-hope says they've barcoded with leftover MSB stickers. I have no idea what those are or how to deal with those at this point, other than having them rebarcode them. They need to provide an inventory. But solving the problem of the 25,000 new labels so that KSB can use them would be a huge step in the right direction.

[campmlc]

Agree with more granularity in roles. And if we had that, viewing permissions could be granted to operators across collections - they could view only in one collection, manage in another. We dont have that capability now.

[dustymc]

These barcodes are not within the owning institution's claims.

temp_noclaim_bc_inst.csv.zip

I'll update containers and/or claims very soon if I don't get instructions. HELP!

@campmlc @DerekSikes @megulbranson @ccicero @mkoo @mlbowser

[dustymc]

These labels violate the nonprinting character check. I'll update them to gonna_update_label_to unless I hear otherwise soon.

temp_funky_label.csv.zip

[dustymc]

I will remove all dimensions from these unless I get a better recipe very soon.

temp_partial_dimensions.csv.zip

[campmlc]

Dealing with family health care issues but will try to look over these

[Jegelewicz]

For the DGR followed by 5 numbers - I think they were expected to fall within "DGR10001- DGR20001"

SQL = barcode~'^DGR[0-9]*$') and substr(barcode,4)::INT between 10001 and 20001

Whatever is needed in the SQL statement to agree with the description should fix those. (change the 4 to 5?)

[Jegelewicz]

These labels violate the nonprinting character check.

I fixed all of the ALMNH labels - they included trailing spaces. Maybe try first removing all trailing spaces and see what is left?

I also fixed the three UTEP labels which were not trailing spaces.

[dustymc]

barcode~'^DGR[A-Z0-9_-]{0,8}$' covers them. They're in the list because that's a DGR claim with an MSB container. That's never made any difference before, but it will soon. I suspect the easy thing is to flip all DGR claims and containers to MSB but ???? I don't care how it happens, but allowing nonunique barcodes isn't very compatible with unconstrained claims.

[Jegelewicz]

flip all DGR claims and containers to MSB

That seems reasonable, but @campmlc will have to agree.

[Jegelewicz]

remove all dimensions from these unless I get a better recipe very soon.

I fixed DGR13141

but I don't know what to do with the remaining MSB stuff (weird vials - a lot of parasites)

[dustymc]

I believe this is fully functional at test, please test.

The error logs have been suggesting this needs to get to production ASAP.

BUT, I also suspect some MSB folks really need access to DGR containers, would like to hear from @campmlc on converting if at all possible.

I think everything else can be dealt with at any time.

[dustymc]

I changed ownership of these containers:

temp_noclaim_bc_inst.csv.zip

I manipulated these labels:
temp_funky_label.csv.zip

I remove dimensions from these containers:

temp_partial_dimensions.csv.zip

[ccicero]

@dustymc Looking at barcode series and see your entry:

I tried deleting but don't have permissions - ? I'm not sure about this one but we have a working barcode series for our LN2 chest freezer:

Can you please delete the messy one? Thanks!

[dustymc]

@ccicero please confirm that you mean https://arctos.database.museum/info/barcodeseries.cfm?action=edit&key=666455329 should be DELETED.

[dustymc]

@ccicero these are the containers using the claim I think you want deleted, please advise.

arctosprod@arctos>> select barcode from container where institution_acronym='MVZ' and barcode~'^LN2CF-[A-C]{1}-[0-9]{1,2}$'
arctos-> ;
  barcode   
------------
 LN2CF-A-1
 LN2CF-A-2
 LN2CF-A-3
 LN2CF-A-4
 LN2CF-A-5
 LN2CF-A-6
 LN2CF-A-7
 LN2CF-A-8
 LN2CF-A-9
 LN2CF-A-10
 LN2CF-B-1
 LN2CF-B-2
 LN2CF-B-3
 LN2CF-B-4
 LN2CF-B-5
 LN2CF-B-6
 LN2CF-B-7
 LN2CF-B-8
 LN2CF-B-9
 LN2CF-B-10
 LN2CF-C-1
 LN2CF-C-2
 LN2CF-C-3
 LN2CF-C-4
 LN2CF-C-5
 LN2CF-C-6
 LN2CF-C-7
 LN2CF-C-8
 LN2CF-C-9
 LN2CF-C-10
(30 rows)

[campmlc]

@dustymc can you send a list of what containers are under DGR as institution? Yes, MSB folks need access, but I'll need to look over first to decide in conversion

[dustymc]

Please open issues for any remaining questions.