On 30 Nov 2009, at 6:49pm, Nicolas Williams wrote: > On Mon, Nov 30, 2009 at 06:37:11PM +0000, Simon Slavin wrote: >> > >> The column 'language' >> could perhaps be absolute, or perhaps be used as a default if the >> individual values did not declare a language. On the other hand, it >> might perhaps not be necessary to declare the language for each >> column: it's likely that all columns for any database would want to >> use the same language for collation. > > See my previous message: it would make no sense to have a column with > data-dependent collations. But perhaps I'm missing something. Can you > describe the semantics of data-dependent collations?
Suppose I am preparing a sales system. It can be used by users in many countries, but a user in each country sees only products sold in that country, and they see those products sorted into alphabetic order -- however their own country sorts its alphabet. So in one table I have Product ID Market Product name ---------- ------ ------------ 1 US Box 2 US Bible 3 US Barrel 4 France Boîte 5 France Bible 6 France Baril 7 Germany Kasten 8 Germany Bibel 9 Germany Faß So in the same column I have words in three different languages. If German had a different alphabet to English, depending on which customer I am SELECTing for I will need to use a different collation to sort the results. Of course, I expect this is relatively rare, and can be perhaps be handled using VIEWs or some other trick, but I wanted to demonstrate that it is sometimes needed. >>> Note too that Unicode has codepoints for specifying the language that >>> the subsequent text is written in. > > http://unicode.org/unicode/faq/languagetagging.html#2 > http://www.unicode.org/versions/Unicode5.0.0/ch16.pdf#G17521 > (section 16.9) Many thanks. I did not know this and it saves me from having to invent my own system in something else I am doing. I do understand why it should not normally be used and I will not over-use it. On 30 Nov 2009, at 7:16pm, Nuno Lucas wrote: > Note that some countries have different collations depending on the > objective of the output. For example, dictionary order may be different > from phone-book order and different from other general listing order. Arghhhhhhhh ! Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users