7つのデータベース7つの世界を読んだ

タイトルは半分嘘で、全部じゃなくて途中まで読んだ。具体的には、第2章のPostgreSQL、第3章のRiak、第4章のHBase(Hadoop)の1,2日目、第5章のMongoDB、第8章のRedisの1,2日目。CouchDBとNeo4jはイントロだけ。

PostgreSQL

postgresqlは一応慣れ親しんでる(?)はずだけど、知らない事・使った事の無い機能がいっぱい載ってた。というか、普段はActiveRecord越しでイジるかデータの確認にシェルでselectするかぐらいでしか使ってなかったので、実質的には慣れ親しんで無かったんだと思う。

2日目で言うと、ストアドプロシージャ、トリガー、ビュー、クロス集計は使った事が無い機能だった。


ストアドプロシージャとは

DB側である程度まとまった処理を行いたい時に、プロシージャとして名前をつけて処理を保存しておける機能。「7つのデータベース 7つの世界」では、例としてテーブルA上に特定のレコードaがあるかを確認して、無ければaを作成してからテーブルB上にaと関連づけられたレコードbを追加するというストアドプロシージャのコードが載っていた。レコードの「確認(select)」と「追加(insert)」を1回でまとめて行えるので、パフォーマンスも上がるらしい。

ちょっと自分でもコードを書いてみようと思う。

データベースを作る。

いろいろ試すために、まずは練習用のデータベースを作成する。psqlコマンドでシェルを立ち上げて、以下のコマンドを実行。seven_databaseテーブルが作られる。

user=# create database seven_database; // terminalでcreatedbでも良い
CREATE DATABASE
user=# \c seven_database
You are now connected to database "seven_database" as user "(user_name)".

これでOK。次に、contribパッケージとしてcube等が必要になるので、インストールする。一番楽なのはCREATE EXTENSION (パッケージ名)でインストールする事らしいので、tablefunc, dict_xsyn, fuzzystrmatch, pg_trgm, cubeについて順に実行してインストール。

現在のデータベースにインストールされたパッケージは、pg_extensionビューで確認できる。

seven_database=# select extname from pg_catalog.pg_extension ;
    extname
---------------
 plpgsql
 tablefunc
 dict_xsyn
 fuzzystrmatch
 pg_trgm
 cube
(6 rows)

ちゃんとインストールされている事が分かる。

次に、テーブルとして7つのデータエース 7つの世界に載ってたgenres, movies, actors, movie_actorsテーブルを作る。

このページにおいてあるコードをダウンロードしてpsql -d seven_database < create_movies.sqlで実行するか、シェルから以下のSQLを実行。

CREATE TABLE genres (
    name text UNIQUE,
    position integer
);
CREATE TABLE movies (
    movie_id SERIAL PRIMARY KEY,
    title text,
    genre cube
);
CREATE TABLE actors (
    actor_id SERIAL PRIMARY KEY,
    name text
);

CREATE TABLE movies_actors (
    movie_id integer REFERENCES movies NOT NULL,
    actor_id integer REFERENCES actors NOT NULL,
    UNIQUE (movie_id, actor_id)
);
CREATE INDEX movies_actors_movie_id ON movies_actors (movie_id);
CREATE INDEX movies_actors_actor_id ON movies_actors (actor_id);
CREATE INDEX movies_genres_cube ON movies USING gist (genre);

これでOK。

ストアドプロシージャを作ってみる

とりあえず書籍をパクって、moviesテーブルにレコードがあるかを確認して、無ければ作成してからそれに関連付けられたレコードをactorsテーブルにinsertしてみる。

  1 CREATE OR REPLACE FUNCTION add_actor_with_movie( actor_name text, movie_title text, movie_genre cube )
  2 RETURNS boolean AS $$
  3 DECLARE
  4   did_insert   boolean := false;
  5   found_count  integer;
  6   the_movie_id integer;
  7   the_actor_id integer;
  8 BEGIN
  9   SELECT movie_id INTO the_movie_id
 10   FROM movies m
 11   WHERE m.title=movie_title AND m.genre=movie_genre
 12   LIMIT 1;
 13
 14   IF the_movie_id IS NULL THEN
 15     INSERT INTO movies (title, genre)
 16     VALUES (movie_title, movie_genre)
 17     RETURNING movie_id INTO the_movie_id;
 18
 19     did_insert := true;
 20   END IF;
 21
 22   -- Note: not an “error”, as in some programming languages
 23   RAISE NOTICE 'Venue found %', the_movie_id;
 24
 25   INSERT INTO actors (name)
 26   VALUES (actor_name)
 27   RETURNING actor_id INTO the_actor_id;
 28
 29   INSERT INTO movies_actors(movie_id, actor_id)
 30   VALUES (the_movie_id, the_actor_id);
 31
 32   RETURN did_insert;
 33 END;
 34 $$ LANGUAGE plpgsql;

とりあえずこんな感じで書いたらちゃんと動いた。32行目でRETURN did_insertしてて、実際にmovieのinsertを行った場合はtが返ってきてる。

seven_database=# SELECT add_actor_with_movie('Taro', 'Star Wars','(0,7,0,0,0,0,0,0,0,7,0,0,0,0,10,0,0,0)');
NOTICE:  Venue found 2
 add_actor_with_movie
----------------------
 t
(1 row)

'Star Wars'に違うactorを紐づけて実行すると、ちゃんとactorはinsertされるけど返り値はfになる。

seven_database=# SELECT add_actor_with_movie('Jiro', 'Star Wars','(0,7,0,0,0,0,0,0,0,7,0,0,0,0,10,0,0,0)');
NOTICE:  Venue found 2
 add_actor_with_movie
----------------------
 f
(1 row)

とりあえず想定通りの挙動はしてる。

ハマりポイント

ストアドプロシージャの引数名がtableのカラム名と一致してるとエラーが出た。下の実行例は、ストアドプロシージャの引数名としてtitleを使ってたら、movie tableのtitleカラムと同じって事でエラーが出てる。

seven_database=# SELECT add_actor_with_movie('Saburo', 'Star Wars','(0,7,0,0,0,0,0,0,0,7,0,0,0,0,10,0,0,0)');
ERROR:  column reference "title" is ambiguous
LINE 2:   WHERE m.title=title AND m.genre=movie_genre
                        ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT movie_id                     FROM movies m
  WHERE m.title=title AND m.genre=movie_genre
  LIMIT 1
CONTEXT:  PL/pgSQL function add_actor_with_movie(text,text,cube) line 8 at SQL statement

これをErrorにするのは許せない。。。

あと、途中でいくつか作り替えながら試してみたら、同じadd_actor_with_movieという名前のストアドプロシージャを複数作ってしまって削除の必要が生じた。どうやら、引数の型が違うと同じ名前でも別のプロシージャとして保存されるっぽい。

このページいわく

drop function プロシージャ名([引数の型])

で削除できたので、drop function add_actor_with_movie(text, text, cube);で削除した。あと、引数の型は変えずに名前だけ変えようとすると既存のプロシージャを消してくれってエラーが出た。

seven_database=# \i my_code/add_actor_with_movie.sql
psql:my_code/add_actor_with_movie.sql:34: ERROR:  cannot change name of input parameter "title"
HINT:  Use DROP FUNCTION add_actor_with_movie(text,text,cube) first.

いったんまとめ

学んだ機能を順に説明してくつもりがストアドプロシージャだけでそこそこ時間たっちゃったので、今日はここまで。ストアドプロシージャは強力だけどデータベースにロジックが激しく依存してしまうので、使いどころは難しそう。覚悟の上で使うなら良いと思う。