Customize your API

Customize your API
lac31
You can customize your API by defining custom multitable resources. Multitable resources return results in a single response from multiple tables (for example, Customers, Orders, and Payments), possibly from multiple databases.
In this article:
2
Sources for Resources
Your API can consist of the following kinds of resources, or endpoints, in your API:
  • Schema resources. The resources that are defaulted from the schema.
  • System resources. The metadata services used to obtain the list of tables and resources and their attributes.
  • Custom resources. The resource endpoints you define explicitly.
    You can also create your own RESTful endpoints by creating custom endpoints or create endpoints to execute your JavaScript in 
    CA Live API Creator
     are return a JSON response by creating functions.
    For more information:
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:
  • Base table resources.
  • View resources.
  • Stored procedure resources.
Base Table Resources
Base table resources are resources for each base table.
Rows - JavaScript Object Model
When you create an API and you connect API Creator to your database, 
CA Live API Creator
 also builds a JavaScript object model from your database schema and creates a row object type (class) for each base table. This object model reads and writes data by addressing attribute/object accessors and persistence services.
For more information about the JavaScript object model and the row object type, see Row Objects.
View Resources
View resources are resources for each view table. You can access complex query processing using view resources.
For more information about how to access complex query processing, including automatic discovery, see View Resources.
Stored Procedure Resources
Stored procedure resources are 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:
System Resources
CA Live API Creator
 automatically creates the following system resources, which are metadata services used to obtain the list of tables and resources and their attributes:
  • /@tables,
  • /@tables/{tablename},
  • /@resources,
  • /@resources?projectId=1000,
  • /@authentication,
  • /@procedures,
  • /@docs
For more information about how to use the REST endpoints your API includes, see System REST endpoints.
Custom Resources
Custom resources are resources that you can define to shape your API. These resources can include related data (joined tables), alias attributes, and defined filters.
Custom resources provide the following:
  • Database abstraction layer.
     REST resources are loosely akin to database views, where you can define named projections, filters, and alias names. The database 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.
  • 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.
  • 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 Multitable Resources
After you define your custom multitable resource, you can select the attributes that your resource returns.
  1. With your API open, go to the Create, Resources, Resource tab and create a new custom resource by clicking 
    New Resource
    .
  2. Complete the following fields 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 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.
      For more information:
    • Free SQL.
       Free SQL resources are resources where you manually specify the SQL to execute.
      For more information about how to define Free SQL resource types, see Free SQL Resources.
      Pirate Syntax. Why just talk like a pirate, when you can also code like one? You can use the well-recognized 'arghhh' spelling of arg in API Creator.
    • 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
    Table
    The base table name. If you rename the table, fix the base table name. Renaming the base table 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 only used for parent subresources.
    For more information, see the Parent Subresources.
    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 resource as a resource whose attributes are combined and included in a containing 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]
  3. 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 and 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 multitable resource is defined.
Resource Details
You can apply filters and sorts to each level of the endpoint.
  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 table 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 table 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 table 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.
  1. With your API open, go to the Create, Resources, Attributes tab.
  2. Select the resource for which you want to return attributes, complete the following fields, and then save your changes:
    Columns
    Clear the checkbox for the base table 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 attributes, API Creator interprets this as "all attributes in table" and applies table columns you then add to this resource.
The attributes for that resource 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 to do filter and order, see the Resource Details section.
Create Subresources
Subresources are shown indented under each level. You can add custom child resources using JavaScript code.
  1. Create an API by connecting to a database called Demo.
  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 table, 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. 
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. To retrieve parent data, the best practice is to use parent subresources. 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 using one of the following methods:
  • 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 display in your API documentation.
    For more information about how to define non-persistent attributes, see Non-persistent Attributes
Move Resources between APIs
For more information about importing and exporting API definitions, see Import and Export APIs.
Extend Resources
You can further control resource processing using the following extensibility services:
  • Control the returned response values with 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.