One of GeneXus 9.0 Version utilities is the Database Reverse Engineering Tool (DBRET). DBRET is a tool that enables performing an inverse engineering process of an existing database. This means that, starting from the definition of a database (table structure and their relations) it creates a GeneXus data model to support it.

Many may know its predecessor, the Data View Generator. Even though the concept is the same, this version includes new features that facilitate the process further and substantial improvements in the creation of the data model that represents this database.

The usage objectives for this tool can be very diverse, ranging from just accessing a table from another system to generate programs with GeneXus in order to read, update or just publish that information, to the whole reengineering of a legacy system.

How to use the DBRET Wizard

Working with this tool is really simple; it just requires executing a series of steps guided by a wizard. Let's see an example.

We'll take the database included in SQL Server 2005, called Adventure Works, which simulates the operating database of a sporting goods sales system.

DBRET can be executed from GeneXus (Tools / Database Reverse Engineering Tool) or by running the Database Reverse Engineering Tool 1.0.1 option from the Programs menu under the GeneXus group. Since it is the first time we are connecting to that database, we select the New Connection Information option and then press Next.

In the second screen we must set the database connection options (DBMS, database name, user, etc.).

This version includes several DBMS access methods, such as ODBC, ADO.NET o JDBC. Some require the installation of additional drivers. Visit the Requirements section here for additional information.

1. DBRET - Connection Information

As a result of this step (after clicking Next) what we call DBRET metadata is created. It's an xml file with all the information of the existing tables, indexes and relations in the database.

The advantage of this file is that it will enable us to add knowledge into our KB incrementally, without the need to connect to the database again.

The second step is selecting the tables we wish to integrate to our Knowledge Base.

2. DBRET - Table Selection

After selecting all the tables we wish to integrate into our model, we go into the final report.

This is where the inverse reengineering process is performed and the necessary GeneXus objects are created to represent that data model. This implies creating tables and attributes following the URA convention used in GeneXus to name them (and to relate the tables).

Most databases do not follow this name convention to establish the relations between their tables; instead referential integrity rules are defined. DBRET uses these rules to define the attribute names in the various tables.

TIP: If the selected database does not have referential integrity between its tables, but uses this same name convention to relate them, you must change the Naming Rule property to same name - same thing.

If you want further information about this point, please refer to the Reverse Engineering Process Overview document.

In the last screen of the wizard we can see a report with the GeneXus objects to be consolidated. If DBRET was called from GeneXus, this information will be automatically consolidated in the KB after clicking the Finish button. Otherwise an xpz (distribution file) will be created which could later be consolidated in GeneXus using the Knowledge Manager.

3. DBRET Final Report

TIP: If you wish to add new tables to a Knowledge Base where you have previously added tables from that same external database, we recommend calling DBRET from GeneXus, since in this case the inverse reengineering process will consider the tables and Data Views already defined in the KB to establish or avoid relations (depending on the information in the metadata) with the new tables (views) to be consolidated.

Now you can generate the Transactions and manipulate the external tables data from a GeneXus application. You can also create new objects or use GeneXus Patterns to create a whole web system based on that database, without programming a code line. Try it!

4. Patterns over Transactions imported by DBRET

For additional information about the DBRET, go to:

http://www.gxtechnical.com/dbret

If you wish to learn more about the executed inverse reengineering process, please refer to: http://wiki.gxtechnical.com/wiki/tiki-index.php?page=ReverseEngineeringProcess