CA Live API Creator 5.0

5.4 5.3 5.2 5.1 5.0 4.1 4.0 3.2 3.1.1 3.1
English

Manage Tables in Managed Databases

Manage Tables in Managed Databases
lac42
As an application developer and author of your API, you want to create a new model (schema) that captures your business entities (for example, orders and products) and figures (for example, revenue and sales). You also want to create an API for this model so that client applications or partner integrations can consume this model.
You manage the content in a data source for a managed database by manipulating the managed data schema, using Data Explorer or API Creator. The following procedures describe how to manage the content in a data source for a managed database using Data Explorer.
If you change the schema, you must synchronize those changes with the admin repository by reloading the schema.
For more information about how to reload schemas, see Database Administration.
In this article:
 
 
Verify the Prerequisites
Before you can manage the tables and fields in your data source for a managed database, verify the following prerequisites:
  • You have set up a managed data server.
    For more information about how to set up a managed data server, see Managed Data Server Administration.
  • The data source is a data source for a managed database.
    You can convert data sources that are not data sources for a managed databases to be data sources for managed databases. For more information about how to convert a data source, see Manage Existing Schemas.
Create Tables
You can create tables in your data source for a managed database. You can insert records into tables. The tables that you create display as tabs in the Child Grid. These tabs represent existing child tables for the parent table that is displayed in the Form.
Creating a table adds a tab to the Child Grid. This tab represents a new/existing child table for the parent table that is displayed in the form.
 The tables that you create from CSV files are read-only. All columns are defined as 
char
 data type by default. Since the tables you create from the CSV data source do not have a primary key, you cannot update them directly.
Follow these steps:
 
  1. In Data Explorer (in Author Mode), click the plus sign (+) that displays above the list of tables in the Main Grid.
    For more information about how to access Data Explorer in Author Mode, see Author Mode.
    The Create Table window opens.
    The following image shows this icon:
      Screen Shot 2017-03-10 at 3.04.36 PM.png  
  2. Select a data source, enter a unique name for the table in the scope of your schema, and then click 
    Create
    .
     In Data Explorer, the table's full name is the data source and uses the following syntax:
    <data source prefix>:<table name>
The table is created, containing the ident and Name columns and a sample row.
Create a Parent Relationship
Adding a lookup field creates a relationship from a child table to a parent table. A lookup is a relationship in the database that a child table has to a parent table.
Creating a parent relationship creates the following:
  • A parent table.
  • A lookup field in the database.
  • A field in the child table that Data Explorer can use to perform lookups on the parent table.
You can relate a row to a specific parent record by creating a parent relationship. Data Explorer uses the parent relationship when it performs lookups linking rows from one table to another.
Best practice:
 Factor common data to the lookup table. For example, define fields that are associated with Product with the product, not on the Item table. You can join in the product information so you can see it.
For more information about how to join in parent tables using Data Explorer, see Author Mode.
Prerequisites: 
The table that you want to become the child is selected in the Main Grid.
For more information:
Follow these steps:
 
  1. In Data Explorer (in Author Mode), click 
    Add Field
     in the Form or in the Main Grid.
    The Field Details window opens. The default name for the field displays in the Name field.
  2. Complete the following fields, and then click 
    Create
    :
    Type
     
    Defines the field type. Select 
    Lookup
    .
    Entity
     
    Defines the parent table. Complete one of the following options:
    • Select an existing table from the drop-down.
    • To create a parent table, leave the default 
      --New Table--
       and then enter the parent table name in the 
      Table Name
       field.
    Table Name
     
    The name for the parent table. To create a parent table, leave the default 
    --New Table--
     in the 
    Entity
     field and then enter the parent table name.
    Default:
     parent
A parent table is created. The relationship between the parent table and the child table is created.
Create a Child Relationship
Creating a child relationship defines the relationship between the child table and the parent table. Data Explorer uses this relationship when performing lookups linking rows from one table to another.
Best practice: 
Add your lookup fields before you create your relationships. For example, in defining products for items, define the lookup from Item to Product.
Creating a child relationship creates the following:
  • A child table.
  • A lookup field in the database.
  • A tab representing the child relationship in the Child Grid. This relationship creates a foreign key in the child table.
For more information:
  • About relationships and how 
    CA Live API Creator
     computes the relationship names, see Manage Relationships.
  • About the JavaScript object model, see Row Objects.
Follow these steps:
 
  1. In Data Explorer (in Author Mode), complete one of the following in the Child Grid:
    • If the selected parent table does not have child relationships, click 
      Add a Child
      .
    • If the selected parent table has at least one child relationship, click an empty tab (indicated with the plus (+) sign).
    The Create Child Relationship window opens.
  2. Complete the following fields, and then click 
    Create
    :
    Entity
     
    Defines the parent table. Complete one of the following options:
    • Select an existing parent table from the drop-down.
    • To create a child table related to the table that you have selected in the Form, leave the default 
      --New Table--
       and then enter the child table name in the 
      Table Name
       field.
    Table Name
     
    The name for the child table. To create a child table, leave the default 
    --New Table--
     in the 
    Entity
     field and then enter the child table name.
     In Data Explorer, the table's full name uses the following syntax:
    <data source prefix>:<table name>
    Default:
     child
The child table is created.
Relate a Child Table to Another Parent
Relating a child table to another parent adds another parent lookup.
Prerequisite:
 The tab for the child table is selected in the Child Grid.
Follow these steps:
 
  1. In Data Explorer (in Author Mode), click 
    Add Field
     in the Child Grid.
    The Field Details window opens. The default name for the field displays in the Name field.
  2. Complete the following fields, and then click 
    Create
    :
    Type
     
    Defines the field type. Select 
    Lookup
    . 
    Entity
     
    Defines the parent table. Complete one of the following options:
    • Select an existing table from the drop-down.
    • To create a parent table, leave the default 
      --New Table--
       and then enter the parent table name in the 
      Table Name
       field.
    Table Name
     
    The name for the parent table. To create a parent table, leave the default 
    --New Table--
     in the 
    Entity
     field and then enter the parent table name.
The child table is related to another parent.
Add Fields
You can add fields, such as lookup fields (relationships), using the widgets in Data Explorer.
Fields represent the columns in your database. The term 
field
 is used to stay within the metaphor of applications and screens, and not database terminology.
Prerequisite:
 A table has been created in your data source for a managed database.
Follow these steps:
 
  1. In Data Explorer (in Author Mode), select 
    Entities
     from the endpoint type drop-down and then select the table or view to which you want to add the field from the endpoints drop-down.
    The following image shows the endpoint type and the endpoints drop-downs in Data Explorer:
      Screen Shot 2017-03-06 at 1.39.39 PM.png  
  2. Do one of the following:
    • To add a field to the table that you are viewing in the Form, click 
      Add Field
       in the Form.
    • To add a field to the table that you are viewing in the Main Grid, click 
      Add Field
       in the Main Grid.
    • To add a field to the table that you are viewing in the Child Grid, click 
      Add Field
       in the Child Grid.
    The Field Details window opens.
  3. Enter a name for the field (or accept the default), select a field type, and then click 
    Create
    .
The field is added.
Update and Delete Tables, Fields, and Lookup Fields from your Data Source
You can update and delete tables, fields, and lookup fields using Data Explorer in Author Mode:
  • To update or delete a table, click the table icon in the Form and in the Main Grid.
  • To update or delete a field, click the field settings icon next to the field name in the Form.
    Deleting a lookup field deletes the relationship.
  • To update or delete a lookup field, click the field settings icon next to the lookup field name in the Form.
For more information about how to update or delete fields, including lookup fields, see Author Mode.