EUID - A Summary

<< Click to Display Table of Contents >>

EUID - A Summary

The concept of the EUID [yoo-id] is to provide an EQuIS Unique Identifier to tables in the database, e.g. every record in the database can have a unique ID that distinguishes it from any other record in the database, regardless of what table it is in. This is different from IDs in the past (version 5 and earlier) that have been unique within a table (and sometimes the combination of table and facility).


EUID columns are not always required. In some cases, a EUID column can exist on a table that allows NULL values. This is not the case with the IDs that have been converted, such as SAMPLE_ID, TEST_ID, etc.; they are already required fields in the database. Additionally, when auditing is turned on for a table, the EUID field will be populated for every record in that table, and then the EUID will become a required field in that table. This is done with the EQUIS.POPULATE_EUID stored procedure, which also adds a trigger that insures EUIDs are populated. Not requiring EUIDs to be populated on every table reduces the number of IDs that are assigned and the overhead on the database for such.


The EQUIS.FIND_EUID procedure will find the primary data object based on the EUID. You execute the procedure and pass in a EUID value and it will return the details of the primary data object to which that EUID is assigned (e.g. a record in DT_LOCATION or DT_SAMPLE or some other primary table).


The EQUIS.ADD_EUID_INDEX function will add the index (to increase performance) to the table, regardless of whether or not the EUID column is computed. If the EUID column is computed, the index is added on the underlying ***_ID column.


Updating EQuIS 5 Databases


Before EUIDs can be applied, a few updates to the schema of the database, and a conversion process for existing IDs must be completed via a script called "Convert_EQ5_ID_to_EQ6_EUID.sql", typically installed to C:\Program Files\EarthSoft\EQuIS\db\utility). The changes are to the table or procedure that assign the IDs such that every ID handed out becomes unique, regardless of what table or facility it belongs to. The conversion script modifies existing IDs (e.g. SAMPLE_ID, TEST_ID, USER_ID, EBATCH, etc.) so that they are unique within the database, but it does not create the EUID field in every table; it simply changes the existing IDs so they comply with the concept of the EUID. Note that the script does not convert the FACILITY_ID. While it does not convert this field, it still complies with the concept of a EUID, since it was previously unique within the DT_FACILITY table, and all other IDs that are assigned during and after the conversion will not include the values used by existing FACILITY_IDs. Doing this allows users to retain use of a very common ID and reduce the large task of reassigning all existing IDs in the database.


The second update that needs to be done is adding a EUID field to each table in the database. This is accomplished with a stored procedure called EQUIS.ADD_EUID. Note that for tables that already had ID fields (e.g. FACILITY_ID, SAMPLE_ID, TEST_ID, etc.), the ID field actually stores the EUID value; however, for clarity a computed column is added to the table called EUID; i.e. the EUID is actually just set equal to the value found in the existing ID column.