PGroonga 1.0.6 has been released

BloGroonga

2016-04-15

PGroonga 1.0.6 has been released

PGroonga (píːzí:lúnɡά) 1.0.6 has been released!

See PGroonga 1.0.0 release announce about PGroonga.

Changes

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.

Prefix search and prefix RK search

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)

How to upgrade

This version is compatible with 1.0.3, 1.0.4 and 1.0.5. Upgrade by steps in "Compatible case" in Upgrade document.

Conclusion

New PGroonga version has been released.

Try PGroonga when you want to perform fast full text search against all languages on PostgreSQL!

BloGroonga