DatabaseReverseEngineeringTool GeneXus9.0

Unofficial Content

Database Reverse Engineering Tool 1.2


Overview

Would you like your GeneXus application to access data stores defined in other databases? Or, in a more extreme scenario, do you have an existing database over which you want to run a full GeneXus application?

The new Database Reverse Engineering tool allows you to do so more easily than its predecessor, the Data View Generator by applying reverse engineering to existing databases. This means that it reads all schema information from a database, defines an xml file with its information (metadata), and creates the necessary GeneXus objects (Tables, Transactions, Data Views, Subtypes) that support this schema to be consolidated in a GeneXus KB to define the data model. Now, all these tasks can be performed incrementally and in minimal time!

Features

New connection methods to access the database.
Three different methods can be used to access data: ADO.NET, JDBC and ODBC.

Tables can be incrementally consolidated in a GeneXus Model.
If you already have a KB to add new tables, you must call DBRET from GeneXus (Tools -> Database Reverse Engineering Tool). After the new tables are chosen, the DBRET automatically reads the KB tables to check whether there are any relationships between them. The new tables and relationships will be included in the KB, avoiding name conflicts.

Increased subtype inference capacity
GeneXus uses the URA concept to define relationships between tables. When DBRET creates the data model from the external database, it automatically defines the internal tables and relationships between them based on this concept.
For example, there may be several tables with the same Primary Key name, with different or equal meanings. DBRET knows whether it is necessary to define subtypes or to keep the same name to preserve or avoid the relationships.

Impact Report
An impact report shows information about new GX objects that will be created (Tables, Subtypes, Transactions) and warnings about existing tables that change their relationships, tables without indexes, etc.

Execute from command line (no UI)
This is very important if you need to capture metadata on Linux environments.

Requirements

  • .NET Framework 1.1 or higher.
  • GXpublic Version 8.0 or higher (this is required when DBRET is called from a GeneXus KB).
  • ADO connection required data providers:
    • For DB2 UDB, install version 8.1 or higher. The IBM.Data.DB2.dll file needs to be copied to the DBRET installation directory.
    • For DB2 for iSeries install IBM iSeries Access Version 5 Release 3 or higher. The IBM.Data.DB2.iSeries.dll file needs to be copied to the DBRET installation directory.
    • MySql: The Mysql.Data.dll or MySQLDriverCS.dll file needs to be copied to the DBRET installation directory.
    • Informix: The IBM Informix Client Software Development Kit needs to be installed in the machine. Then copy the IBM.Data.Informix.dll to the DBRET installation directory.

Steps

The tool is a three step wizard:

1. Start Options
The first step is the connection to the external database.

In this dialog, all connection information to access the DBMS must be filled out, such as system name, connection method, database name, user and password.

After this step is completed, all information about tables, indexes and relationship between tables are retrieved from the DBMS and saved in a metadata file.

2. Database Object Selection
In this step you must select the tables you want to consolidate in this instance.

Tip: Use the "Add Related" button to automatically add all referenced tables (N-1 relations) to those already selected.

Tip: The "Save Selection" button enables you to save all selected tables to be easily retrieved in a further execution through the "Load Selection" button.

After this step, the very Reverse Engineering Process takes place, where the DBRET evaluates all relationships between selected tables and defines the necessary GeneXus objects. If the DBRET was called from GeneXus , the KB tables are also included in this analysis.

img/wiki_up//dbretwizard3.jpg


3. Knowledge Base impact report.
This report shows all defined objects created during the reverse engineering process, and in case it finds any problems, the corresponding warning will be shown.
These are some special warnings that could appear:
    • "<Attribute> is a FK to <Table> but it's not selected". In this case there is an option -Add Table- to select this table and automatically reprocess the objects.
    • "The table doesn't have a primary key". When the external table doesn't have a primary key the DBRET creates a Data View but not the Transaction. If you want to create it anyway, select one of the table's attributes as a primary key by means of the "Choose Primary Key" link.
    • "The attribute <Attribute> is auto number and was chosen as primary key". This warning appears when the table does not have a primary key but contains an auto numbered attribute.
    • The type <data_type> is not supported. This message is triggered when the table contains an attribute with a data type that cannot be automatically matches with GeneXus ones. Use the "Change Type" option the select one of the GeneXus data type that better adjusts.

Tip: There is an option to filters only objects with warnings/errors in the tree view in that dialog.

You can change same settings or define new rules in this step before finish (reprocess could be performed).
The output of this step is a distribution file (xpz) to be consolidated in a GeneXus Knowledge Base.

img/wiki_up//dbretwizard4.jpg


Settings

  • Generate Transactions
    • True: A Transaction will be generated with the structure of each table to be imported.
    • False: Only Data View will be consolidated
  • Identify Multilevel Transactions:
    • False: By default each table to be imported will define a Transaction and different Data View. The generated transactions will then be of only one level.
    • True: Certain subordination patterns are looked for to define transactions of more than one level. For example, if there is a table for OrderSalesHeader and other for OrderSalesLines, only one Transaction will be created with the two levels.
  • Generate Schema: When this option is True, the table schema information is stored in the data view schema property.
  • Naming Rule:
    • Prefix only when needed: Based on the referential integrity definition in the database, the tool automatically prefixes those attributes that need to be changed to avoid conflict and preserve the tables relations based on URA. This is the default option.
    • Prefix always: Always prefix attributes with the table name and defines the corresponding subtypes groups to preserve tables' relationships.
    • Same name-same thing: This option applies for databases created with GeneXus or others based on URA definition. If two tables have the same attribute but no referential integrity control is defined between them, will be considered as related tables.
  • Names separator: If an attribute needs to be prefixed with the table name, this separator will be used.
  • Datastore: By default all Data Views will belong to default datastore. With this property you can chose another datastore o define a new one.
  • Folder: Specify the folder name where the new objects will be consolidating in the GeneXus KB.
  • Use Database description:
    • True: The attributes description will be load from the data base to set the "Description Attribute" property.
    • False: The attribute's description will be the same one that the attribute's name.
  • Generate Views as Transactions: Activating this property the user will be able to select a ?key? attribute for the View in order to create a Transaction object associated to it.
  • Backup model before consolidate: If it is activated, a model backup is created before the consolidation take place.
  • Significant Transaction name length*: This determines how many characters will determine the name's uniqueness. For example if 5 is entered, the first 5 characters will control the Transactions unity name.
  • Significant Table name length*: Same as transaction_length for Tables and Indexes names.
  • Significant Attribute name length*: Same as transaction_length for Attributes names.

(*) If DBRET is called inside GeneXus , from a non empty Knowledge Base, these options will be taken from the KB and therefore will not appear available.

Rules

There are four types of rules: Rename, Replace, Change (data) type and Add Relation. For the first three rules you have to specify the old information, the new information and the scope.

Once the rule is defined, you have to press de "Add Rule" button to add it to the rules list. The rules will be automatically applied when you back to the Report tab dialog.

  • Rename: Rename all referenced to a Table, Attribute or Index "Name" with the "New name". The rule will be applied only for the "Object type" scope. If the Table Name scope is specified, the rule only applies to this table.
  • Replace: Replace all the occurrences of the string "To replace" with "Replace with" in the names of "Object type" scope. If the "Table name" parameter is specified, the rule only applies to this table. Besides, if the "Object Name" parameter is specified, the rule only applies to this table/index or table/attribute.
  • Change Type: This rule allows changing the attribute data type. With the "Table name" and "Attribute name" parameters you specify the scope of the rule.
  • Add Relation: Use this rule to establish a relationship between two tables that DBRET could not determine. This information will be taken to create the Knowledge Base model (does not affect the real database).

Save/Load Rules
Use the Save and Load button to backup or retrieve previously saved rules.

Advanced options

Runs while disconnected
The first time that you connect to the database, metadata (xml) is created with all schema information. This metadata can be used with the DBRET to select the tables to import without a database connection. This feature is useful for users who have special database access privileges, because it allows them to generate the metadata and then send it to another user to continue with the process.

To allow this, in the "ReverseEngineering.exe.config" file, the option
<add key="WizardLayout" value="Default"/>
must be changed to:
<add key="WizardLayout" value="Full"/>

When the DBRET is lunched, the followings two options appear in the first step:
    • Open metadata: Select this option if you already have a metadata with the database information to avoid connecting again to the DBMS to process new tables. By default this metadata is created under the Windows user temp directory, as "DBRETMetadata.xml".
    • KB Connected: Enter a Knowledge Base path and the result will be consolidated automatically in that Knowledge Base. Furthermore, the current KB information (if it has some Data Views previously consolidated) will be used in the reverse engineering process to detect relationships between them and the new selected tables.

Considerations

  • In Oracle, when a numeric column is defined without specifying their size, it is created with length 22. In this case DBRET will create it as Numeric(18), which is the maximum numeric length by default in GeneXus . This default value can be changed by means of the "Maximum Numeric Length" design model property.

  • When the DBRET is called connected to a KB, the following is taken into account in the reverse engineering process:
    • KB tables do not participate in the process unless they are included in the DBRET metadata. If they have an attribute in common, the new ones are renamed.
    • If there exists a table in the KB with an attribute with the same name of an attribute coming in the DBRET process inside a View, the new attribute is renamed only if it has a different data type.

  • DBRET retrieves the attribute's description for the following DBMS: SQL Server, Oracle, Access and MySQL and DB2 for iSeries. For iSeries Native retrieves also the attributes' Column Heading and the Column Text and the Record Format name from physical files and the Index name from logical files.

Limitations

  • PostgreSQL DBMS does not support ADO.NET connection.
  • Oracle DBMS can not be connected with the Oracle Driver. Use Microsoft's driver or another connection method, such as ODBC Data Source, ADO or JDBC.
  • Access can only be acceded via ADO.NET.
  • Some DBMS can not be connected with ODBC Data Source. Use ODBC Driver, ADO or JDBC instead.

Comments & Collaboration
See presentation at XV GeneXus International Meeting: http://www.genexus.com/portal/hgxpp001.aspx?2,18,358,O,E,0,MNU;E;65;19;MNU;,

Download
This product is automatically instaled with GeneXus 9.0 but you can also download the latest version from here