Data Service

Using Trillo Workbench, you can build a data service for new or existing databases using a model driven approach i.e. you define data model as JSON files using a UI (or edit the JSON directly).

Architecture

The following diagram shows how you can create or discover database schemas using UI and expose them as APIs.

1. Enter metadata of new tables or edit metadata of existing tables. Workbench can discover tables of an existing database (i.e. introspect). The meta attributes of each artifact such as database, schemas, tables, columns, etc. are discussed below.

2. Metadata is saved as JSON in a file and also in the Trillo database. Trillo Workbench transparently archives old versions.

3. If you add/ change metadata of a table, Trillo Workbench can create/migrate tables in the database.

4. Using metadata, Trillo Workbench publishes APIs for each table. It publishes APIs to create, update, batch updates, delete, list, paginate through records, etc. It can also publish complex queries as APIs.

Data Service (Metadata to APIs)

Terminology

Trillo data layer is designed to be generic and independent of the type of database such as relational, document, graph, etc. Therefore, APIs use generic terms from UML (Unified Modelling Language). The following table shows terms that may be used interchangeably.

Similar Terms	Concept
data source, (dsName for short in API)	Represents a database. Data source's name is logical and used in APIs. It can be different from data source name.
table, class, collection (className, tableName, clsName, etc. for short).	Represents a structure with attributes. In a relational DB, it corresponds to a table.
column, attribute	A column of a table, an attribute of a class, a property of an object, etc.
association, relation	Relationship between tables or association between classes.
object, record	An instance of a class or a database record.

Data Source

The data source is same as a database. Its name is the logical name of the database to be used in API. It is a valid programming language identifier and permits no white space.

For example, a finance database may have a name as, "Finance DB" or "Finance-DB". In Trillo Workbench it may be represented by a data source, named 'FinanceDB" or "Finane_DB". Since the data source name should be a valid identifier, its name is different from the database (which is not a valid identifier). A data source name may be the same as its db name if the db name is a valid identifier.

Schema

If the database type is Microsoft SQL or Postgres, Trillo Workbench permits the creation of schemas under data source. Its logical name should be a valid identifier. Like the data source, the logical name may be different than the name in the database.

Class

A class corresponds to a table. It is created under a data source (MySQL) or schema (Postgres, Microsoft SQL). Its logical name is a valid identifier. Like the data source, the logical name may be different than the name in the database.

Similarly, the term attribute is used for a column.

Fully Qualified Names

Trillo Workbench API uses the name of the data source, schema, and class name in APIs. Since two different databases can have a table with the same name. The class name needs to be a fully qualified name in APIs. A fully qualified class name is given by dot (.) notation as follows.

<appName>.<dsName>.<schemaName>.<className>

• appName: application name. For the default database of the workbench, it is either "shared" or "auth". It is a logical folder to indicate the source application to which the database originally belongs.

• dsName: it is the data source name.

• schemaName: DB schema (required in case of Postgres, MySQL).

• className: class name.

Example 1: shared.finance.financeSchema.Invoice: Invoice table of financeSchema of the finance data source. (Postgres and Microsoft SQL).

Example 2: shared.common.AuditLog: AuditLog table of common data source (MySql)

The following 3 APIs can be used to extract the application name, data source name, and class name.

• BaseApi.app(qualifiedClassName): returns app name.

• BaseApi.ds(qualifiedClassName): returns data source/database name.

• BaseApi.cls(qualifiedClassName): return class name. A class name is always returned with the schema name with a dot in between. It means cls("shared.financeDB.financeSchema.Invoice") will return "financeSchema.Invoice".

Default Database

Trillo Workbench by default provisions Cloud SQL in Google Cloud (MySQL). It uses it for storing data, and metadata. It can also used for the new tables. By default, it creates the following two databases:

• cloud_vault (data source name - vault): this is used as the user, and tenant store. Its tables can be customized (while retaining existing attributes without change). You can't add new tables to this database using the workbench UI. The corresponding data source name is vault.

• cloud_common (data source name - common): this is used for several system tables that can be configured (while retaining a few important attributes as they are). Any new table specified in the application is created inside this database. The corresponding data source name is common.

Adding New Tables

Trillo Workbench UI uses the term class as an alias for a table. Similarly, the term attribute is used for a column. The reason for using class and attribute it to have generic terms for other databases such as MongoDB that may be made available through Workbench.

Using UI, you can add new tables, edit existing tables, or remove them.

• The UI is very intuitive.

• It provides a grid to create columns of tables, and an extended pane to specify the detailed meta property of each column (discussed below).

• You can also use the JSON editor to edit the file.

Creating a new table using Trillo Workbench UI

Default Attributes of a Table

When a table is created the following attributes (columns) are automatically added to the initial JSON. These attributes are optional. They can be renamed. But if you are building a new table and have not other reason, we recommend to keep them. They have been proven to work well. If the name is kept then their values would be set automatically by the platform.

Attribute Name	Description
id	By default, object identifier or primary key. An attribute flagged using "primaryKey" : true is treated as primary key for the data service. You can rename it or flag any other attributes as "primaryKey". The primary key type should be biginteger (long) or string. If the primary key type is biginteger and the value of tag "primaryKeyGenerator" is set to "serial", the primary key value will be automatically generated sequentially.
createdAt	Timestamp of object creation.
updatedAt	Timestamp of last time the object was updated.
deleted	If a record is deleted, this switch is set to true. By default, the workbench logically deletes a record. Using a parameter in the API, a record can be deleted permanently.
deletedAt	Timestamp of object's deletion.

The corresponding JSON is shown below (for an example class called Customer).

{
  "name" : "Customer",
  "attributes" : [
    {
      "name" : "id",
      "type" : "biginteger",
      "systemAttr" : true,
      "primaryKey" : true
    },
    {
      "name" : "createdAt",
      "type" : "biginteger",
      "systemAttr" : true
    },
    {
      "name" : "updatedAt",
      "type" : "biginteger",
      "systemAttr" : true
    },
    {
      "name" : "deleted",
      "type" : "boolean",
      "systemAttr" : true
    },
    {
      "name" : "deletedAt",
      "type" : "biginteger",
      "systemAttr" : true
    }
  ],
  "displayName" : "Customer",
  "nameInDS" : "Customer_tbl",
  "tableCreateable" : true,
  "primaryKeyGenerator" : "serial"
}

More Operations Available Via UI

There are several commonly used operations are available via UI.

1. Add a new database by specifying its URL and credentials.

2. Check database connection.

3. Add a new table to a database and specify its metadata.

4. Edit an existing table's metadata.

5. Introspect a database and list its tables. Selectively save metadata of discovered tables.

6. Update a table's schema in the database from metadata.

7. Describe a table to view its metadata (schema) in the database.

Database as APIs

Operations on databases, schemas, and tables are published as APIs. Database APIs take, db name, schema name, or class name (qualified table name) as parameters. For example, to get a record by "id" (identifier or primary key), class name parameter will be default.

A complete list of APIs is given in API documents: a) Trillo Java SDK, or b) Trillo Restful API.

An example API is discussed for each case, SDK or restful.

SDK Toolkit API Example

The platform makes all database tables and their metadata available as an SDK toolkit for use inside a serverless function.

An example API to retrieve an object is shown below (for Java, Python). These APIs are available under the java-class/ python-class DSApi.

Java

public static Object get(String className, String id)

Python

def get(className, id)

parameters: 

className: fully qualified name of the table.
id: primary key (identifier of the record)


returns:

a. If the record is found then it returns a Map (HashMap)/Dict.

b. If there is an error, it returns the Result object.

Result object has the following properties:

status: success | failed | unknown (in this case failed)
message: the error message
code: HTTP code
data: Any application-specific object that is returned to the invoker (it is
      generally null if there is an error).

See the document Trillo Serverless Function SDK (APIs) for more details.

Restful APIs

The platform also automatically publishes restful API for each table. You can view it by selecting the API Tab (as shown in the above screenshot).

Each restful API has the following format:

ds/<operation>/<className>

In the above URL, the meaning of each part is explained below:

ds: it means that this is an API for the data service.
<operation>: it is the name of the operation such as 'save', 
             'get', 'page', etc.
<className>: fully qualified name of the table.

 Example:
 
  /ds/get/shared.cloud_vault.User 

See the document Trillo Workbench Restful API for more details.

Testing APIs

By selecting the API tab, you can exercise an API. For example, you can add a few records using save API and retrieve them using get API. See the diagram below.

API tab for its specification and testing

SQL Queries as API

Complex SQL queries can be published as APIs. Any parameters that need to be substituted are specified by enclosing within 3 curly braces as {{{parameter}}}.

A SQL Query with Parameters Specified using 3 Curly Braces

An example SQL template ('UserByEmailPrefix') is shown below:

select * from cloud_vault.user_tbl as u where u.email like '{{{prefix}}}%'

------

Here, the prefix is a parameter.

when this template is invoked as API by passing the following parameters
as JSON:

{
   "prefix": "a"
}

The retful version of this is shown below.

The URL includes the template name ("UserByEmailPrefix") in the path. The restful API is as follows:

URL: /ds/query/UserByEmailPrefix

Method: POST (although the API does not update the data, in order to keep
              it readable, Trillo uses the POST method).

The complete POST body will be a JSON as follows.

{
    "prefix": "an"
}

Customize System Table

Trillo Workbench provides several tables to support its out-of-the-box functionality. For example, there are tables for user management, task management, audit and log messages, files, folders, etc. There may be instances when you want to extend these tables with additional columns. For example, you may need to add columns to the user's table. Trillo Workbench supports such customization. You will notice a red color button Customize on the toolbar for system tables. Once a table is customized, it is no longer treated as a system table.

We recommend that you don't delete or change the type of existing columns of a system table. It may break the system. You can change the length of the string type attributes. You can change other meta properties such as validation expression, indexed or not, unique or not, etc.

What you Achieved

• Create a complete data model for your application using UI.

• Understand and test APIs published by Trillo Workbench.

• Write complex queries and make them available as APIs.