php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #60871 Support creating collations in SQLite3 class
Submitted: 2012-01-24 19:09 UTC Modified: 2012-01-29 04:01 UTC
From: macdewar at gmail dot com Assigned: rasmus (profile)
Status: Closed Package: SQLite related
PHP Version: 5.3.9 OS: GNU/Linux
Private report: No CVE-ID: None
 [2012-01-24 19:09 UTC] macdewar at gmail dot com
Description:
------------
The C API for SQLite3 offers an sqlite3_create_collation() feature that is missing from the PHP SQLite3 class.  createCollation() should be added to SQLite3, much like createFunction() and createAggregate().

This allows using a PHP function or user-defined function as a collation for use in SQL, e.g.:

$db->createCollation('my_sort_rule', function($a,$b){
  return custom_compare($a,$b);
});
$db->query('SELECT col FROM table ORDER BY col COLLATE my_sort_rule');


The included patch adds SQLite3::createCollation().  (the diff is against 5.3.9 release code, but patching 5.3-dev or 5.4-dev with it works as of 2012-01-24)


Request #55226 is related -- asking for the same feature in PDO-sqlite3.


Test script:
---------------
<?php

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

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

$db->exec('CREATE TABLE t (s varchar(4))');

$db->exec("INSERT INTO t VALUES ('a1') ");
$db->exec("INSERT INTO t VALUES ('a10')");
$db->exec("INSERT INTO t VALUES ('a2') ");

$defaultSort = $db->query('SELECT s FROM t ORDER BY s');
$naturalSort = $db->query('SELECT s FROM t ORDER BY s COLLATE NAT');

echo "default\n";
while ($row = $defaultSort->fetchArray()){
	echo $row['s'], "\n";
}

echo "natural\n";
while ($row = $naturalSort->fetchArray()){
	echo $row['s'], "\n";
}

$db->close();

?>


Expected result:
----------------
default
a1
a10
a2
natural
a1
a2
a10


Actual result:
--------------
SQLite3::createCollation doesn't exist (yet).


Patches

php_sqlite3_create_collation.patch (last revision 2012-01-29 02:52 UTC by macdewar at gmail dot com)

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2012-01-24 19:14 UTC] macdewar at gmail dot com
Test script has a typo.

replace "COLLATE NAT" with "COLLATE NATCMP"
 [2012-01-28 02:17 UTC] rasmus@php.net
This doesn't compile for me. It applied cleanly but I get:

/home/rasmus/php-src/branches/PHP_5_3/ext/sqlite3/sqlite3.c: In function 
‘php_sqlite3_callback_compare’:
/home/rasmus/php-src/branches/PHP_5_3/ext/sqlite3/sqlite3.c:885:58: warning: 
dereferencing ‘void *’ pointer [enabled by default]
/home/rasmus/php-src/branches/PHP_5_3/ext/sqlite3/sqlite3.c:885:58: error: 
request for member ‘fci’ in something not a structure or union
/home/rasmus/php-src/branches/PHP_5_3/ext/sqlite3/sqlite3.c: In function 
‘zim_sqlite3_createCollation’:
/home/rasmus/php-src/branches/PHP_5_3/ext/sqlite3/sqlite3.c:1053:62: error: 
‘ud_cmp_func’ undeclared (first use in this function)
/home/rasmus/php-src/branches/PHP_5_3/ext/sqlite3/sqlite3.c:1053:62: note: each 
undeclared identifier is reported only once for each function it appears in
/home/rasmus/php-src/branches/PHP_5_3/ext/sqlite3/sqlite3.c:1054:12: error: 
‘php_sqlite3_collation’ has no member named ‘collation_name’
/home/rasmus/php-src/branches/PHP_5_3/ext/sqlite3/sqlite3.c: In function 
‘php_sqlite3_object_free_storage’:
/home/rasmus/php-src/branches/PHP_5_3/ext/sqlite3/sqlite3.c:2055:50: error: 
‘php_sqlite3_collation’ has no member named ‘collation_name’
/home/rasmus/php-src/branches/PHP_5_3/ext/sqlite3/sqlite3.c:2057:27: error: 
‘php_sqlite3_collation’ has no member named ‘collation_name’
/home/rasmus/php-src/branches/PHP_5_3/ext/sqlite3/sqlite3.c:2058:7: error: 
‘cmp_func’ undeclared (first use in this function)
make: *** [ext/sqlite3/sqlite3.lo] Error 1

I haven't looked into it beyond applying the patch and trying to build.
 [2012-01-28 02:17 UTC] rasmus@php.net
-Status: Open +Status: Feedback
 [2012-01-29 02:50 UTC] macdewar at gmail dot com
Well, that's embarrassing.

There's a new patch with the required fixes, and it has been _VERY_ carefully checked.

(I was doing some last-minute refactoring before submitting; must have sent a diff from midway through the change, rather than the final version. ...)
 [2012-01-29 03:03 UTC] macdewar at gmail dot com
-Status: Feedback +Status: Open
 [2012-01-29 03:03 UTC] macdewar at gmail dot com
Added new patch, set status back to open.
 [2012-01-29 03:57 UTC] rasmus@php.net
Automatic comment from SVN on behalf of rasmus
Revision: http://svn.php.net/viewvc/?view=revision&amp;revision=322913
Log: Good patch from Brad Dewar that adds missing createCollation()
method. Fixes bug #60871 and is related to bug #55226
 [2012-01-29 04:01 UTC] rasmus@php.net
Patch applied, thanks.
 [2012-01-29 04:01 UTC] rasmus@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: rasmus
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Mar 29 06:01:29 2024 UTC