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