Docs‎ > ‎CA Live API Creator‎ > ‎Create‎ > ‎

Resources

Define new custom REST resources using the Resource link. Resources serve the following important purposes:
  • Database Abstraction Layer: Resources are loosely akin to database views, where you can define named projections and filters.  This protects your application for underlying changes in the database

  • Minimize Network Latency: beyond view-like functionality, you can define child (sub-resource) typically for related parent/child data.  For example, you might define a Customer that includes its Purchase Orders and Line Items.  When you issue REST retrievals, the returned JSON includes all of this data in 1 network trip.

Resource Types


There are three different types of resources:
  • Normal is the most common case: these are resources that join together one or more table, allowing you to alias the table(s) and columns, and specify joins and filters.
  • Free SQL is for resources where you manually specify the SQL to execute. These resources are read-only, but they do give you full latitude to set up the SQL any way you want.
  • JavaScript resources are the most difficult, and the most advanced: they allow you to write server-side JavaScript code that will be executed whenever this resource is accessed. Your JavaScript code will be 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.
  • MongoDB this is a special resource that will connect to a specific MongoDB server/database/collection and return filtered requests in JSON format using pipieline syntax.
Your server has many kinds of resources (also called "End Points"):
  • Base Table Resources - these are automatically created for each Base Table for the active Database
  • Custom Resources - created in the API Live Creator to include related data (joined tables), project / alias attributes, define filters etc.
  • View Resources - automatically created for each View Table
  • Stored Procedure Resources - automatically created for each Stored Procedure
  • Logic Administration Resources - automatically created meta data services to obtain the list of tables and resources, their attributes, etc.
Base Table and Custom Resources provide REST API support for GET, PUT, POST and DELETE.  As further described in the Architecture, their behavior includes pagination, optimistic locking, active logic enforcement, update refresh, etc.

The Is a collection Checkbox

This checkbox describes whether the currently selected resource (in the Resource Tree) is expected to be a single row at most, or potentially more than one row. Put another way, it defines whether the table for the resource in question is a "child" table of the containing resource's table. A top-level resource should normally be a collection. A sub-resource is a collection if there can be many rows for a single row in the containing resource. If a sub-resource is for a table that is a "parent" table of the containing resource's table, then it should not be a collection, since at most one row can be expected.

Default End Points

Live API Creator Resources are built automatically from two sources:
  • Schema Resources: your base tables, view tables and stored procedures become RESTful Resource End Points (subject to security), as shown here


  • Logic Administration Resources: system-supplied meta data is provided so your software can discover all the resources (e.g. /@tables, /@tables/{tablename}, /@resources, /@resources?projectId=1000, /@authentication, /@procedures, /@docs) see this link.
Default resources are described in this section.  The next major section below describes Custom Resources, which you define in the API Live Creator for multi-table (joined) "document model" data access.

Base Table Resources

You can expose your Base Tables as Resources (subject to security) to enable your project to get underway instantly.  You can then introduce Custom Resources as client development proceeds.

Rows - JavaScript Object Model


Live API Creator defines a row object type (class) for each Base Table.  These are created when you Connect your schema to Live Creator(step A), and stay in sync with the Schema.  This is denoted as the JS Object Model in the diagram below.



Instances of these objects are created during update operations, and passed as context to your logic (events, derivations, validations etc).

Unlike the anti-pattern Anemic Data Objects, you can think of these as "Smart Data Objects" since they encapsulate and actively enforce the business logic you define through events, derivations and validations. Active means that logic invocation is automatic - it's always enforced, since you don't have remember to call it.

Row objects have the following behaviors:
Attributes
Row have attributes for every column, so you can refer to a Order's amount_total using: row.amount_total.  
Role Accessors
Rows provide role accessors, so you can refer to an Orders' row.customer.balance, or obtain a collection of row.lineitems (a collection of lineitem rows).
Expression Help
The JavaScript code editor provides easy "code completion" access to row attributes and role accessors:
toString
This method is provided to show attribute values for logging and debugging.
Persistence
Read, Insert, Update and Delete verbs save changed rows, with logic enforcement.  Please see Interacting with Data.
metaData
Returns the description of the object, useful for extension services seeking to deal with rows generically.
Logic Aware
Rows enforce Reactive Logic, and server-side JavaScript Logic Events.
Resource Row Events
A resource can optionally have Resource Row Events, which allows for programmatic manipulation of the resource results during retrieval, e.g., to inject new columns and to filter out row.

Overriding Implicit Resources

You can define a resource with the same name as the Base Table. This serves two important functions:
  • Database Abstraction: This effectively hides the Base Table, perhaps to provide the database abstraction functionality noted above for Rest retrieval/update
  • Refresh: an important additional consideration is Update Refresh. The update APIs optionally return all the resources affected by your update. The Resource names for these are your Base Tables. Resource overriding enables you to provide abstraction for refresh, just as for Rest retrieval/update.

View Resources

View Resources are automatically created for each View Table. This provides access to complex query processing. You can assign security control to such resources. View Resources are not currently updatable, since the system required view composition for View / Base Table Mapping which is not provided by all databases.

Stored Procedure Resources

Stored Procedure Resources are automatically created for each Stored Procedure.  This enables you to leverage the business logic already developed in your database.

Logic Administration Resources

Logic Administration Resources are automatically provided by the system.  They provide meta data information, such as the list of tables, resources, and so forth.

Custom Resources

You will often want to define named Resources that:
  1. Deliver "document oriented" multi-table results to clients, instead of flat relational tables (these can be more convenient to client code, and reduce latency by eliminating multiple server calls)

  2. Select and alias the attributes, so that your API is not simply a direct reflection of the schema  
The following is an example of a multi-table resource for aliasing attributes on any selected column attribute:

Resource Details

The Details tab for base table (normal) allows filters and sorts to be applied the each level of the endpoint.

Resource Attributes

Optionally, you can select the attributes to be returned for your resource. If you do not select attributes, the system interprets this as "all attributes in table."  This means that table columns added in the future apply to this resource.

You can also explicitly select which attributes are returned (subject to security), and override the default name as shown below.  You can use the Select All option, but unlike selecting no attributes, future table columns are not added.  This provides an effective way to "lock down" a resource to the exact set of attributes desired, unaffected by subsequent changes to the table.


Sub Resources

Sub Resources are shown indented under each level.  Select the level and click 'New Level' and you will see a dialog that will attempt to provide suggestions based on relationship roles (if the level selected is a base table).  Custom child resources can be added via JavaScript code.

The following is an example of defining a Sub Resource, with the following key points:
  1. The project (API) has been created by connecting to a database called Demo.

  2. A custom resource has been created using the Customers table from the Demo database.

  3. We click New Level to create a Sub Resource.

  4. The displayed the dialog box of related tables, where we chose Orders - from a different database.  See Multiple Databases to find out how your API can integrate multiple databases.

The table below describes Sub Resource properties:

 Property  Description  Notes  As
 table Base Table name  You will need to "fix this up" if you rename the table, but all client applications will be unaffected  
 Resource Name  Unique name within Root Resource   Will be the container name with JSON, so can only contain alphanumerics, or the following special characters: _  
 description  Comments    
 is_collection  True if more than 1 row can result  Determines whether the JSON is an array or object; an exception is generated if you specify No, and the system detects multiple rows.

This is typically only used for Parent Sub Resources.
 
 join  Parameterized filter to retrieve results based on containing Resource  Use [] to refer to containing resource attributes, e.g.:
  • fk = [pk] to retrieve child rows for a containing parent

  • pk = [fk] to retrieve parent for a containing child
  Braket terms must follow the "=" sign, as above.
 
 Filter
[Details Tab]
 Filters results (in addition to security) 

 Merged into WHERE sql clause
 Use base table column names (not the Resource attributes aliases)  

 You can filter on Parent Sub Resource attributes using qualified attribute names, so, to filter LineItems to expensive Parts:

   Part.price > 1000

 You are not restricted to Resource Attributes.
 
 Order
[Details Tab
 Comma-separated list of sort fields, with optional asc/desc indicator  Use base table column names  


Each Resource Attribute is identified with an (alias) name, and inclues a column_name and format.

Parent Sub Resources

In the diagram above, note that Product is a Sub Resource of Lineitem.  Even though Product is a parent (one side of a one-to-many), it is defined as a Sub Resource.  That means you get JSON like the snippet shown below:

        "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 very common pattern in defining Resources over a Junction Table. Internally, the system collect such Parent Sub Resources and computes the join that is sent to the DBMS as a single command. Such automated join processing extends to multiple and nested parents. Parent Sub Resources are the preferred method for retrieving parent data. It is not necessary to introduce business logic (formulas, etc) to obtain such data for retrieval.

Resource Row Events

You can provide Resource Row Events to inject computed attributes, and discard rows.

Resources Types

Normal Resource Types automate SQL handling.  Additional Resource Types provide flexibility to integrate other data sources, control SQL, etc. 

Normal

This resource is linked to existing base SQL tables.

Free SQL

Defining a Free SQL Resource enables you to manually control the SQL.  Note such queries are not update-able - the system has no knowledge of what underlying table / rules should be executed.  There is an advanced ability to enable underlying security for specific tables in these queries.  Please see here for more information.

Pirate Syntax
Why just talk like a pirate, when you can also code like one? Live Creator provides full support for the well-recognized arghhh spelling of arg.

JavaScript

Use this Resource Type to supply server-side JavaScript to return JSON.  This enables you to connect to multiple servers of any types.  

For example, you might want to aggregate REST/JSON services as shown here.

MongoDB 

This allows a connection to a MongoDB server, database, and collection. This can be a stand-alone top resource on joined (linked) to SQL using the pipeline syntax ({customer_name: "<name>"} - the <name> represents the parent SQL attribute being passed into the JOIN).

Resource Import / Export

Use this to move individual resources between API projects.