15.2. Lesson: データモデルの実装
私たちはすべての理論をカバーしたので新しいデータベースを作成してみましょう。このデータベースは後に続くレッスンの実習で使います。
このレッスンの目標: 必要なソフトウェアをインストールしてサンプルデータベースの実装に使用します。
15.2.1. PostgreSQLのインストール
注釈
You can find PostgreSQL packages and installation instructions for your operating system at https://www.postgresql.org/download/. Please note that the documentation will assume users are running QGIS under Ubuntu.
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 has very good online documentation.
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)
Type Q to exit.
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)
Type Q to exit.
15.2.6. データベースのシェルセッションの開始
このようにして簡単にデータベースに接続することができます:
psql address
psql データベースシェルを終了するには:
\q
シェルのヘルプを見るには:
\?
sql コマンドのヘルプを見るには:
\help
特定のコマンドのヘルプを表示するには(例):
\help create table
See also the 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列も作ったか確認して下さい。
Answer
The SQL required to create the correct people table is:
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 );
The schema for the table (enter \\d people) looks like this:
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)
For illustration purposes, we have purposely omitted the fkey constraint.
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;
In our current example, the above command would not work. Why not?
Answer
The reason the DROP command would not work in this case is because the people table has a Foreign Key constraint to the streets table. This means that dropping (or deleting) the streets table would leave the people table with references to non-existent streets data.
It is possible to 'force' the streets table to be deleted by using the
CASCADE command, but this would also delete the people and any other table
which had a relationship to the streets table. Use with caution!
people テーブルに同じ drop table コマンドを使う場合は正しく削除されるでしょう:
drop table people;
注釈
実際にそのコマンドを入力して people テーブルを削除した場合は、再度作成して下さい。次の演習で必要になります。
15.2.11. pgAdmin III について一言
データベースについて学ぶために非常に有効な方法なので psql プロンプトからSQLコマンドを入力しています。しかし、より早くより簡単に行う方法があります。pgAdminIII をインストールするとGUI上のクリック操作でテーブルの create, drop, alter 等を行うことができます。
Ubuntuではこのようにインストールします:
sudo apt install pgadmin3
pgAdmin III は別のモジュールで詳しく取り上げます。
15.2.12. In Conclusion
真新しいデータベースを完全にゼロから作成する方法を見てきました。
15.2.13. What's Next?
次はDBMSを使用して新しいデータを追加する方法を学びます。