Share
Embed
Supabase で PGroonga がサポートされたので Flutter アプリから使ってみた
第 39 回 PostgreSQL アンカンファレンス@オンライン 2023/2/20
Transcript
35 slides
-
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 -
今回の発表ネタ • 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 -
最近の発表ネタが Supabase に偏っている件 4 回 タイトル 開催日 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 -
関連記事 • 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 -
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 -
実装対象の Flutter アプリ(maptool) 12 • 文化財などの近隣スポット検索(Supabase / PostGIS) ※第 29
回の発表後、カテゴリ別の ピン表示にも対応 ▪ -
実装対象の Flutter アプリ(maptool) 13 • スポットの全文検索(Supabase / PostGIS / PGroonga)
▪ PGroonga でキーワードを全文検索して ▪ PostGIS で距離が近い順に表示 • 地図の中心が起点 注:サンプルデータとして、以下を改変して利用 • 愛知県文化財マップ(ナビ愛知)、愛知県、クリエイ ティブ・コモンズ・ライセンス 表示2.1日本 • https://www.pref.aichi.jp/soshiki/joho/0000069385.html -
Supabase で全文検索を使うには • Supabase(Database)側で設定 ◦ PGroonga を有効化 ◦ 検索対象テーブルに全文検索用インデックスを追加 ◦
全文検索を使うストアドファンクションを実装 • クライアント側のコードを実装 ◦ RPC でストアドファンクションを呼び出す ▪ 関数や特殊な表現はクエリビルダーでは使用不可(PostGIS と同様) 14 -
注意 • 通常は RLS(行レベルセキュリティ)と認証を実装する ◦ 今回は自分一人で利用するアプリであり、アプリストアなどでの 公開もしないので RLS とユーザー認証を実装していない ◦
通常のアプリでは、情報漏洩や API の悪用防止のため RLS と ユーザー認証を実装する 15 -
「PGROONGA」が見つからないときは • Setting - General - Infrastructure の Pause project
◦ 一旦プロジェクトを停止後、再び起動する 17 Restart serverではなく 使うのはこちら→ -
検索対象テーブルに全文検索用インデックスを追加 • 元のテーブル・インデックス定義はこちら 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/ • スポットのタイトル・説明文・都道府県+市区町村が検索対象 • トークナイザーとして MeCab(形態素解析器)を指定
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'); 19 -
全文検索を使うストアドファンクションを実装 • 近隣スポット検索のストアドファンクションを変更 • 検索キーワード「keywords」を引数に追加 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 $$ 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 -
全文検索を使うストアドファンクションを実装 ◦ 「ft_text &@~ keywords」が全文検索を行っている箇所 ▪ 「LIKE」は性能面で不利なので「&@」または「&@~」を使うほうが良い 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; 22 -
クライアント側のコードを実装 • SupabaseClient を使って接続 ◦ 今回、Flutter 用のライブラリ「supabase_flutter」に変更 ▪ ここでの使い方は以前の「supabase」(Dart 用ライブラリ)と同じ
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); } 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, 'point_longitude': latLng.longitude, 'dist_limit': (distLimit ?? -1), 'category_id_number': (categoryId ?? -1), '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 ←以前のコードに引数「keywords」を追加しただけ (ライブラリのバージョンアップで最後の 「.execute()」が不要に) -
同義語検索を追加する • Supabase(Database)側で設定 ◦ https://pgroonga.github.io/ja/how-to/synonym-expansion.html ◦ 同義語テーブルを作成 ◦ 同義語テーブルにインデックスを追加 ◦
ストアドファンクションに同義語展開の実装を追加 • クライアント側のコードには変更なし 26 -
同義語テーブルを作成し、インデックスを追加 • 同義語テーブルに同義語データを追加 ◦ 「美術館」→「ミュージアム」 ◦ 「博物館」→「ミュージアム」 ◦ 「ミュージアム」→「美術館」「博物館」 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 -
ストップワード(検索除外キーワード・文字列)対応 • PGroonga では Groonga のストップワードに非対応 ◦ TokenFilterStopWord の指定はできるものの辞書が指定できない •
ワークアラウンドで対応する ◦ 検索キーワードおよび検索対象から特定の文字列を除外する ▪ ストップワードが多いケースには向かない ◦ 今回はインデックス適用対象の生成列変更で対応 ▪ 式インデックスを使う方法もある 30 -
全文検索用インデックスを変更する • 全文検索用の生成列を変更してインデックスを再作成 • 一旦インデックスを DROP して生成列を変更してから再作成 • 生成列で検索対象から「の」「・」を正規表現で除外 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'); 31 -
ストアドファンクションの全文検索部分を再変更 • 検索キーワードの「の」「・」も除外 • 式インデックスを使う方法はこちらの ML アーカイブを参照 https://ja.osdn.net/projects/groonga/lists/archive/dev/2018-November/004708.html AND (CASE
WHEN keywords = '' THEN true ELSE ft_text &@~ pgroonga_query_expand('synonyms', 'term', 'synonyms', REGEXP_REPLACE(keywords, '[の・]', '', 'g')) END) 32 -
まとめ • Supabase では PGroonga の標準的な機能が使える • 検索クエリの実装はストアドファンクションで対応 • クライアント側で
RPC を使ってストアドファンクション を呼び出して使う ◦ クライアント側では自由なクエリは実装できない ▪ 自由に実装できると SQL インジェクションなどの脆弱性に繋がる 34 -
Supabase を発表ネタとして取り上げている理由 • たまたま • 「Supabase が◦◦をサポート」というアナウンスを見て PostgreSQL の拡張機能と使い方を知ることが多い ◦
結果として PostgreSQL の便利な使い方が覚えられる ▪ 今月は pgvector を OpenAI と組み合わせて使う例が Blog に示されている https://supabase.com/blog/openai-embeddings-postgres-vector 35