CLI version
As an alternative for Eclipse IDE plug-in, it is possible to compare database schemas by using command line. To do this, you should download pgCodeKeeper-cli application, which works in stand-alone mode.
Getting Started
First off, install Java SDK for your platform.
The latest version of cli build can be downloaded here. In the unpacked archive use files for parameter passing: pgcodekeeper-cli.sh for Linux systems and pgcodekeeper-cli.bat for Windows systems.
Operation modes
You can view the full set of parameters available for working with the program by executing the command:
./pgcodekeeper-cli.sh --help
- The console version has the following operation modes:
diff
This is the default mode, –mode DIFF command to call. It compares the two sources and generates a migration script. A dump, a pgCodeKeeper project, or a database can be used as a data source.
Example of a command for comparing two PostgreSQL databases and saving the migration script to the file.
./pgcodekeeper-cli.sh \
--db-type PG \
-o /path/to/file/diff.sql \
-s 'jdbc:postgresql://127.0.0.1:5432/test_1?user=test&password=test' \
-t 'jdbc:postgresql://127.0.0.1:5432/test_2?user=test&password=test'
–db-type PG - database type.
-s <url> - path to the source of changes.
-t <url> - path to the recipient of changes.
- o <path> - path to the file the migration script is going to be saved to. If you don’t specify this parameter, the result will be displayed in the console.
Important
You can omit the -s / –source and -t / –target indices (not <url>). If you do that, the first two arguments without an index will be used as a source and a recipient respectively.
Example of a command for comparing an MS SQL project and a database and displaying the result in the console.
pgcodekeeper-cli.bat \
--mode DIFF \
--db-type MS \
/path/to/project/ \
"jdbc:sqlserver://127.0.0.1;databaseName={master};integratedSecurity=true"
Example of a command for comparing a ClickHouse dump and a database and executing the script in the same database.
./pgcodekeeper-cli.sh \
--mode DIFF \
--db-type CH \
-r \
path/to/file/ch_dumpl.sql \
'jdbc:ch://127.0.0.1:8123/default?user=test&password=test'
Example of a result output:
SET search_path = pg_catalog;
CREATE TABLE public.t2 (
id integer NOT NULL,
c2 integer NOT NULL,
c3 integer NOT NULL
);
ALTER TABLE public.t2 OWNER TO test;
parse
–mode PARSE is a command that allows you to enable saving the database as a project. In this mode, the following commands are available:
–update-project - update of an existing project.
Example of a command for creating a new project based on this database.
./pgcodekeeper-cli.sh /
--db-type PG /
--mode PARSE /
-o /path/to/empty_rep/ /
'jdbc:postgresql://127.0.0.0:1/test?user=test&password=test'
where
–db-type <type> - database type.
-o <path> - path the project is going to be saved to.
Example of a command for updating an existing project based on this database.
./pgcodekeeper-cli.sh /
--db-type PG /
--mode PARSE /
--update-project /
-o /path/to/existing_rep/ /
'jdbc:postgresql://0.0.0.0:55001/test?user=test&password=test'
graph
–mode GRAPH is a command that allows you to enable search for dependencies of an object. In this mode, the following commands are available:
–graph-name <object_name> - name of the target object, it supports regular expressions. If this parameter is not specified, the dependencies of all objects will be displayed. For functions, the signature is part of the name.
–graph-reverse - reverse direction of search for dependencies. If this parameter is enabled, the system is searching for objects that the target object depends on. If it’s disabled, the system is searching for all the objects that depend on the target one.
–graph-depth <n> - dependencies search depth. By default, it’s 10.
–graph-filter-object <OBJECT_TYPE> - filtering of dependent objects by type, with displaying only the selected object types.
–graph-invert-filter - changing the –graph-filter-object parameter operations. The object types you select will be hidden.
Example of a command for searching for dependencies in a local project:
./pgcodekeeper-cli.sh
--db-type PG \
--mode GRAPH \
--graph-name public.t1 \
--graph-name public.t2 \
--graph-name 'public\.f1\(.*' \
--graph-depth 2 \
--graph-filter-object FUNCTION \
--enable-function-bodies-dependencies \
--src-lib-xml /path/to/rep/.dependencies \
-o result.txt \
/path/to/rep/
where
–db-type <type> - database type.
–enable-function-bodies-dependencies - search for dependencies in function bodies.
–src-lib-xml <path> - file with libraries for the project.
-o <path> - file the result is going to be recorded to. If you don’t specifiy this parameter, the output will be displayed in the console.
/path/to/rep/ - path to project or database url.
Example of a command for searching for dependencies in an MS SQL database:
pgcodekeeper-cli.bat \
--db-type MS \
--mode GRAPH \
--graph-name \\[dbo\\].\\[TABLE_1\\] \
--graph-name \\[dbo\\].\\[TABLE_2\\] \
--graph-name '.*TABLE_3.*' \
--graph-depth 2 \
--graph-filter-object FUNCTION \
--enable-function-bodies-dependencies \
-o result.txt \
"jdbc:sqlserver://127.0.0.1;databaseName={master};integratedSecurity=true"
Example of a result output:
TABLE public.t1
COLUMN public.t1.id
FUNCTION public.f1(bigint)
VIEW public.v1
VIEW public.v2
VIEW public.v3
VIEW public.v4
VIEW public.v5
FUNCTION public.f2()
VIEW public.v6
VIEW public.v7
TABLE public.t2
VIEW public.v8
insert
–mode INSERT is a command that allows you to enable gathering data from the source database taking into account the FK dependencies. In this mode, the following commands are available:
–insert-name <table_name> - name of the table the data is gathered for.
–insert-filter <filter> - condition under which the data will be gathered from the initial table.
Warning
In this mode, geometric types are wrapped in special functions with specifying the geometry type 4326.
Example of a command for gathering the data into a local file:
./pgcodekeeper-cli.sh /
--db-type PG /
--mode INSERT /
--insert-name public.t1 /
--insert-filter 'id = 1' /
-X /
-o result.txt /
'jdbc:postgresql://127.0.0.1:5432/test?user=test&password=test'
where
–db-type <type> - database type.
-X - wraps the generated script into a transaction.
-o <path> - file the result is going to be recorded to. If you don’t specifiy this parameter, the output will be displayed in the console.
Example of a command for gathering the data and executing a script in the database:
./pgcodekeeper-cli.sh /
--db-type MS /
--mode INSERT /
--insert-name '[dbo].[TABLE_1]' /
--insert-filter 'id = 1' /
-X /
-R 'jdbc:sqlserver://127.0.0.1;databaseName=testdb;user=user;password=pass' /
'jdbc:sqlserver://127.0.0.1;databaseName={master};integratedSecurity=true'
where
-R <url> - database where the resulting scrip is going to be executed.
Example of a result output:
START TRANSACTION;
INSERT INTO public.t2 (id, c2, c3)
VALUES (1, NULL, 1)
ON CONFLICT DO NOTHING;
INSERT INTO public.t1 (id, c2, c3)
VALUES (1, 1, 1)
ON CONFLICT DO NOTHING;
UPDATE public.t2 SET c2 = 1 WHERE id = 1;
COMMIT TRANSACTION;
verify (beta)
–mode VERIFY allows you to switch the work to the SQL code verification based on a set of rules. In this mode, the following commands are available:
–verify-source <path> - path to the directory or file with sources to be verified.
–verify-rule-set <path> - path to the file containing rules.
Rules are set using the file with the .properties extension which contains a list of key-value pairs. Here is the list of available keys:
Rule |
Value type |
Default value |
Description |
|---|---|---|---|
check_quotes_in_table_column |
boolean |
false |
check if there are no quotation marks in the table column name |
check_semicolon_after_simple_sql |
boolean |
false |
checking the semicolon (;) symbol after a simple sql expression in the body of a function |
check_space_after_if |
boolean |
false |
check if there is a space symbol after ‘if’ in the body of a function |
check_space_on_math |
boolean |
false |
check if there are spaces before and after mathematical expressions in the body of a function |
check_space_after_comma |
boolean |
false |
check if there is a space after a comma in the body of a function |
check_temp_table |
boolean |
false |
check if non-temp tables are created in the body of a function |
check_indents |
boolean |
false |
check if there are indents in the body of a function (in development) |
indent_size |
integer |
2 |
indent value in the body of a function, used with check_indents (in development) |
cyclomatic_complexity |
integer |
-1 |
maximum allowed code nesting in the body of a function |
max_function_length |
integer |
-1 |
maximum length of the body of a function (in strings) |
max_function_params |
integer |
-1 |
maximum number of input parameters of a function (excluding OUT parameters) |
method_count |
integer |
-1 |
maximum allowed number of expressions in a function |
deny_grant |
list |
- |
list of users who are not allowed to be granted rights |
allowed_function_start |
list |
- |
list of allowed values with which a function can start and a check if there is a syllabification at the beginning of a function |
Example of a file containing settings
check_case_without_else = true
check_quotes_in_table_column = true
check_semicolon_after_simple_sql = true
check_space_after_if = true
check_space_on_math = true
check_space_after_comma = true
check_temp_table = true
cyclomatic_complexity = 5
max_function_length = 150
max_function_params = 4
method_count = 40
allowed_function_start = $$, $_$, $BODY$
deny_grant = Public, user0
Example of a command used to verify code:
./pgcodekeeper-cli.sh \
--mode verify \
--verify-rule-set rules.properties \
--verify-source file1.sql \
--verify-source file2.sql \
--verify-source dir
Example of a result output
file1.sql line 1:1 A function have 7 parameters. There should be no more than 4 input parameters.
file1.sql line 20:5 Using the case block without the else block is not allowed.
file1.sql line 39:12 Creating only a temporary table is allowed.
file1.sql line 48:9 There should be no quotation marks in the table column name.
file1.sql line 49:9 There should be no quotation marks in the table column name.
file1.sql line 34:7 Warning: Not space after 'if' keyword.
file1.sql line 42:6 There must be spaces between math expressions.
file1.sql line 42:7 There must be spaces between math expressions.
file1.sql line 43:6 There must be spaces between math expressions.
file1.sql line 43:7 There must be spaces between math expressions.
file1.sql line 43:8 There must be spaces between math expressions.
file1.sql line 43:11 There must be spaces between math expressions.
file1.sql line 43:12 There must be spaces between math expressions.
file1.sql line 43:16 There must be spaces between math expressions.
file1.sql line 50:48 Warning: Not space after comma.
file1.sql line 12:12 Warning: Function body must be start on: [$$, $_$, $body$].
file1.sql line 1:1 The method has an NCSS line count 47 expressions. There should be no more than 40 expressions.
vmargs
All parameters after -vmargs will be transferred to VM.
Usage with restrictions of the memory used:
./pgcodekeeper-cli.sh 1.sql 2.sql -vmargs -Xms256m -Xmx2g
The VM parameter ru.taximaxim.codekeeper.parser.poolsize allows you to specify the number of parser threads:
./pgcodekeeper-cli.sh 1.sql 2.sql -vmargs -Dru.taximaxim.codekeeper.parser.poolsize=5