[groonga-talk:318] [ANN] PGroogna 1.0.6

[Kouhei S. <ko...@cl...>]

Hi,

PGroonga 1.0.6 has been released!

  http://groonga.org/en/blog/2016/04/15/pgroonga-1.0.6.html

PGroonga is a PostgreSQL extension that makes PostgreSQL
fast full text search platform for all languages!


Here are changes since 1.0.3:

  * [Windows] Added version information to DLL.

  * [pgroonga.text_full_text_search_ops_v2 operator class]
    Added &~? operator that does similar search.

  * [pgroonga.text_term_search_ops_v2 operator class] Added
    &^ operator that does prefix search.

  * [pgroonga.text_term_search_ops_v2 operator class] Added
    &^~ operator that does prefix RK search.

  * [Windows] Upgraded base PostgreSQL to 9.5.2.

  * [Windows] Upgraded bundled Groonga to 6.0.1.

  * [Windows] Changed Visual Studio version for building
    PGroonga to 2013. Because PostgreSQL binary is built by
    2013.



This release adds pgroonga.text_term_search_ops_v2 operator
class. You can use prefix search and prefix RK search with
this operator class. They are useful to implement input
completion on search text box.

The following description shows how to use prefix search and
prefix RK search. The following description uses an example
that implements tag name input completion.

First, you need to insert tag names and tag readings. Tag
readings should be in Katakana.

----
CREATE TABLE tags (
  name text PRIMARY KEY
);

CREATE TABLE tag_readings (
  tag_name text
    REFERENCES tags ON DELETE CASCADE ON UPDATE CASCADE,
  katakana text,
  PRIMARY KEY (tag_name, katakana)
);

INSERT INTO tags VALUES ('PostgreSQL');
INSERT INTO tags VALUES ('Groonga');
INSERT INTO tags VALUES ('PGroonga');
INSERT INTO tags VALUES ('pglogical');

INSERT INTO tag_readings VALUES ('PostgreSQL', 'ポストグレスキューエル');
INSERT INTO tag_readings VALUES ('PostgreSQL', 'ポスグレ');
INSERT INTO tag_readings VALUES ('Groonga', 'グルンガ');
INSERT INTO tag_readings VALUES ('PGroonga', 'ピージールンガ');
INSERT INTO tag_readings VALUES ('pglogical', 'ピージーロジカル');
----

You need to create indexes against tag names and tag
readings. It's important that
pgroonga.text_term_search_ops_v2 operator class is used for
tags.name and tag_readings.katakana.

----
CREATE INDEX pgrn_tags_index ON tags
  USING pgroonga (name pgroonga.text_term_search_ops_v2);
CREATE INDEX pgrn_tag_readings_index ON tag_readings
  USING pgroonga (katakana pgroonga.text_term_search_ops_v2);
----

Here is a SELECT to use prefix search against tag names such
as PostgreSQL and Groonga:

----
SELECT name
  FROM tags
  WHERE name &^ 'pos';
--     name    
-- ------------
--  PostgreSQL
-- (1 row)
---

Here is a SELECT to search tags by romanization of Japanese:

----
SELECT tag_name, katakana
  FROM tag_readings
  WHERE katakana &^~ 'pos';
--   tag_name  |        katakana        
-- ------------+------------------------
--  PostgreSQL | ポスグレ
--  PostgreSQL | ポストグレスキューエル
-- (2 rows)
----

You can use UNION to get both results:

----
SELECT name
  FROM tags
  WHERE name &^ 'pos'
UNION
SELECT tag_name
  FROM tag_readings
  WHERE katakana &^~ 'pos';
--     name    
-- ------------
--  PostgreSQL
-- (1 row)
----

Here is an example that searches by pi-ji-:

----
SELECT name
  FROM tags
  WHERE name &^ 'pi-ji-'
UNION
SELECT tag_name
  FROM tag_readings
  WHERE katakana &^~ 'pi-ji-';
--    name    
-- -----------
--  PGroonga
--  pglogical
-- (2 rows)
----

If you can implement input completion by PostgreSQL, you can
use PostgreSQL on more situations.

In the next release, you will be able to use the following
SQL to implement the same features:

----
CREATE TABLE tags (
  name text PRIMARY KEY,
  readings text[]
);

CREATE INDEX pgrn_tags_index ON tags
  USING pgroonga (name     pgroonga.text_term_search_ops_v2,
                  readings pgroonga.text_array_term_search_ops_v2);

INSERT INTO tags VALUES ('PostgreSQL', ARRAY['ポストグレスキューエル', 'ポスグレ']);
INSERT INTO tags VALUES ('Groonga',    ARRAY['グルンガ']);
INSERT INTO tags VALUES ('PGroonga',   ARRAY['ピージールンガ']);
INSERT INTO tags VALUES ('pglogical',  ARRAY['ピージーロジカル']);

SELECT name
  FROM tags
  WHERE name &^ 'pi-ji-' OR
        readings &^~ 'pi-ji-';
--    name    
-- -----------
--  PGroonga
--  pglogical
-- (2 rows)
----


Thanks,
--
kou