Auto-populating primary key IDs for instruments/proposals/users

[casey-pnnl]

Pacifica Metadata version

v0.70 (current version)

Pacifica Core Software versions

N/A

Platform Details

RHEL 7

Scenario:

This issue is to submit a request for auto-populating/auto-incrementing IDs for instruments, proposals, and users. Currently the Pacifica Metadata ORM allows for any ID to be inserted, which can cause collisions, and is untenable for automatically populating external users. Want to see the existing primary-key ID column changed to an auto-incrementing integer for these tables:

• instruments
• proposals
• users

If tracking existing organizational IDs is still valuable, a separate, non-primary-key column could be added for that purpose.

In any event, it is strongly preferred to decouple organizational IDs from the primary key of the table.

Steps to Reproduce:

Currently we are populating internal users via scripts running off of an organizational ID and external users IDs are handpicked.

External users are, for now, irrelevant as they have no mechanism to log in. In order to prepare for external access we need to set up the database so that different organizations (other national labs, universities, etc), which may have organizational ID collisions with our organization are supported. This also alleviates problems which may happen if an organization uses a non-integer ID (sha hash, GUID, etc)

Expected Result:

Auto-incrementing IDs will automatically populate each new instrument, proposal, and user database entry with a guaranteed-unique value. We may need to rerun our ID-swapping scripts on production to switch records to the new setup, after which all future IDs will be automatically handled by the database and not by our own devised numbering systems.

This request is a piece of the problem of managing external users, and along with authentication mechanisms like Keycloak, pave the way to automatic self-directed external user registration on the frontend.

Actual Result:

At the moment, external users lack the ability to log in, so again this is currently a non-issue. However it will become very important over time, as in the current setup a live person that is knowledgeable of the Pacifica ORM has to add each new external user by hand.

[GitBytes]

Related to #200

[dmlb2000]

This is going to be blocked by results from #205 please add opinions on keeping vs dropping IDs there.