#BTP #SAPCAP: Quirky Nuggets (N03-N04)

Ajit_K_Panda · ‎2023 Jun 05

Blog Series

Quirky Nuggets (N01-N02): CAP Event Handler, Data Uniquenes (@assert.unique)
Quirky Nuggets (N03-N04): OData Operator, Undeployment of DB artifacts
Quirky Nuggets (N05-N06): CF Application Health Checks, Consumption of Functions/Actions of a remote...

Introduction

This blog post is part of the ongoing series titled 'Quirky Nuggets.' Within this blog post, we will delve into the world of OData operators and delve into the undeployment of database artifacts using the HDI deployer.

Nugget 03

This quirky nugget is about OData operators. In OData, operators are used to perform various operations on data within the context of a query. These operators enable filtering, sorting, and transforming data in OData queries. Some commonly used operators are:

Comparison Operators: eq (equal), ne (not equal), gt (greater than), ge (greater than or equal), lt (less than), le (less than or equal)
Logical Operators: and, or, not
Arithmetic Operators: add, sub, mul, div, mod
String Operators: concat, substringof
Date and Time Operators: year, month, day, hour, minute, second

These are some of the operators commonly used in OData queries. The specific set of operators supported may vary depending on the OData service implementation you are working with. Based on this, I have tried some of the operators with OData service of a CAP based application as shown below:

You can utilize the provided schema and service definition as a basis for the examples provided. If you prefer, you can load data into the schema to test them.

db/schema.cds

srv/service.cds

namespace cap_quirks_03.db;
using {cuid} from '@sap/cds/common';

entity Roots : cuid {
    name    : String(20);
    descr   : String(100);
    dfield  : Date;
    tfield  : Time;
    dtfield : DateTime;
    tsfield : Timestamp;
    nfield  : Integer;
    afield  : Decimal(20, 6);
}

using { cap_quirks_03.db as db } 
      from '../db/schema';

service MyService {

  entity Roots as projection on db.Roots;

}

Now let's look at some examples:

@Host = {{Host}}

GET {{Host}}/my/Roots?$filter=nfield eq floor(789.20)
GET {{Host}}/my/Roots?$filter=nfield ge ceiling(700.30)
GET {{Host}}/my/Roots?$filter=nfield ge round(800.30)
GET {{Host}}/my/Roots?$filter=afield ge 23891100.123
GET {{Host}}/my/Roots?$filter=startswith(name,'root')
GET {{Host}}/my/Roots?$filter=startswith(toupper(name),'ROOT')
GET {{Host}}/my/Roots?$filter=startswith(tolower(name),tolower('ROOT'))
GET {{Host}}/my/Roots?$filter=endswith(name,'1')
GET {{Host}}/my/Roots?$filter=contains(name,'ot')
GET {{Host}}/my/Roots?$filter=INDEXOF(tolower(name),tolower('Root')) ne -1
GET {{Host}}/my/Roots?$filter=LENGTH(name) gt 5
GET {{Host}}/my/Roots?$filter=substring(name,0,4) eq tolower('Root')
GET {{Host}}/my/Roots?$filter=trim(name) eq 'root 1'
GET {{Host}}/my/Roots?$filter=concat(name,descr) eq 'root 2descr 2'
GET {{Host}}/my/Roots?$filter=year(dfield) eq 2023
GET {{Host}}/my/Roots?$filter=month(dfield) gt 6
GET {{Host}}/my/Roots?$filter=day(dfield) gt 10
GET {{Host}}/my/Roots?$filter=hour(tfield) gt 10
GET {{Host}}/my/Roots?$filter=minute(tfield) gt 15
GET {{Host}}/my/Roots?$filter=second(tfield) gt 15
GET {{Host}}/my/Roots?$filter=day(dtfield) ge 10 and hour(dtfield) ge 10
GET {{Host}}/my/Roots?$filter=fractionalseconds(tsfield) gt 400
GET {{Host}}/my/Roots?$filter=date(dtfield) eq 2023-02-20
GET {{Host}}/my/Roots?$filter=time(dtfield) ge 10:20:20

Nugget 03 Summary

OData operators enable filtering, sorting, and transforming data in queries, including comparison, logical, arithmetic, string, and date/time operators.

Note: It is essential that proper space is provided in query and unnecessary space will result in erroneous response.

Nugget 04

The SAP HDI Deployer (@sap/hdi-deploy) module is a deployment tool provided by SAP to simplify the deployment of db artifacts to SAP HANA databases. It is designed to work with Node.js and provides a command-line interface (CLI) for deploying HDI artifacts, such as database tables, views, and procedures, to a target HANA database instance. The @Sisn/hdi-deploy module abstracts the complexity of the deployment process and automates tasks such as creating database containers, deploying artifacts, and managing dependencies. It is commonly used in SAP HANA development projects to streamline the deployment process and ensure consistent and reliable deployments.

During the development of an application, modifications to the schema or service definitions often lead to corresponding changes in the database tables and views. These changes may involve adding or removing tables and views. In a CAP based application, when the project is built, it generates HANA database artifacts based on the provided schema and service definitions. Normally, the HDI deployer then schedules only the newly added or modified HANA database artifacts for deployment. By default, the deployer does not schedule the removal of deleted artifacts. However, additional configuration options can be implemented in your project to enable automatic undeployment of the deleted artifacts. This nugget will provide you with various configurations to achieve that.

To illustrate the necessary configuration, consider the following schema, which consists of three entities: Roots, CompMItems, and Roots2.

schema.cds

When it becomes necessary to remove the Roots2 entity from the schema definition, it is also essential to delete it from the HDI container in the HANA database. Here are different approaches to achieve this:

Using ‘cds deploy’ CLI commands
```
cds deploy --to hana:cap_quirks_04-db --auto-undeploy 
```
The aforementioned command performs the rebuilding of the schema definition and deploys it into the HDI container named 'cap_quirks_04-db'. Additionally, it undeploys the Roots2 database artifact.

When the deployment occurs, the HDI deployer scans the provided artifacts and compares them with the existing artifacts in the server-side HDI container. It determines the set of added, modified, and deleted files. The '--auto-undeploy' tag ensures that the deleted files are appropriately handled and applied.
Via undeploy.json with array of paths to the artifacts
When you add HANA db configuration to your CAP project using command ‘cds add hana’, it automatically adds undeploy.json file into your db with the following content:

undeploy.json

This above configuration ensures that in every deployment, deleted views or indexes or constraints are applied or executed. Here we can include path to Root2 entity, so that it gets deleted from container on deployment. Note that, it requires full complete path containing namespaces: "src/gen/cap_quirks_04.db.Roots2.hdbtable"
```
[
  "src/gen/**/*.hdbview",
  "src/gen/**/*.hdbindex",
  "src/gen/**/*.hdbconstraint",
  "src/gen/cap_quirks_04.db.Roots2.hdbtable"
]
```
Note: "real" paths, path patterns and path negations are supported as shown below:
```
[
  "src/Table.hdbcds",
  "src/Procedure.hdbprocedure",
  "src/*.hdbtable",
  "**/*.hdbtable",
  "!**/*.hdbtable"
]
```

Set auto-undeploy in MTA development descriptor
Both of the previous approaches can also be handled via development descriptor (mta.yaml) as shown below. In first approach simply you can set ‘auto_undeploy’ property to true. In second approach, list of paths to deleted artifacts are provided in ‘undeploy’ property.

First Approach

- name: cap_quirks_04-db-deployer
  type: hdb
  path: gen/db
  properties:
    HDI_DEPLOY_OPTIONS:
      auto_undeploy: true
  parameters:
    buildpack: nodejs_buildpack
  requires:
    - name: cap_quirks_04-db

Second Approach

- name: cap_quirks_04-db-deployer
  type: hdb
  path: gen/db
  properties:
    HDI_DEPLOY_OPTIONS:
      undeploy:
        - "src/gen/cap_quirks_04.db.Roots2.hdbtable"
  parameters:
    buildpack: nodejs_buildpack
  requires:
    - name: cap_quirks_04-db

Note that, both properties are set under HDI_DEPLOY_OPTIONS and that becomes an user-defined environment variable in cap_quirks_04-db-deployer which is used by HDI deployer to process undeployment.

Nugget 04 Summary

By applying the provided additional configuration, the HDI Deployer can be utilized to automatically undeploy deleted artifacts.

Conclusion

In this blog post, we explored a bit about OData operators and undeployment of database artifacts using the HDI deployer.

More information about cloud application programming model can be found here. You can follow my profile to get notification of the next blog post on CAP. Please feel free to provide any feedback you have in the comments section below and ask your questions about the topic in sap community using this link.