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
FROMpg_class c, pg_class c2, pg_index i      WHERE c.relname = ? AND c.oid = i.indrelid AND        i.indexrelid =
c2.oidAND 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