Summary Chapter 6: Advanced tools 6. Introduction 6.13. Command line interface

6.13. Command line interface

The pgModeler command line interface, or simply pgmodeler-cli, encapsulates the most important pgModeler operations like export, import, diff and other auxiliary features. The ideia behind the CLI is enable the user to perform a set of operations over a model or database without the need of doing the tasks in the graphical interface. This approach can be handy when dealing with software deployment, for instance.

In order to exemplify a software deployment using the CLI, let's say you need export a model to a database server during the installation of a Java based software through the Apache Ant tool. You can easily integrate the pgmodeler-cli command to execute the export process taking the database model file as input parameter in the middle of the deployment process like this:pgmodeler-cli -ed -if demo.dbm -ca local-db.

The command line interface has dozens of parameters that can be combined with the objective to optimize operations to be executed. One important thing to observe is that the CLI accepts short and long parameters and they can appear in any order. Long parameters must have the values specified using the assignment operator =, in the other hand, short options must have values assigned by separating the parameter from its respective value by using a blank space. The complete list of parameters is described on the following tables.

General options

Parameter Description
-if, --input [FILE] Input model file (.dbm). This is mandatory for fix, export operations.
-id, --input-db [DBNAME] Input database name. This is mandatory for import operation.
-of, --output [FILE] Output file. This is mandatory for fixing model or exporting to file, png or svg.
-fm, --fix-model Try to fix the structure of the input model file in order to make it loadable again.
-ft, --fix-tries [NUMBER] Model fix tries. When reaching the maximum count the invalid objects will be discarded.
-ef, --export-to-file Export the input model to a sql script file.
-ep, --export-to-png Export the input model to a png image.
-es, --export-to-svg Export the input model to a svg file.
-ed, --export-to-dbms Export the input model directly to a PostgreSQL server.
-im, --import-db Import a database to an output file.
-df, --diff Compares a model and a database or two databases generating the SQL script to synch the latter in relation to the first.
-v, --pgsql-ver Force the PostgreSQL version of generated SQL code.
-s, --silent Silent execution. Only critical messages and errors are shown during process.
-h, --help Show the CLI help menu.

Connection options

Parameter Description
-lc, --list-conns List available connections in file connections.conf.
-ca, --conn-alias [ALIAS] Connection configuration alias to be used.
-H, --host [HOST] PostgreSQL host in which a task will operate.
-p, --port [PORT] PostgreSQL host listening port.
-u, --user [USER] PostgreSQL username.
-w, --passwd [PASSWORD] PostgreSQL user password.
-D, --initial-db [DBNAME] Connection's initial database.

PNG and SVG export options

Parameter Description
-sg, --show-grid Draws the grid in the exported image.
-sl, --show-delimiters Draws the page delimiters in the exported image.
-pp, --page-by-page Each page will be exported in a separated png image. (Only for PNG images)
-zf, --zoom [FACTOR] Applies a zoom (in percent) before export to png image. Accepted zoom interval: 5-500 (Only for PNG images)

DBMS export options

Parameter Description
-ir, --ignore-duplicates Ignores errors related to duplicated objects that eventually exist in the server.
-ic, --ignore-error-codes [CODES] Ignores additional errors by their codes. A comma-separated list of alphanumeric codes should be provided.
-dd, --drop-database Drop the database before execute a export process.
-do, --drop-objects Runs the DROP commands attached to SQL-enabled objects.
-sm, --simulate Simulates an export process by executing all steps but undoing any modification in the end.
-tn, --use-tmp-names Generates temporary names for database, roles and tablespaces when in simulation mode.

Database import options

Parameter Description
-ie, --ignore-errors Ignore all errors and try to create as many as possible objects.
-is, --import-sys-objs Import system built-in objects. This option causes the model bloating due to the importing of unneeded objects.
-ix, --import-ext-objs Import extension objects. This option causes the model bloating due to the importing of unneeded objects.
-d, --debug-mode Run import in debug mode printing all queries executed in the server.

Diff options

Parameter Description
-ct, --compare-to [DBNAME] The database used in the comparison. All the SQL code generated is applied to it.
-sd, --save-diff Save the generated diff code to output file.
-ad, --apply-diff Apply the generated diff code on the database server.
-np, --no-diff-preview Don't preview the generated diff code when applying it to the server.
-dc, --drop-cluster-objs Drop cluster level objects like roles and tablespaces.
-rv, --revoke-perms Revoke permissions already set on the database. New permissions configured in the input model are still applied.
-dm, --drop-missing Drop missing objects. Generates DROP commands for objects that are present in the input model but not in the compared database.
-fd, --force-drop-cols Force the drop of missing columns and constraints. Causes only columns and constraints to be dropped, other missing objects aren't removed.
-rn, --rename-db Rename the destination database when the names of the involved databases are different.
-nd, --no-cascade Don't drop or truncate objects in cascade mode.
-tt, --trunc-type-change Truncate tables prior to alter columns. Avoids errors related to type casting when the new type of a column isn't compatible to the old one.
-ns, --no-sequence-reuse Don't reuse sequences on serial columns. Drop the old sequence assigned to a serial column and creates a new one.
-nf, --no-force-recreation Don't force the recreation of objects. Avoids the usage of a DROP and CREATE commands to create a new version of the objects.
-nu, --no-unmod-recreation Don't recreate the unmodifiable objects. These objects are the ones which can't be changed via ALTER command.

Miscellaneous options

Parameter Description
-mt, --dbm-mime-type [ACTION] Handles the file association to .dbm files. The ACTION can be [install or uninstall].

Despite the usefulness of the command line interface there're some important limitations in the tool which need to be described. The first one, is the lack of ability to run in environments without a graphical interface like on a Linux server for instance. This limitation is related to how some of the libraries of pgModeler are implemented and the dependency with a graphical environment mainly when handling graphical operations like export to PNG or SVG. Another limitation is that only one operation is supported per execution, in other words, the parameters --export-to-file, --export-to-png, --export-to-svg, --export-to-dbms, --import-db, --diff, --fix-model and --dbm-mime-type can't appear in the same command call. Finally, the last limitation is that in any operation that produces an output file based upon the input model you are obligated to specify the input and output files and these files should have different names or the process will be aborted.

6.13.1. Command line examples

Below we have some command samples, an important note to be given is that on any operation that involves the connection to a database server you can either use the parameter --conn-alias alone or specify the entire connection configuration using the rest of the connection options (see above).

  1. Export a model to server using a connection alias:
    pgmodeler-cli --export-to-dbms --input demo.dbm --conn-alias local-db

  2. Export a model to server using the connections options:
    pgmodeler-cli --export-to-dbms --input demo.dbm --host localhost --user myuser --passwd mypass --initial-db mydb

  3. Export a model to PNG file applying a zoom factor of 200%:
    pgmodeler-cli --export-to-png --input demo.dbm --output demo.png --zoom 200

  4. Export a model to SQL script forcing the generation of code to PostgreSQL 9.6:
    pgmodeler-cli --export-to-file --input demo.dbm --output demo.sql --pgsql-ver 9.6

  5. Import a database ignoring errors and in debug mode:
    pgmodeler-cli --import-db --input-db demodb --output demo_imported.dbm --conn-alias local-db --ignore-errors --debug-mode

  6. Diff a model and a database applying the changes on the server:
    pgmodeler-cli --diff --input demo.dbm --compare-to demodb --conn-alias local-db --apply-diff

  7. Diff two databases living on different servers using connection aliases:
    pgmodeler-cli --diff --input-db pagila --compare-to demodb --conn-alias local-db --conn-alias1 remote-db --apply-diff

  8. Diff two databases living on different servers using connection parameters and saving the diff to a file:
    pgmodeler-cli --diff --input-db pagila --compare-to demodb --output diff.sql -H localhost -u postgres -w postgres -D postgres -H1 localhost -u1 postgres -w1 postgres -D1 postgres --save-diff

Sep 5, 2018 at 16:20