CAP - Preparing initial data with UUID keys

[AB]

One of the many great things about the SAP Cloud Application Programming model is the ability to seed your non-productive databases with domain specific example data . This feature is particularly useful when demonstrating the fully functional application you built this very morning ( using Fiori Elements and CDS annotations ) to a sceptical group in the afternoon. After all, nothing speaks so powerfully as the data of the user.

The excellent documentation at https://cap.cloud.sap/docs/guides/databases provides some details on where this example data should be located and the format it should have. In short - a CSV like text file with an initial row with headers (entity attributes) and subsequent rows for each record.

The CAP documentation is quite opinionated - in the style of a caring wise mentor - and  recommends UUIDs for technical keys - https://cap.cloud.sap/docs/guides/domain-models#use-uuids-for-technical-keys and the use of other attributes as semantic keys where required.

I often find myself extracting some domain specific example data from an ECC SAP system, where commonly but not exclusively, UUIDs are not used as technical keys. This means I have to augment my source extracted file with UUIDs. That's easy enough for a handful of data - but tedious for more.

And thus I come to the nub of the blog - how to simply prepare some example data with UUIDs for use in your SAP Cloud Application.

The approach I take is to generate a file of UUIDs and paste them into my extracted csv file such that the outcome is a nicely constructed my.banana-OrderTypes.csv ready for CAP to consume.

Exhibit A - what I'm starting with:

codeID,descr,active,notes

0100,Internal orders: Development,0

0200,Internal orders: Construction,0

0300,Internal orders: Tools and equipment,0

0400,Internal orders: Marketing,1

0500,Internal orders: Third-party services,1

0600,Internal orders: Investment,1

0650,Capital investment order,1

0700,Internal orders: Production,1

Exhibit B - what I'm looking to achieve with the least effort as possible:

ID,codeID,descr,active

a43675ee-79af-44eb-826d-9c9555291c4a,0100,Internal orders: Development,0

c9e78068-ddff-4f29-b71c-c4e0cf59c852,0200,Internal orders: Construction,0

4d64d273-aa29-47b4-a698-f4a3b8b2999d,0300,Internal orders: Tools and equipment,0

7e22012e-6e2a-41c2-ab46-e42c0dd519f4,0400,Internal orders: Marketing,1

I suspect that some of the ancient command line tools available in a Bash shell under Linux or MacOS do the job nicely. So let's see how this goes.

How many UUIDs do I need to create? As many as the rows in the example data file. OK - that's easy enough. This should do the trick.

cat source.csv | wc -l

  42

How do I generate a UUID? uuidgen to the rescue - and then tr to translate upper case to lower case.

uuidgen | tr "[:upper:]" "[:lower:]"

  d085a1f0-60a3-4fe3-aa5e-c1ae5c21ab42

But I want 42 UUIDs in a file please - since there are 42 records in my source csv file - and please - don't forget the ID in the header row.

echo 'ID' > UUIDListing

for i in {1..42}; do uuidgen | tr "[:upper:]" "[:lower:]" >> UUIDListing ; done

So now I've got

ID

a43675ee-79af-44eb-826d-9c9555291c4a

c9e78068-ddff-4f29-b71c-c4e0cf59c852

4d64d273-aa29-47b4-a698-f4a3b8b2999d

...

But we somehow need to combine the UUIDListing file and the source.csv into my.banana-OrderTypes.csv. Hmm - sounds like a job for ... paste -- merge corresponding or subsequent lines of files

paste -d ',\n' UUIDListing source.csv > my.banana-OrderTypes.csv

to end up with the desired outcome.

ID,codeID,descr,active

a43675ee-79af-44eb-826d-9c9555291c4a,0100,Internal orders: Development,0

c9e78068-ddff-4f29-b71c-c4e0cf59c852,0200,Internal orders: Construction,0

4d64d273-aa29-47b4-a698-f4a3b8b2999d,0300,Internal orders: Tools and equipment,0

7e22012e-6e2a-41c2-ab46-e42c0dd519f4,0400,Internal orders: Marketing,1

But hang on - let's look at that paste again - what is -d ',\n' all about?

paste -d ',\n' UUIDListing source.csv > my.banana-OrderTypes.csv

-d - uses the following characters to replace the new line characters in the files. So at the end of the first file - UUIDListing - replace the end of line character with a comma , and at the end of line of the second file - replace the end of line character with \n - an end of line character. Eh? Just being explicit! See the documentation for paste.

So putting it all together leads to:

Step 1: Determine number of UUIDs needs based upon rows in the source file

developer$ cat source.csv | wc -l

Step 2: Create a file for the UUIDs with a header of ID

developer$ echo 'ID' > UUIDListing

Step 3: Generate the UUIDs convert them to lower case and add them to the UUID Listing file

developer$ for i in {1..42}; do uuidgen | tr "[:upper:]" "[:lower:]" >> UUIDListing ; done

Step 4: Paste the UUIDs and the source file to give me my CAP Ready csv file

developer$ paste -d ',\n' UUIDListing source.csv > my.banana-OrderTypes.csv

For those with some shell scripting skills, it isn't that hard to turn this sequence of computing instructions into a single program - a quick macro? to do the task in one hit.

Enjoy!

Postscript: I use the following rough and ready script. Unlike what is written above, I also add the headers manually to the target.csv after the UUIDs have been added.

developer$ ./script.sh source.csv target.csv

#!/bin/bash

lines=$(cat $1 | wc -l );

source_file=$1

target_file=$2

uuid_file=$(mktemp)

for i in `seq 1 $lines`;

   do uuidgen | tr "[:upper:]" "[:lower:]" >> $uuid_file ; done

paste -d ',\n' $uuid_file $source_file > $target_file

rm $uuid_file