Validation Flow
Validation Flow Diagram
The diagram below presents the plug-in validation flow. Each step of the plug-in validation flow is described in the sections below.
Validation Start
When a dynamic action implementing the Automatic Constraint Validation Pro is fired, the plug-in evaluates items/columns filtered by a combination of dynamic action Event Type and Selection Type. The plug-in supports Lose Focus and Change Events with Selection Type jQuery Selector and Region. The plug-in ignores items when the source is not a database table column. In the case that the plug-in is triggered by a Change event and by a Lose Focus event on the same page item input, then the second duplicate invocation is detected and will be skipped.
Before the plug-in validation begins the Validation start event is triggered on the document - dynamic actions listening to this event have access to special validation objects exposed via this.data. A validation start event can be used to display the loading indicator and the Validation end to remove it immediately after validation is complete.
Client Process
Automatic Constraint Validation is performed on every form item and IG column that triggers the dynamic action.
- On page load, the plug-in identifies the supported regions and their associated database objects. It then stores information about the record ids and columns that are involved in composite constraint conditions in the client process.
- When the plug-in is triggered, it collects the item name, value, and the type of region of the triggering element, plus information about the record id and other involved item names and values. When the current item occurs in composite constraint conditions, then all involved item values are collected. In Interactive Grids, record ids of updated and deleted records/rows are collected. This information is sent to the server process.
- The server process performs constraint checks and sends back messages with the validation results.
- The client process will receive multiple result messages in the reply and will clear and display inline messages and page notifications.
- A second test for uniqueness is performed by probing new and changed records/rows in the Interactive Grid.
Server Process
The server process accesses the system catalog and the APEX page definition for information about the data types, formats, storage limits, and database constraints. The process then generates and executes probing queries to validate the constraints and sends back messages containing the validation results.
Pre-Processing
Before the database constraints can be evaluated, input strings have to be converted to database data types. The plug-in identifies the database tables and columns and generates checks for conversion and storage limits of common data types:
For numeric, date, and timestamp data types, the data must be convertible according to implicit or explicit format strings and NLS settings. Additional limits exist for the storage of columns with string and number data types.
For numeric, 'year to month' interval, 'day to second' interval, date, and timestamp data types, the precision and scale set storage size limits for the number parts.
For numeric, date, and timestamp data types, the minimum and maximum range is derived from the page item definitions.
- The minimum and maximum date value from the form-item or grid-column settings can be an absolute value in the current item date format or 'YYYYMMDDHH24MI' format or a relative value with respect to today's date, such as +7d, or a dynamic value using substitution syntax, providing the substitution returns the correct date format or relative value.
For date and timestamp data types, the permitted range for the calendar year can be restricted with optional plug-in settings.
For varchar2 data types, the maximum length in combination with the character semantic (char or byte) defines a storage limit. The column/item setting 'Trim Spaces' is recognized in the length calculation.
Constraint Validation
For the detected table columns the plug-in identifies the constraint conditions and will perform the following checks and produce messages:
- Value required, e.g., not null constraints
- Data conversion with format mask.
- Range limits for numbers depend on precision and scale.
- Compare Text length with maximum length in char or byte semantic for character columns, after trimming leading and/or trailing blanks according to the item setting.
- Table check constraints like: income > 0, credit < credit_limit, channel in ('online','direct'). For multi-column constraints, all involved columns must be selected in the query and must exist in the grid or form as visible or hidden items to perform the validations.
- Table/View Foreign Key Constraints with one or more referenced columns. The existence test is done by probing the database for existing values.
- Table/View Unique Key Constraints with one or more columns and with support for composite primary keys or ROWID in the uniqueness test. The uniqueness test probes the database for existing values where rows that are modified or deleted in the Interactive Grid will be excluded from the probe.
Validation End
When validation is finished the plug-in triggers the Validation End event on document and triggers an item/column event, Validation Passed or Validation Failed, depending on the validation result.