SQLite3 class missing createCollation() View as Markdown

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