/[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.3 - (show annotations)
Tue Aug 12 12:00:07 2003 UTC (19 years, 5 months ago) by dpavlin
Branch: MAIN
Changes since 1.2: +18 -7 lines
fixes, add update for diff rows (stupid overlook, really...)

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();

  ViewVC Help
Powered by ViewVC 1.1.26