Introduction

 

Goal

The goal of the xTuple Application Programmer Interface (API) is to make importing data directly into the database much safer and easier than it is going directly into the regular table structure. The specific target audience is users such as solution providers and customers who wish to integrate e-commerce solutions with xTuple Applications (including the open source PostBooks and the commercial-license OpenMFG) without having deep experience with xTuple business logic. Our appoach is to allow users to create, access, and update xTuple documents using a special database schema that closely mirrors the graphical user interface (GUI).

Philosophy

The xTuple technology is built on a classic client/server platform, where the client application is written in C++ using Trolltech's Qt development library, and connects to a highly normalized PostgreSQL database. Using the xTuple client requires little technical expertise; however, loading data into the database using any other interface can be challenging because users are required to know the intracacies of the xTuple table schema, and perhaps even C++ programming to access and change underlying data.

The traditional approach to this problem is to wrap the business logic of an application like the xTuple client into a library that makes it accessible to a particular method such as C++, Java, COM, .NET, or Python. These tools allow developers to load business objects from the application that can be viewed, altered and saved programatically. This solves a general compatability problem between object oriented (OO) programming and relational database structures, particularly in cases where the business objects presented to users do not exactly match the table structure within which they are stored. 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 where that data is actually stored as 5 records in 3 separate tables. An API library provides a programmer a way to access the same data points available in the GUI without having to worry about how or where it is stored. Furthermore, APIs solve the problem where the database tables do not provide the level of checking and defaults nor save field level data in the same form as presented in the GUI, which saves developers from having to worry about data integrity and translation as well.

Unfortunately an API library only satisfies a narrow audience because 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, this adds complexity and a potential obstacle to a .NET developer who wants to leverage xTuple. Extending the flexibility of the API requires additional wrappers, translations or bridges to be built. Second, API libraries are 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. Finally, any movement toward rewrapping business logic in a 3rd tier will ultimately lead to a more complicated application to maintain and operate for users and developers, which is antithetical to our approach of keeping xTuple technology easy to install, maintain and use.

What is needed is a technology-agnostic method of abstracting business logic that makes external linking and mapping to xTuple Applications easy and safe. We have settled on the strategy of implementing database views in a separate schema as a mechanism to accomplish this. This idea is predicated on the notion that all modern languages have a means to attach to databases and manipulate records as objects. By embedding all business logic in the database, developers can use their technology of choice to manage xTuple records using familiar tools. This also leaves the door open to users who would prefer to use straight SQL statements or 3rd party GUI tools such as Microsoft Office or Open Office to attach to and manipulate data. Finally, it provides a safe and easy mechanism to integrate third party tools at the database level using views, triggers and functions.

Prerequisites

This guide is targeted at developers and programmers who desire to access xTuple Applications from 3rd party programs. 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 2.2.1 or higher.

  • A basic understanding of the xTuple Applications business flows.

  • Access to a PostgreSQL database with a demo PostBooks database loaded.

Working versions of the examples described below are attached here: