The database import process, or reverse engineering as commonly known, consists in read the information about all database objects stored on the system catalogs (
information_schema.*) and, based on these details, generating a reliable database model.
This process works quite well in pgModeler even being experimental but sometimes, depending on the database structure, this process may fail. But, fortunately, there are some workarounds that can be used to create almost all database objects without problems. The image below shows all available options in the import dialog and their details in the following table.
|Previously configured connection used as the communication channel to the server. It is important to remember that the user configured in the selected connection must have the needed permissions to query the system catalogs or the process will certainly fail.
Import objects to the working model
|Creates all imported objects in the current working model instead of creating a new one. This is useful to create several objects from another database to the current model. The option will be enabled only when there is at least one database model being handled on Design view.
Random color for relationships
|Random colors will be assigned to imported relationships facilitating the identification of links between tables mainly in large models.
Automatically resolve dependencies
|Try to resolve some of the object's dependencies by querying the catalog when a needed object does not exist on the loaded set. In some cases, it's necessary to combine this option with the others below. This option does not apply to database-level objects like role, tablespace, and language as well for data types and extensions.
Import system objects
|Enables the import of system built-in objects. It's recommended to select only those objects that are directly referenced by the ones to be imported. It's important to note that trying to import a huge set of system objects can bloat the resultant model or even crash pgModeler due to memory/CPU overuse.
Import extension objects
|Enables the import of objects created by extensions. Generally, there is no need to check this option but if there are objects in the database that directly references this category of objects this mode must be enabled.
Ignore import errors
|pgModeler ignores import errors and will try to create as many as possible objects. By checking this option the import operation will not be aborted but an incomplete model will be constructed. This option generates a log file on pgModeler's temp directory.
|All catalog queries as well as the created objects' source code are printed to standard output (stdout).
|This set of fields lists all available databases on the server. The tree view right below the database listing is used to organize the objects directly linked to the selected item above. This view is automatically updated whenever the user changes the working database.
|The user can use this to filter the tree view elements. As the user types the name of objects the view is updated. This is useful when you intend to import only a subset of objects and don't remember where exactly the objects are on the database, so you can filter them by name and select the occurrences as they appear.
|This is a modifier for the
Filter field. When checked this option causes the filter to match elements on the tree view by the OID (object identifier) instead of the object's name.
|The four command buttons at the bottom of the
Database group are respectively used to select all objects on the tree view, clear the current object selection, expand all items, and collapse all items.
In order to start an import process you first need to select the connection to be used in order to establish a new communication to the server. Once connected, all available databases will be listed in the combo box in the
Database group. Select the desired database and wait for the tree view to be updated with the database objects. Define the desired import options and click Import. The process can be a bit little slow depending on the number of objects to be imported. Once finished, the dialog is automatically closed and the generated database model is loaded.
The object filtering in the reverse engineering dialog allows the user to provide filtering patterns that force pgModeler to list only those items matching the defined criteria, importing only a small subset of the database. This feature comes in handy mainly when you need to work with a few objects that reside in a huge database avoiding the importing of the entire set of objects.
In older releases, the user was able to work with subsets of the database but there was the need to list all objects first and then select only those to be imported. This could consume unnecessary time. By design, reverse engineering is not a quick operation since it demands lots of queries on the PostgreSQL's system catalog, and depending on the size of the database those queries can take a lot of time to retrieve all objects information, list them o the tree, and only after that allow the user to select the desired ones. So thinking of optimizing this process of listing and selecting objects the filters were introduced.
The image above shows the
demodb database containing ~50 objects. So listing everything first before selecting only those objects to import would be a waste of computational resources and time. So, here's where the filters enter. In the image below, there're two filters specified, and once applied they could retrieve only 4 in just a small fraction of the time that it could take to bring all the objects thus selecting only just a few. The available controls and options of the filtering widget are detailed below.
|Add a new entry in the filters grid.
|Remove all filters already configured. In order to erase a filter individually click the icon
|This button toggles the popup menu with options to tweak the filtering. The available options are
Match by signature,
Only matching, and
|This button applies lists only the objects matching the filters in the grid inside the group
Match by signature
|This option, checked by default, causes the patterns to be matched against object signatures instead of their names only. The signature of an object is composed of the schema name (if applicable), the name, and the parameters data type (only for objects that handle parameters like functions and procedures). So, examples of signatures could be
schema.function(param_type, param_type1) and so on.
|This option causes objects strictly matching filters to be listed.
|This one is a special filter option that causes table children objects (constraints, indexes, triggers, rules, and policies) to be listed when there's the presence of at least one table/view/foreign table filter. This is useful, for instance, when you need to import just a small set of tables and their peers (defined by foreign keys). It's important to say that this special action is taken only for the tables matching the filters. Clicking the button will raise a menu containing the children types that you want to force the listing.
In the filters grid, the column
Object indicates the object type that the filter in that row applies to. The
Pattern column is a string that is matched against the object names or signatures. The
Matching is the mode in which the pattern is compared with the names and signatures. There're two possible modes:
Regexp. Basically, the matching modes are the indicators of what SQL matching will be performed when querying the system catalog. The
Wildcard operates similarly to the
LIKE operator in SQL but instead of a
% for wildcard character, we use a
Regexp is the alias to the regular expression matching operator
~*. It's important to note that all comparisons are case insensitive.