V.S.A. III

[Secure-SBM:005] データベース構築 (PostgreSQL 設定)

こんにちは，五十嵐です．今回はデータベースを構築します．

主となるテーブルはユーザ(Users)とブックマーク(Bookmarks)，ロール(Roles)，タグ(Tags)の 4 つで考えます．「ロール」というのは今まで出てきませんでしたが，「役割」という意味です．Catalyst のマニュアルを読んでいると，Role という言葉が良く使われていますし，情報セキュリティの分野でも頻繁に使われる言葉ですので，「グループ」の代わりに「ロール」と呼ぶことにします．概念的にはグループと同じものだと考えてください．

図は大まかにデータベースのテーブルを表したものです．主テーブルの 4 つを関連づける，4 つのテーブルを持つことにします．

User_Roles: ユーザとロールの関連付けを行います．
User_Bookmarks: ブックマークとユーザの関連付けを行い，ユーザのコメントを格納します．
Bookmark_Roles: ブックマークとロールの関連付けを行います．
User_Bookmark_Tags: ユーザとブックマークおよびタグの関連付けを行います．
Role_Owners: ロールのオーナーとしてユーザを関連付けます．

データベースは "ssbmdb" と名付けます．データベースエンジンは (Catalyst がサポートしていれば) なんでもかまいませんが，Catalyst と相性がよいと思われる PostgreSQL を使用します．

データベースには初期値としていくつかのデータを入力しておきます．

ユーザのログインIDは e-mail アドレスとしますが，特殊なケースとして "admin" を登録します．これは管理者ユーザで，特権を持ち，全体の管理権限を持ちます．このユーザを削除することをできないように実装します．初期パスワードは "admin" です．
ロールには "admin" と "public" を登録します．それぞれ，特権ロールと一般公開用ロールです．基本的に公開可能なブックマークは "public" に登録され，すべてのユーザは "public" のロールを持ちます．
タグには "tyzoh" を登録します．
ブックマークには，この Tyzoh ブログの URL と，Tyzoh のトップページが登録されます．
これらをそれぞれ関連付するように初期値を設定します．

まず，PostgreSQL を起動するところから始めます．話が前後しますが，Linux (Fedora Core 6) を使用して開発を進めます．rpm や yum などを使用して，postgresql と postgresql-server がインストールされていることを確認してください．

$ rpm -q postgresql
postgresql-8.1.8-1.fc6
$ rpm -q postgresql-server
postgresql-server-8.1.8-1.fc6

と実行してそれぞれが表示されればインストールされています．もしインストールされていない場合は，yum コマンドを使用して postgresql-server をインストールすれば，postgresql などの関連するパッケージも同時にインストールされます．スーパーユーザー(root)で

# yum -y install postgresql-server

と実行すればインストールされるはずです．

インストールしただけではまだ PostgreSQL は使用できません．システムの初期化時にサーバーが立ち上がるように設定します．スーパーユーザ(root)にて．

# chkconfig postgresql on
# chkconfig --list postgresql
postgresql      0:off   1:off   2:on    3:on    4:on    5:on    6:off
# (cd /; env - /etc/rc.d/init.d/postgresql start)
データベースを初期化中:                                    [  OK  ]
postgresql サービスを開始中:                               [  OK  ]

これで PostgreSQL が使用可能になりました．ちなみに "cd /" とするのは，プログラムから作業ディレクトリ(カレントディレクトリ)を切り離すためで，"env - " と付けるのは，ユーザの環境変数の設定が影響を与えないようにするためです．最近のサーバープログラム(デーモン)はよくできていますので，これらの心配は必要ないかもしれません．歴史的に UNIX のデーモンにはカレントディレクトリを掴んだままであったり，起動時の環境変数が動作に影響するものがありました．それを避けるための方策です．さらに余談ですが，"env" コマンドは引数に "-" を付けるとすべての環境変数を無効にします．逆に "env hogehoge=gerogero command" とすると，指定した環境変数 (この場合は "hogehoge") を export した状態で実行してくれます．

次に，PostgreSQL の実行環境の設定を確認します．私の環境では PostgreSQL の設定ファイルは "/var/lib/pgsql/data" にあります．このなかで，pg_hba.conf ファイルを編集します．ユーザの確認に IDENT を使用しないようにするためです．PostgreSQL を良く知っている方は，それなりに設定してください．とりあえず，どのユーザでも使用できるように設定します．

*** pg_hba.conf.org     2007-03-19 17:33:48.000000000 +0900
--- pg_hba.conf 2007-03-19 17:33:48.000000000 +0900
***************
*** 64,70 ****
  # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
  
  # "local" is for Unix domain socket connections only
! local   all         all                               ident sameuser
  # IPv4 local connections:
  host    all         all         127.0.0.1/32          ident sameuser
  # IPv6 local connections:
--- 64,71 ----
  # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
  
  # "local" is for Unix domain socket connections only
! #local   all         all                               ident sameuser
! local   all         all                               trust
  # IPv4 local connections:
  host    all         all         127.0.0.1/32          ident sameuser
  # IPv6 local connections:

ここでは，ローカルユーザはすべて信用するように設定しました．この設定ファイルを変更した後，PostgreSQL を再起動しておきます．

次に，データベースにアクセスするためのユーザを作成します．PostgreSQL にアクセスするためのユーザを作成するには， "postgres" ユーザになる必要があります．

$ sudo su - postgres
-bash-3.1$ createuser -SdRW ssbm
Password: <= ここでパスワードを入力します．今回は "ssbm" を入力しました．
CREATE ROLE
-bash-3.1$

続けてデータベースを作成しておきます．

-bash-3.1$ psql
Welcome to psql 8.1.8, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# CREATE DATABASE ssbmdb OWNER ssbm;
CREATE DATABASE
postgres=# \q

-bash-3.1$ exit
logout
$

この後，開発用のユーザに戻って，データベースのテーブルを作成します．

createPostgreSQLTables.sql:

CREATE TABLE users (
        user_id                 SERIAL,
        user_email              TEXT NOT NULL,
        user_password   TEXT,
        user_name               TEXT,
        user_surname    TEXT,
        PRIMARY KEY(user_id),
        UNIQUE (user_email)
);

CREATE TABLE roles (
        role_id         SERIAL,
        role_name       TEXT NOT NULL,
        role_refcount   INTEGER DEFAULT 1,
        PRIMARY KEY(role_id),
        UNIQUE (role_name)
);

CREATE TABLE bookmarks (
        bookmark_id             SERIAL,
        bookmark_uri    TEXT NOT NULL,
        bookmark_title  TEXT NOT NULL,
        bookmark_refcount       INTEGER DEFAULT 1,
        PRIMARY KEY(bookmark_id),
        UNIQUE (bookmark_uri)
);

CREATE TABLE tags (
        tag_id          SERIAL,
        tag_name        TEXT NOT NULL,
        tag_refcount    INTEGER DEFAULT 1,
        PRIMARY KEY(tag_id),
        UNIQUE (tag_name)
);

CREATE TABLE user_roles (
        user_role_id    SERIAL,
        user_id                 INTEGER,
        role_id                 INTEGER,
        PRIMARY KEY(user_role_id, user_id, role_id),
        FOREIGN KEY(user_id) REFERENCES users(user_id),
        FOREIGN KEY(role_id) REFERENCES roles(role_id)
);

CREATE TABLE user_bookmarks (
        user_bookmark_id        SERIAL,
        user_id                         INTEGER,
        bookmark_id                     INTEGER,
        user_bookmark_ts        TIMESTAMP WITH TIME ZONE DEFAULT now(),
        user_bookmark_comment   TEXT,
        PRIMARY KEY(user_bookmark_id),
        FOREIGN KEY(user_id) REFERENCES users(user_id),
        FOREIGN KEY(bookmark_id) REFERENCES bookmarks(bookmark_id)
);

CREATE TABLE user_bookmark_tags (
        user_bookmark_tag_id    SERIAL,
        user_bookmark_id                INTEGER,
        tag_id                                  INTEGER,
        PRIMARY KEY(user_bookmark_tag_id, user_bookmark_id, tag_id),
        FOREIGN KEY(user_bookmark_id)
                REFERENCES user_bookmarks(user_bookmark_id),
        FOREIGN KEY(tag_id) REFERENCES tags(tag_id)
);

CREATE TABLE user_bookmark_roles (
        user_bookmark_role_id   SERIAL,
        user_bookmark_id                INTEGER,
        role_id                                 INTEGER,
        PRIMARY KEY(user_bookmark_role_id, user_bookmark_id, role_id),
        FOREIGN KEY(user_bookmark_id)
                REFERENCES user_bookmarks(user_bookmark_id),
        FOREIGN KEY(role_id) REFERENCES roles(role_id)
);

CREATE TABLE role_owners (
        role_owner_id   SERIAL,
        role_id                 INTEGER,
        user_id                 INTEGER,
        PRIMARY KEY(role_owner_id, role_id, user_id),
        FOREIGN KEY(role_id) REFERENCES roles(role_id),
        FOREIGN KEY(user_id) REFERENCES users(user_id)
);

CREATE TABLE user_profiles (
        user_profile_id                                 SERIAL,
        user_id                                                 INTEGER,
        user_profile_publish                    INTEGER DEFAULT 0,
        user_profile_nickname                   TEXT,
        user_profile_nickname_publish   INTEGER DEFAULT 0,
        user_profile_surname                    TEXT,
        user_profile_name                               TEXT,
        user_profile_name_publish               INTEGER DEFAULT 0,
        user_profile_birthday                   DATE,
        user_profile_birthday_publish   INTEGER DEFAULT 0,
        user_profile_age_publish                INTEGER DEFAULT 0,
        user_profile_email                              TEXT,
        user_profile_email_publish              INTEGER DEFAULT 0,
        user_profile_introduce                  TEXT,
        user_profile_introduce_publish  INTEGER DEFAULT 0,
        user_profile_updatetime                 
                              TIMESTAMP WITH TIME ZONE DEFAULT now(),
        PRIMARY KEY(user_profile_id, user_id),
        FOREIGN KEY(user_id) REFERENCES users(user_id)
);

CREATE TABLE informations (
        information_id          SERIAL,
        information_title       TEXT,
        information_content     TEXT,
        information_date        DATE,
        information_ts          TIMESTAMP WITH TIME ZONE DEFAULT now(),
        information_publish     INTEGER DEFAULT 0,
        PRIMARY KEY(information_id)
);

CREATE TABLE sideinfos (
        information_id          SERIAL,
        information_title       TEXT,
        information_content     TEXT,
        information_date        DATE,
        information_ts          TIMESTAMP WITH TIME ZONE DEFAULT now(),
        information_publish     INTEGER DEFAULT 0,
        PRIMARY KEY(information_id)
);

CREATE TABLE adminstatus (
        adminstatus_id                          SERIAL,
        adminstatus_info_start          INTEGER,
        adminstatus_info_num            INTEGER,
        adminstatus_sideinfo_start      INTEGER,
        adminstatus_sideinfo_num        INTEGER,
        PRIMARY KEY(adminstatus_id)
);
--
INSERT INTO users (user_email, user_password, user_name, user_surname)
        VALUES (
                'admin',
                'd033e22ae348aeb5660fc2140aec35850c4da997',
                'Administrator',
                'Secure-SBM'
        );

INSERT INTO roles (role_name) VALUES ( 'admin' );
INSERT INTO roles (role_name) VALUES ( 'public' );

INSERT INTO bookmarks (bookmark_uri, bookmark_title)
        VALUES ( 'http://www.tyzoh.jp/', 'Tyzoh (タイゾウ)');
INSERT INTO bookmarks (bookmark_uri, bookmark_title)
        VALUES (
                'http://www.tyzoh.jp/modules/weblog/',
                'Tyzohブログ Tyzoh (タイゾウ)'
        );

INSERT INTO tags (tag_name, tag_refcount) VALUES ( 'tyzoh', 2 );

INSERT INTO user_roles (user_id, role_id) VALUES ( 1, 1 );
INSERT INTO user_roles (user_id, role_id) VALUES ( 1, 2 );

INSERT INTO user_bookmarks (
                user_id, bookmark_id, user_bookmark_ts, user_bookmark_comment
        ) VALUES ( 1, 1, DEFAULT, '技術サイト' );
INSERT INTO user_bookmarks (
                user_id, bookmark_id, user_bookmark_ts, user_bookmark_comment
        ) VALUES ( 1, 2, DEFAULT, '技術ブログ' );

INSERT INTO user_bookmark_tags (user_bookmark_id, tag_id) VALUES ( 1, 1 );
INSERT INTO user_bookmark_tags (user_bookmark_id, tag_id) VALUES ( 2, 1 );

INSERT INTO user_bookmark_roles (user_bookmark_id, role_id) VALUES ( 1, 2 );
INSERT INTO user_bookmark_roles (user_bookmark_id, role_id) VALUES ( 2, 2 );

INSERT INTO role_owners (role_id, user_id) VALUES ( 1, 1 );
INSERT INTO role_owners (role_id, user_id) VALUES ( 2, 1 );

INSERT INTO user_profiles (
                user_id,
                user_profile_publish,
                user_profile_nickname_publish,
                user_profile_name_publish,
                user_profile_birthday,
                user_profile_birthday_publish,
                user_profile_age_publish,
                user_profile_email_publish,
                user_profile_introduce_publish
        ) VALUES ( 1, 0, 0, 0, 'now', 0, 0, 0, 0 );

INSERT INTO informations (
                information_title,
                information_content,
                information_date,
                information_publish
        ) VALUES (
                'Secure-SBM リリース',
                'Secure-SBM Version 0.1 alpha をリリースしました．<br />
                バグ出しにご協力ください．',
                'now',
                1
        );

INSERT INTO sideinfos (
                information_title,
                information_content,
                information_date,
                information_publish
        ) VALUES (
                '管理者用のページ',
                '管理者用のページは<a href="/admin">こちら</a>から',
                'now',
                1
        );
INSERT INTO sideinfos (
                information_title,
                information_content,
                information_date,
                information_publish
        ) VALUES (
                'Ver.0.1 alpha リリース',
                'SecureSBM Version 0.1 alpha 版をリリースしました．
                バグ出しにご協力ください．',
                'now',
                1
        );

INSERT INTO adminstatus (
                adminstatus_info_start,
                adminstatus_info_num,
                adminstatus_sideinfo_start,
                adminstatus_sideinfo_num
        ) VALUES ( 1, 1, 1, 2 );

/*
This Program is distributed under version 1.0 of the Rinza Public
License Agreement, that is bundled with this package in the file
LICENSE, and is available through the website at the following URL:
http://www.tyzoh.jp/rinza/licenses/LICENSE-1.0.txt.

The Initial Developer of the Original Program is Nihon Unisys, Ltd.
The Original Program is copyrighted (C) 2006-2007 by Nihon Unisys, Ltd.
with all rights reserved.
There is NO WARRANTY OF ANY KIND by the Initial Developer of the
Original Program.
*/

この sql を使用して，データベースを作成します．

$ psql -U ssbm ssbmdb < createPostgreSQLTables.sql 
CREATE TABLE
...(省略)
NOTICE:  CREATE TABLE will create implicit sequence 
"bookmark_roles_bookmark_role_id_seq" for serial column 
"bookmark_roles.bookmark_role_id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"bookmark_roles_pkey" for table "bookmark_roles"
CREATE TABLE
INSERT 0 1
...(省略)
INSERT 0 1
$

以下のような 2 行は無視してかまいません．

NOTICE:  CREATE TABLE will create implicit sequence 
      "users_user_id_seq" for serial column "users.user_id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
      "users_pkey" for table "users"

しかし，他のメッセージがないことは確認しておく必要があります．

作成したテーブルと，登録した内容に間違いがないかどうか，この時点で確認しておきます．

$ cat showPostgreSQLTables.sql 
SELECT * FROM users;
SELECT * FROM roles;
SELECT * FROM bookmarks;
SELECT * FROM tags;
SELECT * FROM user_roles;
SELECT * FROM user_bookmarks;
SELECT * FROM user_bookmark_tags;
SELECT * FROM user_bookmark_roles;
SELECT * FROM role_owners;
SELECT * FROM user_profiles;
SELECT * FROM informations;
SELECT * FROM sideinfos;
SELECT * FROM adminstatus;
$ psql -U ssbm ssbmdb < showPostgreSQLTables.sql 
 user_id |         user_email         |              user_password               
|   user_name   | user_surname 
---------+----------------------------+------------------------------------------+
---------------+--------------
       1 | admin                      | 
d033e22ae348aeb5660fc2140aec35850c4da997 | Administrator | Secure-SBM

(1 row)
...(省略)
$

ここまでで，データベースの作成が終わりました．この後，Catalyst を使用したアプリケーションの作成に入っていきます．

参考：