sqlite3 and result ordering according to non-ASCII collations

For a while I was not sure how to order query results in Codeigniter when using sqlite3 database, which natively lacks non-ascii text collation functions. So I resorted to sorting php arrays, which is quite suboptimal with larger datasets.

When I took a closer look, I realized that it is quite simple to handle sorting in vanilla PHP case. Below I post an example of creating a Lithuanian collation and using it with sqlite3.

But I see createCollation and createFunction sqlite3 driver methods are not used in Codeigniter4 code. So I have to find a way to implement these myself.

Could someone take a look at the vanilla php example below and maybe throw in a suggestion on how to optimally do that in Codeigniter?

Vanilla php example

PHP Code:

// the locale offers proper collation order, set it if it is not default
setlocale(LC_ALL, 'lt_LT.UTF-8');

$db = new SQLite3(baz.db);

// create collation
$db->createCollation('LT', function ($a, $b) {
   return strcoll($a, $b);
});

// create alternative to native LOWER function for case-insensitive comparisons
$db->createFunction('LT_LOWER', function ($str) {
   return mb_strtolower($str);
}); 


And you continue as if you were using native sqlite3 functions:

PHP Code:

$results = $db->query('SELECT bar FROM foo where LT_LOWER(foo) LIKE %Žemai%' ORDER BY bar ASC COLLATE LT);
while ($row = $results->fetchArray()) {
    var_dump($row);
} 


You can also assign this LT collation as default to individual columns when creating a table (and then omit it in select statements), but then you won't be able to read that table without declaring those additional collation methods before the basic select queries.

==

Donatas G.