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
Для начала необходимо установить Java SDK для вашей платформы.
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
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.sh \
--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;
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