Re: Proposal - Collation at database level

Zdenek Kotala wrote:
> Radek Strnad napsal(a):
>
> <snip>
>
>>
>> I'm thinking of dividing the problem into two parts - in beginning
>> pg_collation will contain two functions. One will have hard-coded rules
>> for these basic collations (SQL_CHARACTER, GRAPHIC_IRV, LATIN1, ISO8BIT,
>> UCS_BASIC). It will compare each string character bitwise and guarantee
>> that the implementation will meet the SQL standard implemented in
>> PostgreSQL.
>> Second one will allow the user to use installed system locales. The set
>> of these collations will obviously vary between systems. Catalogs will
>> contain encoding and collation for calling the system locale function.
>> This will allow us to use collations such as en_US.utf8, cs_CZ.iso88592
>> etc. if they will be availible.
>>
>> We will also need to change the way how strings are compared. Regarding
>> the set database collation the right function will be used.
>> http://doxygen.postgresql.org/varlena_8c.html#4c7af81f110f9be0bd8eb2bd99525675
>>
>>
>> This design will make possible switch to ICU or any other implementation
>> quite simple and will not cause any major rewriting of what I'm coding
>> right now.
>
>
> Collation function is main point here. How you mentioned one will be
> only wrapper about strcmp and second one about strcoll. (maybe you
> need four - char/wchar) Which function will be used it is defined in
> pg_collation catalog by CREATE COLLATION command. But you need specify
> name of locale for system locales. It means you need attribute for
> storing locale name.
>
You're right. I've extended pg_collation for system locale columns. In
the first stage we actually don't need any other catalogs such as
encoding, etc. and we can build this functionality only on following
pg_collation catalog. Used collation function (system or built-in) will
be decided on existing collation name.

CATALOG(pg_collations, ###)
{
NameData colname; /* collation name */
Oid colschema; /* collation schema */
NameData colcharset; /* character set specification */
Oid colexistingcollation; /* existing collation */
bool colpadattribute; /* pad attribute */
bool colcasesensitive; /* case sensitive */
bool colaccent; /* accent sensitive */
NameData colsyslccollate; /* lc_collate */
NameData colsyslcctype; /* lc_ctype */
regproc colfunc; /* used collation function */
} FormData_pg_collations;

>> FormData_pg_collations;
> It would be good to send list of new and modified SQL commands (like
> CREATE COLLATION) for wide discussion.
>
CREATE COLLATION <collation name> FOR <character set specification> FROM
<existing collation name> [ <pad characteristic> ] [ <case sensitive> ]
[ <accent sensitive> ] [ LC_COLLATE <lc_collate> ] [ LC_CTYPE <lc_ctype> ]

<pad characteristic> := NO PAD | PAD SPACE
<case sensitive> := CASE SENSITIVE | CASE INSENSITIVE
<accent sensitive> := ACCENT SENSITIVE | ACCENT INSENSITIVE

Since you can specify order by in select clause there's no need for
adding ascending and descending type of collation. They will allways be
ascending.

DROP COLLATION <collation name>

CREATE DATABASE ... [ COLLATE <collation name> ] ...

ALTER DATABASE ... [ COLLATE <collation name> ] ...

Any thoughts?

Radek