ERP 2.50:Developers Guide/Concepts/DB/Constraints and Triggers
Both check constraints and triggers are objects defined physically in database. Remind that this document will not explain the basis for triggers and constraints but just the particularities Openbravo ERP has in their usage.
When adding a check constraint, triggers and indexes modularity naming rules have to be taken into account. This because triggers and indexes are global objects for a database. The modularity naming rule is as follows: the constraint /index/trigger name must start with the DB Prefix of the module the constraint belongs to. For instance, MYMODULEDBPREFIX_CONSTRAINTNAME.
In the case of indexes and constraints, if the index/constraint is added to a table of another module then an additional EM_ prefix is required: EM_MYMODULEDBPREFIX_CONSTRAINTNAME.
By following this naming rule the index/trigger/constraint is exported to the module directory and packaged with the module.
NOTE: The name of the constraints and triggers must not exceed the 30 characters long, otherwise it will crash during the installation of the module on another instance of OB. For more information see issue 12779
Check constraints do not have any particularity in Openbravo, except how they should be named and how the back-end treats them to show messages.
It is possible to define a message to be shown when the rule defined by the constraint is not satisfied. How to do that is explained in the Messages documentation.
Modules should allow compatibility for other ones built on top of them at least between minor versions, additionally there could be user data already in the application if it is in a productive environment. This means that user data or other module's could rely in the current database model and in case a new constraint is added or an existent one is modified to be more restrictive than it was, backwards compatibility could be broken. Therefore it should be avoided to add new constraints or to modify existent ones to make them more restrictive during between versions.
Function based indexes
It is possible to use functions in the index columns, as long as:
- No more than one column uses a function expression per index
- The function expressions does not contain empty string parameters
For instance, this indexes are supported now:
CREATE INDEX c_bpartner_upper_name ON c_bpartner USING btree (upper(name) COLLATE pg_catalog."default"); CREATE INDEX c_bpartner_upper_replace_name ON c_bpartner USING btree (REPLACE(upper(name),'A','B') COLLATE pg_catalog."default");
But these are not:
CREATE INDEX c_bpartner_bad_1 ON c_bpartner USING btree (upper(name) COLLATE pg_catalog."default", upper(c_bpartner_id)); CREATE INDEX c_bpartner_bad_2 ON c_bpartner USING btree (REPLACE(upper(name),'A','') COLLATE pg_catalog."default");
In postgres it is required to use certain operator classes (text_pattern_ops, varchar_pattern_ops or bpchar_pattern_ops) in queries involving pattern matching expressions.
For instance, the following index:
CREATE INDEX c_bpartner_upper_name ON c_bpartner USING btree (upper(name) COLLATE pg_catalog."default" varchar_pattern_ops);
will potentially be used in the following query:
SELECT * FROM c_bpartner WHERE upper(name) LIKE 'JOHN%'
This means that if the grid and selectors are configured to use the iStartsWith operator, the queries done to retrieve the data will potentially take advantage of the indexes using the operator index.
Triggers, as the rest of PL code in Openbravo ERP, should be written following some restrictions in order to make them compatible between PostgreSQL and Oracle and to make possible to correctly export and import using DBSourceManager. These rules are detailed in the PL-SQL code rules document.
Exceptions and messages
Triggers can raise exceptions (it is the common practice to abort a transaction), the back-end captures that exception in order to show a proper message. Read in the messags documentation how to do it.
Whereas oracle evaluates expressions in a lazy manner PostrgreSQL does not do so. This specially important for triggers that are for insert and delete because in the first case there are :new variables and in the second one there are :old ones, so when writing if clauses (even in Oracle if we want to do compatible code) this must be taken into account writing two clauses instead of just one.
All triggers in Openbravo ERP should be able to be disabled softly. This means not disabling it in database but just in a logic way. This is used by DAL when it is importing data: triggers must be disabled in order to allow data imporation without triggering them, but that only should affect to the session that it is executing the importation. To add this capability to triggers, the first lines of each of them should look like:
IF AD_isTriggerEnabled()='N' THEN RETURN; END IF;
IF AD_isTriggerEnabled()='N' THEN IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; END IF;
PostgreSQL trigger function must explicitly take care of returning the trigger object, also depending on the type of the trigger. This means that the last line of the trigger function must be something like:
IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
ERP 2.50:Developers Guide/Concepts/DB/Tables | ERP 2.50:Developers Guide/Concepts/DB/PL-SQL code infrastructure