Creating details entries using an SQL Procedure

I blogged the other day (see here) about a way to generate a report of the user to group assignment in the ObjectServer, which was the result of a RFE request.   Sort of related to that, we've had another RFE request that can be implemented with an SQL procedure, that I think is worth sharing here.

 

The request (see here for details) is to add a method for creating details in the alerts.details table, in a similar manner to the jinsert SQL procedure that simplifies the creation of journal entries.

The alerts.details table is used to hold additional elements from an event that don't have a dedicated column in alerts.status.  There can be multiple details associated with each event, the Primary key for a detail is comprised of the Identifier of the event the detail is related to and the sequence number of this detail in the set of details for this event. The pain, for anything other than the probe that created the event, creating a detail is knowing what Sequence number to assign, as that requires a query of the alerts.details prior to performing the insert.

 

The advantages of having a procedure do the insert are that it hides the implementation of the underlying table.  The procedure can take care of ensuring the sequence number is unique.   The SQL for the procedure is below:

 

1  create or replace procedure dinsert( in Identifier char(255), in Name char(255), in Detail char(255) )
2  declare
3  seq int;
4  detail_found bool;
5  begin
6        -- find the next spare detail slot.
7        -- Allow a maximum of 25 details per alarm, decrease or increase this value to suit your environment
8        for seq = 1 to 25 do
9        begin
10                set detail_found = false;
11                -- Create a candidate primary key for the Identifier and search to see if its already been used
12                for each row ad in alerts.details where ad.KeyField = Identifier + '####' + to_char(seq)
13                begin
14                        -- This keyfield value exists, try another
15                        set detail_found = true;
16                end;
17
18                -- The primary key doesn't exist, so create the detail
19                if ( detail_found = false ) then
20                        insert into alerts.details ( KeyField, Identifier, AttrVal, Sequence, Name, Detail ) values ( Identifier + '####' + to_char(seq),
21                                Identifier, 1, seq, Name, Detail );    
22                        break;
23                end if;
24        end;
25  end;

 

(note line numbers will need to be removed prior to submitting the SQL to the ObjectServer)

Line 1:  Defines the procedure, its called dinsert and takes three input parameters, the Identifier of the event we're attaching the detail to.  The name of the detail and the value of the detail
Lines 2 &3: We declare two local variables, seq which is of type int.  We will use this to hold the sequence number & detail_found which is a boolean, used to determine when we have a unique primary key
Line 8:  The start of a FOR loop that we will use to find a unique primary key. We're going to iterate a maximum of 25 times to find a unique key.  This limits the number of details that can be associated with an event with this procedure to 25, increase or decrease the value as appropriate for local conditions.

Line 10: Set the detail_found flag to false before we search for a candidate primary key
Line 12: Create a candidate primary key by concatenating the Identifier, #### and the current sequence number - Identifier + '####' + to_char(seq) - Then search the alerts.details table to find the key.  Because a primary key has to be unique we know we will find one or zero rows.  The search will be quick, because the primary key column of any table is always indexed, so we're essentially doing a direct look up on the key value.

Line 15:  If we reach here, the candidate primary we created on line 12 isn't unique, so we set detail_found to be true as we will need to go round the FOR loop again, increment the sequence number and try again.
Line 19:  Check the value of detail_found, if its false, then the candidate primary key is unique
Line 20: Create the detail by inserting into the alerts.details table, using our candidate key

Line 22:  We want to exit the FOR loop now, as we've created our detail

Lines 23,24,25: Simply closing out the FOR loops and the procedure.

 

The procedure can be executed as follows from a trigger
    execute procedure dinsert(  old.Identifier, 'ServiceAffected', ServiceName );

The procedure could also be invoked by right click tools etc.

 

The procedure should be considered a starting point, rather than the final word. For example the procedure as starts will allow multiple details with the same name to be created, which may not be desirable.

Some notes on best practises surrounding the use of alerts.details are worth reviewing in the 7.4.0 OMNIbus product documentation

 

If you think having a procedure like this as part of the default configuration would be useful, or if there are other SQL functions that you would like to see added to the ObjectServer, then please let us know via the RFE tool