SQLite on Railsシリーズ（06）arrayカラムを使えるようにする（翻訳）

SQLite on Railsシリーズ（06）arrayカラムを使えるようにする（翻訳）

Ruby on RailsアプリケーションでSQLiteを使うのをためらう理由の1つに、 PostgeSQLやMySQLで慣れ親しんだ特定の機能が使えなくなるのではないかという不安があると思います。
過去記事で説明したように、SQLite拡張機能をRailsアプリケーションに読み込むことでSQLiteの機能を拡張できます。さらに今回は、私がPostgreSQLで好きな機能の1つであるarrayカラムに一致する振る舞いをSQLiteのプリミティブ上に構築して提供できることをお見せしたいと思います。

---

リレーショナルデータを扱っていると、「関連データがほんの少ししかない場合はどう扱えばよいのか」というジレンマに遭遇することがよくあります。

• 外部キーを指定してまったく新しいテーブルを作成すれば、スキーマは高度に正規化されますが、このデータにアクセスするたびにJOINしなければならなくなります。
• データをプライマリテーブルのJSONカラムに単純に詰め込めば、JOINは不要になるものの、プライマリテーブルが肥大化して、そのカラムに詰め込まれるデータが構造化されなくなる可能性が生じます。

こんなときPostgreSQLなら、arrayカラムが実装されているので、両者の適切な妥協案を提供しています。
このカラムは、不定形のJSON BLOBと異なり、常に単純な値の配列になります。しかもJOINを必要とせずに、シンプルな2列の関連テーブル（外部キーカラムと値のカラム）の有効な振る舞いと一致します。

このツールの有用性がよくわかる私のお気に入りの例として、Nate Hopkinによるタグ付けシステムの実装を考えてみましょう。これはPostgreSQLのarrayカラム上に構築されています。

皆さんがリンク先をクリックしなくてもこのソリューションがいかに優れているかを示すために、READMEのコード例を以下に引用します。

# db/migrate/TIMESTAMP_add_groups_to_user.rb
class AddGroupsToUser < ActiveRecord::Migration[5.0]
  def change
    add_column :users, :groups, :string, array: true, default: [], null: false
    add_index :users, :groups, using: "gin"
  end
end

# app/models/user.rb
class User < ApplicationRecord
  include TagColumns
  tag_columns :groups
end

user = User.find(1)

# タグを割り当てる
user.groups << :reader
user.groups << :writer
user.save

# タグをチェックする
is_writer            = user.has_group?(:writer)
is_reader_or_writer  = user.has_any_groups?(:reader, :writer)
is_reader_and_writer = user.has_all_groups?(:reader, :writer)

# タグ付きのレコードを検索する
assigned                = User.with_groups
unassigned              = User.without_groups
writers                 = User.with_any_groups(:writer)
non_writers             = User.without_any_groups(:writer)
readers_or_writers      = User.with_any_groups(:reader, :writer)
readers_and_writers     = User.with_all_groups(:reader, :writer)
non_readers_and_writers = User.without_all_groups(:reader, :writer)

# 全ユーザーで一意のタグを検索する
User.unique_groups

# 名前が`Smith`のユーザーで一意のタグを検索する
User.unique_groups(last_name: "Smith")

モデルにarrayカラムを1つ追加するだけで、コアとなる「タグ付けスタイル」機能の完全なスイートを利用できます。
私はこういうソリューションが大好きです。gem全体は、TagColumns concernを定義するファイル1つだけで構成され、そのファイルはたった105行（コードは89 行）です。SQLiteはエレガントでシンプルです。では、arrayカラムプリミティブを使わずに同じ結果を実現するにはどうすればよいでしょうか。

---

最初に、配列にしかならないカラムをテーブルに追加する方法について見ていきましょう。SQLiteはさまざまなJSON機能をサポートしています。また、カラムのCHECK制約も標準でサポートしています。これで必要なものはすべて揃います。JSONカラムを定義して、そのカラムがarray JSON型のみになることを保証する制約を追加します。RailsのマイグレーションDSLでテーブルの作成時にこのカラムを作成すると、以下のようになります。

create_table :posts, force: true do |t|
  t.json :tags, null: false, default: []
  t.check_constraint "JSON_TYPE(tags) = 'array'", name: 'post_tags_is_array'
end

このカラムを既存のテーブルに追加するだけの場合、マイグレーションは以下のようになります。

add_column :posts, :tags, :json, default: [], null: false
add_check_constraint "JSON_TYPE(tags) = 'array'", name: 'post_tags_is_array'

これによって、値の配列だけで構成されるJSONカラムが作成されます。スキーマを設定せずに、サポートしたい「タグ付け可能な」機能に目を向けてみましょう。tag_columnsは以下の11個のメソッドをサポートしています²。

Model.unique_column_name()
Model.column_name_cloud()
Model.with_column_name()
Model.without_column_name()
Model.with_any_column_name(*items)
Model.with_all_column_name(*items)
Model.without_any_column_name(*items)
Model.without_all_column_name(*items)

model.has_any_column_name(*items)
model.has_all_column_name(*items)
model.has_column_name(*items)

これらのメソッドをサポートするSQLクエリがそれぞれ必要です。その後、Active Recordメソッドを呼び出してそれらのクエリを生成します。本記事では個別のクエリとActive Recordメソッドについて考える余裕はないので、要点のみ記します。

実装の中心となるのは、SQLiteが提供するJSON_EACH関数です。この関数は、配列内にある個別の値をテーブル内の行であるかのように扱います。個別の仮想行には、SELECT可能なvalueカラムがあります。したがって、例のpostsテーブルのtagsカラムの一意の値セットを取得するには、以下のSQLクエリが必要です。

SELECT DISTINCT value
FROM "posts",
     JSON_EACH("posts"."tags");

簡潔で読みやすいクエリですね。
同様に、draftタグが付けられたpostsを検索するには以下のクエリが使えます。

SELECT "posts".*
FROM "posts"
WHERE EXISTS (
  SELECT 1
  FROM JSON_EACH("posts"."tags")
  WHERE value IN ('draft')
  LIMIT 1
)

このクエリは複雑になりました。タグ付きのすべてのpostsを検索するには、postsとそのすべての属性をSELECTするクエリを、タグが存在するかどうかを確認するクエリから分離する必要があります。これは、ネストしたクエリの完璧なユースケースです。内側のクエリは、特定のタグが存在するかどうかを確認します³。

外側のクエリではWHERE EXISTSチェックのブール値を返すだけでよいため、SELECT 1を使います。
内側のクエリを少し最適化するためにLIMIT 1を使います。これは、draftがtags配列に1個以上存在するかどうかを知るだけでよく、重複は問題に鳴らないためです。すべてのany_*メソッドは、この形式のクエリによって駆動されます。

all_*メソッドをサポートするには、返されるpostsに、指定した個別の値が含まれるようにするクエリが必要です。
たとえば、Post.with_all_tags('draft', 'sqlite')は、draftタグとsqliteタグの両方でタグ付けされたposts だけを返さなければなりません。
draftタグのみでタグ付けされたpostsは無視されます。

このクエリは以下のような感じになります。

SELECT "posts".*
FROM "posts"
WHERE (
  SELECT COUNT(DISTINCT value)
  FROM JSON_EACH("posts"."tags")
  WHERE value IN ('draft', 'sqlite')
) = 2;

これで、基本的なWHERE EXISTSチェックの代わりに、外側のクエリは、postに一致するタグの個数がクエリされたタグの個数と一致するかどうかをチェックするようになりました。

JSON_EACH は、配列のカラムを「行を含む仮想テーブル」に効果的に変換することを思い出しましょう。したがって、 SELECT COUNT(*) FROM JSON_EACH() WHERE ...は、配列のカラムでwhere条件に一致するエントリの個数をカウントし、それを整数として返します。

内側のクエリから返された整数を用いることで、外側のクエリが、指定されたタグの合計数を含むpostsのみを返すようにできます。重複するtagsの可能性を処理するために、DISTINCT valueのみをCOUNTするようにします。SELECT COUNT(*)かSELECT COUNT(value)を使うと、重複が排除されず、内側のクエリから返される整数が2（クエリ対象のタグの配列のサイズ）より大きくなる可能性があります。内部クエリがタグの配列と同じかそれより小さい整数のみを返すようにするには、異なる値のみをカウントする必要があります。

ただし、これら3つの基本的なクエリが実装全体の基礎を形成します。with_*スコープは=を利用し、without_*は!=を利用しますが、形はすべて同じです。したがって、パズルの最後のピースは、これらのクエリをActive Recordで生成することです。

---

繰り返しますが、私はクエリの詳細にこだわるつもりはありません。Active Recordで生SQL文字列を使うのは脆弱なので、できれば生SQL文字列は使いたくありません。また、モデルにconcernを提供したいので、堅牢性は特に重要です。つまり、Arelを使う必要があります。

Arelは、Active Recordを支えるリレーショナル代数ライブラリで、ActiveRecordのクエリインターフェイスはArel上に構築されています（本記事ではそれらの詳細に立ち入らないので、Arelを直接操作するための優れた入門書として、こちらのブログ記事をどうぞ）。

前述したように、必要なすべてのクエリの中心ではJSON_EACHが使われるようになります。そのため、この関数をRubyで生成できる必要があります。Arelは、以下のように利用可能な関数のインターフェイスを提供します。

# JSON_EACH("{table}"."{column}")
json_each = Arel::Nodes::NamedFunction.new("JSON_EACH", [arel_table[column_name]])

原注: arel_tableが利用可能なのは、このコードがActive Recordモデル関連のコンテキストで実行されるからです。

json_each式オブジェクトが準備できたら、以下のように.unique_tagsメソッドを構築できます。

# SELECT DISTINCT value FROM "{table}", JSON_EACH("{table}"."{column}")
define_singleton_method :"unique_#{method_name}" do |conditions = "true"|
  select('value')
    .from([arel_table, json_each])
    .distinct
    .pluck('value')
    .sort
end

.with_any_tagsスコープを設定するには、以下のようなメソッドビルダーが必要です。

# SELECT "{table}".* FROM "{table}" WHERE EXISTS (SELECT 1 FROM JSON_EACH("{table}"."{column}") WHERE value IN ({values}) LIMIT 1)
scope :"with_any_#{method_name}", ->(*items) {
  values = array_columns_sanitize_list(items)
  overlap = Arel::SelectManager.new(json_each)
    .project(1)
    .where(Arel.sql('value').in(values))
    .take(1)
    .exists

  where overlap
}

上に対応する.with_all_tagsスコープは以下のようになります。

# SELECT "{table}".* FROM "{table}" WHERE (SELECT COUNT(DISTINCT value) FROM JSON_EACH("{table}"."{column}") WHERE value IN ({values})) = {values.size};
scope :"with_all_#{method_name}", ->(*items) {
  values = array_columns_sanitize_list(items)
  count = Arel::SelectManager.new(json_each)
    .project(Arel.sql('value').count(distinct = true))
    .where(Arel.sql('value').in(values))
  contains = Arel::Nodes::Equality.new(count, values.size)

  where contains
}

ここでは、必要なSQLクエリをArelベースのRubyコードにマッピングする方法を示すことが目的なので、各メソッドをすべて貼り付けるつもりはありません（Gistを参照してください）。

これらすべてをArrayColumnsモデルのconcernにラップすれば、準備完了です。

スキーママイグレーションとモデルのconcernを適切に記述したことで、SQLiteデータベースで「arrayカラム」型を定義し、JOINのコストをかけずにそれらを関連テーブルであるかのようにクエリできるようになりました。

以上で、SQLiteで利用できるパワーと柔軟性が実証されたと思います。PostgreSQLが提供するすべてのネイティブ機能やデータ型が揃っていなくても、少し工夫すれば同等の機能を提供できます。

モデルのconcernの完全なコードはこのGistで詳しく説明されています。完全なスクリプトとテストケースの完全なセットもチェックしてみてください。

関連記事

SQLite on Railsシリーズ（01）Gitブランチごとにデータベースを切り替える（翻訳）

SQLite on Railsシリーズ（02）SQLiteをチューニングで強化する（翻訳）

SQLite on Railsシリーズ（03）SQLite拡張機能を読み込む（翻訳）

SQLite on Railsシリーズ（04）LitestreamでSQLiteをバックアップしよう（翻訳）

https://techracho.bpsinc.jp/hachi8833/2025_04_15/149096