CAP - @sql.append for different sql dialects - Sequence

[jasszz]

Hi,

for a project that uses a sqlite3 db locally and a hana db in production. We are using https://cap.cloud.sap to create the database table.

I would like to create display an external facing ID for the end-users that is easier to read then a UUID. It would be easy to create such a "displayId" as an Integer and use AUTOINCREMENT for sqlite or and "IDENTITY column" in hana as described here: https://blogs.sap.com/2014/06/04/quick-note-on-identity-column-in-sap-hana/

But as far as I know CAP doesn't support the AUTOINCREMENT keyword.

I found this blogpost which does more or less what I need but the solution feels a bit complicated:

https://cap.cloud.sap/docs/guides/databases?q=%40sql.append#mapping-cds-to-sql

Is it possible to use the @sql.append and change the string that is appended to the table according to the current database dialect?

For example:

if(db.type === 'hana'){
  define type Autoincrement: Integer @sql.append : 'GENERATED BY DEFAULT AS IDENTITY';
} else {
  define type Autoincrement: Integer @sql.append : 'AUTOINCREMENT';
}
entity User: {
  cuiddisplayId : Autoincrement;
  description : String;
  name : String;
}
  

[hjb]

I think switching the test environments can be achieved with hybrid testing, but my knowledge here is limited. Maybe christian.georgi can help out.

[hjb]

hm, I wouldn't try to "extend" the model. In fact "displayId" is part of the core model, only the annotations vary

Why don't you just:

annotate db.User with { displayId @sql.append 'AUTOINCREMENT'; };

and

annotate db.User with { displayId @sql.append 'GENERATED BY DEFAULT AS IDENTITY'; };

in the backend specific files?

[jasszz]

Thank you for your reply!

Using annotate makes the code easier to read 🙂

I can now compile sql file for the specific database types with

cds compile .\db\hana\hanaModel.cds -2 sql

or

cds compile .\db\sql\sqlModel.cds -2 sql

And they will contain

displayId INTEGER GENERATED BY DEFAULT AS IDENTITY,

or

displayId INTEGER AUTOINCREMENT,

Are the commands above how you imagined it to work?Or is there a way to add logic to the cds files or any other part of the programm, that will select the correct folder according to the database?This is how I changed the files:

generalModel.cds

namespace db;

entity User: {
  description : String;
  name : String;
}

hanaModel.cds

namespace db.hana;
using db from '../data-model';

annotate db.HierarchyNodes with { displayId @sql.append : 'GENERATED BY DEFAULT AS IDENTITY'; };

sqlModel.cds

namespace db.sqlite;
using db from '../data-model';

annotate db.HierarchyNodes with { displayId @sql.append : 'AUTOINCREMENT' };

[chgeo]

The above configuration can be activated using profiles. All cds commands know a `--profile <name>` switch that let you activate one. Using this mechanism, you can 'activate' different cds files according to the configuration/environment.

[hjb]

Hi Jonas,

the idea is to keep DB specific parts separate in source files for each DB backend. Well hidden in the capire documentation there is a very nice code example that illustrates this approach by using different DB functions for SQLite and HANA. The same setup can of course be used to separate annotations.

Please read https://cap.cloud.sap/docs/guides/databases#sqlite-and-hana-functions and here especially the link to https://github.com/SAP-samples/cloud-cap-samples/commit/65c8c82f745e0097fab6ca8164a2ede8400da803.

HTH, Hans-Joachim

[jasszz]

Thank you for all the helpful information Hans-Joachim!

I tried to apply the code to my implementation but it doesn't seem to work at the moment. It would be great if can help me to correct it 🙂

I cannot find a way how to dynamically use the hana/hanaModel.cds or the sqlite/sqlModel.cds.
I ran it with "cds watch". Do I need to pass the stage (development/production) somehow in the cds command?

Let me give you some more insights into the code.
I currently get the error message "Element or variable “displayId” has not been found (in entity: "service.Users"/element: "displayId" "

project structure:

- db
- generalModel.cds
- hana
- hanaModel.cds
- sqlite
- sqlModel.cds
- srv
- service.cds

- package.json

file content

generalModel.cds

namespace db;

entity User: {
  description : String;
  name : String;
}

hanaModel.cds

namespace db.hana;
using db from '../data-model';

extend db.User with {
    displayId : Integer @sql.append : 'GENERATED BY DEFAULT AS IDENTITY';
}

sqlModel.cds

namespace db.sqlite;

using db from '../data-model';<br><br>extend db.User with {
    displayId : Integer @sql.append : 'AUTOINCREMENT';
}

service.cds

using db from '../db/generalModel';

service UserService {
   entity Users as projection on db.Users;
}

package.json

"cds": {
    "build": {
        "target": "."
    },
    "requires": {
        "db": {
            "kind": "sql",
            "[development]": {
                "model": "db/sqlite"
            },
            "[production]": {
                "model": "db/hana"
            },
            "credentials": {
                "database": "sql.db"
            }
        }

    },
    "odata": {
        "version": "v4"
    },
    "hana": {
        "deploy-format": "hdbtable"
},