Re: MySql 'REPLACE'

Thomas Swan wrote:

> You should be able to do this with two separate queries inside a
> transaction.

Yes, sorry, I was not clear enough. Unfortunately, the function I need
to write is a "generic" one that takes a number of fields/values and
generate a SQL instruction. So, there is no previous information about
the underlining table structure. What I did is a query to pg_class and
other pg_* tables to get a list of unique indexes and build the DELETEs
needed prior the INSERT.

> The only part I'm not clear on is whether to use an 'and' or an 'or'
> on the delete.  Check and see if all values have to match or if
> partial matches, i.e. only one of the columns, are acceptable.  If it
> does partial matching, then use the 'or', otherwise use the 'and'.

I went for AND.

You're welcome to check if my (Perl) code is completely wrong :-)

This is the MySql version:

sub sqlReplace {my($self, $table, $data) = @_;my($names, $values);
foreach (keys %$data) {	if (/^-/) {		$values .= "\n  $data->{$_},";		s/^-//;	} else {		$values .= "\n  " . $self->{_dbh}->quote($data->{$_}) . ',';	}	$names .= "$_,";}
chop($names);chop($values);
my $sql = "REPLACE INTO $table ($names) VALUES($values)\n";$self->sqlConnect();return $self->sqlDo($sql) or errorLog($sql);
}

and my PostgreSQL version:

sub sqlReplace {my($self, $table, $data) = @_;my($names, $values);
foreach (keys %$data) {	if (/^-/) {		$values .= "\n  $data->{$_},";		s/^-//;	} else {		$values .= "\n  " . $self->{_dbh}->quote($data->{$_}) . ',';	}	$names .= "$_,";}
chop($names);chop($values);
# We study the table structure - this code comes from psql -E
my $cols = $self->{_dbh}->selectcol_arrayref  (q{SELECT a.attname      FROM pg_class c, pg_attribute a      WHERE c.relname = ? AND a.attnum > 0 AND a.attrelid = c.oid      ORDER BY a.attnum}, undef, $table) || [];       unshift @$cols, ''; # To have values starting at index 1my $all_uniq = $self->{_dbh}->selectcol_arrayref  (q{SELECT indkey      FROM pg_class c, pg_class c2, pg_index i      WHERE c.relname = ? AND c.oid = i.indrelid AND        i.indexrelid = c2.oid AND indisunique IS TRUE},    undef, $table) || [];$self->{_dbh}->{AutoCommit} = 0; # BEGIN TRANSACTIONforeach (@$all_uniq) {  my @acols = @$cols[split];  my $check = 1;  map {$check &&= defined $data->{$_}} @acols;  next unless $check;  my $sql = "DELETE FROM $table WHERE " .     join (' AND ', 	  map "$_ = " . $self->{_dbh}->quote($data->{$_}), @acols);  $self->{_dbh}->do ($sql);}
my $sql = "INSERT INTO $table ($names) VALUES ($values)";$self->{_dbh}->do($sql);$self->{_dbh}->commit; # END TRANSACTION# return $self->sqlDo($sql) or errorLog($sql);
}

-- 
Alessio F. Bragadini		alessio@albourne.com
APL Financial Services		http://village.albourne.com
Nicosia, Cyprus		 	phone: +357-2-755750

"It is more complicated than you think"	-- The Eighth Networking Truth from RFC 1925