15.2. Lesson: データモデルの実装
すべての理論をカバーしたところで新しいデータベースを作ってみましょう。このデータベースは後に続くレッスンの実習で使います。
このレッスンの目標: 必要なソフトウェアをインストールしてサンプルデータベースの実装に使用します。
15.2.1. PostgreSQLのインストール
注釈
お使いのオペレーティングシステムに対応したPostgreSQLのパッケージとインストール方法は、https://www.postgresql.org/download/ でご覧いただけます。なお、このドキュメントでは、ユーザーがUbuntuでQGISを実行していることを想定しています。
Ubuntuで:
sudo apt install postgresql-9.1
このようなメッセージを取得するはずです:
[sudo] password for qgis:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
postgresql-client-9.1 postgresql-client-common postgresql-common
Suggested packages:
oidentd ident-server postgresql-doc-9.1
The following NEW packages will be installed:
postgresql-9.1 postgresql-client-9.1 postgresql-client-common postgresql-common
0 upgraded, 4 newly installed, 0 to remove and 5 not upgraded.
Need to get 5,012kB of archives.
After this operation, 19.0MB of additional disk space will be used.
Do you want to continue [Y/n]?
Y と Enter キーを押し、ダウンロードとインストールが完了するまで待ちます。
15.2.2. ヘルプ
PostgreSQLにはとても良い オンライン ドキュメントがあります。
15.2.3. データベースユーザーの作成
Ubuntuで:
インストールが完了したらこのコマンドを実行して postgres ユーザーになり、新しいデータベースユーザーを作成します:
sudo su - postgres
入力を求められたら通常のログインパスワードを入力します (sudo権限を持っている必要があります)。
では、postgres ユーザーでの bash プロンプトでデータベースユーザーを作成します。ユーザー名は unix ログイン名と一致させて下さい。そうするとログインする時に postgres が自動的に認証するのでいろいろと楽になります:
createuser -d -E -i -l -P -r -s qgis
入力を求められたらパスワードを入力します。ログインパスワードとは異なるパスワードを使用するべきです。
これらのオプションはどういう意味ですか?
-d, --createdb role can create new databases
-E, --encrypted encrypt stored password
-i, --inherit role inherits privileges of roles it is a member of (default)
-l, --login role can login (default)
-P, --pwprompt assign a password to new role
-r, --createrole role can create new roles
-s, --superuser role will be superuser
今、入力することにより、postgresユーザーのbashシェル環境を残す必要があります:
exit
15.2.4. 新しいアカウントの確認
psql -l
このように返されるはずです:
Name | Owner | Encoding | Collation | Ctype |
----------+----------+----------+------------+------------+
postgres | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
template0 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
template1 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
(3 rows)
Q を入力して終了します。
15.2.5. データベースの作成
createdb コマンドは新しいデータベースを作成するのに使います。これはbash シェルプロンプトから実行しましょう:
createdb address -O qgis
このコマンドを使用して新しいデータベースの存在を確認できます:
psql -l
このように返されるはずです:
Name | Owner | Encoding | Collation | Ctype | Access privileges
----------+----------+----------+------------+------------+-----------------------
address | qgis | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
postgres | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
template0 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 | =c/postgres: postgres=CTc/postgres
template1 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 | =c/postgres: postgres=CTc/postgres
(4 rows)
Q を入力して終了します。
15.2.6. データベースのシェルセッションの開始
このようにして簡単にデータベースに接続することができます:
psql address
psql データベースシェルを終了するには:
\q
シェルのヘルプを見るには:
\?
sql コマンドのヘルプを見るには:
\help
特定のコマンドのヘルプを表示するには(例):
\help create table
Psql cheat sheet も参照してください。
15.2.7. SQLでテーブルを作成する
いくつかのテーブルを作ってみましょう! ガイドとしてER図を使用します。まず、address（住所）データベースに接続します:
psql address
streets （街路）テーブルを作成します:
create table streets (id serial not null primary key, name varchar(50));
serial と varchar は データ型 です。 serial は新しいレコードのそれぞれに id を自動的に設定するために PostgreSQLに整数連番（自動付番）を開始させます。 varchar(50) は PostgreSQLに長さ50文字の文字列フィールドを作成させます。
コマンドが ; で終わっていることに気づきましたか。すべてのSQLコマンドはこのように終わるべきです。 Enter キーを押すと psql は次のように報告します:
NOTICE: CREATE TABLE will create implicit sequence "streets_id_seq"
for serial column "streets.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"streets_pkey" for table "streets"
CREATE TABLE
streets.id を使用する主キー streets_pkey を持つテーブルが正しく作成されました。
注: ; を入力せずに Enterキーを押すと address-# のようなプロンプトが表示されます。PGはさらなる入力を期待しています。コマンドを実行するには ; を入力して下さい。
テーブルのスキーマを表示するにはこうします:
\d streets
このように表示されるはずです:
Table "public.streets"
Column | Type | Modifiers
--------+-----------------------+--------------------------------------
id | integer | not null default
| | nextval('streets_id_seq'::regclass)
name | character varying(50) |
Indexes:
"streets_pkey" PRIMARY KEY, btree (id)
テーブルの内容を表示するにはこうします:
select * from streets;
このように表示されるはずです:
id | name
---+------
(0 rows)
ご覧のようにテーブルは現在空です。
Try Yourself 
上記のアプローチを使用してpeople（人々）というテーブルを作成します:
電話番号、自宅住所、名前などのフィールドを追加します。上記と同じデータ型のID列も作ったか確認して下さい。
答え
正しいpeopleテーブルを作るSQLは次の通り:
create table people (id serial not null primary key,
name varchar(50),
house_no int not null,
street_id int not null,
phone_no varchar null );
テーブルのスキーマ（\\d people を入力）は次のようなものです:
Table "public.people"
Column | Type | Modifiers
-----------+-----------------------+-------------------------------------
id | integer | not null default
| | nextval('people_id_seq'::regclass)
name | character varying(50) |
house_no | integer | not null
street_id | integer | not null
phone_no | character varying |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
説明のために、あえてfkey制約を省略しています。
15.2.8. SQLでキーを作成する
上記のソリューションの問題はデータベースが people と streets に論理的な関係があることを知らないことです。この関係を表現するには、streets テーブルの主キーを指す外部キーを定義する必要があります。
これを行うには2つの方法があります:
テーブル作成後にキーを追加する
テーブル作成時にキーを定義する
テーブルは既に作成されているので最初の方法を採ります:
alter table people
add constraint people_streets_fk foreign key (street_id) references streets(id);
people テーブルの street_id フィールドは streets テーブルの有効な街路 id と一致しなければならないことを指示します。
より一般的には制約の作成はテーブルの作成時に行います:
create table people (id serial not null primary key,
name varchar(50),
house_no int not null,
street_id int references streets(id) not null,
phone_no varchar null);
\d people
制約を追加した後、テーブルのスキーマはこのようになります:
Table "public.people"
Column | Type | Modifiers
-----------+-----------------------+---------------------------------
id | integer | not null default
| | nextval('people_id_seq'::regclass)
name | character varying(50) |
house_no | integer | not null
street_id | integer | not null
phone_no | character varying |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"people_streets_fk" FOREIGN KEY (id) REFERENCES streets(id)
15.2.9. SQLでインデックスを作成する
人の名前をすばやく検索できるようにするにはpeople（人々）テーブルのname（名前）列にインデックスを作成します:
create index people_name_idx on people(name);
\d people
その結果:
Table "public.people"
Column | Type | Modifiers
-----------+-----------------------+-----------------------------------
id | integer | not null default nextval
| | ('people_id_seq'::regclass)
name | character varying(50) |
house_no | integer | not null
street_id | integer | not null
phone_no | character varying |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
"people_name_idx" btree (name) <-- new index added!
Foreign-key constraints:
"people_streets_fk" FOREIGN KEY (id) REFERENCES streets(id)
15.2.10. SQLでテーブルを削除する
テーブルを取り除きたい場合は drop コマンドを使用します:
drop table streets;
現在の例では、上記のコマンドは機能しません。なぜダメなのでしょう？
答え
この場合、DROPコマンドが機能しないのは、people テーブルが streets テーブルに対して外部キー制約を持っているためです。つまり、streets テーブルをドロップ（または削除）すると、存在しない*streets* データへの参照が people テーブルに残ってしまうのです。
CASCADE コマンドを使用することで、streets テーブルを「強制的に」削除することは可能ですが、この場合、streets テーブルと関係がある people やその他のテーブルも削除されます。注意して使用してください！
people テーブルに同じ drop table コマンドを使う場合は正しく削除されるでしょう:
drop table people;
注釈
実際にそのコマンドを入力して people テーブルを削除した場合は、再度作成して下さい。次の演習で必要になります。
15.2.11. pgAdmin III について一言
データベースについて学ぶために非常に有効な方法なので psql プロンプトからSQLコマンドを入力しています。しかし、より早くより簡単に行う方法があります。pgAdmin III をインストールするとGUI上のクリック操作でテーブルの create, drop, alter 等を行うことができます。
Ubuntuではこのようにインストールします:
sudo apt install pgadmin3
pgAdmin III は別のモジュールで詳しく取り上げます。
15.2.12. In Conclusion
真新しいデータベースを完全にゼロから作成する方法を見てきました。
15.2.13. What's Next?
次はDBMSを使用して新しいデータを追加する方法を学びます。