【Android / iOS】スマホOSとSQLiteのバージョン対応

スマホアプリ内のデータベースと言えば、SQLite を使うことが多いと思います。

自作のアプリでも SQLite を使っていましたが、非常にシンプルなクエリのみでした。
そのためあまり気にしていなかったのですが、新たなアプリで少し複雑なクエリが必要になって調べたところ、他の RDBMS のようにはできないことがありました。

現時点で機能が不足していても、新しいスマホ OS バージョンではサポートする SQLite のバージョンが上がり、今後だんだん解消されると思います。
しかし、古い OS までアプリを対応させる場合には考慮が必要になりそうなので、情報をまとめておきます。

SQLite と Android のバージョン

SQLite	Android API	Android OS
3.19	27	8.1
3.18	26	8.0
3.9	24	7.0
3.8	21	5.0
3.7	11	3.0.x
3.6	8	2.2.x

android.database.sqlite | Android Developers

最新の情報は上記リンク先をご覧ください。

また、この表では間が抜けていますが、次のページにはもっと細かい情報が載っています。
必要に応じて確認してください。

database - Version of SQLite used in Android? - Stack Overflow

SQLite と iOS のバージョン

SQLite	iOS
3.24.0	12.1
3.24.0	12.0
3.19.3	11.0
3.16.0	10.3.1
3.14.0	10.2
3.14.0	10.0 GM
3.13.0	10.0 beta 2
3.8.10.2	9.3.5
3.8.10.2	9.3.1
3.8.8	9.0
3.8.5	8.4
3.8.5	8.2
3.7.13	8.1
3.7.13	8.0.2
3.7.13	7.0.6
3.7.13	7.0
3.7.13	6.0.1
3.7.7	5.1.1
3.6.23.2	4.2.0

SQLite version (bundled with OS) · yapstudios/YapDatabase Wiki · GitHub

最新の情報は上記リンクの先をご覧ください。
（この記事の対象外ですが）Mac OS の情報も載っています。

Bulk Insert

INSERT INTO user (id, name) VALUES (1, '佐藤');
INSERT INTO user (id, name) VALUES (2, '鈴木');
INSERT INTO user (id, name) VALUES (3. '高橋');

普段サーバサイドで MySQL を使うことが多いのですが、複数のデータの INSERT はこのようにはしません。
この程度の数なら良いのですが、もっと多くなると一件ずつでは非常にパフォーマンスが悪くなるので、次のようにまとめて一度に行っています（Bulk Insert）。

INSERT INTO user (id, name)
VALUES (1, '佐藤'), (2, '鈴木'), (3. '高橋');

SQLite ではどうなのでしょうか。
公式のリリースノートを見てみます。

2012-03-20 (3.7.11)

Enhance the INSERT syntax to allow multiple rows to be inserted via the VALUES clause.

この部分が Bulk Insert のことになりますね。
SQLite 3.7.11 から対応しているようです。

Android 4.1（API レベル 16）以上
iOS 8.1 以上

これより前の OS バージョンは、そろそろサポート対象外にしても良さそうなほど古くなっていますね。
あまり問題にならないかもしれません。

UPSERT

レコードが既に存在していれば UPDATE し、なければ INSERT する機能です。
これは SQLite 3.24.0 でサポートされています。

Android 未対応
iOS 12.0 以上

RDBMS によって使い方が異なりますが、「Add support for PostgreSQL-style UPSERT.」と書かれているので SQLite は PostgreSQL 風です。 ON CONFLICT のやつです。

ちなみに、INSERT OR REPLACE はもっと古くから対応しているようです。
UPSERT とは似て非なるものなので代わりに使えるとは限りません。
更新の仕方もたぶん違います（REPLACE のほうはおそらく DELETE と INSERT が起こります）。

Window 関数

古い MySQL のバージョンで Window 関数に対応していない件について、以前に記事を書きました。

【MySQL】グループごとの上位N件ずつを取得 - のんびり精進

新たに作っているアプリでサーバサイド（MySQL）とアプリ（SQLite）で似たテーブル構造にしておいて、クエリも似た感じでできればと思ったのですが、SQLite でも Window 関数の問題にぶち当たりました。

SQLite での Window 関数の対応状況は、次のページの「6. History」という部分に書かれています。

Window Functions

Window function support was first added to SQLite with release version 3.25.0 (2018-09-15).

SQLite 3.25.0 から対応したと書かれています。
SQLite と Android / iOS とのバージョン対応表を先ほど載せましたが、その表で確認すると・・・

まだ対応していません！

Android 10 Q Beta：SQLite 3.22.0（非対応）
iOS 12.1： SQLite 3.24.0（非対応）

残念です。
あと少し、と言いたいところですが、対応するバージョンが出てきても対応しないバージョンまでサポートすることを考えると、本格的に使うのはかなり先になりそうです。

SQLite の Window 関数の動作と機能

ちなみに、先ほどのページを見ると SQLite の Window 関数について次のように書かれています。

The SQLite developers used the PostgreSQL window function documentation as their primary reference for how window functions ought to behave. Many test cases have been run against PostgreSQL to ensure that window functions operate the same way in both SQLite and PostgreSQL.

PostgreSQL の Window 関数と同じ動作となることを考慮しながら作られているようです。

In SQLite version 3.28.0 (2019-04-16), windows function support was extended to include the EXCLUDE clause, GROUPS frame types, window chaining, and support for " PRECEDING" and " FOLLOWING" boundaries in RANGE frames.

SQLite 3.28.0 では機能がさらに拡張されるとのことです。

今は少し不十分でも、今後は便利なものになっていきそうですね。