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.


Hi Alberto

Did you try setting SQLite's unicode flag?

Try something like:

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.

There is a very simple, cheap and stupid trick to shut up the used-once warning of course.

$DBD::SQLite::COLLATION{mine} = $DBD::SQLite::COLLATION{mine} = sub { $collator->cmp(@_) };

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?


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:

$DBD::SQLite::COLLATION{mine} =
$DBD::SQLite::COLLATION{mine} = sub { $collator->cmp(@_) };

Leave a comment

About Alberto Simões

user-pic I blog about Perl. D'uh!