Autogenerated ID in CAP

[balbino_soaresferreirafil]

Hi guys, I would like to know the best aproach and recomendation to create a autogenerated ID in a entity.

entity Courses: managed{
key ID: Integer;
name: localized String;
}

I would not like to use UUID, because I want a ID more simple for users identify. Is there a way to create a sequence in definition? Or I need to do this in the custom logic of the service?

[frasie]

Hi,

I have implemented auto increment id's as follows

  async function getNextId(entity){
    const result = await SELECT.one(entity).orderBy({id: 'desc'})
    return result ? result.id + 1 : 1
  }

  srv.before ('CREATE','Teams', async context => {  
    context.data.id = context.data.id || await getNextId(Teams)
  })

If no id was provided it will simply take the max. id + 1.

Regards,
Frank

[avula913]

Hi,

We have implemented service.before handler to generate ID based on the maximum number of a field and implementing by 1 on maxID.

This is working good for the single post request. But when we are trying through the sap UI5 with the batch request it is failing.

When are adding more than 2 entries and saving the entries. It is throwing an error as entry already exists.

Please help me how to handle this use case

[frasie]

Hi Avula,

David Kunz from CAP development recommended to use SELECT.forUpdate() to avoid this race condition. Never tried it myself yet.

[qmacro]

Hi - I'm going to stick my neck out here and say that whatever the 'best' approach is, it will involve some logic on the server side to generate the 'next' IDs in sequence, while avoiding clashes and being fast enough, also while working across a distributed environment if you're deploying as a cloud native application too. In other words, it makes me remember how the number ranges work on the R/3 ABAP stack architecture, and what's involved there.

It's very likely that you'll need some sort of persistence at the database level to make this work too.

The UUIDs may be damn ugly but they are also beautiful in a different way 😉

[mvoros]

So does this mean that there is no standard way of doing this right now? Is this at least planned? Many of the business applications have a requirement to provide some user friendly IDs for object.

I also don't understand your comment. Of course it needs to be done in backend. I would guess that almost every relational database supports auto increment. At least this seems to be true for SQLite, Postgres and HANA.

[Shibaji]

If you have the ID as UUID then you need not to pass ID explicitly. CAP will generate the ID as new GUID.

Example: //Note cuid aspect of sap/cds/common automatically adds UUID

namespace com.sap.orderprocess.app;

using { cuid } from '@sap/cds/common';
entity SalesOrderFlow: cuid{

SOID: String;

WFID: String;

Status: String;

}

HTTP POST

<host>/order-workflow/SOFlow

--header

Content-Type application/json

--body

{ "SOID": "S00002", "WFID": "W12346", "Status": "Started" }

Response:

{

"@odata.context": "$metadata#SOFlow/$entity",

"ID": "acb8765a-3239-4d32-b5ec-acbb7a4133e4",

"SOID": "S00002",

"WFID": "W12346",

"Status": "Started"

}