Live Database Reverse Engineering

PowerDesigner can reverse engineer from a live database connection into a PDM. The queries that control live reverse engineering are available in the Script/Objects category.

The following queries are used in live reverse engineering:

SqlListQuery - Retrieves a list of available objects to populate the Database Reverse Engineering dialog. This query is memory intensive, and should retrieve the smallest number of columns possible. If it is not defined, then SqlAttrQuery will be used to populate the dialog.
SqlAttrQuery - Retrieves the object attributes to be reverse-engineered. This query is not necessary if the object has few attributes, and the SqlListQuery can retrieve all necessary information, as is the case for tablespaces in Sybase SQL Anywhere.
SqlOptsQuery - Retrieves the physical options to be reverse-engineered.
SqlListChildrenQuery - Retrieves lists of child objects (such as columns of an index or key or joins of a reference) to be reverse-engineered.
SqlSysIndexQuery - Retrieves system indexes created by the database.
SqlChckQuery - Retrieves object check constraints.
SqlPermQuery - Retrieves object permissions.

Note: You can also create your own queries (see Creating Queries to Retrieve Additional Attributes).

Each type of query has the same basic structure comprised of a comma-separated list of PowerDesigner variables enclosed in curly braces { } followed by a select statement to extract values to populate these variables. The values of the returned records are stored in these variables, which are then committed as object attribute values.

For example, the SqlListQuery in the View category of Oracle 11g R1 extracts values for eight variables:

{OWNER, VIEW, VIEWSTYLE, ExtObjViewType, 
    ExtObjOIDList, ExtObjSuperView, XMLSCHEMA EX, XMLELEMENT EX}

select
   v.owner,
   v.view_name,
   decode (v.view_type, 'XMLTYPE', 'XML', 'View'),
   v.view_type,
   v.oid_text,
   v.superview_name,
   decode (v.view_type, 'XMLTYPE', '%SqlXMLView.'||v.owner||v.view_name||'1%', ''),
   decode (v.view_type, 'XMLTYPE', '%SqlXMLView.'||v.owner||v.view_name||'2%', '')
from sys.all_views v
[where v.owner = %.q:SCHEMA%]

Each comma-separated part of the header may contain the following:

Name of variable - [required] can be any standard PDM variable (see PDM Variables and Macros), metamodel public name (see Metamodel Concepts) or the name of an extended attribute defined under the metaclass in the Profile (see Profile Category (DBMS)).
ID - [optional] the variable is part of the identifier.
... - [optional] the variable must be concatenated for all the lines returned by the SQL query that have the same values for the ID columns. The ID and ... (ellipsis) keywords are mutually exclusive.
Value pairs - [optional] lists conversions between retrieved values and PowerDesigner values in the following format (where * means all other values):
```
(value1 = PDvalue1, value2 = PDvalue2, * = PDvalue3)
```

Example: Using ID to Define the Identifier

In this script, the identifier is defined as TABLE + ISKEY+ CONSTNAME through the use of the ID keyword:

{TABLE ID, ISPKEY ID, CONSTNAME ID, COLUMNS ...}
select
 t.table_name,
 1,
 null,
 c.column_name + ', ',
 c.column_id
from 
 systable t,
 syscolumn c
where
etc..

In the resulting lines returned by the SQL script, the values of the fourth field are concatenated in the COLUMNS field as long as these ID values are identical.

SQL Result set
Table1,1,null,'col1,'
Table1,1,null,'col2,'
Table1,1,null,'col3,'
Table2,1,null,'col4,'
In PowerDesigner memory
Table1,1,null,'col1,col2,col3'
Table2,1,null,'col4'

In the example, COLUMNS will contain the list of columns separated by commas, and PowerDesigner will process the contents to remove the last comma.

Example: Converting Value Pairs

In this example, when the SQL query returns the value 25 or 26, it is replaced by JAVA in the TYPE variable:

{ADT, OWNER, TYPE(25=JAVA , 26=JAVA)}
SELECT t.type_name, u.user_name,  t.domain_id
FROM sysusertype t, sysuserperms u
WHERE [u.user_name = '%SCHEMA%' AND]
(domain_id = 25 OR domain_id = 26) AND
t.creator = u.user_id

Creating Queries to Retrieve Additional Attributes
You can create queries to retrieve additional attributes. These attributes could be added to SqlAttrQuery, but retrieving them in a separate query helps to avoid overloading that item. User-created queries are only called during reverse-engineering if their names are added to the ReversedQueries item.
Calling Sub-Queries with the EX Keyword
DBMS system tables may store information to be reversed in columns with LONG, BLOB, TEXT and other incompatible data types, which PowerDesigner cannot directly concatenate into strings.
Live Database Reverse Engineering Physical Options
During reverse engineering, physical options are concatenated in a single string statement. However, when the system tables of a database are partitioned (like in Oracle) or fragmented (like in Informix), the partitions/fragments share the same logical attributes but their physical properties like storage specifications, are stored in each partition/fragment of the database. The columns in the partitions/fragments have a data type (LONG) that allows storing larger amount of unstructured binary information.
Live Database Reverse Engineering Function-based Index
In Oracle 8i and later versions, you can create indexes based on functions and expressions that involve one or more columns in the table being indexed. A function-based index precomputes the value of the function or expression and stores it in the index. The function or the expression will replace the index column in the index definition.
Live Database Reverse Engineering Qualifiers
A qualifier allows the use of the object qualifier that is displayed in the dropdown list box in the upper left corner of the Database Reverse Engineering dialog box. You use a qualifier to select which objects are to be reverse engineered.

Parent topic: Controlling Database Generation and Reverse Engineering