如何为Postgresql数据库全文搜索(full text search)编写解析器(parser)

Postgre SQL 前端之家
前端之家收集整理的这篇文章主要介绍了如何为Postgresql数据库全文搜索(full text search)编写解析器(parser)，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

用英文写的，主要是命令和代码，就不翻译了，偷懒一下。

This article show you how to create a parser to handle camel case [3] in string in Postgresql full text search. This parser is tested on Postgresql 8.3.9 version. The OS is ubuntu 8.10.

[1,2] will give you basic background and sample for old version. I update the code to fit 8.3.9 version,especial lots of changes in the sql statement.

Create parser

First,create a directory for put all files,for example: /home/user/Desktop/test_parser

Create file "test_parser.c". I use vim.

#include "postgres.h"



#include "utils/builtins.h"










#ifdef PG_MODULE_MAGIC



PG_MODULE_MAGIC;



#endif







/*



* types



*/







/* self-defined type */



typedef struct {



char * buffer; /* text to parse */



int len; /* length of the text in buffer */



int pos; /* position of the parser */



} ParserState;







/* copy-paste from wparser.h of tsearch2 */



typedef struct {



int lexid;



char *alias;



char *descr;



} LexDescr;







/*



* prototypes



*/



PG_FUNCTION_INFO_V1(testprs_start);



Datum testprs_start(PG_FUNCTION_ARGS);







PG_FUNCTION_INFO_V1(testprs_getlexeme);



Datum testprs_getlexeme(PG_FUNCTION_ARGS);







PG_FUNCTION_INFO_V1(testprs_end);



Datum testprs_end(PG_FUNCTION_ARGS);







PG_FUNCTION_INFO_V1(testprs_lextype);



Datum testprs_lextype(PG_FUNCTION_ARGS);







/*



* functions



*/



Datum testprs_start


(PG_FUNCTION_ARGS)



{



ParserState *pst = (ParserState *) palloc(sizeof(ParserState));



pst->buffer = (char *) PG_GETARG_POINTER(0);



pst->len = PG_GETARG_INT32(1);



pst->pos = 0;



PG_RETURN_POINTER(pst);



}







Datum testprs_getlexeme


(PG_FUNCTION_ARGS)



{



ParserState *pst = (ParserState *) PG_GETARG_POINTER(0);



char **t = (char **) PG_GETARG_POINTER(1);



int *tlen = (int *) PG_GETARG_POINTER(2);



int type;







*tlen = pst->pos;



*t = pst->buffer + pst->pos;







HTML clipboard/* main process here */


 if (((pst->buffer)[pst->pos] >= 'A' && (pst->buffer)[pst->pos] <= 'Z') && 
(pst->pos < pst->len)) {


/* word type */


type = 3;


(pst->pos)++;


/* for case like: ItemURL => Item URL*/


if(((pst->buffer)[pst->pos] >= 'A' && (pst->buffer)[pst->pos] <= 'Z') && (pst->pos 
< pst->len)){


while (((pst->buffer)[pst->pos] >= 'A' && (pst->buffer)[pst->pos] <= 'Z') 
&& (pst->pos < pst->len)) {


(pst->pos)++;


}


}





/* go to the next upper case character */


while (((pst->buffer)[pst->pos] >= 'a' && (pst->buffer)[pst->pos] <= 'z') 
&& (pst->pos < pst->len)) {


(pst->pos)++;


}


} 





if (((pst->buffer)[pst->pos] >= 'a' && (pst->buffer)[pst->pos] <= 'z') && 
(pst->pos < pst->len)) {


/* word type */


type = 3;


(pst->pos)++;


/* go to the next upper case character */


while (((pst->buffer)[pst->pos] >= 'a' && (pst->buffer)[pst->pos] <= 'z') 
&& (pst->pos < pst->len)) {


(pst->pos)++;


}


}







*tlen = pst->pos - *tlen;







/* we are finished if (*tlen == 0) */



if (*tlen == 0) type=0;



PG_RETURN_INT32(type);



}







Datum testprs_end


(PG_FUNCTION_ARGS)



{



ParserState *pst = (ParserState *) PG_GETARG_POINTER(0);



pfree(pst);



PG_RETURN_VOID();



}







Datum testprs_lextype


(PG_FUNCTION_ARGS)



{



/*



Remarks:



- we have to return the blanks for headline reason



- we use the same lexids like Teodor in the default



word parser; in this way we can reuse the headline



function of the default word parser.



*/



LexDescr *descr = (LexDescr *) palloc(sizeof(LexDescr) * (2+1));







/* there are only two types in this parser */



descr[0].lexid = 3;



descr[0].alias = pstrdup("word");



descr[0].descr = pstrdup("Word");



descr[1].lexid = 12;



descr[1].alias = pstrdup("blank");



descr[1].descr = pstrdup("Space symbols");



descr[2].lexid = 0;



PG_RETURN_POINTER(descr);



}

Create "Makefile" file under the same directory.

override CPPFLAGS := -I. $(CPPFLAGS)
MODULE_big = test_parser
OBJS = test_parser.o

DATA_built = test_parser.sql
DATA =
REGRESS = test_parser

ifdef USE_PGXS
PGXS := $(shell pg_config --pgxs)
include $(PGXS)
else
subdir = contrib/test_parser
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif

Create "test_parser.sql" file under the same directory.

SET search_path = public;
BEGIN;

--DROP TEXT SEARCH CONFIGURATION testcfg;
--DROP TEXT SEARCH PARSER testparser;

CREATE OR REPLACE FUNCTION testprs_start(internal,int4)
RETURNS internal
AS '$libdir/test_parser'
LANGUAGE 'C';

CREATE OR REPLACE FUNCTION testprs_getlexeme(internal,internal,internal)
RETURNS internal
AS '$libdir/test_parser'
LANGUAGE 'C';

CREATE OR REPLACE FUNCTION testprs_end(internal)
RETURNS void
AS '$libdir/test_parser'
LANGUAGE 'C';

CREATE OR REPLACE FUNCTION testprs_lextype(internal)
RETURNS internal
AS '$libdir/test_parser'
LANGUAGE 'C';

CREATE TEXT SEARCH PARSER testparser(
START = 'testprs_start',
GETTOKEN = 'testprs_getlexeme',
END = 'testprs_end',
LEXTYPES = 'testprs_lextype'
);

CREATE TEXT SEARCH CONFIGURATION testcfg (
PARSER ='testparser'
);

ALTER TEXT SEARCH CONFIGURATION testcfg ADD MAPPING FOR word WITH english_stem;

END;

Note: the bold part is total changed for the 8.3.9 version. "DROP TEXT SEARCH " is very useful if you make modification and load the script again.

Install the parser:

  • open a terminal,switch to the directory you just create. For example: cd /home/user/Desktop/test_parser
  • type: sudo PATH=/usr/local/pgsql/bin:$PATH USE_PGXS=1 make install
  • switch to user "prostgres",type: /usr/local/pgsql/bin/psql -f /usr/local/pgsql/share/contrib/test_parser.sql your-database
    SET
    BEGIN
    CREATE FUNCTION
    CREATE FUNCTION
    CREATE FUNCTION
    CREATE FUNCTION
    DROP TEXT SEARCH CONFIGURATION
    DROP TEXT SEARCH PARSER
    CREATE TEXT SEARCH PARSER
    CREATE TEXT SEARCH CONFIGURATION
    ALTER TEXT SEARCH CONFIGURATION
    COMMIT
  • type: /usr/local/pgsql/bin/psql postgres
  • test the parser:
    postgres=# SELECT to_tsvector('testcfg','itemValue');
    to_tsvector
    --------------------
    'item':1 'value':2
    (1 row)

    postgres=# SELECT to_tsvector('testcfg','ItemValue');
    to_tsvector
    --------------------
    'item':1 'value':2
    (1 row)

    postgres=# SELECT to_tsquery('testcfg','itemsValue');
    to_tsquery
    -------------------
    'items' & 'value'
    (1 row)

Reference:
[1] http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/HOWTO-parser-tsearch2.html
[2] http://www.pgcon.org/2007/schedule/attachments/12-fts.pdf
[3] http://en.wikipedia.org/wiki/CamelCase
[4] http://www.sai.msu.su/~megera/postgres/fts/doc/sql-fts-createmap.html
[5] http://www.postgresql.org/docs/8.3/static/textsearch-configuration.html
[6] http://developer.postgresql.org/pgdocs/postgres/sql-createtsconfig.html
[7] http://www.postgresql.org/docs/7.4/interactive/sql-createfunction.html

猜你在找的Postgre SQL相关文章

PosegreSQL基础回顾(第 4 章 SQL语法)
来源：http://www.postgres.cn/docs/11/ 4.1.1.&#160;标识符和关键词 SQL标识符和关键...
PosegreSQL基础回顾(第 8 章 数据类型)
来源：http://www.postgres.cn/docs/11/ 8.1.&#160;数字类型 数字类型由2、4或8字节的...
PosegreSQL基础回顾(第 5 章 数据定义)
来源：http://www.postgres.cn/docs/11/ 5.1.&#160;表基础 SQL并不保证表中行的顺序。...
PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
来源：http://www.postgres.cn/docs/11/ 6.4.&#160;从修改的行中返回数据 有时在修改行...
PosegreSQL基础回顾(第 13 章 并发控制)
来源：http://www.postgres.cn/docs/11/ 13.2.1.&#160;读已提交隔离级别 读已提交是Po...
PostgreSQL安装和使用
青岛OJ系统用的关系型数据库是PostgreSQL,为此对PostgreSQL大致了解下。 今天的主要话题围...
PostgreSQL整体架构
 
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
专访唐成：从小工到专家，PostgreSQL的修炼之道
唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问