Database Sequences

API Server handles database Primary Key Generation automatically.  You can also create your own keys that utilize Database Sequences, as described on this page.

The Database Sequences API project sample illustrates how to manage sequences, generated by both code and by the database. 

You can download the artifacts from the attached GENNEDKEY.zip file.

This sample API project illustrates:
  • How to acquire a database connection.
  • How to control the generation of primary keys.

Requirements

Referring to following diagram:
  • The parent table has a key we want to generate with values like "Row: 1", "Row 2", etc.
  • The primary key of the child is a (more common) database-generated key, using Oracle sequences.

Sample Database

Early Event

The Early Event uses an Oracle Sequence MYSEQUENCE to compute the primary key, as shown in the following image:

Compute the Child Primary Key using Oracle Sequences

To compute the Child Primary Key, sequences are defined, as shown in the following image:
Test

Use the Command Line Interface (CLI) to post data to a resource with join. The following occurs:

  1. The system creates the parent. This causes the Early Event to compute the Primary Key.
  2. The system inserts each child. Their Foreign Key is set to the Primary key.
#! /bin/bash
#login to 2 different servers (For WAR change to localhost:8080/APIServer)
lac login -u demo -p Password1 http://localhost:8080/rest/default/seq/v1 -a seqAPI
lac use seqAPI

#Post data to a resource with join
lac post main:GENNEDKEY_MINE_PARENT -j '{ "MYGENKEY": "??", "COMMENTS": "This is the parent row" }'

The attached GENNEDKEY.zip file contains an sh script that inserts a parent/child set.

Cascade Add

Consider a resource joining Parent and Child.  Resources support Cascade Add: the ability to Post a Parent and set of children in the same request.  This is bit challenging, since the client cannot pre-supply these Foreign Keys.  

For database generated Primary Keys, API Server automatically inserts values into Child Foreign Keys. This behavior works whether you are using (declarative) database sequences, or triggers.

However, Cascade add does not work for primary keys generated using Early Events. This is because the physical insert actually occurs prior to rule processing, so the (subsequent) Early Event results in update. This fails to cascade properly.

If you require such functionality, either use a database trigger, or two transactions.