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 1
my $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 TRANSACTION
foreach (@$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(at)albourne(dot)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