Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

[qmacro]

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

In this task you'll define and implement an unbound action, that does something that you might remember from the Developer Challenge on APIs last year in August, and you'll end up writing some (hopefully) interesting code for the implementation.

Background

Last year we ran a Developer Challenge on APIs. In one of the tasks - Task 3 - Have a Northbreeze product selected for you - you needed to make a call to an API endpoint supplying your SAP Community ID and it would return a Northwind product. In the example for my ID, "qmacro", it showed this being returned:

{
  "@odata.context": "$metadata#Edm.String",
  "value": "Rössle Sauerkraut"
}

In other words, the product "corresponding to" my SAP Community ID is "Rössle Sauerkraut". Different products were returned for different SAP Community IDs, so that your answers would be different to each other. The calculation of which product to return was based on the value of your SAP Community ID.

In this task, you will need to define an API endpoint, in the form of an unbound action, and write the implementation to calculate, look up and return the name of a product that corresponds to the SAP Community ID that is sent by the TESTER.

Correspondence calculation

So how should you determine which product to select for a given SAP Community ID? The same way that it was determined in last year's Developer Challenge for that task. And that is to turn the SAP Community ID string value into a numeric value, and then use that to select a specific product with an ID (in ProductID) that matches that numeric value.

Here's the logic:

First:

• ensure the SAP Community ID is all lower case
• convert each character to its equivalent decimal ASCII code
• add the resulting list of ASCII codes together to make a total

Then:

• determine how many products are in your Northbreeze data
• read a single product where the key is the ASCII codes total value

Noting that:

• you will need to use modulo arithmetic to ensure that the ASCII codes total value falls within the range between 1 and the total number of Northbreeze products

And here's an example of that logic in action, where (for the sake of illustration) the SAP Community ID value "QmacrO" is sent in the payload of the HTTP request:

• converting "QmacrO" to lower case gives "qmacro"
• the decimal ASCII codes for "qmacro" are 113, 109, 97, 99, 114 and 111
• added together, these codes come to 643
• there are 77 Northbreeze products
• using modulo arithmetic to turn 643 into a number between 1 and 77, it becomes 28
• the product with ID 28 is Rössle Sauerkraut

Be careful with the modulo calculation, because you need to take into account that the result of a modulo calculation could be 0, which is not in the ID range. It would also never be 77. This is why 643 modulo 77 becomes 28, not 27 (basically, do the modulo calculation and add 1).

Clearly these instructions have already told you that there are 77 products. You should have 77 products too if you started with the repo as recommended. Nevertheless, we encourage you to use some CQL in your implementation to programatically determine how many products there are, not just set the value 77 in a constant 🙂

The requirements

Here are the specific requirements for this task.

Add an unbound action called selectProduct to the definition of your northbreeze service. It should expect a String value for a single parameter named communityid, and it should return the same type as the ProductName element in the Products entity type.

Note that although you will of course be supplying your SAP Community ID as normal when you submit to the TESTER (see the next section), as usual, it will be the TESTER that picks and sends an SAP Community ID to your endpoint, not you, of course.

In the implementation, use CQL to determine the number of products you have, and then use the logic described in the "Correspondence calculation" section earlier to determine the product ID, based on the (semi-random) SAP Community ID value received. Then use CQL to retrieve that product and return the product name.

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-selectProduct.

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-selectProduct"
}

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 😀

[Liyon_SV]

There seems to be a slight inconsistency in the instructions. The task initially mentions creating an unbound action but then later specifies requirements for an unbound function. Or perhaps I am misunderstanding the instructions.

[qmacro]

Awesome, well spotted. Mea culpa. Corrected (function -> action). Thanks!

[mxmw]

Passed!

Noticed the same as Liyon_SV and I went for the action as this is mentioned in title.

[qmacro]

Thanks, yes, sorry for the confusion. Definitely should be an action. Corrected.

[AndrewBarnard]

The spread operator, map and reduce come in handy here.  

[qmacro]

Lovely! You say all the right things to pique my interest! Would love to see some example from you here!

In wanting to generate some sample tests to use in the TESTER module for this, I generated stuff with jq, "because jq". And you wouldn't believe how lovely this was - it just flowed from my keyboard, and was instantly beautiful. Not because of my coding prowess, but because of how jq has been designed. See the `getid` function here:

[AndrewBarnard]

@qmacro 

OK - now that your interest was piqued - here is what I did.

I took the communityid and using the spread operator, spread it from a String into an array of individual characters.

Noting that the map() method of an Array creates a new array populated with the results of calling a provided function on every element in the calling array, I used map with a function to convert to lower case and then determine the numeric value of each of the elements. ( the "characters").

The reduce() method of an Array executes a user-supplied "reducer" callback function on each element of the array, passing in the return value from the calculation on the preceding element "forwards". The final result of running the "reducer function" across all elements of the array is a single value. I used reduce to sum the numeric values into one single value. 

So here it is:

const theSum = [...communityid].map(b => b.toLowerCase().charCodeAt()).reduce( (a,b) => a + b )

[qmacro]

Absolutely lovely. I like the use of spread on a string, instead of, say, split(''). Did you see the jq function explode which does effectively both splitting and converting to codes? https://jqlang.github.io/jq/manual/#explode 

And to your classic use of reduce with (a, x) => a + x, the jq's add function (in my screenshot) is actually just syntactic sugar for that - see https://github.com/jqlang/jq/blob/1f1e619f4e1478598aca56115948eb14d484b9fe/src/builtin.jq#L11:

def add: reduce .[] as $x (null; . + $x);

[cguttikonda24]

Hi DJ, @qmacro ,

Looks like the instructions inter-twined between action and function.

Did build a function and couldnt get pass the tester changed it to action and it works...

{
ID: "e373bede-7a34-467c-a988-99a0017b708d",
task: "northbreeze-selectproduct",
result: "PASS",
createdAt: "2024-07-26T11:28:27.792Z",
createdBy: "anonymous",
modifiedAt: "2024-07-26T11:28:27.792Z",
modifiedBy: "anonymous",
serviceurl: "https://northbreeze-main.cfapps.us10-001.hana.ondemand.com/odata/v4/northbreeze",
communityid: "cguttikonda24"
},

 Just wondering is there a fluent api approach on select to get count of records from a table.

[qmacro]

Nice work, and sorry about the confusion. 

You ask a good question - I'm not sure how this might work (altho I have a small brain) because we have to know the number of products in order to calculate the index, via the modulo of that number before diving in with that calculated index. That said, I'd love to hear from others who have other ideas! 

[YogSSohanee]

Initially created an unbound function and was receiving the value correctly. 

But then realized after getting an error via the deployed application that instead of function, an unbound action is expected. 

So changed it to action, but then started getting this error. 

Can anyone please help if they have encountered this or have knowledge on this? 

[qmacro]

Rather than me tell you directly, I'll give you a hint.

Functions are called with GET and parameters are sent inside parentheses at the end of the URL. 

Actions, on the other hand, are called with POST and parameters are sent in the payload of the request, usually in JSON.

It most likely didn't help that I confused you into creating a function first, sorry. So - just check how you're making the call to your action. It is likely / possible that you've implemented it correctly - it's just that you're calling it like a function 🙂 

HTH!

[Alpesa1990]

My submission fot task 9.

Congrats again! I faced to the same problem but solved it by switching to unbound action.

Regards

[YogSSohanee]

Thanks @qmacro for your valuable suggestion. I got it working now and cleared the difference between calling Functions and Actions as well. 

PFB the tester response.

[geek]

A couple of curve balls in there to make things interesting. Thanks for this:

[qmacro]

🙂

[gphadnis2000]

 my submission for task 9.Finally learned about unbound action and how to pass payload.Nice task.

[sudarshan_b]

Catching up this fantastic task during the weekend, here's my submission - 

My CQL statement to determine the number of products (count) was working fine when tested locally (localhost:4004), but was failing after deploying to CF. It took me quite some time to figure out that the issue was the "count" function needs an alias to work in CF i.e.; 'count(ProductID) as count'.  Strangely the "count" without alias works fine locally. 

Finally, got it working with 

New learning!! 🙂

[M-K]

Here's my submission.

Big thanks to @sudarshan_b, I ran into the very same problem and fixed it by adding an alias. The BTP application log was helpful here, but the error "❗️Uncaught Cannot read properties of undefined (reading 'ProductName')" was not really pointing me to search for the error at the SELECT count(*).

[MioYasutake]

My submission for task 9.

[sandeepGottipati]

completed this task. But had difficulty while using count in the cql statement. But figured out by comments , solved it using alias in the count statement.

[Wang65]

Thank you for your prompt response and for sharing this valuable information. It’s greatly appreciated

[qmacro]

Hey there @sudarshan_b @M-K @sandeepGottipati I'm curious, not using an alias in a count() scenario should work fine, regardless of runtime context (e.g. BTP/CF), esp. in the simple context of the northbreeze repo. Can you please share more info - it's hard to tell without any detail. Perhaps you could share the project code in a gist or repo, or just show the .js implementation file here?

[M-K]

After some debugging I found out what happened. Here is a snipped from my main.js

const db = await cds.connect.to('db');
const { Products } = db.entities; 

const productCountQuery = await SELECT.one.from(Products).columns('count(*)');
console.log(productCountQuery);

const productCountQuery2 = await SELECT.one.from(Products).columns('count(*) as count');
console.log(productCountQuery2);

console.log-Entries local in BAS:

{ count: 77 }
{ count: 77 }

console.log Entries from CF:

{ 'count ( * )': 77 }
{ count: 77 }

So why do I get a different result here?

By the way: the Error "‌‌️️❗️Uncaught Cannot read properties of undefined (reading 'ProductName')" was because I used the undefined "count" for the modulo calculation, leaving me selecting the product with ProductID = "NaN"

const productCount = productCountQuery.count;
const charCodesCount = this.getCharCodesCount(communityid);
const productId = charCodesCount % productCount + 1;

const productQuery = await SELECT.one.from(Products, productId, product => { product.ProductName });
return productQuery.ProductName;

[qmacro]

Some great debugging and analysis there, @M-K !

I still can't reproduce what you're seeing, and I've just deployed the same code, i.e.

const cds = require('@sap/cds')

module.exports['northbreeze'] = class NorthbreezeService extends cds.ApplicationService {
    async init() {

        const db = await cds.connect.to('db')
        const { Products } = db.entities

        this.on('countNoAlias', async () => {
            const c = await SELECT
                .one
                .from(Products)
                .columns('count(*)')
            console.log(c)
            return c.count
            })
        this.on('countWithAlias', async () => {
            const c = await SELECT
                .one
                .from(Products)
                .columns('count(*) as count')
            console.log(c)
            return c.count
        })

        return super.init()
    }
}

 and in each case the log output is the same:

   2024-07-30T06:10:56.55+0000 [APP/PROC/WEB/0] OUT [odata] - GET /northbreeze/countWithAlias()
   2024-07-30T06:10:56.58+0000 [APP/PROC/WEB/0] OUT { count: 77 }

   2024-07-30T06:11:10.84+0000 [APP/PROC/WEB/0] OUT [odata] - GET /northbreeze/countNoAlias()
   2024-07-30T06:11:10.85+0000 [APP/PROC/WEB/0] OUT { count: 77 }

If you could share your entire project directory e.g. on GitHub then I could take a further look. Thanks!

[sudarshan_b]

Hello DJ,

Here is my implementation of the custom handler (notice the queries on lines 18 and 23) - 

this.on("selectProduct", async (req) => {
    const db = await cds.connect.to("db");
    const { Products } = db.entities("northwind");

    const { data } = req; //get the input data
    const communityid = data.communityid;
    // Convert input community Id into LowerCase and then derive its ASCII decimal value
    const commIdArr = Array.from(communityid.toLowerCase());

    let commIdAsciiVal = 0;
    for (x of commIdArr) {
      commIdAsciiVal += x.charCodeAt(0);
    }

    console.log("Comm ID Ascii Value: " + commIdAsciiVal);

    // determine total number of products in the db
    const totalProducts = await SELECT.one
      .from(Products)
      .columns("count(ProductID) as count");
    console.log("Total Products with alias: " + totalProducts.count);

    const totalProductsNoAlias = await SELECT.one
      .from(Products)
      .columns("count(ProductID)");
    console.log("Total Products No alias: " + totalProductsNoAlias.count);

    // Derive a 'logical' product Id from community id by taking Mod
    const commIdProductId = (commIdAsciiVal % totalProducts.count) + 1;

    console.log("Comm Id Prod id: " + commIdProductId);
    //Select the relevant product
    const result = await SELECT.one
      .from(Products)
      .columns("ProductName")
      .where({ ProductID: commIdProductId });

    console.log(result);

    return result.ProductName;
  });

and here is the CF logs output (notice the output on lines 3 and 4) -

:"POST /odata/v4/northbreeze/selectProduct ","type":"log"}
   2024-07-30T07:19:55.50+0000 [APP/PROC/WEB/0] OUT Comm ID Ascii Value: 1162
   2024-07-30T07:19:55.51+0000 [APP/PROC/WEB/0] OUT Total Products with alias: 77
   2024-07-30T07:19:55.52+0000 [APP/PROC/WEB/0] OUT Total Products No alias: undefined
   2024-07-30T07:19:55.52+0000 [APP/PROC/WEB/0] OUT Comm Id Prod id: 8
   2024-07-30T07:19:55.52+0000 [APP/PROC/WEB/0] OUT { ProductName: 'Northwoods Cranberry Sauce' }

Also, you can refer to my project on github at - https://github.com/slash-h/devchallenge-072024

Thank you.

[qmacro]

Thanks, I'll take a look tomorrow! BTW, I love your GitHub handle, "/h" 🙂 

[sudarshan_b]

Thanks DJ 🙂 coming from ABAP background “/h” was the first choice 😉

[M-K]

"/h" is great. But sometimes you need "/hs" 😉

[sudarshan_b]

Hi DJ,

Yes, it was a strange issue for me as well because I performed test code with CDS REPL before actually writing it in the handler. The count() without alias worked fine in REPL and also during the local testing. However, after deploying, I got the same error as mentioned by @M-K  i.e., the SELECT count(ProductID) was failing.

[M-K]

Ok, I found out how to fix that behaviour by making some changes to the package.json:

I mosty kept the file like it was since generating the project for Task 0 and I added the files for the subsequent tasks in the same project folder.

My package.json contains this section:

"dependencies": {
  "@sap/cds": "^7",
  "express": "^4",
  "sqlite3": "^5.1.7"
},
"devDependencies": {
  "@cap-js/sqlite": "^1",
  "@sap/eslint-plugin-cds": "^3",
  "eslint": "^9"
},

and the package.json from @qmacro's northbreeze-project looks like this (without devDependencies)

"dependencies": {
  "@cap-js/sqlite": "^1.0.1",
  "@sap/cds": "^7",
  "@sap/xssec": "^4.2.0"
},

after modifying my package.json it started working locally in BAS and on CF the same. @sudarshan_b's package.json contains the same dependencies like mine before, so I think it can be fixed there like that, too.

So what is the difference between "@cap-js/sqlite" and "sqlite3" here?

[vineelaallamnen]

task 9 is done

[tobiasz_h]

Hello,
Passed:

[spassaro]

PASS!

Another great opportunity to use a reduce function 🙂

I struggled a bit when deploying the app on the trial btp account. A strange message was preventing me to deploying the app with this message ""Routes cannot be mapped to destinations in different spaces". After trying deleting all spaces/apps/routes, i managed to get rid of the message by manually setting another route with this command

cf map-route northbreeze cfapps.us10-001.hana.ondemand.com --hostname northbreeze2

I know it's a bit off topic, just wanted to share the issue and the workaround I found.

[qmacro]

Thanks for sharing, @spassaro ! The cause is quite likely to be that you already had an app of the same name deployed somewhere else...

[MatLakaemper]

here my submission