15.2. Lesson: データモデルの実装¶
私たちはすべての理論をカバーしたので新しいデータベースを作成してみましょう。このデータベースは後に続くレッスンの実習で使います。
このレッスンの目標: 必要なソフトウェアをインストールしてサンプルデータベースの実装に使用します。
15.2.1. PostgreSQLのインストール¶
ノート
このドキュメントの範囲外ではありますが、Macユーザは Homebrew を用いてPostgreSQLをインストールすることができます。Windowsユーザはここにあるグラフィカルなインストーラを使うことができます: http://www.postgresql.org/download/windows/ このドキュメントではUbuntuでQGISを動作させているユーザを想定しています。
Ubuntuで:
sudo apt-get 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 and Enter キーを押し、ダウンロードとインストールが完了するまで待ちます。
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)
ご覧のようにテーブルは現在空です。
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;
ノート
現在の例では上記のコマンドは動作しないでしょう。なぜでしょうか。 こちら を参照して下さい。
people テーブルに同じ drop table コマンドを使う場合は正しく削除されるでしょう:
drop table people;
ノート
実際にそのコマンドを入力して people テーブルを削除した場合は、再度作成して下さい。次の演習で必要になります。
15.2.11. pgAdmin III 上の単語¶
データベースについて学ぶために非常に有効な方法なので psql プロンプトからSQLコマンドを入力しています。しかし、より早くより簡単に行う方法があります。pgAdminIII をインストールするとGUI上のクリック操作でテーブルの create, drop, alter 等を行うことができます。
Ubuntuではこのようにインストールします:
sudo apt-get install pgadmin3
pgAdmin III は別のモジュールで詳しく取り上げます。
15.2.12. In Conclusion¶
真新しいデータベースを完全にゼロから作成する方法を見てきました。
15.2.13. What’s Next?¶
次はDBMSを使用して新しいデータを追加する方法を学びます。