Kouhei Sutou
null+****@clear*****
Wed Nov 30 15:35:58 JST 2016
Kouhei Sutou 2016-11-30 15:35:58 +0900 (Wed, 30 Nov 2016) New Revision: 6f470236517670b0ad7e36e049c7d20ba4333747 https://github.com/pgroonga/pgroonga.github.io/commit/6f470236517670b0ad7e36e049c7d20ba4333747 Message: Describe about pgroonga.command(name, ARRAY[...]) style Modified files: _po/ja/reference/index.po ja/reference/functions/pgroonga-command.md ja/reference/functions/pgroonga-query-extract-keywords.md ja/reference/index.md reference/functions/pgroonga-command.md reference/index.md Modified: _po/ja/reference/index.po (+14 -1) =================================================================== --- _po/ja/reference/index.po 2016-11-30 01:07:49 +0900 (c5f9376) +++ _po/ja/reference/index.po 2016-11-30 15:35:58 +0900 (bc95c6b) @@ -1,7 +1,7 @@ msgid "" msgstr "" "Project-Id-Version: PACKAGE VERSION\n" -"PO-Revision-Date: 2016-11-29 18:22+0900\n" +"PO-Revision-Date: 2016-11-30 15:33+0900\n" "Language: ja\n" "MIME-Version: 1.0\n" "Content-Type: text/plain; charset=UTF-8\n" @@ -164,6 +164,16 @@ msgstr "## 関数" msgid " * [`pgroonga.command` function](functions/pgroonga-command.html)" msgstr " * [`pgroonga.command`関数](functions/pgroonga-command.html)" +msgid "" +" * [`pgroonga.command_escape_value` function](functions/pgroonga-command-esca" +"pe-value.html)" +msgstr "" +" * [`pgroonga.command_escape_value`関数](functions/pgroonga-command-escape-valu" +"e.html)" + +msgid " * [`pgroonga.escape` function](functions/pgroonga-escape.html)" +msgstr " * [`pgroonga.escape`関数](functions/pgroonga-escape.html)" + msgid " * [`pgroonga.flush` function](functions/pgroonga-flush.html)" msgstr " * [`pgroonga.flush`関数](functions/pgroonga-flush.html)" @@ -186,6 +196,9 @@ msgstr "" " * [`pgroonga.match_positions_character`関数](functions/pgroonga-match-position" "s-character.html)" +msgid " * [`pgroonga.query_escape` function](functions/pgroonga-query-escape.html)" +msgstr " * [`pgroonga.query_escape`関数](functions/pgroonga-query-escape.html)" + msgid "" " * [`pgroonga.query_extract_keywords` function](functions/pgroonga-query-extr" "act-keywords.html)" Modified: ja/reference/functions/pgroonga-command.md (+208 -1) =================================================================== --- ja/reference/functions/pgroonga-command.md 2016-11-30 01:07:49 +0900 (eb722ac) +++ ja/reference/functions/pgroonga-command.md 2016-11-30 15:35:58 +0900 (652354d) @@ -14,15 +14,220 @@ title: pgroonga.command関数 ```text text pgroonga.command(command) +text pgroonga.command(name, + ARRAY[argument_name1, argument_value1, + argument_name2, argument_value2, + ...]) ``` +2つめの構文を推奨します。理由は引数の値を自動でエスケープするからです。これによりシンタックスエラーとGroongaコマンドインジェクションを防げます。 + +1つめの構文の説明は以下の通りです。 + `command`は`text`型の値です。`pgroonga.command`は`command`をGroongaのコマンドとして実行します。 +2つめの構文の説明は以下の通りです。 + +`name`は`text`型の値です。実行するコマンド名です。 + +`argument_name`は`text`型の値です。引数名です。この後に対応する引数の値が続きます。 + +`argument_value`は`text`型の値です。直前にした引数名に対応する値です。 + +`pgroonga.command`は`name`と`argument_name`と`argument_value`からGroongaコマンドを作り、そのGroongaコマンドを実行します。 + Groongaのコマンドは結果をJSONとして返します。`pgroonga.command`はJSONを`text`型の値として返します。結果を`json`型か`jsonb`型にキャストすると[PostgreSQLが提供するJSON関数・演算]({{ site.postgresql_doc_base_url.ja }}/functions-json.html)を使うことができます。 ## 使い方 -[チュートリアルの例](../../tutorial/#groonga)も参照してください。 +サンプルスキーマとデータは次の通りです。 + +```sql +CREATE TABLE memos ( + content text +); + +CREATE INDEX pgroonga_memos_index + ON memos + USING pgroonga (content); + +INSERT INTO memos VALUES ('PGroonga (PostgreSQL+Groonga) is great!'); +``` + +以下は[`status` Groongaコマンド](http://groonga.org/ja/docs/reference/commands/status.html)の実行例です。このコマンドには引数はありません。 + +```sql +SELECT jsonb_pretty(pgroonga.command('status')::jsonb); +-- jsonb_pretty +-- ----------------------------------------- +-- [ + +-- [ + +-- 0, + +-- 1480484730.607103, + +-- 0.0001363754272460938 + +-- ], + +-- { + +-- "uptime": 859, + +-- "version": "6.1.0-53-g460b5c9",+ +-- "n_queries": 6, + +-- "starttime": 1480483871, + +-- "start_time": 1480483871, + +-- "alloc_count": 14034, + +-- "cache_hit_rate": 0.0, + +-- "command_version": 1, + +-- "max_command_version": 3, + +-- "default_command_version": 1 + +-- } + +-- ] +-- (1 row) +``` + +以下は挿入したデータを検索する例です。検索には[`select` Groongaコマンド](http://groonga.org/ja/docs/reference/commands/select.html)を使います。[`pgroonga.table_name`関数](pgroonga-table-name.html)を使ってPGroongaのインデックス名をGroongaのテーブル名に変換する必要があります。 + +```sql +SELECT jsonb_pretty( + pgroonga.command( + 'select ' || + '--table ' || pgroonga.table_name('pgroonga_memos_index') + )::jsonb +); +-- jsonb_pretty +-- ------------------------------------------------------------ +-- [ + +-- [ + +-- 0, + +-- 1480484984.533947, + +-- 0.0005786418914794922 + +-- ], + +-- [ + +-- [ + +-- [ + +-- 1 + +-- ], + +-- [ + +-- [ + +-- "_id", + +-- "UInt32" + +-- ], + +-- [ + +-- "content", + +-- "LongText" + +-- ], + +-- [ + +-- "ctid", + +-- "UInt64" + +-- ] + +-- ], + +-- [ + +-- 1, + +-- "PGroonga (PostgreSQL+Groonga) is great!",+ +-- 1 + +-- ] + +-- ] + +-- ] + +-- ] +-- (1 row) +``` + +以下は「PostgreSQL」と「Groonga」を含むレコードを検索する例です。「PostgreSQL Groonga」を1つの引数値として扱うためにダブルクォート（またはシングルクォート）で囲む必要がある点に注意してください。 + +```sql +SELECT jsonb_pretty( + pgroonga.command( + 'select ' || + '--table ' || pgroonga.table_name('pgroonga_memos_index') || ' ' || + '--match_columns content ' || + '--query "PostgreSQL Groonga"' + )::jsonb +); +-- jsonb_pretty +-- ------------------------------------------------------------ +-- [ + +-- [ + +-- 0, + +-- 1480485153.923481, + +-- 0.002448797225952148 + +-- ], + +-- [ + +-- [ + +-- [ + +-- 1 + +-- ], + +-- [ + +-- [ + +-- "_id", + +-- "UInt32" + +-- ], + +-- [ + +-- "content", + +-- "LongText" + +-- ], + +-- [ + +-- "ctid", + +-- "UInt64" + +-- ] + +-- ], + +-- [ + +-- 1, + +-- "PGroonga (PostgreSQL+Groonga) is great!",+ +-- 1 + +-- ] + +-- ] + +-- ] + +-- ] +-- (1 row) +``` + +引数を配列で指定するスタイルと使うと、ダブルクォートで囲むことについて気にする必要がなくなります。 + +```sql +SELECT jsonb_pretty( + pgroonga.command( + 'select', + ARRAY[ + 'table', pgroonga.table_name('pgroonga_memos_index'), + 'match_columns', 'content', + 'query', 'PostgreSQL Groonga' + ] + )::jsonb +); +-- jsonb_pretty +-- ------------------------------------------------------------ +-- [ + +-- [ + +-- 0, + +-- 1480485246.841189, + +-- 0.00008869171142578125 + +-- ], + +-- [ + +-- [ + +-- [ + +-- 1 + +-- ], + +-- [ + +-- [ + +-- "_id", + +-- "UInt32" + +-- ], + +-- [ + +-- "content", + +-- "LongText" + +-- ], + +-- [ + +-- "ctid", + +-- "UInt64" + +-- ] + +-- ], + +-- [ + +-- 1, + +-- "PGroonga (PostgreSQL+Groonga) is great!",+ +-- 1 + +-- ] + +-- ] + +-- ] + +-- ] +-- (1 row) +``` ## Groongaの`select`コマンドに関する注意事項 {#attention} @@ -34,4 +239,6 @@ Groongaのコマンドは結果をJSONとして返します。`pgroonga.command` * [チュートリアルにある例](../../tutorial/#groonga) + * [`pgroonga.table_name`関数](pgroonga-table-name.html) + * [`pgroonga_tuple_is_alive` Groonga関数](../groonga-functions/pgroonga-tuple-is-alive.html) Modified: ja/reference/functions/pgroonga-query-extract-keywords.md (+16 -16) =================================================================== --- ja/reference/functions/pgroonga-query-extract-keywords.md 2016-11-30 01:07:49 +0900 (37fa986) +++ ja/reference/functions/pgroonga-query-extract-keywords.md 2016-11-30 15:35:58 +0900 (26fe9bd) @@ -32,40 +32,40 @@ ANDのみの場合はすべての語がキーワードになります。 ```sql SELECT pgroonga.query_extract_keywords('Groonga PostgreSQL'); - query_extract_keywords ------------------------- - {PostgreSQL,Groonga} -(1 row) +-- query_extract_keywords +-- ------------------------ +-- {PostgreSQL,Groonga} +-- (1 row) ``` ORのみの場合はすべての語がキーワードになります。 ```sql SELECT pgroonga.query_extract_keywords('Groonga OR PostgreSQL'); - query_extract_keywords ------------------------- - {PostgreSQL,Groonga} -(1 row) +-- query_extract_keywords +-- ------------------------ +-- {PostgreSQL,Groonga} +-- (1 row) ``` カッコを使えます。 ```sql SELECT pgroonga.query_extract_keywords('Groonga (MySQL OR PostgreSQL)'); - query_extract_keywords ----------------------------- - {Groonga,PostgreSQL,MySQL} -(1 row) +-- query_extract_keywords +-- ---------------------------- +-- {Groonga,PostgreSQL,MySQL} +-- (1 row) ``` NOT条件の語はキーワードになりません。 ```sql SELECT pgroonga.query_extract_keywords('Groonga - MySQL PostgreSQL'); - query_extract_keywords ------------------------- - {PostgreSQL,Groonga} -(1 row) +-- query_extract_keywords +-- ------------------------ +-- {PostgreSQL,Groonga} +-- (1 row) ``` ## 参考 Modified: ja/reference/index.md (+6 -0) =================================================================== --- ja/reference/index.md 2016-11-30 01:07:49 +0900 (c752c59) +++ ja/reference/index.md 2016-11-30 15:35:58 +0900 (e30bf3e) @@ -82,6 +82,10 @@ PGroonga 1.Y.Zは`pgroonga.XXX_v2`という演算子クラスを提供します * [`pgroonga.command`関数](functions/pgroonga-command.html) + * [`pgroonga.command_escape_value`関数](functions/pgroonga-command-escape-value.html) + + * [`pgroonga.escape`関数](functions/pgroonga-escape.html) + * [`pgroonga.flush`関数](functions/pgroonga-flush.html) * [`pgroonga.highlight_html`関数](functions/pgroonga-highlight-html.html) @@ -90,6 +94,8 @@ PGroonga 1.Y.Zは`pgroonga.XXX_v2`という演算子クラスを提供します * [`pgroonga.match_positions_character`関数](functions/pgroonga-match-positions-character.html) + * [`pgroonga.query_escape`関数](functions/pgroonga-query-escape.html) + * [`pgroonga.query_extract_keywords`関数](functions/pgroonga-query-extract-keywords.html) * [`pgroonga.score`関数](functions/pgroonga-score.html) Modified: reference/functions/pgroonga-command.md (+208 -1) =================================================================== --- reference/functions/pgroonga-command.md 2016-11-30 01:07:49 +0900 (d36f5a3) +++ reference/functions/pgroonga-command.md 2016-11-30 15:35:58 +0900 (d0160a7) @@ -14,15 +14,220 @@ Here is the syntax of this function: ```text text pgroonga.command(command) +text pgroonga.command(name, + ARRAY[argument_name1, argument_value1, + argument_name2, argument_value2, + ...]) ``` +The second syntax is recommended because it escapes argument values automatically. It prevents syntax error and Groonga command injection. + +Here is the description about the first syntax: + `command` is a `text` type value. `pgroonga.command` executes `command` as a Groonga command. +Here is the description about the second syntax: + +`name` is a `text` type value. It's a command name to be executed. + +`argument_name` is a `text` type value. It's an argument name followed by the corresponded argument value. + +`argument_value` is a `text` type value. It's an argument value of the preceding argument name. + +`pgroonga.command` builds a Groonga command from `name` and `argument_name`s and `argument_value`s and executes the built Groonga command. + Groonga command returns result as JSON. `pgroonga.command` returns the JSON as `text` type value. You can use [JSON functions and operations provided by PostgreSQL]({{ site.postgresql_doc_base_url.en }}/functions-json.html) by casting the result to `json` or `jsonb` type. ## Usage -See [examples in tutorial](../../tutorial/#groonga). +Here are sample schema and data: + +```sql +CREATE TABLE memos ( + content text +); + +CREATE INDEX pgroonga_memos_index + ON memos + USING pgroonga (content); + +INSERT INTO memos VALUES ('PGroonga (PostgreSQL+Groonga) is great!'); +``` + +Here is an example to run [`status` Groonga command](http://groonga.org/en/docs/reference/commands/status.html) that doesn't have any arguments: + +```sql +SELECT jsonb_pretty(pgroonga.command('status')::jsonb); +-- jsonb_pretty +-- ----------------------------------------- +-- [ + +-- [ + +-- 0, + +-- 1480484730.607103, + +-- 0.0001363754272460938 + +-- ], + +-- { + +-- "uptime": 859, + +-- "version": "6.1.0-53-g460b5c9",+ +-- "n_queries": 6, + +-- "starttime": 1480483871, + +-- "start_time": 1480483871, + +-- "alloc_count": 14034, + +-- "cache_hit_rate": 0.0, + +-- "command_version": 1, + +-- "max_command_version": 3, + +-- "default_command_version": 1 + +-- } + +-- ] +-- (1 row) +``` + +Here is an example to search inserted data. You can use [`select` Groonga command](http://groonga.org/docs/reference/commands/select.html) for the purpose. You need to convert PGroonga index name to Groonga table name by [`pgroonga.table_name` function](pgroonga-table-name.html). + +```sql +SELECT jsonb_pretty( + pgroonga.command( + 'select ' || + '--table ' || pgroonga.table_name('pgroonga_memos_index') + )::jsonb +); +-- jsonb_pretty +-- ------------------------------------------------------------ +-- [ + +-- [ + +-- 0, + +-- 1480484984.533947, + +-- 0.0005786418914794922 + +-- ], + +-- [ + +-- [ + +-- [ + +-- 1 + +-- ], + +-- [ + +-- [ + +-- "_id", + +-- "UInt32" + +-- ], + +-- [ + +-- "content", + +-- "LongText" + +-- ], + +-- [ + +-- "ctid", + +-- "UInt64" + +-- ] + +-- ], + +-- [ + +-- 1, + +-- "PGroonga (PostgreSQL+Groonga) is great!",+ +-- 1 + +-- ] + +-- ] + +-- ] + +-- ] +-- (1 row) +``` + +Here is an example that searches records that contains "PostgreSQL" and "Groonga". Note that you need to quote "PostgreSQL Groonga" to treat as one argument value: + +```sql +SELECT jsonb_pretty( + pgroonga.command( + 'select ' || + '--table ' || pgroonga.table_name('pgroonga_memos_index') || ' ' || + '--match_columns content ' || + '--query "PostgreSQL Groonga"' + )::jsonb +); +-- jsonb_pretty +-- ------------------------------------------------------------ +-- [ + +-- [ + +-- 0, + +-- 1480485153.923481, + +-- 0.002448797225952148 + +-- ], + +-- [ + +-- [ + +-- [ + +-- 1 + +-- ], + +-- [ + +-- [ + +-- "_id", + +-- "UInt32" + +-- ], + +-- [ + +-- "content", + +-- "LongText" + +-- ], + +-- [ + +-- "ctid", + +-- "UInt64" + +-- ] + +-- ], + +-- [ + +-- 1, + +-- "PGroonga (PostgreSQL+Groonga) is great!",+ +-- 1 + +-- ] + +-- ] + +-- ] + +-- ] +-- (1 row) +``` + +If you use arguments array style, you don't need to care about quoting: + +```sql +SELECT jsonb_pretty( + pgroonga.command( + 'select', + ARRAY[ + 'table', pgroonga.table_name('pgroonga_memos_index'), + 'match_columns', 'content', + 'query', 'PostgreSQL Groonga' + ] + )::jsonb +); +-- jsonb_pretty +-- ------------------------------------------------------------ +-- [ + +-- [ + +-- 0, + +-- 1480485246.841189, + +-- 0.00008869171142578125 + +-- ], + +-- [ + +-- [ + +-- [ + +-- 1 + +-- ], + +-- [ + +-- [ + +-- "_id", + +-- "UInt32" + +-- ], + +-- [ + +-- "content", + +-- "LongText" + +-- ], + +-- [ + +-- "ctid", + +-- "UInt64" + +-- ] + +-- ], + +-- [ + +-- 1, + +-- "PGroonga (PostgreSQL+Groonga) is great!",+ +-- 1 + +-- ] + +-- ] + +-- ] + +-- ] +-- (1 row) +``` ## Attention for `select` Groonga command {#attention} @@ -34,4 +239,6 @@ See [`pgroonga_tuple_is_alive` Groonga function](../groonga-functions/pgroonga-t * [Examples in tutorial](../../tutorial/#groonga) + * [`pgroonga.table_name` function](pgroonga-table-name.html) + * [`pgroonga_tuple_is_alive` Groonga function](../groonga-functions/pgroonga-tuple-is-alive.html) Modified: reference/index.md (+2 -0) =================================================================== --- reference/index.md 2016-11-30 01:07:49 +0900 (0fc3021) +++ reference/index.md 2016-11-30 15:35:58 +0900 (1655193) @@ -82,6 +82,8 @@ If you use them, you need to use [incompatible case steps](../upgrade/#incompati * [`pgroonga.command` function](functions/pgroonga-command.html) + * [`pgroonga.command_escape_value` function](functions/pgroonga-command-escape-value.html) + * [`pgroonga.escape` function](functions/pgroonga-escape.html) * [`pgroonga.flush` function](functions/pgroonga-flush.html) -------------- next part -------------- HTML����������������������������...Download