[SAP BTP Chronicles #4] CAP framework and HANA Cloud migrations with journaling

🔔This is the 4th part about BTP-related topics. See others here.

In the previous part we saw that some changes to the CDS model in our project can cause problems — for example, the loss of production data. The default schema evolution in CAP is not always 100% safe and should be treated with caution. There are ways to mitigate this — and that's what we'll cover in this part.

Migration with HDI

HDI (HANA Deployment Infrastructure) provides an option to migrate schemas using hdbmigrationtable files. These files enable, first, explicit versioning and, second, the steps required for schema migration. That means they can be very useful for gaining greater control over table changes and are helpful when migrating large tables. Why? The documentation states:

...the migration-table plug-in (.hdbmigrationtable) uses explicit versioning and migration tasks, which means that the modifications of the database table are explicitly specified in the design-time file and carried out on the database table exactly as specified, without incurring the cost of an internal table-copy operation such as the one performed by the .hdbtable plug-in. This behavior makes the .hdbmigrationtable plug-in especially useful for tables that contain a lot of data.

Let's see how this looks in practice.

Enabling versioning

Continuing the project from the previous part. The initial model for the Reviews entity looks like this:

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;
}

Each column in the table contains data:

I add the annotation @cds.persistence.journal. After building the project, a file sap.capire.reviews.Reviews.hdbmigrationtable will be generated in the db/src directory. This file will also appear in the gen directory for the Reviews table (instead of an hdbtable file):

== version=1
COLUMN TABLE sap_capire_reviews_Reviews (
  ID NVARCHAR(36) NOT NULL,
  subject NVARCHAR(111),
  reviewer NVARCHAR(255),
  rating INTEGER,
  title NVARCHAR(111),
  "TEXT" NVARCHAR(1111),
  "DATE" SECONDDATE,
  liked INTEGER DEFAULT 0,
  PRIMARY KEY(ID)
) WITH ASSOCIATIONS (
  MANY TO MANY JOIN sap_capire_reviews_Likes AS likes ON (likes.review_ID = ID)
)

As you can see, the initial generation of files for HANA marked this DDL as the first version of our table.

Applying schema changes

Next, we reapply some changes from the previous part. To begin, a change that previously caused no issues — for example, adding a column.

@cds.persistence.journal
entity Reviews {
  key ID       : UUID;
      subject  : ReviewedSubject;
      reviewer : User;
      rating   : Rating;
      accepted : Boolean; // nowa
      title    : String(111);
      text     : String(1111);
      date     : DateTime;
      likes    : Composition of many Likes
                   on likes.review = $self;
      liked    : Integer default 0;
}

Let's take a look on the file sap.capire.reviews.Reviews.hdbmigrationtable again:

== version=2
COLUMN TABLE sap_capire_reviews_Reviews (
  ID NVARCHAR(36) NOT NULL,
  subject NVARCHAR(111),
  reviewer NVARCHAR(255),
  rating INTEGER,
  accepted BOOLEAN,
  title NVARCHAR(111),
  "TEXT" NVARCHAR(1111),
  "DATE" SECONDDATE,
  liked INTEGER DEFAULT 0,
  PRIMARY KEY(ID)
) WITH ASSOCIATIONS (
  MANY TO MANY JOIN sap_capire_reviews_Likes AS likes ON (likes.review_ID = ID)
)

== migration=2
-- generated by cds-compiler version 4.7.6
ALTER TABLE sap_capire_reviews_Reviews ADD (accepted BOOLEAN);

And now we can clearly see that a change in the CDS entity causes the second version of our table to be generated. The changes are highlighted as well.

Now let's see what happens when we introduce a change that previously caused the loss of production data. We rename the column from title to heading — in the default approach this was done by dropping the old column (DROP) and adding a new one (ADD).

@cds.persistence.journal
entity Reviews {
  key ID       : UUID;
      subject  : ReviewedSubject;
      reviewer : User;
      rating   : Rating;
      accepted : Boolean;
      heading  : String(111); // name changed
      text     : String(1111);
      date     : DateTime;
      likes    : Composition of many Likes
                   on likes.review = $self;
      liked    : Integer default 0;
}

In the file sap.capire.reviews.Reviews.hdbmigrationtable we can see the new version — version 3. This time there is also a message indicating that manual resolution of the issue is required.

== version=3
COLUMN TABLE sap_capire_reviews_Reviews (
  ID NVARCHAR(36) NOT NULL,
  subject NVARCHAR(111),
  reviewer NVARCHAR(255),
  rating INTEGER,
  accepted BOOLEAN,
  heading NVARCHAR(111),
  "TEXT" NVARCHAR(1111),
  "DATE" SECONDDATE,
  liked INTEGER DEFAULT 0,
  PRIMARY KEY(ID)
) WITH ASSOCIATIONS (
  MANY TO MANY JOIN sap_capire_reviews_Likes AS likes ON (likes.review_ID = ID)
)

== migration=3
-- generated by cds-compiler version 4.7.6
>>>>> Manual resolution required - DROP statements causing data loss are disabled by default.
>>>>> You may either:
>>>>>   uncomment statements to allow incompatible changes, or
>>>>>   refactor statements, e.g. replace DROP/ADD by single RENAME statement
>>>>> After manual resolution delete all lines starting with >>>>>
ALTER TABLE sap_capire_reviews_Reviews ADD (heading NVARCHAR(111));
-- ALTER TABLE sap_capire_reviews_Reviews DROP (title);

cds build also notifies us that something is wrong.

[ERROR]  Manual resolution required for file db\src\sap.capire.reviews.Reviews.hdbmigrationtable.
Check migration version content for further details.
Current model changes require manual resolution

So let's do what the cds-compiler suggests — replace the DROP/ADD with:

...
== migration=3
RENAME COLUMN sap_capire_reviews_Reviews.title TO heading;

In the log we can see that migration no. 3 was executed:

After deployment, the data in the heading column — previously title — is preserved.

So we have an additional manual step for our database-layer changes that allowed us to avoid data loss caused by the default DROP/ADD approach. We can check which DDL can help in our case. BTW — such a migration will not run every time; it will execute once when upgrading the table from version 2 to 3.

So — are we switching entirely to writing SQL for problematic database changes?

Not necessarily. We can keep the default, fully automated approach (with hdbtable) without versioning, and for database changes apply Continuous Deployment techniques that are database-agnostic — a simple recipe to perform changes in steps so nothing breaks and no data is lost.

More on that in a later part.