--- pgdiff	2003/08/12 20:38:02	1.5
+++ pgdiff	2003/10/28 18:56:54	1.11
@@ -43,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;
@@ -63,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);
 
@@ -107,7 +119,8 @@
 $verbose = 1 if ($debug);
 
 # init object for scheme in master database
-my $mscheme = new Pg::Scheme( 'dbh' => $mdbh, 'DEBUG' => 0 ) || die "can't query schema";
+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?
 
@@ -115,6 +128,16 @@
 
 debug "Comparing tables: ".join(", ",@tables)."\n";
 
+# start transaction
+print "begin work;\n";
+
+# disable active triggers on slave database
+my @triggers = $sscheme->get_activetriggers();
+
+foreach my $tr (@triggers) {
+	print "update pg_trigger set tgenabled = false where tgname='$tr';\n";
+}
+
 my $cols;
 my $diff_total = 0;
 
@@ -122,7 +145,22 @@
 
 	my $sth;
 
+print "-- schema...\n";
 	# diff schema
+	foreach my $row (@{$mscheme->pg_attribute($table)}) {
+#		print Dumper($row);
+	}
+
+print "-- constraints...\n";
+	# diff constraints
+	foreach my $tr (@{$mscheme->triggers($table)}) {
+#		print Dumper($tr);
+	}
+print "-- triggers...\n";
+	# diff triggers
+	foreach my $tr (@{$mscheme->triggers($table)}) {
+#		print Dumper($tr);
+	}
 
 	# all colums (for insert)
 	my @cols = @{$mscheme->cols($table)};
@@ -143,7 +181,7 @@
 	my @cols_test;	# all colums to test (without skipped)
 
 	foreach my $row (@{$mscheme->pg_attribute($table)}) {
-		# attname | format_type | attnotnull | atthasdef | attnum
+		# attname format_type attnotnull atthasdef attnum default references
 
 		# FIXME: do something with attributes which shouldn't be compared
 		# (date, time, datetime, timestamp)
@@ -207,11 +245,11 @@
 
 	debug_sql($msql);
 
-	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;
-	$ssth->execute() || die;
+	my $ssth = $sdbh->prepare($ssql) || die $sdbh->errstr;
+	$ssth->execute() || die $ssth->errstr;
 
 	my $diff_row = 0;
 
@@ -266,6 +304,7 @@
 
 		# 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 = @_;
@@ -273,7 +312,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.=")";
@@ -283,15 +322,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;
@@ -301,12 +341,19 @@
 
 		# 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 $val = shift @_ || die "need new val";
-			my @cols_pk = @_ || die "need pk idenitifier";
+			my $row = shift @_ || die "need row";
+			my @cols_pk = @_;
 
-			my $sql = "udate $table set $col=".$mdbh->quote($val);
+			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;
 		}
@@ -326,7 +373,7 @@
 				$diff_row++;
 				$pk_same = 0;
 				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";
+				print sql_delete($sdbh,$table,$srow,@cols_pk),";\n";
 				$have_srow = FETCH_ROW;
 				last;
 			} elsif ( ($have_mrow == HAVE_ROW && $have_srow == NO_ROW) ||
@@ -334,7 +381,7 @@
 				$diff_row++;
 				$pk_same = 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($table,$mrow,@cols),";\n";
+				print sql_insert($mdbh,$table,$mrow,@cols),";\n";
 				$have_mrow = FETCH_ROW;
 				last;
 			}
@@ -346,7 +393,7 @@
 				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_update($table,$col,$mrow->{$col},@cols_pk),";\n";
+					print sql_update($mdbh,$table,$col,$mrow,@cols_pk),";\n";
 				}
 			}
 			$have_mrow = FETCH_ROW;
@@ -368,5 +415,12 @@
 	}
 }
 
+# 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();