/[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

Annotation of /pgdiff

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.1 - (hide annotations)
Tue Aug 12 10:38:23 2003 UTC (20 years, 7 months ago) by dpavlin
Branch: MAIN
Branch point for: DbP
Initial revision

1 dpavlin 1.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 "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 "ROW data is undef!\n";
86     return;
87     }
88     print STDERR "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     $verbose = 1 if ($debug);
102    
103     # which tables to compare?
104    
105     my @tables;
106     if ($tables) {
107     @tables = split(/,/,$tables);
108     } else {
109     # take all tables
110     #$sql="select tablename from pg_tables where tablename not like 'pg_%' and tablename not like '_rserv_%'";
111     # show tables (based on psql \dt)
112     $sql = "
113     SELECT c.relname as table
114     FROM pg_catalog.pg_class c
115     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
116     WHERE c.relkind = 'r'
117     AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
118     AND pg_catalog.pg_table_is_visible(c.oid)
119     and c.relname not like '_rserv_%'
120     ";
121     my $sth = $mdbh->prepare($sql);
122     $sth->execute() || die;
123     while(my $row = $sth->fetchrow_hashref()) {
124     push @tables,$row->{table};
125     }
126     }
127    
128     print "Comparing tables: ",join(", ",@tables),"\n" if ($debug);
129    
130     my $cols;
131     my $diff_total = 0;
132    
133     foreach my $table (@tables) {
134    
135     my ($sth);
136    
137     # find table oid
138     $sql = "
139     SELECT c.oid, n.nspname, c.relname
140     FROM pg_catalog.pg_class c
141     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
142     WHERE pg_catalog.pg_table_is_visible(c.oid)
143     AND c.relname = '$table'
144     ORDER BY 2, 3
145     ";
146    
147     $sth = $mdbh->prepare($sql);
148     $sth->execute() || die;
149     my $row = $sth->fetchrow_hashref();
150     if (! $row) {
151     print STDERR "Can't find OID of table '$table'\n";
152     exit 1;
153     }
154    
155     my $oid = $row->{oid};
156    
157     # diff schema
158    
159     my @cols_notnull;# colums compared by a=b
160     my @cols_null; # colums compared by a=b or a is null and b is null
161     my @cols_skip; # skipped columns
162     my @cols_test; # all colums to test (without skipped)
163     my @cols; # all colums (for insert)
164    
165     my $sql="
166     SELECT a.attname,
167     pg_catalog.format_type(a.atttypid, a.atttypmod),
168     a.attnotnull, a.atthasdef, a.attnum
169     FROM pg_catalog.pg_attribute a
170     WHERE a.attrelid = $oid AND a.attnum > 0 AND NOT a.attisdropped
171     ORDER BY a.attnum
172     ";
173    
174     $sth = $mdbh->prepare($sql);
175     $sth->execute() || die;
176     while(my $row = $sth->fetchrow_hashref()) {
177     # attname | format_type | attnotnull | atthasdef | attnum
178    
179     push @cols,$row->{attname};
180    
181     # FIXME: do something with attributes which shouldn't be compared
182     # (date, time, datetime, timestamp)
183     if ($row->{format_type} =~ /(date)|(time)/i) {
184     push @cols_skip,$row->{attname};
185     next;
186     }
187    
188     push @cols_test,$row->{attname};
189    
190     if ($row->{attnotnull}) {
191     push @cols_notnull,$row->{attname};
192     } else {
193     push @cols_null,$row->{attname};
194     }
195     }
196     $sth->finish();
197    
198     if ($debug) {
199     print STDERR "table $table [$oid] not null: (",join(", ",@cols_notnull),")";
200     print STDERR " - null: (",join(", ",@cols_null),")" if (@cols_null);
201     print STDERR " - skip: (",join(", ",@cols_skip),")" if (@cols_skip);
202     print STDERR "\n";
203     }
204    
205     # diff data
206    
207     my @cols_pk; # columns which are primary key
208     my %in_pk;
209    
210     $sql="
211     SELECT
212     i.indexrelid as indexrelid, i.indrelid as indrelid,
213     count(a.attname) as cols_in_pk
214     FROM
215     pg_catalog.pg_class c,
216     pg_catalog.pg_index i,
217     pg_catalog.pg_attribute a
218     WHERE
219     c.oid = i.indrelid
220     and i.indisunique
221     and c.relname = '$table'
222     and a.attrelid = i.indexrelid
223     GROUP BY
224     i.indexrelid, i.indrelid, c.relname, i.indisprimary, i.indisunique
225     ORDER BY
226     cols_in_pk ASC, i.indisprimary DESC, i.indisunique DESC, c.relname DESC
227     ";
228     debug_sql($sql);
229     $sth = $mdbh->prepare($sql);
230     $sth->execute() || die;
231     $row = $sth->fetchrow_hashref();
232     if ($row) {
233     $sql="
234     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";
235    
236     debug_sql($sql);
237     my $sth2 = $mdbh->prepare($sql);
238     $sth2->execute() || die;
239     @cols_pk = ();
240     while (my $row2 = $sth2->fetchrow_hashref()) {
241     push @cols_pk,$row2->{attname};
242     $in_pk{$row2->{attname}}++;
243     }
244    
245     }
246     if (! @cols_pk) {
247     print STDERR "can't find PK rows for table '$table' using all\n";
248     @cols_pk = @cols;
249     }
250    
251     my @cols_cmp; # columns to compare
252    
253     foreach my $col (@cols_test) {
254     push @cols_cmp,$col if (! $in_pk{$col});
255     }
256    
257     if ($verbose) {
258     print "table '$table' using for key: (",join(", ",@cols_pk),") to compare cols: (",join(", ",@cols_cmp),")\n";
259     }
260    
261     # diff data
262    
263     my $msql = "select ".join(",",@cols)." from $table";
264     my $ssql = $msql;
265    
266     sub sql_where {
267     my @cols = @_;
268     my $and = "";
269     my $where = " where ";
270     foreach my $col (@cols) {
271     $where .= "$and$col=?";
272     $and = " and ";
273     }
274     return $where;
275     }
276    
277     sub sql_order {
278     my @cols = @_;
279     my $order = " order by ";
280     my $comma = "";
281     foreach my $col (@cols) {
282     $order .= "$comma$col asc";
283     $comma = ", ";
284     }
285     return $order;
286     }
287    
288     my $order = sql_order(@cols_pk);
289     $msql .= $order;
290     $ssql .= sql_where(@cols_pk) . $order;
291    
292     debug_sql($msql);
293     debug_sql($ssql);
294    
295     my $msth = $mdbh->prepare($msql) || die;
296     $msth->execute() || die;
297    
298     my $ssth = $sdbh->prepare($ssql) || die;
299    
300     my $diff_row = 0;
301    
302     my ($mrow,$srow);
303     my ($have_mrow,$have_srow) = (0,0);
304     my @pk_val;
305    
306     my $more_rows = 1;
307     while (!$have_mrow || !$have_srow) {
308     # fetch row from master
309     if (!$have_mrow) {
310     print "fetch row from master [$more_rows]: $msql\n" if ($debug);
311     $mrow = $msth->fetchrow_hashref();
312     debug_row($mrow,@cols);
313    
314     if ($mrow) {
315     # fill-in primary key values
316     @pk_val = ();
317     foreach my $col (@cols_pk) {
318     push @pk_val,$mrow->{$col};
319     }
320     $have_mrow = 1;
321     } else {
322     $have_mrow = 0;
323     }
324     }
325    
326     # fetch row from slave
327     if (!$have_srow) {
328     print "fetch row from slave [$more_rows]: $ssql\n" if ($debug);
329     $ssth->execute(@pk_val) || die;
330     $srow = $ssth->fetchrow_hashref();
331     debug_row($srow,@cols);
332     if ($srow) {
333     $have_srow = 1;
334     } else {
335     $have_srow = 0;
336     }
337     }
338    
339     # end of this table?
340     if (!$have_mrow && !$have_srow) {
341     last;
342     }
343    
344     # insert into slave database
345     sub sql_insert {
346     my $table = shift @_ || die "need table as argument";
347     my $row = shift @_ || die "need row data";
348     my @cols = @_;
349    
350     my $sql = "insert into $table (".join(",",@cols).") values (";
351     my $comma = "";
352     foreach my $col (@cols) {
353     $sql .= $comma.$mdbh->quote($row->{$col});
354     $comma = ",";
355     }
356     $sql.=")";
357     debug_sql($sql);
358     return $sql;
359     }
360    
361     # delete from slave database
362     sub sql_delete {
363     my $table = shift @_ || die "need table as argument";
364     my $row = shift @_ || die "need row as argument";
365     my @cols_pk = @_;
366    
367     my $where = sql_where(@cols_pk);
368    
369     my $sql = "delete from $table ";
370     foreach my $col (@cols_pk) {
371     my $val = $sdbh->quote($row->{$col}) || die "can't find value in row for col $col";
372     $where =~ s/\?/$val/;
373     }
374     $sql .= $where;
375     debug_sql($sql);
376     return $sql;
377     }
378    
379     # master slave
380     # 1 = 1 test
381     # 1 < 2 insert mrow
382     # 2 > 1 delete srow
383     # 1 = undef insert mrow
384     # undef = 1 delete srow
385    
386     my $pk_same = 1;
387    
388     # check key cols for row
389     foreach my $col (@cols_pk) {
390     if ( (!$mrow && $srow) || ($mrow && $srow && ($mrow->{$col} gt $srow->{$col})) ) {
391     $diff_row++;
392     $pk_same = 0;
393     print sql_delete($table,$srow,@cols_pk),"\n";
394     $have_srow = 0; # fetch new slave row
395     last;
396     } elsif ( ($mrow && !$srow) || ($mrow && $srow && ($mrow->{$col} lt $srow->{$col})) ) {
397     $diff_row++;
398     $pk_same = 0;
399     print sql_insert($table,$mrow,@cols),"\n";
400     $have_mrow = 0;
401     last;
402     }
403     }
404    
405     if ($pk_same) {
406     # check non-key cols for row
407     foreach my $col (@cols_cmp) {
408     if ($mrow->{$col} ne $srow->{$col}) {
409     $diff_row++;
410     print STDERR "DIFF in table '$table' row ($col): [".join(",",@pk_val)."] '$mrow->{$col}' != '$srow->{$col}'\n" if ($verbose);
411     print sql_delete($table,$srow,@cols_pk),"\n";
412     print sql_insert($table,$mrow,@cols),"\n";
413     }
414     }
415     }
416     }
417    
418     print STDERR "$diff_row differences in table $table\n" if ($verbose && $diff_row > 0);
419     $diff_total += $diff_row;
420     }
421    
422     print STDERR "$diff_total differences in all tables\n" if ($verbose && $diff_total > 0);
423    
424     $mdbh->disconnect();
425     $sdbh->disconnect();

  ViewVC Help
Powered by ViewVC 1.1.26