--- pgdiff	2003/08/12 10:38:23	1.1
+++ pgdiff	2003/10/26 19:22:36	1.10
@@ -11,6 +11,7 @@
 use Getopt::Long;
 use DBI;
 use Data::Dumper;
+use Pg::Scheme;
 
 $| = 1;
 
@@ -42,7 +43,7 @@
 	--slavehost=hostname --slaveport=port
 	--slaveuser=username --slavepassword=string
 	--slavefile=filename
-	--tables[s]=table[,table...]
+	--table[s]=table[,table...]
 ";
 #    exit ((scalar(@ARGV) < 2)? 1:0);
     exit;
@@ -62,8 +63,20 @@
 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 $mdbh = DBI->connect("DBI:Pg:$minfo", $masteruser, $masterpassword, { PrintError => 0 } );
+if (! $mdbh) {
+	print "Can't connect to master database $master";
+	print "on $masterhost" if ($masterhost);
+	print "\n";
+	exit 1;
+}
+my $sdbh = DBI->connect("DBI:Pg:$sinfo", $slaveuser, $slavepassword, { PrintError => 0 });
+if (! $sdbh) {
+	print "Can't connect to slave database $slave";
+	print "on $slavehost" if ($slavehost);
+	print "\n";
+	exit 1;
+}
 
 my ($diff_shema,$diff_data) = (0,0);
 
@@ -74,7 +87,7 @@
 	my $sql = shift;
 	$sql =~ s/[\n\r]/ /gs;
 	$sql =~ s/\s\s+/ /g;
-	print STDERR "SQL: $sql\n";
+	print STDERR "DEBUG: SQL: $sql\n";
 }
 
 sub debug_row {
@@ -82,10 +95,10 @@
 	my $row = shift;
 	my @cols = @_;
 	if (! $row) {
-		print STDERR "ROW data is undef!\n";
+		print STDERR "DEBUG: ROW data is undef!\n";
 		return;
 	}
-	print STDERR "ROW: [",$#cols+1,"] ";
+	print STDERR "DEBUG: ROW: [",$#cols+1,"] ";
 	foreach my $col (@cols) {
 		print STDERR "$col:";
 		if ($row->{$col}) {
@@ -98,161 +111,87 @@
 	print STDERR "\n";
 }
 
+sub debug {
+	return if (!$debug);
+	print STDERR "DEBUG: ",@_;
+}
+
 $verbose = 1 if ($debug);
 
+# init object for scheme in master database
+my $mscheme = new Pg::Scheme( 'dbh' => $mdbh, 'DEBUG' => 0 ) || die "can't query master schema";
+my $sscheme = new Pg::Scheme( 'dbh' => $sdbh, 'DEBUG' => 0 ) || die "can't query slave schema";
+
 # 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_%'
-	";
-	my $sth = $mdbh->prepare($sql);
-	$sth->execute() || die;
-	while(my $row = $sth->fetchrow_hashref()) {
-		push @tables,$row->{table};
-	}
-}
+my @tables = $mscheme->list_tables($tables);
 
-print "Comparing tables: ",join(", ",@tables),"\n" if ($debug);
+debug "Comparing tables: ".join(", ",@tables)."\n";
+
+# start transaction
+print "begin work;\n";
+
+# disable active triggers on slave database
+my @triggers = $sscheme->get_triggers();
+
+foreach my $tr (@triggers) {
+	print "update pg_trigger set tgenabled = false where tgname='$tr';\n";
+}
 
 my $cols;
 my $diff_total = 0;
 
 foreach my $table (@tables) {
 
-	my ($sth);
+	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;
-	}
+	# diff schema
 
-	my $oid = $row->{oid};
+	# all colums (for insert)
+	my @cols = @{$mscheme->cols($table)};
 
-	# diff schema
+	# colums compared by a=b
+	my @cols_notnull = @{$mscheme->cols_notnull($table)};
+
+	# colums compared by a=b or a is null and b is null
+	my @cols_null = @{$mscheme->cols_null($table)};
+
+	# primary key columns
+	my @cols_pk = @{$mscheme->cols_pk($table)};
+
+	# columns to compare (not in primary key)
+	my @cols_cmp = @{$mscheme->cols_notpk($table)};
 
-	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()) {
+	foreach my $row (@{$mscheme->pg_attribute($table)}) {
 		# 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};
+			push @cols_test,$row->{attname};
 		}
+
 	}
-	$sth->finish();
 
 	if ($debug) {
-		print STDERR "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 "DEBUG: table $table not null cols: (",join(", ",@cols_notnull),")";
+		print STDERR " - null cols: (",join(", ",@cols_null),")" if (@cols_null);
+		print STDERR " - skip cols: (",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";
@@ -287,62 +226,70 @@
 
 	my $order = sql_order(@cols_pk);
 	$msql .= $order;
-	$ssql .= sql_where(@cols_pk) . $order;
+	$ssql .= $order;
 
 	debug_sql($msql);
-	debug_sql($ssql);
 
-	my $msth = $mdbh->prepare($msql) || die;
-	$msth->execute() || die;
+	my $msth = $mdbh->prepare($msql) || die $mdbh->errstr;
+	$msth->execute() || die $msth->errstr;
 
-	my $ssth = $sdbh->prepare($ssql) || die;
+	my $ssth = $sdbh->prepare($ssql) || die $sdbh->errstr;
+	$ssth->execute() || die $ssth->errstr;
 
 	my $diff_row = 0;
 
 	my ($mrow,$srow);
-	my ($have_mrow,$have_srow) = (0,0);
-	my @pk_val;
+	# 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;
+		}
 
-	my $more_rows = 1;
-	while (!$have_mrow || !$have_srow) {
 		# fetch row from master
-		if (!$have_mrow) {
-			print "fetch row from master [$more_rows]: $msql\n" if ($debug);
+		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
-				@pk_val = ();
-				foreach my $col (@cols_pk) {
-					push @pk_val,$mrow->{$col};
-				}
-				$have_mrow = 1;
+				$have_mrow = HAVE_ROW;
 			} else {
-				$have_mrow = 0;
+				$have_mrow = NO_ROW;
 			}
 		}
 
 		# fetch row from slave
-		if (!$have_srow) {
-			print "fetch row from slave [$more_rows]: $ssql\n" if ($debug);
-			$ssth->execute(@pk_val) || die;
+		if ($have_srow == FETCH_ROW) {
+			debug "fetch row from slave: $ssql\n";
 			$srow = $ssth->fetchrow_hashref();
 			debug_row($srow,@cols);
 			if ($srow) {
-				$have_srow = 1;
+				$have_srow = HAVE_ROW;
 			} else {
-				$have_srow = 0;
+				$have_srow = NO_ROW;
 			}
 		}
 
-		# end of this table?
-		if (!$have_mrow && !$have_srow) {
-			last;
-		}
+		debug "have mrow: $have_mrow srow: $have_srow\n";
 
 		# insert into slave database
 		sub sql_insert {
+			my $dbh = shift @_ || die "need dbh";
 			my $table = shift @_ || die "need table as argument";
 			my $row = shift @_ || die "need row data";
 			my @cols = @_;
@@ -350,7 +297,7 @@
 			my $sql = "insert into $table (".join(",",@cols).") values (";
 			my $comma = "";
 			foreach my $col (@cols) {
-				$sql .= $comma.$mdbh->quote($row->{$col});
+				$sql .= $comma.$dbh->quote($row->{$col});
 				$comma = ",";
 			}
 			$sql.=")";
@@ -360,15 +307,16 @@
 
 		# delete from slave database
 		sub sql_delete {
+			my $dbh = shift @_ || die "need dbh";
 			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 ";
+			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";
+				my $val = $dbh->quote($row->{$col}) || die "can't find value in row for col $col";
 				$where =~ s/\?/$val/;
 			}
 			$sql .= $where;
@@ -376,6 +324,24 @@
 			return $sql;
 		}
 
+		# update row in slave database
+		sub sql_update {
+			my $dbh = shift @_ || die "need dbh";
+			my $table = shift @_ || die "need table as argument";
+			my $col = shift @_ || die "need col to update";
+			my $row = shift @_ || die "need row";
+			my @cols_pk = @_;
+
+			my $sql = "update $table set $col=".$dbh->quote($row->{$col});
+			my $where = sql_where(@cols_pk);
+			foreach my $col (@cols_pk) {
+				my $val = $dbh->quote($row->{$col}) || die "can't find value in row for col $col";
+				$where =~ s/\?/$val/;
+			}
+			$sql .= $where;
+			debug_sql($sql);
+			return $sql;
+		}
 		# master	slave
 		# 1	=	1	test
 		# 1	<	2	insert mrow
@@ -387,31 +353,36 @@
 
 		# check key cols for row
 		foreach my $col (@cols_pk) {
-			if ( (!$mrow && $srow) || ($mrow && $srow && ($mrow->{$col} gt $srow->{$col})) ) {
+			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 sql_delete($table,$srow,@cols_pk),"\n";
-				$have_srow = 0;	# fetch new slave row
+				print STDERR "EXTRA row in table '$table' pk: [".join(",",@cols_pk)."] value (".join(",",pk_val($srow,@cols_pk)).")\n" if ($verbose);
+				print sql_delete($sdbh,$table,$srow,@cols_pk),";\n";
+				$have_srow = FETCH_ROW;
 				last;
-			} elsif ( ($mrow && !$srow) || ($mrow && $srow && ($mrow->{$col} lt $srow->{$col})) ) {
+			} 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 sql_insert($table,$mrow,@cols),"\n";
-				$have_mrow = 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($mdbh,$table,$mrow,@cols),";\n";
+				$have_mrow = FETCH_ROW;
 				last;
 			}
 		}
 
-		if ($pk_same) {
+		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(",",@pk_val)."] '$mrow->{$col}' != '$srow->{$col}'\n" if ($verbose);
-					print sql_delete($table,$srow,@cols_pk),"\n";
-					print sql_insert($table,$mrow,@cols),"\n";
+					print STDERR "DIFF in table '$table' row ($col): [".join(",",@cols_pk)."] '$mrow->{$col}' != '$srow->{$col}'\n" if ($verbose);
+					print sql_update($mdbh,$table,$col,$mrow,@cols_pk),";\n";
 				}
 			}
+			$have_mrow = FETCH_ROW;
+			$have_srow = FETCH_ROW;
 		}
 	}
 
@@ -419,7 +390,22 @@
 	$diff_total += $diff_row;
 }
 
-print STDERR "$diff_total differences in all tables\n" if ($verbose && $diff_total > 0);
+if ($verbose) {
+	if ($diff_total == 0) {
+		print STDERR "databases are same";
+	} elsif ($diff_total > 0) {
+		print STDERR "$diff_total differences in all tables\n";
+	} else {
+		die "this shouldn't happend. please report a bug!";
+	}
+}
+
+# enable triggers again on slave
+foreach my $tr (@triggers) {
+	print "update pg_trigger set tgenabled = true where tgname='$tr';\n";
+}
+# end transaction
+print "commit;\n";
 
 $mdbh->disconnect();
 $sdbh->disconnect();