Define Table-Based Resource Types

Define Table-Based Resource Types
lac42
Table-based resources are a type of resource that you define explicitly in your API. 
CA Live API Creator
 performs logic only on table-based resources. The database abstraction layer that these resources provide protects your application from underlying changes in the database. You form this layer by giving aliases to entities (tables or views) and columns in the context of a table-based resource. You can also specify joins and filters by joining together one or more tables, and define nested documents that can join data from multiple databases.
In this article:
 
 
3
 
 
Create a Table-Based Resource
  1. With your API open, in the Create section, click 
    Resources
    .
    If you have not defined any resources yet, the Welcome to Resources page appears. If you have at least one resource defined, the Resource tab displays.
  2. Do one of the following options:
    • If you have not defined a resource yet, create a resource by clicking 
      Create a Resource
      .
    • If you have at least one resource defined, click 
      New Resource
      .
    The Add Resource window opens.
  3. Complete the following fields, and then click 
    Add
    :
     
    Resource Type
     
    Defines the type of resource you want to create. Select 
    Table-based Resource
    .
     
    Values:
     
    •  
      Ⓣ Table-based Resource.
       Resources that are linked to existing base SQL entities (tables and views) and automate SQL handling. You can integrate other data sources and control SQL by defining the resource type.
    • Ⓢ Free SQL
       Resource
      . Resources that you specify the SQL the data source executes.
    • Ⓙ JavaScript
       Resource
      . (Advanced users) Resources that you supply the server-side JavaScript that is executed whenever the resource is accessed and returns JSON.
    • Ⓜ MongoDB
       Resource
      . Resources that you can connect to a specific MongoDB server, database, and collection.
     
    Default:
     
      • (If you have added a connection to a database) Table-based Resource
      • (If you have not added a connection to a database) JavaScript Resource
     
    Entity
     
    Defines the entity (table or view) you want to use for your table-based resource.
    If you rename the entity, update the value for this field. Renaming the entity does not affect client applications.
     
    Resource Name
     
    The resource name is the container name with JSON.
     
    Unique:
     Yes
Your table-based resource is created and displays in the list of resources.
Apply Filters and Sorts to Each Level of the Endpoint
You can filter results (in addition to security) and define a comma-separated list of sort fields, with an optional asc/desc indicator.
 
Follow these steps:
 
  1. With your API open and your resource selected, click the 
    Details
     tab.
  2. Complete the following fields and then save your changes:
     
    Filter
     
    Specify more filtering on the resource, for example, to restrict the rows that the resource returns.
    For SQL table-based resources, enter a fragment of a 
    WHERE
     expression into the 
    Filter
     field.
    For example:
    Part.price > 1000 and paid = 'Y'
    The fragments that you enter are merged into a 
    WHERE
     SQL clause. You are not restricted to resource attributes. Use base entity column names (not resource attributes aliases). You can filter on parent subresource attributes using qualified attribute names.
    For MongoDB resources, enter Mongo syntax in the 
    Filter
     field.
    For example:
    {"$and": [{"$lt": {"amount_total": 1000}}, {"paid": "Y"}]}
     
    Order
     
    Specify row ordering on the resource, for example, to sort customers by balance. Use base entity column names.
The filters and sorts are applied to each level of the endpoint.
Select the Attributes That Your Resource Returns
You optionally can shape the response attributes by choosing and renaming columns. By default, the names of the resource attributes are the same as the corresponding columns. You can change the attribute names. Choosing and renaming columns is akin to giving an alias to the column in the context of that specific resource.
When you create a table-based resource, 
CA Live API Creator
 returns all base entity columns by default for that resource. You can override this default by selecting the columns that you want the resource to return. 
CA Live API Creator
 always returns the primary key column, whether you select it or not.
 
Prerequisite: 
You have explicitly defined a resource in API Creator.
For more information about how to select the attributes to display for MongoDB resources, see Define MongoDB Resource Types.
 
Follow these steps:
 
  1. With your API open and your resource selected, on the 
    Resource
     tab, clear the 
    Use Table/View Schema
     checkbox.
    The 
    Alias
    Description
    , and 
    Key
     fields display.
  2. Complete the following fields, and then save your changes:
     
    Attribute
     
    Clear the checkbox for the base entity columns that you do not want the resource to return.
     
    Default:
     Selected
     
    Alias
     
    Select which attributes your resource returns (subject to security) and override the default name. Each resource attribute is identified with an (alias) name
     
    Optional: 
    Yes
     If you do not select the response attributes, this resource returns all the attributes in the table.
     
    Description
     
    The description of the attribute.
     
    Optional: 
    Yes
     
    Key
     
     
     
    (Advanced) Select this checkbox to define this attribute as an effective primary key for this resource and not for the underlying table for the 
    MERGE_INSERT
     and 
    LOOKUP
     metadata action tags. Attributes defined as effective primary keys are independent of virtual primary keys.
    You can select one or more attributes as effective primary keys.
    For more information:
     
    Optional: 
    Yes
The attributes that you want your resource to return are selected and saved.
Define How you Want 
CA Live API Creator
 to Handle Incoming Requests for your Resource
You can provide options on how you want 
CA Live API Creator
 to handle incoming requests for your resources by defining the extended properties for the resource. When creating table-based resources and you are using the 
MongoDB
 data source provider as a data source, you can define aliases or exclude attributes for the collections that the MongoDB database returns by defining the extended properties for the resources.
For more information:
Inject Computed Attributes and Discard Rows
You can inject computed attributes and discard rows by:
  • Defining resource row events.
    For more information about how to define resource row events, including the variables that you can define for the JavaScript code, see Resource Row Events.
  • Defining non-persistent attributes.
     Non-persistent attributes show up in your API documentation.
    For more information about how to define non-persistent attributes, see Manage Non-Persistent Attributes.
Define Resources Between Databases
You can define resources that combine data from different databases. The resource's join condition defines how this resource is joined with the containing resource.
 
CA Live API Creator
 efficiently optimizes resources that combine data from different databases. For example, 
CA Live API Creator
 retrieves customers and then retrieves the 
OrdersFromSample
 rows.
With your resource open to the 
Resource
 tab, enter syntax in the 
Join
 field and then save your changes. For example:
"customer_name" = [name]
 Defining the relationship between the databases defaults the join and displays the key as the value in the 
Join
 field. You can change this value.
Reference Columns in a Table of a Containing Resource
Use the following notation in the 
Join
 field:
customer_number = [custnum] and region_ident = [regident]
In this example, 
customer_number
 and 
region_ident
 are columns of the current resource's table, and 
custnum
 and 
regident
 are columns of the table for the containing resource.
Call Table-Based Resources
You can call table-based resources using GET, POST, PUT, and DELETE requests.
If you are using the 
MongoDB
 data source provider as a data source, API users can call table-based resources only using the GET HTTP method.
For more information about this limitation, see MongoDB Data Source.
Table-Based Subresource Data Retrieval Approach
 
CA Live API Creator
 processes table-based resource row retrieval one level at a time. At the parent table-based resource level, it retrieves rows based on the value that you set for 
Page Size Default
. You can override this setting on a per-request basis by adding the 
pagesize
 attribute as a URL parameter.
You can further control the number of queries that 
CA Live API Creator
 executes to return the rows for table-based subresources by changing the value for 
Chunk Size Default
. This setting can be especially helpful when you have set a large Page Size Default value. You can override this setting on a per-request basis by adding the chunksize attribute as a URL parameter.
 
Example:
 
You have set the value for 
Page Size Default
 and 
Chunk Size Default
 to 20 for your API. Your table-based resource retrieves customers and their orders. Upon receiving a request, 
CA Live API Creator
 does the following:
  1. Extracts the twenty customer keys from the customers resource.
  2. Performs a query for orders with a where clause for the twenty customers (
    "cust-1 or cust-2, ..."
    ) using the customer keys.
  3. Composes the JSON response by distributing the orders to the related customer.
 
CA Live API Creator
 processes chunks of twenty orders in a single query instead of twenty individual queries. This optimization improves the overall performance of nested table-based resources sourced from a single database or multiple databases.
For more information:
  • About the 
    Page Size Default
     and the 
    Chunk Size Default
     API-level settings, see API Properties.
  • For more information about how to add the 
    pagesize
     attribute as a URL parameter in a GET request, see URL Parameters.