Extension Mechanism for Live Database Reverse Engineering Queries

During reverse engineering, PowerDesigner executes queries to retrieve information from the columns of the system tables. The result of the query is mapped to PowerDesigner internal variables via the query header. When the system tables of a DBMS store information in columns with LONG, BLOB, TEXT and other incompatible data types, PowerDesigner cannot concatenate these data into strings.

You can bypass this limitation by using the EX keyword and creating user-defined queries and variables in the existing reverse engineering queries with the syntax:

%UserDefinedQueryName.UserDefinedVariableName%

These user-defined variables will be evaluated by sub-queries which you write.

In the following example, the value of OPTIONS is marked as containing a user-defined query, and we see in the body of the query that the 'global partition by range' option contains a user-defined query called :'SqlPartIndexDef', which seeks values for the variables 'i.owner' and 'i.index_name':

{OWNER, TABLE, CONSTNAME, OPTIONS EX}

select
 c.owner,
 c.table_name,
 c.constraint_name,
	...
	'global partition by range
		(%SqlPartIndexDef.'||i.owner||i.index_name||'%)', 
	...

The following graphic illustrates the process of variable evaluation during reverse engineering:

Note:

Extended queries should not be defined in the ReversedQueries item.

Step1

A query is executed to evaluate variables in a set of string statements.

If the EX keyword is present in the query header, PowerDesigner searches for user-defined queries and variables to evaluate. These user-defined variables are created to be filled with data proceeding from columns with LONG/BLOB/TEXT... data type.

You can create user-defined queries in any live database reverse engineering query. Each query must have a unique name.

Step 2

The execution of the user-defined query generates a resultset containing pairs of user-defined variable names (without %) and variable value for each of the variables as needed.

For example, in the following resultset, the query returns 3 rows and 4 columns by row:

Variable 1	1	Variable 2	2
Variable 3	3	Variable 4	4
Variable 5	5	Variable 6	6

Step 3

These values replace the user-defined variables in the original query.

The following sections explain user-defined queries defined to address reverse engineering limitations.