5.3. Generate differences scripts

v0.9.2 Summary Chapter 5: Export, import and diff 5. Introduction 5.3. Generate differences scripts

The comparison or diff process in pgModeler consists in generating a set of SQL commands that will make both model and database synchronized. In most recent versions of the tool there's a possibility to compare two databases as well. In details, the comparison is made from model to database, meaning that any object existent in model and absent in the database will be created. In the same analogy, any object absent in the model and present in the database will be destroyed in the latter. The key idea here is that the model is the main reference meaning that any change made to the database that is not in the former is completely ignored. This is useful to keep the model always up to date because the user needs to alter first that one and then apply the changes to the database.

The process itself is quite simple, first pgModeler will generate a temporary model based upon the database to be compared using the reverse engineering feature. Once generated the model it's time to compare the objects from the input model (the reference) and the ones present in the temporary one. Any detected difference is stored and processed later resulting in a set of SQL commands that may create new objects, destroy old ones or just alter the structure of the current ones. When the comparison finishes, the commands are ready to be applied to the database. In this moment, depending on the settings perfomed by the user, the generated commands will be saved in a file or exported directly to the server. In case of exporting to the server, the user will be prompted to proceed with the irreversible changes to be done to the database. Before confirming the modifications it is highly recommended to make a backup of the database in case of something goes wrong.

In newer versions the support to diff presets was added and it allows the user to save different settings and reuse them whenever needed. This is useful when you need to run the diff process in every stage of a software development, let's say, development, homologation and production. On each of these stages you may use different diff settings depending on your needs. For instance, in development and homologation you can force the recreation of objects or even drop objects in cascade mode, but the same options can't be used in production stage for security reasons. So, with this feature, pgModeler allows you to flexibilize the options used in the diff for different contexts.

Finally, the model-database diff dialog has a long set of options and they may affect how the resulting commands are generated as well their semantics. You seldom need to change their values but in some few cases the default settings can't generate the diff as expected so you'll need to tweak them to achieve the desired result. Below all available options are fully described. This feature is still in experimental state and may produce false-positive results or extra/redundant code in the final code, but these issues are being solved little by little. Another limitation of the diff process is that object's renaming in the model can't be detected and instead of issuing an ALTER ... RENAME command on the diff script the object with the old name will be dropped, so be extremely careful when doing diffs!

Presets

Option/Field	Description
`Presets list`	This combo box lists the loaded diff presets.
`Add new preset`	Triggered by or by hitting `Ins` this action starts the saving of a new preset based on the currently selected options checked in the dialog.
`Edit preset name`	Triggered by or by hitting `Ctrl + E` this action is used to rename the selected preset.
`Save preset`	Triggered by or by hitting `Ctrl + S` this action saves the currently selected options in the form to the selected preset.
`Delete selected preset`	Triggered by or by hitting `Del` this action deletes the currently selected preset.
`Restore default presets`	Triggered by or by hitting `Ctrl + R` this action restore the default presets causing all user-defined preset to be deleted.

Source database

Option/Field Description

Current model The working database model used as input of the diff process.

Database If you intend to compare two databases instead of a model and a database you can select this option and choose a connection on the field Connectionto browse the desired database. Once connected to the server the combo box Database will list all available databases to be used in the comparison. Note that the user configured in the selected connection must have the needed permissions to query the system catalogs and manage objects or the process will certainly fail.

Option/Field	Description
`Current model`	The working database model used as input of the diff process.
`Database`	If you intend to compare two databases instead of a model and a database you can select this option and choose a connection on the field `Connection`to browse the desired database. Once connected to the server the combo box `Database` will list all available databases to be used in the comparison. Note that the user configured in the selected connection must have the needed permissions to query the system catalogs and manage objects or the process will certainly fail.

Compare to

Option/Field	Description
`Connection`	Previous configured connection used as communication channel to the server where to look for the database compared against the input model or database. This connection also needes to be configured with a user with the needed permissions to query the system catalogs and manage objects.
`Database`	The database selected here is the one that will be compared to the model or database selected in the `Source database` field.

Diff mode

Option/Field	Description
`Use PostgreSQL`	Override the PostgreSQL version when generating the diff code. The default is to use the same version as the input database (detected automatically).
`Store in SQL file`	Compares the model and the input database storing the diff in a SQL file for later usage.
`Apply on server`	Compares the model and the input database generating a diff and applying it directly to the latter. This mode causes irreversible changes on the database and in case of failure the original structure is not restored, so make sure to have a backup before proceed.

Diff options

Option/Field	Description
`Keep cluster objects`	Database cluster level objects like roles and tablespaces will not be dropped.
`Keep object's permissions`	Permissions already set on database objects will be kept. The ones configured on the model will be applied to the database.
`Do not drop missing objects`	Avoid the generation of `DROP` commands for objects that exist in database but not in the model. This is useful when diff a partial model against the complete database.
`Drop missing columns and constraints`	Force the generation of `DROP` commands for columns and constraints that exist in database but not in the model. This is useful when diff a partial model against the complete database and the user needs to drop columns and constraints but preserves the rest of the objects.
`Preserve database name`	No command to rename the destination database will be generated even the model's name differ from database name.
`Drop or truncate in cascade mode`	For `DROP` command, the objects that depend on an object to be dropped will be deleted as well. For `TRUNCATE` command, tables that are linked to a table to be truncated will be truncate too. This option can affect more objects than listed in the output or diff preview.
`Truncate tables before alter columns`	Clears the data of all tables which will have columns modified. This is useful to avoid errors related to type casting. Do not use this option on production servers and always make a backup before use it.
`Reuse sequences on serial columns`	Serial columns are converted to integer and having the default value changed to `nextval(sequence)` function call. By default, a new sequence is created for each serial column but checking this option sequences matching the name on column's default value will be reused and will not be dropped.
`Force recreation of objects`	Instead of use an `ALTER` command to modify certain kind of objects a `DROP` and `CREATE` will be used in order to do a full modification. This option does not affect the database object.
`Recreate only unmodifiable objects`	Recreates only objects that can't be changed through `ALTER` commands according to pgModeler implementation not the PostgreSQL one. Currently, those objects are: aggregate, cast, constraint, collation, conversion, language, operator, operator class, operator family, rule, trigger and view.

The options available in the tab Import & Export are the same ones used by the import and export processes so the details about them can be seen in the previous sections in this chapter. After configure the desired options you can start a diff process by hitting the button Generate. The whole process can take a long time due to the amount of the comparisons done and the size of the involved model and database. Unlike the import, the diff dialog will not be closed automatically at the end of the process this because the user can compare other databases to the model using the current settings.