Apache + Tomcat + PostgreSQL（PostgreSQL 編）

[サーバの実験室 Slackware]

作成 : 2004/01/02
修正 : 2004/07/21

"サーバの実験室"の検索

Apache + Tomcat + PostgreSQL

Apache（WEB サーバ）と Tomcat（サーブレットコンテナ）と PostgreSQL（JSP・データベースサーバ）を使用して、所有している本のデータベースから題名や著者で検索ができるようにしてみる。 完成したときのイメージは、http://www.nina.jp/collection/ をみてね。

PostgreSQL の入手とインストール

PostgreSQL の入手とインストール、環境変数の設定については、postgresql-7.3.4 を参照。 2004/01/02 現在の最新版は 7.4.1 だが、やりかたは 7.3.4 と同じで大丈夫。

データベースクラスタの初期化

最初にデータベースを置く領域（データベースクラスタ）を初期化する。 -D でデータベースを置くディレクトリを指定する。 -W をつけて、データベースクラスタの管理者である postgres ユーザ（OS が管理する postgres ユーザとは別）のパスワードを設定しておく。 

# su - postgres
$ initdb -D /usr/local/pgsql/data -W

注意！ initdb を実行するときは、postgres ユーザで実行すること。

collection データベースの作成

template1 データベースに接続して、CREATE DATABASE コマンドでデータベースを作成する。 WITH ENCODING で使用する日本語の種類（EUC_JP）を指定している。

SQL コマンドを使用するときは、次の点に注意すること。 

# psql -U postgres -d template1

template1=# CREATE DATABASE collection WITH ENCODING='EUC_JP';
CREATE DATABASE

データベースが作成されたか確認するには、psql コマンドの \l（エル）を実行する。 psql コマンドの終わりには ;（セミコロン）をつけない。 

template1=# \l
        List of databases
    Name    |  Owner   | Encoding
------------+----------+-----------
 collection | postgres | EUC_JP
 template0  | postgres | SQL_ASCII
 template1  | postgres | SQL_ASCII

データベースが作成されたことを確認したら、\q で template1 データベースから切断しておく。 

template1=# \q

参考 データベースを削除するときは、DROP DATABASE コマンドを使用する。 

template1=# DROP DATABASE collection;
DROP DATABASE

mybook 表の作成

作成する表の列名、データ型、制約を次のように決めておく。 データ型や制約については、日本 PostgreSQL ユーザーグループの PostgreSQL 日本語ドキュメントを参照。

列用途題名著者訳者出版社購入日
列名titleauthortranslatorpublisherpurchase
データ型texttexttexttextdate
制約not null----

collection データベースに接続して、CREATE TABLE コマンドで表を作成する。 

# psql -U postgres -d collection

collection=# CREATE TABLE mybook (
collection-#     title text not null,
collection-#     author_1 text,
collection-#     translator_1 text,
collection-#     publisher text,
collection-#     purchase date );
CREATE TABLE

表が作成されたか確認するには、psql コマンドの \dt を実行する。 

collection=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | mybook | table | postgres

表の構成を確認するには、psql コマンドの \d を実行する。 

collection=# \d mybook
     Table "public.mybook"
   Column   | Type | Modifiers
------------+------+-----------
 title      | text | not null
 author     | text |
 translator | text |
 publisher  | text |
 purchase   | date |

参考 表を削除するときは、DROP TABLE コマンドを使用する。 

collection=# DROP TABLE mybook;
DROP TABLE

データの挿入

表にデータを挿入するには、INSERT INTO コマンドを使用する。 title、author、publisher それぞれの列に '新編 銀河鉄道の夜'、'宮沢賢治'、'新潮文庫'というデータ（文字列定数なのでシングルクォートで囲っている）を挿入している。 表を作成するときに定義した列のデータ型と違っていると、エラーになる。 

collection=# INSERT INTO mybook (title,author,publisher)
collection-#     values ('新編 銀河鉄道の夜','宮沢賢治','新潮文庫');
INSERT

挿入されたデータを確認するには、SELECT コマンドを使用する。 

collection=# SELECT * FROM mybook;
       title       |  author  | translator | publisher | purchase
-------------------+----------+------------+-----------+----------
 新編 銀河鉄道の夜 | 宮沢賢治 |            | 新潮文庫  |

CSV 形式のファイルからデータをまとめて挿入するには、COPY コマンドを使用する。 USING DELIMITER で、項目の区切り文字を ,（カンマ）に指定している。 すべての項目を省略することはできず、下の例では角川文庫の後の ,（カンマ）がないとエラーになる。 また、文字列定数はシングルクォートで囲まなくてもいいようだ。 

新編 風の又三郎,宮沢賢治,,新潮文庫,
....

collection=# COPY mybook FROM '/path/filename' USING DELIMITERS ',';
COPY

挿入されたデータを確認するには、SELECT コマンドを使用する。 

collection=# SELECT * FROM mybook;
       title       |  author  | translator | publisher | purchase
-------------------+----------+------------+-----------+----------
 新編 銀河鉄道の夜 | 宮沢賢治 |            | 新潮文庫  |
 新編 風の又三郎   | 宮沢賢治 |            | 新潮文庫  |

ところで、INSERT INTO で挿入した 1 行目のデータと COPY で挿入した 2 行目のデータの translator 列と purchase 列は、同じように見えて違う。 \pset コマンドで、null データを *null* と表示するように設定して、SELECT コマンドを実行してみる。 

collection=# \pset null *null*

collection=# SELECT * FROM mybook;
       title       |  author  | translator | publisher | purchase
-------------------+----------+------------+-----------+----------
 新編 銀河鉄道の夜 | 宮沢賢治 | *null*     | 新潮文庫  | *null*
 新編 風の又三郎   | 宮沢賢治 |            | 新潮文庫  |

INSERT INTO コマンドでは、データを挿入しなかった translator 列と purchase 列は null になる。 COPY コマンドでは、translator 列と purchase 列には長さ 0 の文字列が挿入されている。 COPY コマンドでも WITH NULL AS を指定することで、特定の文字列（たとえば長さ 0 の文字列）を null として挿入することができる。 

collection=# COPY mybook FROM 'path/filename' USING DELIMITERS ',' WITH NULL AS '';
COPY

collection=# \pset null *null*

collection=# SELECT * FROM mybook;
       title       |  author  | translator | publisher | purchase
-------------------+----------+------------+-----------+----------
 新編 銀河鉄道の夜 | 宮沢賢治 | *null*     | 新潮文庫  | *null*
 新編 風の又三郎   | 宮沢賢治 | *null*     | 新潮文庫  | *null*

参考 データを更新するときは、UPDATE コマンドを使用する。 WHERE 句で指定した条件に一致する行だけ更新される。 

collection=# UPDATE mybook
collection-#     SET author = 'みやざわけんじ', publisher = 'しんちょうぶんこ'
collection-#     WHERE author = '宮沢賢治', publisher = '新潮文庫';
UPDATE

collection=# SELECT * FROM mybook;
       title       |     author     | translator |    publisher     | purchase
-------------------+----------------+------------+------------------+----------
 新編 銀河鉄道の夜 | みやざわけんじ |            | しんちょうぶんこ | 
 新編 風の又三郎   | みやざわけんじ |            | しんちょうぶんこ |

参考 データを削除するときは、DELETE コマンドを使用する。 WHERE 句で指定した条件に一致する行だけ更新される。 （WHERE 句を省略すると、表のすべてのデータが削除されるので注意） 

collection=# DELETE FROM mybook
collection-#     WHERE title ~ '銀河鉄道の夜';
DELETE

collection=# SELECT * FROM mybook;
       title       |     author     | translator |    publisher     | purchase
-------------------+----------------+------------+------------------+----------
 新編 風の又三郎   | みやざわけんじ |            | しんちょうぶんこ |

ユーザの作成

SELECT コマンドでデータの表示だけできるユーザ guest を作成する。 通常は guest ユーザで collection データベースに接続させ、データの挿入・更新・削除ができないようにする。

ユーザを作成するには、CREATE USRE コマンドを使用する。 WITH PASSWORD でパスワードを設定しておく。 JDBC を使ってデータベースに接続する際は、パスワードが必要になる（らしい）。 

collection=# CREATE USER guest WITH PASSWORD 'guest'
CREATE USER

ユーザが作成されたか確認するには、システム表 pg_user を見る。 

 usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil| useconfig
----------+----------+-------------+----------+-----------+----------+----------+-----------
 postgres |        1 | t           | t        | t         | ******** |          |
 guest    |      100 | f           | f        | f         | ******** |          |

しかし、guest ユーザで collection データベースに接続して、SELECT コマンドを実行しようとするとエラーになる。 作成したばかりのユーザは表などのオブジェクトに対する操作権限を持っていないため。 

# psql -U guest -d collection

collection=$ SELECT * FROM mybook;
ERROR:  mybook: permission denied

参考 ユーザを削除するときは、DROP USRE コマンドを使用する。 

collection=# DROP USER guest
DROP USER

参考 ユーザのパスワードを変更するときは、ALTER USRE コマンドを使用する。 

collection=# ALTER USER guest WITH PASSWORD='password'
ALTER USER

ユーザへの権限付与

さきほど作成した guest ユーザに対し、mybook 表の SELECT コマンドを実行する権限を付与する。 権限を付与するには、GRANT コマンドを使用する。 

collection=# GRANT SELECT ON mybook TO guest;
GRANT

権限が付与されたか確認するには、\db コマンドを実行する。 

collection=# \dp
  Access privileges for database "collection"
 Schema | Table  |      Access privileges
--------+--------+------------------------------
 public | mybook | {=,postgres=arwdRxt,guest=r}

guest ユーザで collection データベースに接続して SELECT コマンドと DELETE コマンドを実行してみる。 SELECT は成功するが、DELETE は失敗する。 

# psql -U guest -d collection

collection=$ SELECT * FROM mybook;
       title       |  author  | translator | publisher | purchase
-------------------+----------+------------+-----------+----------
 新編 銀河鉄道の夜 | 宮沢賢治 |            | 新潮文庫  |
 新編 風の又三郎   | 宮沢賢治 |            | 新潮文庫  |

collection=$ DELETE FROM mybook;
ERROR:  permission denied for relation mybook

参考 付与した権限を削除するときは、REVOKE コマンドを使用する。 

collection=# REVOKE SELECT ON mybook FROM guest
REVOKE

TCP/IP 接続のための設定

デフォルトの設定では TCP/IP 接続を受け付けないようになっている。 JDBCで接続する場合は、postgresql.conf（/usr/local/pgsql/data）で TCP/IP 接続を許可するよう設定する。 

tcpip_socket = true    <--- ネットワーク経由の接続を許可

また、TCP/IP 接続の場合はパスワードで認証をするよう、pg_hda.conf（/usr/local/pgsql/data）で設定する。 

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD
host    all         all         127.0.0.1         255.255.255.255   md5

JDBC ドライバを使用するための設定

PostgresSQL の JDBC ドライバを使用するには、postgresql.jar（/usr/local/pgsql/share/java）ファイルを Java の CLASSPATH に追加しなければならない。 めんどうなので、$JAVA_HOME/jre/lib/ext ディレクトリに postgresql.jar をコピーしておく。 

# cp /usr/local/pgsql/share/java/postgresql.jar $JAVA_HOME/jre/lib/ext/

データベースのバックアップ

なにかあったときに備えて、データベースのバックアップを取得する。 データベースをバックアップするには、pg_dumpユーティリティを使用する。 バックアップするときは、データベースに対してSELECTを実行する権限を持つユーザを指定する。

次の例では、postgresユーザでSQLにログインして、dumpfileファイルにバックアップする。 バックアップファイルは、ASCIIテキスト。 

# pg_dump -U postgres -d collection > dumpfile

注意！ pg_dumpでは、ユーザのバックアップは取得できない。 （ユーザはデータベースクラスタに所属するため） ユーザのバックアップも取得するには、次のpg_dumpallでデータベースクラスタごとバックアップする。

参考 データベースクラスタ全体のバックアップを取得するには、pg_dumpallユーティリティを使用する。 

# pg_dumpall -U postgres > dumpallfile

データベースのリストア

バックアップファイルから、データをリストアする。 リストアするときは、postgresqlユーザ（スーパーユーザ）を指定する。 

# psql -U postgres -d collection -f dumpfile

[サーバの実験室 Slackware]