v1.1.0 Summary Chapter 6: Advanced tools 6. Introduction 6.7. Model validation

6.7. Model validation

The main goal of this tool is to avoid references or rules breaking during design time as well as to prevent problems when exporting the model directly to a PostgreSQL server. This mechanism simplified the way pgModeler loads and saves the model files, shortening the execution time of these two operations with the advantage that the validation creates a consistent, reliable, and almost error-free model. The validation process is divided into four steps: reference checking, name uniqueness checking, permanent relationship invalidation checking, and object's SQL checking.

The first validation, reference checking, will analyze if the objects are being created in the correct order. The basic rule when designing in pgModeler is that all objects must be created in a certain chronological order, but in many cases, the user cannot predict this situation and make links between objects without worrying about their creation order. This can generate a database model with several reference breaks, which means, objects being created before their dependencies.

In the second validation step, the name uniqueness checking, pgModeler can verify if tables, views, primary keys, unique keys, and exclude constraints have distinguished names if they reside on the same schema.

Now, the permanent relationship invalidation checking will look for the relationships that are permanently broken. A broken relationship in this context is the one that has the column propagation affected in some way so one or more columns aren't created from the reference table to the receiver one (details about column propagation mechanism can be found in section Relationships at Chapter 3. The only solution for permanent invalidation is the relocation of the relationship which means changing the creation order of the object.

The object's SQL checking is the last validation step and it is useful to verify other details on the generated SQL like stored procedures syntax, views definitions, and many other aspects not covered by the first three validation steps. An important note here is that this process will not be executed while the model does not pass in the previous validation iterations.

Despite being possible to save an invalidated model this is a risky and not recommended action since it can produce a corrupted model file that will demand fixes through the model fix tool or the command-line interface, so to avoid problems always validate your model before saving it. The validation widget on the image below encapsulates all the validation steps in a single object. The process is quite verbose which facilitates the identification of errors. After executing the validation process you can manually fix the problem based upon the errors returned or let pgModeler apply automatic fixes for you.

NOTE: The validation isn't a perfect mechanism because it can detect none of the validation problems mentioned above in stored procedures sources, custom SQL codes attached to other objects or even in default values of columns. In these cases the user is responsible for fixing these problems manually by using the switch objects ids tool (detailed in the next section) which can fix almost all these issues.
Action/Option Description
Validate This button starts a new validation process on the currently open model.
Apply fixes Let pgModeler do the automatic fixes on all problematic objects listed on the output widget. Using this button will trigger the validation process when there are errors listed on the output.
Swap ids This button triggers the dialog where it is possible to swap the IDs (creation order) of two objects. Generally, there is no need to use this dialog but in cases when you know and want an object to be created before another you can use this resource to avoid errors.
Cancel This button is used to abort a running validation process. Aborting a validation will not cause the restoration of the model's initial state because the validation can occur during the automatic fix process.
Options This button shows or hides the SQL validation options.
Output This button shows a popup menu containing two actions that handle the output of the validation process. The action Copy as text will copy the entire output in plain text format useful to report problems on pgModeler issues page at GitHub. The action Clear will clear the output widget.
SQL validation Enables the validation of the SQL code in the DBMS. This process requires the use of a pre-configured connection. SQL validation will occur only in the last step (when all objects were validated) or when there are no warnings.
Use unique temporary names
for cluster-level objects
pgModeler will generate unique and temporary names for database, role, and tablespace objects. This option avoids object duplication errors when running the SQL validation.

Mar 14, 2024 at 15:36