Overview of the xTuple API

Structure

All data records and business logic for xTuple Applications 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". Using pgAdmin you can browse to find the api schema and subordinate views in your database or you can list them by running the following SQL statement:

SELECT viewname FROM pg_views WHERE schemaname = 'api' ORDER BY viewname;
viewname
-----------------
custchar
custcomment
custcreditcard
customer
custshipto
custtax
quote
quotecomment
quoteline
quotelinechar
quotelinecomment
salesline
saleslinechar
saleslinecomment
salesorder
salesordercomment

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_class c, pg_namespace n, pg_attribute a, pg_type t
WHERE c.relname = 'customer'
AND n.nspname = 'api'
AND n.oid = c.relnamespace
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
attname
-----------------
customer_number
customer_type
customer_name
active
sales_rep
commission
ship_via
ship_form
shipping_charges
accepts_backorders...

The nomenclature of the views is designed to correspond closely with that found on the corresponding window caption and list titles for the record. Field names match user GUI labels as closely as possible.

Example of api.customer field mappings to Customer Window

Using API Views

The API views may be manipulated using standard SQL query SELECT, INSERT and UPDATE statements.

Selecting Records

SELECT * FROM api.customer;
customer_number  customer_type  customer_name
"1010"           "NORMAL"       "SpectraWorks Inc."
active  sales_rep   commission   ship_via
t        "10"        0.0750    "UPS-Ground-UPS Ground"...

Note that combo box fields in the GUI are shown with code values in the view so developers querying and writing back to the database only need to concern themselves with code names, not the underlying keys. The field 'customer_type' for example shows the code selection 'NORMAL.'

Inserting Records

When inserting records the views will honor all the same business logic supported by the GUI which means fields that would normally have automatic defaults in the corresponding window, will automatically default if not specified by an insert statement. This insert statement works successfully even though it only specifies 7 of the 76 fields associated with the api.customer view.

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','10','2-10-NET-30',
'Fred','McClure')

Viewing the Customer record for Chesapeake Electronics from the GUI should look like this:

New Customer record for Chesapeake Electronics

Note how values for commission, ship via, shipping form and several check boxes are populated with defaults. Also note that a linked CRM Account was automatically created for this customer with the associated contact. Records were inserted into 3 tables and 7 tables were referenced by the view to process this record.

An associated Ship-to Address for the customer would be created like this:

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')

Which should appear on the shipto window for Chesapeake Electronics as so:

Ship to Address for Chesapeake Electronics

Again notice that several fields were populated by default, including the ship-to number. It is worth noting that this particular record also spans 3 underlying tables: shipto_info, addr and cntct. The view again took care of populating the appropriate tables as well as the business logic.

Updating Records

Records may be updated using standard SQL statements. Here's an example of a an update that would be very complex if attempted directly to the 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'

Go back and reopen the Chesapeake Electronics customer record and see how your changes affected the Customer record. Now open the CRM contact list. Note the phone number was updated for Fred McClure, and a new contact was created for Mary Olander. Finally look at the address list. Observe the two addresses on Cedar Road in Chesapeake. 878 is the ship to address associated with Jane Boyd while the 877 address 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 assigned to both of them.

Deleting Records

Just as in the GUI, you will not be able to delete the Customer record unless subordinate shipto records are deleted, so two statements are required to delete this customer. Note how the delete query for the shipto record needs to know two things: which customer and which shipto.

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 still leaves the CRM Account, Contact and Address records that were associated with it intact.