topic Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever in Application Development and Automation Discussions

Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Reverse APIs")

qmacro — Mon, 22 Jul 2024 10:21:46 GMT

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

This task sees you creating a third service, and a first API endpoint within that service. Unlike the previous two services this service will be a more fully formed OData service complete with some sample data. Ready?

Background

The previous two services in this month's Developer Challenge, basic and plain, were fairly simple and only contained API endpoints that were based on simple functions and an action. There were no entities defined in the CDS model, no data, and no implicit, out-of-the-box CRUD+Q OData operations involved. Today's task is where that changes.

Northwind and Northbreeze

For this task and the rest of the tasks this month (there are 4 more after this one), you'll make available API endpoints that all revolve around a simple data model based off the famous Northwind service, various versions of which are running at https://services.odata.org. As you can see from the OData V4 Northwind service's service document there are quite a few "collections", otherwise known by the OData term "entity sets", including:

Categories
CustomerDemographics
Customers
Employees
Orders
Categories
Products
Regions
Shippers
Suppliers
...

I like to keep things simple, and often turn to a simplified version that I like to call "Northbreeze" (geddit?), where I just have three entity types, with relations between them:

Categories
Products
Suppliers

I have a live Northbreeze service running at https://qmacro.cfapps.eu10.hana.ondemand.com/northbreeze and this is based on a simple CAP service that I've defined and made available in a GitHub repo at qmacro/northbreeze. As you can see, there's no actual code, there's just:

the schema-level definition of Categories, Products and Suppliers
CSV based data to seed the three entity sets that correspond to those entity types
an extremely simple service exposing those three entity types via direct projections

And that's pretty much it. Note that the data reflects as accurately as possible the data in the original Northwind service, i.e. there are 77 products ranging from Chai, in the Beverages category, from the supplier Exotic Liquids to Original Frankfurter grüne Soße, in the Condiments category, from the supplier Plutzer Lebensmittelgroßmärkte AG.

You can (and I recommend that you do) clone the repo and use that as a starting point for the service for this and the subsequent tasks in this challenge.

The requirements

Here are the specific requirements for this task.

The service must be served using the OData V4 protocol, with the (default) path prefix /odata/v4 plus the service name northbreeze, i.e.:

/odata/v4/northbreeze

You must make an API endpoint available that returns a sort of "product information" string. The endpoint has a single integer parameter id and the TESTER will make a call to the endpoint with the value of a product ID (it will be between 1 and 77).

It should return a string, formed thus:

For example, let's take the Northbreeze product with the ID 11:

the product name is Queso Cabrales
the name of the product's supplier is Cooperativa de Quesos 'Las Cabras'

So the call would be made to:

/odata/v4/northbreeze/productInfo(id=11)

and the expected response should be:

{
  "@odata.context": "$metadata#Edm.String",
  "value": "Queso Cabrales by Cooperativa de Quesos 'Las Cabras'"
}

By the way, looking back at the OData query operation URLs for the supplier of Queso Cabrales just now, did you know that for such scalar properties returned for OData query or read operations (CompanyName in this example) you can get the literal value on its own? Just append /$value to the URL, so that

https://qmacro.cfapps.eu10.hana.ondemand.com/northbreeze/Products(11)/Supplier/CompanyName

becomes

https://qmacro.cfapps.eu10.hana.ondemand.com/northbreeze/Products(11)/Supplier/CompanyName/$value

and you get

Cooperativa de Quesos 'Las Cabras'

instead of

{
  "@odata.context": "../../$metadata#Suppliers(5)/CompanyName",
  "value": "Cooperativa de Quesos 'Las Cabras'"
}

The endpoint definition

As you may have worked out by now, this API endpoint needs to be defined as an unbound function (yes, it could be implemented as a function bound to a particular product but we'll be doing that sort of thing in a later task).

When you have defined the endpoint, you should see evidence of it (productInfo) in the OData metadata, which should look like this (vastly reduced for brevity):

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
  <edmx:DataServices>
    <Schema Namespace="northbreeze" xmlns="http://docs.oasis-open.org/odata/ns/edm">
      <EntityContainer Name="EntityContainer">
        <EntitySet Name="Products" EntityType="northbreeze.Products">
          <NavigationPropertyBinding Path="Category" Target="Categories"/>
          <NavigationPropertyBinding Path="Supplier" Target="Suppliers"/>
        </EntitySet>
        <EntitySet Name="Suppliers" EntityType="northbreeze.Suppliers">
          <NavigationPropertyBinding Path="Products" Target="Products"/>
        </EntitySet>
        <FunctionImport Name="productInfo" Function="northbreeze.productInfo"/>
      </EntityContainer>
      <Function Name="productInfo" IsBound="false" IsComposable="false">
        <Parameter Name="id" Type="Edm.Int32"/>
        <ReturnType Type="Edm.String"/>
      </Function>
    </Schema>
  </edmx:DataServices>
</edmx:Edmx>

The endpoint implementation

The idea of this task is to get you using some CQL. In other words, you'll have to write a simple implementation for this unbound function, writing a handler for the on hook as usual, for the productInfo event.

CQL

If you're not sure where to start, I'd recommend the Querying in JavaScript section of Capire, in particular the SELECT class. Within that SELECT section, you may want to pay particular attention to the following properties:

In particular, you'll have to work out how to get a property of a related entity. In other words, how do you express what you want, using the facilities such as path expressions or projection functions described in the columns section. On this topic, you may find this blog post helpful: Turning an OData expand into a cds.ql CQL query with a projection function in CAP.

Using async/await

One more tip: Up until now, you've defined your JavaScript function based handlers for functions and actions in quite a straightforward way. But now you're about to use the SELECT class, which by itself defines a query. It doesn't run it directly. If you read the Executing Queries section of Capire you'll see that "you can just await a constructed query, which by default passes the query to cds.db.run()". That means you can use await in front of your SELECT expression.

But that in turn means that your handler function needs to be defined as an asynchronous one, with async. If you're looking for some examples or inspiration, remember that there are some great CAP samples in the sap-samples org on GitHub. And remember also that GitHub has an excellent search mechanism, where you can express detailed contextual searches, like this one:

org:sap-samples language:js "await SELECT" AND (path:/srv/)

Don't you just love it when you come across a well thought out URL scheme for this sort of search facility? Beautiful.

Setting up for CQL

Note that also, for this handler function and some of the subsequent ones in this service, you'll need a connection to the database layer, and a 'handle' on the Products entity definition (to use in your SELECT expressions, specifically with the .from() method). You can just use this classic pair of constant definitions, before you start to define your on handler:

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

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

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

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!

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

mwn — Mon, 22 Jul 2024 14:45:41 GMT

I'm trying to follow the examples, but keep getting

Expected argument 'target' to be an entity path string, a CSN definition, a {ref}, a {SELECT}, or a {SET}

from my

let result = await SELECT ...

I also got stuck for a while before I found that the word select is case sensitive !

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

qmacro — Mon, 22 Jul 2024 14:53:54 GMT

Thanks for digging in! Yes, SELECT is, well, upper case. Working thru this stuff helps cement those small but important details for us 👍

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

Alpesa1990 — Mon, 22 Jul 2024 15:58:09 GMT

My Submission for task 7.

Another funny task!

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

qmacro — Mon, 22 Jul 2024 16:29:20 GMT

🎉

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

M-K — Mon, 22 Jul 2024 20:26:27 GMT

Here's my submission. That was a great learning experience.

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

qmacro — Tue, 23 Jul 2024 05:20:52 GMT

That's great to hear, thanks!

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

Liyon_SV — Tue, 23 Jul 2024 05:42:00 GMT

Phew, finally caught up!

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

mxmw — Tue, 23 Jul 2024 11:35:28 GMT

Im running into an issue and getting a little stuck. Do you have any tips?

If I test the developed services with cds watch 'local' in BAS then I get a succesfull answer.

When I deploy to cloud foundry I get an sqlite error that the CompanyName column of Suppliers is not present.

If I expand the standard Products odata to CompanyName then it does work...

So it seems that sqlite is not putting up with my SELECT statement in my function.

Any idea?

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

sudarshan_b — Tue, 23 Jul 2024 11:58:00 GMT

Another exciting task filled with lots of learning. This time CDS REPL was my buddy, such an awesome tool it is.

Here's my submission for the task -

Thank you.

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

MioYasutake — Tue, 23 Jul 2024 20:57:20 GMT

My submission for task 7.

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

MioYasutake — Tue, 23 Jul 2024 21:17:27 GMT

While implementing an event handler, I noticed that requests to existing entities such as Products and Suppliers, stopped working with a message "Service "northbreeze" has no handler for "READ northbreeze.Products"." This occurs as soon as I add the srv/main.js file.

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

cguttikonda24 — Wed, 24 Jul 2024 04:16:14 GMT

Hello DJ @qmacro,

Tried the repl to build the query and it was fun. Never had to set the debugger to dig into any error.

REPL is a great find.

{ ID: "f40ee4c1-8b95-4591-a0cc-4590cebb96ec", task: "northbreeze-productinfo", result: "PASS", createdAt: "2024-07-23T09:49:27.657Z", createdBy: "anonymous", modifiedAt: "2024-07-23T09:49:27.657Z", modifiedBy: "anonymous", serviceurl: "https://northbreeze-main.cfapps.us10-001.hana.ondemand.com/odata/v4/northbreeze", communityid: "cguttikonda24" } ]

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

qmacro — Wed, 24 Jul 2024 06:41:18 GMT

Excellent!

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

qmacro — Wed, 24 Jul 2024 06:41:54 GMT

Super, that's great to hear!

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

qmacro — Wed, 24 Jul 2024 06:42:18 GMT

Good work!

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

qmacro — Wed, 24 Jul 2024 08:42:20 GMT

BTW are you still stuck? Let us know, and if so, also provide a bit more info on what you were doing when you got the "Expected ..." error. Good luck!

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

mxmw — Wed, 24 Jul 2024 08:45:07 GMT

I cleaned my code and I changed my setup for CQL and it started working 🎉

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

qmacro — Wed, 24 Jul 2024 08:52:35 GMT

Hi there - can you supply more info. When does the sqlite error occur, what are you requesting? It's hard to guess what the problem might be without this sort of info. Is the service (in CF) available publicly, and if so, what is the URL (so we can check)? Cheers!

Re: Task 7 - Using CQL in an unbound function implementation (July Developer Challenge - "Rever

qmacro — Wed, 24 Jul 2024 09:05:28 GMT

Excellent, well done!