--- pgdiff	2003/08/12 11:43:36	1.2
+++ pgdiff	2003/08/12 18:45:04	1.4
@@ -11,6 +11,7 @@
 use Getopt::Long;
 use DBI;
 use Data::Dumper;
+use Pg::Scheme;
 
 $| = 1;
 
@@ -105,33 +106,12 @@
 
 $verbose = 1 if ($debug);
 
+# init object for scheme in master database
+my $mscheme = new Pg::Scheme( 'dbh' => $mdbh ) || die "can't query 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_%'
-	";
-	foreach my $table (@tables) {
-		$sql .= " and c.relname like '$table'";
-	}
-	my $sth = $mdbh->prepare($sql);
-	$sth->execute() || die;
-	while(my $row = $sth->fetchrow_hashref()) {
-		push @tables,$row->{table};
-	}
-}
+my @tables = $mscheme->list_tables($tables);
 
 debug "Comparing tables: ".join(", ",@tables)."\n";
 
@@ -140,27 +120,7 @@
 
 foreach my $table (@tables) {
 
-	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;
-	}
-
-	my $oid = $row->{oid};
+	my $sth;
 
 	# diff schema
 
@@ -170,18 +130,8 @@
 	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->explain_table($table)) {
 		# attname | format_type | attnotnull | atthasdef | attnum
 
 		push @cols,$row->{attname};
@@ -201,12 +151,11 @@
 			push @cols_null,$row->{attname};
 		}
 	}
-	$sth->finish();
 
 	if ($debug) {
-		print STDERR "DEBUG: 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";
 	}
 
@@ -236,7 +185,7 @@
 	debug_sql($sql);
 	$sth = $mdbh->prepare($sql);
 	$sth->execute() || die;
-	$row = $sth->fetchrow_hashref();
+	my $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";
@@ -298,7 +247,6 @@
 	$ssql .= $order;
 
 	debug_sql($msql);
-	debug_sql($ssql);
 
 	my $msth = $mdbh->prepare($msql) || die;
 	$msth->execute() || die;
@@ -392,6 +340,17 @@
 			return $sql;
 		}
 
+		# update row in slave database
+		sub sql_update {
+			my $table = shift @_ || die "need table as argument";
+			my $col = shift @_ || die "need col to update";
+			my $val = shift @_ || die "need new val";
+			my @cols_pk = @_ || die "need pk idenitifier";
+
+			my $sql = "udate $table set $col=".$mdbh->quote($val);
+			debug_sql($sql);
+			return $sql;
+		}
 		# master	slave
 		# 1	=	1	test
 		# 1	<	2	insert mrow
@@ -407,14 +366,16 @@
 				($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";
+				print STDERR "EXTRA row in table '$table' pk: [".join(",",@cols_pk)."] value (".join(",",pk_val($srow,@cols_pk)).")\n" if ($verbose);
+				print sql_delete($table,$srow,@cols_pk),";\n";
 				$have_srow = FETCH_ROW;
 				last;
 			} 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";
+				print STDERR "MISSING row in table '$table' pk: [".join(",",@cols_pk)."] value (".join(",",pk_val($mrow,@cols_pk)).")\n" if ($verbose);
+				print sql_insert($table,$mrow,@cols),";\n";
 				$have_mrow = FETCH_ROW;
 				last;
 			}
@@ -426,12 +387,11 @@
 				if ($mrow->{$col} ne $srow->{$col}) {
 					$diff_row++;
 					print STDERR "DIFF in table '$table' row ($col): [".join(",",@cols_pk)."] '$mrow->{$col}' != '$srow->{$col}'\n" if ($verbose);
-					print sql_delete($table,$srow,@cols_pk),"\n";
-					print sql_insert($table,$mrow,@cols),"\n";
-					$have_mrow = FETCH_ROW;
-					$have_srow = FETCH_ROW;
+					print sql_update($table,$col,$mrow->{$col},@cols_pk),";\n";
 				}
 			}
+			$have_mrow = FETCH_ROW;
+			$have_srow = FETCH_ROW;
 		}
 	}
 
@@ -439,7 +399,15 @@
 	$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!";
+	}
+}
 
 $mdbh->disconnect();
 $sdbh->disconnect();