Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
Showing results for 
Search instead for 
Did you mean: 
Developer Advocate
Developer Advocate
This is a searchable description of the content of a live stream recording, specifically "Episode 14 - Exploring CAP service level features and annotations" in the "Hands-on SAP dev with qmacro" series. There are links directly to specific highlights in the video recording. For links to annotations of other episodes, please see the "Catch the replays" section of the series blog post.

This episode, titled "Exploring CAP service level features and annotations", was streamed live on Fri 22 Mar 2019 and is approximately one hour in length. The stream recording is available on YouTube.

Below is a brief synopsis, and links to specific highlights - use these links to jump directly to particular places of interest in the recording, based on 'hh:mm:ss' style timestamps.

Brief synopsis

After a pleasant detour hacking together some CSV related utilities with Node.js we return to our Northbreeze CAP project and take a look at some of the service level features, looking how they can complement the data model, and playing around with some of the annotations available to us, to see what effect they have. We also take a look at fixing the issue we had with the boolean property :-0 back in Ep.10.

00:03:35: I was reminded by nabheet.madan3 that we finished Ep.10 on a cliffhanger, in that all the values for the discontinued property were "true", which is not actually the case when we look at the source data in Northwind itself.

00:05:10: Remember that SAP Inside Track OSLO is on 17 August 2019!

00:05:45: Proud of the latest keyboard in my growing collection of mechanical keyboards: an Anne Pro 2 with Gateron Brown switches, purchased from the same place I got my Vortex Race 3 - Candy Keys - recommended! The Anne Pro 2 is a so-called "60% keyboard" which refers to the size and number of keys.

00:07:10: There are no dedicated arrow keys on this keyboard so I've adjusted my Spectacle settings accordingly. (It turns out that I can define arrow keys in a layer, and use the Fn, Menu, Ctrl and Shift key cluster on the right, works very well in fact!).

00:08:40: Reminding ourselves of the detail of the discontinued problem, looking at the data model and specifically at the definition of the Products entity which includes the discontinued property.

00:10:35: In the CSV file for the Products data we can see various values for the discontinued property, specifically true and false randomly throughout. But when we look at the data via an OData query operation, we can see that every value for discontinued is true!

00:12:00: I've installed the Vimium Chrome extension which allows me to operate Chrome pretty-much mouse-less, and more specifically, using Vim style key bindings. Wonderful! I demonstrate this using my home website

00:12:55: We check directly in the persistence layer (the SQLite database) to see what values are actually stored:
14:10 $ sqlite3 northbreeze.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .tables
Breezy_Categories Restricted_Orders northbreeze_Products
Breezy_Products northbreeze_Categories northbreeze_Suppliers
Breezy_Suppliers northbreeze_Orders
sqlite> select * from northbreeze_Products;
1|Chai|10 boxes x 20 bags|18|39|0|10|false|1|1
2|Chang|24 - 12 oz bottles|19|17|40|25|false|1|1
3|Aniseed Syrup|12 - 550 ml bottles|10|13|70|25|false|1|2
4|Chef Anton's Cajun Seasoning|48 - 6 oz jars|22|53|0|0|0|2|2
5|Chef Anton's Gumbo Mix|36 boxes|21.35|0|0|0|true|2|2
6|Grandma's Boysenberry Spread|12 - 8 oz jars|25|120|0|25|false|3|2

00:13:20: It turns out that the problem lay in the conversion from JSON to CSV in the grab.js script, using the json2csv package.

00:14:45: To illustrate, we created a quick CDS project in my /tmp/ directory:

=> cds init --modules db,srv x

... then the definition of the Books entity to have a Boolean type property thus:
entity Books {
key ID : Integer;
title : String;
instock : Boolean;

and then added a CSV file my.bookshop-Books.csv with this content:
1,Book Title A,false
2,Book Title B,true

Then, after an npm install --save sqlite3 we ran cds deploy --to sqlite:x.db, which meant then we could immediately thereafter start the server with cds run, which gave us the http://localhost:4004 to check.

00:17:40: Lo and behold, we observed the problem first hand - both books were shown to be in stock!Turns out this issue is known, and I found an issue logged against the package's repository: Issue converting columns with boolean values. Basically, the conversion is treating true and false as strings, rather than boolean values, and as we know, any non-empty string is "truthy", resulting in every value for the discontinued property being true.

00:20:50: We create a second launch configuration in the project's launch.json for the deploy command, so we can put a breakpoint in init-from-csv.js (which is part of @Sap/cds).

00:22:15: A side note on VS Code's search facility, which by default explicitly excludes any node_modules/ directory when searching for files. This is usually what we want. But when we don't, like now, we can change the setting, by opening the settings JSON and adding this:

"search.exclude": {
"**/node_modules": false


This means we can now navigate to init-from-csv.js and set a breakpoint in the _init function to see what's going on.

00:24:34: Kicking off the launch configuration that we just created for the cds deploy command, we now hit the breakpoint we just sent, and can examine the data, which looks like this:
> Array(3) ["2", "Book Title B", "true"]

The third value is a string, rather than a boolean!

00:26:20: To fix this quickly, we go into the CSV file and modify the values - changing false to 0 and true to 1. Not the best solution, but why not, especially as we have control of the input data! 🙂

Here are the global substitution commands used:

After a new cds deploy invocation, we can see that the discontinued values are now correct. Good!

00:28:30: Thinking about the explicit difference in layers, between the data model and the service definition, in our project (and many typical CAP projects).

00:31:05: Starting to play around with a couple of annotations: @readonly and @insertonly to see what their effect is.

In the temporary project we created earlier, the CatalogService is defined as having a single entity Books which is annotated with @readonly:
service CatalogService {
@readonly entity Books as projection on my.Books;

00:35:10: We look briefly at the scripts repo content, which contains various configuration settings, including the shortcuts that I used, and a mechanism to generate them. The one in question here is t which I use to change to the /tmp/ directory, and in fact, like other shortcuts I have that take me to specific directories, I have the shortcut do for me what I'd do anyway, which is to call ls to show the files. This is how t is defined:
alias t='cd /tmp && ls -a'

00:36:40: In order to test whether we can make non-read-only calls on the Books entity, we prepare some book data in JSON format, in a new file book.json:

"ID": 42,
"title": "The Hitch Hiker's Guide To The Galaxy",
"instock": true

Now we can test, using curl:
=> curl -d @book.json -H "Content-Type: application/json" http://localhost:4004/catalog/Books

And as expected (or at least hoped for), we get a proper and appropriate HTTP response code - 405 Method Not Allowed!

00:40:25: We check in the service's metadata document to have a quick look at the UI focused annotations that are related (and are generated from the same @readonly annotation in the service definition.

00:42:00: We now try an OData delete operation on one of the entities, and receive the same response:
=> curl -X DELETE 'http://localhost:4004/catalog/Books(1)'
{"error":{"code":"405","message":"Method Not Allowed"}}


00:43:15: Having a brief look at another small modification of the service definition, this time using the excluding keyword, to move from a simple pass-through of an entity definition in the data model, to a custom, tuned version thereof. Going back to the main Northbreeze project, we add the following service to the service.cds file:
service Restricted {
entity Orders as projection on northbreeze.Orders;

This is for a new entity we also define, in model.cds, using the managed aspect from @Sap/cds/common:
entity Orders : managed {
key ID : UUID;
quantity : Integer;

Looking at the output of cds compile for this, we see the following SQL:

CREATE TABLE northbreeze_Orders (
modifiedAt SECONDDATE,
createdBy NVARCHAR(255),
modifiedBy NVARCHAR(255),
quantity INTEGER,

This shows us that the use of the managed aspect brings in the modified and created fields here, which are reflected in the metadata document.

00:50:00: So for the sake of demonstration, we now exclude the createdAt and createdBy properties:
service Restricted {
entity Orders as projection on northbreeze.Orders
excluding { createdAt, createdBy };

and, as expected, the metadata document describes the Orders entity without the created properties. I love it when a plan comes together..

00:52:45: Preparing for the final test, we replace the explicit UUID based key property ID by something that's the same but implicit, brought about by the use of the cuid aspect:
entity Orders : managed, cuid {
quantity : Integer;

And now we try creating a new order, thus:
=> curl -d '{"quantity":42}' -v -H "Content-Type: application/json" http://localhost:4004/restricted/Orders

And as a happy end to this episode, we indeed get back the HTTP response we were expecting, i.e. an HTTP 201 CREATED with the data in the response payload. Note in passing the Location header in the response (which goes hand-in-hand with a 201 response code), showing the (relative) address of the entity that was just created:
Location: Orders(a374413d-612f-4a8f-b3b3-1a9812311e53d)

00:58:50: Checking in the persistence layer with sqlite3, we see the data!
sqlite> select * from northbreeze_Orders;