dbUpdateUtil

By Allan Kim Eriksen, NordTeam Gruppen Aps

What it does:

This utility is able to update any kind of DATAFLEX-databases, where the update is based upon a set of definition-files (*.DEF).
The update-utility itself supports structural changes (updates) with preservation of the existing data as well as a change of the data in a record on a specific database (initializing).
Thanks to my boss I'm now able to give it away for free, the source code that is!

The update-utility is able to initialize the databases to make sure that certain information is present after the database is updated, including the CODETYPE and CODEMAST databases.
An example of this would be to make sure that certain choices in combo-boxes are present, or maybe you want to change a supervisor password, update system files... you name it!
The initializing process can be forced to use indexes to make sure that no duplicate records are created.

Technical description

The update-utility is using FIELDNAMES rather than field numbers, so it is possible to insert fields anywhere inside the database.
If a fieldname has changed, the update-utility will ask the user which of the fieldnames are identical and/or if the field has been erased.
As the Update Utility is based upon DEF-files, the Update Utility will not be able to make files in which a fieldname consists of "@" (The Make_file command in DATAFLEX does not seem to understand the syntax?). It will however be able to change the fieldname.
If ie. the old name was "@PASSWORD" to "PASSWORD". This could then be placed in a "Known-Issues" file so that the enduser is not bothered with this question (an example of this is in the zip-file).

In order NOT to disturb the end-user with these kind of questions, the update-utility is able to understand "Known-Issues" files (NTK file).
In these files you can define which fieldnames are identical or which of the unmatched fields have been erased.
By keeping these files you can update previous versions of databases without needing to know what version the end user has.
If the information in the "Known-Issues" files get obsolete you don't have to erase that information, thereby insuring
you can update any previous versions.

There are limitations for initalizing fieldtypes:
- TEXT-fields can not include Linefeed-characters such as a RETURN/ENTER-character. The field can be considered a ASCII-type where data can be of one long text-line.
- BINARY-fields can not be initialized (who ever want that anyway :)
- OVERLAP-fields can not be initalized by themselves, only by the fields that the OVERLAP-field is build upon.

The textfile is an example of how to make a "Known Issues" file. What it contains is instructions to the update utility what to do when the update utility finds a fieldname in the old database that is not represented in the new database.
As for now you will have to make this file on your own (i.e. with notepad).
Give the name for the file the same name as the physical name of the dataflex-file.
In the example the dataflex file could be VTXT.DAT and the "Known-Issues" file would then be "VTXT.NTK".
The extension .NTK tells the update utility that this is a "Known Íssues" file.
When the update utility meets a fieldname in the old datafile that it cannot find in the new datafile it will normally ask the user what to do (Erase the field and thereby its contents, or what fieldname it has changed to). In order not to prompt the end user for this question the update utility will search the information in the "Known Issues" file for the specific datafile.
The update utility understands 2 different instructions (as for now)
 "DELETE" and "IDENTICAL".

DELETE OldFieldName
This tells the update utility that the field name in the old datafile is not
in use anymore and has been deleted in the new datafile. This is the same as
if the end user had selected <Erase field>. This instruction causes the
update utility to skip importing the data saved for this field before the
update of the datafile and therefore erase all data for the field.

IDENTICAL OldFieldName NewFieldName
This tells the update utility that the fieldname in the old datafile has
been renamed to NewFieldName. This is the same as if the end user had
selected another field with the NewFieldName. This instruction causes the
update utility to import the saved data for the OldFieldName into the
NewFieldName.

Examples in VTXT.NTK:
"Delete, ID"
The field with the name "ID" in the VTXT-file has been deleted in the
updated VTXT-file.

"Identical, Num, Vtxtnum"
The field with the name "Num" in the VTXT-file has been renamed to "Vtxtnum"
in the updated VTXT-file.

"IDENTICAL  ,  @PASSWORD     ,  PASSWORD"
The field with the name "@PASSWORD" in the VTXT-file has been renamed to
"PASSWORD" in the updated VTXT-file.

Please note that the update utility is case insensitive regarding the instructions and fieldnames, and also insensitive regarding how many spaces before or after each instruction and fieldname. One thing though, each instruction has to start on a new line!

The update-utility is returning a status-file that tells you whether or not the update was successful or not. That can be used in
conjunction with i.e. WISE to make certain safety issues like replacing the partly updated databases with the backup version made
before the update-utility was started.

The update-utility consists of 2 applications, a version that is to be operated manually and a full-automatic version.
The manual version is for the developer where you can test an update and make notes on for example fieldname changes.
This is also the version to use when you need to make definition (*.DEF) files and/or the initialize-files.
It is also possible to use the DataFlex Builder to create the definition files.
The other version (for the end user/customer) only consists of progress bars.
It will start the update when invoked and close by itself when it has finished the update.
You can update a customer base without prompting the user for anything if you combine the automatic version with "Known-Issues"-files and initialize-files.
The update-utility only prompts the user and asks a question, if a change in fieldnames is not known to it.
In that case it will ask the user which of the filenames are identical or if the field has to be erased (same as in the manual version).

For the full automatic part to work I am using WISE and a version number-file.
That is useful for me when some of my customers have to upload their information from a laptop to the main database on a server. By examining the version numbers in these databases right at the beginning of the dataflexapplication I can force the user to update
their application if any of the version numbers is different from the hardcoded version number inside the application.

The autoupdate utility works with registry keys to tell where to find the complete database, the filelist and the descriptionfiles, the initialization files and "known issues" files.
You will have to define 3 string values on the clients machine under the following registry key:

HKEY_LOCAL_MACHINE\Software\Data Access Corporation\Visual DataFlex\6\Defaults

As for the Update database you can specify multiple datapaths i.e. "H:\Data1;H:\Data2" or "\\Server\Data1;\\Server\Data2" and so on.
The Update Filelist could then be "H:\Data1\Filelist.cfg"
The description files (and initialize files, known issues) should be placed in a separate map and that could then be "H:\Descriptions". In WISE these files are placed in a temporary map and after the update utility has finished the map is erased.

For the moment there is no support for a workspace selector as I do not wish to force the end user to select a workspace, since it has to be a full automatic update. I do however get the information from a specific workspace in the registry database in WISE and
place the information in the three registry keys specified above before running the update utility from WISE.

The program manupd.src

You can compile this program with the compiler from VDF5/6 (7 has not been tested, yet) without the need to create a workspace first. Just select [defaults] as workspace and choose the filename.
Hit the compile button after that.
When you start the program you can start with generating description and initialize files.

We are going to show you an easy example where we will add an extra record into the codemast file for the shipping type. Assume that you have a customer using the order-entry example and that they want to have the option that customers need to pay before anything is shipped. We are going to call this a prepayment. So, we will need to add a line with the option "PREPAY"
First select the datadescription view.

Selecting the <start> button will write out all the necessary description fields.

When you select the <Initializing> button, the update utility shows a list of datafiles in a selectionlist from where you can select a datafile to initialize. Select the CODEMAST file.

If the datafile already has an initialize file, it will be marked with (init) before the filename in the list.
A new window will popup and this one you can specify new records that should be present after the datafile has been updated.
All the fieldnames are listed in the view and each row represents a field in the datafile.

In order to create a new record use the standard DataFlex-keys (F5, F2, F7, F8 and so on) or use the buttons at the right side of the
window.
All data is handled in internal memory and is not written to an initialization file until you select the <Generate> button.
When you are done select the index that should be used during initializing the datafile.
When choosing an index file, you make sure that the initialisation does overwrite existing data and therefore not causing duplicate record errors. This is also why you can change for example a supervisor password (requiring that the password field is not part of the index *S*).

What we have done here is just entered our data, clicked on the <save> button to store the new record in memory and after that selected index 1.
Then you can click on the <Generate> button so that the initialization file is actually written to disk .
The destination of the file is the path specified in the Description form and it will be named the same as the physical name of the datafile with the extension "NTI" (i.e. "CODEMAST.NTI").
The update utility will automatically initialize a file if an initialization file for the updated file is present in the description-path.

When you generate the description files from the manual update (by hitting <Start>) you actually write ".DEF" files in the map specified in the description form.
The file "Filelist.ntf" is a description of the filelist. I made this because I had some problems copying the new filelist to the updated database.
In some cases the copyfile command (from DataFlex) will not copy the file, probably because Windows hasn't let go of the file. Therefore I use the"Filelist.ntf" and update the current filelist manually.
The reason I write "dummy" to CODETYPE.DEF and CODEMAST.DEF is because the update utility only updates those files that have a .DEF file in the description map.
The CODETYPE and CODEMAST files are never (or should never be) updated, but we would still like to have a way to initialize these files. The presence of the DEF-file is the handle to access these.

Note for if you try to run this example, be aware that by default the flexerrs file is not physically available in the data folder while it is listed in the filelist. You have to copy this file into the data folder before running the example.

I use the manual update utility to prepare for the automatic update utility.
When I'm done creating the initialization and description files I test to see if the test database (a customer database or deploy-version) work.
If the update utility pops up some questions during the update I write them down and how to solve them (i.e. "In VTXT Delete ID").
Then I make the "Known Issues" file(s) in the description-map.
After that I try it again and this time the update utility shouldn't pop up questions.
Finally the description map is packed inside a WISE script with all its safety operations (making a backup, checking to see whether or not the update went allright), along with the automatic update utility new vdf-programs and so on...