Customize your API

Customize your API
lac32
Your API can consist of the following kinds of resources, or endpoints:
  •  The resources that are defaulted from the schema.
  •  The resource endpoints you define explicitly.
    You can also create your own RESTful endpoints by creating custom endpoints or by creating functions.
    For more information:
  • System REST endpoints.
     The metadata services used to obtain the list of entities and resources and their attributes.
    For more information about how to use the system resources your API includes, see System REST Endpoints.
You can customize your API by defining custom resources.
In this article:
2
Schema Resources
When you create an API and you connect API Creator to your database, 
CA Live API Creator
 creates endpoints by scanning the database schema for the following resource types:
  • Tables.
     Resources for each base entity.
  • Views.
     Resources for each database-defined view. You can access complex query processing using view resources.
    For more information about how to access complex query processing using view resources, including automatic discovery, see Manage Views.
  •  
    Stored procedure resources. 
    Resources for each stored procedure, created as REST endpoints. You can harness the business logic already developed in your database using stored procedure resources. You can also protect these resources using security.
    For more information:
Rows - JavaScript Object Model
When you create an API and you connect API Creator to your database, 
CA Live API Creator
 also creates (not code generation) the JavaScript object model, made available through the 
row
 variable passed into your logic. This object model reads and writes data by addressing attribute/object accessors and persistence services.
For more information:
Custom Resources
Custom resources are resources that you can define to shape your API. They can include related data (joined tables, alias attributes, and defined filters.
Custom resources:
  • Provide mapping and transformation.
     REST resources are loosely akin to database views, where you can define named projections, filters, and alias names. Custom resources provide an abstraction layer for database access. This abstraction layer protects your application for underlying changes in the database. You can also protect your application from schema changes in the database. You do this by giving aliases to tables and columns in the context of a resource (of type Normal).
  • Minimize network latency.
     Beyond view-like functionality, you can define subresources for related parent-child data. For example, you might define a customer that includes its purchase orders and line items. The data is included in the JSON that is returned from issued REST requests.
  • Provide a convenient programming model.
     JSON results are returned as a document model with nesting for subresources. Client applications often prefer results returned as a document model.
  • Provide integration.
     Subresources can come from different databases, including non-SQL sources such as REST, Mongo, and ERP systems.
    For more information about REST integration examples, see REST Integration Examples.
Define Custom Resources
Prerequisite:
 You have defined the relationship between the tables. If the database does not define foreign keys, you can create virtual relationships.
Follow these steps:
 
  1. With your API open, in the Create section, click 
    Resources
    .
    A list of your resources display.
  2. Above the resource list, click 
    New Resource
    .
    A new resource displays in the list.
  3. Complete the following fields on the Resource tab, and then save your changes:
    Resource type
     
    Defines the resource type.
    Values:
     
    • Normal.
       (The most common case) A resource of type Normal automates SQL handling. You can integrate other data sources and control SQL by defining the resource type. You can give aliases to tables/views and columns in the context of a resource (of type Normal). You can also specify joins and filters by joining together one or more table. Normal resources are linked to existing base SQL tables/views.
      For more information:
    • Free SQL.
       Free SQL resources are resources where you manually specify the SQL to execute.
      For more information about defining Free SQL resource types, see Free SQL Resources.
    • JavaScript.
       (Advanced Users) Supply server-side JavaScript that is executed whenever the resource is accessed and returns JSON. Your JavaScript code is responsible for retrieving data and for formatting it into JSON. The most common use for this resource type is to make a call to an outside REST services and connect to multiple servers of any type. For example, you might want to aggregate REST/JSON services.
      For more information about aggregating REST/JSON services, see Define JavaScript Resource Types.
    • MongoDB.
       MongoDB resources are resource you can connect to a specific MongoDB server, database, and collection.
      For more information about integrating with MongoDB, see Define MongoDB Resource Types.
    Default:
     Normal
    Entity
     
    The base entity name. If you rename the entity, update the base entity name. Renaming the base entity does not affect client applications.
    Resource name
     
    The unique name within the root resource. The resource name is the container name with JSON. The name must contain alphanumerics or the underscore (_) special character.
    Is a collection
     
    Defines whether the resource is expected to be a single row at most or potentially more than one row. That is, it defines whether the table for the resource is a child table of the containing resource's table. Determines whether the JSON is an array (cleared) or object (selected). When cleared, if API Creator detects multiple rows, it generates an exception. This field is typically used only for parent subresources.
    For more information, see the "Parent Subresources" section.
    Best practice:
     Define top-level resources as collections. Subresources are collections if there can be many rows for a single row in the containing resource. You cannot define a subresource is for a table that is a parent table of the containing resource's table as a collection. At most only one row can be expected.
    Combined
     
    Defines the subresource as a resource whose attributes are combined and included in a containing root resource. This checkbox displays only for subresources.
    Default:
     Cleared
    For more information about combined resources, including this checkbox, see Combined Resource.
    Join
     
    A parameterized filter to retrieve results based on the containing resource. Use brackets ([]) to refer to containing resource attributes. The terms within the brackets must follow the "=" sign.
     
    Examples:
     
    • To retrieve child rows for a containing parent, use the following:
      fk = [pk]
    • To retrieve parent for a containing child, use the following:
      pk = [fk]
  4. Create a subresource by completing the following:
    1. Click 
      New Level
      .The Sub-Resource Helper window opens.
    2. Select a related table from the list and then click 
      Create Resource
      .Subresources for a tree-like document model are created.
      The following image shows the 
      CustomerBusinessObject
       custom REST resource of type Normal and the nesting of related tables in the Demo API: Screen Shot 2017-03-03 at 3.48.30 PM.png A list of related objects based on your schema's foreign keys is presented. If your schema does not have foreign keys, you can define virtual foreign keys.
Your custom resource is defined.
Define Document-Oriented Resources
You can define named resources that deliver document-oriented multitable results to clients, instead of flat relational tables. These results can be more convenient to client code and reduce latency by delivering all the required data in one transmission (with pagination for large results) instead of in multiple server calls.
You can protect your application from underlying schema changes in the database by provisioning resource/attribute alias names. You can select and alias the attributes so that your API is not simply a direct reflection of the schema.
Resource Details
You can apply filters and sorts to each level of the endpoint.
 
Prerequisite:
 The resource is of type Normal or MongoDB.
  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 additional filtering on the resource, for example, to restrict the rows that the resource returns. For SQL resources of type Normal, 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 to be Returned by your Resource
You optionally can shape the response attributes by choosing and renaming columns. By default, resource attributes have the same name as the corresponding columns, but you can change that and you can give each attribute a name that is better suited to your purposes. Choosing and renaming columns is akin to giving an alias to the column in the context of that specific resource.
When you create a resource of type Normal, 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. API Creator always returns the primary key column, whether you select it or not.
 
Prerequisite: 
You have defined your custom resource.
  1. With your API open, in the Create section, click 
    Resources
    .
    A list of your resources display.
  2. Click 
    Attributes
    .
  3. From the list of resource, click the name of the resource for which you want to return attributes.
  4. Complete the following fields, and then save your changes:
     
    Columns
     
    Clear the checkbox for the base entity columns you do not want the resource to return.
    Default:
     Selected.
     
    Attribute name
     
    Select which attributes your resource returns (subject to security) and override the default name. Each resource attribute is identified with an (alias) name, and includes a 
    column_name
     and 
    format
    .
     
    Optional: 
    Yes
    If you do not select the response attributes, this resource returns all attributes in table.
The attributes that you want your resource to return are selected and saved.
Filter and Order
You can filter results (in addition to security) and define a comma-separated list of sort fields, with an optional asc/desc indicator.
For more information about how apply filters and sorts to each level of the endpoint, see the Apply Filters and Sorts to Each Level of the Endpoint section.
Create Subresources
Subresources are shown indented under each level. You can add custom child resources using JavaScript code.
  1. Open the Demo API.
  2. Create a custom resource using the Customers table from the Demo database.
  3. Click 
    New Level
    .
    The Sub Resource Helper dialog opens displaying related tables. If the level you selected is a base entity, suggestions are provided based on relationship roles.
  4. Choose orders from a different database and then click 
    Create Resource
    .
    For more information about how your API can integrate multiple databases, see Multiple Databases.
The subresource is created. Each resource attribute is identified with an (alias) 
name
 and includes a 
column_name
 and 
format
.
 Parent Subresources
In the previous image, Product is a subresource of Lineitem. Even though Product is a parent (one side of a one-to-many), it is defined as a subresource. That means you get JSON, like the following snippet illustrates:
"order_number": 1,
"amount_total": 35,
"paid": false,
"notes": "This is a small order",
"customer_name": "Alpha and Sons",
"links": [],
"Lineitems": [
{
"@metadata": {
"href": "http://localhost:8080/APICreator/rest/demo1/OneCustomer.Orders.Lineitems/1",
"checksum": "69a06521a60842973ca3a27e50520851"
},
"lineitem_id": 1,
"product_number": 1,
"order_number": 1,
"qty_ordered": 1,
"product_price": 10,
"amount": 10,
"links": [],
"Product": {
"@metadata": {
"href": "http://localhost:8080/APICreator/rest/demo1/OneCustomer.Orders.Lineitems.Product/1",
"checksum": "0efd354caacf68997d3a4685783bf740"
},
"product_number": 1,
"name": "Hammer",
"price": 10
}
},
This is a common pattern in defining resources over a junction table. Internally, 
CA Live API Creator
 collects the parent subresources and computes the join that is sent to the database management system. The automated join processing extends to multiple and nested parents. Parent subresources are the preferred method for retrieving parent data. You can obtain parent data for retrieval without introducing business logic, such as formulas.
 For more information:
Declare Combined Subresource Attributes into a Containing Resource
You can declare combined subresource attributes into the containing resource. The following code snippet shows an example:
"order_number": 1,
"amount_total": 35,
"paid": false,
"notes": "This is a small order",
"customer_name": "Alpha and Sons",
"links": [],
"Lineitems": [
{
"@metadata": {
"href": "http://localhost:8080/APICreator/rest/demo1/OneCustomer.Orders.Lineitems/1",
"checksum": "69a06521a60842973ca3a27e50520851"
},
"lineitem_id": 1,
"product_number": 1,
"order_number": 1,
"qty_ordered": 1,
"product_price": 10,
"amount": 10,
"name": "Hammer"...
For more information about combined subresources, see Combined Resource.
Inject Computed Attributes and Discard Rows
You can inject computed attributes and discard rows by:
  • Providing resource row events.
    For more information about how to provide resource row events, including the variables 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.
Move Resources Between APIs
For more granular operations, you can import and export individual resources and move them from one API to another.
  1. In API Creator, open the API containing the resources your want to export.
  2. In the Create section, click 
    Resources
    .
    The Resource page appears.
  3. Click 
    Export
    .
    .JSON
     file containing the exported resources is dowloaded.
  4. Open the API to which you want to import the exported resources.
  5. Under the Create menu, click 
    Resources
    .
    The Resource page appears.
  6. Click 
    Import
    .
    The Import JSON window opens.
  7. Select the exported 
    .JSON
     file you want to import and click 
    OK
    .
    The 
    .JSON
     file containing the exported resources is imported into the API.
The resources are moved from one API to another.
Extend Resources
You can further control resource processing using the following extensibility services:
  • Control the returned response values using resource row events.
    For more information about extending resources using resource row events, see Resource Row Events.
  • Define subresources (and obtain those rows using JavaScript) using JavaScript resource types.
    For more information about extending resources using JavaScript resource types, see Define JavaScript Resource Types.