Utilities

REST Resources

Use the Logic Designer to define REST Resources you can use to retrieve and update data. Resources serve two important purposes:

Database Abstraction Layer: REST 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 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.

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

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.

Default resources are described in this section. The next major section below describes Custom Resources, which you define in the Espresso Designer 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

Espresso defines a row object type (class) for each Base Table. These are created when you Connect your schema to Espresso (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 behavior described below.

Attributes

Row have attributes for every column, so you can refer to a Order's 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).

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.

Select and alias the attributes, so that your API is not simply a direct reflection of the schema

Here is a quick example of a multi-table resource:

and for aliasing attributes:

The numbers on the diagram refer to the Getting Started Guide discussion that shows how to create resources:

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

Indent vs. Parent/Child

Containment means that the resultant rows are selected based on the containing row.

In the example above:

Lineitems are contained in Orders, and they are related as a child

Product is contained in LineItems, and they are related as a parent

Below is an example of defining a Sub Resource:

Key Points:

We have created a Project (API) by connecting to a database called Sample

We have created a Custom Resource using the Customers table from the Sample database

We click New Level to create a Sub Resource

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.

where clause

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

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:

Resource Row Events

Linked Resources

Use Linked Resources to generate JSON with Link tags to designated Resources. This aids in discoverability.

Resource Types

In the "normal" case, you simply select tables and the system builds the SQL for you. Resource Types afford additional control over the means by which your resources are retrieved.

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 wish to aggregate REST/JSON services as shown here.

Custom SQL

Use Custom SQL to control the SQL that is generated, but retain support for updates.

** Planned future feature **

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? So, Espresso provides full support for the well-recognized arghhh spelling of arg.