English|Português|Español
UserPasswordLogin

HOME
SESSIONS
INFO
Imagen6267E

Database reorganization in GeneXus

The database reorganization that is automatically performed by GeneXus has been strengthened by the changes introduced in GeneXus' Olimar version. By Gustavo Proto, Program Manager of GeneXus Development

Basically, database reorganization (RGZDB) is a process resulting from the analysis of two database structures (the current one and the designed one) for determining the best way to convert one into the other with the least information loss possible.
 
The capacity of GeneXus to automatically perform the RGZDB is one of the key differentiators of our product, without which, the incremental development model proposed by GeneXus would be practically impossible to implement.
 
With the ideas, and the description of situations and their possible solutions, provided by our clients, beta testers and ourselves, the GeneXus Olimar version has "revitalized" the RGZDB in different aspects:
Impact Analysis Report (IAR). The IAR is a report resulting from a comparison between the current and the designed database structures. It shows what has happened to each of the tables, and explains what the conversion process applied to it is. Although it is a very important report, it becomes especially relevant in projects involving many developers where many of their changes are consolidated into a single Knowledge Base (KB): it is the means for GeneXus to communicate with both, the person who assesses the quality of the data structure as well as with the person who has to plan and execute the reorganization.


IAR modifications in the GeneXus Olimar version:
 
·         The most visible modification is the change in the way it looks. Icons, colors, etc. have been modified as a way to make it more legible.
 
·         Information related to the referential integrity has been added. This information enables us to know why there are indexes that GeneXus defines automatically and also to detect possible differences between what is being modeled and what had to be modeled as well as errors in the model.
 
·         Changes in external tables as well as in tables that have been declared in the server (and for which there is a request to refrain from reorganization) are analyzed and displayed. This makes it possible to report the changes that have to be made on these tables, which have not been reorganized by GeneXus, and to detect changes that have not been intended to occur.
 
·         The most relevant properties of the attributes are clearly shown. This includes the type of data, the possibility of it being null, auto-numbering, etc.
 
·         The changes that have taken place are shown even if they do not imply reorganization. This enables the possibility of seeing modifications in external tables or in tables declared "in the server", and deciding whether the model should be updated or not.
 
·         More warnings or errors have been implemented, thus contributing with developers in their task of detecting and correcting possible situations ahead of time.
 
·         The errors that might be detected will stop the reorganization from taking place and being executed.
More data conversion cases: In previous versions, situations arose in which developers expected a certain conversion to take place based on the changes that had been made, and yet these changes never occurred. GeneXus did not know how to implement them. In many cases, this was "unexplainable" for developers.
 

By having incorporated many of the reorganization suggestions that have been received, the improvements on the Olimar version will certainly reduce to a minimum these disappointments, thus turning it into a more "natural" or predictable version:


·         The tables that no longer exist in the model are now also eliminated from the database.
 
·         When changing a table's primary key, its data is kept. This, I believe, was one of the most difficult points to understand, especially for the new users of our product. GeneXus Olimar version enables a table's primary key to be changed and a data conversion into the new structure to occur.


·         The substitution of an attribute by one of its subtypes or vice versa keeps the data, and enables initialization with the other. When substituting, for instance, the CustId attribute by the CustIdInvoice attribute, the CustId values are automatically assigned to CustIdInvoice. This makes subtype implementation easier as the application changes together with the requirements.

 

·         Subtype definition on pre-existing attributes causes the data to migrate. This reorganization is relatively common among our users as data models evolve. For instance, a Customers transaction may be originally defined, but in time, it might be necessary to unify all of the entities that have any relation with the application. A Companies transaction will be then defined, and the Customers transaction attributes will become subtypes of the Companies attributes. The Olimar version is capable of populating the Companies table from the pre-existing Customers table.

An interesting aspect of this reorganization is that many "origin" (Customer) tables might exist for the same destination (Companies) table.

 

·         "Initial value" properties for attributes. Giving an initial value to an attribute enables GeneXus to assign this value to the attribute when the attribute becomes a part of a pre-existing table. This eliminates the need to program a Procedure in order to do so.

Controls for anticipating failure. Whenever possible, GeneXus proposes solutions, in the RGZDB, for situations that might cause an error, when there is a reasonable alternative, and displays a warning identifying the situation. For instance, if an attribute does not allow nulls, but there is the possibility that it might have them, GeneXus will show that this may happen as well as how it will solve this.
 
In situations where finding a reasonable alternative is not possible, GeneXus shows an error that must be resolved in order to continue.
The number of warning and error messages has been increased to avoid undesired results or errors that in previous versions where not detected with enough anticipation.
 
The implementation of controls made at the execution time of reorganization programs is something even more important.  For each of the cases in which the only way to know whether an error will occur is by knowing the data existing in the tables, the GeneXus Olimar version can write the necessary code in order to spot the error and stop the reorganization execution ahead of time.
 
The definition of a single index, for instance on an existing table, could fail if there are duplicates within the data that have already been loaded. GeneXus generates code that controls, at execution time, the duplication of single keys by canceling reorganization before it gets to execute.

Present and future: I know that we have made important progress in one of the differentiating aspects of our product, thus helping our clients to become more focused on business aspects rather than on technical ones. Fortunately, because of the pleasure that we find in facing these things, we still have a lot to do in this area. With the support of our clients, we will be able to add some to the list. Please, feel free to direct your suggestions to us (and solutions, whenever possible). We will focus on them in upcoming versions.

Related
GeneXus Olimar version beta 3 has been released
First system developed with the Pocket PC generator
Sub-types in the GeneXus Olimar version
From the GeneXus Olimar version: Client Side Validation