バリデート値を変数にできない。リテラル値で指定する必要がある。
成果物
情報源
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句相当
NameはA,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句相当
NameはABCを部分文字列として含んでいること。
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] |
`` | a〜dまでのいずれかに一致 |
[^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>
所感
次は正常値リストを変数にする方法を模索する。
対象環境
- Raspbierry pi 4 Model B
- Raspbian buster 10.0 2019-09-26 ※
- bash 5.0.3(1)-release
- SQLite 3.33.0 ※ 学習まとめ
$ uname -a Linux raspberrypi 4.19.97-v7l+ #1294 SMP Thu Jan 30 13:21:14 GMT 2020 armv7l GNU/Linux
