How to keep track of database changes

Question

I'm working with Progress 11.6 appBuilder and procedure editor (and Data Dictionary).

Regularly we are doing modifications at the customer's database, there are two types of modifications:

• Modifications of the structure: those are done, using interactive GUI of the data dictionary.
• Modifications of the data: those are done, using the procedure editor

An example of a data modification in the procedure typically looks like this:

FOR EACH Table1 WHERE Table1.Field1 = <value>:
  CREATE Table2. 
  Table2.Field1 = <value>.
  Table2.Field2 = <some-other-value>.
END.

This is completely in contradiction with one of the basics of software delivery quantity, repeatability: there is no way to return to the previous situation!

Therefore I'm looking for ways to do this in an (automatable) repeatable way, hence my questions:

• What can we use instead of the interactive GUI of data dictionary (without undo feature) in order to perform/undo database structure modifications?
• What can we do in order to undo database data modifications? (Is there something like a Oracle redo log or a Oracle archive log in Progress?)

In case you say "What are you talking about? You can do "Undo transaction" in the data dictionary.", I mean the following:
I perform a transaction using the data dictionary, I leave the data dictionary and the day later the customer complains. When I open the data dictionary at that moment, the "Undo transaction" feature is disabled.

Answer 1

At a high level you should be creating "df files" (DDL scripts) and applying those to the customer database rather than manually making changes. There are many ways to create those files and you can automate the entire process with the appropriate tooling.

One of the most common ways to create a df file is to create whatever new schema you need in your development database and then use the "create an incremental df" facility in the data dictionary tool. This tool compares the development database schema to the target schema and builds a "df file" (DDL script) of the differences. You could connect directly to the target db for this process or you could have an empty skeleton db that you use for this.

How to create an incremental df file

(If you then reverse the comparison you can also create a reversing df file to undo the changes.)

Most df files consist of additions - new tables, new fields, new indexes. These can all be added online and that can all be completely scripted. And, of course, the individual df files and all of the supporting scripts can (and should) be stored in a repository (like git or whatever).

As for the data change scripts... there's no reason that those programs cannot be written as actual programs and saved in a repository. You can enclose the whole update in a transaction and UNDO it if that is appropriate. For what it is worth, I personally do not think that is a very good idea. Especially when large amounts of data are involved you really don't want to be creating monstrous multi-gigabyte undo logs. You're better off with a second "reversing transaction" script that will roll things back piecemeal. A side benefit is that you can still use that if you decide to back out the change a day or three afterwards.

The really gory details are going to depend on your development process and the customers change management process and the tooling available. It kind of sounds like there is not much process or tooling at either end of this relationship so you probably have a lot of adventures ahead of you!