Layer7 Live API Creator 5.4

PDF
5.4 5.3 5.2
English

Associate Entities to Sequences

Sequences are database objects that the sequence generator in the database uses to generate sequential values. Your database can auto-generate unique primary key for an entity (table or view) using these sequential values. Without the presence of identity columns, your database can obtain a unique primary key value using these sequences.
lac51
Sequences
 are database objects that the sequence generator in the database uses to generate sequential values. Your database can auto-generate unique primary key for an entity (table or view) using these sequential values. Without the presence of identity columns, your database can obtain a unique primary key value using these sequences.
When you connect to a database that supports sequences, such as Oracle, 
Layer7 Live API Creator
 detects the sequences that are defined in your schema. 
Layer7 Live API Creator
 cannot know which entity uses which sequence.
For more information about the databases that support sequences, see the documentation for the specific data source.
If you are using Oracle 12c, your database can use identity columns and it can obtain a unique primary key value for the identity column using sequences. If you use this approach, you do not need to associate entities to sequences using API Creator.
For more information about how to have the Oracle database generate sequential values, see Oracle Data Source.
If your database does not use identity columns, you can generate sequential values using one of the following approaches:
Associate entity columns to sequences if 
any
 the following cases are true:
  • You want 
    Layer7 Live API Creator
     to set the primary key for the entities.
  • You want 
    Layer7 Live API Creator
     to auto-generate numbers for the entity columns using the sequences.
Verify the Prerequisites
Before you associate entity columns to sequences, verify that your database includes sequences.
Use Triggers with Sequences
  1. In the database, define code that returns an auto-increment value as the primary key. The following Oracle sample code shows creating a sequence, a table, and a trigger that uses the sequence:
    CREATE SEQUENCE orders_seq increment by 1 start with 2000 nocache
    CREATE TABLE orders (
      ident number(9) PRIMARY KEY
    , amount_total decimal(19,4)
    )
    -- create a trigger that uses the sequence
CREATE OR REPLACE TRIGGER orders_trg BEFORE INSERT ON orders
  FOR EACH ROW
    WHEN (new.ident IS NULL)
      BEGIN
        SELECT orders_seq.NEXTVAL INTO :new.ident FROM dual;
    END;
  2. In API Creator, for each entity that uses triggers, associate the entity column that is set by the trigger. Complete the following steps:
    1. With your API open, in the Create section, click 
      Data Sources
      .
      The Connection tab appears by default.
    2. Click the 
      Sequences
       tab.
      The Sequences page displays. The entities, the available columns, and the sequence names that 
      Layer7 Live API Creator
       detects for the data source are displayed. 
    3. Select the entity column that is set by a trigger for 
      Column
      , accept the default value (
      <None>
      ) for 
      Sequence
      , and then save your changes by clicking 
      Save
      . Accepting the default value of 
      <None>
       allows the Oracle database to generate a sequential value in the trigger and assign it to the entity column.
The entity column is associated to the sequence.
Associate Entity Columns to Sequences using API Creator
  1. With your API open, In the Create section, click 
    Data Sources
    .
    The Connection tab appears by default.
  2. Click the 
    Sequences
     tab.
    The Sequences page displays. The entities, the available columns, and the sequence names that 
    Layer7 Live API Creator
     detects for the data source are displayed. 
  3. For each entity that uses a sequence, select the entity column for 
    Column
    , select the sequence for the entity for 
    Sequence
    , and then save your changes by clicking 
    Save
    .
The entity column is associated to the sequence.
Define Rules that Return a Unique Primary Key
You can have 
Layer7 Live API Creator
 generate the following primary keys for the table using sequences and assign it to a column by way of event rules:
  • Alphanumeric unique primary key:
     Define a 
    pre-insert event rule
    . Within the code for the rule, define how 
    Layer7 Live API Creator
     computes the key. Pre-insert event rules fire only on client inserts. Rules of this type automatically handle inserting a parent and a set of children in a single transmission (Cascade Add).
     The 
    Oracle Sequence Example
     API sample illustrates how you can have 
    Layer7 Live API Creator
     insert a parent and a set of children in the same request.
    For more information about this example, see Oracle Sequence Example API Sample.
  • Unique primary key:
     Define an 
    early event rule
    . Define a rule of this type for row inserts that are triggered from within your rule logic (for example, audit changes into child tables).
     The 
    Oracle Sequence Example
     API sample illustrates how you can have 
    Layer7 Live API Creator
     compute an alphanumeric primary key in an early event rule using sequences.
    For more information about this example, see Oracle Sequence Example API Sample.
For more information about how to define pre-insert and early event rule types, see Event Rule Types.