こんにちは，五十嵐です．今回はデータベースを構築します．
主となるテーブルはユーザ(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 を使用したアプリケーションの作成に入っていきます．
参考：
Keyword: Perl Catalyst Secure-SBM SSBM セキュア・ソーシャル・ブックマーク
ikarashiさんのエントリ配信
主となるテーブルはユーザ(Users)とブックマーク(Bookmarks)，ロール(Roles)，タグ(Tags)の 4 つで考えます．「ロール」 というのは今まで出てきませんでしたが， 「役割」 という意味です．Catalyst のマニュアルを読んでいると，Role という言葉が良く使われていますし，情報セキュリティの分野でも頻繁に使われる言葉ですので， 「グループ」 の代わりに 「ロール」 と呼ぶことにします．概念的にはグループと同じものだと考えてください．
Rinza
開発日記