Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:57483
Mailing-List: contact internals-help@lists.php.net; run by ezmlm
Received-SPF: pass (pb1.pair.com: domain gmail.com designates 209.85.215.42 as permitted sender)
MIME-Version: 1.0
Date: Mon, 23 Jan 2012 18:28:41 -0400
Message-ID: <CAEakXBrh4H4f+N0XU_fPxWCnnS3eCAguD3DQwe-iyoKjWf9U+A@mail.gmail.com>
To: internals@lists.php.net
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Subject: SQLite3 class missing createCollation()
From: macdewar@gmail.com (b dewar)

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.=C2=A0 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 =3D array(
=C2=A0 'ab1.png',
=C2=A0 'ab2.png',
=C2=A0 'ab10.png',
=C2=A0 'ab11.png',
=C2=A0 'ac.png',
=C2=A0 'aa.png',
=C2=A0 'ab3.png');

$db =3D new SQLite3(':memory:');

$db->createCollation('NATURAL_SORT', 'strnatcmp');

$db->exec("CREATE TABLE filenames (fname varchar(32));");

$stmt =3D $db->prepare("INSERT INTO filenames VALUES (?);");
foreach($filenames as $fname){
=C2=A0 $stmt->bindParam(1, $fname);
=C2=A0 $stmt->execute();
}
$result =3D $db->query("SELECT fname FROM filenames ORDER BY fname
COLLATE NATURAL_SORT;");
while($row =3D $result->fetchArray()){
=C2=A0 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