This is an automated email from the git hooks/post-receive script. sebastic pushed a commit to branch master in repository pg_comparator.
commit bff313356dd898d6fa9c0325773efa2ac5cc297c Author: Bas Couwenberg <[email protected]> Date: Fri Jul 7 09:21:57 2017 +0200 New upstream version 2.3.0 --- INSTALL | 3 +- LICENSE | 2 +- Makefile | 16 ++-- README.pg_comparator | 6 +- README.pgc_casts | 7 -- README.pgc_checksum | 20 ----- README.xor_aggregate | 9 --- pg_comparator | 158 ++++++++++++++++++++++++++++++------- pgc_checksum.sql.in | 39 --------- pgc_casts.sql.in => pgcmp--3.0.sql | 92 ++++++++++++++++++++- pgcmp.c | 4 + xor_aggregate.sql | 34 -------- 12 files changed, 234 insertions(+), 156 deletions(-) diff --git a/INSTALL b/INSTALL index 4ce1b4b..cc077b1 100644 --- a/INSTALL +++ b/INSTALL @@ -4,8 +4,7 @@ With version 8.0 or better, simply use the pgxs GNU makefile provided: sh> make install - -You must only insure that the "pg_config" found in your path is the one +You must only ensure that the "pg_config" found in your path is the one of the target postgresql server, and that development packages are installed. Then load the needed extensions (casts, checksum functions, xor aggregate): diff --git a/LICENSE b/LICENSE index 4eff9fb..c634949 100644 --- a/LICENSE +++ b/LICENSE @@ -1,6 +1,6 @@ pg_comparator is distributed under the terms of the BSD License: -Copyright (c) 2004-2015, Fabien Coelho <fabien at coelho dot net> +Copyright (c) 2004-2017, Fabien Coelho <fabien at coelho dot net> All rights reserved. Redistribution and use in source and binary forms, with or without diff --git a/Makefile b/Makefile index fc33c46..8bfa8a1 100644 --- a/Makefile +++ b/Makefile @@ -1,4 +1,4 @@ -# $Id: Makefile 1528 2014-08-04 07:09:24Z coelho $ +# $Id: Makefile 1557 2015-08-17 07:44:52Z coelho $ # # PostgreSQL stuff @@ -6,14 +6,12 @@ name = pg_comparator +EXTENSION = pgcmp SCRIPTS = $(name) -MODULES = pgc_checksum pgc_casts -DATA_built = $(MODULES:%=%.sql) -DATA = xor_aggregate.sql -DOCS = README.$(name) \ - README.xor_aggregate \ - README.pgc_checksum \ - README.pgc_casts +MODULES = $(EXTENSION) +DATA_built = $(name) +DATA = pgcmp--3.0.sql +DOCS = README.$(name) EXTRA_CLEAN = $(name).1 $(name).html pod2htm?.tmp @@ -33,7 +31,7 @@ $(name).html: $(name) touch -r $< $@ # dependencies -pgc_checksum.o: jenkins.c fnv.c +pgcmp.o: jenkins.c fnv.c pgsql_install: install pgsql_uninstall: uninstall diff --git a/README.pg_comparator b/README.pg_comparator index 61b4036..034bb9d 100644 --- a/README.pg_comparator +++ b/README.pg_comparator @@ -1,3 +1,7 @@ -to get help about pg_comparator, do: +To get help about pg_comparator, try: sh> pg_comparator --man + +To load necessary extensions into PostgreSQL: + + psql> CREATE EXTENSION pgcmp; diff --git a/README.pgc_casts b/README.pgc_casts deleted file mode 100644 index 30bf3dc..0000000 --- a/README.pgc_casts +++ /dev/null @@ -1,7 +0,0 @@ -provide useful casts for pg_comparator: - - bytea to/from bit & varbit, - - bit & varbit to int2. - -load with: - - sh> psql < <path-to-postgresql>/share/contrib/pgc_casts.sql diff --git a/README.pgc_checksum b/README.pgc_checksum deleted file mode 100644 index 72a5b89..0000000 --- a/README.pgc_checksum +++ /dev/null @@ -1,20 +0,0 @@ -provide fast NOT cryptographycally-secure checksum functions -for TEXT, results being of INT2, INT4 and INT8 types. -The cksum* functions are based on Jenkins hash. -The fnv* functions are based on FNV version 1a hash. - -load with: - - sh> psql < <path-to-postgresql>/share/contrib/pgc_checksum.sql - -use as: - - psql> SELECT cksum2('some text'); - psql> SELECT cksum4('some text'); - psql> SELECT cksum8('some text'); - psql> SELECT fnv2('some text'); - psql> SELECT fnv4('some text'); - psql> SELECT fnv8('some text'); - -For cksum, an NULL text results in hash value 0 and -an empty text results in some predefined value. diff --git a/README.xor_aggregate b/README.xor_aggregate deleted file mode 100644 index 729b246..0000000 --- a/README.xor_aggregate +++ /dev/null @@ -1,9 +0,0 @@ -provide xor-aggregate function for INT2, INT4, INT8 and BIT types. - -load with: - - sh> psql < <path-to-postgresql>/share/contrib/xor_aggregate.sql - -use as: - - pgsql> SELECT ..., XOR(column_name) FROM ... GROUP BY ...; diff --git a/pg_comparator b/pg_comparator index 66bff82..ba05fee 100755 --- a/pg_comparator +++ b/pg_comparator @@ -1,6 +1,6 @@ -#!/usr/bin/perl +#!/usr/bin/env perl # -# $Id: pg_comparator.pl 1540 2015-04-18 06:23:47Z coelho $ +# $Id: pg_comparator.pl 1569 2017-07-07 04:28:00Z coelho $ # # HELP 1: pg_comparator --man # HELP 2: pod2text pg_comparator @@ -227,6 +227,10 @@ Default is B<text> because it is faster. Show option summary. +=item C<--pg-text-cast> + +With PostgreSQL add explicit TEXT casts to work around some typing issues. + =item C<--pg-copy=128> Experimental option to use PostgreSQL's COPY instead of INSERT/UPDATE @@ -466,10 +470,10 @@ The possibly schema-qualified table to use for comparison. No default for first connection. Default is same as first connection for second connection. -Note that MySQL does not have I<schemas>, but strangely enough -their I<database> concept is just like a I<schema>, -so MySQL really does not have I<databases>, although there is -something of that name. Am I clear? +Note that MySQL does not have I<schemas>, so the schema part must be empty. +However, strangely enough, their I<database> concept is just like a +I<schema>, so one could say that MySQL really does not have I<databases>, +although there is something of that name. Am I clear? =item B<keys> @@ -575,6 +579,75 @@ In case of tuple checksum collisions, false negative results may occur. Changing the checksum function would help in such cases. See the ANALYSIS sub-section. +=head1 INSTALL + +This section describes how to install extensions (functions, casts, aggregates) +needed by pg_comparator for the different target databases. + +First, get pg_comparator +L<sources|http://www.coelho.net/pg_comparator/pg_comparator-2.3.0.tgz>. + +=head2 PostgreSQL + +For installing on PostgreSQL, you must ensure that the C<pg_config> command +found in your path is the one of the target PostgreSQL server, and that +development packages are installed. + +Then compile and install the extensions' shared objects: + + sh> make pgsql_install + +To load the extension files into the target C<DB> database, +where C<...> are the connection options: + + sh> psql ... -c 'CREATE EXTENSION pgcmp' DB + +To uninstall: + + sh> psql ... -c 'DROP EXTENSION pgcmp' DB + sh> make pgsql_uninstall + +=head2 MySQL + +For installing on MySQL, you must ensure that the C<mysql_config> command +found in your path is the one of the target MySQL server, and that +development packages are installed. + +Then compile and install the extensions' shared objects: + + sh> make mysql_install + +And load the extension files into the database: + + sh> mysql ... < PATH-TO-EXTENSION/mysql_casts.sql + sh> mysql ... < PATH-TO-EXTENSION/mysql_checksum.sql + +See C<mysql_config --plugindir> for the extension directory path. +On some systems C<PATH-TO-EXTENSION> might be C</usr/lib/mysql/contrib>. + +To uninstall: + + sh> make mysql_uninstall + +=head2 SQLite + +For installing with SQLite, the corresponding development package is needed. + +First compile and install the extensions' shared objects (you +may adjust C<SQLITE.libdir> make variable to change the target directory, +which is by default C</usr/local/lib>): + + sh> make sqlite_install + +Then load the extension by executing (to do it always, you may +append the line to your C<.sqliterc> file): + + SELECT load_extension('/usr/local/lib/sqlite_checksum.so'); + +To uninstall: + + sh> make sqlite_uninstall + =head1 DEPENDENCES Three support functions are needed on the database: @@ -965,9 +1038,6 @@ L<Altova Database Spy|http://www.altova.com/databasespy/> L<AUI Soft SQLMerger|http://auisoft.com/sqlmerger/> =item * -L<Citrus Tech Data Comparison|http://www.citrustechnology.com/solutions/data-comparison> - -=item * L<Clever Components dbcomparer|http://www.clevercomponents.com/products/dbcomparer/> =item * @@ -977,7 +1047,10 @@ L<Comparezilla|http://comparezilla.sourceforge.net/> L<Datanamic Datadiff|http://www.datanamic.com/datadiff/> =item * -L<DB Balance|http://www.dbbalance.com/db_comparison.htm> +L<DB Balance|http://www.dbbalance.com/db_cmp_pro.htm> + +=item * +L<DBConvert|https://dbconvert.com/postgresql/> =item * L<DBSolo datacomp|http://www.dbsolo.com/datacomp.html> @@ -989,16 +1062,10 @@ L<dbForge Data Compare|http://www.devart.com/dbforge/sql/datacompare/> L<DiffKit|http://www.diffkit.org/> =item * -L<DKGAS DBDiff|http://www.dkgas.com/dbdiff.htm> - -=item * -L<Maakit mk-table-sync|http://code.google.com/p/maatkit/> +L<Percona Toolkit|https://www.percona.com/software/mysql-tools/percona-toolkit> =item * -L<MySQL DBCompare|http://dev.mysql.com/doc/workbench/en/mysqldbcompare.html> - -=item * -L<List of SQL Server Tools|http://www.programurl.com/software/sql-server-comparison.htm> +L<MySQL DBCompare|https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqldbcompare.html> =item * L<SQL Server tablediff Utility|http://msdn.microsoft.com/en-US/library/ms162843.aspx> @@ -1013,13 +1080,16 @@ L<Spectral Core OmegaSync|http://www.spectralcore.com/omegasync/>, L<SQL Delta|http://www.sqldelta.com/> =item * +L<SQLite sqldiff|https://www.sqlite.org/sqldiff.html> + +=item * L<AlfaAlfa SQL Server Comparison Tool|http://www.sql-server-tool.com/> =item * L<SQLyog MySQL GUI|http://www.webyog.com/> =item * -L<xSQL Software Data Compare|http://www.xsqlsoftware.com/Product/Sql_Data_Compare.aspx> +L<xSQL Software Data Compare|http://www.xsql.com/products/sql_server_data_compare/> =back @@ -1121,19 +1191,35 @@ cannot use pg_comparator to compare table contents on a synchronized replica. Allow larger checksum sizes. -Make it a PostgreSQL extension. - Add an option to avoid IN (x,y,...) syntax, maybe with a temporary table to hold values and use a JOIN on that. I'm not sure about the performance implications, though. +Allow to generate the SQL update script without applying it. + +Option to generate more compact updates, i.e. only update attributes with +different values. + =head1 VERSIONS -See L<PG Foundry|http://pgfoundry.org/projects/pg-comparator/> for the latest -version. My L<web site|http://www.coelho.net/pg_comparator/> for the tool. +See L<web site|http://www.coelho.net/pg_comparator/> for the latest version. +Although versions are really managed with SVN, there is also a +L<github repos|https://github.com/zx80/pg_comparator>. =over 4 +=item B<version 2.3.0> (r1569 on 2017-07-07) + +Add new L</"INSTALL"> Section. +Turn cast, functions and aggregates into a PostgreSQL extension. +Fix C<--where> handling when C<--tcs> is used, reported by I<Kenneth Hammink>. +Add C<--pg-text-cast> option to work around missing implicit casts, issue +reported by Saulius Grigaitis. +Documentation updates. + +The I<release> validation was run successfully +on PostgreSQL 9.6.3 and MySQL 5.7.18. + =item B<version 2.2.6> (r1540 on 2015-04-18) Fix some typos found by Lintian and pointed out by I<Ivan Mincik>. @@ -1386,7 +1472,7 @@ as suggested by I<Erik Aronesty>. =item B<version 1.3> (r239 on 2004-08-31) -Project moved to L<PG Foundry|http://pgfoundry.org/>. +Project moved to PG Foundry. Use cksum8 checksum function by default. Minor doc updates. @@ -1412,7 +1498,7 @@ Initial revision. =head1 COPYRIGHT -Copyright (c) 2004-2015, I<Fabien Coelho> +Copyright (c) 2004-2017, I<Fabien Coelho> <pg dot comparator at coelho dot net> L<http://www.coelho.net/> This software is distributed under the terms of the BSD Licence. @@ -1427,8 +1513,8 @@ saying so. See my webpage for current address. =cut -my $script_version = '2.2.6 (r1540)'; -my $revision = '$Revision: 1540 $'; +my $script_version = '2.3.0 (r1569)'; +my $revision = '$Revision: 1569 $'; $revision =~ tr/0-9//cd; ################################################################# SOME DEFAULTS @@ -1443,7 +1529,7 @@ my ($factor, $expect_warn) = (7, 0); my ($skip_inserts, $skip_updates, $skip_deletes) = (0, 0, 0); # condition, tests, max size of blobs, data sources... my ($expect, $longreadlen, $source1, $source2, $key_cs, $tup_cs, $do_lock, - $env_pass, $max_report, $stats, $pg_copy); + $env_pass, $max_report, $stats, $pg_copy, $pg_text_cast); # algorithm defaults # hmmm... could rely on base64 to handle binary keys? @@ -1567,9 +1653,18 @@ sub firebird_null_template($$$) { die "unexpected null $null"; } +sub text_cast($) { + my ($list) = @_; + my @l = (); + for my $i (@$list) { + push @l, "(($i)::TEXT)"; + } + return @l; +} + sub bb_concat($$) { my ($sep, $list) = @_; - return join("||'$sep'||", @$list); + return join("||'$sep'||", ($pg_text_cast? text_cast($list): @$list)); } sub mysql_concat($$) { @@ -2567,6 +2662,8 @@ sub compute_summary($$$$$$@) &{$M{$db}{andop}}($kcs, $masks[$level]) . " AS kcs, " . $M{$db}{$agg} . "(${tcs}) AS tcs " . "FROM ${from} " . + # apply where only now, if T0 was not built + ($tup_cs && $where && $level == 1? "WHERE $where ": "") . # the "& mask" is really a modulo operation "GROUP BY " . &{$M{$db}{andop}}(${kcs}, $masks[$level]); if ($M{$db}{create_as}) { @@ -2922,7 +3019,8 @@ GetOptions( # misc "long-read-len|lrl|L=i" => \$longreadlen, "version|V" => sub { print "$0 version is $script_version\n"; exit 0; }, - "pg-copy:i" => \$pg_copy + "pg-copy:i" => \$pg_copy, + "pg-text-cast" => \$pg_text_cast ) or die "$! (try $0 --help)"; # propagate expect specification diff --git a/pgc_checksum.sql.in b/pgc_checksum.sql.in deleted file mode 100644 index 533e5d0..0000000 --- a/pgc_checksum.sql.in +++ /dev/null @@ -1,39 +0,0 @@ --- $Id: pgc_checksum.sql.in 1520 2014-08-03 11:27:06Z coelho $ - -LOAD 'MODULE_PATHNAME'; - -CREATE OR REPLACE FUNCTION cksum2(TEXT) -RETURNS INT2 -LANGUAGE C -CALLED ON NULL INPUT -AS 'MODULE_PATHNAME', 'text_checksum2'; - -CREATE OR REPLACE FUNCTION cksum4(TEXT) -RETURNS INT4 -LANGUAGE C -CALLED ON NULL INPUT -AS 'MODULE_PATHNAME', 'text_checksum4'; - -CREATE OR REPLACE FUNCTION cksum8(TEXT) -RETURNS INT8 -LANGUAGE C -CALLED ON NULL INPUT -AS 'MODULE_PATHNAME', 'text_checksum8'; - -CREATE OR REPLACE FUNCTION fnv2(TEXT) -RETURNS INT2 -LANGUAGE C -CALLED ON NULL INPUT -AS 'MODULE_PATHNAME', 'text_fnv2'; - -CREATE OR REPLACE FUNCTION fnv4(TEXT) -RETURNS INT4 -LANGUAGE C -CALLED ON NULL INPUT -AS 'MODULE_PATHNAME', 'text_fnv4'; - -CREATE OR REPLACE FUNCTION fnv8(TEXT) -RETURNS INT8 -LANGUAGE C -CALLED ON NULL INPUT -AS 'MODULE_PATHNAME', 'text_fnv8'; diff --git a/pgc_casts.sql.in b/pgcmp--3.0.sql similarity index 50% rename from pgc_casts.sql.in rename to pgcmp--3.0.sql index 74f11a1..92d1c86 100644 --- a/pgc_casts.sql.in +++ b/pgcmp--3.0.sql @@ -1,6 +1,49 @@ --- $Id: pgc_casts.sql.in 1022 2010-08-06 07:28:07Z fabien $ +-- +-- $Id: pgcmp--3.0.sql 1554 2015-08-17 07:22:40Z coelho $ +-- -LOAD 'MODULE_PATHNAME'; +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION pgcmp" to load this file. \quit + +-- +-- XOR AGGREGATE +-- + +-- default behavior for strict functions used: NULLs are ignored + +DROP AGGREGATE IF EXISTS XOR(bit); +CREATE AGGREGATE XOR( + BASETYPE = BIT, + SFUNC = bitxor, + STYPE = BIT +); + +DROP AGGREGATE IF EXISTS XOR(INT2); +CREATE AGGREGATE XOR( + BASETYPE = INT2, + SFUNC = int2xor, + STYPE = INT2 +); + +DROP AGGREGATE IF EXISTS XOR(INT4); +CREATE AGGREGATE XOR( + BASETYPE = INT4, + SFUNC = int4xor, + STYPE = INT4 +); + +DROP AGGREGATE IF EXISTS XOR(INT8); +CREATE AGGREGATE XOR( + BASETYPE = INT8, + SFUNC = int8xor, + STYPE = INT8 +); + +-- +-- CASTS +-- + +-- LOAD 'MODULE_PATHNAME'; CREATE OR REPLACE FUNCTION varbit(BYTEA, INT, BOOL) RETURNS VARBIT @@ -39,9 +82,8 @@ LANGUAGE C IMMUTABLE STRICT AS 'MODULE_PATHNAME', 'varbittoint2'; - -- no data loss, very similar types --- AS IMPLICIT? +-- AS IMPLICIT would be ok? DROP CAST IF EXISTS (BYTEA AS VARBIT); CREATE CAST (BYTEA AS VARBIT) WITH FUNCTION varbit(BYTEA, INT, BOOL); @@ -68,3 +110,45 @@ WITH FUNCTION varbit2int2(VARBIT, INT, BOOL); DROP CAST IF EXISTS (BIT AS INT2); CREATE CAST (BIT AS INT2) WITH FUNCTION bit2int2(BIT, INT, BOOL); + +-- +-- CHECKSUMS +-- + +LOAD 'MODULE_PATHNAME'; + +CREATE OR REPLACE FUNCTION cksum2(TEXT) +RETURNS INT2 +LANGUAGE C +CALLED ON NULL INPUT +AS 'MODULE_PATHNAME', 'text_checksum2'; + +CREATE OR REPLACE FUNCTION cksum4(TEXT) +RETURNS INT4 +LANGUAGE C +CALLED ON NULL INPUT +AS 'MODULE_PATHNAME', 'text_checksum4'; + +CREATE OR REPLACE FUNCTION cksum8(TEXT) +RETURNS INT8 +LANGUAGE C +CALLED ON NULL INPUT +AS 'MODULE_PATHNAME', 'text_checksum8'; + +CREATE OR REPLACE FUNCTION fnv2(TEXT) +RETURNS INT2 +LANGUAGE C +CALLED ON NULL INPUT +AS 'MODULE_PATHNAME', 'text_fnv2'; + +CREATE OR REPLACE FUNCTION fnv4(TEXT) +RETURNS INT4 +LANGUAGE C +CALLED ON NULL INPUT +AS 'MODULE_PATHNAME', 'text_fnv4'; + +CREATE OR REPLACE FUNCTION fnv8(TEXT) +RETURNS INT8 +LANGUAGE C +CALLED ON NULL INPUT +AS 'MODULE_PATHNAME', 'text_fnv8'; diff --git a/pgcmp.c b/pgcmp.c new file mode 100644 index 0000000..facf88c --- /dev/null +++ b/pgcmp.c @@ -0,0 +1,4 @@ +// $Id: pgcmp.c 1554 2015-08-17 07:22:40Z coelho $ +#include "pgc_casts.c" +#undef PG_MODULE_MAGIC +#include "pgc_checksum.c" diff --git a/xor_aggregate.sql b/xor_aggregate.sql deleted file mode 100644 index bd00e62..0000000 --- a/xor_aggregate.sql +++ /dev/null @@ -1,34 +0,0 @@ --- $Id: xor_aggregate.sql 1141 2012-08-09 12:22:18Z fabien $ --- --- add XOR aggregate to PostgreSQL --- - --- default behavior for strict functions used: NULLs are ignored... - -DROP AGGREGATE IF EXISTS XOR(bit); -CREATE AGGREGATE XOR( - BASETYPE = BIT, - SFUNC = bitxor, - STYPE = BIT -); - -DROP AGGREGATE IF EXISTS XOR(INT2); -CREATE AGGREGATE XOR( - BASETYPE = INT2, - SFUNC = int2xor, - STYPE = INT2 -); - -DROP AGGREGATE IF EXISTS XOR(INT4); -CREATE AGGREGATE XOR( - BASETYPE = INT4, - SFUNC = int4xor, - STYPE = INT4 -); - -DROP AGGREGATE IF EXISTS XOR(INT8); -CREATE AGGREGATE XOR( - BASETYPE = INT8, - SFUNC = int8xor, - STYPE = INT8 -); -- Alioth's /usr/local/bin/git-commit-notice on /srv/git.debian.org/git/pkg-grass/pg_comparator.git _______________________________________________ Pkg-grass-devel mailing list [email protected] http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/pkg-grass-devel
