perl libSqliteIcu.so collate icu

Question

The libSqliteIcu works fine in sqlite3 terminal with:

sqlite3
...
.load './libSqliteIcu.so'
SELECT icu_load_collation('pl_PL', 'POLISH');
SELECT DISTINCT miasto FROM tab ORDER BY miasto COLLATE POLISH;

How to code the sequence above into perl?

Answer 1

Untested:

use DBI qw();
my $dbh = DBI->connect('DBI:SQLite:thedatabase.sqlite');
$dbh->sqlite_enable_load_extension(1);
$dbh->do(q{SELECT load_extension('./libSqliteIcu.so')});
$dbh->do(q{SELECT icu_load_collation('pl_PL', 'POLISH')};
my $miasto = $dbh->selectall_arrayref(q{SELECT DISTINCT miasto FROM tab ORDER BY miasto COLLATE POLISH});

---

---

Alternatively, sort in Perl:

use utf8;
my @tongue_twister = qw(Król Karol kupił królowej Karolinie korale koloru koralowego);

---

use Unicode::ICU::Collator qw();    # ICU C library, fast
my $c = Unicode::ICU::Collator->new('pl_PL');

---

use Unicode::Collate qw();          # pure-perl, slow
my $c = Unicode::Collate->new(locale => 'pl_PL');

---

my @sorted = $c->sort(@tongue_twister);
# (
#     'Karol',
#     'Karolinie',
#     'koloru',
#     'korale',
#     'koralowego',
#     'Król',
#     'królowej',
#     'kupił'
# )

Answer 2

One simple, but not perfect, solution is to use pl_PL.UTF8 locale.

In sqlite3 we prepare:

create table A(idA);
...
insert into A values('ź');
insert into A values('Ż');
insert into A values('ź');
insert into A values('Š');
...
insert into A values('ś');
...

and now:

select * from A;

gives: Z ź Ż ź z ś ż S Š z.

So we load:

.load './libSqliteIcu.so'
SELECT icu_load_collation('pl_PL', 'POLISH');
select idA from A order by idA collate POLISH;

and we have: S Š ś z z Z ź ź ż Ż.

So now in perl we can put:

SELECT idA FROM A ORDER BY idA

and get not correct order: S Z z z ś Š ź ź Ż ż

Now, with perllocale we put in perl:

SELECT idA FROM A ORDER BY idA COLLATE perllocale

and get the order: S Š ś z z Z ź ź ż Ż.

The $dbh->do(q{SELECT load_extension('./libSqliteIcu.so')}); does not work in my case; it makes an error:

DBD::SQLite::db do failed: unable to delete/modify user-function due to active statements error during initialization