Req #60871 [Com]: Support creating collations in SQLite3 class

[macdewar at gmail dot com]

Edit report at https://bugs.php.net/bug.php?id=60871&edit=1

 ID:                 60871
 Comment by:         macdewar at gmail dot com
 Reported by:        macdewar at gmail dot com
 Summary:            Support creating collations in SQLite3 class
 Status:             Feedback
 Type:               Feature/Change Request
 Package:            SQLite related
 Operating System:   GNU/Linux
 PHP Version:        5.3.9
 Block user comment: N
 Private report:     N

 New Comment:

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. ...)


Previous Comments:
------------------------------------------------------------------------
[2012-01-28 02:17:16] ras...@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-24 19:14:37] macdewar at gmail dot com

Test script has a typo.

replace "COLLATE NAT" with "COLLATE NATCMP"

------------------------------------------------------------------------
[2012-01-24 19:09:28] 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).



------------------------------------------------------------------------



-- 
Edit this bug report at https://bugs.php.net/bug.php?id=60871&edit=1