Skip to Content
Technical Articles

Timesheet Management with CAP & Trello ⏱️ – Setup a Database and Service Module #2

Hello there!

Welcome back to the second hands-on blog of this “Timesheet Management with CAP & Trello ⏱️” blog series. In this blog “Setup a Database and Service Module #2” we will create our Database and Service Module in our MTA-Project using the CDS-Tools.

If you missed some other blogs of this series, you can find them here:

Timesheet Management with CAP & Trello ⏱️
Timesheet Management with CAP & Trello ⏱️ – Setup the IDE & MTA Project #1
Timesheet Management with CAP & Trello ⏱️ – Setup a Database and Service Module #2 (this blog)
Timesheet Management with CAP & Trello ⏱️ – Connect to Trello API’s via a Node.js Module #3
Timesheet Management with CAP & Trello ⏱️ – Build the Trello Timesheet HTML5 Module #4
Timesheet Management with CAP & Trello ⏱️ – Add a Fiori Launchpad Site Module #5

The GitHub Repository is available here:

Timesheet Management with CAP & Trello GitHub Repository

 

Introduction

Since we want to digitize our Timesheet maintenance in a real digital way (and not in spreadsheet files), we will need a place to store and access all this Timesheet data. We do not want to replicate our Trello boards and cards, so we consume them via a Node Module we will create in the upcoming blogs. The “Spent Hours” on certain Trello boards and cards will be stored in our database and will be exposed via a Service (srv) that exposes this data in an OData V4 service.

 

Use the CDS-Tools

We did not install the CDS-Tools in the first blog “Setup the IDE & MTA Project #1” for nothing. We just arrived at the point where we will consume them to add a Database and Service (ODataV4) Module to our MTA project.

Before we execute the first CDS command, we copy the content of the “package.json” file inside the root directory of our MTA project. We save this content since we will need it afterwards. Next we run the following “CDS” command inside our MTA project its root directory, to initialize the required modules (db & srv):

cds init

This will raise an error because a “package.json” file already exists:

As mentioned in the error, this file can be overwritten with the following command:

cds init -–force

This time the command succeeded successfully:

This has overridden our “package.json” file and content, and we add our “ui5” and “devDependencies” from our previous file to it. Our “package.json” file should look like this at the moment:

It also create 3 new directories in our MTA project, an “app”, “db” and “srv“ directory:

We delete the “app” directory since we already added a UI Module inside our MTA project and we will use the “db” folder for our database Module and the “srv” folder for the ODataV4 service.

 

Configure the Database Module

The “Spent Hours” and additional Timesheet information will be stored in a database inside our MTA project. We will store this data in a HANA database instead of an SQLite database.

For every “Spent Hour” we store in the database, we store the following fields:

Field Data type
ID (key) UUID
boardId String
cardId String
date DateTime
hours Decimal(4, 2)
status String
fullName String
comment String

Every record inside our database will have a unique identifier along with the Trello board and card id. This way we can retrieve “Spent Hours” of a Trello card by providing a board and card id. The time worked on such a Trello card (Trello card holds a task you have to execute) is stored in the hours field and takes place on a certain date, which is stored in the date fields.

Cards can be in different states, “Done”, “Ongoing” or “On hold” and are stored in our status field. Every “Spent Hour” is created by someone and that someone his full name is saved in the corresponding field. Either you did something really good or made a mess, you can tell the database about it in the comment field. 😝

Time to add this Database Module!

The first thing we want to do is to create a “data-model.cds” file inside our “db” directory.

This can be done via the GUI or by executing the following command on the terminal inside the “db” directory:

touch data-model.cds

Once the file has been created, we add the following configuration to it:

namespace TrelloTimesheetManagementCAP.db;

entity SpentHours {
    key ID       : UUID;
        boardId  : String;
        cardId   : String;
        date     : DateTime;
        hours    : Decimal(4, 2);
        status   : String;
        fullName : String;
        comment  : String;
}

With this content we define the namespace and fields we described earlier in this blog. The “UUID” of the “key” field is an automatically generated field that will serve the purpose of a unique identifier for every record in our table.

A little word about the “Decimal” data type:

In a CDS you have to know the meaning of the numbers, the first digit is the precision and the second is the scale.

Example: Decimal(4, 2);

Precision: the total count of digits that form the number, ignoring the dot of a decimal number.

Scale: the maximum number of digits allowed to the right of the decimal point.

 

Next we want to execute the following command in our root directory, to add HANA as kind of database to our MTA project:

cds add hana

This will add the following “cds” properties to the “package.json” file inside our root directory:

It also added the “src” folder and “.hdcofnig” file inside “db” folder containing all plugin information.

 

Configure the Service Module

As mentioned earlier this Service Module will expose an ODataV4 Service called “TimesheetManagementService”, containing an entity “SpentHours” to provide all the spent hours on Trello cards. This service will also be secured, so only authenticated users can access the service. The authentication process can take place since we will connect our service to the earlier created “XSUAA” service.

The first thing we want to do is to create a “cat-service.cds” file inside our “srv” directory.

This can be done via the GUI or by executing the following command on the terminal inside the “srv” directory:

touch cat-service.cds

Once the file has been created, we add the following configuration to it:

using TrelloTimesheetManagementCAP.db from '../db/data-model';
service TimesheetManagementService @(requires:'authenticated-user'){
  entity SpentHours @(restrict: [{ grant: '*', to: 'authenticated-user' }]) as projection on db.SpentHours;
}

This configuration imports and uses the database model and requires an authenticated scope to access the “TimesheetManagementService” ODataV4 service. It grants all operation rights on the “SpentHours” entity to authenticated users.

Install all the added required dependencies (@sap/cds, express, @sap/hana-client) inside the project:

npm i

Since we want to secure our ODataV4 Service, we tell the “cds” property its “requires” property inside our “package.json” file, that we want to use a “uaa” service of the kind “xsuaa”. We add the following configuration at the same level as the “db” property inside the “requires” property:

"uaa": {
    "kind": "xsuaa"
}

Your “cds” its “requires” property inside your “package.json” file should look like this:

 

Deploy the Modules

Now that we configured our Modules and told our MTA project that we want to use HANA as kind of database, and that we want to use an “xsuaa” service to secure our ODataV4 service. We can create a “hanatrial hdi-shared database” instance inside our Cloud Foundry Space by executing the following command inside our root directory:

cds deploy --to hana

Once the database instance creation has finished, we will see the following message on the terminal:

We can also verify this creation inside the SAP Cloud Platform Cloud Foundry Space under Service Instances:

This also created a “default-env.json” file inside your root directory, which contains the credentials to connect to your created database instance. During this deployment it also generated the tables and schemas so we can consume them via our service.

 

Test the Modules

Now that we have our database and service Module configured and deployed, we can connect to it. This can be done by executing the following “cds” command:

cds watch

This command starts the connection to the database and brings up the OData V4 Service. It can connect to the database, since a “default-env.json” file was added inside the root directory of the MTA project. Which holds the credentials to connect to the database instance that was deployed with the “cds deploy –to hana” command and which also created this “default-env.json” inside the root directory.

You can see that it connects to this database instance through the “default-env.json” file by check the terminal:

The Business Application Studio will also ask us if we want to expose this service, which we obviously want to do, so we press the “Expose and Open” button:

Next we provide a description for the service and we name it “srv”:

This will open the welcome page of our OData V4 service, where we can access our service and its entities as well as the metadata of our service:

Now when we try to access our “SpentHours” entity, by clicking the “SpentHours” link. An authentication pop-up will appear since we told our service to be secure and to grant all operation (CRUD) permissions to authenticated users only. Here we press the “Cancel” button to check if we have access to it:

Just like it should be, we get an error message which shows “Unauthorized”:

Now when we refresh the page and we leave the credentials fields empty continued by pressing “Login” we will see that we have access to the “SpentHours” entity:

 

Test the Modules via the Approuter

Like we saw in the beginning of this Blog Series, we want to access our resources via the Approuter in our MTA project. This means we have to make some small adjustments to our Approuter.

The first adjustment takes place inside the “xs-app.json” file. Here we will add a route to our “routes” array, so the Approuter can forward requests to this OData V4 Service/resource. Add the following route above your “html5-apps-repo-rt” route, this because the route-sources are regexes and are checked in chronologically:

{
    "source": "^/timesheetService/(.*)$",
    "target": "/timesheet-management/$1",
    "authenticationType": "xsuaa",
    "destination": "srv_api",
    "csrfProtection": false
}

Every request starting with “/timesheetService” will be forwarded to the destination “srv_api” and it will target the “timesheet-management” service and use the entity that is passed in the URL “$1”. Indeed, we did not define any destinations yet. Since this is an MTA service inside this project can be linked to each other via “default-urls”. Meaning we do not have to define the destinations using the destination service and do not have to add them inside the SAP Cloud platform.

But what we do have to do, is adding the destination inside our “default-env.json” file inside our “trello-cap-timesheetmanagement-approuter” directory. Add the following “destinations” property to this file, outside the “VCAP_SERVICES” property:

"destinations": [
    {
        "name": "srv_api",
        "url": "http://localhost:4004",
        "forwardAuthToken": true
    }
],

The structure of your file should look like this:

Inside this destinations we define the name “srv_api”, just like we used it in our “route” inside the “xs-app.json” file. We tell the destination to forward the authentication token to the http://localhost:4004 URL. We know that this port has to be “4004” since it is logged on the console when we start the service by executing the “cds watch” command.

Now we open 2 terminals, one to start the Approuter from its directory with “npm run start”, and another terminal to run the OData V4 Service from the root directory using the “cds watch” command. We both expose and name the started services. This will open-up our Approuter and OData V4 Service.

Now we will try to access our OData V4 Service via our Approuter by requesting it via the following URL Path:

/captrelloTimesheetManager/timesheetService/SpentHours

When we press cancel inside the authentication pop-up, we will get an “Unauthorized” error message.

But when we try to login with our SAP CP email address and password, we won’t succeed either. The authentication pop-up keeps appearing:

This is because we are still trying to login via a mock service, which is the default behavior. We have to tell our project that we want to use the “JWT” strategy (real authentication) to authenticate ourselves, instead of mocking the authentication. This can be done by adding the following configuration inside our root directory its “package.json” file:

"auth": {
    "passport": {
         "strategy": "JWT"
    }
}

This at the same level as the “requires” property:

If you prefer to keep this strategy separated from your “package.json” file, you can also place it inside the json-object inside your “.cdsrc.json” file in the root directory.

Now we restart the Approuter (make sure your OData V4 Service is still running too) and we see that we get the following error:

If we have a look at the terminal of our OData Service, we see that the reason for this error is a missing module, which raised the error (Cannot find module ‘@sap/xssec’):

Our service needs access to our environment variables and for that it needs this “xssec” module as well as the “xsenv” and “passport” module.

First, we stop our OData V4 service, and next we install these modules in the root directory of our project by executing the following command:

npm install passport @sap/xssec@^2 @sap/xsenv

Now try to run the service again by executing the “cds watch“ command:

cds watch

We will have an error again….

This time it will tell us that “no service matches xsuaa”. This is actually true, since we have our “xsuaa” service credentials available inside the “default-env.json” file of our Approuter, but not in the “default-env.json” file of our root directory.

So, copy the xsuaa property and its content from the “default-env.json” file of the Approuter into the “default-env.json” file of the root directory.

You can also bind it again via the command pallet, but that would be a little more copy pasting and adjusting. In this case you also have to make sure you select the local “xsuaa” instance we created earlier.

Now try to run the service again by executing the “cds watch“ command:

cds watch

Tis time your service started successfully:

Try to access the service again via the Approuter by requesting the following URL:

/captrelloTimesheetManager/timesheetService/SpentHours

You receive the expected result from your service, the “SpentHours” entity which is empty of course:

 

Deploy MTA Project

Now that we have our database, service, UI5-App and Approuter up and running, we are ready to deploy our Multi-Target-Application project with all its modules. But before we can do this we have to add our OData Service and Database Module to our “mta.yaml” file, the reason for that is that need to be build and deployed top during the deployment process of the MTA.

Rename your current “mta.yaml” file to “mta1.yaml”.

This because we will use the “cds add mta” command to generate an “mta.yaml” file with the service and database module preconfigured.

 

Execute to following command to generate the “mta.yaml” file:

cds add mta

As you can see this created a new “mta.yaml” file with the following content:

Now we are going to merge our “mta.yaml” file and the our “mta1.yaml” file to include all the modules and resources in a single “mta.yaml” file. I already prepared the content for the file below:

_schema-version: "3.2"
ID: Trello-CAP-TimesheetManagement
version: 0.0.1
parameters:
  enable-parallel-deployments: true
modules:
  - name: trello-cap-timesheetmanagement-approuter
    type: approuter.nodejs
    path: trello-cap-timesheetmanagement-approuter
    requires:
      - name: Trello-CAP-TimesheetManagement_html_repo_runtime
      - name: uaa_Trello-CAP-TimesheetManagement
      - name: srv_api
        group: destinations
        properties:
          forwardAuthToken: true
          name: srv_api
          url: "~{url}"
    parameters:
      disk-quota: 256M
      memory: 256M
  - name: Trello-CAP-TimesheetManagement_ui_deployer
    type: com.sap.application.content
    path: .
    requires:
      - name: Trello-CAP-TimesheetManagement_html_repo_host
        parameters:
          content-target: true
    build-parameters:
      build-result: resources
      requires:
        - artifacts:
            - TimesheetManager-content.zip
          name: TimesheetManager
          target-path: resources/
  - name: TimesheetManager
    type: html5
    path: TimesheetManager
    build-parameters:
      builder: custom
      commands:
        - npm run build
      supported-platforms: []
  - name: Trello-CAP-TimesheetManagement-srv
    type: nodejs
    path: gen/srv
    properties:
      EXIT: 1 # required by deploy.js task to terminate
    requires:
      # Resources extracted from CAP configuration
      - name: Trello-CAP-TimesheetManagement-db
      - name: uaa_Trello-CAP-TimesheetManagement
    provides:
      - name: srv_api
        properties:
          url: "${default-url}"
  - name: db
    type: hdb
    path: gen/db
    parameters:
      app-name: Trello-CAP-TimesheetManagement-db
    requires:
      # 'hana' and 'xsuaa' resources extracted from CAP configuration
      - name: Trello-CAP-TimesheetManagement-db
      - name: uaa_Trello-CAP-TimesheetManagement
resources:
  - name: Trello-CAP-TimesheetManagement_html_repo_runtime
    type: org.cloudfoundry.managed-service
    parameters:
      service: html5-apps-repo
      service-plan: app-runtime
  - name: Trello-CAP-TimesheetManagement_html_repo_host
    type: org.cloudfoundry.managed-service
    parameters:
      service: html5-apps-repo
      service-plan: app-host
  - name: uaa_Trello-CAP-TimesheetManagement
    type: org.cloudfoundry.managed-service
    parameters:
      path: ./xs-security.json
      service: xsuaa
      service-name: Trello-CAP-TimesheetManagement-xsuaa-service
      service-plan: application
  - name: Trello-CAP-TimesheetManagement-db
    type: com.sap.xs.hdi-container
    parameters:
      service: hana
      service-plan: hdi-shared
    properties:
      hdi-service-name: ${service-name}
build-parameters:
  before-all:
    - builder: custom
      commands:
        - npm install
        - npx cds build

I only made 4 adjustments inside the “mta.yaml” file.

  1. The “Trello-CAP-TimesheetManagement-srv” will not provide “srv-binding” but a “srv-api”, just like we named it in our Approuter files. These names need to be identical.
  2. I also changed the name of the “xsuaa” name in the “requires” section of the “db” and “srv” module to the one that we defined earlier in our resources. Note that we are using the none-local “xsuaa” service instance.
  3. I added the “srv_api” to the requires section of the Approuter so it can access this resource. This along with the required parameters to forward the authentication tokens to the correct generate URLs inside the MTA project.
  4. Inside the “srv” module under the provides section I changed the “srv-url” property to “url” and added double quotes to the value “${default-url}”.

The last adjustment I performed is the path to the “db” and “srv” module. I removed the “gen/” part in the path, because we will build our full project and deploy it from the “mta_archives” and not from the “gen” directory.

Time to build our MTA-project by executing the following command in the root directory of our project:

mbt build

This will build your MTA project into a single file called “Trello-CAP-TimesheetManagement_0.0.1” inside a directory called ”mta_archives”.

Now to deploy your MTA project, more in particular your “Trello-CAP-TimesheetManagement_0.0.1” you execute the following command with the “delete-services” parameter, to remove discontinued services and update the changed ones:

cf deploy mta_archives/Trello-CAP-TimesheetManagement_0.0.1.mtar --delete-services

Once your deployment process finished successfully you will see the following message (“Process finished”):

When you have a look at your “service instances” inside your Cloud Foundry space, you will see that all required instances are available. If a module required the “xsuaa” resource inside the “mta.yaml” file, it is passed as a “referencing application” for the “xsuaa” instance. Since the “db and “srv” module both require the “hanatrial” service they are also marked as “referencing applications” for this service instance:

 

Test the deployed MTA Project

Now it is time to try and access all our modules via the Approuter. For this we go back to our deployed applications inside the Cloud Foundry environment. You will see that the database and service application are deployed here as well. We open the Approuter again via the displayed URL:

If you see the UI5 application when you open the Approuter URL, your Approuter is still up and running and still configured correctly so far.

Now try to call your ODataV4 Service via the Approuter by calling the following path in the URL:

/timesheetService/SpentHours

If you see the following result:

You executed and configured all the steps correctly and are ready to head for the next blog inside this Blog Series!

 

Wrap Up 🎁

In this blog we setup, configured and implemented our Database Module along with an OData V4 service (“srv” Module) to expose our database table data as entities in the OData service. We secured our OData service, so only authenticate users can access the information inside our database. Last but not least we extended the Approuter Module inside the “mta.yaml” file so we can access this OData service via our Approuter.

We described the following requirements from the beginning and implemented the following ones:

  • A Multi Target Application (MTA) project
  • An Approuter
  • An xsuaa instance/service
  • Appropriate roles assigned by role collections (xsuaa)
  • A HANA database (via CDS)
  • An OData V4 service (via CDS)
  • A Node.js application to authenticate and authorize against Trello
  • A User-Provided Service to store the Trello API Keys.
  • A UI5 App
  • A Fiori Launchpad Module

See you in the next blog Timesheet Management with CAP & Trello ⏱️ – Connect to Trello API’s via a Node.js Module #3. Where we will create a Node.js Application to authorize and authenticate ourselves against the Trello API’s to gain access to our Trello data.

Kind regards,

Dries

Be the first to leave a comment
You must be Logged on to comment or reply to a post.