SQLite3 : 新機能(3.31.0) Generated Column (生成列)を使ってみました!

MySQL, PostgreSQL で使用できる Generated Column (生成列) が SQLiteでも バージョン 3.31.0 (2020/01/22 リリース)からサポートされましたので使ってみましたのでレポートします。
Generated Column を使うと、(同じテーブル内の) 他の列の値を利用した計算結果を、特定の列に格納することが可能になります。Oracleの仮想列に近い機能だと思います。
今回は前半がリリース前のドキュメントのまとめ(ほとんど日本語訳!?)と、実際に使ってみての検証を書いてみました。

Generated Column とは

Generated Column は同じレコードの他の列から計算結果を格納した列です。Generated Column は読み込み可能ですが、直接、値を書き込むことはできません。Generated Column の値を変更する方法は、Generated Column の計算で使用される列の値を変更するしかありません。

構文

構文的には、Generated Column は、"GENERATED ALWAYS" 列制約を使って指定されます。
例："a", "b", "c" は通常列、"d", "e" が Generated Column

example1.sql

CREATE TABLE t1(
   a INTEGER PRIMARY KEY,
   b INT,
   c TEXT,
   d INT GENERATED ALWAYS AS (a*abs(b)) VIRTUAL,
   e TEXT GENERATED ALWAYS AS (substr(c,b,b+1)) STORED
);

"GENERATED ALWAYS" キーワードは制約の最初に記述して、"VIRTUAL" と "STORED" キーワードは制約の最後に記述します。
"AS" キーワードと () だけは必須記述となりますが、他のキーワードは省略が可能です。
"VIRTUAL" か "STORED" のキーワードが省略された場合、 VIRTUAL がデフォルトになります。

従って、上記 SQL 文を以下のようなシンプルな SQL 文にすることができます。

example2.sql

CREATE TABLE t1(
   a INTEGER PRIMARY KEY,
   b INT,
   c TEXT,
   d INT AS (a*abs(b)),
   e TEXT AS (substr(c,b,b+1)) STORED
);

VIRTUAL 列と STORED 列

Generated Column は、VIRTUAL か STORED のどちらかになります。
VIRTUAL 列の値は、読み込み時に計算されるのに対して、STORED 列の値はレコードが書き込まれる時に計算されます。STORED 列はデータベースファイルの中で格納するスペースが必要なのに対して。VIRTUAL 列はより多くの CPU を使用します。
SQL の視点では、STORED 列と VIRTUAL 列は、ほとんど同じでクエリの結果も同じです。
唯一の機能的な違いは、ALTER TABLE ADD COLUMN コマンドを使用して、新しい STORED 列を追加することができません。VIRTUAL 列は、ALTER TABLE コマンドで追加可能です。

機能

1. Generated Column は、データタイプを持つことができます。SQLite は通常列と同じアフィニティルールを使用して生成する表現の結果をそのデータ型に変換することを試します。
2. Generated Column は、通常列のように、NOT NULL, CHECK, UNIQUE制約や外部キー制約を持つことができるしれません。
3. Generated Column は、通常列のようにインデックスに加えることができます。
4. Generated Column は、テーブル定義のどこでも存在することができます。example1.sql, example2.sql のようにテーブル定義の列リストの終わりに Generated Column に入れる必要はありません。つまり、通常列とGenerated Columnを交互に記述することも可能です。

制限事項

1. Generated Column はデフォルト値を持つことができません( DEFAULT 句を使用することができません)。Generated Column の値は常に "AS" キーワードに続く表現によって指定された値です。
2. Generated Column は、PRIMARY KEY の一部として使用できません。
3. Generated Column は、同じレコードのリテラルおよび列を参照するだけかもしれないし、 deterministic 関数を使用するだけかもしれません。Generated Column の表現はサブクエリ、集約関数、ウインド関数などを使用できません。
4. Generated Column の表現は同じレコードで他の Generated Column を参照できません。
5. Generated Column の表現は直接 ROWID を参照できませんが、INTEGER PRIMARY KEY 列は参照できます。
6. どのテーブルも最低 1つのGenerated Column 以外の列が必要です。
7. ALTER TABLE ADD COLUMN で、STORED 列を追加できませんが、VIRTUAL 列は追加できます。

互換性

Generated Column は、SQLite version 3.31.0 で追加されましたが、SQLite の旧バージョンがスキーマに Generated Column を含んでいるデータベースファイルをオープンしようとすると、 Generated Column 構文をエラーと判断して、データベース破損エラーになります。
SQLite version 3.31.0 は SQLite 3.0.0 (2004-06-18) 以降のどのバージョンで作られたデータベースも読み書きできます。そして、3.31.0 以前のバージョンは、Generated Column が含んでいない限り、3.31.0 で作成されたデータベースを読み書きできます。

Generated Columnを使ってみました!

テーブルを作成

CREATE TABLE で Generated Column を使った emp テーブルを作成します。full_name 列と len 列が、Generated Column です。

sqlite> CREATE TABLE emp(
   ...>   id        INTEGER PRIMARY KEY,
   ...>   name1     TEXT,
   ...>   name2     TEXT,
   ...>   full_name AS (name1||' '||name2) STORED,
   ...>   len       AS (length(name1||' '||name2)) VIRTUAL
   ...> );

データを追加

実際に値を emp テーブルに INSERT します。

sqlite> INSERT INTO emp(id, name1, name2) VALUES (1, "Toshiba", "Taro");
sqlite> INSERT INTO emp(id, name1, name2) VALUES (2, "Kannai", "Jiro");
sqlite> INSERT INTO emp(id, name1, name2) VALUES (3, "Mishima", "Saburo");

Generated Column を SELECT で確認

SELECT でテーブルを参照すると、full_name 列と len 列に格納されています。

sqlite> SELECT * FROM emp;
id          name1       name2       full_name     len       
----------  ----------  ----------  ------------  ----------
1           Toshiba     Taro        Toshiba Taro  12        
2           Kannai      Jiro        Kannai Jiro   11        
3           Mishima     Saburo      Mishima Sabu  14

Generated Column を直接、更新してみると・・・

Generated Column を直接、更新してみます。INSERT でも、UPDATE でもエラーになります。

sqlite> INSERT INTO emp(id, name1, name2, full_name) VALUES (4, "Amakusa", "Shiro", "Amakusa Shiro");
Error: cannot INSERT into generated column "full_name"
sqlite> INSERT INTO emp(id, name1, name2, len) VALUES (4, "Amakusa", "Shiro", 13);
Error: cannot INSERT into generated column "len"
sqlite> UPDATE emp SET len=0;
Error: cannot UPDATE generated column "len"

列指定を省略した INSERT

INSERT で列指定が面倒だという場合、通常列の数のデータを VALUES に記述すると可能です。

sqlite> INSERT INTO emp VALUES (4, "Amakusa", "Shiro");

ALTER TABLE で、flag 列を追加して、INSERT で列指定を省略して実行してみます。VALUES のデータ数を通常列数の 4つにすれば、問題なく登録ができます。

sqlite> ALTER TABLE emp ADD COLUMN flag BOOLEAN;
sqlite> INSERT INTO emp VALUES (5, "Yamada", "Goro",TRUE);
sqlite> SELECT * FROM emp;
id          name1       name2       full_name     len         flag      
----------  ----------  ----------  ------------  ----------  ----------
1           Toshiba     Taro        Toshiba Taro  12                    
2           Kannai      Jiro        Kannai Jiro   11                    
3           Mishima     Saburo      Mishima Sabu  14                    
4           Amakusa     Shiro       Amakusa Shir  13                    
5           Yamada      Goro        Yamada Goro   11          1 

ちなみに、PostgreSQL の Generated Column でも同じ INSERT で大丈夫です。MySQL の Generated Column は、以下のように VALUES の Generated Column 列に DEFAULT を記述することで、列指定を省略した INSERT が可能です。

INSERT INTO emp VALUES (4, "Amakusa", "Shiro", DEFAULT, DEFAULT);

データベースファイルをダンプして、Generated Column を 確認

Generated Column が格納されたデータベースファイルをダンプしてみました。

$ od -tx1z -Ax test.db
000000 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00  >SQLite format 3.<
---------------------------        省略         ---------------------------
001f60 00 00 00 00 00 00 00 1b 05 06 00 19 15 23 09 59  >.............#.Y<
001f70 61 6d 61 64 61 47 6f 72 6f 59 61 6d 61 64 61 20  >amadaGoroYamada <
001f80 47 6f 72 6f 1e 04 05 00 1b 17 27 41 6d 61 6b 75  >Goro......'Amaku<
001f90 73 61 53 68 69 72 6f 41 6d 61 6b 75 73 61 20 53  >saShiroAmakusa S<
001fa0 68 69 72 6f 20 03 05 00 1b 19 29 4d 69 73 68 69  >hiro .....)Mishi<
001fb0 6d 61 53 61 62 75 72 6f 4d 69 73 68 69 6d 61 20  >maSaburoMishima <
001fc0 53 61 62 75 72 6f 1a 02 05 00 19 15 23 4b 61 6e  >Saburo......#Kan<
001fd0 6e 61 69 4a 69 72 6f 4b 61 6e 6e 61 69 20 4a 69  >naiJiroKannai Ji<
001fe0 72 6f 1c 01 05 00 1b 15 25 54 6f 73 68 69 62 61  >ro......%Toshiba<
001ff0 54 61 72 6f 54 6f 73 68 69 62 61 20 54 61 72 6f  >TaroToshiba Taro<

SQLite では、レコードがページの最後から格納されます。
001fe6 は、第1レコードの先頭です。001fe9 - 001fef : name1 列、001ff0 - 001ff4 : name2 列、そして、001ff5 - 001fff : Generated Column の full_name 列です。STORED 列なのでデータベースファイルに格納されます。一方、len 列は、VIRTUAL 列なのでデータベースファイルに格納されていません。

最後に

SQLite の新機能の Generated Column を実際に使ってみました。SQLite 側で処理をしてくれるので、アプリ側の処理を減らすことができる等のメリットがありそうです。
また、これまで view で対応していた仮想列やトリガーでの処理などが、Generated Column を使うことで対応できるので、余分な view やトリガーを作らなくても良くなるのではないかと思いました。

もし、記述について誤りがあったり、気になることがあれば、編集リクエストやコメントでフィードバックしていただけると助かります。