重要
翻訳は あなたが参加できる コミュニティの取り組みです。このページは現在 86.42% 翻訳されています。
15.2. レッスン: データモデルの実装
すべての理論をカバーしたところで新しいデータベースを作ってみましょう。このデータベースは後に続くレッスンの実習で使います。
このレッスンの目標: 必要なソフトウェアをインストールしてサンプルデータベースの実装に使用します。
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
Then create a streets table:
create table streets (id serial not null primary key, name varchar(50));
serial and varchar are data types. serial tells
PostgreSQL to start an integer sequence (auto-number) to populate the id
automatically for every new record. varchar(50) tells PostgreSQL to
create a character field of 50 characters in length.
You will notice that the command ends with a ; - all SQL commands should
be terminated this way. When you press Enter, psql will report something
like this:
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
That means your table was created successfully, with a primary key streets_pkey
using streets.id.
Note: If you hit return without entering a ;, then you will get a prompt like
this: address-#. This is because PostgreSQL is expecting you to enter more. Enter
; to run your command.
テーブルのスキーマを表示するにはこうします:
\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)
ご覧のようにテーブルは現在空です。
やってみよう: ★★☆ （中級レベル）
上記のアプローチを使用して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);
That tells the people table that its street_id fields must match
a valid street id from the streets table.
より一般的には制約の作成はテーブルの作成時に行います:
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でテーブルを削除する
If you want to get rid of a table you can use the DROP command:
drop table streets;
現在の例では、上記のコマンドは機能しません。なぜダメなのでしょう？
答え
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!
If you used the same drop table command on the people table, it would
be successful:
drop table people;
注釈
If you actually did enter that command and dropped the people
table, now would be a good time to rebuild it, as you will need it in the
next exercises.
15.2.11. pgAdmin III について一言
データベースについて学ぶために非常に有効な方法なので psql プロンプトからSQLコマンドを入力しています。しかし、より早くより簡単に行う方法があります。pgAdmin III をインストールするとGUI上のクリック操作でテーブルの create, drop, alter 等を行うことができます。
Ubuntuではこのようにインストールします:
sudo apt install pgadmin3
pgAdmin III は別のモジュールで詳しく取り上げます。
15.2.12. 結論
真新しいデータベースを完全にゼロから作成する方法を見てきました。
15.2.13. 次は?
次はDBMSを使用して新しいデータを追加する方法を学びます。