How Does UGO Collect Data from Source Databases?

Oracle as the Source Database Type

Unless otherwise specified, DDL information about Oracle objects is obtained using the DBMS_METADATA.get_ddl system package function.

The basic information about an object is obtained from system views, which consists of DBA views and All views. The following uses the All views as an example.

INDEX: During index collection, all constraint information is queried from the ALL_INDEXES view, filter out the indexes that are created based on primary keys or unique key constraints. (If an index is created before a constraint is created and the constraint name is the same as the index name, the index will not be collected.)

PostgreSQL as the Source Database Type

UGO uses the following method to collect each object of the source database.

• SCHEMA: Run CREATE SCHEMA schema name;
• TABLE: Query the pg_catalog.pg_attribute, pg_catalog.pg_class, pg_catalog.pg_namespace, and pg_inherits system views to obtain the table name, field name, field type, and table partition information, and run the CREATE TABLE statement. Information about generated columns, table constraints, and foreign key constraints is not collected.
• INDEX: Query the pg_indexes view to obtain the indexdef field and run the CREATE INDEX statement.
• VIEW: Query the pg_views view to obtain the definition field and run the CREATE VIEW statement.
• FUNCTION/PROCEDURE: Use pg_get_functiondef to obtain the CREATE FUNCTION and CREATE PROCEDURE statements.

  Note that pg_get_functiondef cannot process aggregate functions. User-defined aggregate functions cannot be collected.

• TRIGGER: Use pg_get_functiondef to obtain the trigger function definition, use pg_get_triggerdef to obtain the CREATE TRIGGER statement, and run the CREATE TRIGGER statement.

There are no specific methods for object DDL collection in PostgreSQL. The DDLs of some objects are obtained by querying metadata. As a result, the collected DDL information may be inconsistent with the DDL information in the source database, or even the information may be missing.

If the collected DDLs cannot meet your requirements, you can use pg_dump or a third-party database client tool to obtain object DDLs, and then convert the SQL statements.