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]?

YEnter キーを押し、ダウンロードとインストールが完了するまで待ちます。

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));

serialvarcharデータ型 です。 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 moderate

上記のアプローチを使用してpeople(人々)というテーブルを作成します:

電話番号、自宅住所、名前などのフィールドを追加します。上記と同じデータ型のID列も作ったか確認して下さい。

15.2.8. SQLでキーを作成する

上記のソリューションの問題はデータベースが people と streets に論理的な関係があることを知らないことです。この関係を表現するには、streets テーブルの主キーを指す外部キーを定義する必要があります。

../../../_images/er-people-streets.png

これを行うには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?

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を使用して新しいデータを追加する方法を学びます。