[SAP BTP Chronicles #3] CAP framework and HANA Cloud migrations
🔔This is the 3rd part about BTP-related topics. See others here.
Database schema migrations can sometimes be complicated - the CAP framework tries to help us here. If you have worked or are working with ABAP, you are probably used to the platform doing the work for you (automatically or semi-automatically) related to adding, removing, or changing field types, even if there is already data in the table. In CAP, we also have support - but in certain situations, we need to help ourselves and, above all, be cautious.
Sample Application
The example application is a simple service with a model copied entirely from the file
in SAP sample app.
After cds init sample-hana-migrations and copying the above content to the db/schema.cds folder, I added the service:
srv/service.cds
using {sap.capire.reviews as db} from '../db/schema.cds';
service ReviewsService {
entity Reviews as projection on db.Reviews;
}
Then I added the HANA and MTA features.
cda add hana, mta
cds build --production
npx mbt build
cf deploy mta_archives\sample-hana-migrations_1.0.0.mtar
HANA Cloud - what we have at the start
Let's see how the tables in the database created from our model look:
The tables are currently empty - what will happen if we completely "demolish" them - change
the column names and their data types? In the Reviews entity, I changed every field name
and data type.
entity Reviews {
key ID : UUID;
what : Int16;
who : Int16;
how_many : String(20);
about : Int16;
description : Decimal(10, 2);
happened : Date;
loved : Composition of many Likes
on loved.review = $self;
counted : String(90);
}
After deployment:
It went smoothly - the table was empty, so there was no problem with the schema migration.
Let's move on to more interesting cases.
Migrations for a table with data
I restored the Reviews entity to its initial state and added a few rows.
Let's now look at various basic scenarios that may occur on the developer's path.
Adding a new column
We add a new column accepted with the Boolean type:
entity Reviews {
key ID : UUID;
subject : ReviewedSubject;
reviewer : User;
rating : Rating;
accepted : Boolean; // new
title : String(111);
text : String(1111);
date : DateTime;
likes : Composition of many Likes
on likes.review = $self;
liked : Integer default 0;
}
It went smoothly, no problems - the new column was added with NULLs inside.
When adding new columns, the table is modified (ALTER TABLE) and no further actions are required on our part.
Removing a column
Now let's look at two cases of removing a column. First, let's take the newly
created accepted field - there are no values in this column in any row.
entity Reviews {
key ID : UUID;
subject : ReviewedSubject;
reviewer : User;
rating : Rating;
title : String(111);
text : String(1111);
date : DateTime;
likes : Composition of many Likes
on likes.review = $self;
liked : Integer default 0;
}
After deployment:
The change went smoothly.
For the changes so far, everything is going well, according to the documentation - if compatible changes can be applied, it happens without any data loss or the need for additional migration logic.
Now let's see what happens when we want to remove the rating field - in
all rows in this column, we already have values.
entity Reviews {
key ID : UUID;
subject : ReviewedSubject;
reviewer : User;
title : String(111);
text : String(1111);
date : DateTime;
likes : Composition of many Likes
on likes.review = $self;
liked : Integer default 0;
}
After a smooth deployment:
So the column with data was removed. Therefore, be cautious with changes in the cds
files with the model, as accidental field deletions can lead to potential data
loss in the production table - there is no warning.
Renaming a column
Let's see what happens when renaming a column - changing the field name title to heading:
entity Reviews {
key ID : UUID;
subject : ReviewedSubject;
reviewer : User;
heading : String(111);
text : String(1111);
date : DateTime;
likes : Composition of many Likes
on likes.review = $self;
liked : Integer default 0;
}
This time we lost all the titles - the column was "changed", which means according to the documentation, the old column was dropped and a new one was added.
Changing the column type
Let's now look at type changes. The date field in our model has the DateTime
type, let's see what happens when converting it to just a date (Date).
entity Reviews {
key ID : UUID;
subject : ReviewedSubject;
reviewer : User;
heading : String(111);
text : String(1111);
date : Date; // change from DateTime
likes : Composition of many Likes
on likes.review = $self;
liked : Integer default 0;
}
Data in the table before conversion:
After the change and deployment:
Not bad - the data is preserved correctly. After restoring the DateTime format,
the dates are still correctly preserved with the time set to zero:
Now let's convert this field to String:
entity Reviews {
key ID : UUID;
subject : ReviewedSubject;
reviewer : User;
heading : String(111);
text : String(1111);
date : String(500); // change from DateTime
likes : Composition of many Likes
on likes.review = $self;
liked : Integer default 0;
}
After deployment:
The data was preserved without any issues. Now let's see the conversion of this field to Integer:
entity Reviews {
key ID : UUID;
subject : ReviewedSubject;
reviewer : User;
heading : String(111);
text : String(1111);
date : Integer; // change from String
likes : Composition of many Likes
on likes.review = $self;
liked : Integer default 0;
}
When attempting to deploy, we encounter an error. In the database deployer module logs, we have:
So we cannot perform such an incompatible change. Let's change the type back to
DateTime for the date field and look at another thing - let's change the liked
field first to String:
entity Reviews {
key ID : UUID;
subject : ReviewedSubject;
reviewer : User;
heading : String(111);
text : String(1111);
date : DateTime; // change from String
likes : Composition of many Likes
on likes.review = $self;
liked : String;
}
After deployment, the date column has the correct data. The liked column also - the
number of likes was correctly converted to a string:
Now let's change liked to another numeric type - let's use Double:
entity Reviews {
key ID : UUID;
subject : ReviewedSubject;
reviewer : User;
heading : String(111);
text : String(1111);
date : DateTime; // change from String
likes : Composition of many Likes
on likes.review = $self;
liked : Double;
}
After deployment, we see that there were no issues converting strings with numbers to numeric values:
Another conversion is shortening the data type - let's change the length of the
string for heading to 10 characters:
entity Reviews {
key ID : UUID;
subject : ReviewedSubject;
reviewer : User;
heading : String(10); // length changed from 111 to 10
text : String(1111);
date : DateTime;
likes : Composition of many Likes
on likes.review = $self;
liked : Double;
}
Shortening the type caused data loss, meaning the column was dropped and recreated with the new type.
Help in determining which conversions will work can be found in the documentation, with tables where we can check what to expect when attempting to convert one type to another.
How does it work?
The CAP framework uses the
HANA Deployment Infrastructure
to deploy tables for HANA. After running the cds build command, you will find
various files
with the hdb... extension in the gen directory.
Example hdbtable file for the Reviews table contains the SQL that will be executed on the database:
After various changes made in this article, we always ended up with a file that was then interpreted by HDI. As you can see, it does not contain any hints regarding the changes introduced.
Summary
Through various examples, we have seen that some changes are seamless, but some can
cause us trouble (data loss) if we are not aware of the mechanisms.
In the next episodes, we will see how to address this.
