viewvc.cgi/pgdiff/pgdiff

#!/usr/bin/perl -w

# Compare data in two different databases
#
# 2003-08-07 Dobrica Pavlinusic
#
# somewhat based on ides from
# DataDiff 0.1 by Jon D. Frisby, http://www.mrjoy.com

use strict;
use Getopt::Long;
use DBI;
use Data::Dumper;

$| = 1;

my ($debug,$verbose) = (0,0);
my ($help,$masterhost,$masterport,$masteruser,$masterpassword,
        $slavehost,$slaveport,$slaveuser,$slavepassword,
        $masterfile,
        $slavefile,
        );
my $tables;

my $result = GetOptions(
        "debug!" => \$debug, "verbose!" => \$verbose, "help" => \$help,
        "masterhost=s" => \$masterhost, "masterport=i" => \$masterport,
        "masteruser=s" => \$masteruser, "masterpassword=s" => \$masterpassword,
        "masterfile=s" => \$masterfile,
        "slavehost=s" => \$slavehost, "slaveport=i" => \$slaveport,
        "slaveuser=s" => \$slaveuser, "slavepassword=s" => \$slavepassword,
        "slavefile=s" => \$slavefile,
        "tables=s" => \$tables,
        );

if (defined($help) || (scalar(@ARGV) < 2)) {
    print "Usage: $0 [options] masterdb slavedb
Options:
        --masterhost=hostname --masterport=port
        --masteruser=username --masterpassword=string
        --masterfile=filename
        --slavehost=hostname --slaveport=port
        --slaveuser=username --slavepassword=string
        --slavefile=filename
        --tables[s]=table[,table...]
";
#    exit ((scalar(@ARGV) < 2)? 1:0);
    exit;
}

my $master = $ARGV[0] || "master";
my $slave = $ARGV[1] || "slave";

my $minfo = "dbname=$master";
$minfo = "$minfo host=$masterhost" if (defined($masterhost));
$minfo = "$minfo port=$masterport" if (defined($masterport));

my $sinfo = "dbname=$slave";
$sinfo = "$sinfo host=$slavehost" if (defined($slavehost));
$sinfo = "$sinfo port=$slaveport" if (defined($slaveport));

print "Master connection is $minfo\n" if ($debug);
print "Slave connection is $sinfo\n" if ($debug);

my $mdbh = DBI->connect("DBI:Pg:$minfo", $masteruser, $masterpassword, { PrintError => 1 });
my $sdbh = DBI->connect("DBI:Pg:$sinfo", $slaveuser, $slavepassword, { PrintError => 1 });

my ($diff_shema,$diff_data) = (0,0);

my $sql;

sub debug_sql {
        return if (! $debug);
        my $sql = shift;
        $sql =~ s/[\n\r]/ /gs;
        $sql =~ s/\s\s+/ /g;
        print STDERR "DEBUG: SQL: $sql\n";
}

sub debug_row {
        return if (! $debug);
        my $row = shift;
        my @cols = @_;
        if (! $row) {
                print STDERR "DEBUG: ROW data is undef!\n";
                return;
        }
        print STDERR "DEBUG: ROW: [",$#cols+1,"] ";
        foreach my $col (@cols) {
                print STDERR "$col:";
                if ($row->{$col}) {
                        print $row->{$col};
                } else {
                        print "null";
                }
                print " ";
        }
        print STDERR "\n";
}

sub debug {
        return if (!$debug);
        print STDERR "DEBUG: ",@_;
}

$verbose = 1 if ($debug);

# which tables to compare?

my @tables;
if ($tables) {
        @tables = split(/,/,$tables);
} else {
        # take all tables
        #$sql="select tablename from pg_tables where tablename not like 'pg_%' and tablename not like '_rserv_%'";
        # show tables (based on psql \dt)
        $sql = "
        SELECT c.relname as table
        FROM pg_catalog.pg_class c
                LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind = 'r'
                AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                AND pg_catalog.pg_table_is_visible(c.oid)
                and c.relname not like '_rserv_%'
        ";
        foreach my $table (@tables) {
                $sql .= " and c.relname like '$table'";
        }
        my $sth = $mdbh->prepare($sql);
        $sth->execute() || die;
        while(my $row = $sth->fetchrow_hashref()) {
                push @tables,$row->{table};
        }
}

debug "Comparing tables: ".join(", ",@tables)."\n";

my $cols;
my $diff_total = 0;

foreach my $table (@tables) {

        my ($sth);

        # find table oid
        $sql = "
        SELECT c.oid, n.nspname, c.relname
        FROM pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE pg_catalog.pg_table_is_visible(c.oid)
        AND c.relname = '$table'
        ORDER BY 2, 3
        ";

        $sth = $mdbh->prepare($sql);
        $sth->execute() || die;
        my $row = $sth->fetchrow_hashref();
        if (! $row) {
                print STDERR "Can't find OID of table '$table'\n";
                exit 1;
        }

        my $oid = $row->{oid};

        # diff schema

        my @cols_notnull;# colums compared by a=b
        my @cols_null;  # colums compared by a=b or a is null and b is null
        my @cols_skip;  # skipped columns
        my @cols_test;  # all colums to test (without skipped)
        my @cols;       # all colums (for insert)

        my $sql="
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
a.attnotnull, a.atthasdef, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = $oid AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
";

        $sth = $mdbh->prepare($sql);
        $sth->execute() || die;
        while(my $row = $sth->fetchrow_hashref()) {
                # attname | format_type | attnotnull | atthasdef | attnum

                push @cols,$row->{attname};

                # FIXME: do something with attributes which shouldn't be compared
                # (date, time, datetime, timestamp)
                if ($row->{format_type} =~ /(date)|(time)/i) {
                        push @cols_skip,$row->{attname};
                        next;
                }

                push @cols_test,$row->{attname};

                if ($row->{attnotnull}) {
                        push @cols_notnull,$row->{attname};
                } else {
                        push @cols_null,$row->{attname};
                }
        }
        $sth->finish();

        if ($debug) {
                print STDERR "DEBUG: table $table [$oid] not null: (",join(", ",@cols_notnull),")";
                print STDERR " - null: (",join(", ",@cols_null),")" if (@cols_null);
                print STDERR " - skip: (",join(", ",@cols_skip),")" if (@cols_skip);
                print STDERR "\n";
        }

        # diff data

        my @cols_pk;    # columns which are primary key
        my %in_pk;

        $sql="
SELECT
        i.indexrelid as indexrelid, i.indrelid as indrelid,
        count(a.attname) as cols_in_pk
FROM
        pg_catalog.pg_class c,
        pg_catalog.pg_index i,
        pg_catalog.pg_attribute a
WHERE
        c.oid = i.indrelid
        and i.indisunique
        and c.relname = '$table'
        and a.attrelid = i.indexrelid
GROUP BY
        i.indexrelid, i.indrelid, c.relname, i.indisprimary, i.indisunique
ORDER BY
        cols_in_pk ASC, i.indisprimary DESC, i.indisunique DESC, c.relname DESC
";
        debug_sql($sql);
        $sth = $mdbh->prepare($sql);
        $sth->execute() || die;
        $row = $sth->fetchrow_hashref();
        if ($row) {
                $sql="
                select a1.attname as attname from pg_attribute a1, pg_attribute a2 where a1.attrelid = ".$row->{indexrelid}." and a2.attrelid=".$row->{indrelid}." and a1.attname = a2.attname and a2.attnotnull";
                
                debug_sql($sql);
                my $sth2 = $mdbh->prepare($sql);
                $sth2->execute() || die;
                @cols_pk = ();
                while (my $row2 = $sth2->fetchrow_hashref()) {
                        push @cols_pk,$row2->{attname};
                        $in_pk{$row2->{attname}}++;
                }
                
        }
        if (! @cols_pk) {
                print STDERR "can't find PK rows for table '$table' using all\n";
                @cols_pk = @cols;
        }

        my @cols_cmp;   # columns to compare

        foreach my $col (@cols_test) {
                push @cols_cmp,$col if (! $in_pk{$col});
        }

        if ($verbose) {
                print "table '$table' using for key: (",join(", ",@cols_pk),") to compare cols: (",join(", ",@cols_cmp),")\n";
        }

        # diff data

        my $msql = "select ".join(",",@cols)." from $table";
        my $ssql = $msql;

        sub sql_where {
                my @cols = @_;
                my $and = "";
                my $where = " where ";
                foreach my $col (@cols) {
                        $where .= "$and$col=?";
                        $and = " and ";
                }
                return $where;
        }

        sub sql_order {
                my @cols = @_;
                my $order = " order by ";
                my $comma = "";
                foreach my $col (@cols) {
                        $order .= "$comma$col asc";
                        $comma = ", ";
                }
                return $order;
        }

        my $order = sql_order(@cols_pk);
        $msql .= $order;
        $ssql .= $order;

        debug_sql($msql);

        my $msth = $mdbh->prepare($msql) || die;
        $msth->execute() || die;

        my $ssth = $sdbh->prepare($ssql) || die;
        $ssth->execute() || die;

        my $diff_row = 0;

        my ($mrow,$srow);
        # have_*
        use constant NO_ROW => 0;
        use constant FETCH_ROW => 1;
        use constant HAVE_ROW => 2;
        my ($have_mrow,$have_srow) = (FETCH_ROW,FETCH_ROW);

        while ($have_mrow != NO_ROW || $have_srow != NO_ROW) {

                debug "have mrow: $have_mrow srow: $have_srow\n";

                sub pk_val {
                        my $row = shift || die "need row";
                        my @cols = shift || die "need cols";
                        my @val;
                        foreach my $col (@cols) {
                                push @val,$row->{$col};
                        }
                        return @val;
                }

                # fetch row from master
                if ($have_mrow == FETCH_ROW) {
                        debug "fetch row from master: $msql\n";
                        $mrow = $msth->fetchrow_hashref();
                        debug_row($mrow,@cols);

                        if ($mrow) {
                                # fill-in primary key values
                                $have_mrow = HAVE_ROW;
                        } else {
                                $have_mrow = NO_ROW;
                        }
                }

                # fetch row from slave
                if ($have_srow == FETCH_ROW) {
                        debug "fetch row from slave: $ssql\n";
                        $srow = $ssth->fetchrow_hashref();
                        debug_row($srow,@cols);
                        if ($srow) {
                                $have_srow = HAVE_ROW;
                        } else {
                                $have_srow = NO_ROW;
                        }
                }

                debug "have mrow: $have_mrow srow: $have_srow\n";

                # insert into slave database
                sub sql_insert {
                        my $table = shift @_ || die "need table as argument";
                        my $row = shift @_ || die "need row data";
                        my @cols = @_;

                        my $sql = "insert into $table (".join(",",@cols).") values (";
                        my $comma = "";
                        foreach my $col (@cols) {
                                $sql .= $comma.$mdbh->quote($row->{$col});
                                $comma = ",";
                        }
                        $sql.=")";
                        debug_sql($sql);
                        return $sql;
                }

                # delete from slave database
                sub sql_delete {
                        my $table = shift @_ || die "need table as argument";
                        my $row = shift @_ || die "need row as argument";
                        my @cols_pk = @_;

                        my $where = sql_where(@cols_pk);

                        my $sql = "delete from $table ";
                        foreach my $col (@cols_pk) {
                                my $val = $sdbh->quote($row->{$col}) || die "can't find value in row for col $col";
                                $where =~ s/\?/$val/;
                        }
                        $sql .= $where;
                        debug_sql($sql);
                        return $sql;
                }

                # update row in slave database
                sub sql_update {
                        my $table = shift @_ || die "need table as argument";
                        my $col = shift @_ || die "need col to update";
                        my $val = shift @_ || die "need new val";
                        my @cols_pk = @_ || die "need pk idenitifier";

                        my $sql = "udate $table set $col=".$mdbh->quote($val);
                        debug_sql($sql);
                        return $sql;
                }
                # master        slave
                # 1     =       1       test
                # 1     <       2       insert mrow
                # 2     >       1       delete srow
                # 1     =       undef   insert mrow
                # undef =       1       delete srow

                my $pk_same = 1;

                # check key cols for row
                foreach my $col (@cols_pk) {
                        if ( ($have_mrow == NO_ROW && $have_srow == HAVE_ROW) ||
                                ($have_mrow == HAVE_ROW && $have_srow == HAVE_ROW && $mrow->{$col} gt $srow->{$col}) ) {
                                $diff_row++;
                                $pk_same = 0;
                                print STDERR "EXTRA row in table '$table' pk: [".join(",",@cols_pk)."] value (".join(",",pk_val($srow,@cols_pk)).")\n" if ($verbose);
                                print sql_delete($table,$srow,@cols_pk),";\n";
                                $have_srow = FETCH_ROW;
                                last;
                        } elsif ( ($have_mrow == HAVE_ROW && $have_srow == NO_ROW) ||
                                ($have_mrow == HAVE_ROW && $have_srow == HAVE_ROW && $mrow->{$col} lt $srow->{$col}) ) {
                                $diff_row++;
                                $pk_same = 0;
                                print STDERR "MISSING row in table '$table' pk: [".join(",",@cols_pk)."] value (".join(",",pk_val($mrow,@cols_pk)).")\n" if ($verbose);
                                print sql_insert($table,$mrow,@cols),";\n";
                                $have_mrow = FETCH_ROW;
                                last;
                        }
                }

                if ($pk_same && $have_mrow == HAVE_ROW && $have_srow == HAVE_ROW) {
                        # check non-key cols for row
                        foreach my $col (@cols_cmp) {
                                if ($mrow->{$col} ne $srow->{$col}) {
                                        $diff_row++;
                                        print STDERR "DIFF in table '$table' row ($col): [".join(",",@cols_pk)."] '$mrow->{$col}' != '$srow->{$col}'\n" if ($verbose);
                                        print sql_update($table,$col,$mrow->{$col},@cols_pk),";\n";
                                }
                        }
                        $have_mrow = FETCH_ROW;
                        $have_srow = FETCH_ROW;
                }
        }

        print STDERR "$diff_row differences in table $table\n" if ($verbose && $diff_row > 0);
        $diff_total += $diff_row;
}

print STDERR "$diff_total differences in all tables\n" if ($verbose && $diff_total > 0);

$mdbh->disconnect();
$sdbh->disconnect();
1	#!/usr/bin/perl -w
2
3	# Compare data in two different databases
4	#
5	# 2003-08-07 Dobrica Pavlinusic
6	#
7	# somewhat based on ides from
8	# DataDiff 0.1 by Jon D. Frisby, http://www.mrjoy.com
9
10	use strict;
11	use Getopt::Long;
12	use DBI;
13	use Data::Dumper;
14
15	$\| = 1;
16
17	my ($debug,$verbose) = (0,0);
18	my ($help,$masterhost,$masterport,$masteruser,$masterpassword,
19	$slavehost,$slaveport,$slaveuser,$slavepassword,
20	$masterfile,
21	$slavefile,
22	);
23	my $tables;
24
25	my $result = GetOptions(
26	"debug!" => \$debug, "verbose!" => \$verbose, "help" => \$help,
27	"masterhost=s" => \$masterhost, "masterport=i" => \$masterport,
28	"masteruser=s" => \$masteruser, "masterpassword=s" => \$masterpassword,
29	"masterfile=s" => \$masterfile,
30	"slavehost=s" => \$slavehost, "slaveport=i" => \$slaveport,
31	"slaveuser=s" => \$slaveuser, "slavepassword=s" => \$slavepassword,
32	"slavefile=s" => \$slavefile,
33	"tables=s" => \$tables,
34	);
35
36	if (defined($help) \|\| (scalar(@ARGV) < 2)) {
37	print "Usage: $0 [options] masterdb slavedb
38	Options:
39	--masterhost=hostname --masterport=port
40	--masteruser=username --masterpassword=string
41	--masterfile=filename
42	--slavehost=hostname --slaveport=port
43	--slaveuser=username --slavepassword=string
44	--slavefile=filename
45	--tables[s]=table[,table...]
46	";
47	# exit ((scalar(@ARGV) < 2)? 1:0);
48	exit;
49	}
50
51	my $master = $ARGV[0] \|\| "master";
52	my $slave = $ARGV[1] \|\| "slave";
53
54	my $minfo = "dbname=$master";
55	$minfo = "$minfo host=$masterhost" if (defined($masterhost));
56	$minfo = "$minfo port=$masterport" if (defined($masterport));
57
58	my $sinfo = "dbname=$slave";
59	$sinfo = "$sinfo host=$slavehost" if (defined($slavehost));
60	$sinfo = "$sinfo port=$slaveport" if (defined($slaveport));
61
62	print "Master connection is $minfo\n" if ($debug);
63	print "Slave connection is $sinfo\n" if ($debug);
64
65	my $mdbh = DBI->connect("DBI:Pg:$minfo", $masteruser, $masterpassword, { PrintError => 1 });
66	my $sdbh = DBI->connect("DBI:Pg:$sinfo", $slaveuser, $slavepassword, { PrintError => 1 });
67
68	my ($diff_shema,$diff_data) = (0,0);
69
70	my $sql;
71
72	sub debug_sql {
73	return if (! $debug);
74	my $sql = shift;
75	$sql =~ s/[\n\r]/ /gs;
76	$sql =~ s/\s\s+/ /g;
77	print STDERR "DEBUG: SQL: $sql\n";
78	}
79
80	sub debug_row {
81	return if (! $debug);
82	my $row = shift;
83	my @cols = @_;
84	if (! $row) {
85	print STDERR "DEBUG: ROW data is undef!\n";
86	return;
87	}
88	print STDERR "DEBUG: ROW: [",$#cols+1,"] ";
89	foreach my $col (@cols) {
90	print STDERR "$col:";
91	if ($row->{$col}) {
92	print $row->{$col};
93	} else {
94	print "null";
95	}
96	print " ";
97	}
98	print STDERR "\n";
99	}
100
101	sub debug {
102	return if (!$debug);
103	print STDERR "DEBUG: ",@_;
104	}
105
106	$verbose = 1 if ($debug);
107
108	# which tables to compare?
109
110	my @tables;
111	if ($tables) {
112	@tables = split(/,/,$tables);
113	} else {
114	# take all tables
115	#$sql="select tablename from pg_tables where tablename not like 'pg_%' and tablename not like '_rserv_%'";
116	# show tables (based on psql \dt)
117	$sql = "
118	SELECT c.relname as table
119	FROM pg_catalog.pg_class c
120	LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
121	WHERE c.relkind = 'r'
122	AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
123	AND pg_catalog.pg_table_is_visible(c.oid)
124	and c.relname not like '_rserv_%'
125	";
126	foreach my $table (@tables) {
127	$sql .= " and c.relname like '$table'";
128	}
129	my $sth = $mdbh->prepare($sql);
130	$sth->execute() \|\| die;
131	while(my $row = $sth->fetchrow_hashref()) {
132	push @tables,$row->{table};
133	}
134	}
135
136	debug "Comparing tables: ".join(", ",@tables)."\n";
137
138	my $cols;
139	my $diff_total = 0;
140
141	foreach my $table (@tables) {
142
143	my ($sth);
144
145	# find table oid
146	$sql = "
147	SELECT c.oid, n.nspname, c.relname
148	FROM pg_catalog.pg_class c
149	LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
150	WHERE pg_catalog.pg_table_is_visible(c.oid)
151	AND c.relname = '$table'
152	ORDER BY 2, 3
153	";
154
155	$sth = $mdbh->prepare($sql);
156	$sth->execute() \|\| die;
157	my $row = $sth->fetchrow_hashref();
158	if (! $row) {
159	print STDERR "Can't find OID of table '$table'\n";
160	exit 1;
161	}
162
163	my $oid = $row->{oid};
164
165	# diff schema
166
167	my @cols_notnull;# colums compared by a=b
168	my @cols_null; # colums compared by a=b or a is null and b is null
169	my @cols_skip; # skipped columns
170	my @cols_test; # all colums to test (without skipped)
171	my @cols; # all colums (for insert)
172
173	my $sql="
174	SELECT a.attname,
175	pg_catalog.format_type(a.atttypid, a.atttypmod),
176	a.attnotnull, a.atthasdef, a.attnum
177	FROM pg_catalog.pg_attribute a
178	WHERE a.attrelid = $oid AND a.attnum > 0 AND NOT a.attisdropped
179	ORDER BY a.attnum
180	";
181
182	$sth = $mdbh->prepare($sql);
183	$sth->execute() \|\| die;
184	while(my $row = $sth->fetchrow_hashref()) {
185	# attname \| format_type \| attnotnull \| atthasdef \| attnum
186
187	push @cols,$row->{attname};
188
189	# FIXME: do something with attributes which shouldn't be compared
190	# (date, time, datetime, timestamp)
191	if ($row->{format_type} =~ /(date)\|(time)/i) {
192	push @cols_skip,$row->{attname};
193	next;
194	}
195
196	push @cols_test,$row->{attname};
197
198	if ($row->{attnotnull}) {
199	push @cols_notnull,$row->{attname};
200	} else {
201	push @cols_null,$row->{attname};
202	}
203	}
204	$sth->finish();
205
206	if ($debug) {
207	print STDERR "DEBUG: table $table [$oid] not null: (",join(", ",@cols_notnull),")";
208	print STDERR " - null: (",join(", ",@cols_null),")" if (@cols_null);
209	print STDERR " - skip: (",join(", ",@cols_skip),")" if (@cols_skip);
210	print STDERR "\n";
211	}
212
213	# diff data
214
215	my @cols_pk; # columns which are primary key
216	my %in_pk;
217
218	$sql="
219	SELECT
220	i.indexrelid as indexrelid, i.indrelid as indrelid,
221	count(a.attname) as cols_in_pk
222	FROM
223	pg_catalog.pg_class c,
224	pg_catalog.pg_index i,
225	pg_catalog.pg_attribute a
226	WHERE
227	c.oid = i.indrelid
228	and i.indisunique
229	and c.relname = '$table'
230	and a.attrelid = i.indexrelid
231	GROUP BY
232	i.indexrelid, i.indrelid, c.relname, i.indisprimary, i.indisunique
233	ORDER BY
234	cols_in_pk ASC, i.indisprimary DESC, i.indisunique DESC, c.relname DESC
235	";
236	debug_sql($sql);
237	$sth = $mdbh->prepare($sql);
238	$sth->execute() \|\| die;
239	$row = $sth->fetchrow_hashref();
240	if ($row) {
241	$sql="
242	select a1.attname as attname from pg_attribute a1, pg_attribute a2 where a1.attrelid = ".$row->{indexrelid}." and a2.attrelid=".$row->{indrelid}." and a1.attname = a2.attname and a2.attnotnull";
243
244	debug_sql($sql);
245	my $sth2 = $mdbh->prepare($sql);
246	$sth2->execute() \|\| die;
247	@cols_pk = ();
248	while (my $row2 = $sth2->fetchrow_hashref()) {
249	push @cols_pk,$row2->{attname};
250	$in_pk{$row2->{attname}}++;
251	}
252
253	}
254	if (! @cols_pk) {
255	print STDERR "can't find PK rows for table '$table' using all\n";
256	@cols_pk = @cols;
257	}
258
259	my @cols_cmp; # columns to compare
260
261	foreach my $col (@cols_test) {
262	push @cols_cmp,$col if (! $in_pk{$col});
263	}
264
265	if ($verbose) {
266	print "table '$table' using for key: (",join(", ",@cols_pk),") to compare cols: (",join(", ",@cols_cmp),")\n";
267	}
268
269	# diff data
270
271	my $msql = "select ".join(",",@cols)." from $table";
272	my $ssql = $msql;
273
274	sub sql_where {
275	my @cols = @_;
276	my $and = "";
277	my $where = " where ";
278	foreach my $col (@cols) {
279	$where .= "$and$col=?";
280	$and = " and ";
281	}
282	return $where;
283	}
284
285	sub sql_order {
286	my @cols = @_;
287	my $order = " order by ";
288	my $comma = "";
289	foreach my $col (@cols) {
290	$order .= "$comma$col asc";
291	$comma = ", ";
292	}
293	return $order;
294	}
295
296	my $order = sql_order(@cols_pk);
297	$msql .= $order;
298	$ssql .= $order;
299
300	debug_sql($msql);
301
302	my $msth = $mdbh->prepare($msql) \|\| die;
303	$msth->execute() \|\| die;
304
305	my $ssth = $sdbh->prepare($ssql) \|\| die;
306	$ssth->execute() \|\| die;
307
308	my $diff_row = 0;
309
310	my ($mrow,$srow);
311	# have_*
312	use constant NO_ROW => 0;
313	use constant FETCH_ROW => 1;
314	use constant HAVE_ROW => 2;
315	my ($have_mrow,$have_srow) = (FETCH_ROW,FETCH_ROW);
316
317	while ($have_mrow != NO_ROW \|\| $have_srow != NO_ROW) {
318
319	debug "have mrow: $have_mrow srow: $have_srow\n";
320
321	sub pk_val {
322	my $row = shift \|\| die "need row";
323	my @cols = shift \|\| die "need cols";
324	my @val;
325	foreach my $col (@cols) {
326	push @val,$row->{$col};
327	}
328	return @val;
329	}
330
331	# fetch row from master
332	if ($have_mrow == FETCH_ROW) {
333	debug "fetch row from master: $msql\n";
334	$mrow = $msth->fetchrow_hashref();
335	debug_row($mrow,@cols);
336
337	if ($mrow) {
338	# fill-in primary key values
339	$have_mrow = HAVE_ROW;
340	} else {
341	$have_mrow = NO_ROW;
342	}
343	}
344
345	# fetch row from slave
346	if ($have_srow == FETCH_ROW) {
347	debug "fetch row from slave: $ssql\n";
348	$srow = $ssth->fetchrow_hashref();
349	debug_row($srow,@cols);
350	if ($srow) {
351	$have_srow = HAVE_ROW;
352	} else {
353	$have_srow = NO_ROW;
354	}
355	}
356
357	debug "have mrow: $have_mrow srow: $have_srow\n";
358
359	# insert into slave database
360	sub sql_insert {
361	my $table = shift @_ \|\| die "need table as argument";
362	my $row = shift @_ \|\| die "need row data";
363	my @cols = @_;
364
365	my $sql = "insert into $table (".join(",",@cols).") values (";
366	my $comma = "";
367	foreach my $col (@cols) {
368	$sql .= $comma.$mdbh->quote($row->{$col});
369	$comma = ",";
370	}
371	$sql.=")";
372	debug_sql($sql);
373	return $sql;
374	}
375
376	# delete from slave database
377	sub sql_delete {
378	my $table = shift @_ \|\| die "need table as argument";
379	my $row = shift @_ \|\| die "need row as argument";
380	my @cols_pk = @_;
381
382	my $where = sql_where(@cols_pk);
383
384	my $sql = "delete from $table ";
385	foreach my $col (@cols_pk) {
386	my $val = $sdbh->quote($row->{$col}) \|\| die "can't find value in row for col $col";
387	$where =~ s/\?/$val/;
388	}
389	$sql .= $where;
390	debug_sql($sql);
391	return $sql;
392	}
393
394	# update row in slave database
395	sub sql_update {
396	my $table = shift @_ \|\| die "need table as argument";
397	my $col = shift @_ \|\| die "need col to update";
398	my $val = shift @_ \|\| die "need new val";
399	my @cols_pk = @_ \|\| die "need pk idenitifier";
400
401	my $sql = "udate $table set $col=".$mdbh->quote($val);
402	debug_sql($sql);
403	return $sql;
404	}
405	# master slave
406	# 1 = 1 test
407	# 1 < 2 insert mrow
408	# 2 > 1 delete srow
409	# 1 = undef insert mrow
410	# undef = 1 delete srow
411
412	my $pk_same = 1;
413
414	# check key cols for row
415	foreach my $col (@cols_pk) {
416	if ( ($have_mrow == NO_ROW && $have_srow == HAVE_ROW) \|\|
417	($have_mrow == HAVE_ROW && $have_srow == HAVE_ROW && $mrow->{$col} gt $srow->{$col}) ) {
418	$diff_row++;
419	$pk_same = 0;
420	print STDERR "EXTRA row in table '$table' pk: [".join(",",@cols_pk)."] value (".join(",",pk_val($srow,@cols_pk)).")\n" if ($verbose);
421	print sql_delete($table,$srow,@cols_pk),";\n";
422	$have_srow = FETCH_ROW;
423	last;
424	} elsif ( ($have_mrow == HAVE_ROW && $have_srow == NO_ROW) \|\|
425	($have_mrow == HAVE_ROW && $have_srow == HAVE_ROW && $mrow->{$col} lt $srow->{$col}) ) {
426	$diff_row++;
427	$pk_same = 0;
428	print STDERR "MISSING row in table '$table' pk: [".join(",",@cols_pk)."] value (".join(",",pk_val($mrow,@cols_pk)).")\n" if ($verbose);
429	print sql_insert($table,$mrow,@cols),";\n";
430	$have_mrow = FETCH_ROW;
431	last;
432	}
433	}
434
435	if ($pk_same && $have_mrow == HAVE_ROW && $have_srow == HAVE_ROW) {
436	# check non-key cols for row
437	foreach my $col (@cols_cmp) {
438	if ($mrow->{$col} ne $srow->{$col}) {
439	$diff_row++;
440	print STDERR "DIFF in table '$table' row ($col): [".join(",",@cols_pk)."] '$mrow->{$col}' != '$srow->{$col}'\n" if ($verbose);
441	print sql_update($table,$col,$mrow->{$col},@cols_pk),";\n";
442	}
443	}
444	$have_mrow = FETCH_ROW;
445	$have_srow = FETCH_ROW;
446	}
447	}
448
449	print STDERR "$diff_row differences in table $table\n" if ($verbose && $diff_row > 0);
450	$diff_total += $diff_row;
451	}
452
453	print STDERR "$diff_total differences in all tables\n" if ($verbose && $diff_total > 0);
454
455	$mdbh->disconnect();
456	$sdbh->disconnect();