一行入魂　2010年05月

アクセスログテーブルを作る

アクセスログテーブルの作り方メモです。

要件
・アクセスログは一ヶ月ごとに別テーブルで管理します。
・アクセスログは1年分は生で保存することにします。

そこで、アクセスログテーブルを偶数年12ヶ月、奇数年12ヶ月の24個のテーブルを作ります。
なぜ2年分必要かというと、例えば6月のアクセスログを入れるためにテーブルをtruncateすると
6月頭では11ヶ月分しか生データが存在しないことになってしまうからです。
テーブル名は以下のようにします。
t_access_log_e01, t_access_log_e02, ... , t_access_log_e12
t_access_log_o01, t_access_log_o02, ... , t_access_log_o12

また、簡単にinsertできるようにPostgreSQLのテーブル継承、パーティションの機能を使います。
詳しくはここを参照してください。
これを使うと
INSERT INTO t_access_log VALUES (1);
と親のテーブルを指定するだけで、適切な子テーブルに保存されるようになります。

まずは、基底となるテーブルの作成です。


DROP TABLE IF EXISTS t_access_log CASCADE;
CREATE TABLE t_access_log
(
    session_id                     BIGINT NOT NULL,
    insert_ts                      TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    user_id                        BIGINT,
    url                            TEXT DEFAULT '' NOT NULL,
    user_agent                     TEXT DEFAULT '' NOT NULL,
    ip_address                     TEXT DEFAULT '' NOT NULL,
    param1                         TEXT DEFAULT '' NOT NULL,
    param2                         TEXT DEFAULT '' NOT NULL,
    param3                         TEXT DEFAULT '' NOT NULL,
    param4                         TEXT DEFAULT '' NOT NULL,
    param5                         TEXT DEFAULT '' NOT NULL,
    bk                             TEXT
);

session_idとinsert_tsが主キーですが、ここでは宣言しません。継承先でインデックスを指定します。
次に継承先のテーブルを作成します。


DROP TABLE IF EXISTS t_access_log_e01 CASCADE;
CREATE TABLE t_access_log_e01 (
    CHECK (0 = date_part('year', insert_ts)::int % 2 AND 1 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_e01 ON t_access_log_e01 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_e02 CASCADE;
CREATE TABLE t_access_log_e02 (
    CHECK (0 = date_part('year', insert_ts)::int % 2 AND 2 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_e02 ON t_access_log_e02 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_e03 CASCADE;
CREATE TABLE t_access_log_e03 (
    CHECK (0 = date_part('year', insert_ts)::int % 2 AND 3 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_e03 ON t_access_log_e03 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_e04 CASCADE;
CREATE TABLE t_access_log_e04 (
    CHECK (0 = date_part('year', insert_ts)::int % 2 AND 4 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_e04 ON t_access_log_e04 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_e05 CASCADE;
CREATE TABLE t_access_log_e05 (
    CHECK (0 = date_part('year', insert_ts)::int % 2 AND 5 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_e05 ON t_access_log_e05 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_e06 CASCADE;
CREATE TABLE t_access_log_e06 (
    CHECK (0 = date_part('year', insert_ts)::int % 2 AND 6 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_e06 ON t_access_log_e06 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_e07 CASCADE;
CREATE TABLE t_access_log_e07 (
    CHECK (0 = date_part('year', insert_ts)::int % 2 AND 7 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_e07 ON t_access_log_e07 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_e08 CASCADE;
CREATE TABLE t_access_log_e08 (
    CHECK (0 = date_part('year', insert_ts)::int % 2 AND 8 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_e08 ON t_access_log_e08 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_e09 CASCADE;
CREATE TABLE t_access_log_e09 (
    CHECK (0 = date_part('year', insert_ts)::int % 2 AND 9 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_e09 ON t_access_log_e09 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_e10 CASCADE;
CREATE TABLE t_access_log_e10 (
    CHECK (0 = date_part('year', insert_ts)::int % 2 AND 10 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_e10 ON t_access_log_e10 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_e11 CASCADE;
CREATE TABLE t_access_log_e11 (
    CHECK (0 = date_part('year', insert_ts)::int % 2 AND 11 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_e11 ON t_access_log_e11 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_e12 CASCADE;
CREATE TABLE t_access_log_e12 (
    CHECK (0 = date_part('year', insert_ts)::int % 2 AND 12 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_e12 ON t_access_log_e12 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_o01 CASCADE;
CREATE TABLE t_access_log_o01 (
    CHECK (1 = date_part('year', insert_ts)::int % 2 AND 1 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_o01 ON t_access_log_o01 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_o02 CASCADE;
CREATE TABLE t_access_log_o02 (
    CHECK (1 = date_part('year', insert_ts)::int % 2 AND 2 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_o02 ON t_access_log_o02 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_o03 CASCADE;
CREATE TABLE t_access_log_o03 (
    CHECK (1 = date_part('year', insert_ts)::int % 2 AND 3 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_o03 ON t_access_log_o03 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_o04 CASCADE;
CREATE TABLE t_access_log_o04 (
    CHECK (1 = date_part('year', insert_ts)::int % 2 AND 4 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_o04 ON t_access_log_o04 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_o05 CASCADE;
CREATE TABLE t_access_log_o05 (
    CHECK (1 = date_part('year', insert_ts)::int % 2 AND 5 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_o05 ON t_access_log_o05 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_o06 CASCADE;
CREATE TABLE t_access_log_o06 (
    CHECK (1 = date_part('year', insert_ts)::int % 2 AND 6 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_o06 ON t_access_log_o06 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_o07 CASCADE;
CREATE TABLE t_access_log_o07 (
    CHECK (1 = date_part('year', insert_ts)::int % 2 AND 7 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_o07 ON t_access_log_o07 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_o08 CASCADE;
CREATE TABLE t_access_log_o08 (
    CHECK (1 = date_part('year', insert_ts)::int % 2 AND 8 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_o08 ON t_access_log_o08 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_o09 CASCADE;
CREATE TABLE t_access_log_o09 (
    CHECK (1 = date_part('year', insert_ts)::int % 2 AND 9 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_o09 ON t_access_log_o09 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_o10 CASCADE;
CREATE TABLE t_access_log_o10 (
    CHECK (1 = date_part('year', insert_ts)::int % 2 AND 10 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_o10 ON t_access_log_o10 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_o11 CASCADE;
CREATE TABLE t_access_log_o11 (
    CHECK (1 = date_part('year', insert_ts)::int % 2 AND 11 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_o11 ON t_access_log_o11 (insert_ts, session_id);

DROP TABLE IF EXISTS t_access_log_o12 CASCADE;
CREATE TABLE t_access_log_o12 (
    CHECK (1 = date_part('year', insert_ts)::int % 2 AND 12 = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_o12 ON t_access_log_o12 (insert_ts, session_id);

CHECK制約で適切な年と月を指定しています。

次にinsertの実行前に行われるtriggerの実体のストアードプロシージャです。


CREATE OR REPLACE FUNCTION t_access_log_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
  IF FALSE THEN
    NULL;
  ELSIF 0 = date_part('year', NEW.insert_ts)::int % 2 AND 1 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_e01 VALUES (NEW.*);
  ELSIF 0 = date_part('year', NEW.insert_ts)::int % 2 AND 2 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_e02 VALUES (NEW.*);
  ELSIF 0 = date_part('year', NEW.insert_ts)::int % 2 AND 3 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_e03 VALUES (NEW.*);
  ELSIF 0 = date_part('year', NEW.insert_ts)::int % 2 AND 4 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_e04 VALUES (NEW.*);
  ELSIF 0 = date_part('year', NEW.insert_ts)::int % 2 AND 5 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_e05 VALUES (NEW.*);
  ELSIF 0 = date_part('year', NEW.insert_ts)::int % 2 AND 6 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_e06 VALUES (NEW.*);
  ELSIF 0 = date_part('year', NEW.insert_ts)::int % 2 AND 7 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_e07 VALUES (NEW.*);
  ELSIF 0 = date_part('year', NEW.insert_ts)::int % 2 AND 8 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_e08 VALUES (NEW.*);
  ELSIF 0 = date_part('year', NEW.insert_ts)::int % 2 AND 9 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_e09 VALUES (NEW.*);
  ELSIF 0 = date_part('year', NEW.insert_ts)::int % 2 AND 10 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_e10 VALUES (NEW.*);
  ELSIF 0 = date_part('year', NEW.insert_ts)::int % 2 AND 11 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_e11 VALUES (NEW.*);
  ELSIF 0 = date_part('year', NEW.insert_ts)::int % 2 AND 12 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_e12 VALUES (NEW.*);
  ELSIF 1 = date_part('year', NEW.insert_ts)::int % 2 AND 1 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_o01 VALUES (NEW.*);
  ELSIF 1 = date_part('year', NEW.insert_ts)::int % 2 AND 2 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_o02 VALUES (NEW.*);
  ELSIF 1 = date_part('year', NEW.insert_ts)::int % 2 AND 3 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_o03 VALUES (NEW.*);
  ELSIF 1 = date_part('year', NEW.insert_ts)::int % 2 AND 4 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_o04 VALUES (NEW.*);
  ELSIF 1 = date_part('year', NEW.insert_ts)::int % 2 AND 5 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_o05 VALUES (NEW.*);
  ELSIF 1 = date_part('year', NEW.insert_ts)::int % 2 AND 6 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_o06 VALUES (NEW.*);
  ELSIF 1 = date_part('year', NEW.insert_ts)::int % 2 AND 7 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_o07 VALUES (NEW.*);
  ELSIF 1 = date_part('year', NEW.insert_ts)::int % 2 AND 8 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_o08 VALUES (NEW.*);
  ELSIF 1 = date_part('year', NEW.insert_ts)::int % 2 AND 9 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_o09 VALUES (NEW.*);
  ELSIF 1 = date_part('year', NEW.insert_ts)::int % 2 AND 10 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_o10 VALUES (NEW.*);
  ELSIF 1 = date_part('year', NEW.insert_ts)::int % 2 AND 11 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_o11 VALUES (NEW.*);
  ELSIF 1 = date_part('year', NEW.insert_ts)::int % 2 AND 12 = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_o12 VALUES (NEW.*);
  ELSE
    RAISE EXCEPTION 'Date out of range.  Fix the t_access_log_insert_trigger() function!';
  END IF;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

最初のIF文が怪しいですが、気にしない（笑）
各IF文の条件は、テーブルのCHECK制約とまったく同じです。
最後に、triggerを設定します。


CREATE TRIGGER insert_t_access_log_trigger
  BEFORE INSERT ON t_access_log
  FOR EACH ROW EXECUTE PROCEDURE t_access_log_insert_trigger();

以上で完成です。先ほど記述したINSERT文を実行してみたところ、正しく指定のテーブルに入りました。

最後におまけ。
子テーブルそストアードプロシージャはgroovyで自動生成したので、そのプログラムです。
怪しげなIF文の意味がこのプログラムから読み取れます。
つまり、ちゃんと書くとめんどくさいから（笑）


def apply(src) {
  def val = ""
  (0..1).each { year ->
    (1..12).each { month ->
      if (year == 0) {
        val = "e"
      } else {
        val = "o"
      }
      if (month < 10) {
        val = val + "0"
      }
      val = val + month
      print(src.replaceAll("###", val).replaceAll("!!!", year.toString()).replaceAll("&&&", month.toString()))
    }
  }
}

-- テーブル作成

def table = """\r
DROP TABLE IF EXISTS t_access_log_### CASCADE;
CREATE TABLE t_access_log_### (
    CHECK (!!! = date_part('year', insert_ts)::int % 2 AND &&& = date_part('month', insert_ts))
) INHERITS (t_access_log);
CREATE INDEX ix_t_access_log_### ON t_access_log_### (insert_ts, session_id);

"""

apply(table)

-- ストアードプロシージャ作成

def prefix = """\r
CREATE OR REPLACE FUNCTION t_access_log_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
  IF FALSE THEN
    NULL;
"""

def function = """\r
  ELSIF !!! = date_part('year', NEW.insert_ts)::int % 2 AND &&& = date_part('month', NEW.insert_ts) THEN
    INSERT INTO t_access_log_### VALUES (NEW.*);
"""

def postfix = """\r
  ELSE
    RAISE EXCEPTION 'Date out of range.  Fix the t_access_log_insert_trigger() function!';
  END IF;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;
"""

print(prefix)
apply(function)
print(postfix)

| HOME | »

日	月	火	水	木	金	土
-	-	-	-	-	-	1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31	-	-	-	-	-

日	月	火	水	木	金	土
-	-	-	-	-	-	1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31	-	-	-	-	-

日	月	火	水	木	金	土
-	-	-	-	-	-	1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31	-	-	-	-	-

日	月	火	水	木	金	土
-	-	-	-	-	-	1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31	-	-	-	-	-