やってみる

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

SQLite3のSQL文だけでバリデートする(check制約)

 バリデート値を変数にできない。リテラル値で指定する必要がある。

成果物

情報源

SQL構文におけるバリデーション

 check制約で行う。たとえばName列にはA,B,Cのいずれかしか入力できないようにするには以下。

create table Names(
  Name text,
  constraint IsValidName check(Name in ('A','B','C'))
);

問題

 正常値リストを変数にできない。

 check制約は変数、サブクエリどちらも非サポート。よってリテラル値しか指定できない。

 また、SQLite3はalter table add/drop constraint構文が未実装である。そのためcheck制約を変更することもできない。create tableからやり直すことになる。

変数にできないと困るとき

 正常値の仕様が変更されたときに困る。create tableを書き直して丸ごと作り直した上、データを挿入し直さねばならない。特に膨大なデータが存在するときは厳しい。倍のファイル容量と、長い処理時間を要する。

 もし正常値リストを変数にできれば、テーブルを作り直す必要がなくなり解決する。

考えられるバリデート・パターン

  • ある列の値は、ある範囲内であること(BETWEEN句相当)
  • ある列の値は、ある表が持つ値のいずれかと一致する(IN句相当)
  • ある列の値は、ある文字列パターンと一致する(LIKE,GLOB,REGEXP句相当)

 これをcheck制約で表現すると以下のようになる。

BETWEEN句相当

 Age-1 < Age < 1000の範囲内であること。

create table Names(
  Age int
  constraint IsValidName check(-1 < Age and Age < 1000)
);

 残念ながら、check制約は変数をサポートしていない。よって範囲値はリテラル値でしか指定できない。

.parameter set @min -1
.parameter set @max 1000
create table Names(
  Age int
  constraint IsValidName check(@min < Age and Age < @max)
);
Error: parameters prohibited in CHECK constraints

IN句相当

 NameA,B,Cのいずれかであること。

create table Names(
  Name text,
  constraint IsValidName check(Name='A' or Name='B' or Name='C')
);

 異常値を挿入すると制約違反エラーになる。

insert into Names values('X');
Error: CHECK constraint failed: IsValidName

 IN句を使う。

create table Names(
  Name text,
  constraint IsValidName check(Name in ('A','B','C'))
);
insert into Names values('X');
Error: CHECK constraint failed: IsValidName

 残念ながら、check制約はサブクエリをサポートしていない。よって正常値リストはリテラル値でしか指定できない。

create table NameValidateValues(Name text primary key not null) without rowid;
insert into NameValidateValues values('A'),('B'),('C');
create table Names(
  Name text,
  constraint IsValidName check(Name in (select Name from NameValidateValues))
);
Error: subqueries prohibited in CHECK constraints

LIKE句相当

 NameABCを部分文字列として含んでいること。

create table Names(
  Name text,
  constraint IsValidName check(Name Like '%ABC%')
);
特殊文字 エスケープ 意味
% $% 任意の0文字以上の文字列
_ \_ 任意の1文字
  • 大文字と小文字を区別しない

GLOB句相当

 Nameは先頭に任意3文字_の後にa,b,cのいずれか1字であること。

create table Names(
  Name text,
  constraint IsValidName check(Name GLOB '???_[abc]')
);
insert into Names values('xxx_a'); /* OK */
insert into Names values('xxx_z'); /* NG */
特殊文字 エスケープ 意味
* `` 任意の0文字以上の文字列
? `` 任意の1文字
[abc] `` a,b,cのいずれかに一致
[a-d] `` adまでのいずれかに一致
[^abc] `` a,b,cのいずれにも一致しない

REGEXP句相当

 REGEXPはデフォルトで存在しない。ユーザ関数として拡張する必要がある。

sudo apt -y install libsqlite3-dev
git clone https://github.com/ralight/sqlite3-pcre
cd sqlite3-pcre
make
cp pcre.so ~/root/sys/env/tool/sqlite_ext
vim ~/.sqliterc

~/.sqliterc

.load /home/pi/root/sys/env/tool/sqlite_ext/pcre.so

 あとはsqlite3コマンドを起動すればいい。

sqlite3 :memory:

 Nameは先頭に任意3文字_の後にa,b,cのいずれか1字であること。

create table Names(
  Name text,
  constraint IsValidName check(Name REGEXP '[0-9]{4}-[0-9]{2}-[0-9]{2}')
);
insert into Names values('2000-01-01'); /* OK */
insert into Names values('xxx'); /* NG */
Error: CHECK constraint failed: IsValidName

制約は変更不可

 なお、SQLite3は制約を変更することができない。それに相当する構文が実装されていないため。

  • alter table add/drop constraint check(...)

実行結果

========== alter_table_drop_constraint.sql ==========
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite> create table Names(
   ...>   Name text,
   ...>   constraint IsValidName check(Name in ('A','B','C'))
   ...> );
sqlite> alter table Names drop constraint IsValidName;
Error: near "drop": syntax error
sqlite> alter table Names add constraint IsValidName check(Name in ('A','B','C','Z'));
Error: near "constraint": syntax error
sqlite> 
========== change_constraint_by_recreate.sql ==========
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite> -- check制約を変更する。alter構文による変更は未実装であるため、テーブル再作成にて実現する。
sqlite> create table Names(
   ...>   Name text,
   ...>   constraint IsValidName check(Name in ('A','B','C'))
   ...> );
sqlite> -- alter table Names drop constraint IsValidName;
sqlite> -- alter table Names add constraint IsValidName check(Name in ('A','B','C','Z'));
sqlite> insert or ignore into Names values('X'),('A'),('B'),('C'),('Z');
sqlite> select * from Names;
A
B
C
sqlite> select sql from sqlite_master where type='table' and name='Names';
CREATE TABLE Names(
  Name text,
  constraint IsValidName check(Name in ('A','B','C'))
)
sqlite> 
sqlite> alter table Names rename to OLD_Names;
sqlite> create table Names(
   ...>   Name text,
   ...>   constraint IsValidName check(Name in ('A','B','C','D'))
   ...> );
sqlite> insert into Names select * from OLD_Names;
sqlite> drop table OLD_Names;
sqlite> .tables
Names
sqlite> select * from Names;
A
B
C
sqlite> select sql from sqlite_master where type='table' and name='Names';
CREATE TABLE Names(
  Name text,
  constraint IsValidName check(Name in ('A','B','C','D'))
)
sqlite> 
========== pattern_glob.sql ==========
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite> create table Names(
   ...>   Name text,
   ...>   constraint IsValidName check(Name GLOB '???_[abc]')
   ...> );
sqlite> insert or ignore into Names values('xxx_a'),('xxx_z');
sqlite> select * from Names;
xxx_a
sqlite> 
========== pattern_like.sql ==========
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite> create table Names(
   ...>   Name text,
   ...>   constraint IsValidName check(Name Like '%ABC%')
   ...> );
sqlite> insert or ignore into Names values('A'),('ABC'),('xABCx'),('Z');
sqlite> select * from Names;
ABC
xABCx
sqlite> 
========== pattern_regexp.sql ==========
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite> create table Names(
   ...>   Name text,
   ...>   constraint IsValidName check(Name REGEXP '[0-9]{4}-[0-9]{2}-[0-9]{2}')
   ...> );
sqlite> insert or ignore into Names values('2000-01-01'),('xxx'); /* OK */
sqlite> select * from Names;
2000-01-01
sqlite> 
========== range_and.sql ==========
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite> create table Names(
   ...>   Age int
   ...>   constraint IsValidName check(-1 < Age and Age < 1000)
   ...> );
sqlite> insert or ignore into Names values(-1),(1000),(0),(999);
sqlite> select * from Names;
0
999
sqlite> 
========== range_and_var.sql ==========
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite> .parameter set @min -1
sqlite> .parameter set @max 1000
sqlite> create table Names(
   ...>   Age int
   ...>   constraint IsValidName check(@min < Age and Age < @max)
   ...> );
Error: parameters prohibited in CHECK constraints
sqlite> 
========== select_in.sql ==========
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite> create table Names(
   ...>   Name text,
   ...>   constraint IsValidName check(Name in ('A','B','C'))
   ...> );
sqlite> insert or ignore into Names values('X'),('A'),('B'),('C'),('Z');
sqlite> select * from Names;
A
B
C
sqlite> 
========== select_in_subquery.sql ==========
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite> create table NameValidateValues(Name text primary key not null) without rowid;
sqlite> insert into NameValidateValues values('A'),('B'),('C');
sqlite> create table Names(
   ...>   Name text,
   ...>   constraint IsValidName check(Name in (select Name from NameValidateValues))
   ...> );
Error: subqueries prohibited in CHECK constraints
sqlite> 
========== select_or.sql ==========
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite> create table Names(
   ...>   Name text,
   ...>   constraint IsValidName check(Name='A' or Name='B' or Name='C')
   ...> );
sqlite> insert or ignore into Names values('X'),('A'),('B'),('C'),('Z');
sqlite> select * from Names;
A
B
C
sqlite> 

所感

 次は正常値リストを変数にする方法を模索する。

対象環境

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