データベースsqlite3 その９

sql文の実行は、指定したテーブル以外にその実行が引き金になって、別のsql文を実行するような仕組みを備えております。たとえば、あるレコードが挿入されたときに、そのレコード数の定義されているテーブルの列がカウントアップするsql文が発行されるような場合が、これにあたります。

外部キー制約においても、このような関連のあるテーブルの列に作用するような仕組みがありますが、上記のようなsql文実行によるものではない点で異なる仕組みといえます。

トリガーアクションとは、sql文によって指定されたデータベースイベントが発生したときに自動的に実行されるデータベース操作ということです。

SQLITE3 トリガー構文

CREATE TRIGGER trigger_name [BEFORE|AFTER] INSERT ON table name  or
CREATE TRIGGER trigger_name [BEFORE|AFTER] DELETE ON table name  or
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column name ON table name
BEGIN
 -- Trigger logical sentences.
END;
/*
  テーブルに対する処理が行なわれるWHERE句直前と直後の値は、「OLD.カラム名」「NEW.カラム名」で取得できます。
insert trigger の場合 new.列名
update trigger の場合 old.列名 new.列名
delete trigger の場合 old.列名
　として利用できます。
DROP TRIGGER trigger_name;  トリガー解除
  WHEN 句が指定されている場合、指定された SQL ステートメントは、WHEN 句が真である行に対してのみ実行されます。WHEN 句が指定されていない場合、SQL ステートメントはすべての行に対して実行されます。
  変更するテーブルは、トリガーが関連付けられているテーブルまたはビューと同じデータベースに存在する必要があり
*/

CREATE TRIGGER trigger_name [BEFORE|AFTER] INSTEAD OF column name ON view table name
BEGIN
 -- Trigger logical sentences.
END;
読み取り専用ビューからトリガーを発行し、トリガー文においてテーブル列に変更を加える構文

SQLITE3トリガーアクションについて

以下のような事項に留意する必要があります。

トリガーは、 DELETE、UPDATE、INSERTのいずれかの操作に対して起動することを指定する必要があります。
SQL文を実行する前　（BEFORE）、SQL文を実行する後（AFTER）のタイミングを指定します。
TEMP 以外のトリガーの場合、変更またはクエリを実行するテーブルは、トリガーが接続されているテーブルまたはビューと同じデータベース内に存在する必要があります。
WHEN 句が指定されている場合、指定された SQL ステートメントは、WHEN 句が真である行に対してのみ実行されます。WHEN 句が指定されていない場合、SQL ステートメントはすべての行に対して実行されます。
関連付けられているテーブルが削除されると、トリガーは自動的に削除されます。

トリガー構文チュートリアル

ターミナルウィンドウを開き、ドットコマンドを使ってトリガー処理を確認してみます。

題材

自分が作った記事をテーブルmy_contentsに落とし、管理します。記事には、タイトルとページ数とジャンルを書き込みます。それとは別に、ジャンルを管理するテーブルmy_junleを作ります。ジャンル名とカウント数を管理します。それぞれ、id junle_idというプライマリーキーをもち、my_contentsのjunle列はmy_junleのjunle_idを参照しています。但し、今回は外部キー制約は使っていません。my_junleのカウント数をトリガーにより書き込み処理します。

テーブル生成

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

sqlite> create table my_contents(
   ...>   id integer primary key,
   ...>   title text,
   ...>   junle integer,
   ...>   page_num integer
   ...> );
sqlite> create table my_junle(
   ...>   junle_id integer primary key,
   ...>   junle_name text not null,
   ...>   junle_count integer default 0
   ...> );
sqlite> .tables
my_contents  my_junle

my_junleテーブルにデータを入れる

sqlite> insert into my_junle(junle_name)
   ...>   values('science');
sqlite> insert into my_junle(junle_name)
   ...>   values('essay');
sqlite> insert into my_junle(junle_name)
   ...>   values('poem');

sqlite> .mode column
sqlite> select * from my_junle;
junle_id  junle_name  junle_count
--------  ----------  -----------
1         programing  0
2         science     0
3         essay       0
4         poem        0

insertトリガー生成

---- my_contentsテーブルにレコードを追加したら、my_junleテーブルのjunle_count列データをカウントアップする
sqlite> 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;


---- my_contentsテーブルにレコードを追加する
sqlite> insert into my_contents(title, junle, page_num) values('java', 1, 3);
sqlite> insert into my_contents(title, junle, page_num) values('c++', 1, 5);
sqlite> insert into my_contents(title, junle, page_num) values('my_life', 3, 10);
sqlite> insert into my_contents(title, junle, page_num) values('bird_wondering', 2, 15);

---- トリガーアクションの確認
sqlite> select * from my_junle;
junle_id  junle_name  junle_count
--------  ----------  -----------
1         programing  2     -- count up
2         science     1     -- count up
3         essay       1     -- count up
4         poem        0
sqlite> select * from my_contents;
id  title           junle  page_num
--  --------------  -----  --------
1   java            1      3
2   c++             1      5
3   my_life         3      10
4   bird_wondering  2      15

deleteトリガー生成

---- my_contentsテーブルにレコードを削除したら、my_junleテーブルのjunle_count列データをカウントダウンする
sqlite> 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;

---- my_contentsテーブル id=3のレコードを削除する
sqlite> delete from my_contents where id=3;
sqlite> select * from my_junle;
junle_id  junle_name  junle_count
--------  ----------  -----------
1         programing  2
2         science     1
3         essay       0      -- 1->0にカウントダウン
4         poem        0

updateトリガー

---- my_contentsテーブルにレコードをのジャンルを変更したら、my_junleテーブルの変更前old と変更後new レコードのjunle_count列データをそれぞれカウントアップ、カウントダウンする
sqlite> 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;

---- my_contents　title='bird_wondering'レコード
のjunle列を変更する
sqlite> update my_contents set junle=3 where title='bird_wondering';
sqlite> select * from my_contents;
id  title           junle  page_num
--  --------------  -----  --------
1   java            1      3
2   c++             1      5
4   bird_wondering  3      15
sqlite> select * from my_junle;
junle_id  junle_name  junle_count
--------  ----------  -----------
1         programing  2
2         science     0   -- 1 -> 0
3         essay       1   -- 0 -> 1
4         poem        0

instead トリガー

---- my_contentsとmy_junleテーブルを内部結合したviewを作成する
sqlite> create view my_contents_detail as select title, junle_name, page_num from my_contents
   ...> inner join my_junle
   ...> on junle=junle_id;
sqlite> select * from my_contents_detail;
title           junle_name  page_num
--------------  ----------  --------
java            programing  3
c++             programing  5
bird_wondering  essay       15

---- 読み取り専用である、view_my_contentsからinsted of構文を使って、titleのupdateを可能にする
トリガー生成
sqlite> create trigger chg_title instead of update on view_my_contents
   ...> begin
   ...>   update my_contents set title=new.title where title=old.title;
   ...> end;


---- トリガーアクションを確認してみる
sqlite> update view_my_contents set title='birds_wondering' where title='bird_wondering';
sqlite> select * from view_my_contents;
title            junle_name  page_num
---------------  ----------  --------
java             programing  3
c++              programing  5
birds_wondering  essay       15  -- updated
sqlite> select * from my_contents;
id  title            junle  page_num
--  ---------------  -----  --------
1   java             1      3
2   c++              1      5
4   birds_wondering  3      15  -- updated

セルフテスト

sqlite3にはsqlステートメントをテストする機能が備わっております。アプリケーション側でも各種のテストが行われますが、端末でテストすることがある場合は、有用になるのではないかと思います。

ここでは、ドットコマンドを用いて、作成したトリガー文をテストしてみます。

.selftest ?OPTIONS?      Run tests defined in the SELFTEST table
    Options:
       --init               Create a new SELFTEST table
       -v                   Verbose output

.testcase NAME           Begin redirecting output to 'testcase-out.txt'

selftest テーブル生成

$ sqlite3 trigger_sample

---- セルフテストテーブルの生成
sqlite> .selftest -v --init
110: run SELECT hex(sha3_query('SELECT type,name,tbl_name,sql FROM sqlite_schema ORDER BY 2',224)) 　-- ①
Result: 8460D9BA1A7F9221AD818D416647E115AAAE54609472112FE0A02CD0
120: run SELECT hex(sha3_query('SELECT * FROM "my_contents" NOT INDEXED',224)) -- ②
Result: AA134083930541315F9AA9C25D55B6F981150A1A8FADE88CECCAB913
130: run SELECT hex(sha3_query('SELECT * FROM "my_junle" NOT INDEXED',224)) -- ②
Result: 40BCC884F0B00680DD378D34ED4982C1DE68C6E881F8C241079CCF46
140: run PRAGMA integrity_check  -- ③
Result: ok
0 errors out of 4 tests

initコマンドオプションでselftestテーブルが生成されます。sqlステートメントの検証は、このテーブルにテストコードを挿入することで実現できます。但し、以下の事がテストコードの定義をしなくても、実行されます。

sqlite_schema テーブルのコンテンツに対してのSHA3ハッシュ値が生成される。①
すべてのテーブルのコンテンツのハッシュ値が生成される。 ②
データベースの低レベルのフォーマットと整合性チェックが行われます。sql構文として正しくなければ、エラーの個数が返されます。エラーが見つからなかった場合は、値「ok」を持つ 1 つの行が返されます。③

ハッシュ値をエントリーすることにより、このセルフテスト対象データベースのテーブルの内容に変更が無いことを保証するのに使われます。ここでは、ハッシュ値の比較を行いませんが、次回以降にセルフテストを行うようなことがあれば、利用するのも良いでしょう。

selftestテーブルの構成

sqlite> .schema selftest
CREATE TABLE selftest(
  tno INTEGER PRIMARY KEY,  -- テスト番号
  op TEXT,  -- オペレーションタイプ 'memo' または 'run'を指定
  cmd TEXT,  -- 'memo'の場合は出力するコメント 'run'では実行するsql文
  ans TEXT  -- 'run'の場合は上記sql文の実行結果の期待値
);

テスト結果の出力

selftestコマンドで、selftestテーブルに挿入されたテスト結果が出力されます。.testcaseは、testcase-out.txtというファイル名でカレントフォルダーにファイル出力するコマンドです。

---- selftestテーブルのレコードをクリアーにする。
sqlite> delete from selftest;

---- 以降、テスト結果がファイル出力されます。
sqlite> .testcase trigger_case1  -- 引数が無くても良い
sqlite> .shell ls -la testcase-out.txt
-rw-r--r-- 1 takai None 0  5月  3 20:29 testcase-out.txt

ターゲットとなるテーブルの内容を出力する。

---- [target tables]というメモ書きを出力する。
sqlite> insert into selftest(tno, op, cmd) values(150, 'memo', '[target tables]');
sqlite> .selftest

---- my_contents、my_junleテーブルのレコードを出力する
sqlite> .mode column
sqlite> select * from my_contents;
sqlite> select * from my_junle;

---- 出力されたファイルの内容を表示する
takai@takai-pc ~/db
$ more testcase-out.txt
[target tables]    -- メモ書きの出力
0 errors out of 0 tests  -- テストは行われていない
id  title            junle  page_num
--  ---------------  -----  --------
1   java             1      3
2   c++              1      5
4   birds_wondering  3      15
junle_id  junle_name  junle_count
--------  ----------  -----------
1         programing  2
2         science     0
3         essay       1
4         poem        0

テスト内容を挿入する

selftesttテーブルのop句が’memo’の場合は、以下のフォーマットで出力することにします。

[test$numbers]$test_name => Excepted$tno; $Expected
 $numbersはターゲットテストのテスト番号(tno) 複数ある場合はtno1-tnoN
 $test_nameはテストの名称
 $tnoはテスト番号
 SExpectedは出力結果と比較する為の期待値

---- my_junleテーブルのid=4レコードのjunle_count句の値が0になっているか
sqlite3> delete from selftest;
sqlite> .mode line
sqlite> insert into selftest(tno, op, cmd) values(200, 'memo', '[test201]select count of my_junle table => Expected201: 0');
sqlite> insert into selftest values(null, 'run', 'select junle_count from my_junle where junle_id=4;', '0');
sqlite> .selftest
sqlite> select * from selftest;

---- 出力ファイルの内容確認
takai@takai-pc ~/db
$ more testcase-out.txt  -- ファイル出力が追記されている
[target tables]
0 errors out of 0 tests
id  title            junle  page_num
--  ---------------  -----  --------
1   java             1      3
2   c++              1      5
4   birds_wondering  3      15
junle_id  junle_name  junle_count
--------  ----------  -----------
1         programing  2
2         science     0
3         essay       1
4         poem        0
[test201]select count of my_junle table => Expected201: 0  -- テスト番号200の表示内容
0 errors out of 1 tests  -- テスト番号201の表示内容
  tno = 200   -- 以下 selftestテーブルのレコードが出力されている
   op = memo
  cmd = [test201]select count of my_junle table => Expected201: 0
  ans =

  tno = 201
   op = run
  cmd = select junle_count from my_junle where junle_id=4;
  ans = 0


---- my_contentsのjunle=4のレコードが追加されると、cntup_junle after insert on my_contentsトリガー処理がおこなわれ、my_junleテーブルのjunle_id=4のレコードのjunle_count句の値が0から1にカウントアップする
sqlite> delete from selftest;
sqlite> insert into selftest(tno, op, cmd) values(300, 'memo', '[test301]cntup_junle after insert on my_contents => Expected301 => 1');

sqlite> insert into selftest(op, cmd, ans) values(
   ...>   'run',
   ...>   'insert into my_contents values(null, ''test1'', 4, 10);
   ...>    select junle_count from my_junle where junle_id=4;',
   ...>    '1');


sqlite> .selftest
sqlite> select * from selftest;

---- 出力確認
takai@takai-pc ~/db
$ more testcase-out.txt
　:
  .
  .
[test301]cntup_junle after insert on my_contents => Expected301 => 1
0 errors out of 1 tests   -- テスト番号301の表示内容
  tno = 300
   op = memo
  cmd = [test301]cntup_junle after insert on my_contents => Expected301 => 1
  ans =

  tno = 301
   op = run
  cmd = insert into my_contents values(null, 'test1', 4, 10);
  select junle_count from my_junle where junle_id=4;
  ans = 1


---- my_contentsテーブルのtitle='test1'レコードが削除されると、cntdown_junle after delete on my_contentsトリガー処理がおこなわれ、削除レコードのjunle=4に該当するmy_junleテーブルのjunle_id=4のjunle_countが1から0にかわる
sqlite> delete from selftest;
sqlite> insert into selftest(tno, op, cmd) values(400, 'memo', '[test401]cntdown_junle after delete on my_contents => Expected401: 0');

sqlite> insert into selftest(op, cmd, ans) values(
   ...>   'run',
   ...>   'delete from my_contents where title=''test1'';
   ...>    select junle_count from my_junle where junle_id=4;',
   ...>    '0');


sqlite> .selftest
sqlite> select * from selftest;

takai@takai-pc ~/db
$ more testcase-out.txt
  .
  .
  .
[test401]cntdown_junle after delete on my_contents => Expected401: 0
0 errors out of 1 tests  -- テスト番号401の出力内容
  tno = 400
   op = memo
  cmd = [test401]cntdown_junle after delete on my_contents => Expected401: 0
  ans =

  tno = 401
   op = run
  cmd = delete from my_contents where title='test1';
  select junle_count from my_junle where junle_id=4;
  ans = 0
  
---- my_contentsテーブルの title='birds_wondering'のレコードのjunleが3から4に変更されると、my_cntdownup_junle after update of junle on my_contentsトリガー処理により、テーブルのjunle_id=4のjunle_countが0から1になり、junle_id=3のjunle_countが1から0になる
sqlite> delete from selftest;
sqlite> insert into selftest(tno, op, cmd) values(500, 'memo', '[test501-502]cntdownup_junle after update of junle on my_contents => Expected501; 1 Expected502: 0');

sqlite> insert into selftest(op, cmd, ans) values(
   ...>   'run',
   ...>   'update my_contents set junle=4 where title=''birds_wondering'';
   ...>    select junle_count from my_junle where junle_id=4;',
   ...>    '1');
sqlite> insert into selftest(op, cmd, ans) values(
   ...>   'run',
   ...>    'select junle_count from my_junle where junle_id=3;',
   ...>    '0');
   


sqlite> .selftest
sqlite> select * from selftest;

---- 行われた全てのテストの表示確認
takai@takai-pc ~/db
$ more testcase-out.txt
[target tables]
0 errors out of 0 tests
id  title            junle  page_num
--  ---------------  -----  --------
1   java             1      3
2   c++              1      5
4   birds_wondering  3      15
junle_id  junle_name  junle_count
--------  ----------  -----------
1         programing  2
2         science     0
3         essay       1
4         poem        0
[test201]select count of my_junle table => Expected201: 0
0 errors out of 1 tests
  tno = 200
   op = memo
  cmd = [test201]select count of my_junle table => Expected201: 0
  ans =

  tno = 201
   op = run
  cmd = select junle_count from my_junle where junle_id=4;
  ans = 0
[test301]cntup_junle after insert on my_contents => Expected301 => 1
0 errors out of 1 tests
  tno = 300
   op = memo
  cmd = [test301]cntup_junle after insert on my_contents => Expected301 => 1
  ans =

  tno = 301
   op = run
  cmd = insert into my_contents values(null, 'test1', 4, 10);
  select junle_count from my_junle where junle_id=4;
  ans = 1
[test401]cntdown_junle after delete on my_contents => Expected401: 0
0 errors out of 1 tests
  tno = 400
   op = memo
  cmd = [test401]cntdown_junle after delete on my_contents => Expected401: 0
  ans =

  tno = 401
   op = run
  cmd = delete from my_contents where title='test1';
  select junle_count from my_junle where junle_id=4;
  ans = 0
[test501-502]cntdownup_junle after update of junle on my_contents => Expected501
; 1 Expected502: 0
0 errors out of 2 tests  -- テスト番号501と502の出力内容
  tno = 500
   op = memo
  cmd = [test501-502]cntdownup_junle after update of junle on my_contents => Exp
ected501; 1 Expected502: 0
  ans =

  tno = 501
   op = run
  cmd = update my_contents set junle=4 where title='birds_wondering';
  select junle_count from my_junle where junle_id=4;
  ans = 1

  tno = 502
   op = run
  cmd = select junle_count from my_junle where junle_id=3;
  ans = 0

どんなテストが行われたかを記録しなければ、testcaseコマンド、memoオペレーション、selftestテーブルのselect文を省略しても良いと思います。端末画面にテスト検証結果が出力されるだけになります。

データベースsqlite3 その９

SQLITE3 トリガー構文

SQLITE3トリガーアクションについて

トリガー構文チュートリアル

題材

テーブル生成

my_junleテーブルにデータを入れる

insertトリガー生成

deleteトリガー生成

updateトリガー

instead トリガー

セルフテスト

selftest テーブル生成

selftestテーブルの構成

テスト結果の出力

ターゲットとなるテーブルの内容を出力する。

テスト内容を挿入する

いいね:

関連

コメントを残すコメントをキャンセル

SQLITE3 トリガー構文

SQLITE3トリガーアクションについて

トリガー構文チュートリアル

題材

テーブル生成

my_junleテーブルにデータを入れる

insertトリガー生成

deleteトリガー生成

updateトリガー

instead トリガー

セルフテスト

selftest テーブル生成

selftestテーブルの構成

テスト結果の出力

ターゲットとなるテーブルの内容を出力する。

テスト内容を挿入する

いいね:

関連

関連タグ

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル