RDB(リレーショナルデータベース)蔵書管理アプリ | サンプルアプリ一覧

アシアル情報教育研究所・所長の岡本雄樹です。今日はみなさまにリレーショナルデータベースを活用したサンプルアプリと、SQLの書き方について紹介します。

スマートフォンのブラウザでも動作しますので、ぜひ、動かしてみて下さい。

今すぐブラウザで動かす

Monaca Educationのプロジェクトとしてインポートする

まえがき

文部科学省が2019年頃に公開した
『高等学校情報科「情報Ⅰ」教員研修用教材』（2022年度から始まる情報Ⅰに向けた、指導者のための資料）
をご存じでしょうか。

その中にある『4章・学習21 さまざまな形式のデータとその表現形式』ではリレーショナルデータベースを活用した蔵書管理のSQLクエリー例が掲載されています。

リレーショナルデータベースを動かすためには一般的に、実行環境としてSQL言語を処理できる『データベースサーバー』が必要となります。準備が大変なせいもあってか、同資料ではSQLの実行環境は紹介されていませんでした。

しかしながら、世の中にはすごい人たちがいるものです

『JavaScript製のSQL実行エンジン』

というものがオープンソースで公開されていました。
『AlaSQL』という製品になります。

これがあれば、ブラウザだけでSQL言語が処理できるため、データベースサーバーを別途構築しなくても、ブラウザだけで気軽にSQLの体験や学習が行えます。

岡本所長は喜び勇んで、サンプルアプリを開発して解説記事を公開しました。

情報Ⅰ・第4章情報通信ネットワークとデータの活用リレーショナルデータベース

これが2020年頃のお話です。

リレーショナルデータベースについて詳しく知りたい方は、文科省の教材や2020年に書いた私の記事を読んで頂けると幸いです。

RDB(リレーショナルデータベース)蔵書管理アプリとは

今日ご紹介するサンプルアプリは、SQL言語を活用して本を管理できるアプリです。
データの管理にAlaSQLを採用しており、SQL文でデータの登録や参照を行っています。

『本を確認』ではSELECT命令を使って本の一覧を取得できます。
また、『本を登録』では新しい本を登録できます。登録する際に『著者』も同時に登録できます。

著者を登録する際には、あらかじめ『人を登録』から著者になりうる人物を登録しておく必要があります。
上記の機能はすべてSQL言語によるデータ操作で実現されています。

メニューにないデータ操作を行いたい場合は、『SQLで管理』から行えます。SQLの命令文（クエリー）を組み立てることで、データの更新や削除などが自由に行えます。データの操作に失敗した場合に備えて『初期化』の機能も用意されているので心行くまでSQLを学習いただけます。

SQLではデータの取得にSELECTという構文を使用します。

このページでは、SQLのクエリー『SELECT * FROM book;』が実行され、書籍の一覧が取得されます。

*というのは全ての列を差します、そしてFROM以降の部分でテーブル名を指定しています。

今回のサンプルではデータが2件しかないので取得件数に制限を掛けていませんが、limit句を使うことで10だけ取得するといった制限を掛けられます。また、50件目以降のでデータを10件取得したい、といった場合にはoffset句を併用します。


SELECT * FROM book LIMIT 10 OFFSET 50

なお、今このクエリーを実行すると、50件目のデータなんて存在しないので結果が取得できなくなってしまいます。

また、IDが大きい順に取得したい、など、並び順を指定したい場合にはORDER句を使います。


SELECT * FROM book ORDER BY id DESC

大きい順がDESCで小さい順がASCです。
なお、並び順を省略した場合の取得結果はリレーショナルデータベース側の気分次第となります。

AlaSQLではID順で取れていますが、他のデータベースでもそうなるとは限りません。リレーショナルデータベースを活用して本格的にアプリやシステムを作る際にはORDER句はほぼ必須となります。

ＳＱＬ言語と他の言語の連携

このアプリ内では取得結果はHTMLのTableタグで表の形で出力しています。

SQLはデータを返す仕組みしかないため、データを見やすい形に加工するのはJavaScriptなどの汎用的なプログラミング言語が担うことになります。

また、見た目を整えることに関しても、一般的なプログラミング言語はGUI（グラフィカルユーザーインタフェース）を内包していないため、HTMLxCSSに頼るか、何らかのGUIライブラリが必要となります。

具体的には、
JavaScritp側に用意した関数
『showBookTable()』がSQLクエリーの呼び出しや表示を行っています。

本を登録

本の登録ではINSERT文を使って本の登録を行えます。

ISBNというのは本固有の国際的なIDです。市販の本ではほぼ100%付いています。なお、同人誌や出版流通に乗せない冊子には通常付いていません。本アプリでは省略可能としています。

著者情報も登録できるのですが、裏側の考え方はちょっと複雑です。サンプルデータには岡本雄樹が著者の候補として登録されているので、ここでは、岡本雄樹が執筆した本を追加してみることにします。

登録すると、まず、以下のようなSQL文が実行されます。


INSERT INTO book (name,isbn) VALUES ('イラストでよくわかるPHP','978-4844332954');

このクエリーによって、bookテーブルに本のデータが登録されます。
なお、著者データはbookテーブルには入りません。著者データはAuthorテーブルに格納します。

そして次は以下のようなSELECT文が実行されます。


SELECT max(id) AS max FROM book;

これは、bookテーブルの中で一番大きなidを取得するクエリーです。
サンプル通りに動かしている場合、既に登録されている本が2冊、今回登録した本が3冊目になるため、恐らく結果は3が返ります。


INSERT INTO author (book_id,author_id) VALUES ('3','2');

3番目の本という情報を元にINSERT文を作成してauthorテーブルに著者情報を登録します。INSERT文には2という値もありますが、これは岡本雄樹のpersonテーブルにおけるidになります。
Personテーブルはまた後で紹介しますが、サンプルデータでは1番目にアシアル、2番目に岡本雄樹が入っています。

なぜ書籍の情報と著者の著者は別の表に記録するのか？

リレーショナルデータベースでは一般的に、本の情報と著者の著者は別の表に記録します。同データベースでは、リレーショナル（関係）によってデータに意味を持たせているため、分けた方がデータ同士の関係を明らかにしやすいですし、メンテナンスもしやすくなります。

例えば1人の人が複数の本を執筆した時に、表を分けておけば、著者の表から『書いた本一覧』を取得できたりします。

また、著者が複数いる場合、bookテーブルに列として著者1、著者2、などと増やすことでも対応できますが、10名以上の共著本というのもあるため対応方法としては微妙です。

中間テーブルという方法を使えば、列を無尽蔵に増やすこと無く、このケースにも対応できます。本アプリでは、中間テーブルを使って「本」と「人」を関連付けています。

逆にNoSQL型のデータベースJSONなら1つのレコードにデータをまとめて格納するのが一般的です。列を増やす代わりに、配列的な表現で複数データを1つにまとめていれることになります。

同一トランザクションにまとめないと違う本に著者が登録されるかも

今回の本登録処理では以下3つのクエリーが実行されます。


INSERT INTO book (name,isbn) VALUES ('イラストでよくわかるPHP','978-4844332954');
SELECT max(id) AS max FROM book;
INSERT INTO author (book_id,author_id) VALUES ('3','2');

※著者が2名いる場合はINSERT INTO author がもう一本実行されます

SELECT max(id) が実行される前に、別の人がINSERT INTO book を実行していた場合、3ではなく4の値が取れてしまい、別の本に対して著者を登録してしまう可能性があります。

本アプリは同時に一人しか操作できないため、そのような事故は起こらないのですが、世の中の大半のシステムは複数人が同時に利用することを前提に設計されています。

リレーショナルデータベースでは、同時利用でもデータの不整合を起こさずに操作を行える仕組みとして『トランザクション』という考え方が採用されています。

トランザクションでは、上記3つのクエリをバラバラに実行するのでは無く、1つのトランザクションにまとめて実行することで、他の人のクエリの影響を受けずに処理を実行できます。
また、トランザクション中で問題が発生した際には、実行中のクエリをキャンセルしてデータ操作をなかったことにすることもできます。

リレーショナルデータベースには、そのようなデータを安全に扱う仕組みがあるため、業務システムの開発では今でも主力のデータベースとして利用されています。

リレーショナルデータベースとNoSQL型データベースのどちらを使うべきか（Monacaアプリ編）

ケースバイケースです。

実際には作りたいアプリや、ベースにするサンプルアプリによって決めることになります。

一昔前はリレーショナルデータベース一択でしたが、10年ぐらい前からNoSQL型のデータベースが台頭し、それと同時にクラウド経由で使えるNoSQLも登場しました。また、最近のブラウザは『ローカルストレージ』というNoSQL型のデータベースを内包しているため、選択肢は大分増えました。

Monaca Educationではクラウド経由で使えるNoSQLデータベースとして『Monaca Education簡易データベース』を2021年度より提供しています。

プログラミング入門者が配列や連想配列の延長としてデータベースを求めている場合であれば、NoSQL型の方が学びやすいかもしれません。

なお、SQL言語は非手続き型言語でエラーが発生しにくく、学習コストが汎用的なテキストプログラミング言語より低いため、こちらから入っていくのもありです。SQLはデータ分析にも活用でき、また、ITパスポートの試験にもでるので、学ぶ価値は高いです。

ただしテーブルの設計には知識や経験が必要なのでカジュアルなアプリを作るケースならNoSQL型に分があります。

Monaca Educationではクラウド経由で使えるNoSQLデータベースとして『Monaca Education簡易データベース』を2021年度より提供しています。

クラウド経由で利用できるSQLサービスが欲しい

開発コストや運用コストの面から、2021年時点では見送りました。
利用したいという要望が大量に来たら再検討します。

人を登録

人を事前に登録すると、本を登録するときに候補リストに反映されます。

登録すると、以下のようなSQL文が実行されます。


INSERT INTO person (name,note) VALUES ('あんこ','激甘');

personテーブルは人を扱うテーブルです。著者かどうかに限らず、人を登録します。著者かどうかを記録するテーブルはauthorテーブルが利用されます。

今回のアプリでは存在しないですが本の編集者を記録したければ『editor』という中間テーブルを新設すれば、既存のテーブルや列に変更を加えること無く、編集者をデータで表現できるようになります。

SQLで管理

このアプリで一番重要なページです。

今までのページではJavaScriptがSQL文を組み立てて実行してくれていましたが、本アプリでは直接、SQL文を実行できるように『SQLで管理』というページを用意しました。

データを削除するDELETE文や更新を行うUPDATE文など、自由に実行できます。また、データが壊れてしまったときのために、『初期化』のボタンもあるため、データを全部消してしまった場合でも、初期化をすれば最初の状態に戻れます。

SQLのクエリーは短いのでスマートフォンでも十分に打てます、一緒に学習してみましょう。

SQL言語の実習

今回の記事はここからが本番です。一緒にSQL文を実行してリレーショナルデータベースを学習しましょう。

SELECT文を実行してみよう

SELECT文では列名やテーブル名を指定してデータを取得します。

①本の名前を一覧で取得


SELECT name FROM book;

③列名に『*』を利用すると、全列取得できます。


SELECT * FROM book;

列名を指定する際に、実は関数を使うことができます。集約関数max()を使うとテーブルの中で一番大きな値が取得できます。

③一番大きなidを取得


SELECT max(id) FROM book;

max()があるならmin()も存在しますし、平均や中央値を求める関数も使えます。上手く活用できると、JavaScriptなどと連携する際にも、JavaScript側で集計しなくて済むので便利です。

なお、集約関数を使うと列名の戻りが『max(id)』みたいな表記になり、不便です。何か適切な列名を再設定すると、外部のプログラムとの連携がしやすいです。列名の設定はAS句を使います。

④列名に別名を付けつつ一番大きなidを取得し


SELECT max(id) AS maxId FROM book;

同じ列名を二回以上指定することも出来るので、こんなSQLも実行できます。

⑥idの最大値と最小値を同時に取得


SELECT max(id) AS maxId, min(id) AS minId FROM book;

あまりやり過ぎると、SQLにのめり込みすぎて、あだ名をデータベースサーバーにされてしまうかもしれないので、今回はこれぐらいにしておきましょう。

INSERT文を実行してみよう

データを登録する際にはINSERT文を使います。INSERTの際に、列名の列挙が必要なのが注意点です。

①bookテーブルに新しいデータのレコードを追加


INSERT INTO book (name) VALUES ('Monacaのはじめかた');

bookテーブルにはname以外にもISBNや、今回のサンプルでは使っていないstockという在庫数を管理する列があるのですが、データを登録したくない場合は省略可能です。

(列名A,列名B) VALUES (値1,値2)

という形で、VALUESの左辺に列名、右辺に値が列挙されます。

なお、idに関してはテーブル作成の段階で、自動的にid値が連番で振られるように設定済みです。参考までに、後でbookテーブルを作成する命令を解説します。

DELETE文を実行してみよう

データを削除するときにはDELETE文を使います。例えば、サンプルデータのid2として記録されている、『PhoneGap 入門ガイド』が絶版なので消そうと思ったら以下のようになります。

①bookテーブルから指定したidのレコードを削除する


DELETE FROM book WHERE id = 2;

間違って『DELETE FROM book』のようにWHERE句を付け忘れると全部のデータが消えます。

SQLというのは大抵、汎用プログラミング言語の裏側で動いているため、一々、確認なんてできないのです。

ゲームアプリなどで毎回アイテムを消費するたびに『DELETE実行します Yes/ No』なんて出てきたら邪魔でしょうが無いですからね。

心配な場合は先にSELECT文でWHERE句の挙動を確認してからやりましょう。


SELECT * FROM book WHERE id =2;

と書いた後でWHEREの前の部分を書き換えて


DELETE FROM book WHERE id =2;

という形にすれば、間違えることはありません。多分…

UPDATE文を実行してみよう

先ほどINSERTしたid3の本の名前を変えてみることにします。

①指定したidのレコードの本の名前を変更する


UPDATE book SET name = 'Monaca Educationのはじめかた' WHERE id = 3;

SET句で変更したい列名と値を記述します。対象の列が1つだけなら、INSERT文よりは書きやすいかもしれません。

ただし、UPDATE文もDELETEと同様に、WHERE句を付け忘れると全ての列の値が書き換わるので結構危険です。

SQL文は強力すぎるため、データ分析のためにSQL文を実行する際は、本番のシステムのデータベースを直接操作するのではなく、分析用にコピーしたｰデータベースに対して行う方が安全です。

なお、今回のサンプルアプリは個人用なので好きなだけSQLを実験して頂いて問題ありません。

本アプリのテーブル設計

本アプリで使われているテーブルを確認します。
以下はER図というリレーショナルデータベースのテーブル設計で使われる図になります。

bookテーブル

bookテーブルがメインのテーブルになります。
本の名前の他、在庫数やISBN、それと青空文庫の番号を記録できる設計になっています。ただし、今回のアプリで使われて
いるのは名前とISBNだけになります。他の列はアプリのカスタマイズで使うかもしれません。

authorテーブル

著者の情報はauthorテーブルに記録します。
本と著者は多対多の関係になるため、中間テーブルという方法を使って記録する必要があります。このテーブルにはbookのidとpersonのidを記録します。

personテーブル

personテーブルは人の名前を記録するテーブルです。アプリのカスタマイズ用に備考欄としてのnote列が存在します。

また、アプリのカスタマイズ用のテーブルとしてmemoテーブルが存在します。1冊の本に対して複数のメモが設定できるよう、一対多の関係を設定しています。具体的には、memoテーブルにbook_id列という本のidを記録する列を設けることで実現できます。

一対多の関係を作っておくことで、SQLで参照する際にJOIN句を使って特定の本に関連するメモを一括で取得することが可能となります。

【参考】テーブルの作り方

テーブル設計や複数テーブルのJOINは中上級テクニックなので、また、別の機会に詳しく解説したいのですが、参考のためにbookテーブルを例にテーブル作成命令の一部を紹介します。


CREATE TABLE book (
    id serial, 
    name text, 
    stock int, 
    isbn text, 
    aozora int
);

CREATE TABLEという命令でテーブルを作成します。
作りたいテーブルの名前に続けて、列名と列の型のペアを列挙します。

はい、型というものがでてきます。

JavaScriptなどのスクリプト言語だと型は自動的に決まるので馴染みが薄いかもしれませんが、リレーショナルデータベースにおいては非常に重要です。

例えばserial型は自動的に連番を振ってくれるものなので、これがあるとINSERT処理が格段に楽になります。id列を毎回指定しなくても、勝手にリレーショナルデータベースがidに値を入れてくれます。

textは文字列を扱う型です、intは数値を扱う型です。一般的なリレーショナルデータベースは、数値の型のところに文字列を入れようとしてもSQLのクエリーが成立しないので、間違って文字列が入り込んでデータの不整合が起こる、と言うことがありません。

また、浮動小数点数型（float）はプログラミングの際に計算誤差が発生しますが、リレーショナルデータベースには実数を扱う型もあるので、そちらを使えば計算誤差も回避できます。