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

第 39 回 PostgreSQL アンカンファレンス@オンライン 2023/2/20

Transcript

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

   View Slide

2. 自己紹介
   松久裕保（@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
   

   View Slide

3. 今回の発表ネタ
   ● 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
   

   View Slide

4. 最近の発表ネタが 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
   

   View Slide

5. 関連記事
   ● 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
   

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

18. 検索対象テーブルに全文検索用インデックスを追加
    ● 元のテーブル・インデックス定義はこちら
    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
    

    View Slide

19. 検索対象テーブルに全文検索用インデックスを追加
    ● 全文検索用の生成列とインデックスを追加
    ○ 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
    

    View Slide

20. 全文検索を使うストアドファンクションを実装
    ● 近隣スポット検索のストアドファンクションを変更
    ● 検索キーワード「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
    

    View Slide

21. 全文検索を使うストアドファンクションを実装
    ● このあたりは変更なし
    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
    

    View Slide

22. 全文検索を使うストアドファンクションを実装
    ○ 「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
    

    View Slide

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

    View Slide

24. クライアント側のコードを実装
    ● 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
    

    View Slide

25. RPC でストアドファンクションを呼び出す（変更）
    Future> searchNearSpot(SupabaseClient client, LatLng latLng,
    int? distLimit, int? categoryId, String? keywords) async {
    final List 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 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()」が不要に）
    

    View Slide

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

    View Slide

27. 同義語テーブルを作成し、インデックスを追加
    ● 同義語テーブルに同義語データを追加
    ○ 「美術館」→「ミュージアム」
    ○ 「博物館」→「ミュージアム」
    ○ 「ミュージアム」→「美術館」「博物館」
    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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

31. 全文検索用インデックスを変更する
    ● 全文検索用の生成列を変更してインデックスを再作成
    ● 一旦インデックスを 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
    

    View Slide

32. ストアドファンクションの全文検索部分を再変更
    ● 検索キーワードの「の」「・」も除外
    ● 式インデックスを使う方法はこちらの 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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide