# SQLite3 class missing createCollation()

- Thread: https://externals.io/message/57483
- Original archive: https://news-web.php.net/php.internals/57483
- Started by b dewar on 2012-01-23T22:28:41+00:00
- Messages: 3
- Reply by email to internals@lists.php.net
- More for agents: search with https://externals.io/search.md?q={query} · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt

---

## #57483 — b dewar — 2012-01-23T22:28:41+00:00

URL: https://externals.io/message/57483#57483

Hi

I initially sent this to php-db, but I guess it really belongs on internals.

The C API for SQLite3 has an sqlite3_create_collation() feature that's
missing from PHP's SQLite3 class API.

I'm working on a patch that adds the SQLite3::createCollation(string
collation_name, mixed callback) method.  This allows registering a PHP
function as a comparator for the database to use while collating data.
 The callback passed to createCollation() should act like `strcmp()`,
i.e. it should accept two strings and return an integer indicating
their relative sort order.

e.g.

<?php

$filenames = array(
  'ab1.png',
  'ab2.png',
  'ab10.png',
  'ab11.png',
  'ac.png',
  'aa.png',
  'ab3.png');

$db = new SQLite3(':memory:');

$db->createCollation('NATURAL_SORT', 'strnatcmp');

$db->exec("CREATE TABLE filenames (fname varchar(32));");

$stmt = $db->prepare("INSERT INTO filenames VALUES (?);");
foreach($filenames as $fname){
  $stmt->bindParam(1, $fname);
  $stmt->execute();
}
$result = $db->query("SELECT fname FROM filenames ORDER BY fname
COLLATE NATURAL_SORT;");
while($row = $result->fetchArray()){
  echo $row['fname'], "\n";
}

$db->close();

?>

Output:

aa.png
ab1.png
ab2.png
ab3.png
ab10.png
ab11.png
ac.png



I've built 5.3.9 with my patch on Ubuntu on x86_64, and it works as
expected. I've never submitted a patch for PHP before, so I could use
some advice/hand-holding on the process.

Thanks,
Brad

---

## #57484 — Damien Tournoud — 2012-01-23T22:44:02+00:00

URL: https://externals.io/message/57483#57484 · In reply to: #57483

Also see https://bugs.php.net/bug.php?id=55226

Same idea, but for the PDO_sqlite implementation.

Damien


> Hi
>
> I initially sent this to php-db, but I guess it really belongs on internals.
>
> The C API for SQLite3 has an sqlite3_create_collation() feature that's
> missing from PHP's SQLite3 class API.
>
> I'm working on a patch that adds the SQLite3::createCollation(string
> collation_name, mixed callback) method.  This allows registering a PHP
> function as a comparator for the database to use while collating data.
>  The callback passed to createCollation() should act like `strcmp()`,
> i.e. it should accept two strings and return an integer indicating
> their relative sort order.
>
> e.g.
>
> <?php
>
> $filenames = array(
>   'ab1.png',
>   'ab2.png',
>   'ab10.png',
>   'ab11.png',
>   'ac.png',
>   'aa.png',
>   'ab3.png');
>
> $db = new SQLite3(':memory:');
>
> $db->createCollation('NATURAL_SORT', 'strnatcmp');
>
> $db->exec("CREATE TABLE filenames (fname varchar(32));");
>
> $stmt = $db->prepare("INSERT INTO filenames VALUES (?);");
> foreach($filenames as $fname){
>   $stmt->bindParam(1, $fname);
>   $stmt->execute();
> }
> $result = $db->query("SELECT fname FROM filenames ORDER BY fname
> COLLATE NATURAL_SORT;");
> while($row = $result->fetchArray()){
>   echo $row['fname'], "\n";
> }
>
> $db->close();
>
> ?>
>
> Output:
>
> aa.png
> ab1.png
> ab2.png
> ab3.png
> ab10.png
> ab11.png
> ac.png
>
>
>
> I've built 5.3.9 with my patch on Ubuntu on x86_64, and it works as
> expected. I've never submitted a patch for PHP before, so I could use
> some advice/hand-holding on the process.
>
> Thanks,
> Brad

---

## #57490 — b dewar — 2012-01-24T19:21:48+00:00

URL: https://externals.io/message/57483#57490 · In reply to: #57484

> Also see https://bugs.php.net/bug.php?id=55226
>
> Same idea, but for the PDO_sqlite implementation.
>
> Damien
>

Thanks.

I submitted my patch, and commented on both, referring each to the other.