6.13. Command line interface

v0.9.2 Summary Chapter 6: Advanced tools 6. Introduction 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. The options must be separated from their values (when accepted) by blank space. All options are described below:

HINT: If you're running the CLI on a system without graphical interface you'll notice that pgModeler CLI will not work this because it uses components that depends on the graphical interface to be running. Fortunately, there's a way to workaround this situation. Just append the option -platform offscreen to the list of options and pgmodeler-cli will run properly on any environment lacking graphical interface.

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.
`-et, --export-to-dict`	Export the input model to a data directory in HTML format.
`-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.

Data dictionary export options

Parameter	Description
`-sp, --splitted`	The data dictionaries are generated in separated files inside the selected output directory.
`-ni, --no-index`	Avoids the generation of the index that is used to help navigating through the data dictionary.

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.

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).

Export a model to server using a connection alias:
pgmodeler-cli --export-to-dbms --input demo.dbm --conn-alias local-db
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
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
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
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
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
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
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

Apache Ant

http://ant.apache.org