市場シミュレーション（第24回）：SQL入門(VII)

はじめに

前回の「市場シミュレーション（第23回）：SQL入門(VI)」では、SQLについて理解すべきいくつかの重要な点について説明しました。これにより、データベースを扱うための最低限の基礎が得られました。したがって、まだ議論すべきことは非常に多く、また内容もかなり複雑になるため、ここでは導入に時間をかけません。すぐに本題に入ります。

問題の理解

データベースは、突き詰めればファイル内のレコード集合にすぎません。しかし、これから説明しようとしている内容を理解するために、次のコードを使用してみましょう。

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL
17. );
18. 
19. INSERT INTO tb_Symbols (id, symbol) VALUES
20.     (2, 'PETR4'),
21.     (1, 'ITUB3'),
22.     (3, 'VALE3');
23. 
24. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES
25.     ('2023-07-10', '22.00', 1),
26.     ('2023-07-11', '22.20', 1),
27.     ('2023-07-12', '22.40', 1),
28.     ('2023-07-13', '22.30', 1),
29.     ('2023-07-14', '22.60', 1),
30.     ('2023-07-10', '26.00', 2),
31.     ('2023-07-11', '26.20', 2),
32.     ('2023-07-12', '26.40', 2),
33.     ('2023-07-13', '26.30', 2),
34.     ('2023-07-14', '26.60', 2),
35.     ('2023-07-10', '62.00', 3),
36.     ('2023-07-11', '62.20', 3),
37.     ('2023-07-12', '62.40', 3),
38.     ('2023-07-13', '62.30', 3),
39.     ('2023-07-14', '62.60', 3);
40. 
41. SELECT tq.of_day AS 'Data da cotação',
42.        tq.price AS 'Preço Atual',
43.        ts.symbol AS 'Nome do Ativo'
44.     FROM tb_Quotes AS tq, tb_Symbols AS ts
45.     WHERE tq.fk_id = ts.id
46.     ORDER BY price DESC;

コード01

コード01にはおそらく見覚えがあると思います。というのも、この中で使用されているすべての要素は、これまでの記事で詳細に説明されてきたからです。このコードを実行すると、次のような結果が得られます。

図01

この図は、まさに私たちが期待していた通りの結果を示しています。3つの銘柄を作成し、それぞれに5つの価格データを追加したため、合計で15件のレコードが存在しています。しかし、ここからが本題です。この問題は主に、データベースから何かを削除しようとしたときに発生します。「なぜデータベースからレコードを削除すると問題が発生するのだろうか。それはあまり意味がないのではないか。」本質的には、その通りです。しかし、関連テーブルを使用している場合には、別のスキーマ設計アプローチでは発生しない問題が生じます。このような問題の一つは、以下に示すプログラムを実行することで確認できます。

1. DELETE FROM tb_Symbols WHERE symbol = 'PETR4';
2. 
3. SELECT tq.of_day AS 'Data da cotação',
4.        tq.price AS 'Preço Atual',
5.        ts.symbol AS 'Nome do Ativo'
6.     FROM tb_Quotes AS tq, tb_Symbols AS ts
7.     WHERE tq.fk_id = ts.id
8.     ORDER BY price DESC;

コード02

あなたはこう思うかもしれません。「なるほど、しかしそれの何が問題なのだろう。私の目的はまさにPETR4のレコードをすべて削除することだったのだ。」実際、1行目が実行されるとSQLはPETR4のレコードを削除します。実際にその通りで、3行目のSELECTを実行すると、次のような結果が得られます。

図02

この図は、1行目のコマンドが正しく機能したことを示しています。しかし、1行目のコマンドが何をしたのかを本当に理解しているでしょうか。さらに言えば、データベース内部で何が起きていたのかを本当に理解しているでしょうか。多くの人は、もちろん理解していると答えるでしょう。では質問です。このコード01はリレーショナルデータベースを作成しているのでしょうか。それともしていないのでしょうか。もししているのであれば、その関連付けはどこで定義されているのでしょうか。逆にしていないのであれば、その関連付けはどのように保証されるのでしょうか。少しSQLコマンドを知っているだけでデータベースを扱えると思っている人が多いことはわかっています。しかし、すべては見た目ほど単純ではありませんし、同時に多くの人が想像するほど難しいものでもありません。いずれにせよ、一つ理解しておいてほしいことがあります。

コード01はリレーショナルデータベースを作成していません。

「そんなはずはない。テーブルで外部キーを宣言して、他のテーブルのデータを参照しているのを見ればわかる。リレーショナルではないとはどういう意味だ。おかしいのではないか。」いいえ。これから、そのデータベースがリレーショナルではないことを示します。次の図を見てください。

図03

30通りすべての組み合わせは見えていないものの、PETR4への参照が存在していないことがわかります。しかし、fk_idが2の値は存在しているにもかかわらず、その値は銘柄識別子には含まれていません。これにより、時間の経過とともにデータ整合性の問題が発生しやすくなります。その理由については後ほど詳しく説明します。では、DELETEコマンドがデータベースに対して実際に何をおこなっているのかを理解しましょう。このデータベースはリレーショナルではないため、2つのテーブルは独立しており、データは単なる参照値によってのみ結び付けられています。

この構造には利点もありますが、実際にリレーショナルデータベースを構築していることを保証するものではありません。ここで本質的な理由を見ていきます。ご存知の通り、id 2は空いています。このことはtb_Symbolsテーブルを見るだけで確認できます（下図参照）。

図04

この情報があれば、BBDC4などの別の銘柄をこの空いているidに割り当てることができます。これは次のようにおこないます。

図05

質問です。これに何か問題はあるのでしょうか。原則的には問題はありません。なぜならid 2は本質的に空いていたからです。しかし、ここに問題が潜んでいます。データがtb_Quotesテーブルに挿入される際、整合性の取れないデータがデータベース内に入り込む可能性があります。そして、それに気づくのは大量のデータを入力したかなり後になってからである可能性が高いのです。時間が経つほど、データのどこかに問題があることに気づく可能性は低くなります。ところが、もし仮にBBDC4をtb_Symbolsテーブルに挿入した直後にデータベースをクエリした場合、次の図のような結果になるかもしれません。

図06

「待ってください。どういうことですか。BBDC4をデータベースに挿入したばかりなのに、もうその情報があるのですか。」はい、それは起こり得ます。これは、自分が理解していると思い込んで作業を進めることの危険性です。思い込みや過信は、このような問題を招き、問題が表面化するのはかなり後になることもあります。その結果、データベースに保存されたデータの信頼性を検証する手段が失われてしまう可能性があります。この問題を解決するには、主キーと外部キーの間に関連付けが成立するようにデータベースの構造を変更する必要があります。したがって、同じコード01は次のように実行されるべきです。

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. INSERT INTO tb_Symbols (id, symbol) VALUES
21.     (2, 'PETR4'),
22.     (1, 'ITUB3'),
23.     (3, 'VALE3');
24. 
25. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES
26.     ('2023-07-10', '22.00', 1),
27.     ('2023-07-11', '22.20', 1),
28.     ('2023-07-12', '22.40', 1),
29.     ('2023-07-13', '22.30', 1),
30.     ('2023-07-14', '22.60', 1),
31.     ('2023-07-10', '26.00', 2),
32.     ('2023-07-11', '26.20', 2),
33.     ('2023-07-12', '26.40', 2),
34.     ('2023-07-13', '26.30', 2),
35.     ('2023-07-14', '26.60', 2),
36.     ('2023-07-10', '62.00', 3),
37.     ('2023-07-11', '62.20', 3),
38.     ('2023-07-12', '62.40', 3),
39.     ('2023-07-13', '62.30', 3),
40.     ('2023-07-14', '62.60', 3);
41.     
42. SELECT tq.of_day AS 'Data da cotação',
43.        tq.price AS 'Preço Atual',
44.        ts.symbol AS 'Nome do Ativo'
45.     FROM tb_Quotes AS tq, tb_Symbols AS ts
46.     WHERE tq.fk_id = ts.id
47.     ORDER BY price DESC;

コード03

なお、両方のスクリプトの唯一の違いは17行目です。まさにそこが、テーブル間の実際の関連付けを確立している部分です。「でも、もし解決策がそれだけ単純なら、なぜ最初からコード03のように全部作らないのですか。」問題はそこです。コード03のような形で要素を作成すると、レコード削除の手順や制約が増え、削除処理が扱いにくくなります。これらの問題を分けて考えるために、次の新しい話題に進みましょう。

関連テーブルにおけるレコード削除

なぜ多くのユーザーがデータベース構築にコード01を使い、コード03を避けるのかを理解するために、先ほどと同じことを試してみましょう。つまり、PETR4を削除し、それをBBDC4に置き換えるという操作です。以下の例を使ってこれを実行できます。

図07

図07に注目すると、ハイライトされた箇所はtb_SymbolsテーブルからPETR4を削除しようとしています。しかしSQLは、この操作が実行できないことを示します。これはデータベースがコード03で作成されているためです。多くの人はこのようなSQLの挙動に苛立ち、解決策を探そうとします。そして最も単純な解決策としてコード01を使うようになりますが、それは遅かれ早かれ別の問題を引き起こすことになります。良い点として、図07で示されているSQLの応答は単なるエラーではありません。これはtb_Symbolsテーブルとtb_Quotesテーブルの間に実際の関連付けが存在しており、それがデータの誤削除を防いでいることを意味しています。

この関連付けがどこで定義されているのかを理解することは多くの場面で重要ですが、そのためには前回の記事で扱ったデータベースダイアグラムを参照する必要があります。ただし、ここではテーブルが2つしかないため、比較的簡単に理解できます。では本題に入りましょう。

図08

そして、この図08のコードは以下の通りです。

01. DELETE FROM tb_Quotes 
02.     WHERE fk_id = (SELECT ts.id FROM tb_Symbols AS ts WHERE ts.symbol = 'PETR4');
03. DELETE FROM tb_Symbols WHERE symbol = 'PETR4';
04. 
05. SELECT tq.of_day AS 'Data da cotação',
06.        tq.price AS 'Preço Atual',
07.        ts.symbol AS 'Nome do Ativo'
08.     FROM tb_Quotes AS tq, tb_Symbols AS ts
09.     WHERE tq.fk_id = ts.id
10.     ORDER BY price DESC;

コード04

こうして見ると、単に物事を複雑にしているだけのように見えるかもしれませんが、実際にはより安全にしているのです。コード04の1行目では、SQLに対してtb_Quotesテーブルから何かを削除するよう指示していることに注意してください。2行目では、何を削除すべきかを指定しています。ここで重要なのは、この処理を次のように解釈すべきだという点です。 SQLはまずtb_Quotesテーブルに行き、PETR4に関連するすべてのレコードを削除し、その後tb_Symbolsテーブルに移動してPETR4のレコードを削除する必要がある、ということです。一見するとやりすぎのようにも思えますし、何も起こらないのではないかと感じるかもしれません。しかし、tb_Quotesテーブルをクエリした結果を見ると状況は明らかになります。

図09

「すべて予想通りに動いたのですか。」では次に、BBDC4を挿入し、その直後にデータベースの中身を確認してみましょう。これは次の図に示されています。

図10

図10のマークされた領域を見ると、fk_idの値がBBDC4のidと一致していないことが分かります。しかし、このようなデータを大規模なデータベースで分析するのはかなり混乱を招く可能性があります。ただし重要なのは、クエリそのものに注目することではなく、データベース内部に何が存在しているのかを理解することです。もし実務でおこなうようなリレーショナルクエリを実行すれば、その結果は次のようになります。

図11

一見すると何も表示されていないように見えますが、実際にはそれが望ましい結果です。青い領域はSQLクエリの実行結果を示しており、そして緑色の領域は実行されたコマンドを示しています。つまりSQLは、BBDC4のidに対応する古いPETR4の参照を見つけることができなかったということです。言い換えれば、データベースの整合性は維持されているということです。

さて、これは望ましい結果ですが、ここからさらに興味深い話に進みましょう。そのために次のトピックへ移ります。

トリガーの使用

これから見る内容は、自分が何をしているのかを本当に理解している場合にだけ使ってください。前のセクションの内容に慣れるまでは使わないでください。そうでなければ、SQLで非常に厄介な状況に陥ることになります。では、これが何を意味するのか理解していきましょう。たとえば、テーブル構造の観点から非常に複雑なデータベースが存在するとします。そしてこのデータベースでは、テーブル間に特定の関連付けが用いられています。前のトピックですでに示したように、あるテーブルから別のテーブルによって外部キーで参照されている主キーのレコードを削除しようとすると、その主キーへの参照がすべて削除されるまでは実行できません。

この処理方法自体は前のトピックで説明しました。テーブルが少ない場合は非常に簡単で分かりやすいですが、テーブルが多くなると状況は複雑になります。なぜなら、各テーブルごとにDELETE文を作成しなければならなくなるからです。

そこで登場するのがトリガーの考え方です。トリガーを使うことで、このような処理を簡略化し、複数のDELETEコマンドの代わりに1つのDELETEコマンドだけで処理できるようにすることができます。一見すると単純で便利な仕組みに思えますし、実際その通りでもありますが、問題もあります。まず、SQLの実装ごとにトリガーの扱いが異なるという点です。たとえばSQLite向けに書かれたトリガーのコードは、SQL ServerやMySQLでは異なる動作や構文になる場合があります。同じSQLという言語であっても、トリガーの実装方法は大きく異なることがあります。さらに、SQLite自体がオープンソースであるため、用途に応じて挙動が変更されている可能性さえあります。

SQLiteでのトリガーの一例は以下のように示されます。

図12

同様の図はlang_createtriggerでも確認できます。そこでは、この図に加えて仕組みの簡単な説明も見ることができます。この模式図を理解すること自体は難しくありませんし、トリガー作成に必要なコマンドの流れを理解する助けにもなります。

「なるほど、でもこの矢印だらけの図は一体何を示しているのですか。ほとんどわかりません。」心配はいりません。慣れれば簡単になります。ここでは無理してストレスを感じる必要はありません。図の上部に円があることに注目してください。そこがコードの開始点です。各コマンドは円で示されており、矢印は次に実行されるべきコマンドの流れを示しています。理論だけで終わらせないために、実際にトリガーの作成方法を見ていきましょう。コード03のことを覚えていますか。そのコードにトリガーを追加するには、図12で示されているようなものを作成すればよいだけです。その結果、次のようなコードになります。

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TRIGGER tr_DeleteSymbol BEFORE DELETE ON tb_Symbols
13. BEGIN
14.     DELETE FROM tb_Quotes WHERE fk_id = OLD.id;
15. END;
16. 
17. CREATE TABLE IF NOT EXISTS tb_Quotes
18. (
19.     of_day NOT NULL,
20.     price NOT NULL,
21.     fk_id NOT NULL,
22.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
23. );
24. 
25. INSERT INTO tb_Symbols (id, symbol) VALUES
26.     (2, 'PETR4'),
27.     (1, 'ITUB3'),
28.     (3, 'VALE3');
29. 
30. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES
31.     ('2023-07-10', '22.00', 1),
32.     ('2023-07-11', '22.20', 1),
33.     ('2023-07-12', '22.40', 1),
34.     ('2023-07-13', '22.30', 1),
35.     ('2023-07-14', '22.60', 1),
36.     ('2023-07-10', '26.00', 2),
37.     ('2023-07-11', '26.20', 2),
38.     ('2023-07-12', '26.40', 2),
39.     ('2023-07-13', '26.30', 2),
40.     ('2023-07-14', '26.60', 2),
41.     ('2023-07-10', '62.00', 3),
42.     ('2023-07-11', '62.20', 3),
43.     ('2023-07-12', '62.40', 3),
44.     ('2023-07-13', '62.30', 3),
45.     ('2023-07-14', '62.60', 3);
46.     
47. SELECT tq.of_day AS 'Data da cotação',
48.        tq.price AS 'Preço Atual',
49.        ts.symbol AS 'Nome do Ativo'
50.     FROM tb_Quotes AS tq, tb_Symbols AS ts
51.     WHERE tq.fk_id = ts.id
52.     ORDER BY price DESC;
53. 

コード05

ここで注意すべきなのは、コード03とコード05の違いです。おそらく気づいたと思いますが、その違いはまさに私たちが作成したトリガーにあります。基本的には、コード05の内容はほとんど理解できるはずですが、1つだけ疑問が残ります。それは「OLD」という値がどこから来たのか、そしてこのトリガーが実際にどのように動作しているのかという点です。では、それを理解していきましょう。この「OLD」というキーワードはSQLite固有のものです。つまり、SQLiteの実装に完全に依存しているということです。他のDBMSでは別の名前が使われる可能性があるため、より詳細な情報は各ドキュメントを参照する必要があります。

しかし、OLDは「古い」という意味です。ただしここで扱っているのは識別子です。「でも、どの識別子ですか。トリガーには何のパラメータも渡していないのに、どうやって指定していないものを参照できるのですか。」落ち着いてください。このトリガーの仕組みは少し混乱しやすいものだということは、すでに述べた通りです。したがって、まずはより単純なテーブルや基本的な仕組みについて理解している必要があります。トリガーはSQLの中でも最も高度な機能の一つですので、急ぐ必要はありません。

このOLDがどこから来るのかを理解するには、まずコードがどのように実行されるのかを理解する必要があります。コード05を実行すると、データベースからPETR4が削除され、その代わりにBBDC4が挿入されることになります。ただし今回は、トリガーが存在するため、これまでとは異なる方法で処理されます。この変更のためには、次のコードを使用します。

1. DELETE FROM tb_Symbols WHERE symbol = 'PETR4';
2. INSERT INTO tb_Symbols(id, symbol) VALUES(2, 'BBDC4');
3. 
4. SELECT tq.of_day AS 'Data da cotação',
5.        tq.price AS 'Preço Atual',
6.        ts.symbol AS 'Nome do Ativo'
7.     FROM tb_Quotes AS tq, tb_Symbols AS ts
8.     WHERE tq.fk_id = ts.id
9.     ORDER BY price DESC;

コード06

コード06を実行すると、次の結果が得られます。

図13

「え？どうしてですか。私を騙そうとしているでしょう。冗談でしょう。」冗談であればよかったのですが、これは事実です。なぜならトリガーが存在しているからです。コード06の1行目が実行されると、データベース内のPETR4に何らかの形で関連しているすべてのレコードが削除されます。そして、値2のIDは現在空いているため、コード06の2行目を使って、そのIDを用いてBBDC4を挿入することができます。だからこそ、もう一度強調しておきたいのですが、トリガーを使う前に、まず基本的なSQLの動作を理解してください。そうしなければ、実行されるコードに完全に混乱してしまうことになります。

トリガーを使ったデータ挿入

さて、前のトピックで見た内容が少し難しく感じられたのであれば、ここからはさらに難しい内容になります。つまり、トリガーを使ったデータ挿入です。始める前に、もう一度強調しておきます。知識というものは段階的に、少しずつ成熟していくものです。より複雑な内容に進む前に、まず基礎をしっかり理解しておくことが非常に重要です。そうでなければ、必要な前提知識が不足したまま先へ進むことになってしまいます。仕組みを完全に理解するまでは、何も使用しようとしないでください。

トリガーを使ってデータを挿入する方法を理解するためには、まずもう少し単純なところから始める必要があります。少し立ち止まり、基本的な概念を先に確認しましょう。これは、まだ十分に説明されていない内容に対して先へ進みすぎないようにするためです。繰り返しになりますが、ここで扱うすべての内容は標準的なSQLiteの実装を前提としています。もし疑問がある場合は、使用している実装のドキュメントを参照し、ここで扱う内容がどのように構成・設定されているのかを確認してください。では、次のコードを見ていきましょう。

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );

コード07

ここまで来ると、コード07が何をしているのか、そしてそのテーブルにレコードを挿入し始めたときにどのようなデータベースが作られるのかは、皆さんも理解できていると思います。しかし、前のトピックで十分に明確にされていなかった点がいくつか残っているかもしれません。つまり次の点です。

データベースを作成する前に、データベースに含めるトリガーを定義する必要があります。

提示された情報を見ると、トリガーは後からデータベースに挿入したり削除したりできるものだという印象を受けたかもしれません。しかし実際には、必ずしもそういう仕組みではありません。SQLがデータベースを含むファイルを作成する時点で、必要な要素はすべて適切に定義されていなければなりません。ある意味では、もしそれが実装側でサポートされていなければ、すでに作成されたデータベースに対して手続き的なロジックを後から追加したり削除したりすることはできないということになります。ただし、SQL Serverのように、後からストアドロジックを追加・削除できる実装も存在します。この場合、スクリプトは必ずしもデータベース本体に強く結びついているわけではないため、柔軟に変更できます。

一方でトリガーに関しては、通常はより厳密に扱われます。そのため、何となくデータベースを作るのではなく、事前に構造全体を慎重に設計する必要があります。そうすることで、後から予期しない問題に直面することを避けることができます。したがって次のステップは、データベースへのレコード挿入時に発火するトリガーを追加することです。これにより、コード07はコード08へと変わります。その全体は次に示す通りです。

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. CREATE TRIGGER IF NOT EXISTS tr_InsertSymbol AFTER INSERT ON tb_Symbols
21. BEGIN
22.     UPDATE tb_Symbols SET symbol = upper(NEW.symbol) WHERE NEW.id = id;
23. END;

コード08

ここで図12を見逃さないでください。つまり、コード08の中で何が作られているのかという点です。トリガーに関して言えば、この同じ図にはDELETE、INSERT、UPDATEの各コマンドが示されています。繰り返しになりますが、コードの中に何が含まれているのかを正しく理解する必要があります。

コード07を更新してコード08を生成する目的は非常に単純です。tb_Symbolsテーブルに新しい銘柄が挿入されると、このトリガーが自動的にそのレコードを大文字に変換し、すべての文字が大文字で表示されるようにします。「ちょっと、冗談ですよね。」いいえ、冗談ではありません。データベースでは通常、データが何らかの形で検証され、必要に応じて修正されることが求められます。

コード08でおこなっていることは一見些細な処理に見えるかもしれませんが、特定のシナリオでは十分に意味があります。この目的が達成できているかどうかを確認するために、次のコードを使用します。改めて強調すると、コード08を実行し、その後かなり時間が経ってからコード09を実行したとしても、すべては通常通り正常に動作するはずです。

1. INSERT INTO tb_Symbols (id, symbol)
2.                 VALUES (1, 'vale3'),
3.                        (2, 'PetR4'),
4.                        (3, 'ITUB4');
5. SELECT * FROM tb_Symbols;

コード09

なお、値は大文字と小文字の両方で入力している点に注意してください。しかしデータベース内では、コード09によって挿入されたすべてのレコードは大文字で記録されるはずです。これは、行05を実行した結果として確認でき、次のようになります。

図14

まるで魔法のように、強調された領域では値が変換されていることが確認できます。しかし、これがトリガーなしでも起こると考えてはいけません。SQLは文字列を自動的に変更・修正することはなく、元のまま保持します。ただし、コード08で作成したようなトリガーを使用した場合は別であり、その目的は特定のレコードを変更、あるいは更新することにあります。

結果を確認したところで、次はコード08に実装されたトリガーがどのように動作しているのかを理解したくなります。というのも、コード09の中にはそのトリガーが明示的に呼び出されていないからです。このような状況は、SQL初心者にとってしばしば不安の原因になります。一見すると、トリガーを直接呼び出していないため、発火すべきではないように思えるからです。

では、コード08に戻ってトリガーの仕組みを確認しましょう。行20はコード08に書かれている通りに正確に読む必要があります。まだ解釈しようとせず、まずは書かれている内容そのままを読み取ってください。理解の助けとして、次のように読みます。

もし存在しない場合、tb_Symbolsテーブルにデータが挿入された後(AFTER INSERT)に実行されるトリガー「tr_InsertSymbol」を作成する。

これは難しいことではありません。重要なのは、何を実現したいのかに注意を払うことです。行21および行23は、手続きの本体を示すSQLの予約語にすぎません。詳細については、再び図12を参照してください。ここではあくまで概念的な説明にとどめます。では行22を見てみましょう。これも行20と同じように読みます。理解のために、次のように解釈してください。

現在のレコードがtb_Symbolsテーブルで更新される際、idが一致する場合、symbolの値をupper(NEW.symbol)に更新する。

ここでもSQLite特有の要素が出てきます。NEWキーワードはSQLiteにおいてのみ登場する概念です。削除時にはOLD、挿入時にはNEWを使います。この違いは一見すると非常に混乱しやすいものです。「確かに混乱しています。なぜNEWとOLDを使い分けるのですか。トリガー名はtr_InsertSymbolですよね。挿入したのに更新しているなら、それはもうOLDではないのですか。まったくわかりません。」

この疑問は自然です。実際のところ、最初は少し混乱を招く細かい点がいくつかありますが、それらも実際に使い、学んでいくうちに混乱しなくなり、意味が分かるようになります。それでも、ここでは少しでも分かりやすくするために、「NEW」と「OLD」をいつ使うのかを明確に整理して説明します。少なくとも、これによって読者の混乱を少しでも減らせればと思います。

DELETE操作を行う場合、削除されるデータはすでにデータベース内に存在しており、これから消える対象です。そのため「OLD」を使用します。一方でINSERT操作を行う場合、データはまだデータベース内に存在しておらず、これから作成されるため「NEW」を使用します。混乱の原因は、コード08の行20にあるトリガーがINSERTの後（「AFTER INSERT」）に実行されると定義されている点にあります。しかし重要なのは、そのデータは「新しく挿入されたものであり、以前はデータベースに存在していなかった」ということです。

しかし、UPDATE操作に関連するトリガーの場合は状況が少し複雑になります。そのため、もう少し広い視点で考える必要があります。注意してください。データベース内の既存の情報を置き換えるために使われる新しい値は「NEW」として扱われます。一方で、すでにデータベース内に存在し、置き換えられる前の古い情報は「OLD」として扱われます。この点を理解できれば、標準的なSQLiteデータベースで動作するあらゆるトリガースクリプトを扱うことができるようになります。ただしここで扱っているのは標準的なSQLiteの動作であり、実装によっては異なる挙動をする場合もあります。その場合は、必ずその実装のドキュメントを確認してください。

選択する道

データベースにレコードを挿入する際にトリガーを使い、データの整合性を確保する方法を理解した今、もう一つ同じくらい重要な点を考える必要があります。なぜなら、データベースの整合性は部分的にしか保証されないからです。「しかし、なぜ「部分的にしか」と言うのか？」その理由はUPDATEコマンドにあります。ここまで説明してきた保護の仕組みは、INSERTを基準にしたものだからです。

しかし、UPDATEコマンドには解決すべき問題があります。たとえば次のようなケースを考えてみましょう。UPDATEコマンドを使ってデータベースの内容を変更したい場合、SQLはどのルールを適用すべきかを自動的には判断できません。そのため、UPDATEによって入力されたデータはすべてSQLに受け入れられ、事前に検証されたデータであっても、そのまま以前の値を置き換えてしまうことになります。これを示すために、次のコードを見てください。

1. INSERT INTO tb_Symbols (id, symbol)
2.                 VALUES (1, 'vale3'),
3.                        (2, 'PetR4'),
4.                        (3, 'ITUB4');
5. 
6. UPDATE tb_Symbols SET symbol = 'iTub4' WHERE id = 3;
7.
8. SELECT * FROM tb_Symbols;

コード10

コード10は、コード08によって作成されたデータベース上で実行されなければなりません。これは、以下に示す結果を得るために必要です。

図15

データベースの強調された領域を見ると、本来そこにあってはならないものが見つかります。本来であればすべての値は大文字で記録されるべきです。しかし、コード10の6行目の影響によりSQLがそのレコードを変更してしまい、その結果としてデータベースの整合性が損なわれています。ここで重要なのは、すべての動作は達成すべき目的に依存しているということです。単一の正解となる公式は存在しません。まず問題を理解し、その後で解決方法を検討する必要があります。ただし、それらすべてはデータベースが機密データを受け取り始める前に定義されていなければなりません。

では次のように考えてみましょう。一度作成されたレコードは変更できないものとします。この設計により、先ほどのような問題を回避することができます。しかし、もしデータベースの更新を許可するのであれば、既存レコードに対して挿入されるデータは一定のルールに従わなければなりません。その場合、UPDATEコマンドに関する問題を解決するために別の方法を取る必要があります。

一方で、銘柄名の変更自体は許可することもできます。しかしその場合、ユーザーには名前が変更されたように見えるだけです。実際のデータベース内部では、新しいレコードが作成される形でデータがコピーされます。古いレコードはユーザーから直接アクセスできない状態のまま保持されます。ただしデータベース管理者は元のデータを復元することができます。なぜなら元のレコードはそのまま残っているからです。このような考え方は別の解決策につながり、それには実装も必要になります。つまり、それぞれのケースは独立した問題であり、すべてに適用できる単一の解決策は存在しないということです。

では状況をより分かりやすくするために、ユーザーが銘柄名を変更できるようにしつつも、その名前が最終的にはINSERTコマンドと同じルールに従うようにUPDATEコマンドを扱う方法を見ていきましょう。そのために、元のスクリプトを次のように修正することができます。

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. CREATE TRIGGER IF NOT EXISTS tr_InsertSymbol AFTER INSERT ON tb_Symbols
21. BEGIN
22.     UPDATE tb_Symbols SET symbol = NEW.symbol WHERE NEW.id = id;
23. END;
24. 
25. CREATE TRIGGER IF NOT EXISTS tr_UpdateSymbol AFTER UPDATE ON tb_Symbols
26. BEGIN
27.     UPDATE tb_Symbols SET symbol = upper(NEW.symbol) WHERE NEW.id = id;    
28. END;

コード11

「しかし、ちょっと待ってください。今の説明は明らかにおかしいのではないでしょうか。UPDATEコマンド用のトリガーコードが、INSERT用トリガーのほぼ完全なコピーになっているのはどういうことでしょうか。本当にこれで動作するのでしょうか。すると思いません。INSERTすれば20行目のトリガーが発火しますし、22行目ではUPDATEコマンドを使用しているため、25行目に実装されたトリガーも発火するはずです。そして27行目が実行されると、SQLはループに入ってしまうのではないでしょうか。正直に言って、これが動くとは思えません。」

もしこの実行シーケンスを本当にそのように捉えているのであれば、まだSQLにおけるトリガーの仕組みを正しく理解できていないということになります。ただし、それは問題ありません。では、コード11で新しいデータベースを作成し、その直後にコード10をこの新しいデータベースで実行するとどうなるかを見てみましょう。

図16

ここで次のことを理解してください。ユーザーがtb_Symbolsテーブルへのレコード挿入を要求した場合、SQLはそのレコードを挿入することもあれば、しないこともあります。もし挿入された場合、SQLはコード11の20行目にあるトリガーを実行します。これにより22行目が実行され、データベース内のデータが更新されます。そして22行目の処理によって25行目のトリガーが実行されます。ここまではあなたの理解は正しいです。その後27行目が実行されると、再びSQLに対してデータベースの更新が要求されます。

しかし今回は、レコードの内容を強制的に特定のパターンへと変換し、INSERTコマンドで作成された時点の状態と完全に一致する形に戻します。このようにして、INSERTコマンドとUPDATEコマンドの両方をカバーすることができました。最良の説明方法とは言えませんが、これがこの仕組みを説明するために私が見つけた最も単純な方法です。

そして最後に伝えたいのは、UPDATEコマンドによる変更を特定のレコードに対して禁止したい場合、その制御はデータベース作成時に次のコードを使うことで比較的簡単に実現できるということです。

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. CREATE TRIGGER IF NOT EXISTS tr_InsertSymbol AFTER INSERT ON tb_Symbols
21. BEGIN
22.     UPDATE tb_Symbols SET symbol = NEW.symbol WHERE NEW.id = id;
23. END;
24. 
25. CREATE TRIGGER IF NOT EXISTS tr_UpdateSymbol AFTER UPDATE ON tb_Symbols
26. BEGIN
27.     UPDATE tb_Symbols SET symbol = upper(NEW.symbol) WHERE NEW.id = id;    
28. END;
29. 
30. CREATE TRIGGER IF NOT EXISTS tr_UpdateSymbol_Before BEFORE UPDATE ON tb_Symbols
31.     WHEN upper(NEW.symbol) != upper(OLD.symbol)
32. BEGIN
33.     SELECT RAISE(ABORT, 'ERROR: Unable to update this record...');
34. END;

コード12

ここでは少し状況が異なります。しかし、コード12で作成されたデータベースに対して再びコード10を使用すると、次のような結果が得られます。

図17

ここで図17を注意深く見て、コード12が具体的に何を作成したのかを理解しようとしてください。そのうえでコード10を実行した結果として、この出力が得られた理由を考える必要があります。まず、コード10の1行目は正常に実行され、データベースにレコードが作成されます。しかし6行目が実行されるとエラーが発生し、その内容は図17に示されています。ここで図17の内容に注目してください。メッセージの3行目を見てみましょう。そしてコード12に戻ると、それがまさに33行目と同じメッセージであることが分かります。これは一体どういうことでしょうか。

テーブルにデータを挿入しようとすると、レコード挿入後すぐに20行目のトリガーが実行されます。これにより22行目の処理が走り、レコードの更新コマンドが発行されます。しかし更新の前に、まず30行目のトリガーが実行され、その後に25行目のトリガーが実行されます。ここで重要なのは、コード11の動作が変更されているという点です。まさにそのために31行目のチェックが存在します。このチェックは、更新しようとしているレコードが既存のデータと異なるか、それとも一致しているかを判定します。このチェックによって、レコードが不用意に変更されないようにしています。もしレコードが異なる場合にはトリガーが発火し、その結果として33行目が実行されます。SQLが33行目を実行すると、更新を要求した側に対して「その操作は何らかの理由で実行できない」というメッセージが返されます。

このようなスキーマ設計は、場合によっては有用です。ぜひ学んでみてください。将来的に問題を回避する助けになるかもしれません。

最後に

ここで示した内容は、これから学ぶべき多くのSQL概念の基礎にすぎません。SQLという言葉が出てくると、「学ぶのは無駄だ」「理解するくらいなら、データベースに入れるようなデータを自分でファイルとして作ればいい」といった否定的な意見を持つ人も少なくありません。しかしここで改めて強調したいのは、SQLは非常に扱いやすく、適切に使えば本来ならもっと大きな労力を必要とする作業を簡単にしてくれるということです。

人々がPythonやExcelでおこなっている多くのことは、SQLを使えばもっと簡単に解決できるはずです。しかし、SQLは学習の手間がかかり、きちんと理解して使う必要があるため、多くの人はそれを避け、従来型のプログラミングでデータや情報を結び付けようとして頭を悩ませています。それでも私は断言できます。SQLを学ぶ価値は本当にあります。特にデータ同士がどのように関連しているかを理解し始めたとき、その価値はさらに明確になります。

SQLに関する議論はここで一区切りとなります。ただし、ここから学習を続けていく必要があります。次の記事では、ここまで説明してきた内容がMQL5と組み合わされたときに、実際の実装でどのように使われるのかを見ていきます。まだ、MetaTrader 5上でできることの本質の表面に少し触れたにすぎません。

ファイル	説明
Experts\Expert Advisor.mq5	Chart TradeとEAの連携を示す（Mouse Studyが必要）
Indicators\Chart Trade.mq5	送信する注文を設定するウィンドウを作成する（Mouse Studyが必要）
Indicators\Market Replay.mq5	市場リプレイ/シミュレーションサービスとのやりとりのためのコントロールを作成する（Mouse Studyが必要）
Indicators\Mouse Study.mq5	グラフィカルコントロールとユーザーとのやり取りを実現する（リプレイシステムと実市場での運用の両方に必要）
Services\Market Replay.mq5	市場リプレイ/シミュレーションサービス（システム全体のメインファイル）を作成および維持する
Server.cpp (VS C++)	C++で作成されたソケットサーバーを作成および維持する（ミニチャット版）
Code in Python\Server.py	MetaTrader 5とExcel間のリンク用のPythonソケットを作成および維持する
Indicators\Mini Chat.mq5	インジケーターを介してミニチャットを実装できるようにする（動作にはサーバーが必要）
Experts\Mini Chat.mq5	EAを介してミニチャットを実装できるようにする（動作にはサーバーが必要）
Scripts\SQLite.mq5	MQL5からSQLスクリプトを実行する方法を示す
Files\Script 01.sql	外部キーを持つシンプルなテーブルの作成方法を示す
Files\Script 02.sql	テーブルに値を追加する方法を示す