/[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.11 - (hide annotations)
Tue Oct 28 18:56:54 2003 UTC (20 years, 5 months ago) by dpavlin
Branch: MAIN
Changes since 1.10: +17 -2 lines
working on diff for schema, triggers and constraints

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 dpavlin 1.4 use Pg::Scheme;
15 dpavlin 1.1
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 dpavlin 1.10 --table[s]=table[,table...]
47 dpavlin 1.1 ";
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 dpavlin 1.7 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 dpavlin 1.1
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 dpavlin 1.2 print STDERR "DEBUG: SQL: $sql\n";
91 dpavlin 1.1 }
92    
93     sub debug_row {
94     return if (! $debug);
95     my $row = shift;
96     my @cols = @_;
97     if (! $row) {
98 dpavlin 1.2 print STDERR "DEBUG: ROW data is undef!\n";
99 dpavlin 1.1 return;
100     }
101 dpavlin 1.2 print STDERR "DEBUG: ROW: [",$#cols+1,"] ";
102 dpavlin 1.1 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 dpavlin 1.2 sub debug {
115     return if (!$debug);
116     print STDERR "DEBUG: ",@_;
117     }
118    
119 dpavlin 1.1 $verbose = 1 if ($debug);
120    
121 dpavlin 1.4 # init object for scheme in master database
122 dpavlin 1.8 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 dpavlin 1.4
125 dpavlin 1.1 # which tables to compare?
126    
127 dpavlin 1.4 my @tables = $mscheme->list_tables($tables);
128 dpavlin 1.1
129 dpavlin 1.2 debug "Comparing tables: ".join(", ",@tables)."\n";
130 dpavlin 1.1
131 dpavlin 1.8 # start transaction
132     print "begin work;\n";
133    
134     # disable active triggers on slave database
135 dpavlin 1.11 my @triggers = $sscheme->get_activetriggers();
136 dpavlin 1.8
137     foreach my $tr (@triggers) {
138     print "update pg_trigger set tgenabled = false where tgname='$tr';\n";
139     }
140    
141 dpavlin 1.1 my $cols;
142     my $diff_total = 0;
143    
144     foreach my $table (@tables) {
145    
146 dpavlin 1.4 my $sth;
147 dpavlin 1.1
148 dpavlin 1.11 print "-- schema...\n";
149 dpavlin 1.1 # diff schema
150 dpavlin 1.11 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 dpavlin 1.1
165 dpavlin 1.5 # all colums (for insert)
166     my @cols = @{$mscheme->cols($table)};
167    
168     # colums compared by a=b
169     my @cols_notnull = @{$mscheme->cols_notnull($table)};
170    
171     # colums 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 dpavlin 1.1 my @cols_skip; # skipped columns
181     my @cols_test; # all colums to test (without skipped)
182    
183 dpavlin 1.5 foreach my $row (@{$mscheme->pg_attribute($table)}) {
184 dpavlin 1.11 # attname format_type attnotnull atthasdef attnum default references
185 dpavlin 1.1
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 dpavlin 1.5 } else {
191     push @cols_test,$row->{attname};
192 dpavlin 1.1 }
193    
194     }
195    
196     if ($debug) {
197 dpavlin 1.4 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 dpavlin 1.1 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 dpavlin 1.2 $ssql .= $order;
245 dpavlin 1.1
246     debug_sql($msql);
247    
248 dpavlin 1.9 my $msth = $mdbh->prepare($msql) || die $mdbh->errstr;
249     $msth->execute() || die $msth->errstr;
250 dpavlin 1.1
251 dpavlin 1.9 my $ssth = $sdbh->prepare($ssql) || die $sdbh->errstr;
252     $ssth->execute() || die $ssth->errstr;
253 dpavlin 1.1
254     my $diff_row = 0;
255    
256     my ($mrow,$srow);
257 dpavlin 1.2 # 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 dpavlin 1.1
277     # fetch row from master
278 dpavlin 1.2 if ($have_mrow == FETCH_ROW) {
279     debug "fetch row from master: $msql\n";
280 dpavlin 1.1 $mrow = $msth->fetchrow_hashref();
281     debug_row($mrow,@cols);
282    
283     if ($mrow) {
284     # fill-in primary key values
285 dpavlin 1.2 $have_mrow = HAVE_ROW;
286 dpavlin 1.1 } else {
287 dpavlin 1.2 $have_mrow = NO_ROW;
288 dpavlin 1.1 }
289     }
290    
291     # fetch row from slave
292 dpavlin 1.2 if ($have_srow == FETCH_ROW) {
293     debug "fetch row from slave: $ssql\n";
294 dpavlin 1.1 $srow = $ssth->fetchrow_hashref();
295     debug_row($srow,@cols);
296     if ($srow) {
297 dpavlin 1.2 $have_srow = HAVE_ROW;
298 dpavlin 1.1 } else {
299 dpavlin 1.2 $have_srow = NO_ROW;
300 dpavlin 1.1 }
301     }
302    
303 dpavlin 1.2 debug "have mrow: $have_mrow srow: $have_srow\n";
304 dpavlin 1.1
305     # insert into slave database
306     sub sql_insert {
307 dpavlin 1.6 my $dbh = shift @_ || die "need dbh";
308 dpavlin 1.1 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 dpavlin 1.6 $sql .= $comma.$dbh->quote($row->{$col});
316 dpavlin 1.1 $comma = ",";
317     }
318     $sql.=")";
319     debug_sql($sql);
320     return $sql;
321     }
322    
323     # delete from slave database
324     sub sql_delete {
325 dpavlin 1.6 my $dbh = shift @_ || die "need dbh";
326 dpavlin 1.1 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 dpavlin 1.6 my $sql = "delete from $table";
333 dpavlin 1.1 foreach my $col (@cols_pk) {
334 dpavlin 1.6 my $val = $dbh->quote($row->{$col}) || die "can't find value in row for col $col";
335 dpavlin 1.1 $where =~ s/\?/$val/;
336     }
337     $sql .= $where;
338     debug_sql($sql);
339     return $sql;
340     }
341    
342 dpavlin 1.3 # update row in slave database
343     sub sql_update {
344 dpavlin 1.6 my $dbh = shift @_ || die "need dbh";
345 dpavlin 1.3 my $table = shift @_ || die "need table as argument";
346     my $col = shift @_ || die "need col to update";
347 dpavlin 1.6 my $row = shift @_ || die "need row";
348     my @cols_pk = @_;
349 dpavlin 1.3
350 dpavlin 1.6 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 dpavlin 1.3 debug_sql($sql);
358     return $sql;
359     }
360 dpavlin 1.1 # 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 dpavlin 1.2 if ( ($have_mrow == NO_ROW && $have_srow == HAVE_ROW) ||
372     ($have_mrow == HAVE_ROW && $have_srow == HAVE_ROW && $mrow->{$col} gt $srow->{$col}) ) {
373 dpavlin 1.1 $diff_row++;
374     $pk_same = 0;
375 dpavlin 1.3 print STDERR "EXTRA row in table '$table' pk: [".join(",",@cols_pk)."] value (".join(",",pk_val($srow,@cols_pk)).")\n" if ($verbose);
376 dpavlin 1.6 print sql_delete($sdbh,$table,$srow,@cols_pk),";\n";
377 dpavlin 1.2 $have_srow = FETCH_ROW;
378 dpavlin 1.1 last;
379 dpavlin 1.2 } elsif ( ($have_mrow == HAVE_ROW && $have_srow == NO_ROW) ||
380     ($have_mrow == HAVE_ROW && $have_srow == HAVE_ROW && $mrow->{$col} lt $srow->{$col}) ) {
381 dpavlin 1.1 $diff_row++;
382     $pk_same = 0;
383 dpavlin 1.3 print STDERR "MISSING row in table '$table' pk: [".join(",",@cols_pk)."] value (".join(",",pk_val($mrow,@cols_pk)).")\n" if ($verbose);
384 dpavlin 1.6 print sql_insert($mdbh,$table,$mrow,@cols),";\n";
385 dpavlin 1.2 $have_mrow = FETCH_ROW;
386 dpavlin 1.1 last;
387     }
388     }
389    
390 dpavlin 1.2 if ($pk_same && $have_mrow == HAVE_ROW && $have_srow == HAVE_ROW) {
391 dpavlin 1.1 # check non-key cols for row
392     foreach my $col (@cols_cmp) {
393     if ($mrow->{$col} ne $srow->{$col}) {
394     $diff_row++;
395 dpavlin 1.2 print STDERR "DIFF in table '$table' row ($col): [".join(",",@cols_pk)."] '$mrow->{$col}' != '$srow->{$col}'\n" if ($verbose);
396 dpavlin 1.6 print sql_update($mdbh,$table,$col,$mrow,@cols_pk),";\n";
397 dpavlin 1.1 }
398     }
399 dpavlin 1.3 $have_mrow = FETCH_ROW;
400     $have_srow = FETCH_ROW;
401 dpavlin 1.1 }
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 dpavlin 1.4 if ($verbose) {
409     if ($diff_total == 0) {
410     print STDERR "databases are same";
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 dpavlin 1.1
418 dpavlin 1.8 # 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 dpavlin 1.1 $mdbh->disconnect();
426     $sdbh->disconnect();

  ViewVC Help
Powered by ViewVC 1.1.26