[trac]会社のtrac環境

火を噴いたプロジェクトも収束に向かってきており、ようやく少し余裕が出てきた。
前のエントリでも少し触れたが、会社のtrac環境を実際どう構築しているか書いてみよう。

データベース環境

PostgreSQLを使用している。データベースは "trac_db" という名称で１つ作り、リポジトリごとにユーザ/スキーマを作成する方針としている。
以下のスクリプトは現在の環境をpgAdminで覗いて書き出してみた。（固有情報は適当に書き換えてあります）

実はPostgresの権限とか、仕組みがよくわかっていないため、そこらへんの指定の仕方は行き当たりばったりでとりあえず使えるようにした感じなので、あまり参考にしないほうがいいかもしれません。
うまい権限設定ポリシーとかあればコメントいただけるとうれしいです。

CREATE TABLESPACE trac_space
  OWNER postgres
  LOCATION '表領域を置く物理ディレクトリ';

CREATE ROLE trac_group
  NOSUPERUSER INHERIT CREATEDB NOCREATEROLE;

CREATE DATABASE trac_db
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = trac_space;
GRANT TEMPORARY ON DATABASE trac_db TO public;
GRANT ALL ON DATABASE trac_db TO trac_group;

新しいリポジトリを作成する際は、以下のようなスクリプトでスキーマとそこに接続するユーザを作成する。(xxxxを作成するリポジトリの名称にあわせて書換）

CREATE ROLE trac_xxxx LOGIN PASSWORD 'trac_xxxx'
  CREATEDB
   VALID UNTIL 'infinity'
   IN ROLE trac_group;
	
CREATE SCHEMA trac_xxxx
  AUTHORIZATION trac_xxxx;
GRANT ALL ON SCHEMA trac_xxxx TO GROUP trac_xxxx;
GRANT ALL ON SCHEMA trac_xxxx TO GROUP trac_group;

PostgreSQLで複数スキーマをまたぐ参照

tracリポジトリが複数存在するため、管理する立場からすると全体を見渡せる仕組みがあるとうれしいので、全スキーマのticketテーブルをUNIONするビューを作成する。
ただし普通に作成すると、リポジトリを追加するたびにビューを修正しなければならず煩わしいので、以下のようなプロシージャを作成した。
この関数にselect文を渡すと、存在する全スキーマに対してそのSQLを実行し、結果をUNIONして返す。
tracに限らず、PostgreSQLで複数スキーマに対する任意のクエリの実行結果を得たいときには使えると思う。

CREATE OR REPLACE FUNCTION query_all_schema(text) RETURNS SETOF record AS
$BODY$
DECLARE
    ref information_schema.schemata%rowtype;
    cur refcursor;
    rec record;
    esql text;
BEGIN
    FOR ref IN select * from information_schema.schemata where schema_owner <> 'postgres'
    LOOP
        esql := replace($1, '$$', ref.schema_name);

        OPEN cur FOR EXECUTE esql ;
        LOOP
            FETCH cur INTO rec;
            EXIT WHEN NOT FOUND;
            RETURN NEXT rec;
        END LOOP;
        CLOSE cur;

    END LOOP;
    RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' STABLE;