Data Import API views
The xTuple database contains specially-constructed views designed to make importing data into the database much safer and easier than going directly into the regular table structure. The specific target audience is users such as solution providers and customers who wish to import legacy data or integrate third-party information with xTuple applications without having deep experience with xTuple business logic. Our approach is to allow users to create, access, and update xTuple documents using a special database schema that closely mirrors the graphical user interface (GUI).
Table of Contents:
The traditional solution to this problem is to wrap the business logic into a code library, often restricted to a particular programming language such as C++, Java, or Python. These libraries allow developers to load business objects programmatically and can manage data integrity and format-translation issues. Well-designed libraries can do this without forcing the developer to worry about how and where the data are stored in the database, particularly when the business objects presented to users do not exactly match the underlying table structure. For example the Customer window in PostBooks presents the user with primary and secondary contact and address information that appears as though it is all stored as part of the Customer record while those data are actually stored as 5 records in 3 separate tables.
Unfortunately an API library only satisfies a narrow audience:
- It limits users to using the language and technology the API is designed in. For example if the API is written to interface with Java, a .NET developer would either have to learn Java or build a bridge.
- It is almost completely inaccessible to casual users who would like to simply map and import data from other GUI tools such as Microsoft Excel or Access.
- It adds yet more code to learn, maintain and operate, which is antithetical to xTuple's approach of keeping our technology as simple as possible.
We solved these problems by choosing a technology-agnostic strategy of implementing database views that enforce basic business logic. This idea is predicated on the notion that all modern programming languages have a means to attach to databases and manipulate records as objects. By embedding all business logic in the database, users can choose familiar tools to manage xTuple data safely.
This guide is targeted at developers and programmers who want to import and export xTuple data outside the xTuple ERP application. Users reading this document should have the following:
- An understanding of the PostgreSQL Database and how to execute SQL statements
- A working installation of PostBooks or a commercial xTuple ERP database
- A basic understanding of xTuple ERP business flows
The data records and business logic for the open source core xTuple ERP are stored in a schema called
public. Schemas provide a way to logically separate database objects from one another while still allowing them reside in the same database and interact with one another. xTuple API views are stored in a schema called
api. Each extension to the core open source version has its own schema, and any API views in that extension have
api_ as a prefix to their names.
You can browse your database with PgAdmin to find the views in the
api schema r you can list them by running the following SQL statement:
select viewname from pg_views where schemaname = 'api' order by viewname; viewname ---------------------------- account accountchar accountcomment accountfile accountimage address addresschar addresscomment apmemo armemo bom bomitem ...
A list of field names for a particular view may be queried as shown below where
customer may be replaced with the name of any api view in the system:
select a.attname from pg_attribute a join pg_class c on a.attrelid = c.oid join pg_namespace n on c.relnamespace = n.oid where c.relname = 'customer' and n.nspname = 'api' and a.attnum > 0; attname ----------------------------------- customer_number customer_type customer_name active sales_rep commission ship_via ship_form shipping_charges accepts_backorders ...
The names of the views and their columns are chosen to correspond closely with the corresponding window captions and field labels. For example, here are some of the columns from the
|Customer Window Label||API View Column Name|
|Under the Billing Contact label:|
Using API Views
The API views may be manipulated using standard SQL query
Remember to schema-qualify API view references with
api.customer); otherwise you risk selecting or altering data from the
public schema directly. For API views in extensions, make sure to use the right schema and view name.
The basic extraction of data is very simple. Pick a view and
select from it. Here are two queries, one from the
api.customer view and the other from the underlying
select customer_number, customer_type, customer_name, active, default_terms, commission, ship_via from api.customer; customer_number | customer_type | customer_name | active | default_terms | commission | ship_via -----------------+---------------+-------------------------------------+--------+---------------+------------+----------------------- TTOYS | NORMAL | Tremendous Toys Incorporated | t | 2-10N30 | 7.500000 | UPS-GROUND-UPS Ground VCOL | NORMAL-INT | Vienna Collectables | t | 2-10N30 | 0.000000 | UPS-GROUND-UPS Ground XTRM | NORMAL-INT | Xtreme Toys LTD | t | 2-10N30 | 7.500000 | UPS-GROUND-UPS Ground XRETAIL | NORMAL-RETAIL | Master Prodiem Toys Retail Customer | t | COD | 0.000000 | UPS-GROUND-UPS Ground (4 rows) select cust_number, cust_custtype_id, cust_name, cust_active, cust_terms_id, cust_commprcnt, cust_shipvia from custinfo; cust_number | cust_custtype_id | cust_name | cust_active | cust_terms_id | cust_commprcnt | cust_shipvia -------------+------------------+-------------------------------------+-------------+---------------+----------------+----------------------- XRETAIL | 20 | Master Prodiem Toys Retail Customer | t | 44 | 0.000000 | UPS-GROUND-UPS Ground VCOL | 19 | Vienna Collectables | t | 42 | 0.000000 | UPS-GROUND-UPS Ground XTRM | 19 | Xtreme Toys LTD | t | 42 | 0.075000 | UPS-GROUND-UPS Ground TTOYS | 18 | Tremendous Toys Incorporated | t | 42 | 0.075000 | UPS-GROUND-UPS Ground (4 rows)
Look carefully at the output. Note that the customer types, entered through a combo box in the GUI, are shown with code values in the view. By comparison, the
custinfo table query shows the underlying keys - hard-to-interpret integers. The same is true for the default terms -
When inserting records the views will honor the same business logic supported by the GUI. For example, fields that would normally have automatic defaults will automatically receive the correct default if not specified in the
insert. The following
insert statement succeeds even though it only specifies 7 of the 76 fields associated with the
insert into api.customer (customer_number, customer_name, customer_type, sales_rep, default_terms, billing_contact_first, billing_contact_last) values ('CE1','Chesapeake Electronics','NORMAL','1000','2-10N30', 'Fred','McClure');
If you run this statement then view the record with the desktop or web client, note how the commission, ship via, shipping form fields and various check boxes are populated with proper defaults. Also note that a linked CRM Account was automatically created for this customer with the correct contact. Records were inserted into 3 tables and 7 tables were referenced by the view to process this one "simple" insertion.
Inserts and updates through API views will validate code names and user privilege settings. If invalid data are passed, these statements will fail and an error will be returned.
You could create a Ship-to Address for this customer now that the customer has been created:
insert into api.custshipto (customer_number, name, default_flag, address1, city, state, postal_code, first, last, phone) values ('CE1', 'Chesapeake Elec. Warehouse', true, '878 Cedar Road', 'Chesapeake', 'VA', '23322', 'Jane', 'Boyd', '757-555-5555');
Data may also be updated using standard SQL statement. The following API view update would need to be split into 4 or more separate statements onto the underlying tables because no less than 4 records in 3 tables are affected by the change:
update api.customer set billing_contact_voice='757-555-1212', billing_contact_address1='877 Cedar Road', billing_contact_city='Chesapeake', billing_contact_state='VA', billing_contact_postalcode='23322', correspond_contact_first='Mary', correspond_contact_last='Olander', correspond_contact_voice='757-555-1212', correspond_contact_address1='877 Cedar Road', correspond_contact_city='Chesapeake', correspond_contact_state='VA', correspond_contact_postalcode='23322' where customer_number='CE1';
If you reopen the Chesapeake Electronics customer record, you can see how this statement affected the Customer record. It also changed the CRM contact list - the phone number was updated for Fred McClure and a new contact was created for Mary Olander. Finally, observe the two addresses on Cedar Road in Chesapeake. One is the Ship-To address associated with Jane Boyd while the other is for Fred and Mary. The view was smart enough to recognize that Fred and Mary share the same address and consequently only created the one record, which was then assigned to both of them.
Just as in the GUI, you will not be able to delete the Customer record unless subordinate Ship-To records are deleted, so two statements are required to delete this customer. Note how the
delete query for the Ship-To record needs to know two things: which customer and which ship to.
DELETE FROM api.custshipto WHERE customer_number = 'CE1' and shipto_number='1'; DELETE FROM api.customer WHERE customer_number = 'CE1';
As with the GUI interface, deleting the Customer leaves intact the CRM Account, Contact and Address records that were associated with it.
We have several tutorials for using the xTuple API views with different tools:
- Tutorial: Using CSVimp to import records
- Tutorial: Using Microsoft Access to link to API views
- Tutorial: Using Microsoft Excel with API views
You may also be interested in xTuple Training to learn about this topic in more depth.
Creating New API Views
There are basically four tools used to create the business logic for xTuple API views:
- view rules
- database functions
- table constraints
These are well documented in Postgres documentation. The content below describes how to use them specifically for creating and extending xTuple API views.
View rules tell Postgres what to do when a user calls a SQL statement against a view. Postgres automatically creates a
_RETURN rule to be the basic
select query that defines the view.
xTuple has implemented three additional rules for every API view:
Prospect is a good example of an API view that combines information from multiple tables. Here is the basic view definition from 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);
_DELETE rules tell the view what to do when the data is changed. This usually involves calling stored procedures. For example, deleting from
api.prospect gets translated into a call to the
CREATE OR REPLACE RULE "_DELETE" AS ON DELETE TO api.prospect DO INSTEAD SELECT deleteProspect(getProspectId(OLD.prospect_number));
deleteProspect handles all of the business logic involved in deleting a Prospect from the xTuple database. Note that this rule also uses the function
getProspectId to translate from the human-readable Prospect number to the internal database key
prospect_id. This is a critical feature of the API views - they translate between human-readable values and structures and the database' internal structure. xTuple has written a number of
save functions for this purpose and you may need to write your own if you create new API views.
_DELETE rule could also have been written to delete directly from the
prospect table; any requisite business logic could then be implemented in a "delete trigger".
CREATE OR REPLACE RULE "_DELETE" AS ON DELETE TO api.prospect DO INSTEAD delete from public.prospect where prospect_id = getProspectId(OLD.prospect_number);
Note also the use of the
OLD qualifier - this indicates that the value of the
prospect_number from before the function call should be passed in to the function.
NEW clarify the intent of the rule and are especially important in
Functions are invoked to retrieve keys needed for the underlying table, to save changes to other tables, or process otherwise complex business logic. There is a series of
Get functions and
Save functions to handle this conversion problem. Both require some input that describe the record you are looking for or changing; they each then return the code or primary key for that record to be stored in related table you are updating.
Read the full api.prospect view definition for examples of
_UPDATE rules. Look carefully at the nested calls to the
saveAddr functions. Together these show how an
insert into a single view can affect multiple tables:
saveAddrinserts into or updates the
addrtable and returns the created or changed record's
- this internal address id is used by
saveCntctin the new or modified record in the
saveCntctreturns the internal Contact id, which the rule uses in the
These rules also include some additional logic:
warehouse_idbut raises an exception if the site/warehouse is not marked as active
prospect_commentsto be an empty string if no value or a NULL is passed, which might prevent an exception if
prospect_commentswere defined to be
Some general rules of thumb are:
- give view columns names that match the literal labels on the xTuple Graphical User Interface (GUI) as closely as is practical
- where keys exist on tables, use a function to get the human readable name or code field from the associated table
- use functions to modify subtables and simultaneously get the proper key values to update the parent table
- use outer joins on tables where the associated code values or subtable entries are optional
- convert key
varchar. Some RAD tools, like MS Access, see
textas a memo which can not be used as a key for updating but converting to
varcharallows use of the field(s) as keys in those interfaces.
xTuple also makes liberal use of PL/pgSQL 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 PostBooks database. We have attempted try to make the names self-explanatory.
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.
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. Most triggers do data validation near the top, followed by logic affecting other tables, and finally logging changes to the record.
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:
--[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];
Of course there are many examples that conform to these guidelines online in our source code.