Wednesday, December 09, 2009

Renaming a MySQL database without having access to datafiles

If you've used MySQL then you will know this situation; your database name is wrong & MySQL does not support any kind of command like

rename database my_db to my_real_name;

In fact the only way to do this is to rename the directory name the database files are held in on the data store; or so I thought. Turns out that the rename table command comes to the rescue with this beautiful line
As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another:

Giving us something quite simple looking like this:

RENAME TABLE my_db.tbl_name TO my_real_name.tbl_name;

Pin this together with a loop over all tables in a schema & bob is your uncle you've got a database re-namer without going anywhere near the datafiles. The thing that confuses me is why MySQL dropped support for the rename function (which is still documented) when they could have implemented it like this; I'm sure they had their reasons.

Tuesday, December 08, 2009

Operator Precedence

It's that time again when I mention something that's already in the perldocs; this one is about operator precedence. Anyway today I found out that something like
my @a = qw(1);
print 'Yippeee!' if ( ! scalar(@a) >= 2 );
What does this print out? Yippeee!? Nope. The problem here is operator precedence. You may read it as the count of @a being greater than or equal to 2 and negate this (after all that's what ! normally means). Well what actually gets evaluated is ! count and then if this result is >= 2. Possible solutions to this are
print 'Yippeee!' if ( ! (scalar(@a) >= 2) );
print 'Yippeee!' if ( scalar(@a) < 2);
So the first deals with it by adding () to make sure the evaluation order is respected, the second replaces it for a more logical test.

What is the take home message? Everything in Perl has a precedence order.

Wednesday, December 02, 2009

each() in Perl can be dangerous

I'm a Java programmer by trade and one of the first things that is hammered into us (right after never using + for multiple string concatenation) is how to correctly iterate through a Map. Now the reason why is if you want to iterate through a Map's contents using keys only and then performing the value retrieval using its get() method then that is really inefficient. Java's hash implementations are good but considering the majority of the time keys are stored with their values in the backing datastructures; to get the pair out at the same time makes sense. So the first time you come to Perl you think
how on earth do I iterate through a hash?
and then you see the each() method. This gives rise to the following code:


while( my ($key, $value) = each(%my_hash)) {
print $key, '->', $value, "\n";
}


This all seems fine & dandy until we want to do something like this:


our %my_hash = (a=>1,b=>2);
my @elements = get_from_somewhere();
print "Starting\n";
OUTERLOOP: foreach my $el (@elements) {
while( my $key, $value) = each(%my_hash)) {
print $key, "\n";
if($key eq 'a') {
last OUTERLOOP;
}
}
}


Still seems okay right? Wrong! See the problem here comes from iteration of the EntrySet belonging to the hash & not to another data structure so on multiple invocations of this code the iteration of the map continues from where it left-off. So if the elements from the hash are returned in the order a,b & we execute it twice you will see:


Starting
a
Starting
b
a


To get to the correct expected output you need:


our %my_hash = (a=>1,b=>2);
my @elements = get_from_somewhere();
print "Starting\n";
OUTERLOOP: foreach my $el (@elements) {
foreach my $key (keys %my_hash) {
my $value = $my_hash{$key};
print $key, "\n";
if($key eq 'a') {
last OUTERLOOP;
}
}
}


So what's the solution? Well if I said never use each then I would be stupid & wrong. each makes sense. What I would recommend is if you might break out of a loop using a data structure where you may require early termination of a hash structure which is persistent between calls then do not use each. Or use one of the Iterator functions/modules in CPAN to provide a more Java like method of access to hashes; but then that wouldn't be Perl :).