やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

SQLite3のSQL文だけでバリデートする(trriger,raise)

 正常値リストをメタ表に入れることで変数にする。

成果物

情報源

コード

 値がA,B,Cのいずれかであることを確認したい。このとき以下のselect文で真偽値を取得できる。

select 'A' IN ('A','B','C'); /* 1 */
select 'X' IN ('A','B','C'); /* 0 */

 変数を使ってみる。

.parameter set @value 'A'
select @value IN ('A','B','C'); /* 1 */
.parameter set @value 'X'
select @value IN ('A','B','C'); /* 0 */

 表を使ってみる。

create table ValidateValues(Value numeric primary key not null) without rowid;
insert into ValidateValues(Value) values('A'),('B'),('C');

.parameter set @value 'A'
select @value IN (select Value from ValidateValues); /* 1 */

.parameter set @value 'X'
select @value IN (select Value from ValidateValues); /* 0 */

 ValidateValuesのテーブル名は以下のようにすることで重複なく作成できそう。

$TableName_$FieldName_ValidateValues

 でも長すぎるので短くしたい。

$TableName_$FieldName_VV

 これをトリガーで実装する。

create table Names(Name text);
create table Names_Name_VV(Expected numeric primary key not null) without rowid;
insert into Names_Name_VV(Expected) values('A'),('B'),('C');

create trigger Validate_Insert_Names_Name before insert on Names
when not(select NEW.Name in (select Expected from Names_Name_VV))
begin
    select raise(ABORT, 'Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.');
end;

create trigger Validate_Update_Names_Name before update on Names
when not(select NEW.Name in (select Expected from Names_Name_VV))
begin
    select raise(ABORT, 'Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.');
end;

 テーブルに挿入、更新して動作確認。成功。

sqlite> insert into Names values('X');
Error: Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.
sqlite> insert into Names values('A');
sqlite> select * from Names;
A
sqlite> update Names set Name = 'X';
Error: Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.
sqlite> select * from Names;
A
sqlite> update Names set Name = 'B';
sqlite> select * from Names;
B

実行結果

========== in.sql ==========
sqlite> select 'A' IN ('A','B','C'); /* 1 */
1
sqlite> select 'X' IN ('A','B','C'); /* 0 */
0
sqlite> 
========== in_var.sql ==========
sqlite> .parameter set @value 'A'
sqlite> select @value IN ('A','B','C'); /* 1 */
1
sqlite> .parameter set @value 'X'
sqlite> select @value IN ('A','B','C'); /* 0 */
0
sqlite> 
========== table.sql ==========
sqlite> create table ValidateValues(Value numeric primary key not null) without rowid;
sqlite> insert into ValidateValues(Value) values('A'),('B'),('C');
sqlite> .parameter set @value 'A'
sqlite> select @value IN (select Value from ValidateValues); /* 1 */
1
sqlite> .parameter set @value 'X'
sqlite> select @value IN (select Value from ValidateValues); /* 0 */
0
sqlite> 
========== trigger_0.sql ==========
sqlite> create table Names(Name text);
sqlite> create table Names_Name_VV(Expected numeric primary key not null) without rowid;
sqlite> insert into Names_Name_VV(Expected) values('A'),('B'),('C');
sqlite> 
sqlite> create trigger Validate_Insert_Names_Name before insert on Names
   ...> when not(select NEW.Name in (select Expected from Names_Name_VV))
   ...> begin
   ...>     select raise(ABORT, 'Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.');
   ...> end;
sqlite> 
sqlite> insert into Names values('X');
Error: Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.
sqlite> insert into Names values('A');
sqlite> select * from Names;
A
sqlite> 
========== trigger_1.sql ==========
sqlite> create table Names(Name text);
sqlite> create table Names_Name_VV(Expected numeric primary key not null) without rowid;
sqlite> insert into Names_Name_VV(Expected) values('A'),('B'),('C');
sqlite> 
sqlite> create trigger Validate_Insert_Names_Name before insert on Names
   ...> when not(select NEW.Name in (select Expected from Names_Name_VV))
   ...> begin
   ...>     select raise(ABORT, 'Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.');
   ...> end;
sqlite> create trigger Validate_Update_Names_Name before update on Names
   ...> when not(select NEW.Name in (select Expected from Names_Name_VV))
   ...> begin
   ...>     select raise(ABORT, 'Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.');
   ...> end;
sqlite> 
sqlite> insert into Names values('X');
Error: Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.
sqlite> insert into Names values('A');
sqlite> select * from Names;
A
sqlite> update Names set Name = 'X';
Error: Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.
sqlite> select * from Names;
A
sqlite> update Names set Name = 'B';
sqlite> select * from Names;
B
sqlite> 
========== trigger_attach.sql ==========
sqlite> -- データと正常値リストを別DBにする。だがTriggerは同一テーブルしか参照できない。スキーマ名があるとエラーになる。
sqlite> -- Error: trigger Validate_Insert_Names_Name cannot reference objects in database valid
sqlite> attach ':memory:' as valid;
sqlite> create table Names(Name text);
sqlite> create table valid.Names_Name(Expected numeric primary key not null) without rowid;
sqlite> insert into valid.Names_Name(Expected) values('A'),('B'),('C');
sqlite> 
sqlite> create trigger Validate_Insert_Names_Name before insert on Names
   ...> when not(select NEW.Name in (select Expected from valid.Names_Name))
   ...> begin
   ...>     select raise(ABORT, 'Invalid value. The Names.Name must match one of the Expected column values in the valid.Names_Name table.');
   ...> end;
Error: trigger Validate_Insert_Names_Name cannot reference objects in database valid
sqlite> 
========== trigger_param.sql ==========
sqlite> -- トリガーは変数を使えない。
sqlite> -- Error: trigger cannot use variables
sqlite> create table Names(Name text);
sqlite> create table Names_Name_VV(Expected numeric primary key not null) without rowid;
sqlite> insert into Names_Name_VV(Expected) values('A'),('B'),('C');
sqlite> 
sqlite> .param set @actual 'X'
sqlite> create trigger Validate_Insert_Names_Name before insert on Names
   ...> when not(select @actual in (select Expected from Names_Name_VV))
   ...> begin
   ...>     select raise(ABORT, 'Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.');
   ...> end;
Error: trigger cannot use variables
sqlite> 
sqlite> --.param set @actual 'X'
sqlite> --insert into Names values(@actual);
sqlite> --.param set @actual 'A'
sqlite> --insert into Names values(@actual);
sqlite> --select * from Names;
sqlite> 

注意

  • トリガーは変数が使えない(.param set
    • 代わりにNEW.列名, OLD.列名を使う
  • トリガーのinsert,update時が同じコードでも2つ作成せねばならない
    • 冗長だが仕方ない
  • 期待値を保持するメタテーブルが必要である
    • 同一データベースファイル内であるべき
      • トリガーは別DBをattachしても参照できない

所感

 check制約で変数やサブクエリに対応してくれたらトリガー不要で助かるのだが……。

対象環境

$ uname -a
Linux raspberrypi 4.19.97-v7l+ #1294 SMP Thu Jan 30 13:21:14 GMT 2020 armv7l GNU/Linux