User ID format in Postgres vs Oracle - bcgov/nr-spar GitHub Wiki

Context

In oracle database for SPAR, user ids are stored in such format {providerName}\{userName}.

For instance, an IDIR user with user name GROOT will have its user id stored as IDIR\GROOT.

Similarly, a bceid user will have its ID stored as BCEIDBUSINESS\SNOOP.

The problem

Having a backslash in a user id string is inherently harder to work with as the backslash is used for escaping characters in many system.

Here's an interesting example of how it's hard to work with in JavaScript:

  1. Using \\ to store the user id
    • MicrosoftTeams-image (3)
  2. What actually gets stored
    • MicrosoftTeams-image (4)

The Solution

To reduce the string manipulation gymnastic needed when working with user id, Team Evergreen has decided to go with the format {providerName}@{userName}, basically replacing the \ with an @.

Examples:

  • IDIR\GROOT -> IDIR@GROOT
  • BCEID\SNOOP -> BCEIDBUSINESS@SNOOP

Note

Ideally it is better to store user ids as they are provided by FAM, such as oaisdoigfnad@idir.

However, to have things working with the data sync engine that updates data on Oracle DBs, it is easier to use a format that is as close as the format used in Oracle, so that a simple replacement of @ with \ is all we need to do.