Live Database Reverse Engineering

The DBMS contains live database reverse engineering queries for retrieving objects (like Table, Columns, and so on) from the database.

Most queries follow the same naming pattern "Sql...Query".

Item	Description
SqlListQuery	Lists objects for selection in the Selection box. `SqlListQuery` retrieves objects and fills the reverse engineering window. Then, each of the other queries below are executed for each selected object. If `SqlListQuery` is not defined, standard functions are used to retrieve objects. `SqlAttrQuery,` `SqlOptsQuery` etc. will then be executed, if defined. `SqlListQuery` must retrieve the smallest number of columns possible as the process is memory intensive
SqlAttrQuery	Reverse engineers object attributes. `SqlAttrQuery` may be unnecessary if `SqlListQuery` can retrieve all necessary information. For example, in Sybase Adaptive Server® Anywhere 6, `TablespaceListQuery` is sufficient to retrieve all information required for use in a PDM
SqlOptsQuery	Reverse engineers physical options
SqlListChildrenQuery	Reverse engineers lists child objects, such as columns of a specific index or key, joins of a specific reference
SqlSysIndexQuery	Reverse engineers system indexes created by the database
SqlChckQuery	Reverse engineers object check constraints
SqlPermQuery	Reverse engineers object permissions

You can define additional queries to recover more attributes during live database reverse engineering. This is to avoid loading SqlListQuery with queries for retrieving attributes not supported by SqlAttrQuery, or objects not selected for reverse engineering. These additional queries must be listed in the ReversedQueries item. For example, SqlColnListQuery is used to exclusively retrieve view columns. This query has to be declared in the ReversedQueries item in order to be taken into account during reverse engineering.

Note: extended queries should not be defined in the ReversedQueries item. For more information on ReversedQueries, see Extension mechanism for live database reverse engineering queries.

Query Structure
Each column of a query result set is associated with a variable. A script header specifies the association between the columns of the result set and the variable. The values of the returned records are stored in these variables, which are then committed as object attribute values.
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.
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: Managing Generation and Reverse Engineering