PostgreSQL10.0preview功能增強-國際化功能增強,支援ICU(InternationalComponentsforUnicode)

標籤

PostgreSQL , 10.0 , International Components for Unicode , ICU , collate , 國際化

---

背景

ICU是一個成熟的，被廣泛使用的跨平臺一致性全球化支援庫。使用沒有任何限制的開源許可，可以被商業、開源軟體隨意使用。

ICU is a mature, widely used set of C/C++ and Java libraries providing Unicode and Globalization support for software applications.   
ICU is widely portable and gives applications the same results on all platforms and between C/C++ and Java software.  
  
ICU is released under a nonrestrictive open source license that is suitable for use with both commercial software and with other open source or free software.  

ICU的好處是與UNICODE標準最為貼近，而且可以使用ICU，軟體可以做到跨平臺保持一致性（只要是在ICU支援的平臺中）。

ICU支援的功能如下，包括unicode和文字的轉換，本土化的排序、時間日期格式支援，時區換算，規則表示式的unicode支援，等等。

Code Page Conversion: Convert text data to or from Unicode and nearly any other character set or encoding. ICU`s conversion tables are based on charset data collected by IBM over the course of many decades, and is the most complete available anywhere.  
  
Collation: Compare strings according to the conventions and standards of a particular language, region or country. ICU`s collation is based on the Unicode Collation Algorithm plus locale-specific comparison rules from the Common Locale Data Repository, a comprehensive source for this type of data.  
  
Formatting: Format numbers, dates, times and currency amounts according the conventions of a chosen locale. This includes translating month and day names into the selected language, choosing appropriate abbreviations, ordering fields correctly, etc. This data also comes from the Common Locale Data Repository.  
  
Time Calculations: Multiple types of calendars are provided beyond the traditional Gregorian calendar. A thorough set of timezone calculation APIs are provided.  
  
Unicode Support: ICU closely tracks the Unicode standard, providing easy access to all of the many Unicode character properties, Unicode Normalization, Case Folding and other fundamental operations as specified by the Unicode Standard.  
  
Regular Expression: ICU`s regular expressions fully support Unicode while providing very competitive performance.  
  
Bidi: support for handling text containing a mixture of left to right (English) and right to left (Arabic or Hebrew) data.  
  
Text Boundaries: Locate the positions of words, sentences, paragraphs within a range of text, or identify locations that would be suitable for line wrapping when displaying the text.  

PostgreSQL 以前的全球化是通過glibc庫來支援，受到glibc版本的影響，在更換平臺時，可能影響排序或者本土化的結果。（例如windows, linux, freebsd等跨平臺使用時）。

10.0開始，支援ICU了，在安裝PG軟體的機器上安裝好ICU庫，同時在configure時開啟–with-icu，就可以使用ICU4C了。

pg_collation新增了一個欄位collprovider表示libc或者icu. 增加一個collversion欄位，記錄當時使用的ICU版本，run time時檢查，確保版本一致。

ICU support  
  
Add a column collprovider to pg_collation that determines which library  
provides the collation data.  The existing choices are default and libc,  
and this adds an icu choice, which uses the ICU4C library.  
  
The pg_locale_t type is changed to a union that contains the  
provider-specific locale handles.  Users of locale information are  
changed to look into that struct for the appropriate handle to use.  
  
Also add a collversion column that records the version of the collation  
when it is created, and check at run time whether it is still the same.  
This detects potentially incompatible library upgrades that can corrupt  
indexes and other structures.  This is currently only supported by  
ICU-provided collations.  
  
initdb initializes the default collation set as before from the   
`locale-a` output but also adds all available ICU locales with a "-x-icu"  
appended.  
  
Currently, ICU-provided collations can only be explicitly named  
collations.  The global database locales are still always libc-provided.  
  
ICU support is enabled by configure --with-icu.  
  
Reviewed-by: Thomas Munro <thomas.munro@enterprisedb.com>  
Reviewed-by: Andreas Karlsson <andreas@proxel.se>  

例子

  11 CREATE TABLE collate_test1 (  
  12     a int,  
  13     b text COLLATE "en-x-icu" NOT NULL  
  14 );  
  15   
  16 d collate_test1  
  17   
  18 CREATE TABLE collate_test_fail (  
  19     a int,  
  20     b text COLLATE "ja_JP.eucjp-x-icu"  
  21 );  
  22   
  23 CREATE TABLE collate_test_fail (  
  24     a int,  
  25     b text COLLATE "foo-x-icu"  
  26 );  
  27   
  28 CREATE TABLE collate_test_fail (  
  29     a int COLLATE "en-x-icu",  
  30     b text  
  31 );  
  32   
  33 CREATE TABLE collate_test_like (  
  34     LIKE collate_test1  
  35 );  
  36   
  
  92 -- constant expression folding  
  93 SELECT `bbc` COLLATE "en-x-icu" > `äbc` COLLATE "en-x-icu" AS "true";  
  94 SELECT `bbc` COLLATE "sv-x-icu" > `äbc` COLLATE "sv-x-icu" AS "false";  
  95   
  96 -- upper/lower  
  97   
  98 CREATE TABLE collate_test10 (  
  99     a int,  
 100     x text COLLATE "en-x-icu",  
 101     y text COLLATE "tr-x-icu"  
 102 );  

這個patch的討論，詳見郵件組，本文末尾URL。

PostgreSQL社群的作風非常嚴謹，一個patch可能在郵件組中討論幾個月甚至幾年，根據大家的意見反覆的修正，patch合併到master已經非常成熟，所以PostgreSQL的穩定性也是遠近聞名的。

參考

https://wiki.postgresql.org/wiki/Todo:ICU

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=eccfef81e1f73ee41f1d8bfe4fa4e80576945048

http://site.icu-project.org/