Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

hmatsu47
February 20, 2023

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

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

hmatsu47

February 20, 2023
Tweet

More Decks by hmatsu47

Other Decks in Technology

Transcript

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

    View full-size 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 full-size 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 full-size 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 full-size 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size slide

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

    View full-size 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 full-size 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 full-size slide

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

    View full-size 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size 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 full-size 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide