Saved Signoffs unhelpful error message

[slominskir]

When executing rules in the Saved Signoff page, users may encounter an unhelpful EJBTransactionRollbcckException error message as pictured:

We need to either improve the error message to indicate more precisely the problem, else remove the constraint check causing the database transaction rollback.

The underlying cause is:

java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SRM_OWNER.GROUP_SIGNOFF_HISTORY_AK1) violated

Which is the constraint defined here:

srm/container/oracle/initdb.d/02_ddl.sql

Line 287 in f91e6a8

CONSTRAINT GROUP_SIGNOFF_HISTORY_AK1 UNIQUE (GROUP_ID,SYSTEM_ID,COMPONENT_ID,MODIFIED_DATE)

This constraint effectively says that you can't modify a group's component signoff more than once a second.

The Saved Signoff page allows admins to define rules to quickly modify group component signoff in bulk very quickly. If any of the rules overlap, then an attempt to update a group's component signoff more than once a wall-clock second may occur. In the provided screenshot notice the duplicate rule on the System CLAS12 ALERT AHDC. The two rules for that System actually do vary by group, one for each of the two groups, but downgrade cascade behavior results in the last group being updated twice.

Reported by: moser

[slominskir]

Aside: The constraint exists because the database stores a history of all changes, at the second resolution. To relax this constraint we would need to replace the DATE column with a datatype that supports more fine-grained fractional second resolution. Allowing users to modify a group's component signoff more than once per second isn't necessary though, and would only be hiding a set of saved signoff rules with overlapping effects.

[slominskir]

We could check if a System is found more than once in the set of saved signoff rows selected and prevent execution of the selected rules together in bulk. Fail with a message such as "Only one saved signoff rule per System per bulk execution", plus include the name and counts of Systems with over one row selected. This would drastically minimize chances of concurrent update.