Sorting with DBD::SQLite and Mac OS X
At least in my Mac OS X (Leopard) the perllocale and the perl collations that DBD::SQLite make available do not work to properly sort UTF. Or that, or I am doing something wrong.
Nevertheless, I found a simple way to do that, thanks to the Unicode::Collate module. Just add a few lines of code after you open your database connection:
my $collator = Unicode::Collate->new();
$DBD::SQLite::COLLATION{mine} = sub { $collator->cmp(@_) };
and then use the mine collation:
$sth = $dbh->prepare("SELECT * FROM somewhere ORDER BY key COLLATE mine");
Now I just need a way to make Perl not complain about the usage of DBD::SQLite::COLLATION just once. I can set no warnings around it, but I would prefer a cleaner solution. Maybe suggest a define_collation method for DBD::SQLite.
I blog about Perl. D'uh!
Hi Alberto
Did you try setting SQLite's unicode flag?
Try something like:
sub BUILD
{
my($self) = @_;
my($config) = $self -> config;
my($attr) = {AutoCommit => $$config{AutoCommit}, RaiseError => $$config{RaiseError} };
if ( ($$config{dsn} =~ /SQLite/i) && $$config{sqlite_unicode})
{
$$attr{sqlite_unicode} = 1;
}
$self -> connector
(
DBIx::Connector -> new($$config{dsn}, $$config{username}, $$config{password}, $attr)
);
if ($$config{dsn} =~ /SQLite/i)
{
$self -> connector -> dbh -> do('PRAGMA foreign_keys = ON');
}
$self -> logger
(
Business::Cart::Generic::Util::Logger -> new(config => $config)
);
} # End of BUILD.
Hey
Yes, I have the unicode flag set. That same code works perfectly under linux (using the perllocale COLLATION), but for some time (years, in fact) that I'm mostly sure locales are fucked up in Mac OS X :) I have some freaking problems with C++ too. And now I do not have Jobs to complain to :)
Thanks
There is a very simple, cheap and stupid trick to shut up the used-once warning of course.
Hahaha.. great, thanks Aristotle :)
Hi Aristotle
Your code, split by me:
$DBD::SQLite::COLLATION{mine} =
$DBD::SQLite::COLLATION{mine} =
sub { $c
gets truncated at the $c. And that's whether or not I log in.
Is it truncated for every viewer, or is there some trick I use use to see the rest of that line?
TIA.
Ron
The line is there, the overflow just gets hidden by something silly in the site’s stylesheet. You can triple-click to select and then copy it, then paste it in some editor. There’s nothing interesting in the hidden part, though. It is just Alberto’s line, with the assignment doubled: