作者: digoal

日期: 2016-12-06

标签: PostgreSQL , Hunspell , 分词 , 复数还原 , 字典

背景

在英语中，名词通常都有复数，表示多个；形容词，过去式，动词等。 有large, larger, largest, stories, eating, did, doing, hacked这样的。

这可能会给分词带来一定的困扰，例如我们来看看PG默认的ts config怎么处理它的。

比如english tsconfig是这么处理的

postgres=# SELECT * FROM ts_debug('english', 'larger');
   alias   |   description   | token  |  dictionaries  |  dictionary  | lexemes  
-----------+-----------------+--------+----------------+--------------+----------
 asciiword | Word, all ASCII | larger | {english_stem} | english_stem | {larger}
(1 row)

postgres=# SELECT * FROM ts_debug('english', 'large');
   alias   |   description   | token |  dictionaries  |  dictionary  | lexemes 
-----------+-----------------+-------+----------------+--------------+---------
 asciiword | Word, all ASCII | large | {english_stem} | english_stem | {larg}
(1 row)

postgres=# SELECT * FROM ts_debug('english', 'largest');
   alias   |   description   |  token  |  dictionaries  |  dictionary  |  lexemes  
-----------+-----------------+---------+----------------+--------------+-----------
 asciiword | Word, all ASCII | largest | {english_stem} | english_stem | {largest}
(1 row)

postgres=# SELECT * FROM ts_debug('english', 'stories');
   alias   |   description   |  token  |  dictionaries  |  dictionary  | lexemes 
-----------+-----------------+---------+----------------+--------------+---------
 asciiword | Word, all ASCII | stories | {english_stem} | english_stem | {stori}
(1 row)

很显然，它没有很好的处理这几个词, large, larger, largest, stories。

默认的parser支持的token类型

postgres=# select * from ts_token_type('default');
 tokid |      alias      |               description                
-------+-----------------+------------------------------------------
     1 | asciiword       | Word, all ASCII
     2 | word            | Word, all letters
     3 | numword         | Word, letters and digits
     4 | email           | Email address
     5 | url             | URL
     6 | host            | Host
     7 | sfloat          | Scientific notation
     8 | version         | Version number
     9 | hword_numpart   | Hyphenated word part, letters and digits
    10 | hword_part      | Hyphenated word part, all letters
    11 | hword_asciipart | Hyphenated word part, all ASCII
    12 | blank           | Space symbols
    13 | tag             | XML tag
    14 | protocol        | Protocol head
    15 | numhword        | Hyphenated word, letters and digits
    16 | asciihword      | Hyphenated word, all ASCII
    17 | hword           | Hyphenated word, all letters
    18 | url_path        | URL path
    19 | file            | File or path name
    20 | float           | Decimal notation
    21 | int             | Signed integer
    22 | uint            | Unsigned integer
    23 | entity          | XML entity
(23 rows)

实际上从PostgreSQL 9.6开始，就支持了拼写的纠正字典，参考

https://www.postgresql.org/docs/9.6/static/textsearch-dictionaries.html#TEXTSEARCH-ISPELL-DICTIONARY

通过affix, dict文件进行纠正。

例子

The .affix file of Ispell has the following structure:

prefixes
flag *A:
    .           >   RE      # As in enter > reenter
suffixes
flag T:
    E           >   ST      # As in late > latest
    [^AEIOU]Y   >   -Y,IEST # As in dirty > dirtiest
    [AEIOU]Y    >   EST     # As in gray > grayest
    [^EY]       >   EST     # As in small > smallest
And the .dict file has the following structure:

lapse/ADGRS
lard/DGRS
large/PRTY
lark/MRS

postgrespro开源了一个插件，实现了一些国家语言的fix , 可以用来处理这类拼写纠正。

Hunspell Dictionaries , https://github.com/postgrespro/hunspell_dicts

git clone https://github.com/postgrespro/hunspell_dicts
cd hunspell_dicts
ll
total 28K
drwxr-xr-x 5 digoal users 4.0K Dec  6 19:53 hunspell_de_de
drwxr-xr-x 5 digoal users 4.0K Dec  6 19:53 hunspell_en_us
drwxr-xr-x 5 digoal users 4.0K Dec  6 19:53 hunspell_fr
drwxr-xr-x 5 digoal users 4.0K Dec  6 19:53 hunspell_nl_nl
drwxr-xr-x 5 digoal users 4.0K Dec  6 19:53 hunspell_nn_no
drwxr-xr-x 5 digoal users 4.0K Dec  6 19:53 hunspell_ru_ru
-rw-r--r-- 1 digoal users 1.3K Dec  6 19:53 README.md

cd hunspell_en_us
ll
total 560K
-rw-r--r-- 1 dege.zzz users 3.1K Dec  6 19:53 en_us.affix -- 纠正拼写的语法
-rw-r--r-- 1 dege.zzz users 531K Dec  6 19:53 en_us.dict  -- 纠正字典
drwxr-xr-x 2 dege.zzz users 4.0K Dec  6 19:53 expected
-rw-r--r-- 1 dege.zzz users  804 Dec  6 19:53 hunspell_en_us--1.0.sql
-rw-r--r-- 1 dege.zzz users  150 Dec  6 19:53 hunspell_en_us.control
drwxr-xr-x 2 dege.zzz users 4.0K Dec  6 19:53 license
-rw-r--r-- 1 dege.zzz users  370 Dec  6 19:53 Makefile
drwxr-xr-x 2 dege.zzz users 4.0K Dec  6 19:53 sql

make USE_PGXS=1 install

目前支持的几个字典如下

Module	Dictionary	Configuration
hunspell_de_de	german_hunspell	german_hunspell
hunspell_en_us	english_hunspell	english_hunspell
hunspell_fr	french_hunspell	french_hunspell
hunspell_nl_nl	dutch_hunspell	dutch_hunspell
hunspell_nn_no	norwegian_hunspell	norwegian_hunspell
hunspell_ru_ru	russian_hunspell	russian_hunspell

通过模块安装这些字典

psql

CREATE EXTENSION hunspell_en_us;

postgres=# select * from pg_ts_config;
     cfgname      | cfgnamespace | cfgowner | cfgparser 
------------------+--------------+----------+-----------
 simple           |           11 |       10 |      3722
 danish           |           11 |       10 |      3722
 dutch            |           11 |       10 |      3722
 english          |           11 |       10 |      3722
 finnish          |           11 |       10 |      3722
 french           |           11 |       10 |      3722
 german           |           11 |       10 |      3722
 hungarian        |           11 |       10 |      3722
 italian          |           11 |       10 |      3722
 norwegian        |           11 |       10 |      3722
 portuguese       |           11 |       10 |      3722
 romanian         |           11 |       10 |      3722
 russian          |           11 |       10 |      3722
 spanish          |           11 |       10 |      3722
 swedish          |           11 |       10 |      3722
 turkish          |           11 |       10 |      3722
 english_hunspell |         2200 |       10 |      3722  -- 新增
(17 rows)

解决复数，形容词问题

postgres=# SELECT * FROM ts_debug('english_hunspell', 'stories');
   alias   |   description   |  token  |          dictionaries           |    dictionary    | lexemes 
-----------+-----------------+---------+---------------------------------+------------------+---------
 asciiword | Word, all ASCII | stories | {english_hunspell,english_stem} | english_hunspell | {story}
(1 row)

postgres=# SELECT * FROM ts_debug('english_hunspell', 'large');
   alias   |   description   | token |          dictionaries           |    dictionary    | lexemes 
-----------+-----------------+-------+---------------------------------+------------------+---------
 asciiword | Word, all ASCII | large | {english_hunspell,english_stem} | english_hunspell | {large}
(1 row)

postgres=# SELECT * FROM ts_debug('english_hunspell', 'larger');
   alias   |   description   | token  |          dictionaries           |    dictionary    | lexemes 
-----------+-----------------+--------+---------------------------------+------------------+---------
 asciiword | Word, all ASCII | larger | {english_hunspell,english_stem} | english_hunspell | {large}
(1 row)

postgres=# SELECT * FROM ts_debug('english_hunspell', 'largest');
   alias   |   description   |  token  |          dictionaries           |    dictionary    | lexemes 
-----------+-----------------+---------+---------------------------------+------------------+---------
 asciiword | Word, all ASCII | largest | {english_hunspell,english_stem} | english_hunspell | {large}
(1 row)

一个小的插件，反映的是PostgreSQL社区生态，以及PG社区圈子热衷贡献的精神。还有很多很多这样的例子，在程序实现要花不少时间的问题，可能在PG圈就能找到插件帮你解决。快来用PG吧。