Skip to Content
Technical Articles

Computed field example in CAP

In this post, I show one way of using computed properties in CAP, using CDS and service events in Node.js.

Over in the CAP section of the Community Q&A, Pierre Dominique asked an interesting question that I thought I’d have a go at answering in the form of a short post. Here’s the question: Counting association entities using CDS – go ahead and have a quick read of it, then come back here to find out one way of doing it.

Given the bookshop sample data as a basis, how do we extend the service to include a property which indicates, for each author, how many books they have written?

 

Step 1 – the definitions

Here’s the basic schema. It’s very similar to the example that Pierre gives, but doesn’t have the extra ‘numberOfBooks’ property at this level – I wanted to give myself an extra challenge by not defining it at the data model layer, but defining it only at the service definition layer. If we’re going to have a computed property, we should avoid having it pollute the space at the persistence layer.

namespace my.bookshop;

entity Books {
	key ID : Integer;
	title  : String;
	stock  : Integer;
	author : Association to Authors;
}

entity Authors {
	key ID : Integer;
	name   : String;
	books  : Association to many Books on books.author = $self;
}

db/schema.cds

 

It’s possible to add computed properties to an entity at the service definition level. Here’s what the service definition looks like with a computed property for the requirement at hand:

using my.bookshop as my from '../db/schema';

service CatalogService {

	entity Books as projection on my.Books;
	entity Authors as select from my.Authors {
		*,
		null as numberOfBooks: Integer
	};

}

srv/service.cds

 

Notice the “as select from”, as opposed to the simpler “as projection on”. It allows us to specify properties, which is what we do in the block that follows:

  • the “*” brings in all the existing properties from the my.Authors definition
  • then we define a new property “numberOfBooks” as an Integer type

 

Let’s take a moment to have a look what that produces. Running this at the command line:

cds compile srv/service.cds --to sql

… gives us the schema definition, which includes these two views that have been generated from the two entities defined at the service level:

CREATE VIEW CatalogService_Authors AS SELECT
  Authors_0.ID,
  Authors_0.name,
  NULL AS numberOfBooks
FROM my_bookshop_Authors AS Authors_0;

CREATE VIEW CatalogService_Books AS SELECT
  Books_0.ID,
  Books_0.title,
  Books_0.stock,
  Books_0.author_ID
FROM my_bookshop_Books AS Books_0;

Take note of the “numberOfBooks” property in the “CatalogService_Authors” view.

While we’re in the mood for looking at generated compilations, let’s do the same, but this time see what the service definition will look like, in the form of Entity Data Model XML (EDMX) – which you and I know more comfortably as OData metadata.

Running this at the command line:

cds compile srv/service.cds --to edmx

… gives us this:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
  <edmx:Reference Uri="https://oasis-tcs.github.io/odata-vocabularies/vocabularies/Org.OData.Core.V1.xml">
    <edmx:Include Alias="Core" Namespace="Org.OData.Core.V1"/>
  </edmx:Reference>
  <edmx:DataServices>
    <Schema Namespace="CatalogService" xmlns="http://docs.oasis-open.org/odata/ns/edm">
      <EntityContainer Name="EntityContainer">
        <EntitySet Name="Authors" EntityType="CatalogService.Authors">
          <NavigationPropertyBinding Path="books" Target="Books"/>
        </EntitySet>
        <EntitySet Name="Books" EntityType="CatalogService.Books">
          <NavigationPropertyBinding Path="author" Target="Authors"/>
        </EntitySet>
      </EntityContainer>
      <EntityType Name="Authors">
        <Key>
          <PropertyRef Name="ID"/>
        </Key>
        <Property Name="ID" Type="Edm.Int32" Nullable="false"/>
        <Property Name="name" Type="Edm.String"/>
        <NavigationProperty Name="books" Type="Collection(CatalogService.Books)" Partner="author"/>
        <Property Name="numberOfBooks" Type="Edm.Int32"/>
      </EntityType>
      <EntityType Name="Books">
        <Key>
          <PropertyRef Name="ID"/>
        </Key>
        <Property Name="ID" Type="Edm.Int32" Nullable="false"/>
        <Property Name="title" Type="Edm.String"/>
        <Property Name="stock" Type="Edm.Int32"/>
        <NavigationProperty Name="author" Type="CatalogService.Authors" Partner="books">
          <ReferentialConstraint Property="author_ID" ReferencedProperty="ID"/>
        </NavigationProperty>
        <Property Name="author_ID" Type="Edm.Int32"/>
      </EntityType>
      <Annotations Target="CatalogService.Authors/numberOfBooks">
        <Annotation Term="Core.Computed" Bool="true"/>
      </Annotations>
    </Schema>
  </edmx:DataServices>
</edmx:Edmx>

There’s one thing in there that’s of particular interest – the annotation of this new property. You can either stare at this XML until you see it, or just look at it as extracted from that sea of angle brackets:

<Annotations Target="CatalogService.Authors/numberOfBooks">
  <Annotation Term="Core.Computed" Bool="true"/>
</Annotations>

This has been automatically generated from that simple service definition earlier. Thanks, CAP!

 

Step 2 – the implementation

Implementing the logic to provide values for this computed property is next up. As you may know, we can provide custom logic in the form of functions attached to specific events in the request/response lifecycle as OData operations are processed, and in a very comfortable way, via the “convention over configuration” approach of simply providing a JavaScript file of the same base name as the service definition file, in the same directory.

The Node.js CAP runtime will then discover this file and use it as extra (or overriding) implementation logic. This is what it looks like:

module.exports = srv => {

  const { Books } = srv.entities

  srv.after('READ', 'Authors', (authors, req) => {

    return authors.map(async author => {
      const publications = await cds.transaction(req).run(
        SELECT .from(Books) .where({ author_ID: author.ID })
      )
      author.numberOfBooks = publications.length
    })

  })

}

srv/service.js

(In case you’re wondering: yes, I am trying to avoid semicolons and double quotes, and yes, I like the ES6 fat arrow syntax for functional style, and no, I am not writing and will not write any “class” based code here – in my opinion the whole “object orientation comes to JS” is the wrong direction entirely. Stick that in your pipe and smoke it! :-))

Anyway, here’s what’s going on in the code:

  • we grab the Books entity from within the service object
  • we hook in a function to be called when READ requests are processed on the Authors entity, specifically after the main part of the request has been fulfilled (i.e. using srv.after)
  • that function expects the results of the request fulfilment (i.e. the author(s) retrieved), plus the original request object, from which we can create a context in which to run a CDS query
  • the query is within a map function over the authors retrieved, and goes to get the books for that author

The CDS query is made using the CDS Query Language (CQL) fluent API, which I’ve tried to illustrate with some gratuitous whitespace (before .from and .where, in particular).

Once the value for “numberOfBooks” has been computed and assigned, we simply “let go” and the enhanced result set is returned in the response.

 

Step 3 – profit!

Here’s what this results in, after deploying the definitions and starting the service (I have a few books and authors in some sample CSV files):

=> cds deploy && cds run
 > filling my.bookshop.Authors from db/csv/my.bookshop-Authors.csv
 > filling my.bookshop.Books from db/csv/my.bookshop-Books.csv
/> successfully deployed database to ./bookshop.db

[cds] - connect to datasource - sqlite:bookshop.db
[cds] - serving CatalogService at /catalog - impl: service.js
[cds] - service definitions loaded from:

  srv/service.cds
  db/schema.cds

[cds] - server listens at http://localhost:4004 ... (terminate with ^C)
[cds] - launched in: 1126.872ms

And here’s some sample output, retrieved with “curl” and nicely pretty-printed with “jq” (yes, folks, this is all in a terminal, ON THE COMMAND LINE)*:

=> curl -s http://localhost:4004/catalog/Authors | jq
{
  "@odata.context": "$metadata#Authors",
  "@odata.metadataEtag": "W/\"8q5jjLD6vJ0ARrjnkajTONXIn38vpa1wxoXucua4kzU=\"",
  "value": [
    {
      "ID": 42,
      "name": "Douglas Adams",
      "numberOfBooks": 3
    },
    {
      "ID": 101,
      "name": "Emily Brontë",
      "numberOfBooks": 1
    },
    {
      "ID": 107,
      "name": "Charlote Brontë",
      "numberOfBooks": 1
    },
    {
      "ID": 150,
      "name": "Edgar Allen Poe",
      "numberOfBooks": 2
    },
    {
      "ID": 170,
      "name": "Richard Carpenter",
      "numberOfBooks": 1
    }
  ]
}

 

That’s pretty much all there is to it, at least as far as I can see. I hope you find this useful. I had fun writing it, thanks Pierre for a good question.

 

*i.e. the future

6 Comments
You must be Logged on to comment or reply to a post.
  • Hi,

    Wow, thanks for this post DJ! Very useful and detailed, as usual.

    I have one question though: what are the pros and cons of this approach compared to creating a view (calculation or plain SQL) and computing the number of books in the view and then exposing the view as an (OData) entity? Or maybe doing this at the CDS level (I don’t know if this is supported) in the service definition for instance?

    I guess that doing this at the DB level would be better in terms of performance but then we would lose some of the abstraction provided by CDS?

    Cheers,

    Pierre

    • Great question, and there are pros and cons to each approach. I see that Johannes Vogel has added some info related to this to your question in Q&A, actually, which I will try out myself too later.

      I do like the abstraction nature of CDS, the higher up away from persistence, the more generic it is, for me. Of course, that comes at some cost (performance, perhaps), but (a) performance is only an issue if the delays are measurable, and (b) I wanted to explore and show how to access the rest of the data from a service event hook 🙂

      • I found a way to do it using CDS (just because I wanted to know if this was possible :-)):

        service CatalogService {
        
        	entity Books as projection on my.Books;
        	entity Authors as select from my.Authors, my.Books {
        		key Authors.ID,
            name,
        		@Core.Computed
        	  count(Books.ID) as numberOfBooks: Integer
        	} where Authors.books.ID = Books.ID
            group by Authors.ID;
        }

        cds compile .\srv\cat-service.cds –to sql :

        CREATE VIEW CatalogService_Authors AS SELECT
          Authors_0.ID,
          Authors_0.name,
          COUNT(Books_1.ID) AS numberOfBooks
        FROM ((my_bookshop_Authors AS Authors_0 CROSS JOIN my_bookshop_Books AS Books_1) LEFT JOIN my_bookshop_Books AS Books_2 ON (Books_2.author_ID = Authors_0.ID))
        WHERE Books_2.ID = Books_1.ID
        GROUP BY Authors_0.ID;
  • Hi DJ Adams,

    Before to start to talk about my issue, congratulations for your videos “Hands-on SAP dev” live stream series, really nice work and big effort.

    Well, I have an authorization issue in my cds nodejs when i uploaded to Cloud foundry and i’m stuck here. Please, can you check what i missed? I want to exposed a CDS in nodejs with a scope associated but i received unauthorized, because when i contacted to the app says… “Anonymous access”. Let me explain my config:

    this is my service… I created three different to check the authorization in each of them

    using my.location as my from '../db/data-model';
    
    
    service CatalogService  {
        entity Zstreets_poland as projection on my.ZStreets_poland;
    }
    
    
    service CustomService @(requires:'ztest-p1941544866trial.Display')  {
        entity Zstreets_poland as projection on my.ZStreets_poland;
    }
    
    service CustomServicedentro {
         @(readonly, requires:'testing-p1941544866trial!t20471.Display')
        entity Zstreets_poland as projection on my.ZStreets_poland;
    }
    
    service CustomServicedentro2 {
         @(readonly, requires:'Display')
        entity Zstreets_poland as projection on my.ZStreets_poland;
    }

    My cdsrc.json:

        "auth": {
            "passport":{
                "strategy": "JWT"
            }
        }
    
    }

    And my app protected with route:

    {
        "welcomeFile": "app/index.html",
        "authenticationMethod": "route",
        "logout": {
          "logoutEndpoint": "/do/logout"
        },
        "routes": [
            {
              "source": "^/app/(.*)$",
              "target": "$1",
              "localDir": "/resources",
              "authenticationType": "xsuaa"
            },
            {
              "source": "^/srv/(.*)$",
              "destination": "srv_api"
            }
        ]
    }

    and my roles defined in xs-security.json (here i tested with different options in the name with name-subdomain-space or name-subdomain etc…)

    {
        "xsappname": "testing",
        "tenant-mode": "dedicated",
        "scopes": [
          {
            "name": "$XSAPPNAME.Display",
            "description": "display"
          }
        ],
        "role-templates": [
          {
            "name": "App_ztest_service",
            "description": "Read books",
            "scope-references": [
              "$XSAPPNAME.Display"
            ]
          }
        ]
      }

    And my yaml:

     - name: ztest-app
        type: html5
        path: app
        parameters:
          disk-quota: 256M
          memory: 256M
        requires:
          - name: ztest-dest
          - name: ztest-uaa
          - name: srv_api
            group: destinations
            properties:
              name: srv_api
              url: '~{url}'
              forwardAuthToken: true
              strictSSL: false
    resources:
      - name: ztest-uaa
        type: org.cloudfoundry.managed-service
        parameters:
          path: ./xs-security.json
          service-plan: application
          service: xsuaa
      - name: ztest-dest
        type: org.cloudfoundry.managed-service
        parameters:
          service-plan: lite
          service: destination

    And when i go to my app, i login in my hana trial, but when i’m going to recover the information from datasource says “Forbidden” because its login with anonymous, Why is anonymous? If I’m logging into SAP with my email and the role generated is assigned to me in Cloud foundry. What i missed?

    In addition i tried to add in “requires” the name of role created in sap cloud foundry…. but it didn’t work

    Many Thanks