Skip to Content
Technical Articles
Author's profile photo Mio Yasutake

How to share tables across different CAP projects

Introduction

The purpose of this blog is to demonstrate several patterns of table sharing across different CAP projects. I’ve been using CAP for about a year, but I’m still new to HANA db, so comments and suggestions are appreciated!

 

Updates (2021/10/15)

  • Scenario 3 and 4
    • .hdbsynonym requires only synonym name, because the actual configuration is taken from .hdbsynonymconfig.
    • use SERVICE_REPLACEMENTS in mta.yaml so that we don’t have to specify the actual service name in .hdbsynonymconfig and .hdbgrants files. – thanks to the advice by Dinu PAVITHRAN
    • it is not required to bind the reused HDI container (or user provided service) to srv module.
  • Scenario4
    • use the credentials of reused HDI container for the user provided service, so no need to create a database user. – again, thanks to Dinu PAVITHRAN

 

Scenarios

I’ve come up with the following scenarios.
Each pale blue box represents a CAP project, and “Customers” in the left most box is the entity to be reused.

* Keep in mind that these scenarios are purely for learning purpose. There are cases where consuming OData service is more appropriate than accessing database directly.

  1. A project sharing the same HDI container and namespace with the reused entity
  2. A project sharing the same HDI container but using different namespace from the reused entity
  3. A project using a different HDI container from the reused entity
  4. A project in a different space from the reused entity

 

Summary

The matrix below shows an overview of required artifacts per each scenario.

In the following sections, I’ll show the settings of each project. These projects are developed using HANA Cloud trial. The entire code is available at GitHub repository below.

https://github.com/miyasuta/cap-cross-container

After you’ve completed the settings, you can access reused entity from your OData service, either with /Customers or /<mainenity>?$expand=customer endpoints.

 

Scenario 1

When your project is sharing the same HDI container and namespace with the reused entity, you need to:

  • Annotate the reused entity with @cds.persistence.exists.
namespace master.partners;

entity Vendors {
    key ID: Integer;
    name: String;
    customer: Association to Customers
}

@cds.persistence.exists
entity Customers {
    key ID: Integer;
    name: String
}
  • Use the same HDI container service instance as the reused entity. In the mta.yaml, specify resource type as org.cloudfoundry.existing-service.
resources:
 - name: master-db
   type: org.cloudfoundry.existing-service
   parameters:
     service-name: master-db   

 

Scenario 2

When your project is sharing the same HDI container but using different namespace from the reused entity, you need to:

  • Annotate the reused entity with @cds.persistence.exists.
namespace master.orgs;

entity Plants {
    key ID: Integer;
    name: String;
    customer: Association to Customers
}

@cds.persistence.exists
entity Customers {
    key ID: Integer;
    name: String
}
  • Create .hdbsynonym file to fit the reused entity’s name to your namespace.

 

{
    "MASTER_ORGS_CUSTOMERS": {
        "target": {
            "object": "MASTER_PARTNERS_CUSTOMERS"
        }
    }
}

 

  • use the same HDI container service instance as the reused entity (same as the scenario 1).

 

Scenario 3

When your project is using a different HDI container from the reused entity, you need to:

  • First in the reused project, create roles to allow access from external containers.

 

*File name can be any name, only the extension matters.

MASTER_PARTNERS_EXTERNAL_ACCESS.hdbrole

{
    "role": {
        "name": "MASTER_PARTNERS_EXTERNAL_ACCESS",
        "object_privileges": [
            { 
                "name":"MASTER_PARTNERS_CUSTOMERS", 
                "type":"TABLE", 
                "privileges":[ "SELECT" ], 
                "privileges_with_grant_option":[] 
            }                          
        ]
    }
}

MASTER_PARTNERS_EXTERNAL_ACCESS_G.hdbrole

{
    "role": {
        "name": "MASTER_PARTNERS_EXTERNAL_ACCESS_G#",
        "object_privileges": [
            { 
                "name":"MASTER_PARTNERS_CUSTOMERS", 
                "type":"TABLE", 
                "privileges":[], 
                "privileges_with_grant_option":["SELECT"]
            }                          
        ]
    }
}

These files look similar, only difference being that the first one has the “privileges” for “SELECT”, and the second one has “privileges_with_grant_option” for “SELECT”. Later, the first role will be assigned to an application user of a newly created HDI container and the second role to an object owner of the same. Once you’ve made above changes, deploy the project to the Cloud Foundry.

 

Next steps will be performed in the “sales” project.

 

  • Annotate the reused entity with @cds.persistence.exists.
namespace sales;

entity Orders {
    key ID: Integer;
    amount: Integer;
    customer: Association to Customers;
}

@cds.persistence.exists
entity Customers {
    key ID: Integer;
    name: String;
}

 

  • Create .hdbsynonym file to fit the reused entity’s name to your namespace.
{
    "SALES_CUSTOMERS": {}
}

This file is almost empty, as the actual configuration is coming from .hdbsynonymconfig. This file needs to be placed under db/cfg as below. The artifacts in db/cfg are processed first in deployment time.

 

  • Create .hdbsynonymconfig file to supply schema name.
{
    "SALES_CUSTOMERS": {
        "target": {
            "object": "MASTER_PARTNERS_CUSTOMERS",
            "schema.configure": "master-db-hdi/schema"
        }
    }
}

Schema name will be taken from “schema” property of “master-db-hdi” service instance.

* “master-db-hdi” is an alias for the HDI container “master-db”, which will be defined in SERVICE_REPLACEMENTS in mta.yaml. Thanks to this SERVICE_REPLACEMENTS concept, we don’t have to specify actual service instance names in our development artifacts.

 

  • Create .hdbgrants file to assign roles to HDI container users.
{
    "master-db-hdi": {
        "object_owner": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS_G#"
            ]
        },
        "application_user": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS"
            ]
        }
    }
}

master-db-hdi” at the top points to the reused HDI container service instance, which is the grantor of these privileges.

 

  • Add the HDI container service instance of the reused entity (master-db) to mta.yaml.
 # --------------------- SERVER MODULE ------------------------
 - name: sales-srv
 # ------------------------------------------------------------
   type: nodejs
   path: gen/srv
   parameters:
     buildpack: nodejs_buildpack
   requires:
    # Resources extracted from CAP configuration
    - name: sales-db 
   provides:
    - name: srv-api      # required by consumers of CAP services (e.g. approuter)
      properties:
        srv-url: ${default-url}

 # -------------------- SIDECAR MODULE ------------------------
 - name: sales-db-deployer
 # ------------------------------------------------------------
   type: hdb
   path: gen/db  
   parameters:
     buildpack: nodejs_buildpack
   requires:
    # 'hana' and 'xsuaa' resources extracted from CAP configuration
    - name: sales-db
      properties: 
        TARGET_CONTAINER: ~{hdi-service-name}       
    - name: master-db
      group: SERVICE_REPLACEMENTS
      properties:
        key: master-db-hdi
        service: ~{master-db-hdi}   


resources:
 # services extracted from CAP configuration
 # 'service-plan' can be configured via 'cds.requires.<name>.vcap.plan'
# ------------------------------------------------------------
 - name: sales-db
# ------------------------------------------------------------
   type: com.sap.xs.hdi-container
   parameters:
     service: hana  # or 'hanatrial' on trial landscapes
     service-plan: hdi-shared
   properties:
     hdi-service-name: ${service-name}

 - name: master-db
   type: org.cloudfoundry.existing-service
   parameters:
     service-name: master-db   
   properties:
      master-db-hdi: ${service-name}          

Here, two HDI container are used by srv module and db deployer module each. To let the deployer know which container to deploy the project’s own artifacts, property TARGET_CONTAINER is specified for sales-db.

Also, note that the alias for “master-db” is defined in the SERVICE _REPLACEMENTS section as below.

    - name: master-db
      group: SERVICE_REPLACEMENTS
      properties:
        key: master-db-hdi
        service: ~{master-db-hdi}   

 

Scenario 4

When your project is in a different space from the HDI container of the reused entity, you need to create a user provided service which contains a database user who has the privileges to grant access to reused entities.

For this, we will first create a service key for master-db service instance, and based on the service key we will create a user provided service.

 

  • Create a service key for master-db.
cf create-service-key master-db grantor-key
  • In the accounting project directory, dump the key into a file.
cf service-key master-db grantor-key > grantor-key.json
  • Edit the file (remove a few lines before { ) and make it a valid json file.
  • Tag the service as hana by adding the following property to json
    “tags”: [ “hana” ]
{
	"certificate": "-----BEGIN CERTIFICATE-----xxxx-----END CERTIFICATE-----",
	"driver": "com.sap.db.jdbc.Driver",
	"hdi_password": "xxxx",
	"hdi_user": "3xxxx",
	"host": "6f4ade47-353e-4a67-bbb0-431cba244981.hana.trial-eu10.hanacloud.ondemand.com",
	"password": "xxxx",
	"port": "443",
	"schema": "349472F3D2FB4B6BBBF430B47451A7B2",
	"url": "jdbc:sap://6f4ade47-353e-4a67-bbb0-431cba244981.hana.trial-eu10.hanacloud.ondemand.com:443?encrypt=true\u0026validateCertificate=true\u0026currentschema=349472F3D2FB4B6BBBF430B47451A7B2",
	"user": "xxxx",
	"tags": ["hana"]
}
  • Switch to dev2 space and create a user provided service.
cf crete-user-provided-service ups-master-db -p grantor-key.json

 

The remaining steps are similar to the scenario 3.

  • Annotate the reused entity with @cds.persistence.exists
namespace accounting;

entity Invoices {
    key ID: Integer;
    amount: Integer;
    customer: Association to Customers    
}

@cds.persistence.exists
entity Customers {
    key ID: Integer;
    name: String;
}

 

  • Create .hdbsynonym file to fit the reused entity’s name to your namespace.
{
    "ACCOUNTING_CUSTOMERS": {}
}

 

  • Create .hdbsynonymconfig file to supply schema name. This file should to be placed under db/cfg.
{
    "ACCOUNTING_CUSTOMERS": {
        "target": {
            "object": "MASTER_PARTNERS_CUSTOMERS",
            "schema.configure": "cross-schema-ups/schema"
        }
    }
}

Schema name will be taken from “schema” property of “cross-schema-ups” service instance, which is the user provided service we created in the previous step.

* “cross-schema-ups” is an alias for the actual ups name.

 

  • Create .hdbgrants file to assign roles to HDI container users.
{
    "cross-schema-ups": {
        "object_owner": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS_G#"
            ]
        },
        "application_user": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS"
            ]
        }
    }
}

 

  • Add the user provided service instance (ups-master-db) to mta.yaml.
# --------------------- SERVER MODULE ------------------------
 - name: accounting-srv
# ------------------------------------------------------------
   type: nodejs
   path: gen/srv
   parameters:
     buildpack: nodejs_buildpack
     memory: 256M
     disk-quota: 1024M      
   requires:
    # Resources extracted from CAP configuration
    - name: accounting-db
   provides:
    - name: srv-api      # required by consumers of CAP services (e.g. approuter)
      properties:
        srv-url: ${default-url}

 # -------------------- SIDECAR MODULE ------------------------
 - name: accounting-db-deployer
 # ------------------------------------------------------------
   type: hdb
   path: gen/db  
   parameters:
     buildpack: nodejs_buildpack     
   requires:
    # 'hana' and 'xsuaa' resources extracted from CAP configuration
    - name: accounting-db
      properties: 
        TARGET_CONTAINER: ~{hdi-service-name}     
    - name: ups-master-db
      group: SERVICE_REPLACEMENTS
      properties:
        key: cross-schema-ups
        service: ~{cross-schema-ups}

resources:
 # services extracted from CAP configuration
 # 'service-plan' can be configured via 'cds.requires.<name>.vcap.plan'
# ------------------------------------------------------------
 - name: accounting-db
# ------------------------------------------------------------
   type: com.sap.xs.hdi-container
   parameters:
     service: hana  # or 'hanatrial' on trial landscapes
     service-plan: hdi-shared
   properties:
     hdi-service-name: ${service-name}      

 - name: ups-master-db
   type: org.cloudfoundry.existing-service
   parameters:
      service-name: ups-master-db   
   properties:
      cross-schema-ups: ${service-name}

 

Conclusion

In this blog, I explained four patterns of table sharing across different CAP projects.

  • A project sharing the same HDI container and namespace with the reused entity
    • No special setting is required. Just use the same HDI container service instance as the reused entity.
  • A project sharing the same HDI container but using different namespace from the reused entity
    • .hdbsynonym is required to fit the reused entity’s namespace to your namespace.
  • A project using a different HDI container from the reused entity
    • .hdbsynonym is required to fit the reused entity’s namespace to your namespace.
    • .hdbsynonymconfig is required to supply schema name to the synonym.
    • .hdbgrants is required to grant HDI container users privileges to access reused entities.
  • A project in a different space from the reused entity
    • A user provided service is required to enable cross-space access.
    • .hdbsynonym is required to fit the reused entity’s namespace to your namespace.
    • .hdbsynonymconfig is required to supply schema name to the synonym.
    • .hdbgrants is required to assign HDI container users privileges to access reused entities.

References

YouTube

CAP document

SAP Help

 

Assigned tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Dinu PAVITHRAN
      Dinu PAVITHRAN

      Thanks for posting this lucid explanation.  A few suggestions for you to try out:

      It would be great if you could try out SERVICE_REPLACEMENTS[1] as an way to decouple the service names in hdbsynonymconfig  from the actual name of the service referenced.

      https://www.npmjs.com/package/@sap/hdi-deploy

      In scenario 4, you could try creating another key for the service instance. This key would have the users with the required permissions. This has the added advantage of being a documentation for this usage. It can be done easily and without DBADMIN user access. The user names are ugly though.

      Looking forward to you clear walkthrough of these too.

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Dinu PAVITHRAN,

      Thank you for your advice! I will try using SERVICE_REPLACEMENTS and update this post.

      I'd like to confirm the second piece of your advice.

      Does "creating another key for the service instance" means to create a service key for HDI container master-db in my case? If so, how can this key be referenced from a CAP project in a different space?

      Best regards,

      Mio

       

      Author's profile photo Dinu PAVITHRAN
      Dinu PAVITHRAN

      I dont think space matters if you give the credentials in user provided service. Please try the following:

      - Create a service key
      cf create-service-key master-db grantor-key

      - Dump the key into a file:
      cf service-key master-db grantor-key > grantor-key.json

      - Edit the file (remove a few lines before { ) and make it a valid json file.
      - Tag the service as hana by adding the following property to json
      "tags": [ "hana" ]

      - Switch space
      - Create user provided service
      cf crete-user-provided-service my-db-mine -p grantor-key.json

      That should do the trick.
      Using a json file makes commands simpler.

      PS: This blog is a good reference for syntax in mta for SERVICE_REPLACEMENTS, even though it is in the context of XSA and webide.

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Thank you for providing the steps. it worked!

      This is indeed a better way than to create a new DB user.

      I have updated my post.

       

      Author's profile photo Jason Scott
      Jason Scott

      Excellent post with good simple explanations of difficult concepts.

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Thank you so much for your encouraging comment!

      Author's profile photo smith brown
      smith brown

      Thanks for introducing that opportunity and methods about table sharing. It's a very guiding code and especially all scenarios very helpful. Regards: @smith

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Thank you for your comment! Glad to hear that this content was helpful to you.