Docs‎ > ‎Integrate‎ > ‎

Data Sources


Your database can be in the cloud, or on-premise.  For on-premise databases, see the section on connectivity, below.

API Creator update processing relies on database services for locking and transaction management. Retrieval processing leverages the database engine's services for query optimization (index selection, join strategy).



Connecting to your Database

The API Server needs access to your database, whether it be stored in the cloud or within your firewall..

We include the Java DB (Apache Derby) driver, but all other databases will require the installation of the appropriate JDBC Drivers for your system.  See JDBC Driver Installation for detailed instructions.

Database Types

We are always adding new databases, so please contact us for a current list, or to suggest new requirements.  


Existing Database


You begin by connecting, which creates a project (API), as shown here.  Firewalls require accommodations.  See here for more information.

Connectivity can be trying.  Please contact support for help.  See here for suggestions on Microsoft SQL Server.

You can also start with a new database, as described in the following sections.

Database Connect 

You can create a new database using your current tools, then connect to CA Live API Creator. If the database driver is not available or cannot be loaded - you will need to manually provide the JDBC Driver, restart the API Server, and then attempt to connect again.


Multiple Databases

You can define Multiple Databases, which enables you to combine these in Resources, build rules between databases, and access them in the Live Browser.

Schema as Data Model

API Creator does not duplicate the information from your database catalogs to determine the table names, column names, and foreign key names.  All of these are obtained from the database catalog, so it is not necessary to maintain multiple copies of the same information.

Use your existing tools to manage your schema.  More information is described below.

You do, however, need to understand that API Creator references to database objects (tables, columns, foreign key names) may break if you rename / remove database objects.

Foreign Key-based Relationships

API Creator requires references to foreign key relationships to express Logic (sum/count, parent references etc), retrieval joins, and Live Browser support for Master/Detail, Lookups, and Automatic Joins.  A strong understanding of these Foreign Keys is therefore critical - see here for background, examples and terminology.  

The underlying concept is a one-to-many relationship between 2 tables in a relational database.  Relationships are discovered by Foreign Keys defined in your database.  The Sample Database has examples of multiple different kinds of relationships.

We use the following familiar terminology:
  • Parent - Within a Relationship, the table containing the Primary Key on the "one" side.  For example, Purchaseorder is a parent to Lineitem.

  • Child - Within a Relationship, the table containing the Foreign Key on the "many" side.  For example, Purchaseorder is a child to Customer.  PUT/POST JSON provides mechanisms to associate a child with its parents.

  • Parent Role - the name by which the Child refers to the Parent

  • Child Role - the name by which the Parent refers to the Children

Determining Role Names

Relational database catalogs do not specify role names, so we must determine them as described here.  In most cases, these defaults mean you don't need to worry about this; however, multiple relationships between tables and databases without Foreign Key names require special consideration.

By default:
  • Parent Role are defaulted to the name of the Foreign Key.  If this is not present, the Parent Table Name is used, as shown below
    CONSTRAINT product FOREIGN KEY (product_name) REFERENCES products (name) ON UPDATE CASCADE,

  • Child Role Names are defaulted to the name of the Child Table concatenated with "List".  So, for example, Customers could reference (perhaps in a sum rule) OrdersList.

Naming Alert

Role Names must not conflict with attribute names.
The defaults above don't work when there are multiple relationships between the same two tables, such as in the Sample (Department has worksFor and onLoan Employees).  You can use API Creator to specify your Role Names.  Or, you can encode your role names into your foreign key validation names like this:

ParentRoleName__ChildRoleName

for example:

 CONSTRAINT reportsTo__reportingEmployees FOREIGN KEY (department_name) REFERENCES departments (name) ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT onLoanTo__onLoanEmployees FOREIGN KEY (on_loan_department_name) REFERENCES departments (name) ON DELETE SET NULL ON UPDATE CASCADE


Virtual Foreign Keys

Many database schemas do not provide Foreign Key definitions.  API Creator therefore provides Virtual Foreign Keys, which enable critical pieces of functionality such as API Definition, rules, Live Browser, etc.

Note that the related tables can be in different databases.



Database Administration

API Creator does not replicate your schema (model) in the Repository.  You can freely make changes to your schema using your existing tools and procedures, but please review the topics below.

The startup process creates a test database named Demo, Sample, and Your Database (a blank placeholder).

Use Any Tool

You can manage your schema using the tools you are currently familiar with.

You can configure your datasource to access your database administration web tool, as follows:


Schema Evolution and Caching

When you first create an active connection with the Connect Wizard or manually in the Databases tab, the database meta-data is read.  Depending on the size of the database and the latency to the database server, this may take significant time.  Once the meta-data has been read, it is cached in the API Server administration database and shared between nodes.  

Normally, this is exactly what you want.  However, as the schema evolves, API Creator needs to be told to scan the database meta-data again.  Whenever the schema has changed, press the Reload Schema button to flush the cache and reload the schema.


Verify

The Object Model built using Schema Discovery is not a code generation process.  It always matches your current schema (subject to Schema caching)

Changes to your schema can make Rule Base objects (e.g., Resources) invalid, due to dangling references.   Use the procedure below to verify your project:
  1. Reload your Schema
  2. Verify you Project
  3. Review Issues

Verify Project

Use the Projects screen, and click the Verify button in the lower right.

This will identify resources that refer to objects that no longer exist (e.g., they were deleted or renamed), and log them as Issues (described in next section).



Issues

If errors are detected, the Problems Navigation will be highlighted as shown below.  You can click it to see / address the errors.


Virtual Foreign Keys

Foreign Keys are useful for defining multi-table resources, Object Model accessors, rules, and the Live Browser.   Yet, many schemas do not define these.

 API Creator therefore provides a mechanism to define Foreign Keys, instead of the database schema.  The related tables can be in the same database, or different databases.  See the following section.


Relationship Editor

This editor under the database tab allows additional relationships to be created between parent and child tables.  This is useful when creating Resources that require 'joins' between tables.  The Live Browser will use the existing relationships to create navigation, tab panels, parent pick choices using these definitions.  

Note: the system will reload the schema, so give it a moment.

Sequences

Databases that support sequences will display the column and sequence name.


Synchronizing data with Logic

To maintain high performance, the Logic Engine presumes that existing data matches your logic.  For example, in Demo, we assume that the Customer balance is correct, so we can adjust it on purchaseorder changes and avoid expensive aggregate queries.

If you alter the logic, you will therefore need to bring your current into conformance.  For example, here are the (MySql) queries used for Demo:

update lineitem item set item.product_price = (select price from product p where p.product_number = item.product_number);

update lineitem item set item.amount = item.qty_ordered * item.product_price;

update purchaseorder po set po.amount_total = 
(select sum(item.amount) from lineitem item where (item.order_number = po.order_number));

update customer cust set cust.balance = 
(select sum(po.amount_total) from purchaseorder po where po.customer_name = cust.name and po.paid = false);


For the following rules:


If you are using SQL/Server, a similar script would be:

update [dbo].[Customers] set Balance = 
  (select sum(orders.AmountTotal) from [dbo].[Orders] orders
          where (orders.CustomerID = [dbo].[Customers].CustomerID));

Best Practices

We suggest the following.

Explicit Foreign Key Definitions

Foreign Keys should be defined via Validations, including a Validation Name.


Database-specific Information

Please find JDBC Driver details here.

Oracle

Click here to see Oracle-specific information.

Microsoft SQL Server

Click here to see Microsoft SQL Server specific information.



Trouble Shooting

Please see here.