Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

How to share tables across different CAP projects

MioYasutake
Active Contributor
‎2021 Oct 03 2:19 AM
17,435

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


  • Remove a few lines before the first curly bracket and make it a valid JSON file

  • Remove the outmost {"credentials": ...}

  • Tag the service as hana by adding the following property to JSON
    "tags":  "hana" 


{

	"certificate": "-----BEGIN CERTIFICATE-----certificate-----END CERTIFICATE-----",

        "database_id": "2f4af70e-8103-40fd-ab04-e0224a420803",

	"driver": "com.sap.db.jdbc.Driver",

	"hdi_password": "password",

	"hdi_user": "8C0524334BA3449487CF430CCCF279F4_2CPDC6Q1YEW5CA32U4OXI923P_DT",

	"host": "6f4ade47-353e-4a67-bbb0-431cba244981.hana.trial-eu10.hanacloud.ondemand.com",

	"password": "password",

	"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": "8C0524334BA3449487CF430CCCF279F4_2CPDC6Q1YEW5CA32U4OXI923P_RT",

	"tags": "hana"

}


  • Switch to dev2 space and create a user provided service.


cf create-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



 
68 Comments
Labels in this area
Popular Blog Posts