Task 10 - The power of CDL with as-select (July Developer Challenge - "Reverse APIs")

[qmacro]

This is a task in the July Developer Challenge - "Reverse APIs".

In this task you'll go on a short but hopefully enlightening journey into views and projections, to see how you can elevate SQL concepts to the service definition level in your CDS model, with the power of CDL. You will add a "single expression" entity to your Northbreeze service, which should provide the response required without any implementation at the code (Node.js or Java) level.

Background

In Capire, the Views and Projections part of the section covering Core Definition Language (CDL) introduces us to ways of deriving new entities from existing ones by projections, like views in SQL. Two variants are described:

• as select from
• as projection on

If you've started with sample CAP services, you're very likely to have come across as projection on as it's near ubiquitous in those samples.

But what about as select from, what's that all about? Well it's the "batteries included" approach to deriving a view, with all the power of SQL in its CAP-enhanced form, i.e. CQL.

If you want to peruse some examples, use the power of GitHub's excellent search facility:

org:sap-samples language:cds "as select from"

Again, the cds REPL may come in handy here for experimenting with CQL. How about this, with the basic Northbreeze service (this experiment will make sense in the requirements section next):

await cds.test()
const { Products } = cds.entities
await SELECT `count(ProductID)` .from (Products)

Paste that into a cds REPL and see what you get! For example, put these three lines in a file, e.g. repl-session.js and then paste it into a new REPL like this:

cds repl < repl-session.js

and you should see something like this:

; cds repl < repl-session.js
Welcome to cds repl v 8.0.3
> const server = await cds.test()
[cds] - loaded model from 2 file(s):

  srv/main.cds
  db/schema.cds

[cds] - connect to db > sqlite { url: ':memory:' }
  > init from db/data/northwind-Suppliers.csv
  > init from db/data/northwind-Products.csv
  > init from db/data/northwind-Categories.csv
/> successfully deployed to in-memory database.

[cds] - using auth strategy {
  kind: 'mocked',
  impl: '../../../../../../../usr/lib/node_modules/@sap/cds-dk/node_modules/@sap/cds/lib/auth/basic-auth'
}

[cds] - using new OData adapter
[cds] - serving northbreeze { path: '/northbreeze' }

[cds] - server listening on { url: 'http://localhost:46113' }
[cds] - launched at 7/26/2024, 2:18:05 PM, version: 8.0.3, in: 663.929ms
> const { Products } = cds.entities
> await SELECT `count(ProductID)` .from (Products)
[ { count: 77 } ]

The requirements

Here are the specific requirements for this task.

Define a new entity at the service layer, in your northbreeze service. This entity should be called TotalProducts and defined using the as select from variant as mentioned earlier. It should return a count of the number of products, like this:

{
  "@odata.context": "$metadata#TotalProducts",
  "value": [
    {
      "count": 77
    }
  ]
}

Note the shape of the payload response, in this JSON representation - the value property is an array with a single element, which is an object, which itself has a single property which is count. The type of that property's value is of course an integer.

If you find yourself implementing anything in your services.js file, think again 🙂

That's it!

Submitting to the TESTER

Now you're ready to submit your CANDIDATE service, with this new API endpoint, to the TESTER!

The payload

The task identifier you need to supply in the payload of your submission is: northbreeze-TotalProducts.

You'll have already done this sort of thing previously so just head back there for the more detailed instructions if you need them, or to the the section titled "The Tester service, and making a test request" in the main challenge blog post.

You'll need to submit a JSON payload like this:

{
  "communityid": "<your-community-id>",
  "serviceurl": "<the-URL-of-your-service>",
  "task": "northbreeze-TotalProducts"
}

And, just as with the previous (and all further tasks):

• the value for the communityid property should be your ID on this SAP Community platform (e.g. mine is "qmacro")

• the value for the serviceurl property should be the absolute URL (i.e. including the scheme), of your CANDIDATE service which contains the API endpoint (see ℹ️ A note on URLs and services).

That's it!

Logging of test results

Remember that you can check on your progress, and the progress of your fellow participants - all requests are logged and are available in an entity set served by the TESTER service. The entity set URL is https://developer-challenge-2024-07.cfapps.eu10.hana.ondemand.com/tester/Testlog and being an OData V4 entity set, all the normal OData system query options are available to you for digging into that information.

Until the next task, have fun, and if you have any questions or comments, leave them below!

[Liyon_SV]

Passed 😀

[mxmw]

Pass!

[gphadnis2000]

Here is my submission for task10 Pass!!!! 

[cguttikonda24]

Hello Dj @qmacro 

Thank you for answering my question in the previous task. 
So its by combining the string literals with fluent api.

However in the document of CQN representation we see a count property. 
SELECT = {SELECT:{
distinct: true,
from: source | join,
mixin: { ...element },
columns: projection,
excluding: [ ...string ],
where: _xpr,
groupBy: [ ...expr ],
having: _xpr,
orderBy: [ ...ordering_term ],
limit: { rows:expr, offset:expr },
forUpdate: { wait: number },
forShareLock: { wait: number },
search: _xpr,
count: Boolean
}}

Documentation Ref : https://cap.cloud.sap/docs/cds/cqn#select 

Was checking if we can use this to get the count of the entries.

{
ID: "115757e9-48e5-42f3-9b58-f80f27108cf7",
task: "northbreeze-totalproducts",
result: "PASS",
createdAt: "2024-07-29T10:29:06.006Z",
createdBy: "anonymous",
modifiedAt: "2024-07-29T10:29:06.006Z",
modifiedBy: "anonymous",
serviceurl: "https://northbreeze-main.cfapps.us10-001.hana.ondemand.com/odata/v4/northbreeze",
communityid: "cguttikonda24"
},

[geek]

Challenging workout for a Monday morn:

[sudarshan_b]

My submission, PASS - 

Thank you

[sachin_sap]

Interesting to note, it needs cds deploy if not running in memory as the view is actually created on the DB itself! 

[qmacro]

Good observation, yes! 

[YogSSohanee]

My submission for task 10:

Odata call

[Alpesa1990]

My submission for task 10.

[M-K]

Here's my submission:

[MioYasutake]

My submission for task 10.

[vineelaallamnen]

Task 10 done

[MatLakaemper]

Hello DJ,

your cds repl version is 

Welcome to cds repl v 8.0.3

my version is Welcome to cds repl v 7.9.3 

I am working with the dev-Container,
how can i update that?

Regards, Matthias

[qmacro]

Good question (and kudos for working with the dev container)! If for example you're working with the dev container based on the spec in the `.devcontainer/` directory in the qmacro/northbreeze repo then the Dockerfile looks like this:

# syntax=docker/dockerfile:1

ARG VARIANT="20"
FROM mcr.microsoft.com/vscode/devcontainers/javascript-node:0-${VARIANT}
ARG CAPVER="latest"

# Install some generally useful tools
RUN apt-get update \
    && apt-get -y install --no-install-recommends \
    curl git sqlite3 entr source-highlight

# Install SAP CAP SDK globally
USER node
RUN npm install -g @Sap/cds-dk@$CAPVER

# vi mode everywhere and nicer prompt KTHXBAI
RUN cat <<EOBASHRC >> /home/node/.bashrc
export EDITOR=vi
set -o vi
bind -x '"\C-l": clear'
EOBASHRC

RUN echo 'export PS1=${PS1/\$ /\\\\n$ }' >> /home/node/.bashrc

# Ready!
WORKDIR /home/node

The two relevant lines are:

`ARG CAPVER=latest`

and

`RUN npm install -g @Sap/cds-dk@$CAPVER`

If you rebuild the container "normally" then as nothing has changed, the container layer cache system will be used for efficiency, and you'll get the same container as before.

But you can rebuild without the cache, causing everything to be constructed again.

If you're in VS Code, use the Command Palette to find and run the:

"Dev Containers: Rebuild Container Without Cache"

If you're on the command line, you can use the `--no-cache` option to `docker build`.

Have fun!

[MatLakaemper]

Hello DJ,

very interesting command, didn´t know that,

But:

I am Working on a Win11 System with  Docker-Desktop 4.32.0

cds repl remains on v 7.9.3

also "docker system prune" didn´t help 

.... nevertheless - that's the way it is

kind regards, Matthias

----- Dockerfile: --------------------

# syntax=docker/dockerfile:1

ARG VARIANT="20"

FROM mcr.microsoft.com/vscode/devcontainers/javascript-node:0-${VARIANT}

ARG CAPVER="latest"

# Install some generally useful tools

RUN apt-get update \

    && apt-get -y install --no-install-recommends \

    curl git sqlite3 entr source-highlight

# Install SAP CAP SDK globally

USER node

RUN npm install -g @Sap/cds-dk@$CAPVER

# vi mode everywhere and nicer prompt KTHXBAI

RUN cat <<EOBASHRC >> /home/node/.bashrc

export EDITOR=vi

set -o vi

bind -x '"\C-l": clear'

EOBASHRC

RUN echo 'export PS1=${PS1/\$ /\\\\n$ }' >> /home/node/.bashrc

# Ready!

WORKDIR /home/node

----------- devcontainer.json ---------------------

{

    "name": "Basic CAP devcontainer definition",

    "build": {

        "dockerfile": "Dockerfile",

        "args": {

            "VARIANT": "20"

        }

    },

    "customizations": {

        "vscode": {

            "extensions": [

                "sapse.vscode-cds",

                "dbaeumer.vscode-eslint",

                "humao.rest-client",

                "qwtel.sqlite-viewer",

                "mechatroner.rainbow-csv"

            ]

        }

    },

    "forwardPorts": [

        4004

    ],

    "remoteUser": "node"

}

[qmacro]

Nice - there should be no reason why you can't force a rebuild without cache, even on a Windows machine. 

[MatLakaemper]

found it, reason was in package.json

    "dependencies": {

       "@cap-js/sqlite": "^1.0.1",

         "@sap/cds": "^8"   here was "^7"  

[AndrewBarnard]

Good morning @qmacro ,

I stumbled around for a while with this one and I'm still not happy with my passing solution. 

In CQN I'd like to use SELECT.one.from(Products) .columns `{ count(ProductID) as count}` but couldn't work out how to use the "one" in the CDL definition of the entity.

[qmacro]

This is a great thought! 

I think part of the problem in the circumstance that you're wanting to dig into (which is great) is that at the CDS model level, within a service, an `entity` defines what (when served via the OData adapter) is an entity set in its "resting state", i.e. something naturally resembling a list of items. So if we think about that, then perhaps the cleanest approach would be to embrace that context and define that entity as a singleton, using the @odata.singleton annotation:

@odata.singleton
entity TotalProducts as
    select from Products {
        count(ProductID) as count : Integer
    };

When requested, e.g. via an HTTP GET request to `/odata/v4/northbreeze/TotalProducts`, we now receive a response with a JSON representation that looks like this:

{
  "@odata.context": "$metadata#TotalProducts",
  "count": 77
}

Does that help?

[spassaro]

Nice and easy (and yet very enlightenly). Still wondering why BAS asked me to explicitly set the 

@CDS.redirection.target: 'northwind.Products'

annotation  on the entity Products as projection on... when defining the TotalProducts entity. It seems like two service entities can't be defined related to the same entity.. I've read the Capire docs here https://cap.cloud.sap/docs/cds/cdl#auto-redirect but still wondering why...

[qmacro]

Hi there @spassaro - thanks for the comment! Can you please explain a bit more, and provide some detail? What had you written, what do you mean "BAS asked me to ..." and what did you end up having to do? Can you share the code?

[spassaro]

Sure! This is the code I ended up to in my main.cds file

using northwind from '../db/schema';

// @Path: '/northbreeze'
service northbreeze {
    // @CDS.redirection.target: 'northwind.Products'
    entity Products   as projection on northwind.Products
    actions {
        function stockValue() returns Integer
    };
    entity Suppliers  as projection on northwind.Suppliers;
    entity Categories as projection on northwind.Categories;
    function productInfo (id: Int32) returns String;
    action selectProduct (communityid: String) returns String;

    entity TotalProducts as select from northwind.Products {
        count(ProductID) as count : Integer
    }
        

}

  If I leave uncommented line 5, this is the error I got both in the Products and TotalProducts services:

If I add that line, everything goes fine. Thank you for your time and interest!

[MatLakaemper]

Hi,

since yesterday evening i have some problems, to send requests to the test-Server, 

also the Testlog is not reachable. After some time (about 2 Minutes) it works again.

Regards,
Matthias

Additional:

it seems, that after the following call the Test-Server crashes for about 2 Minutes.

[qmacro]

Hi Matthias, thanks. That's odd, I haven't changed or redeployed anything recently. And the service seems to be running fine now. I'll dig into the logs to see if there is anything odd there. 

[MatLakaemper]

Hi DJ,

perhaps the Problem has to do with the Rest-Client in VS.

The Curl-Command works, but wenn  i call the same via the Restclient i see the following error...
That seems strange. http://localhost results in  172.0.0.1:443 Port 443 is https.

requestError: connect ECONNREFUSED 127.0.0.1:443.

[MatLakaemper]

sorry, iam stupid.... now it works... 

regards, Matthias

#  curl -s --url 'http://localhost:4004/odata/v4/northbreeze/TotalProducts' | jq .

GET http://localhost:4004/odata/v4/northbreeze/TotalProducts

Content-Type: application/json

[MatLakaemper]

But i think my call has crashed the Tester again just now

[tobiasz_h]

Hello,

Passed 🙂

[MatLakaemper]

here is my submission