Define Table-Based Resource Types

Table-based resources are a type of resource that you can define explicitly using API Creator to shape your API per your web/mobile application or integration requirements. Table-based resources provide a data abstraction layer from the underlying tables of your database.
lac52
Table-based resources are a type of resource that you can define explicitly using API Creator to shape your API per your web/mobile application or integration requirements. Table-based resources provide a data abstraction layer from the underlying tables of your database.
You can shape your table-based resource by adding or modifying any of the following:
  • Default resource attributes from the entity's (table or view) columns, and then aliasing the columns to conform to your client requirements
  • Attributes that return computed or derived attributes (virtual resource attributes) 
  • Attributes from the resource's parent as part of the resource (parent resource attributes)
You can also specify joins and filters used to join one or more tables together in the resource. The resources that you define (including the virtual resource attributes and parent resource attributes) are reflected in the OpenAPI (Swagger) documentation.  
 
CA Live API Creator
 applies and automatically reuses the logic that you define in 
CA Live API Creator
 for your entities in all the table-based resources that you create for the entities.
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). You can integrate other data sources and control SQL with this resource type.
    • Ⓢ Free SQL
       Resource
      . Resources that you specify the SQL to execute.
    • Ⓙ JavaScript
       Resource
      . (Advanced users) Resources that you supply the server-side JavaScript that is executed whenever the resource is accessed and returns JSON.
    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) that you want to use for your table-based resource.
    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.
Manage Table-Based Resources
You manage your table-based resources by creating them, by updating them, and by deleting them.
Apply Filters and Sorts to Table-Based Resources
You can specify additional filtering on the resource and restrict the rows that the resource returns. You can also specify row ordering on the resource, for example, to sort customers by balance.
With your API open and your table-based resource selected, 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'
Order
 
 
CA Live API Creator
 merges the fragments that you enter into a 
WHERE
 SQL clause. You are not restricted to filtering resource attributes. Use base entity column names, not aliased resource attributes. You can filter on parent subresource attributes using qualified attribute names.
Specify row ordering on the resource. 
CA Live API Creator
 passes this list to the database. Use proper syntax for your database, for example, for MySQL databases, use the following syntax:
'MyColumn' desc, 'MyOtherColumn' asc
For other databases, use double quotes (") around the entity column name, for example:
"MyColumn" desc, "MyOtherColumn" asc
The filters and sorts are applied to each level of the endpoint.
Call Table-Based Resources
You can call table-based resources using the 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 using only GET requests.
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 a 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 Add URL Parameters in Requests.
Next Steps
After you have defined the table-based resource, you can do the following:
  • Create subresources.
     These nested documents can join related data from one or more tables that span across one or more databases.
    For more information, see Manage Subresources.
  • Add resource attributes. 
    By default, resources use the attributes from the entity (table or view) and return all base entity columns. You can change the way you want 
    CA Live API Creator
     to reflect the underlying entity in accordance to your client requirements.
    For more information about how to add resource attributes, see Manage Resource Attributes.
  • Define extended properties for the resource.
     Provide options on how you want 
    CA Live API Creator
     to handle incoming requests for your table-based resources by defining the extended properties for the resource.
    For more information, see Manage the Extended Properties for Resources.
  • Conditionally discard rows.
     Create GET and PUT/POST events in your API.
    For more information about how to create a resource event, see Manage Resource Events.
  • Inject computed resource attributes.
     Use the following methods: