Bug #34828 for DBD-SQLite: Please add support for user-defined collations

Thu Apr 10 08:36:23 2008 Maik.Zumstrull [...] gmx.de - Ticket created

Subject:

Please add support for user-defined collations

DBD::SQLite currently exports SQLite's interfaces for creating SQL functions and SQL aggregate functions. SQLite also has an interface for defining collations, that is, comparison functions used for sorting. I believe it would be very helpful to export this interface, given that SQLite's internal string sorting is severely limited (it's basically just memcmp, which produces correct results mostly by accident, if at all). If such an interface is added, I further suggest to automatically create a collation called "perl" on every connect that simply uses perl's cmp operator. That way, one could write "ORDER BY foo COLLATE perl" in an SQL statement and transparently and reliably have the strings sorted the same way calling "sort" in perl would have. A workaround currently exists, in that you can simply pull the data from the database unsorted and then call sort from perl. Obviously, that is a less than elegant solution.

Thu Jun 19 16:06:51 2008 DAMI [...] cpan.org - Correspondence added

Le Jeu. Avr. 10 08:36:23 2008, SP8472 a écrit : Show quoted text

> DBD::SQLite currently exports SQLite's interfaces for creating SQL > functions and SQL aggregate functions. > > SQLite also has an interface for defining collations, that is, > comparison functions used for sorting. > > I believe it would be very helpful to export this interface, given

that Show quoted text

> SQLite's internal string sorting is severely limited (it's basically > just memcmp, which produces correct results mostly by accident, if at

all). Show quoted text

> > If such an interface is added, I further suggest to automatically

create Show quoted text

> a collation called "perl" on every connect that simply uses perl's cmp > operator. That way, one could write "ORDER BY foo COLLATE perl" in an > SQL statement and transparently and reliably have the strings sorted

the Show quoted text

> same way calling "sort" in perl would have. >

I needed the same thing, so I rolled up my sleeves and did it, and sent the patch to Matt Sergeant, but so far I have no news from him. So for anybody who has an urgent need for custom-made collations, here is the patch. As suggested by the initial request, the "perl" collation name is builtin. I also added a "perllocale" collation name (taking into account the current locale). Finally, there is also a hook to the "progress_handler" facility of SQLite. I hope this will end up some day in the next official distrib. Laurent Dami

Download sqlite.patch.gz
application/x-gzip 3.9k

Message body not shown because it is not plain text.

Thu Jun 19 16:07:00 2008 The RT System itself - Status changed from 'new' to 'open'

Thu Jun 19 16:38:31 2008 Maik.Zumstrull [...] gmx.de - Correspondence added

From:

Maik.Zumstrull [...] gmx.de

On Do. 19. Jun. 2008, 16:06:51, DAMI wrote: Show quoted text

> > SQLite also has an interface for defining collations, that is, > > comparison functions used for sorting. > > > > I believe it would be very helpful to export this interface, given > > that SQLite's internal string sorting is severely limited

Show quoted text

> I needed the same thing, so I rolled up my sleeves and did it, and > sent the patch to Matt Sergeant, but so far I have no news from him.

Yeah, I got your first eMail regarding the matter, but didn't reply since I had nothing relevant to say. Thanks, though. Show quoted text

> As suggested by the initial request, the "perl" collation name is > builtin. I also added a "perllocale" collation name (taking into > account the current locale).

Request for Clarification: If the calling perl context is running with "use locale;", will "COLLATE perl" use the locale or not? If it will, it might be helpful to also have "perlnolocale", to be able to force a specific behavior either way. On the other hand, one could also do away with perllocale, just have perl and depend on the locale status of the context entirely. Just my 2c, thanks again for your work.

Fri Jun 20 14:09:12 2008 maik [...] zumstrull.net - Correspondence added

Subject:	Re: [rt.cpan.org #34828] Please add support for user-defined collations
Date:	Thu, 19 Jun 2008 22:15:41 +0200
To:	bug-DBD-SQLite [...] rt.cpan.org
From:	Maik Zumstrull <maik [...] zumstrull.net>

Laurent Dami wrote: Show quoted text

> > SQLite also has an interface for defining collations, that is, > > comparison functions used for sorting. > > > > I believe it would be very helpful to export this interface, given > > that SQLite's internal string sorting is severely limited

Show quoted text

> I needed the same thing, so I rolled up my sleeves and did it, and > sent the patch to Matt Sergeant, but so far I have no news from him.

Yeah, I got your first eMail regarding the matter, but didn't reply since I had nothing relevant to say. Thanks, though. Show quoted text

> As suggested by the initial request, the "perl" collation name is > builtin. I also added a "perllocale" collation name (taking into > account the current locale).

Request for Clarification: If the calling perl context is running with "use locale;", will "COLLATE perl" use the locale or not? If it will, it might be helpful to also have "perlnolocale", to be able to force a specific behavior either way. On the other hand, one could also do away with perllocale, just have perl and depend on the locale status of the context entirely. Just my 2c, thanks again for your work.

Sun Apr 05 15:06:23 2009 adamk [...] cpan.org - Correspondence added

Confirming this has been implemented. The collations "perl" and "perllocale" are created at connection-time. I've also added documentation that describes how to add your own.

Sun Apr 05 15:06:26 2009 adamk [...] cpan.org - Status changed from 'open' to 'resolved'