View source | Discuss this page | Page history | Printable version   
Toolbox
Main Page
Upload file
What links here
Recent changes
Help

PDF Books
Add page
Show collection (0 pages)
Collections help

Search

Constraints and Triggers

Contents

Introduction

Both check constraints and triggers are objects defined physically in database. This document will not explain the basis for triggers and constraints but just the particularities Openbravo ERP has in their usage.

Naming

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.

Bulbgraph.png   The name of the constraints and triggers must not exceed the 30 characters as the maximum length of an object name in oracle is 30 characters.

Constraints

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.

A step by step HowTo on How to add a Constraint can be found: here

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.

Backwards compatibility

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.

Indexes

Bulbgraph.png   Function based indexes and operator classes are supported from PR15Q4

Operator Classes

In PostgreSQL Operator Classes certain operator classes (text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops) enables using indexes in queries involving pattern matching expressions. For instance, the following query:

SELECT name
FROM c_bpartner
WHERE name LIKE 'John%'

would not use an index created like this:

CREATE INDEX c_bpartner_name
  ON c_bpartner
  USING btree
  (name COLLATE pg_catalog."default");

but would use an index defined with an operator class:

CREATE INDEX c_bpartner_name
  ON c_bpartner
  USING btree
  (name COLLATE pg_catalog."default" varchar_pattern_ops);

Operator Classes are not needed in Oracle to use an index in the previously defined query, in that case if the index column defines an operator class, the operator class will have no effect.

Function based indexes

Openbravo supports the use of functions in indexes. For instance, this, along with the use of an operator class would enable the use of indexes in case insensitive queries that use the iStartsWith operator, like this one:

SELECT name
FROM c_bpartner
WHERE UPPER(name) LIKE 'JOHN%'

The following index could be used by the previous query:

CREATE INDEX c_bpartner_name
  ON c_bpartner
  USING btree
  (upper(name) COLLATE pg_catalog."default" varchar_pattern_ops);

Any function can be used in the indexes, as long as it is deterministic. Even needed functions are supported:

CREATE INDEX c_bpartner_name
  ON c_bpartner
  USING btree
  (UPPER(REPLACE(name, 'a', 'b')));

The only restrictions is that only one function expression can be used per index, so the following index it is not supported:

CREATE INDEX c_bpartner_name_id
  ON c_bpartner
  USING btree
  (UPPER(REPLACE(name, 'a', 'b')),
   UPPER(c_bpartner_id));

Triggers

A step by step HowTo on How to add a Trigger can be found: here

Syntax

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 it possible to correctly export and import them using DBSourceManager. These rules are detailed in the PL-SQL code rules document.

OB Conventions

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.

Oracle's laziness

Whereas oracle evaluates expressions in a lazy manner PostgreSQL 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.

Soft disabling

All triggers in Openbravo ERP must 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:

In Oracle

 
    IF AD_isTriggerEnabled()='N' THEN 
      RETURN;
    END IF;

In PostgreSQL

 
     IF AD_isTriggerEnabled()='N' THEN
        IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
     END IF;

Object returning

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;

Retrieved from "http://wiki.openbravo.com/wiki/Constraints_and_Triggers"

This page has been accessed 3,395 times. This page was last modified on 8 September 2015, at 16:20. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.