Creating API views

 

 

This page is currently an incomplete work in process

Overview

This document describes style and methodology for implementing API views in xTuple ERP. A basic explanation of how API views are intended to be used may be found in the associated ApiWhitePaper.

There are basically four ways in which business logic is handled in xTuple API views:

Most of these functionalities are well documented in Postgres documentation, so this topic will just describe how they are implemented in xTuple API views.

Style Guidelines

xTuple formats views in a particular way to make them easy to maintain and edit. Please do not submit views created by using script exporting utilities in pgAdmin or other database utilities. We can not accept the script contributions unless they conform to xTuple format shown here:

BEGIN;

  --[view] View

  SELECT dropIfExists('VIEW', '[view]', 'api');
  CREATE OR REPLACE VIEW api.[view] AS

  SELECT
    table_field1 AS literal1,
    table_field2 AS literal2 [,
    ...]
  FROM [table1, table2,...]
  WHERE ([clause])
  ORDER BY [field1, field2,...];

  GRANT ALL ON TABLE api.[view] TO openmfg;
  COMMENT ON VIEW api.[view] IS '[view]';

  --Rules

  CREATE OR REPLACE RULE "_INSERT" AS
    ON INSERT TO api.[view] DO INSTEAD

  INSERT INTO [view]
           (table_field1,
            table_field2,[
            ...] )
           VALUES
           (NEW.literal1,
            NEW.literal2 [,
            ...] );

    CREATE OR REPLACE RULE "_UPDATE" AS
    ON UPDATE TO api.[view] DO INSTEAD

    UPDATE [view] SET
      table_field1=NEW.literal1,
      table_field2=NEW.literal2 [,
      ...]
    WHERE ( [view] )

    CREATE OR REPLACE RULE "_DELETE" AS
    ON DELETE TO api.[view] DO INSTEAD

    [sql_statement];

COMMIT;

Of course there are also many examples that conform to these guidelines online here.

Functions

xTuple also makes liberal use of pg/plsql functions to handle most all complex business logic in xTuple ERP. The good news about this is it makes writing the views much easier because you can often leverage pre-existing functions on your views, especially for the rules. The Prospect, view for example, calls the saveCntct function to handle the business logic behind the contact information of a CRM Account. The functions that most frequently have to be created to support API Views are functions that retrieve key values for codes such as getClasscodeId. The biggest challenge with functions is simply finding what you need as there are over a thousand in an OpenMFG database. We have attempted try to make the names reasonably readable for people, and the procedures inside are usually commented. We do have some community members currently working to document the database objects more thoroughly. A link to all the functions currently existing in the PostBooks code base may be found here.

Views and View Rules:

xTuple has implemented four view rules for every API view _RETURN, _INSERT, _UPDATE, and _DELETE. The rules tell the view what to do when a user calls a SQL statement against it. The _RETURN rule is automatically created by Postgres as the basic SELECT query that defines the presentation, and it usually spans multiple tables.

Some general rules of thumb are:

  • Rename fields on the database to match the literal labels on the xTuple Graphical User Interface (GUI) as closely as is practical.

  • Where keys exist on tables, get the human readable name or code field from the associated table.
  • As with the GUI, primary keys are hidden from the user. One or more of the data fields must constitute a unique "User Key."
  • If any of the fields that make up the user key are text, convert them to varchar. Some RAD tools, like MS Access, see text as memo which can not be used as a key for updating. Converting the field to varchar allows use of the field(s) as keys in those interfaces.

  • Use outer joins on tables where the associated code values are optional

"Prospect" is a good example of a record that combines information from multiple tables. You can see how Prospect fits in the CRM relational model on the wire diagram on the CRM Basics page. This example is of the SELECT statement for the api.prospect view in version 3.1:

  CREATE OR REPLACE VIEW api.prospect AS

  SELECT
    prospect_number::VARCHAR AS prospect_number,  -- Convert text to varchar for user keys
    prospect_name AS prospect_name,
    prospect_active AS active,
    salesrep_number AS sales_rep,
    warehous_code AS site_code,
    taxauth_code AS default_tax_authority,
    prospect_comments AS notes,                   -- Change database names to match literal on GUI
    cntct_number AS contact_number,
    cntct_honorific AS contact_honorific,
    cntct_first_name AS contact_first,
    cntct_middle AS contact_middle,
    cntct_last_name AS contact_last,
    cntct_suffix AS contact_suffix,
    cntct_title AS contact_job_title,
    cntct_phone AS contact_voice,
    cntct_phone2 AS contact_alternate,
    cntct_fax AS contact_fax,
    cntct_email AS contact_email,
    cntct_webaddr AS contact_web,
    (''::TEXT) AS contact_change,
    addr_number AS contact_address_number,
    addr_line1 AS contact_address1,
    addr_line2 AS contact_address2,
    addr_line3 AS contact_address3,
    addr_city AS contact_city,
    addr_state AS contact_state,
    addr_postalcode AS contact_postalcode,
    addr_country AS contact_country,
    (''::TEXT) AS contact_address_change
  FROM
    prospect
      LEFT OUTER JOIN cntct ON (prospect_cntct_id=cntct_id)          -- Use outer joins for optional information
      LEFT OUTER JOIN addr ON (cntct_addr_id=addr_id)
      LEFT OUTER JOIN taxauth ON (prospect_taxauth_id=taxauth_id)
      LEFT OUTER JOIN salesrep ON (prospect_salesrep_id=salesrep_id)
      LEFT OUTER JOIN whsinfo ON (prospect_warehous_id=warehous_id);

The _INSERT, _UPDATE and _DELETE rules tell the view what to do when the data is changed. Functions are invoked to retrieve keys needed for the underlying table, to save changes to other tables, or process otherwise complex business logic. The main business logic addressed by the view rules, besides the basic mapping of data, are converting human readable codes back to the key values needed to populate the tables in the database. There are a series of "Get" functions and "Save" functions to handle this conversion problem. Both require some text input that describe the record you are looking for or changing, they each then return the primary key for that record to be stored in related table you are updating.

INSERT INTO prospect
        (
        prospect_number,
        prospect_name,
        prospect_active,
        prospect_cntct_id,
        prospect_taxauth_id,
        prospect_salesrep_id,
        prospect_warehous_id,
        prospect_comments)
        VALUES (
        UPPER(NEW.prospect_number),        -- Make prospect number upper case, same as GUI
        COALESCE(NEW.prospect_name,''),
        COALESCE(NEW.active,true),
        saveCntct(                         -- This "save" function handles business logic of saving contacts
          getCntctId(NEW.contact_number),  -- This "get" function gets the primary key id if one exists
          NEW.contact_number,
          saveAddr(
            getAddrId(NEW.contact_address_number), -- Another save function for address
            NEW.contact_address_number,
            NEW.contact_address1,
            NEW.contact_address2,
            NEW.contact_address3,
            NEW.contact_city,
            NEW.contact_state,
            NEW.contact_postalcode,
            NEW.contact_country,
            NEW.contact_address_change),
          NEW.contact_honorific,
          NEW.contact_first,
          NEW.contact_middle,
          NEW.contact_last,
          NEW.contact_suffix,
          NEW.contact_voice,
          NEW.contact_alternate,
          NEW.contact_fax,
          NEW.contact_email,
          NEW.contact_web,
          NEW.contact_job_title,
          NEW.contact_change
          ),
        getTaxAuthId(NEW.default_tax_authority),  -- More examples of "Get" functions in action
        getSalesRepId(NEW.sales_rep),
        getWarehousId(NEW.site_code,'ACTIVE'),
        COALESCE(NEW.notes,''));

Update rules make use of "Get" and "Save" helper functions essentially the same way. Delete rules can use them as well. Delete rules can simply be a delete statement to the record being deleted. Sometimes, however, special delete functions are invoked to process complex business logic:

CREATE OR REPLACE RULE "_DELETE" AS
    ON DELETE TO api.prospect DO INSTEAD

SELECT deleteProspect(getProspectId(OLD.Prospect_number));

Table Constraints

Table constraints are the last line of defense to prevent data corruption in the database. The xTuple database makes liberal use of foreign keys, unique constraints and other types of constraints at the table level. This helps prevent users who import data directly from accidentally importing invalid data. It also has the secondary benefit of preventing developers from over looking validation in their application code. The database simply will not allow invalid data to be created, which prevents bugs.

Example here

Triggers

Triggers are used to check security and add other complex business logic such as default data, complex validation, calculated values, or logging changes to comments. We typically have one script per table that matches the table name. The trigger scripts can be found here.

You can see this trigger script for item has the validation and checking on near the top, while most of it is just logic for logging changes to the record. This is often the case with triggers:

Example here.

 

mead