CAP - Auto-incremental IDs / numbering, use of hdbsequence/hdbtrigger/hdbsynonym

[ssimsekler]

Hello

It seems CAP model does not yet offer a streamlined numbering capability like ABAP (number objects) or HDI (hdbsequence & hdbtrigger).

It seems it is possible to use HDI. However, the limited examples I found seem to include schemas/namespaces whereas schema management is hidden in CAP and I am not sure how to refer the tables that CAP manages. So, I am not sure how HDI and CAP can be implemented together. For example, given the following entity definition:

namespace example.cloud.application;

entity BPartners : managed, cuid {
  seqID : Integer;
  name: String;
}

1. What should the .hdbsequence file content look like if it needs to (e.g. for reset_by) refer to the table that is linked to the above entity definition?

2. How do we retrieve nextval/currentval from the hdbsequence in Node.js?

3. Because the number is ideally to be increased at the time of DB save, may the event handlers at service level be too early to increase the counter and assign the ID, e.g. on .before ('CREATE'...)? If yes and if .hdbtrigger & .hdbsynonym need to be used, how should their contents look like?

Kind Regards

[lbreddemann]

The recommendation in CAP is to use GUIDs instead, as these don't require any synchronization. E.g. you never will have to "clear up" used IDs with GUIDs when moving code and (test)-data between systems.

This also steers away from the misunderstanding that surrogate-IDs (like the automatically created seqID) have any meaning beyond their uniqueness.

There must not be an implied order or "uninterrupted streak" of values in a surrogate-ID. If it is, then this ID has application meaning and needs to be handled differently.

[ssimsekler]

Hi lars.breddemann

Thanks for your comment. We already use GUIDs and from the technical aspect I love them. But, as you know business would need mnemonic/human readable IDs. For example, when customers call, they cannot ask their account GUID; rather, they will ask their account ID.

I believe this is a significant requirement that needs to be supported. So, do you have any suggestions if we have to do this?

[ssimsekler · Accepted solution]

I'd accept Jhodel's answer, but it may be misleading as actually the actual solution is in the comments and you need to "Show all" to see the full discussion and the solution. So, under Jhodel's answer, click "Show all" and check out the comments to see the solution perspective.

Also, we had a nice discussion around this under Jhodel's blog here (which actually provides a structured content for a solution) with Lars and Jhodel, where I was insisting on human readable IDs and provided justification. You can go and have a look there, too.

Thanks, Jhodel.

[lbreddemann]

The suggestion for meaningful IDs is to implement your own application level ID generator. You’re right: the application users often need meaningful IDs.

Often, those IDs come with built-in semantics. Think area-code in phone numbers, or date of birth digit in social-security numbers, etc.

Those IDs actually encode additional information that are not covered by a (semantically) simple database sequence.

[ssimsekler]

Hi lars.breddemann
For the current use case I am working on, it has to be a numeric value, say 8 digits. Generating unique IDs with semantics, to me, is much more challenging then having auto-incremental IDs. All said, we can have our application level ID generator; but, number objects in ABAP and hdbsequence in HDI make developers' lives easier.

Because it seems HDI may help with a combination of hdbsequence, hdbsynonym and hdbtrigger files; can you shed any light on that aspect in terms of the questions I asked in my original question? I remember reading an answer where someone achieved this; but, my questions were specifically around the part how I can bring HDI and CAP together, e.g. around HDI referring to tables generated by CAP and how to access hdbsequence in CAP.

[klaus_kopecz]

Hi lbreddemann

Generating unique IDs with semantics on application level is not easy for cloud native apps. Those apps run with multiple instances for horizontal scaling and should be stateless. You need a central, stateful service for doing this. That‘s why a database can be good choice.

serdar.simsekler : What about deploying your own hdbprocedure for generating a unique semantic key and using UUIDs on CAP level for primary keys at the same time?

[ssimsekler]

Hi klaus.kopecz

If you mean using HDI objects, that's what I am after.

It seems HDI may help with a combination of hdbsequence, hdbsynonym and hdbtrigger files. I am after someone shedding some light on that aspect in terms of the questions I asked in my original question?

I found isaiasbarroso 's question and he mentions he managed to solve it. But the post does not have the details on the file contents and how he achieved the schema/namespace side of the things.

That's the reason why my questions are specifically around the part how I can bring HDI and CAP together, e.g. around HDI referring to tables generated by CAP and how to access hdbsequence in CAP.

[lbreddemann]

Ok, I didn't say it would be "easy", whatever that means.

To get meaningful IDs, a central synchronization is not always required to be part of the system. Where this becomes a necessity is when this "ID" should be arbitrary/general in some form.

But let's go with that approach: so you need a sort of central, stateful ID dispenser, that ensures not to give out the same ID multiple times. That's something a service could do. Just as there is a service providing the user authentication.

Does that mean, you this has to be implemented directly on DB level? Not at all. Just look at the ABAP number ranges service.

Coming back to the original question: I haven't done this, but based on https://cap.cloud.sap/docs/advanced/hana/ it seems to me that one can create an HDI container in XSA with .hdbtable, .hdbview, etc. definitions that then can be matched with CDS definition.

So it might be an option to use .hdbsequence to create a sequence in such a schema, and use it in your ID generator procedure.

[klaus_kopecz]

Hi serdar.simsekler

Maybe this helps?

https://cap.cloud.sap/docs/advanced/hana/

[jhodel18]

Hi Serdar,

I have the same answer as with Lars. You should be using GUID or UUID all the time as it helps manage your business object seamlessly in CAP, and it is also the defacto standard for Association/Navigation between entities. Going against this will only bring you issues. However, I do believe that there are very few cases that an external-facing number sequence is still warranted -- but i guess your case doesn't fall into this category.

[ssimsekler]

Hi jhodel18

Thanks for your response. You are right, we need to implement auto-incremental IDs for our use case. We already have GUIDs and we need the user friendly IDs.

[jhodel18]

Hi Serdar,

After conducting some POC, I found out that the solution to this is very simple:

- You just need to create your HDB Sequence file inside the folder db > src > RunningNumber.hdbsequence:

sequence "RunningNumber" start with 1 MAXVALUE 2999999999

- Generate the next number from your custom handler js file:

const db = cds.connect.to("db");
let number = await db.run(`SELECT "RunningNumber".NEXTVAL FROM DUMMY`);

But you probably need to adjust your logic for a scenario when you are using SQLite DB instead of HANA DB. I tested using SQLite DB and the above solution starts to fall apart.

I will dig in some more on this, but I hope the above solution will actually solve your requirements. Cheers!

[ssimsekler]

Hi jhodel18

Seems we were progressing in the same track. It costed me quite sometime to figure that I don't need a synonym for DUMMY. I wish I had seen your comment earlier. Not sure why the synonym is not needed.

For the sequence definition, I did it like this to ensure the numbering correctly initialises in case of a reset:

SEQUENCE "EXAMPLEBPSEQID" START WITH 2100000200 MAXVALUE 2999999999
RESET BY SELECT IFNULL(MAX(SEQID), 2100000200) + 1 FROM "EXAMPLE_CLOUD_APPLICATION_BPARTNERS"

For compatibility with sqlite, I was hoping to find a way todo the number generation via hdbtrigger. But, I cannot find documentation for it. This way, the app would not deal with number assignment in production. For testing side with sqlite, maybe it can be handled with a workaround in the app code.

Do you have any idea for such hdbtrigger implementation?

[jhodel18]

Hi Serdar,

You don't need the synonym because the object is created in the same HDI container.

My goal is to keep the solution as simple as possible so I just tried doing bare-bones implementation of sequence.

I would go for the simplest solution also for SQLite sequence because I'm not using SQLite for anything other than local testing and in-memory usage. So I would do something like below:

let counter = 0;

function getNextNumber(sequence) {
    let nextnumber = 0;
    const db = cds.connect.to("db");
    switch (db.kind) {
        case "hana":
            let number = await db.run(`SELECT ${sequence}.NEXTVAL FROM DUMMY`);
            nextnumber = number[sequence];
            break;
        case "sql":
            counter += counter;
            break;
        default:
            throw new Error("Unsupported DB kind");
    }
    return nextnumber;
}

I won't go as far as setting up db trigger. I'm too lazy to do that. 😄

PS: please turn on your notifications so that you can see my responses. 😄

[ssimsekler]

Hi jhodel18

Yes, that's what I meant. But, the counter in runtime wouldn't necessarily work for persistent sqlite. It needs to read the maximum sequence ID in the table and increase it by one. That would be much more consistent. In case you are not feeling lazy 🙂

[jhodel18]

Hi Serdar,

Even if you need to use persistent SQLite, you could enhance the switch statement I gave to do the query for the max last number. Which also doesn’t need to use triggers.

For me, I only use persistent SQLite whenever I want to investigate the data in the DB or I want to see the generated database objects.

[jhodel18]

Hi Serdar,

I felt like I need to write a blog about this topic. Check out my complete implementation of the getNextNumber logic in here:

https://blogs.sap.com/2020/07/04/cap-using-hana-db-sequence/

[sreehari_vpillai]

I am working on a project to migrate an entire application built on another architecture . Which has close to 50 DB tables interrelated with a parent id , pk id relationship ( integers , auto generated keys ) . I would have used a DML NEXTVAL statement if the CAPM framework let me do so . If not, I have no option other than to jump a sequence and generate ids . I cannot replace numerical ids with guid/uuid , reasons being

1 - Legacy data is already integers - I need the data migration to be smooth .

2 - Reports built relying on these fields

3 - these numbers mean something to the users.

BTW , I loved your other blog where you explained jumping the sequence within CREATE exit .

I would prefer to use uuid autogenerated by CAP framework , if the app is built from scratch -

Sree