пятница, 3 декабря 2010 г.

DBI-Link - why is it so buggy?

I've spent several hours trying to make dbi-link work - I had errors related to table names and errors related to dbi_link.shadow_trigger_func(). However, I couldn't just get up - I was asked to suggest a sollution for MySQL <=> PostgreSQL replication and this module could help me to do the job...
So, I've found the following post , which helped me a lot, so I modified dbi_link.shadow_trigger_func to look the following way:

CREATE OR REPLACE FUNCTION dbi_link.shadow_trigger_func()
RETURNS TRIGGER
LANGUAGE plperlu
AS $$
require 5.8.3;
######################################################
# #
# Immediately reject anything that is not an INSERT. #
# #
######################################################
if ($_TD->{event} ne 'INSERT') {
return "SKIP";
}
spi_exec_query('SELECT dbi_link.dbi_link_init()');
my $data_source_id = shift;
die "In shadow_trigger_function, data_source_id must be an integer"
unless ($data_source_id =~ /^\d+$/);
my $query = "SELECT dbi_link.cache_connection( $data_source_id )";
warn "In shadow_trigger_function, calling\n $query" if $_SHARED{debug};
warn "In shadow_trigger_function, the trigger payload is\n". Dump(\$_TD) if $_SHARED{debug};
my $rv = spi_exec_query($query);
my $remote_schema = $_SHARED{get_connection_info}->({
data_source_id => $data_source_id
})->{remote_schema};
my $table = $_TD->{relname};
warn "Raw table name is $table" if $_SHARED{debug};
warn "In trigger on $table, action is $_TD->{new}{iud_action}" if $_SHARED{debug};
$table =~ s{
\A # Beginning of string.
(.*) # Actual table name.
_shadow # Strip off shadow.
\z # End of string.
}
{$1}sx;
$table = $remote_schema . "." . $table if defined $remote_schema;
warn "Cooked table name is $table" if $_SHARED{debug};
my $iud = {
I => \&do_insert,
U => \&do_update,
D => \&do_delete,
};
if ($iud->{ $_TD->{new}{iud_action} }) {
$iud->{ $_TD->{new}{iud_action} }->({
payload => $_TD->{new},
tbl => $table,
source_id => $data_source_id
});
}
else {
die "Trigger event was $_TD->{new}{iud_action}<, but should have been one of I, U or D!"
}
return 'SKIP';
sub do_insert {
my ($params) = @_;
die "In do_insert, must pass a payload!"
unless (defined $params->{payload});
die "In do_insert, payload must be a hash reference!"
unless (ref $params->{payload} eq 'HASH');
my (@keys, @values);
foreach my $key (sort keys %{ $params->{payload} } ) {
next unless $key =~ /^.?new_(.*)/;
my $real_key = $1;
push @keys, $real_key;
push @values, $_SHARED{dbh}{ $params->{source_id} }->quote(
$params->{payload}{$key}
);
}
my $sql = <<SQL;
INSERT INTO $params->{tbl} (
@{[
join(
",\n ",
@keys
)
]}
)
VALUES (
@{[
join(
",\n ",
@values
)
]}
)
SQL
warn "SQL is\n$sql" if $_SHARED{debug};
$_SHARED{dbh}{ $params->{source_id} }->do($sql);
}
sub do_update {
my ($params) = @_;
die "In do_update, must pass a payload!"
unless (defined $params->{payload});
die "In do_update, payload must be a hash reference!"
unless (ref $params->{payload} eq 'HASH');
my $sql = <<SQL;
UPDATE $params->{tbl}
SET
@{[ make_pairs({
payload => $params->{payload},
which => 'new',
joiner => ",\n ",
transform_null => 'false',
source_id => "$params->{source_id}"
}) ]}
WHERE
@{[ make_pairs({
payload => $params->{payload},
which => 'old',
joiner => "\nAND ",
transform_null => 'true',
source_id => "$params->{source_id}"
}) ]}
SQL
warn "SQL is\n$sql" if $_SHARED{debug};
$_SHARED{dbh}{ $params->{source_id} }->do($sql);
}
sub do_delete {
my ($params) = @_;
die "In do_delete, must pass a payload!"
unless (defined $params->{payload});
die "In do_delete, payload must be a hash reference!"
unless (ref $params->{payload} eq 'HASH');
my $sql = <<SQL;
DELETE FROM $params->{tbl}
WHERE
@{[ make_pairs({
payload => $params->{payload},
which => 'old',
joiner => "\nAND ",
transform_null => 'true',
source_id => "$params->{source_id}"
}) ]}
SQL
warn "SQL is\n$sql" if $_SHARED{debug};
$_SHARED{dbh}{ $params->{source_id} }->do($sql);
}
sub make_pairs {
my ($params) = @_;
die "In make_pairs, must pass a payload!"
unless (defined $params->{payload});
die "In make_pairs, payload must be a hash reference!"
unless (ref $params->{payload} eq 'HASH');
warn "In make_pairs, parameters are:\n". Dump($params) if $_SHARED{debug};
my @pairs;
foreach my $key (
keys %{ $params->{payload} }
) {
next unless $key =~ m/^(.?)$params->{which}_(.*)/;
my $left = "$1$2";
warn "In make_pairs, raw key is $key, cooked key is $left" if $_SHARED{debug};
warn "In make_pairs, pairs are @pairs" if $_SHARED{debug};
$bl=!defined $params->{payload}{$key};
$bl1=defined $params->{transform_null};
if (
defined $params->{transform_null} && # In a WHERE clause,
!defined $params->{payload}{$key} # turn undef into IS NULL
) {
push (@pairs, "$left");
}
else {
warn "o, fuck";
warn "params->source_id is $params->{source_id}";
push @pairs, "$left = " . $_SHARED{dbh}{ $params->{source_id} }->quote(
$params->{payload}{$key}
);
}
}
my $ret =
join (
$params->{joiner},
@pairs,
);
warn "In make_pairs, the pairs are:\n". Dump(\@pairs) if $_SHARED{debug};
return $ret;
}
$$;



And this worked! Now I just can create after each row triggers on local PostgreSQL table and promote data to MySQL. I like DBI-Link, however, if I were to use it in production I would examine its code attentively...

Комментариев нет:

Отправить комментарий