> I've been tasked with migrating XML data from Postgres to Oracle.
> However, some of our XML entities make Oracle choke (don't ask me
> why, im the postgres guy, and it works in _my_ database...). So
> I'm parsing over the XML data which already lives in postgres.
> Take for example the following code:
It took me a couple of read-thru's to see the question in this. I'm still
not completely certain I've read it right, but I'll try.
> while (my $ref = $iterator -> fetchrow_arrayref() ) {
> my ($filename, $contents) = @{ $ref };
> $contents = munge($contents);
> $heap -> {dbiagent} -> push_query(
> query => qq{ update xml_munged set tag1 = ?, tag2 =
> ? tag3 = ? },
> bindvals => [ @tags ],
> );
> }
To code the above with PoCo::DBIAgent, you would define two queries, say
{
select_data_to_munge => 'select xmlfoo from bar',
update_munged_xml => 'update xml_munged set tag1 = ?, tag2 = ? tag3 = ?'
}
but they are defined in the constructor the DBIAgent.
THEN, in one state, we issue the select query:
$heap->{helper}->query(select_data_to_munge => current_session_name =>
'update_munged');
so that when the data comes back from the select query, each row invokes:
sub update_munged {
my ($heap, $row) = @_[HEAP, ARGV0];
return unless defined $row;
return if $row eq 'EOF';
# $row is an arrayref
my @fields = @{ $row };
#
# munge up fields for a minute
#
$heap->{helper}->query(update_munged_xml => current_session_name =>
finished_munging, @fields);
}
sub finished_munging {
# one update has completed. A noop is acceptable,
# or else the next phase of processing after the
# database insert
}
> So here we see stuff that (as far as I can tell from reading the
> pod) is just sloughed off to the agent. I know the update is
> going to take a while since I'm actually passing it 30 columns
> for its update plus the actual XML, however my select operates
> much faster (my selects on a heavily indexed table work at around
> 360/s versus 90/s for the inserts/updates). In this scenario, I
> don't really care about a return value, I just want to have the
> database deal with the inserts, and POE make it easy for perl to
> do this. I'm also aware that this will be hard on the machine
> running Oracle, as well as the machine running perl. This doesn't
> bother me too much, the Oracle server is an E3500, and the perl
> machine is "expendable."
You'll want to have at least two agents for this to be effective, one for
the select and the other(s) can simultaneously be handling updates.
Each will block in its own process, then POE will fire off the event
specified in the query() call when it gets a result back.
It will only be as hard on the Oracle database as running (num_of_agents)
inserts at a time. The rest are simply queued for execution as agents
become free to handle the queries.
So, did I succesfully assess the question? And if so, does this answer
help?
L8r,
Rob
#!/usr/bin/perl -w
use Disclaimer qw/:standard/;
