/[pgdiff]/pgdiff
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Contents of /pgdiff

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.13 - (show annotations)
Fri Oct 31 11:19:45 2003 UTC (20 years, 5 months ago) by dpavlin
Branch: MAIN
CVS Tags: HEAD
Changes since 1.12: +4 -4 lines
typo: columns -> columns

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 use Pg::Scheme;
15
16 $| = 1;
17
18 my ($debug,$verbose) = (0,0);
19 my ($help,$masterhost,$masterport,$masteruser,$masterpassword,
20 $slavehost,$slaveport,$slaveuser,$slavepassword,
21 $masterfile,
22 $slavefile,
23 );
24 my $tables;
25
26 my $result = GetOptions(
27 "debug!" => \$debug, "verbose!" => \$verbose, "help" => \$help,
28 "masterhost=s" => \$masterhost, "masterport=i" => \$masterport,
29 "masteruser=s" => \$masteruser, "masterpassword=s" => \$masterpassword,
30 "masterfile=s" => \$masterfile,
31 "slavehost=s" => \$slavehost, "slaveport=i" => \$slaveport,
32 "slaveuser=s" => \$slaveuser, "slavepassword=s" => \$slavepassword,
33 "slavefile=s" => \$slavefile,
34 "tables=s" => \$tables,
35 );
36
37 if (defined($help) || (scalar(@ARGV) < 2)) {
38 print "Usage: $0 [options] masterdb slavedb
39 Options:
40 --masterhost=hostname --masterport=port
41 --masteruser=username --masterpassword=string
42 --masterfile=filename
43 --slavehost=hostname --slaveport=port
44 --slaveuser=username --slavepassword=string
45 --slavefile=filename
46 --table[s]=table[,table...]
47 ";
48 # exit ((scalar(@ARGV) < 2)? 1:0);
49 exit;
50 }
51
52 my $master = $ARGV[0] || "master";
53 my $slave = $ARGV[1] || "slave";
54
55 my $minfo = "dbname=$master";
56 $minfo = "$minfo host=$masterhost" if (defined($masterhost));
57 $minfo = "$minfo port=$masterport" if (defined($masterport));
58
59 my $sinfo = "dbname=$slave";
60 $sinfo = "$sinfo host=$slavehost" if (defined($slavehost));
61 $sinfo = "$sinfo port=$slaveport" if (defined($slaveport));
62
63 print "Master connection is $minfo\n" if ($debug);
64 print "Slave connection is $sinfo\n" if ($debug);
65
66 my $mdbh = DBI->connect("DBI:Pg:$minfo", $masteruser, $masterpassword, { PrintError => 0 } );
67 if (! $mdbh) {
68 print "Can't connect to master database $master";
69 print "on $masterhost" if ($masterhost);
70 print "\n";
71 exit 1;
72 }
73 my $sdbh = DBI->connect("DBI:Pg:$sinfo", $slaveuser, $slavepassword, { PrintError => 0 });
74 if (! $sdbh) {
75 print "Can't connect to slave database $slave";
76 print "on $slavehost" if ($slavehost);
77 print "\n";
78 exit 1;
79 }
80
81 my ($diff_shema,$diff_data) = (0,0);
82
83 my $sql;
84
85 sub debug_sql {
86 return if (! $debug);
87 my $sql = shift;
88 $sql =~ s/[\n\r]/ /gs;
89 $sql =~ s/\s\s+/ /g;
90 print STDERR "DEBUG: SQL: $sql\n";
91 }
92
93 sub debug_row {
94 return if (! $debug);
95 my $row = shift;
96 my @cols = @_;
97 if (! $row) {
98 print STDERR "DEBUG: ROW data is undef!\n";
99 return;
100 }
101 print STDERR "DEBUG: ROW: [",$#cols+1,"] ";
102 foreach my $col (@cols) {
103 print STDERR "$col:";
104 if ($row->{$col}) {
105 print $row->{$col};
106 } else {
107 print "null";
108 }
109 print " ";
110 }
111 print STDERR "\n";
112 }
113
114 sub debug {
115 return if (!$debug);
116 print STDERR "DEBUG: ",@_;
117 }
118
119 $verbose = 1 if ($debug);
120
121 # init object for scheme in master database
122 my $mscheme = new Pg::Scheme( 'dbh' => $mdbh, 'DEBUG' => 0 ) || die "can't query master schema";
123 my $sscheme = new Pg::Scheme( 'dbh' => $sdbh, 'DEBUG' => 0 ) || die "can't query slave schema";
124
125 # which tables to compare?
126
127 my @tables = $mscheme->list_tables($tables);
128
129 debug "Comparing tables: ".join(", ",@tables)."\n";
130
131 # start transaction
132 print "begin work;\n";
133
134 # disable active triggers on slave database
135 my @triggers = $sscheme->get_activetriggers();
136
137 foreach my $tr (@triggers) {
138 print "update pg_trigger set tgenabled = false where tgname='$tr';\n";
139 }
140
141 my $cols;
142 my $diff_total = 0;
143
144 foreach my $table (@tables) {
145
146 my $sth;
147
148 print "-- schema...\n";
149 # diff schema
150 foreach my $row (@{$mscheme->pg_attribute($table)}) {
151 # print Dumper($row);
152 }
153
154 print "-- constraints...\n";
155 # diff constraints
156 foreach my $tr (@{$mscheme->triggers($table)}) {
157 # print Dumper($tr);
158 }
159 print "-- triggers...\n";
160 # diff triggers
161 foreach my $tr (@{$mscheme->triggers($table)}) {
162 # print Dumper($tr);
163 }
164
165 # all columns (for insert)
166 my @cols = @{$mscheme->cols($table)};
167
168 # columns compared by a=b
169 my @cols_notnull = @{$mscheme->cols_notnull($table)};
170
171 # columns compared by a=b or a is null and b is null
172 my @cols_null = @{$mscheme->cols_null($table)};
173
174 # primary key columns
175 my @cols_pk = @{$mscheme->cols_pk($table)};
176
177 # columns to compare (not in primary key)
178 my @cols_cmp = @{$mscheme->cols_notpk($table)};
179
180 my @cols_skip; # skipped columns
181 my @cols_test; # all columns to test (without skipped)
182
183 foreach my $row (@{$mscheme->pg_attribute($table)}) {
184 # attname format_type attnotnull atthasdef attnum default references
185
186 # FIXME: do something with attributes which shouldn't be compared
187 # (date, time, datetime, timestamp)
188 if ($row->{format_type} =~ /(date)|(time)/i) {
189 push @cols_skip,$row->{attname};
190 } else {
191 push @cols_test,$row->{attname};
192 }
193
194 }
195
196 if ($debug) {
197 print STDERR "DEBUG: table $table not null cols: (",join(", ",@cols_notnull),")";
198 print STDERR " - null cols: (",join(", ",@cols_null),")" if (@cols_null);
199 print STDERR " - skip cols: (",join(", ",@cols_skip),")" if (@cols_skip);
200 print STDERR "\n";
201 }
202
203 # diff data
204
205 if (! @cols_pk) {
206 print STDERR "can't find PK rows for table '$table' using all\n";
207 @cols_pk = @cols;
208 }
209
210
211 if ($verbose) {
212 print "table '$table' using for key: (",join(", ",@cols_pk),") to compare cols: (",join(", ",@cols_cmp),")\n";
213 }
214
215 # diff data
216
217 my $msql = "select ".join(",",@cols)." from $table";
218 my $ssql = $msql;
219
220 sub sql_where {
221 my @cols = @_;
222 my $and = "";
223 my $where = " where ";
224 foreach my $col (@cols) {
225 $where .= "$and$col=?";
226 $and = " and ";
227 }
228 return $where;
229 }
230
231 sub sql_order {
232 my @cols = @_;
233 my $order = " order by ";
234 my $comma = "";
235 foreach my $col (@cols) {
236 $order .= "$comma$col asc";
237 $comma = ", ";
238 }
239 return $order;
240 }
241
242 my $order = sql_order(@cols_pk);
243 $msql .= $order;
244 $ssql .= $order;
245
246 debug_sql($msql);
247
248 my $msth = $mdbh->prepare($msql) || die $mdbh->errstr;
249 $msth->execute() || die $msth->errstr;
250
251 my $ssth = $sdbh->prepare($ssql) || die $sdbh->errstr;
252 $ssth->execute() || die $ssth->errstr;
253
254 my $diff_row = 0;
255
256 my ($mrow,$srow);
257 # have_*
258 use constant NO_ROW => 0;
259 use constant FETCH_ROW => 1;
260 use constant HAVE_ROW => 2;
261 my ($have_mrow,$have_srow) = (FETCH_ROW,FETCH_ROW);
262
263 while ($have_mrow != NO_ROW || $have_srow != NO_ROW) {
264
265 debug "have mrow: $have_mrow srow: $have_srow\n";
266
267 sub pk_val {
268 my $row = shift || die "need row";
269 my @cols = shift || die "need cols";
270 my @val;
271 foreach my $col (@cols) {
272 push @val,$row->{$col};
273 }
274 return @val;
275 }
276
277 # fetch row from master
278 if ($have_mrow == FETCH_ROW) {
279 debug "fetch row from master: $msql\n";
280 $mrow = $msth->fetchrow_hashref();
281 debug_row($mrow,@cols);
282
283 if ($mrow) {
284 # fill-in primary key values
285 $have_mrow = HAVE_ROW;
286 } else {
287 $have_mrow = NO_ROW;
288 }
289 }
290
291 # fetch row from slave
292 if ($have_srow == FETCH_ROW) {
293 debug "fetch row from slave: $ssql\n";
294 $srow = $ssth->fetchrow_hashref();
295 debug_row($srow,@cols);
296 if ($srow) {
297 $have_srow = HAVE_ROW;
298 } else {
299 $have_srow = NO_ROW;
300 }
301 }
302
303 debug "have mrow: $have_mrow srow: $have_srow\n";
304
305 # insert into slave database
306 sub sql_insert {
307 my $dbh = shift @_ || die "need dbh";
308 my $table = shift @_ || die "need table as argument";
309 my $row = shift @_ || die "need row data";
310 my @cols = @_;
311
312 my $sql = "insert into $table (".join(",",@cols).") values (";
313 my $comma = "";
314 foreach my $col (@cols) {
315 $sql .= $comma.$dbh->quote($row->{$col});
316 $comma = ",";
317 }
318 $sql.=")";
319 debug_sql($sql);
320 return $sql;
321 }
322
323 # delete from slave database
324 sub sql_delete {
325 my $dbh = shift @_ || die "need dbh";
326 my $table = shift @_ || die "need table as argument";
327 my $row = shift @_ || die "need row as argument";
328 my @cols_pk = @_;
329
330 my $where = sql_where(@cols_pk);
331
332 my $sql = "delete from $table";
333 foreach my $col (@cols_pk) {
334 my $val = $dbh->quote($row->{$col}) || die "can't find value in row for col $col";
335 $where =~ s/\?/$val/;
336 }
337 $sql .= $where;
338 debug_sql($sql);
339 return $sql;
340 }
341
342 # update row in slave database
343 sub sql_update {
344 my $dbh = shift @_ || die "need dbh";
345 my $table = shift @_ || die "need table as argument";
346 my $col = shift @_ || die "need col to update";
347 my $row = shift @_ || die "need row";
348 my @cols_pk = @_;
349
350 my $sql = "update $table set $col=".$dbh->quote($row->{$col});
351 my $where = sql_where(@cols_pk);
352 foreach my $col (@cols_pk) {
353 my $val = $dbh->quote($row->{$col}) || die "can't find value in row for col $col";
354 $where =~ s/\?/$val/;
355 }
356 $sql .= $where;
357 debug_sql($sql);
358 return $sql;
359 }
360 # master slave
361 # 1 = 1 test
362 # 1 < 2 insert mrow
363 # 2 > 1 delete srow
364 # 1 = undef insert mrow
365 # undef = 1 delete srow
366
367 my $pk_same = 1;
368
369 # check key cols for row
370 foreach my $col (@cols_pk) {
371 if ( ($have_mrow == NO_ROW && $have_srow == HAVE_ROW) ||
372 ($have_mrow == HAVE_ROW && $have_srow == HAVE_ROW && $mrow->{$col} gt $srow->{$col}) ) {
373 $diff_row++;
374 $pk_same = 0;
375 print STDERR "EXTRA row in table '$table' pk: [".join(",",@cols_pk)."] value (".join(",",pk_val($srow,@cols_pk)).")\n" if ($verbose);
376 print sql_delete($sdbh,$table,$srow,@cols_pk),";\n";
377 $have_srow = FETCH_ROW;
378 last;
379 } elsif ( ($have_mrow == HAVE_ROW && $have_srow == NO_ROW) ||
380 ($have_mrow == HAVE_ROW && $have_srow == HAVE_ROW && $mrow->{$col} lt $srow->{$col}) ) {
381 $diff_row++;
382 $pk_same = 0;
383 print STDERR "MISSING row in table '$table' pk: [".join(",",@cols_pk)."] value (".join(",",pk_val($mrow,@cols_pk)).")\n" if ($verbose);
384 print sql_insert($mdbh,$table,$mrow,@cols),";\n";
385 $have_mrow = FETCH_ROW;
386 last;
387 }
388 }
389
390 if ($pk_same && $have_mrow == HAVE_ROW && $have_srow == HAVE_ROW) {
391 # check non-key cols for row
392 foreach my $col (@cols_cmp) {
393 if ($mrow->{$col} ne $srow->{$col}) {
394 $diff_row++;
395 print STDERR "DIFF in table '$table' row ($col): [".join(",",@cols_pk)."] '$mrow->{$col}' != '$srow->{$col}'\n" if ($verbose);
396 print sql_update($mdbh,$table,$col,$mrow,@cols_pk),";\n";
397 }
398 }
399 $have_mrow = FETCH_ROW;
400 $have_srow = FETCH_ROW;
401 }
402 }
403
404 print STDERR "$diff_row differences in table $table\n" if ($verbose && $diff_row > 0);
405 $diff_total += $diff_row;
406 }
407
408 if ($verbose) {
409 if ($diff_total == 0) {
410 print STDERR "databases are same\n";
411 } elsif ($diff_total > 0) {
412 print STDERR "$diff_total differences in all tables\n";
413 } else {
414 die "this shouldn't happend. please report a bug!";
415 }
416 }
417
418 # enable triggers again on slave
419 foreach my $tr (@triggers) {
420 print "update pg_trigger set tgenabled = true where tgname='$tr';\n";
421 }
422 # end transaction
423 print "commit;\n";
424
425 $mdbh->disconnect();
426 $sdbh->disconnect();

  ViewVC Help
Powered by ViewVC 1.1.26