A project may have one or more SQL database locations. Multiple databases can be used in the same API and a single resources can reference multiple 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.
We are always adding new databases, so please contact us for a current list, or to suggest new requirements.
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.
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.
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.
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:
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.
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).
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.
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:
- Reload your Schema
- Verify you Project
- Review Issues
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).
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.
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.
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));
We suggest the following.
Explicit Foreign Key Definitions
Foreign Keys should be defined via Validations, including a Validation Name.
to see Oracle-specific information.
Microsoft SQL Server
to see Microsoft SQL Server specific information.