This blog will enable you to create oData using SAP Cloud Application Programming model using an existing DB.
Let us talk a little more about this scenario. Assume that you have developed a Business Service (could be multi-tenant as well) to expose rest endpoints. This service is up & running and it is being used by consumers. This service is bound to a HANA database and service data is saved in DB (different schema in case of a multi tenant service).
Now, there is a requirement for you to also provide oData, which exposes the business service data. Cloud Application Programming model can be help you build the oData on top of the existing DB. You can reuse the DB service as well as the xsuaa service to create the oData.
Please note that we could also develop the UI with the rest endpoints, but this will definitely need some amount of coding. Whereas if you have an oData, creating the UI becomes very simple with the usage of predefined smart templates available in webIDE. In very few steps you will be able to bind the oData to the UI and run it without writing much code.
Please follow the below steps to create the oData from an existing db.
1. Create an SAP Cloud Platform Business Application from webIDE.
2. Edit the MTA file. In the resource section, you can provide the existing DB and xsuaa services. The service(srv) and db modules too should be bound to the existing DB and xsuaa. Please change the dependency in the require section of db and srv.
3. Create cds files corresponding to hdbcds file in webIDE for the oData. Please give the same namespace provided in the hdbcds file(existing). Tag the entity as @cds.persistence.exists. This will make sure that no table creation in done and the data is fetched from the existing db which was already created. The tables/schema would have already been created using hdbcds file when your Business Service was deployed.
It should have all the fields which you would like to expose in the oData. You can not have additional fields unless you add it in your hdcds file as well. This means that it is going to fetch the data from the db and it will not be able to map anything extra which is not defined in the db.
You must provide a different context name in the data-model.cds or else you will get an error for duplicate entity. Please be careful in providing the namings in data-model.cds and my-service.cds. I have prefixed ‘new’ wherever it is possible to use new names.
4. Edit the my-service.cds file to expose the entities you have defined in the data-model.cds.
5. Change the sql_mapping to hdbcds in the root level package.json. This is needed to generate the exact same names of the entities and tables which is present in hana (existing DB). By default, it is plain. In plain mode, it generates the names in all caps and there is no :: in the table names. But this will not work with existing DB.
6. Build cds for the project and check the csn.json (srv->src->resources->edmx). @cds.persistence.name should have the same name which is present in your db.
7. Build the project and deploy it the space where your database service is deployed. Only then it will be able reuse the existing db.
8. Once the application is deployed, you can access the oData from postman or browser. If the db is secured with oAuth2, you will need jwt to access the oData. This jwt is same as what you would use to access your service.
9. oData can be accessed using the url which is generated in the srv module after deployment along with the namespace you provided. It will look something like – https://abc-srv.cfapps.sap.hana.ondemand.com/odata/v2/com.sap.existingdb.ExistingContextName. You can then access the metadata as well as the list of entities exposed.
10. All the operations supported on oData will work on the generated oData from SAP Cloud Application Programming model e.g. $select, $filter, $top etc.
Bonus – You can check the db name in webIDe directly. You will need to enable the “SAP HANA database development tools” from the Tools->Preferences->Features.