[PHP-BUG] Req #60871 [NEW]: Support creating collations in SQLite3 class

2012-01-24 Thread macdewar at gmail dot com 
From: 
Operating system: GNU/Linux
PHP version:  5.3.9
Package:  SQLite related
Bug Type: Feature/Change Request
Bug description:Support creating collations in SQLite3 class

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:
---
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 bug report at https://bugs.php.net/bug.php?id=60871&edit=1
-- 
Try a snapshot (PHP 5.4):
https://bugs.php.net/fix.php?id=60871&r=trysnapshot54
Try a snapshot (PHP 5.3):
https://bugs.php.net/fix.php?id=60871&r=trysnapshot53
Try a snapshot (trunk):  
https://bugs.php.net/fix.php?id=60871&r=trysnapshottrunk
Fixed in SVN:
https://bugs.php.net/fix.php?id=60871&r=fixed
Fixed in SVN and need be documented: 
https://bugs.php.net/fix.php?id=60871&r=needdocs
Fixed in release:
https://bugs.php.net/fix.php?id=60871&r=alreadyfixed
Need backtrace:  
https://bugs.php.net/fix.php?id=60871&r=needtrace
Need Reproduce Script:   
https://bugs.php.net/fix.php?id=60871&r=needscript
Try newer version:   
https://bugs.php.net/fix.php?id=60871&r=oldversion
Not developer issue: 
https://bugs.php.net/fix.php?id=60871&r=support
Expected behavior:   
https://bugs.php.net/fix.php?id=60871&r=notwrong
Not enough info: 
https://bugs.php.net/fix.php?id=60871&r=notenoughinfo
Submitted twice: 
https://bugs.php.net/fix.php?id=60871&r=submittedtwice
register_globals:
https://bugs.php.net/fix.php?id=60871&r=globals
PHP 4 support discontinued:  
https://bugs.php.net/fix.php?id=60871&r=php4
Daylight Savings:https://bugs.php.net/fix.php?id=60871&r=dst
IIS Stability:   
https://bugs.php.net/fix.php?id=60871&r=isapi
Install GNU Sed: 
https://bugs.php.net/fix.php?id=60871&r=gnused
Floating point limitations:  
https://bugs.php.net/fix.php?id=60871&r=float
No Zend Extensions:  
https://bugs.php.net/fix.php?id=60871&r=nozend
MySQL Configuration Error:   
https://bugs.php.net/fix.php?id=60871&r=mysqlcfg

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

2012-01-24 Thread macdewar at gmail dot com 
Edit report at https://bugs.php.net/bug.php?id=60871&edit=1

 ID: 60871
 User updated by:macdewar at gmail dot com
 Reported by:macdewar at gmail dot com
 Summary:Support creating collations in SQLite3 class
 Status: Open
 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:

Test script has a typo.

replace "COLLATE NAT" with "COLLATE NATCMP"


Previous Comments:
----
[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:
---
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

Req #55226 [Com]: Support creating collations in SQLite

2012-01-24 Thread macdewar at gmail dot com 
Edit report at https://bugs.php.net/bug.php?id=55226&edit=1

 ID: 55226
 Comment by: macdewar at gmail dot com
 Reported by:damien at commerceguys dot com
 Summary:Support creating collations in SQLite
 Status: Open
 Type:   Feature/Change Request
 Package:SQLite related
 PHP Version:5.4SVN-2011-07-18 (SVN)
 Block user comment: N
 Private report: N

 New Comment:

Request #60871 is related.  It adds the same functionality to the SQLite3 class 
(not PDO).


Previous Comments:

[2011-07-18 06:14:22] damien at commerceguys dot com

Description:

As with many other things, SQLite have very limited support for collations and 
relies on the host application to implement anything non trivial [1]. For 
example, an Unicode collations (like accent insensitive collations, for 
example) will have to be implemented in the application.

I would like to add a PDO_sqlite::sqliteCreateCollation() using the same 
technique as PDO_sqlite::sqliteCreateFunction() and 
PDO_sqlite::sqliteCreateAggregate(): allow the application to register a PHP 
callback as a new collation.

[1] http://www.sqlite.org/faq.html#q18

Test script:
---
$db = new pdo('sqlite::memory:');

$db->sqliteCreateCollation('MYCOLLATE', function($a, $b) { return strnatcmp($a, 
$b); });

// This will return results in the natural order ("P1", "P2", "P10").
$result = $db->query('SELECT name FROM foobar ORDER BY name COLLATE MYCOLLATE');








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

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

2012-01-28 Thread 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:
---
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

Req #60871 [Fbk->Opn]: Support creating collations in SQLite3 class

2012-01-28 Thread macdewar at gmail dot com 
Edit report at https://bugs.php.net/bug.php?id=60871&edit=1

 ID: 60871
 User updated by:macdewar at gmail dot com
 Reported by:macdewar at gmail dot com
 Summary:Support creating collations in SQLite3 class
-Status: Feedback
+Status: Open
 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:

Added new patch, set status back to open.


Previous Comments:

[2012-01-29 02:50:41] 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-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:
---
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