企业官网建设流程全解析

PGroonga 官网：https://pgroonga.github.io/

Description：PGroonga (píːzí:lúnɡά) is a PostgreSQL extension to use Groonga as the index.PostgreSQL supports full text search against languages that use only alphabet and digit. It means that PostgreSQL doesn’t support full text search against Japanese, Chinese and so on. You can use super fast full text search feature against all languages by installing PGroonga into your PostgreSQL!

一、安装相关依赖包

yuminstallwgetcurltargzipgcc gcc-c++makezlib zlib-devel msgpack msgpack-devel mecab mecab-devel lz4 lz4-devel

二、下载安装 git

注意：编译要求Git版本为 V2.7.4及以上版本

wgethttps://www.kernel.org/pub/software/scm/git/git-2.7.4.tar.gz --no-check-certificatetar-vzxfgit-2.7.4.tar.gzcdgit-2.7.4/#Configure./configure --with-openssl=/usr/local/openssl#编译安装make&&makeinstall#打开操作系统环境变量配置文件，修改环境变量vi/etc/profile#在底部加上git相关配置exportPATH=$PATH:/usr/local/git-2.7.4#:wq保存，source命令生效source/etc/profile#查看git版本git--version

三、下载编译安装 groonga

Description：Groonga is an open-source fulltext search engine and column store. It lets you write high-performance applications that requires fulltext search.

官网：https://groonga.org/

编译安装：https://groonga.org/docs/install/centos.html#centos-7

PS：下载source进行源码编译，官网上的groonga-release-latest.noarch.rpm直接在本地安装会有问题（可能会存在文件缺失）。

wgethttps://packages.groonga.org/source/groonga/groonga-13.0.9.tar.gz --no-check-certificatetar-xvzfgroonga-13.0.9.tar.gzcdgroonga-13.0.9#Configure./configure#编译Buildmake-j$(grep'^processor'/proc/cpuinfo|wc-l)#installsudomakeinstall#install之后，执行如下命令查看当前系统安装了哪些库？pkg-config --list-all#查看是否能查到groonga，若没有查到groonga头文件和库文件的位置，编译器无法使用，需要设置PKG_CONFIG_PATH环境变量#查找groonga.pc的位置find/-namegroonga.pc#设置PKG_CONFIG_PATH环境变量exportPKG_CONFIG_PATH=/usr/local/lib/pkgconfig/#查看是否可输出groongapkg-config --list-all#正常可输出groonga Groonga - An Embeddable Fulltext Search Engine

四、下载安装 xxHash

Description:xxHash is an Extremely fast Hash algorithm, processing at RAM speed limits. Code is highly portable, and produces hashes identical across all platforms (little / big endian).

Vcpkg用于在Windows、Linux、Mac上管理C和C++库，极大简化了第三方库的安装，它由微软开源，源码地址：https://github.com/Microsoft/vcpkg，最新发布版本为2023.04.15 Release，它的license为MIT。

Building xxHash - Using vcpkg

You can download and install xxHash using the vcpkg dependency manager

gitclone https://github.com/Microsoft/vcpkg.gitcdvcpkg./bootstrap-vcpkg.sh ./vcpkg integrateinstall./vcpkginstallxxhash

五、下载安装 pgroonga

gitclone--recursivehttps://github.com/pgroonga/pgroonga.gitcdpgroongamakemakeinstall

六、登录数据库

psql highgo sysdba

highgo=# select * from pg_available_extensions where name like '%roon%';name|default_version|installed_version|comment-------------------+-----------------+-------------------+-------------------------------------------------------pgroonga|3.1.6|3.1.6|Super fastandalllanguages supportedfulltextsearchindexbasedonGroonga pgroonga_database|3.1.6||PGroongadatabasemanagement module highgo=# create extension pgroonga;错误: 扩展"pgroonga"已经存在 highgo=# \dx已安装扩展列表 名称|版本|架构模式|描述--------------------+-------+--------------------+-----------------------------------------------------------------------------------------------hg_mac|1.0|information_schema|hgdb mandatory access control withoutusingselinux hg_permission|1.0|information_schema|hg permission mysqlface|1.0|public|administrative functionsforPostgreSQL orafce|3.9|public|Functionsandoperators that emulate a subsetoffunctionsandpackagesfromthe Oracle RDBMS passwordcheck|1.0|information_schema|passwordcheck pg_buffercache|1.3|public|examine the shared buffer cache pg_stat_statements|1.7|public|track executionstatisticsofallSQLstatements executed pgroonga|3.1.6|public|Super fastandalllanguages supportedfulltextsearchindexbasedonGroonga plpgsql|1.0|pg_catalog|PL/pgSQL procedurallanguagezhfts|1.1|public|RUMindexaccess method zhparser|2.2|public|a parserforfull-textsearchofChinese(11行记录)

七、使用

1、启用全文搜索作为文本类型的列

CREATETABLEmemos(idinteger,contenttext);CREATEINDEXpgroonga_content_indexONmemosUSINGpgroonga(content);INSERTINTOmemosVALUES(1,'PostgreSQL is a relational database management system.');INSERTINTOmemosVALUES(2,'Groonga is a fast full text search engine that supports all languages.');INSERTINTOmemosVALUES(3,'PGroonga is a PostgreSQL extension that uses Groonga as index.');INSERTINTOmemosVALUES(4,'There is groonga command.');SETenable_seqscan=off;

There are the following operators to perform full text search:

&@

&@~

LIKE

ILIKE

&@~operator You canuse&@~operatortoperformfulltextsearchbyquery syntax suchaskeyword1ORkeyword2: highgo=# SELECT * FROM memos WHERE content &@~ 'PGroonga OR PostgreSQL';id|content----+----------------------------------------------------------------1|PostgreSQLisa relationaldatabasemanagement system.3|PGroongaisa PostgreSQL extension that uses Groongaasindex.(2行记录)&@ operator You canuse&@ operatortoperformfulltextsearchbyone keyword: highgo=# SELECT * FROM memos WHERE content &@ 'engine';id|content----+------------------------------------------------------------------------2|Groongaisa fastfulltextsearchenginethat supportsalllanguages.(1行记录)LIKEoperator PGroonga supportsLIKEoperator.You can perform fastfulltextsearchbyPGroonga without changing existingSQL.columnLIKE'%keyword%'almost equalstocolumn&@'keyword': highgo=# SELECT * FROM memos WHERE content LIKE '%engine%';id|content----+------------------------------------------------------------------------2|Groongaisa fastfulltextsearchenginethat supportsalllanguages.(1行记录)

2、Score（匹配精度排序）

You can usepgroonga.scorefunction to get precision as a number. If a record is more precision against searched query, the record has more higher number.

You need to add primary key column intopgroongaindex to usepgroonga.scorefunction. If you don’t add primary key column intopgroongaindex,pgroonga.scorefunction always returns0.

Here is a sample schema that includes primary key into indexed columns:

CREATETABLEscore_memos(idintegerPRIMARYKEY,contenttext);CREATEINDEXpgroonga_score_memos_content_indexONscore_memosUSINGpgroonga(id,content);INSERTINTOscore_memosVALUES(1,'PostgreSQL is a relational database management system.');INSERTINTOscore_memosVALUES(2,'Groonga is a fast full text search engine that supports all languages.');INSERTINTOscore_memosVALUES(3,'PGroonga is a PostgreSQL extension that uses Groonga as index.');INSERTINTOscore_memosVALUES(4,'There is groonga command.');SETenable_seqscan=off;--执行全文检索并获得分数SELECT*,pgroonga_score(tableoid,ctid)FROMscore_memosWHEREcontent&@'PGroonga'ORcontent&@'PostgreSQL';id|content|pgroonga_score----+----------------------------------------------------------------+----------------1|PostgreSQLisa relationaldatabasemanagement system.|13|PGroongaisa PostgreSQL extension that uses Groongaasindex.|2(2行记录)--可以使用ORDER by子句中的pgroonga_score函数，按精度降序对匹配的记录进行排序：highgo=# SELECT *, pgroonga_score(tableoid, ctid)highgo-# FROM score_memoshighgo-# WHERE content &@ 'PGroonga' OR content &@ 'PostgreSQL'highgo-# ORDER BY pgroonga_score(tableoid, ctid) DESC;id|content|pgroonga_score----+----------------------------------------------------------------+----------------3|PGroongaisa PostgreSQL extension that uses Groongaasindex.|21|PostgreSQLisa relationaldatabasemanagement system.|1(2行记录)

更多用法详见官网：https://pgroonga.github.io/tutorial/

Author： HGDB-QIURU