Universal Reverse Engineering (ADO, OLEDB, ODBC)

Top Previous Next

You can reverse engineer a number of databases, such as Sybase ASA and ASE, Oracle, PostgreSQL, Informix, MSSQL and others. This means that you can extract tables, attributes, relationships, indexes and other objects. It is possible with the Universal Reverse Engineering tool, which connects to a number of databases through OLEDB (ADO) or ODBC link. So, to reverse engineer a particular database, you need a corresponding OLEDB provider, or ODBC driver. In most cases, such libraries are installed in the system with the help of database client applications.

To reverse engineer a database through OLEDB or ODBC link:

1. Select Universal Reverse Engineering item from Reverse Engineering drop-down menu on Database tab of the Ribbon.

2. If the currently opened diagram already contains some objects, the following dialog box will appear:
It requests you to create a new diagram to place the reversed objects into or use the currently opened diagram. Click Yes to create a new diagram, No to use the currently opened diagram, or Cancel to cancel reverse engineering. Select Do not show this dialog again to disable future notifications.

3. The Universal Reverse Engineering tool will be shown. First, provide full OLEDB connection string in the Connection tab.
Click on the "..." button to call the standard system Data Link Properties dialog window, which helps you to build OLEDB connection string:
To use ODBC drivers, click on the Microsoft OLE DB Provider for ODBC Drivers. To use the native OLEDB provider, click on the appropriate item in the list. Browse through tabs to set other tabs and connection properties. Click on the Help button in the bottom of the dialog window to learn more about OLEDB connections. Click on the OK button to store the data link properties in the Connection string field of the Universal Reverse Engineering dialog.

4. You can set reverse engineering options in the Options tab:

Tables only
Reverse engineer only tables ignoring views.

Tables and Views
Reverse engineer both tables and views.

Tables in a diagram row
This option determines how reversed tables will be disposed on the diagram. Reversed tables will be placed on the diagram in rows with equal distance, this option determines how many tables maximum there will be in one row.

Garbage symbols remove/replace
Defines the symbols that will be replaced in the names of the objects.

Replace with
Defines the garbage replacement symbol.

Build references
This option enables extracting foreign keys from the database and creating appropriate references in your diagram.

Automatically rebuild references when no reference is reversed
If there are no physical references extracted, it is possible to build them from logical structure of the database.
Enabling this feature leads to automatic reconstruction of references. Such reconstruction works by the following scheme: each column of the table is being compared with all primary keys of other tables, and if the column name and data type match one of the primary keys, a reference between the source column and the key column will be created.
This option is available for modification only if Build references is checked.

Convert Unique Indexes to Primary Keys
There no primary keys in some databases, but unique indexes used instead. You can use this optin to convert such unique indexes to primary keys.

5. In the Selection tab of the Universal Reverse Engineering tool you can choose the tables you want to reverse engineer.
Click on the checkbox near the table to enable its reverse engineering. Pay attention to the Select All (select-all-items) and Deselect All (deselect-all) buttons on the tab, they allow you to select/deselect all tables in the list.

6. Click OK to start the database reverse engineering process. The Output -> Reverse docking window will display the state of the process.

7. The reversed database objects will be placed in your diagram.

See also:
Reverse Engineering and Import: Reverse Engineering and Import Overview | Reverse Engineering MySQL Database | Import from Access Database