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

Reply via email to