Supabase で PGroonga がサポートされたので Flutter アプリから使ってみた

Supabase で PGroonga がサポートされたので Flutter アプリから使ってみた 第 39 回 PostgreSQL アンカンファレンス@オンライン 2023/2/20 まつひさ（hmatsu47）

自己紹介 松久裕保（@hmatsu47） ● https://qiita.com/hmatsu47 ● 現在のステータス： ○ 名古屋で Web インフラのお守り係をしています ○ Aurora MySQL v1 → v3 移行完了（開発・お試し用含む） ■ https://zenn.dev/hmatsu47/books/aurora-mysql3-plan-book ■ https://zenn.dev/hmatsu47/books/aurora-mysql-do-book 2

• https://qiita.com/hmatsu47
• https://zenn.dev/hmatsu47/books/aurora-mysql3-plan-book
• https://zenn.dev/hmatsu47/books/aurora-mysql-do-book

今回の発表ネタ ● Supabase で PGroonga がサポートされた https://www.clear-code.com/blog/2023/1/17/supabase-support-pgroonga.html ○ Supabase でマネージドな PGroonga を使える！ ● 第 29 回「Flutter で Supabase の PostgreSQL with PostGIS を試してみた」の Flutter アプリに PGroonga 全文検索を実装した ○ スポット検索機能 3

• https://www.clear-code.com/blog/2023/1/17/supabase-support-pgroonga.html

最近の発表ネタが Supabase に偏っている件 回 タイトル 開催日 15 Alibaba Cloud の PolarDB が東京にやってきたのであらためて試してみた（7 ヶ月ぶり 2 回目） 2020/07/30 17 IPA データベーススペシャリスト試験が秋に実施されると聞いたので PostgreSQL と絡めて紹介してみる 2020/09/24 18 MySQL と PostgreSQL と INDEX（良いタイトルが思い浮かばなかったので考えるのを諦めた） 2020/11/02 19 MySQL と PostgreSQL と主キー（良いタイトルが思い浮かばなかったので考えるのを諦めた・その②） 2020/12/07 21 RDS / Auroraパフォーマンスインサイトを使ってみる（PostgreSQL 編） 2021/03/02 29 Flutter で Supabase の PostgreSQL with PostGIS を試してみた 2021/12/21 33 SolidJS で Supabase の Row Level Security を試してみた 2022/05/31 37 Supabase で TCE（透過的列暗号化）を試してみた 2022/12/20 39 Supabase で PGroonga がサポートされたので Flutter アプリから使ってみた 2023/02/20 4

関連記事 ● https://zenn.dev/hmatsu47/articles/supabase_pgroonga_flutter ○ Flutter で Supabase の PGroonga 全文検索を試してみた ● https://zenn.dev/hmatsu47/articles/supabase_pgroonga_synonyms ○ Supabase の PGroonga 全文検索で同義語検索してみる ● https://zenn.dev/hmatsu47/articles/supabase_pgroonga_stopword_wa ○ Supabase の PGroonga 全文検索でストップワード対応のワーク アラウンドを試してみる 5

• https://zenn.dev/hmatsu47/articles/supabase_pgroonga_flutter
• https://zenn.dev/hmatsu47/articles/supabase_pgroonga_synonyms
• https://zenn.dev/hmatsu47/articles/supabase_pgroonga_stopword_wa

Supabase とは？（おさらい） ● BaaS（Backend as a Service）の一つ ○ Firebase Alternative ● サービスは 4 つ（それぞれの機能は以前よりも増えている） ○ Database ← PostgreSQL が使われている ○ Authentication ○ Storage ○ Edge Functions 6

PGroonga とは？（説明不要かもしれませんが、一応） ● ざっくり ○ PostgreSQL 向け拡張機能（Extension） ○ 全文検索エンジン「Groonga」を使用 ○ 高速な日本語全文検索が可能 ○ トークナイザーには N-gram・MeCab などが使用可能 7

Flutter とは？ ● Google 製の UI フレームワーク ○ 使う言語は Dart ○ 当初はクロスプラットフォームモバイルアプリ開発用 ○ その後、Web や Windows / macOS / Linux も対象に ○ 先月バージョン 3.7 が出た ■ もうすぐ 4.0 が出る模様 8

実装対象の Flutter アプリ（maptool） ● https://github.com/hmatsu47/maptool ● 主な機能 ○ 訪問（予定）地へのピン立て（登録）と写真撮影 ○ 登録ピンと関連づけて写真撮影 ○ 登録ピンの検索 ○ 文化財などの近隣スポット検索←ここで PostGIS を利用 ○ スポット名と説明文の全文検索←New!! 9

• https://github.com/hmatsu47/maptool

実装対象の Flutter アプリ（maptool） ● 訪問（予定）地へのピン立て（登録） 注：画面は少し古めのバージョン です（以降同じ） 10

実装対象の Flutter アプリ（maptool） ● 登録ピンと関連づけて写真撮影 11

実装対象の Flutter アプリ（maptool） ● 文化財などの近隣スポット検索（Supabase / PostGIS） ※第 29 回の発表後、カテゴリ別の ピン表示にも対応 ■ 12

実装対象の Flutter アプリ（maptool） ● スポットの全文検索（Supabase / PostGIS / PGroonga） ■ PGroonga でキーワードを全文検索して ■ PostGIS で距離が近い順に表示 ● 地図の中心が起点 注：サンプルデータとして、以下を改変して利用 ● 愛知県文化財マップ（ナビ愛知）、愛知県、クリエイ ティブ・コモンズ・ライセンス 表示２.１日本 ● https://www.pref.aichi.jp/soshiki/joho/0000069385.html 13

• https://www.pref.aichi.jp/soshiki/joho/0000069385.html

Supabase で全文検索を使うには ● Supabase（Database）側で設定 ○ PGroonga を有効化 ○ 検索対象テーブルに全文検索用インデックスを追加 ○ 全文検索を使うストアドファンクションを実装 ● クライアント側のコードを実装 ○ RPC でストアドファンクションを呼び出す ■ 関数や特殊な表現はクエリビルダーでは使用不可（PostGIS と同様） 14

注意 ● 通常は RLS（行レベルセキュリティ）と認証を実装する ○ 今回は自分一人で利用するアプリであり、アプリストアなどでの 公開もしないので RLS とユーザー認証を実装していない ○ 通常のアプリでは、情報漏洩や API の悪用防止のため RLS と ユーザー認証を実装する 15

PGroonga を有効化 ● Database - Extensions で「PGROONGA」を有効化 16

「PGROONGA」が見つからないときは ● Setting - General - Infrastructure の Pause project ○ 一旦プロジェクトを停止後、再び起動する Restart serverではなく 使うのはこちら→ 17

検索対象テーブルに全文検索用インデックスを追加 ● 元のテーブル・インデックス定義はこちら CREATE TABLE spot_opendata ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, category_id int REFERENCES category (id) NOT NULL, title text NOT NULL, describe text NOT NULL, location geometry(point, 4326) NOT NULL, prefecture text NOT NULL, municipality text NOT NULL, pref_muni text GENERATED ALWAYS AS (prefecture || municipality) STORED, created_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL, updated_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL ); CREATE INDEX spot_location_idx ON spot_opendata USING GIST (location); CREATE INDEX spot_pref_idx ON spot_opendata (prefecture); CREATE INDEX spot_muni_idx ON spot_opendata (municipality); CREATE INDEX spot_pref_muni_idx ON spot_opendata (pref_muni); 18

検索対象テーブルに全文検索用インデックスを追加 ● 全文検索用の生成列とインデックスを追加 ○ https://pgroonga.github.io/ja/tutorial/ ALTER TABLE spot_opendata ADD COLUMN ft_text text GENERATED ALWAYS AS (title || ',' || describe || ',' || prefecture || municipality) STORED; CREATE INDEX pgroonga_content_index ON spot_opendata USING pgroonga (ft_text) WITH (tokenizer='TokenMecab'); ● スポットのタイトル・説明文・都道府県＋市区町村が検索対象 ● トークナイザーとして MeCab（形態素解析器）を指定 19

• https://pgroonga.github.io/ja/tutorial/

全文検索を使うストアドファンクションを実装 ● 近隣スポット検索のストアドファンクションを変更 CREATE OR REPLACE FUNCTION get_spots(point_latitude double precision, point_longitude double precision, dist_limit int, category_id_number int, keywords text) RETURNS TABLE ( distance double precision, category_name text, title text, describe text, latitude double precision, longitude double precision, prefecture text, municipality text ) AS $$ ● 検索キーワード「keywords」を引数に追加 20

全文検索を使うストアドファンクションを実装 BEGIN RETURN QUERY SELECT ((ST_POINT(point_longitude, point_latitude)::geography <-> spot_opendata.location::geography) / 1000) AS distance, category.category_name, spot_opendata.title, spot_opendata.describe, ST_Y(spot_opendata.location), ST_X(spot_opendata.location), spot_opendata.prefecture, spot_opendata.municipality FROM spot_opendata INNER JOIN category ON spot_opendata.category_id = category.id ● このあたりは変更なし 21

全文検索を使うストアドファンクションを実装
WHERE
(CASE WHEN dist_limit = -1 AND keywords = '' THEN false ELSE true END)
AND
(CASE WHEN dist_limit = -1 THEN true
ELSE (ST_POINT(point_longitude, point_latitude)::geography <-> spot_opendata.location::geography) <=
dist_limit END)
AND
(CASE WHEN category_id_number = -1 THEN true
ELSE category.id = category_id_number END)
AND
(CASE WHEN keywords = '' THEN true
ELSE ft_text &@~ keywords END)
ORDER BY distance;
END;
$$ LANGUAGE plpgsql;

○ 「ft_text &@~ keywords」が全文検索を行っている箇所
■

「LIKE」は性能面で不利なので「&@」または「&@~」を使うほうが良い
22

ストアドファンクションを直接呼び出してみる ● Database - SQL Editor で「get_spots」を呼び出す 23

クライアント側のコードを実装
● SupabaseClient を使って接続
import 'package:mapbox_gl/mapbox_gl.dart';
import 'package:supabase_flutter/supabase_flutter.dart';
import 'class_definition.dart';
// Supabase Client
SupabaseClient getSupabaseClient(String supabaseUrl, String supabaseKey) {
return SupabaseClient(supabaseUrl, supabaseKey);
}

○ 今回、Flutter 用のライブラリ「supabase_flutter」に変更
■

ここでの使い方は以前の「supabase」（Dart 用ライブラリ）と同じ

24

RPC でストアドファンクションを呼び出す（変更）
Future<List<SpotData>> searchNearSpot(SupabaseClient client, LatLng latLng,
int? distLimit, int? categoryId, String? keywords) async {
final List<dynamic> items =
await client.rpc('get_spots', params: {
'point_latitude': latLng.latitude,
←以前のコードに引数「keywords」を追加しただけ
'point_longitude': latLng.longitude,
（ライブラリのバージョンアップで最後の
'dist_limit': (distLimit ?? -1),
'category_id_number': (categoryId ?? -1),
「.execute()」が不要に）
'keywords': (keywords ?? '')
});
final List<SpotData> resultList = [];
for (dynamic item in items) {
final SpotData spotData = SpotData(
item['distance'] as num,
item['category_name'] as String,
item['title'] as String,
item['describe'] as String,
LatLng((item['latitude'] as num).toDouble(),
(item['longitude'] as num).toDouble()),
PrefMuni(item['prefecture'] as String, item['municipality'] as String));
resultList.add(spotData);
}
return resultList;
}

25

同義語検索を追加する ● Supabase（Database）側で設定 ○ https://pgroonga.github.io/ja/how-to/synonym-expansion.html ○ 同義語テーブルを作成 ○ 同義語テーブルにインデックスを追加 ○ ストアドファンクションに同義語展開の実装を追加 ● クライアント側のコードには変更なし 26

• https://pgroonga.github.io/ja/how-to/synonym-expansion.html

同義語テーブルを作成し、インデックスを追加 CREATE TABLE synonyms ( term text PRIMARY KEY, synonyms text[] ); CREATE INDEX synonyms_search ON synonyms USING pgroonga (term pgroonga_text_term_search_ops_v2); INSERT INTO synonyms (term, synonyms) VALUES ('美術館', ARRAY['美術館', 'ミュージアム']); INSERT INTO synonyms (term, synonyms) VALUES ('博物館', ARRAY['博物館', 'ミュージアム']); INSERT INTO synonyms (term, synonyms) VALUES ('ミュージアム', ARRAY['ミュージアム', '美術館', '博物館']); ● 同義語テーブルに同義語データを追加 ○ 「美術館」→「ミュージアム」 ○ 「博物館」→「ミュージアム」 ○ 「ミュージアム」→「美術館」「博物館」 27

ストアドファンクションの全文検索部分を変更 ● 「pgroonga_query_expand」で同義語を展開 AND (CASE WHEN keywords = '' THEN true ELSE ft_text &@~ pgroonga_query_expand('synonyms', 'term', 'synonyms', keywords) END) 28

再びストアドファンクションを直接呼び出してみる 29

ストップワード（検索除外キーワード・文字列）対応 ● PGroonga では Groonga のストップワードに非対応 ○ TokenFilterStopWord の指定はできるものの辞書が指定できない ● ワークアラウンドで対応する ○ 検索キーワードおよび検索対象から特定の文字列を除外する ■ ストップワードが多いケースには向かない ○ 今回はインデックス適用対象の生成列変更で対応 ■ 式インデックスを使う方法もある 30

全文検索用インデックスを変更する ● 全文検索用の生成列を変更してインデックスを再作成 DROP INDEX pgroonga_content_index; ALTER TABLE spot_opendata DROP COLUMN ft_text, ADD COLUMN ft_text text GENERATED ALWAYS AS (REGEXP_REPLACE((title || ',' || describe || ',' || prefecture || municipality), '[の・]', '', 'g')) STORED; CREATE INDEX pgroonga_content_index ON spot_opendata USING pgroonga (ft_text) WITH (tokenizer='TokenMecab'); ● 一旦インデックスを DROP して生成列を変更してから再作成 ● 生成列で検索対象から「の」「・」を正規表現で除外 31

ストアドファンクションの全文検索部分を再変更 ● 検索キーワードの「の」「・」も除外 AND (CASE WHEN keywords = '' THEN true ELSE ft_text &@~ pgroonga_query_expand('synonyms', 'term', 'synonyms', REGEXP_REPLACE(keywords, '[の・]', '', 'g')) END) ● 式インデックスを使う方法はこちらの ML アーカイブを参照 https://ja.osdn.net/projects/groonga/lists/archive/dev/2018-November/004708.html 32

• https://ja.osdn.net/projects/groonga/lists/archive/dev/2018-November/004708.html

ストアドファンクションを直接呼び出してみる 33

まとめ ● Supabase では PGroonga の標準的な機能が使える ● 検索クエリの実装はストアドファンクションで対応 ● クライアント側で RPC を使ってストアドファンクション を呼び出して使う ○ クライアント側では自由なクエリは実装できない ■ 自由に実装できると SQL インジェクションなどの脆弱性に繋がる 34

Supabase を発表ネタとして取り上げている理由 ● たまたま ● 「Supabase が○○をサポート」というアナウンスを見て PostgreSQL の拡張機能と使い方を知ることが多い ○ 結果として PostgreSQL の便利な使い方が覚えられる ■ 今月は pgvector を OpenAI と組み合わせて使う例が Blog に示されている https://supabase.com/blog/openai-embeddings-postgres-vector 35

• https://supabase.com/blog/openai-embeddings-postgres-vector