データベースsqlite3 その１０

sqliteデータベースにおいて、抑えておきたい機能について検証してみたいと思います。

ROWID

ROWID は INTEGER PRIMARY KEY の別名

デフォルトでは、SQLite のすべてのレコードには、通常「 rowid 」と呼ばれる特別な列があり、テーブル内のその行を一意に識別するのに使用されます。

---- test_rowidテーブルを定義する
sqlite> create table test_rowid(
   ...> id integer primary key, name text);
sqlite> select rowid from test_rowid;
  -- rowid is not created without data.

---- test_rowidテーブルにデータを入れます。
sqlite> insert into test_rowid values(
   ...> null, 'takai');

---- rowid列が生成されているかを確認する。
sqlite> select * from test_rowid;
id  name
--  -----
1   takai
sqlite> select rowid from test_rowid; -- rowid equal id (integer primary key).
id
--
1
sqlite> insert into test_rowid values(
   ...> null, 'takai2');
sqlite> select * from test_rowid;
id  name
--  ------
1   takai
2   takai2
sqlite> select rowid from test_rowid;
id
--
1
2

---- integer primary key列が無いテーブルを生成し、検証する。
sqlite> create table test_rowid2(
   ...> name text, age integer);
sqlite> insert into test_rowid2 values(
   ...> 'takai3', 20);
sqlite> select rowid from test_rowid2;
rowid
-----
1    -- integer primary key が無くても rowid が作られる。

WITHOUT ROWID でパフォーマンスを向上させる

デフォルトではテーブルを生成し、データを入れるたびごとに ROWID が作られ、その分、容量が増えてしまう。これを抑制する為に、CREATE TABLE 文の末尾に WITHOUT ROWID をつけることにより、これを抑制できる。

• SQLiteバージョン 3.8.2 (2013-12-06) 以降が対象である。
• primary key を持つテーブルにのみ適用される。この時、下記に示す integer primary key を持つテーブルでは、rowid のエイリアスにならない。
• AUTOINCREMENT は WITHOUT ROWID テーブルでは機能しません。
• sqlite3関数において、WITHOUT ROWID テーブルではrowidを返す関数は機能しません。
• without rowid を適用したテーブルの primary key列は not null 制約を持たなければならない。

---- without rowidを持つテーブルを定義する
sqlite> create table name_number(
   ...> name text, number integer)
   ...> without rowid;
Error: PRIMARY KEY missing on table name_number  -- primary keyが必要
sqlite> create table name_number(
   ...> name text, number integer primary key)
   ...> without rowid;
sqlite> select rowid from name_number;
Error: no such column: rowid  -- レコードが生成されていなければ、生成されない

---- name_numberテーブルにデータを入れる
sqlite> insert into name_number values(
   ...> 'takai', null);
Error: NOT NULL constraint failed: name_number.number -- primary key制約のついたnumber列はおのずとnot null制約になる
sqlite> insert into name_number values(
   ...> 'takai', 1);
sqlite> insert into name_number values(
   ...> 'takai2', 1);
Error: UNIQUE constraint failed: name_number.number -- primary制約をもつ列はuniqueである
sqlite> insert into name_number values(
   ...> 'takai2', 3);
sqlite> select * from name_number;
name    number
------  ------
takai   1
takai2  3
sqlite> select row_id from name_number;
Error: no such column: row_id  -- 確かのrow_id列は作られない

integer primary key に autoincrement 制約をつける利点

create table ステートメントにおいて、integer primary key 制約をつけるとデータを追加するたびにrowid の値が一つずつカウントアップする。これは、autoincrement 制約をつけたのと同様な動作です。

但し、データを削除した場合に、微妙に動作に違いがあります。値に null 指定の場合、一旦削除された key (rowid と等価)の利用が max に達した場合、increment 制約がある場合は、再利用できません。increment 制約が無い場合は、空きがある任意の値が利用されます。但し、null ではなく、削除した値そのものを指定した場合は、問題なく両者において利用できます。このことから、「integer primary key autoincrement」 制約指定により、削除された key(rowid と等価) を再利用不可にする制約として定義することが出来ます。

rowid が max になりそうも無い場合は、動作に変りがないので、increment 制約をつけなくても支障が在りません。

そのことを、sqlite3 コマンドプロンプトにおいて、以下のように確かめてみました。

「integer primary key not null」 ケース

---- 「integer primary key not null」を持つテーブルを定義
sqlite> create table int_prime(i integer primary key not null);
sqlite> insert into int_prime values(null);
sqlite> insert into int_prime values(null);
sqlite> insert into int_prime values(null);
sqlite> insert into int_prime values(a);
Error: no such column: a

sqlite> select max(rowid) from int_prime;
max(rowid)
----------
3  -- 有効であるレコード数イコール最後にあるrowidの値

---- 最後ではないレコードを削除してみる
sqlite> delete from int_prime where i = 2;
sqlite> select * from int_prime;
i
-
1
3
sqlite> select max(rowid) from int_prime;
max(rowid)
----------
3  -- 最後にあるrowidの値

---- 「integer primary key not null」列に最後にあるrowidの値より大きい値を挿入してみる
sqlite> insert into int_prime values(5);
sqlite> select max(rowid) from int_prime;
max(rowid)
----------
5  -- 最後にあるrowidの値が挿入した値になる
sqlite> select * from int_prime;
i
-
1
3
5


---- nullを挿入してみる
sqlite> insert into int_prime values(null);
sqlite> select * from int_prime;
i
-
1
3
5
6  -- max(row_id)+1

---- integer型格納最大値を挿入してみる
sqlite> insert into int_prime values(9223372036854775807);
sqlite> select max(rowid) from int_prime;
max(rowid)
-------------------
9223372036854775807  -- integer型格納最大値

sqlite> select max(rowid) from int_prime;
max(rowid)
-------------------
9223372036854775807    -- 最大値に達すると、max(rowid)は追加データにたいして、この数値でとどまる

---- max(rowid)がinteger型格納最大値に達している状態でnullを挿入してみる
sqlite> insert into int_prime values(null);
sqlite> select * from int_prime;
i
-------------------
1
3
5
6
4145712561510546069     -- 以降、最大値未満の使われていない任意の数値がセットされる
9223372036854775807

「integer primary key autoincrement not null 」ケース

---- 「integer primary key autoincrement not null」を持つテーブルを定義
sqlite> create table int_prime_incr(i integer primary key autoincrement not null);
sqlite> insert into int_prime_incr values(null);
sqlite> insert into int_prime_incr values(null);
sqlite> insert into int_prime_incr values(null);
sqlite> insert into int_prime_incr values(null);
sqlite> select max(rowid) from int_prime_incr;
max(rowid)
----------
4

---- 最後ではないレコードを削除してみる
sqlite> delete from int_prime_incr where i = 2;
sqlite> select * from int_prime_incr;
i
-
1
3
4
sqlite> select max(rowid) from int_prime_incr;
max(rowid)
----------
4


---- 「integer primary key not null」列に最後にあるrowidの値より大きい値を挿入してみる
sqlite> insert into int_prime_incr values(6);
sqlite> select max(rowid) from int_prime_incr;
max(rowid)
----------
6
sqlite> select * from int_prime_incr;
i
-
1
3
4
6  -- 最後にあるrowidの値が挿入した値になる


---- nullを挿入してみる
sqlite> insert into int_prime_incr values(null);
sqlite> select * from int_prime_incr;
i
-
1
3
4
6
7 -- max(row_id)+1

---- 削除されたkeyを指定して挿入する 
sqlite> insert into int_prime_incr values(5);
sqlite> select * from int_prime_incr;
i
-
1
3
4
5  -- 削除された key を指定すれば key を再利用できる
6
7

---- integer型格納最大値を挿入してみる
sqlite> insert into int_prime_incr values(9223372036854775807);
sqlite> select max(rowid) from int_prime_incr;
max(rowid)
-------------------
9223372036854775807   -- integer型格納最大値


---- max(rowid)がinteger型格納最大値に達している状態でnullを挿入してみる
sqlite> insert into int_prime_incr values(null);
Error: database or disk is full   -- autoincrement がつかない場合と動作が違う

日付情報の取得

sqlit3 において、日付情報を示すデータは以下に示す関数により、NUMERIC アフィニティとして受け取られます。出力されるデータ形式により、integer または text として格納処理されるので、他のリレーショナルデータベースのような 日付・時間型はありません。

時間を扱う関数

1. date(timestring, modifier, modifier, …) # YYYY-mm-dd形式
2. time(timestring, modifier, modifier, …) # HH:MM:SS形式
3. datetime(timestring, modifier, modifier, …) # YYYY-mm-dd HH:MM:SS
4. julianday(timestring, modifier, modifier, …) # ユリウス日紀元前4714年11月24日（予言グレゴリオ暦）のグリニッジ正午からの日数を返す
5. strftime(format, timestring, modifier, modifier, …) # 指定した日時を指定した書式で返す
6. unixepoch(time-value, modifier, modifier, …)

SQLiteで格納される日付時刻にはタイムゾーンの概念はなく、すべての日付時刻をUTCとして保持する。したがって、上記の関数を使用する場合は、modiferに時差を指定する必要があります。

修飾子 modifier

• NNN days
• NNN hours
• NNN minutes
• NNN.NNNN seconds
• NNN months
• NNN years
• start of month
• start of year
• start of day
• weekday N
• unixepoch
• julianday
• auto
• localtime
• utc

date() を使ったケース

sqlite> select typeof(date()), date(); -- 引き数なしで現在時刻 UTC を計算 text型を返す
text|2023-07-09
sqlite> select date('now');  --  引数 'now' でも現在時刻 UTC を計算できる
2023-07-09
sqlite> select date('now', '+9 hours');  -- UTCをJSTに補正した現在時刻
2023-07-09
sqlite> select date('2023-7-8', '+9 hours');  -- 年は4桁、月・日は2桁で指定する必要がある
   -- エラーは表示されない。

sqlite> select date('2023-07-08');
2023-07-08
sqlite> select date('2023-07-08', '+9 hours');
2023-07-08

sqlite> select date('now', 'localtime');  -- 'localtime'でもUTCをJSTに補正した現在時刻になる
2023-07-09
sqlite> select date('now', 'start of month', 'start of year', 'start of day'); -- 年月の初め
2023-01-01


sqlite> select date('now', 'start of month', '+7 month', 'start of year', 'start of day'); -- 現在時刻から７月後の時刻の年月の初め
2024-01-01
sqlite> select date('now', 'start of month', '+5 month', 'start of year', 'start of day');
2023-01-01
sqlite> select date('now', 'start of month', '+1 month', '-1 day');  -- 現在時刻の月末の時刻
2023-07-31

sqlite> select date('now', 'start of month', '+1 month', '-1 day', '+9 hours');  -- JSTに補正した現在時刻の月末の時刻
2023-07-31

datetime() を使ったケース

sqlite> select datetime();  -- 現在時刻を YYYY-MM-DD HH:MM:SS 形式でかえす
2023-07-09 08:30:59
sqlite> select typeof(datetime()), datetime();  -- text ストレージタイプで格納される
text|2023-07-09 08:32:38
sqlite> select datetime('now');  -- 現在時刻を utc で計算
2023-07-09 08:33:04
sqlite> select datetime('now', '+9 hours'); -- 上記を +9 時間補正し jstに
2023-07-09 17:33:41
sqlite> select datetime('now', 'localtime');  -- 現在時刻を jstに
2023-07-09 17:34:32
sqlite> select datetime('2022-06-03');  ①
2022-06-03 00:00:00
sqlite> .mode column  ②
sqlite> select datetime('now') as unixepoch,  ③
   ...> datetime('now', 'localtime') as localtime,
   ...> datetime(current_timestamp) as current_timestamp_utc,
   ...> datetime(current_timestamp, 'localtime') as current_timestamp_jst;
unixepoch            localtime            current_timestamp_utc  current_timestamp_jst
-------------------  -------------------  --------------------- ----------------------
2023-07-09 08:44:32  2023-07-09 17:44:32  2023-07-09 08:44:32          2023-07-09 17:44:32
sqlite> select datetime('now') as unixepoch,
   ...> datetime('now', 'localtime') as localtime,
   ...> datetime(current_timestamp) as utc_current,
   ...> datetime(current_timestamp, 'localtime') as jst_current;
unixepoch            localtime            utc_current          jst_current
-------------------  -------------------  -------------------  -------------------
2023-07-09 08:49:36  2023-07-09 17:49:36  2023-07-09 08:49:36  2023-07-09 17:49:36  
sqlite> SELECT datetime(1092941466, 'unixepoch'),  -- unixepochで数値を指定できる
   ...> datetime(1092941466, 'unixepoch', 'localtime');
datetime(1092941466, 'unixepoch')  datetime(1092941466, 'unixepoch', 'localtime')
---------------------------------  ----------------------------------------------
2004-08-19 18:51:06                2004-08-20 03:51:06

sqlite> select date('now', 'localtime') as date,
   ...> time('now', 'localtime') as time,
   ...> datetime('now', 'localtime') as datetime;
date        time      datetime
----------  --------  -------------------
2023-07-10  11:48:40  2023-07-10 11:48:40

1. 任意の日付を指定する場合は、上記時間を扱う関数に合わせて指定する必要があります。
2. .mode column で２次元表形式で画面表示にかえる。
3. unixepoch は世界標準時を1970年1月1日0時0分0秒からの経過秒を数値で表したものです。

julianday() を使ったケース

sqlite> select typeof(julianday('now')), julianday('now');
typeof(julianday('now'))  julianday('now')
------------------------  ----------------
real                      2460135.68683911

strftime() を使ったケース

フォーマット

%d		day of month: 00
%f		fractional seconds: SS.SSS
%H		hour: 00-24
%j		day of year: 001-366
%J		Julian day number (fractional)
%m		month: 01-12
%M		minute: 00-59
%s		seconds since 1970-01-01
%S		seconds: 00-59
%w		day of week 0-6 with Sunday==0
%W		week of year: 00-53
%Y		year: 0000-9999
%%		%

sqlite> select strftime('%Y', 'now', 'localtime') as year,
   ...> typeof(strftime('%Y', 'now', 'localtime')) as type,
   ...> strftime('%m', 'now', 'localtime') as month,
   ...> typeof(strftime('%m', 'now', 'localtime')) as type,
   ...> strftime('%d', 'now', 'localtime') as day,
   ...> typeof(strftime('%d', 'now', 'localtime')) as type;
year  type  month  type  day  type
----  ----  -----  ----  ---  ----
2023  text  07     text  10   text

sqlite> select strftime('%H', 'now', 'localtime') as hour,
   ...> typeof(strftime('%H', 'now', 'localtime')) as type,
   ...> strftime('%M', 'now', 'localtime') as minute,
   ...> typeof(strftime('%M', 'now', 'localtime')) as type,
   ...> strftime('%S', 'now', 'localtime') as second,
   ...> typeof(strftime('%S', 'now', 'localtime')) as type;
hour  type  minute  type  second  type
----  ----  ------  ----  ------  ----
14    text  00      text  08      text
sqlite> select strftime('%s', 'now', 'localtime') as epoch_seconds,
   ...> typeof(strftime('%s', 'now', 'localtime')) as type;
epoch_seconds  type
-------------  ----
1689002653     text

sqlite> select strftime('%w', 'now', 'localtime') as day_of_week,
   ...> typeof(strftime('%w', 'now', 'localtime')) as type;
day_of_week  type
-----------  ----
1            text

すべて、text ストレージタイプで格納される。

日時の比較

sqlite> select date('now');
date('now') -- 現在の日付
-----------
2023-08-02

sqlite> select (select date('now')) = (select date('2023-08-03'));
(select date('now')) = (select date('2023-08-03')) -- 翌日との比較
--------------------------------------------------
0
sqlite> select (select date('now')) = (select date('2023-08-02'));
(select date('now')) = (select date('2023-08-02')) -- 当日との比較
--------------------------------------------------
1

sqlite> select typeof((select date('now'))), typeof((select date('2023-08-02')));
typeof((select date('now')))  typeof((select date('2023-08-02')))
----------------------------  -----------------------------------
text                          text   -- 文字列同士である

 sqlite> select (select date('now')) - (select date('2023-08-03'));
(select date('now')) - (select date('2023-08-03'))
---------------------------------------------- 
0        -- 計算は出来ない

 sqlite> select (select date('now')) < (select date('2023-08-03'));
(select date('now')) < (select date('2023-08-03'))
--------------------------------------------------
1      -- 比較は出来る

sqlite> select unixepoch();
Error: no such function: unixepoch  -- unixepoch() は使えないようである

sqlite> select strftime('%s'), typeof((select strftime('%s')));
strftime('%s')  typeof((select strftime('%s')))
--------------  -------------------------------
1690968742      text  -- unixepoch   -- 文字列である

sqlite> select strftime('%s', datetime('now')), strftime('%s');
strftime('%s', datetime('now'))  strftime('%s')  -- 同一
-------------------------------  --------------
1690969835                       1690969835

sqlite> select strftime('%s'), strftime('%s')+10, typeof(strftime('%s')+10);
strftime('%s')  strftime('%s')+10  typeof(strftime('%s')+10)
--------------  -----------------  -------------------------
1691035323      1691035333         integer  -- 数値として解釈される

sqlite> select strftime('%s', date('now')), strftime('%s', datetime('now'));
strftime('%s', date('now'))  strftime('%s', datetime('now'))
---------------------------  -------------------------------
1691020800                   1691036753
sqlite> select datetime(1691020800, 'unixepoch');
datetime(1691020800, 'unixepoch')
---------------------------------
2023-08-03 00:00:00  -- date('year-month-day')は year-month-day 00:00:00と解釈される

/* 現在時刻が０時から何秒経過したかを計算する */
sqlite> select ((select strftime('%s', datetime('now'))) - (select strftime('%s', date('now'))));
((select strftime('%s', datetime('now'))) - (select strftime('%s', date('now'))))
---------------------------------------------------------------------------------
17236

/* 2000年1月1日からの現在時刻までの経過秒数 */
sqlite> select ((select strftime('%s', datetime('now'))) - (select strftime('%s', date('2000-01-01'))));
((select strftime('%s', datetime('now'))) - (select strftime('%s', date('2000-01-01'))))
----------------------------------------------------------------------------------------
744437631

トリガー文の実行を中断させる[raise][select case when][on conflict]

RAISE();  トリガー文の実行を中断
RAISE(ROLLBACK/ABORT/FAIL, message);  
  指定されたON CONFLICT処理が実行され、メッセージ文が出力され、現在のクエリが終了します。
RAISE(IGNORE);
  実行しているトリガーが破棄されます。トリガー処理を除いたプログラムは継続されます。

CASE WHEN ELSE を使った条件式の構造
SELECT
  CASE
    WHEN condition THEN expression
    WHEN condition THEN expression
    ELSE expression
  END
FROM table name;

ON CONFLICT処理 制約違反が発生した場合の競合解決方式を指定
ROLLBACK
  実行しているトランザクションをロールバックする。
ABORT
  デフォルトの処理。実行しているsql文は実行前に戻りますが、その前に実行されたsql文の結果は破棄されません。
FAIL 
  実行しているsql文は中断されます。その前に実行されたsql文の結果は破棄されません。UNIQE、NOT NULL、CHECKに対する制約違反にのみ有効。外部キー制約違反の場合は、ABORT として処理されます。

---- a>0 かつ a<10の条件で任意の３つの数字が代入されるテーブルnumをつくる
sqlite> create table num(a integer check(a>0 and a<10));
sqlite> create table num_cnt(cnt integer);
sqlite> insert into num_cnt values(0);

sqlite> create trigger cntup_and_chkmax insert on num
   ...> begin
   ...> update num_cnt set cnt=(select cnt from num_cnt where rowid=1)+1 where rowid=1;  -- カウントアップ処理
   ...> select case when (select cnt from num_cnt where rowid=1) > 3 then raise(rollback, 'input data count over 3!') end; -- カウントアップ終了処理
   ...> end;

sqlite> insert into num values(2);
sqlite> insert into num values(10);
Error: CHECK constraint failed: a>0 and a<10  -- checkによる
sqlite> insert into num values(9);
sqlite> insert into num values(4);
sqlite> insert into num values(5);
Error: input data count over 3!  -- raise()による

sqlite> select * from num;
a
-
2
9
4
sqlite> select * from num_cnt;
cnt
---
3

sqlite は日付情報をサニダイズして使用する必要がある

sqlite において、日時データを yyyy-mm-dd で格納される事（julianday unixepoch を除く）を前提としております。ストレージタイプでは text 格納なのですが、フォーマットされた状態で格納する必要があります。このことをチェックする仕組みは sqlite では用意されていないので、自力で作らなければいけないようです。

日付情報テーブルを定義し、正しく格納する為の確認作業をする

sqlite> create table date_sample (mydate text); -- text 型で定義

sqlite> insert into date_sample values('takai'); 

sqlite> select * from date_sample;
takai  -- そのままでは文字列すべてが格納できてしまう

sqlite> insert into date_sample values(date('takai')); -- 日付変換関数を使用して格納

sqlite> .mode column  -- 列形式表示に

sqlite> select mydate, typeof(mydate) as type from date_sample;
mydate  type
------  ----
takai   text
        null  -- date() 関数を使えば、変換できなければ null で格納されてしまう

日付情報は text として格納されてしまう為、数字以外の文字列もすべて格納できてしまう。date() 関数を使用すると、0000-00-00 形式以外の文字列は null として格納される。

date() 関数の挙動について確認してみる

sqlite> select date('abcd-ef-gh'), typeof(date('abcd-ef-gh')) as type;
date('abcd-ef-gh')  type
------------------  ----
                    null  -- 文字を挿入すると nullとして格納される


sqlite> select date('1900-1-01'), typeof(date('1900-1-01')) as type;
date('1900-1-01')  type
-----------------  ----
                   null  -- 0000-00-00 正しく数字がセットされないと null が格納される

sqlite> select date('0000-01-01'), typeof(date('0000-01-01')) as type;
date('0000-01-01')  type
------------------  ----
0000-01-01          text
sqlite> select date('9999-01-01'), typeof(date('9999-01-01')) as type;
date('9999-01-01')  type
------------------  ----
9999-01-01          text  -- year は 0000年から 9999年まで格納可能である


sqlite> select date('0000-00-01'), typeof(date('0000-00-01')) as type;
date('0000-00-01')  type
------------------  ----
                    null
sqlite> select date('0000-01-01'), typeof(date('0000-01-01')) as type;
date('0000-01-01')  type
------------------  ----
0000-01-01          text
sqlite> select date('0000-12-01'), typeof(date('0000-12-01')) as type;
date('0000-12-01')  type
------------------  ----
0000-12-01          text
sqlite> select date('0000-13-01'), typeof(date('0000-13-01')) as type;
date('0000-13-01')  type
------------------  ----
                    null  -- mpnth は 01月から 12 まで格納でき、それ以外は null が格納される

 
sqlite> select date('0000-01-00'), typeof(date('0000-01-00')) as type;
date('0000-01-00')  type
------------------  ----
                    null
sqlite> select date('0000-01-01'), typeof(date('0000-01-01')) as type;
date('0000-01-01')  type
------------------  ----
0000-01-01          text
sqlite> select date('0000-01-31'), typeof(date('0000-01-31')) as type;
date('0000-01-31')  type
------------------  ----
0000-01-31          text
sqlite> select date('0000-01-32'), typeof(date('0000-01-32')) as type;
date('0000-01-32')  type
------------------  ----
                    null  -- day は 01日から 31日まで、それ以外は null となる

sqlite> select date('0000-02-31'), typeof(date('0000-02-31')) as type;
date('0000-02-31')  type
------------------  ----
0000-02-31          text  -- ２月３１日はありえない日付
sqlite> select date('0000-02-30'), typeof(date('0000-02-30')) as type;
date('0000-02-30')  type
------------------  ----
0000-02-30          text  -- ２月３０日はありえない日付
sqlite> select date('0000-04-31'), typeof(date('0000-04-31')) as type;
date('0000-04-31')  type
------------------  ----
0000-04-31          text  -- ４月３１日はありえない日付
 

date() 関数を使えば、挿入データに対して、ある程度はサニダイズできる。但し、日にちは 01~31、月は 01~12 の範囲に関しての有効定義であり、2、4、6、9、11月末日の考慮はされていない。うるう年も考慮に入れていない。

日付入力をチェックする仕組みを考える

データの入力処理では、日付をチェックする必要があります。各種プログラミング言語において、サニダイズコードを作成することも考えられます。ここでは、あくまでも、端末上で、以下のような処理を考えることにします。

• date() 関数を使って入力データが正しくフォーマットされているかチェックする。
• 入力データが正しくセットされる為に、トリガー処理を使い、不正データは格納しない。
• トリガー処理において、月末日とうるう年のチェックを行う。

尚、うるう年とは、通常、２月末日が２８日であるのに対し、２９日である年のことであります。うるう年を考慮したサニダイズトリガー処理を以下のように考えてみました。

1. date() 関数で入力データがnullを返す場合は、差し戻す。
2. strftime 関数を使って、日を示す数字が 31 であれば、
3. 月を示す数字が 02 または 04 または 06 または 09 または 11であれば差し戻す。
4. 日を示す数字が 30 であれば、
5. 月を示す数字が 02 であれば差し戻す。
6. 日を示す数字が 29 の場合は、
7. 月を示す数字が 02 であれば、
8. 年を示す数字が 4で割り切れなければ、差し戻す。
9. 年を示す数字が 100 で割り切れ、400 で割り切れなければ、差し戻す。

CASE WHEN ELSE を使った条件式の構造
SELECT
  CASE
    WHEN condition THEN expression
    WHEN condition THEN expression
    ELSE expression
  END
FROM table name;

年月日格納サニダイズをするトリガーコード

/* insert on date_sample */
---- date_sampleテーブルにinsert処理されたあとのトリガー
sqlite> create trigger tri_datechk after insert on date_sample
   ...> begin
   ...>   select
   ...>   case
   ...>     when (select date(new.mydate)) is null
   ...>       then raise(rollback, 'mydate date() return null !')
   ...>     when (select ((strftime('%d', new.mydate))='31')) then
   ...>       case when (select ((strftime('%m', new.mydate)) in ('02', '04', '06', '09', '11')))
   ...>         then raise(rollback, 'mydate undefined day=31 set !')
   ...>       end
   ...>     when (select ((strftime('%d', new.mydate))='30')) then
   ...>       case when (select ((strftime('%m', new.mydate))) = '02')
   ...>         then raise(rollback, 'mydate undefined day=30 !')
   ...>       end
   ...>     when (select ((strftime('%d', new.mydate))='29')) then
   ...>       case when (select ((strftime('%m', new.mydate))) = '02') then
   ...>         case when ((select ((strftime('%Y', new.mydate)))%4) != 0)
   ...>           then raise(rollback, 'mydate format 02-29 unleap day !')
   ...>         when ((select ((strftime('%Y', new.mydate)))%100) = 0 and (select ((strftime('%Y', new.mydate)))%400) != 0)
   ...>           then raise(rollback, 'mydate format 02-29 unleap special day !')
   ...>         end
   ...>       end
   ...>     end;
   ...>   end;

/* update of mydate on date_sample */

---- date_sampleテーブルにupdate処理されたあとのトリガー
sqlite> create trigger tri_datechk_u after update of mydate on date_sample
   ...> begin
   ...> select
   ...>   case
   ...>     when (select date(new.mydate)) is null
   ...>       then raise(rollback, 'mydate date() return null 1 !')
   ...>     when (select ((strftime('%d', new.mydate))='31')) then
   ...>       case when (select ((strftime('%m', new.mydate)) in ('02', '04', '06', '09', '11')))
   ...>         then raise(rollback, 'mydate undefined day=31 set !')
   ...>       end
   ...>     when (select ((strftime('%d', new.mydate))='30')) then
   ...>       case when (select ((strftime('%m', new.mydate))) = '02')
   ...>         then raise(rollback, 'mydate undefined day=20 !')
   ...>       end
   ...>     when (select ((strftime('%d', new.mydate))='29')) then
   ...>       case when (select ((strftime('%m', new.mydate))) = '02') then
   ...>         case when ((select ((strftime('%Y', new.mydate)))%4) != 0)
   ...>           then raise(rollback, 'mydate format 02-29 unleap day !')
   ...>         when ((select ((strftime('%Y', new.mydate)))%100) = 0 and (select ((strftime('%Y', new.mydate)))%400) != 0)
   ...>           then raise(rollback, 'mydate format 02-29 unleap special day !')
   ...>         end
   ...>       end
   ...>     end;
   ...>   end;

トリガー処理を検証してみる

sqlite> select * from date_sample;
takai

---- insertトリガーの検証
sqlite> insert into date_sample values('1900-1-1');  -- 不正
Error: mydate date() return null !

sqlite> insert into date_sample values('1900-01-01'); -- 正しい

sqlite> insert into date_sample values('1900-04-31');  -- ありえない
Error: mydate undefined day=31 set !

sqlite> insert into date_sample values('1900-04-30');  -- 正しい
sqlite> insert into date_sample values('1900-02-30'); -- ありえない
Error: mydate undefined day=30 !
sqlite> insert into date_sample values('1900-02-29'); -- ありえない
Error: mydate format 02-29 unleap special day !
sqlite> insert into date_sample values('1904-02-29'); -- 正しい
sqlite> insert into date_sample values('1905-02-29'); -- ありえない
Error: mydate format 02-29 unleap day !
sqlite> select * from date_sample;
takai

1900-01-01
1900-04-30
1904-02-29
sqlite> .mode column
sqlite> select * from date_sample;
mydate
----------
takai

1900-01-01
1900-04-30
1904-02-29

sqlite> select rowid, mydate from date_sample;
rowid  mydate
-----  ----------
1      takai
2
3      1900-01-01
4      1900-04-30
5      1904-02-29

---- updateトリガー検証
sqlite> update date_sample set mydate='1900-1-01' where rowid=3;
Error: mydate date() return null 1 !
sqlite> update date_sample set mydate='1900-01-02' where rowid=3;

sqlite> update date_sample set mydate='1900-04-31' where rowid=4;
Error: mydate undefined day=31 set !
sqlite> update date_sample set mydate='1900-06-30' where rowid=4;

sqlite> update date_sample set mydate='1900-02-29' where rowid=5;
Error: mydate format 02-29 unleap special day !
sqlite> update date_sample set mydate='1908-02-29' where rowid=5;
sqlite> update date_sample set mydate='1909-02-29' where rowid=5;
Error: mydate format 02-29 unleap day !

sqlite> select * from date_sample;
mydate
----------
takai

1900-01-02
1900-06-30
1908-02-29

sqlite_master テーブル

sqlite_masterテーブルは、データベースのスキーマ情報を一括して管理する特殊なテーブルです。データベースにテーブルが生成されると、作られます。

よって、sqlite_を接頭辞にするテーブル名は、予約されているので生成できずエラーになる。

テーブル情報を表示してみる

takai@takai-pc ~/db
$ sqlite3 trigger_sample
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.

---- レコードが存在しているのでsqlite_masterテーブルが作られている
sqlite> .schema sqlite_master
CREATE TABLE sqlite_master (
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

---- sqlite_masterテーブルのレコードを表示する
sqlite> .tables
my_contents         my_contents_detail  my_junle
sqlite> .mode list

sqlite> select * from sqlite_master;
table|my_contents|my_contents|2|CREATE TABLE my_contents(
  id integer primary key,
  title text,
  junle integer,
  page_num integer
)
table|my_junle|my_junle|3|CREATE TABLE my_junle(
  junle_id integer primary key,
  junle_name text not null,
  junle_count integer not null default 0
)
trigger|cntup_junle|my_contents|0|CREATE TRIGGER cntup_junle after insert on my_contents
begin
  update my_junle set junle_count = junle_count+1 where junle_id = new.junle;
end
trigger|cntdown_junle|my_contents|0|CREATE TRIGGER cntdown_junle after delete on my_contents
begin
  update my_junle set junle_count = junle_count-1 where junle_id = old.junle;
end
trigger|cntdownup_junle|my_contents|0|CREATE TRIGGER cntdownup_junle after update of junle on my_contents
begin
  update my_junle set junle_count = junle_count+1 where junle_id=new.junle;
  update my_junle set junle_count = junle_count-1 where junle_id=old.junle;
end
view|my_contents_detail|my_contents_detail|0|CREATE VIEW my_contents_detail as select title, junle_name, page_num from my_contents
inner join my_junle
on junle=junle_id
trigger|chg_title|my_contents_detail|0|CREATE TRIGGER chg_title instead of update on my_contents_detail
begin
  update my_contents set title=new.title where title=old.title;
end

sqlite_masterテーブルはsqlite_schemaテーブルのエイリアスです。

sqlite_masterテーブルのフィールド

type

table index view trigger のいずれかのテキスト文字

name

生成されたtable index view triggerの名前

tbl_name

nameフィールドに関連付けられたtable index view triggerの名前

rootpage

B木アルゴリズムで格納されたsqlite_schemaテーブル情報のページ番号を示しています。ルートは0またはnullとなります。

sql

sqlステートメントのテキスト表示

利用サンプル

takai@takai-pc ~/db
$ sqlite3 trigger_sample
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> .tables
my_contents         my_contents_detail  my_junle

---- 生成されているtableを表示する
sqlite> select name from sqlite_master where type='table';
my_contents
my_junle

---- 生成されているtriggerを表示する
sqlite> select name from sqlite_master where type='trigger';
cntup_junle
cntdown_junle
cntdownup_junle
chg_title

---- 生成されているviewを表示する
sqlite> select name from sqlite_master where type='view';
my_contents_detail