Understanding Depth Events
This topic details the tasks that need to be performed by the SQL scripts, as configured on the Panel | Editing tab of the Depth Adjustment Process Property Editor.
The events that need to be catered for are:
- UPDATE - occurs when an existing interval is changed.
- DELETE - occurs when an existing interval is deleted.
- INSERT FIRST - occurs when a new interval is inserted into an empty dataset; i.e. the original query returned no records.
- INSERT NEW - occurs when a new interval is added to a dataset that already has records.
- INSERT SPLIT - a special type of insert that occurs when an existing interval is split into two parts.
These events are used whenever updates are written to the database. This usually happens:
- when the user executes File | Save Changes from the main menu in the depth adjustment process
- when an interval is deleted
- when a new interval is inserted
During a database commit, Micromine Geobank examines all the items in the change log and determines which actions are required to perform the necessary updates. It will then compile and execute a list of SQL statements, based on the SQL scripts that were configured for each event.
For each interval that needs to be updated, Micromine Geobank will locate the corresponding row in the primary dataset. It will use the values from this row to resolve any master-detail links or parameter substitutions that were included in the SQL scripts.
Micromine Geobank performs all of the updates in a single database transaction. If the updates fail for some reason, the entire transaction will be rolled back. This should be kept in mind when developing stored procedures or functions for the various events.
Micromine Geobank must be able to pick up an exception at any point during the execution of the scripts. It must also maintain control over the primary database transaction.
It is important to understand the way in which Micromine Geobank handles the depth
adjustment events.
UPDATE
The data in a panel cannot be modified unless an update script has been defined for that panel. When this action is disabled, the panel can only be used as a reference panel (i.e. no modifications are allowed).
The UPDATE event is the most common of all the events. It is triggered when the details for a given interval need to be changed.
A single depth adjustment may affect more than one interval, resulting in several update events. For example, as shown in the diagram below, adjusting the floor depth of Interval A will (at the very least) affect Interval B as well.
In this case, Micromine Geobank will raise two update events: one for interval A and one for interval B. The first update event will adjust the floor depth of interval A. The second event will adjust the roof depth of interval B.
DELETE
The DELETE event occurs when an interval is deleted.
Only one delete event is raised by the depth adjustment process; however, depending on foreign key relationships and relevant cascade rules, this may result in more than one record being deleted from the database.
The depth adjustment process does not allow the user to undo a delete action. To ensure consistency, a delete event will always take place in isolation, i.e. the user must save all changes before executing a delete.
INSERT FIRST
Inserting a new record into an empty dataset poses a problem, because there is no original record from which to source values for the special parameters (e.g. drillhole number). Consequently, the SQL used for the INSERT NEW event must make use of normal substitution parameters to obtain these values, resulting in the display of a substitution parameter form at execution time.
INSERT NEW
This event is triggered when a new interval straddles the depth boundary between two existing intervals, as shown in the following example:
This action results in three events:
- An UPDATE event for Interval A
- An UPDATE event for Interval B
- An INSERT NEW event for the new interval
INSERT SPLIT
Now consider the following example, where the new interval splits an existing interval:
This results in the following:
- An UPDATE event to adjust the floor depth of Interval A
- A special INSERT SPLIT event to generate the new (split) part of Interval A
- An INSERT NEW event for the new interval
This is why the INSERT action requires several SQL scripts. Depending on the type of data, there may be a subtle difference between the scripts used for INSERT NEW and INSERT SPLIT events. For example, when splitting a seam, the user will have to supply a code for the new “split” (by way of a standard Micromine Geobank substitution parameter dialog).
Avoiding Table Constraint Violations
As a rule, geological databases are not highly normalised and therefore the depth range is often used as the unique part of the primary key in interval data tables. When used on such tables, the depth adjustment process could potentially result in primary key violations.
Consider the following example, where the adjustments would result in a key violation if they were applied in depth order:
| Before | After |
|---|---|
| 51.5 to 52.0 m | 51.5 to 52.2 m |
| 52.0 to 52.2 m | 52.2 to 52.4 m |
| 52.2 to 52.4 m | 52.4 to 52.6 m |
| 52.4 to 52.8 m | 52.6 to 52.8 m |
With this possibility in mind, the depth adjustment process will sometimes defer an adjustment if there is a possibility that it could result in a duplicate depth range. In the above example, the third adjustment would be performed before the second one. It is therefore important to note that the order in which adjustments are executed cannot be guaranteed.
Despite these precautions, it is impossible to guarantee that no constraint violations will take place during the course of the adjustment process. Therefore, the best way to avoid this type of problem is to ensure that the depth columns are not being used as the basis for a unique database constraint.
The adjustment process will follow this logic irrespective of whether the depths form part of the primary key or not. This is due to the fact that Micromine Geobank is currently unable to retrieve constraint metadata from SQL query result sets.
The need for stored procedures
Very often, the database updates that need to be performed in response to the depth adjustment events are too complex to be expressed in a single SQL statement. In such cases, the SQL script can be used to invoke a user-defined stored procedure.
Consider the example below, where a new lithology interval has been inserted into an interbedded unit. The original interval (A) has two detail records associated with it (AA and AB).
In addition to inserting the new interval (B), we need to split interval A into two parts, effectively adding a new "master" interval (C) and also building two new "detail" entries (CA and CB).
In broad terms, we need to know how to:
- update units A, AA and AB to reflect the new depth range;
- insert units B, C, CA and CB with new primary keys and other required fields;
- copy down additional attributes from A to C, AA to CA and AB to CB.
Since Micromine Geobank supports a flexible data model, the table structure and foreign key constraints are not predefined; therefore, all of these actions need to be configured.
It is not possible to have one generic tool that can cater for this level of complexity "up front"; consequently, we need to supplement the Micromine Geobank functionality by way of stored procedures in the back-end database. For example, when a new interval is inserted, Micromine Geobank executes a pre-configured SQL query, which in turn could execute a stored procedure to do the complex database updates required by the event. These stored procedures may have to raise exceptions* where necessary, in order to allow Micromine Geobank to roll back changes made to the database.
As a result, the configuration of the depth adjustment object can be quite complicated. Unfortunately this is the only way to ensure that the tool will remain useful in as many environments as possible.
* Micromine Geobank performs all of the updates in a single database transaction and will roll back the transaction if a database exception is raised. Keep this in mind when developing your stored procedures.
The Micromine Geobank Standard Demonstration project contains examples of stored procedures that cater for the adjustment of lithology depths in the standard data model.
When raising exceptions in the stored procedure code, you have to ensure that Micromine Geobank will be able to detect the exception (if appropriate). For example, when using the RAISERROR method in Microsoft SQL Server, a severity level of 11 through 16 must be used in order to ensure that the exception is passed on to Micromine Geobank. Values of 10 or below will not be treated as an error by Micromine Geobank, while levels of 17 and higher are normally reserved for system use.
Refer to the documentation provided by your database vendor for more information in this regard.
At runtime, the SQL scripts are opened in a master-detail relationship to the downhole data, as shown in the following diagram.
Therefore, the SQL scripts may use standard master-detail syntax to refer back to the original downhole data record, for example:
WHERE PROJECT = :PROJECT AND SITE_ID = :SITE_ID
A special type of substitution parameter can be used for the Update, Insert and Delete scripts. The parameter token consists of the string "&Param" followed by a column name or a standard parameter name. At execution time, Micromine Geobank replaces the parameter token with the relevant text value. Valid parameter names are listed below:
| Parameter | Description |
|---|---|
| &ParamOldDepthFrom | Roof depth of the interval before adjustment |
| &ParamOldDepthTo | Floor depth of the interval before adjustment |
| &ParamOldCode | Previous annotation code |
| &ParamNewDepthFrom | Roof depth of the interval after adjustment |
| &ParamNewDepthTo | Floor depth of the interval after adjustment |
| &ParamNewCode | New annotation code |
| &ParamColumnName | Where ColumnName is the name of a column returned by the downhole data query |
The values for the standard parameters are generated by the depth adjustment process, while the value for a parameter of the form &ParamColumnName is retrieved from the original interval data (as returned by the downhole dataset).
The following example illustrates the use of these parameters in an SQL statement:
INSERT INTO DBO.GB_NAMED_ZONE
(PROJECT, SITE_ID, ZONE_TYPE, ZONE_CODE, DEPTH_FROM, DEPTH_TO)
VALUES
('&ParamPROJECT',
'&ParamSITE_ID',
'E',
'&ParamNewCode',
&ParamNewDepthFrom,
&ParamNewDepthTo)
After Micromine Geobank has done the substitution, the script will look something like this:
INSERT INTO DBO.GB_NAMED_ZONE
(PROJECT, SITE_ID, ZONE_TYPE, ZONE_CODE, DEPTH_FROM, DEPTH_TO)
VALUES
(' LEOPARD',
' SW02402',
'E',
'S4A',
129.180,
129.980)
Alternatively, the SQL script can be used to invoke a stored procedure or function. The parameters are used in exactly the same way, for example:
EXEC dbo.uspDepthAdjustLithUpdate
@chvTargetProject='&ParamPROJECT',
@chvTargetSiteID='&ParamSITE_ID',
@intTargetInterval=&ParamINTERVAL_SEQ,
@fltNewRoof=&ParamNewDepthFrom,
@fltNewFloor=&ParamNewDepthTo,
@chvNewCode='&ParamNewCode'
which will result in something like the following:
EXEC dbo.uspDepthAdjustLithUpdate
@chvTargetProject='LEOPARD',
@chvTargetSiteID='SW02402',
@intTargetInterval=10200,
@fltNewRoof=201.750,
@fltNewFloor=216.880,
@chvNewCode='CLS'
Traditional Micromine Geobank substitution parameters can also be used in these SQL statements. In that case, the user will be prompted for values in the usual way, using a standard substitution parameter dialogue.
A parameter string in the form &ParamColumnName is replaced by a text representation of the value stored in column ColumnName. In the case of a floating point value, this could result in something like “12.599871223”. In the case of a date field, the string returned by Micromine Geobank will be in the form specified as the “SQL Date Format” in the Micromine Geobank Database Properties Editor. In some cases you may have to use appropriate SQL functions to reformat this value. See your database vendor documentation for more information in this regard.
In the above example, the depth values for standard substitution parameters are rounded to two decimals. This accuracy depends on the Minimum Width setting on the Advanced page of the Database Connection Property Editor under Micromine Geobank Options | Professional.