TOP…＞ プログラム講座…＞応用編…＞PostgreSQL入門

PostgreSQL入門

 　PostgreSQLとは

リレーショナルデーターベースソフトで有名なのは、Microsoft Accessです。これは、Windowsパソコンでスタンドアロンで使うのであれば十分なデーターベースを提供してくれます。しかし、サーバー上で動作し、不特定多数の接続に対してデーターの入出力を受け付けるようにするためには、リレーショナルデーターベースサーバーのソフトが必要となります。

リレーショナルデーターベースサーバーには、有料のMicrosoft SQL server、Oracle等と、無料のPostgreSQL、MySQL等があります。PostgreSQLはそのうちの、無料で提供されているデーターベースサーバーのうちの１つです。

有料のデーターベースサーバーは、ソフトそのものが非常に高価で、さらに同時稼動プロセスに応じたライセンス料を支払わねばならず、運用には相当のお金がかかります。また、Oracleは操作が難しく、特にOracle操作の熟練者には デカマスター オラクルマスターというライセンスを取得できるそうですが、この章はあくまで「ＳＱＬ入門」ですので、そこまで難しい事は取り上げません。

ここでは、PHPやPerl言語を使って最低限のデーターベースへの問い合わせができるようになるために、フリーのデーターベースサーバーであるPostgreSQLの基本を学びましょう。

SQL文
create句
コメント文
列の型
一時テーブル
GRANT句
ALTER句
insert句
update句
delete句
select句
シーケンスの操作
JOIN句
ハッシュ変数との併用
トランザクション

メニューに戻る

　ＳＱＬ文

データーベースは、コマンドライン上から

[user@host ~]$ psql データーベース名

として、コマンドモードに入り、コマンドラインからコマンドやステートメントを入力して操作します。コマンドラインでなくとも、phpPgAdminというソフトがインストールされていれば、ブラウザ上からSELECT文を実行するなどしてデーターを操作する事ができます。また、Ｃ、PHP、Perl等のプログラム上から「ステートメントの実行」コマンドを使ってデーターベースを操作する事もできます。

いずれにしても、ＳＱＬ文の文法をわかってさえいれば、どの言語からでもデーターベースを操作することができるわけです。ここでは、PostgreSQLでよく使うＳＱＬ文の基本について学んでゆきましょう。

先頭に戻る　メニューに戻る

 　create句

create句ではテーブルを作成することができます。

[例]
create table denpyo (
        number serial primary key, 
        hiduke date, 
        tokcode int, 
        tokname text
);

この例では、このようなテーブルが作成されます。


・primary key

その行を一意に表すキー、つまり、その行にアクセスするための、他と絶対にだぶらないキーです。serial型の値はだぶらない事が保障されているため、よく主キーとして使われます。

PostgreSQLにはoidという内部的に管理しているその行を示すキーもあるのですが、これはPostgreSQLのみであり他のＳＱＬとの互換性を考えると、全てのテーブルに主キーを設定しておく方が得策です。

これは筆者の経験則ですが、例えば商品コードとして、A10023というようなコードを使う場合、これを主キーとしてしまっても良いのですが、そうではなく内部で管理するための主キーとしてserial値を連番でとっておいた方が後々便利です。

例えば、もし後から商品コードを変更したい時や、同じ商品コードで別の商品が存在するように仕様変更になった、とか、同じ商品コードで色違いの製品が後からできた、なんて時でも、プログラムの変更箇所が少なくて済みます。また、修正とか削除を行う場合に引き回すキーは商品コードのようなそれ自体が意味のあるコードになっているよりも、連番のようなフィールドを一意に表すそれ自体は意味を持たない数値を引き回したほうが楽だったりします。

・CONSTRAINT

例えば、同じ商品コードが違うカテゴリーには存在するが、同じカテゴリー内で同じ商品コードはありえない、という制約を作ることができます。

CONSTRAINT shohinkey PRIMARY KEY(category, shohincode)

のように定義しておくことで、「カテゴリーと商品コードをあわせてキー」という風に定義することができます。先の例にもあったような、同じ商品コードで色違いが発生した、というような場合は、商品コードと色コードを合わせたキーを設定しておくと便利です。

・デフォルト値

DEFAULTでデフォルト値を指定することができます。たとえば、tanka int4 default 0;という風に指定しておけば、単価（tanka）を入力しなかった場合に自動的に０が入ります。

デフォルト値が指定されてない列のデフォルト値は、NULLとなります。

これも経験則ですが、タイムスタンプ型のような「ない」事を表すのに適切な値がない場合を除けば、データーベースの方でデフォルトでNULLが入らないように設計しておいた方が後々でトラブルが少なくて済みます。文字列にnullが入ってたりすると||で連結しようとして全部nullになってしまったり、field<>''で判別できず、 or field is not null ように、「もしくはヌルではない」という一文を追加したりして面倒な事があります。

・ＮＯＴ　NULL制約

NULLを入れてはいけない列には、NOT NULLと書くことでNULLが入らないようになります。例えば、hiduke date not null と書くことで、日付（hiduke）の入力が必須となり、NULLならエラーになります。また、primary key と指定した列は、暗黙にNOT NULL制約を入れたことになります。

ただ、プログラムの実行中にＳＱＬの文法エラーが出てしまうのは非常にカッコ悪いため、NOT NULL制約をする場合、SQLエラーが出ないように、事前に入力値をチェックする処理をプログラマの責任で入れた方が良いです。その代わり、NOT NULL制約があればNULLが絶対に入ってはいけないフィールドにNULLが入ってしまうというバグがあった場合にSQLエラーになるので発見しやすくなります。

先頭に戻る　メニューに戻る

 　コメント文

テーブルを新規作成する場合、あらかじめテキストエディターでテーブル定義ファイルを作っておき、

psql table < create.sql

みたいに、リダイレクトする事がよくあると思います。

このテーブル定義ファイルの可読性を高めるために、コメントを入れることができます。行の先頭に、--（ハイフンを二個）を入れることでコメント行となり、そこから改行コードまではSQL文として処理しなくなります。

-- 伝票テーブル作成
create table denpyo (
	number serial primary key,  	-- 伝票番号
	hiduke date, 			-- 日付
	tokcode int, 			-- 得意先コード
	tokname text			-- 得意先名
);

こんな風に書いておけば、テーブル定義ファイルの可読性はかなり良くなります。

先頭に戻る　メニューに戻る

 　列の型

テーブルを定義する時には、その列にどんな値が入るかという、列の型を定義する必要があります。型には主に以下のものを指定します。（主なものだけ挙げておきます。）

・日付、時刻型

日付は、[年-月-日]、[月/日/年]、[英語の月名 日, 年]といった書式で入力できます。また、時刻は時:分:秒のように入力します。また、timestamp型の場合は、日付の後にスペースを１個あけて時刻を入れます。

日付は英語圏の人間に慣れた書式でも通用しますが、日本人であれば、2004-08-03 04:52:30 のような日本式の書式で指定するのが一番わかりやすいと思います。

・整数型

この他に、単にintとだけ書くこともできます。単にintとだけ書くと（この原稿を書いてる時点の現バージョンでは）、32ビット長となり、 -2147483648 〜 2147483647 の値を扱うことができます。通常、整数を扱う場合は、この指定で良いでしょう。（２０億以上の数値を扱うなら話は別ですが・・・）

・シリアル型

シリアル型というのは、他のプロセスとだぶる事のない、自動的に繰り上がる連番を自動的に発行するものです。伝票番号とかに使われます。例えば、denpyo serialと指定すると、denpyo_number_seqというシーケンスが自動的に作成され、デフォルト値がnextval('denpyo_number_seq')に設定されます。

行を挿入する時に、シリアル型に定義された列には値を指定しません。そうすると、自動的にシリアル値（連番）を代入してくれます。
シリアル値の最大値は、バージョン7.3未満は32ビット長、7.3以上は64ビット長の最大値ですが、２０億枚以上の伝票を発行して伝票番号が桁あふれした、という話は今のところ聞きません。なので、7.3未満であってもシリアル値の桁あふれは気にしなくても大丈夫と思います。また、バージョン7.3未満では、bigserialという６４ビット長のシリアルを指定する事もできます。

・配列

それぞれの型に配列を作ることができます。たとえば、tanka int[]と定義すると、その列が配列という事になります。ただ、配列に入れるために中カッコでくくったカンマ区切りの文字列を作らないといけないので、若干面倒です。また、バージョン7.4から配列の要素内にある特定の値を検索する事ができるようにはなりましたが、LIKE句が使えなかったりと、色々不便な所があります。

ところで、現バージョンのPostgreSQLのマニュアルには以下のような記述があります。

ティップ: 配列は集合ではありません。特定の配列要素に検索をかけることはデータベース設計が誤っている可能性を示唆しています。配列の要素とみなされるそれぞれの項目を行に持つ別のテーブルを使うことを検討してください。この方が検索がより簡単になり要素数が大きくなっても拡張性があります。

つまり、配列内を検索しなければならない設計自体があまり推奨されていないようです。配列内検索を必要とするのれあえれば、それとは別に検索用の中間テーブルを作った方が良さそうです。

先頭に戻る　メニューに戻る

 　一時テーブル

一時的に、あるデーターをソートしたい事があります。たとえば、巨大なCVSファイルや、シーケンシャルファイルを読み込んでソートして出力する場合などです。

この場合、PerlやPHPの配列やハッシ変数に読み込んでからソートすると、データーが巨大な時にはメモリを湯水のように使い、サーバーをダウンさせてしまいます。しかし、いったんテーブルに入れてそれをselect句で読み込もうとしている間に、別のプロセスが同じことをしようとするかもしれません。

そこで、現在動作しているプロセス専用の一時的に使うテーブルを作成することができます。

[例]
create temp table sorttable (
	hincode		text, 
	hinmei		text, 
	suu		int,
	tanka		int,
	kingaku		int
);

この例のように、create temp table と書きます。あとは、通常のテーブル作成と同じです。この例では元々シーケンシャルファイル、あるいはＣＶＳファイルになっている商品ファイルを、コード順や金額順にソートする時などに使います。

一時ファイルは、そのプロセスが終了するとともに自動的に削除されますので、プログラマ側で一時テーブルの削除を意識する必要がありません。

先頭に戻る　メニューに戻る

 　grant句

作成したテーブルは、テーブルを作ったユーザーに読み書きする権限があります。しかし、商品コードのテーブルなどは任意のユーザーがアクセスできないと、一般のオペレーターが商品コードを確認できなくなり困ります。

そこで、テーブルごとにアクセス権を変更します。

[例]
grant all on shohin to public;

[命令]は、そのテーブルにどの命令を許すかを指定します。allを指定すると、select insert update delete 等どの命令も許可されます。[ユーザー名]はどのユーザーに許可するかを指定します。publicを指定すると全てのユーザーに許可されます。

しかし、これは商品テーブルだから良いのですが、もしユーザーテーブル（顧客情報）が全ての人間にpublicに許可されているのでは問題です。それだと、そのＤＢに接続されたコンピューターをさわれる全ての人間が顧客情報を引き出せてしまいます。

そこで、通常はユーザーごとに使える命令を細かく指定します。

[例]
grant select on users to operator;
grant all on users to admin;

この例では、operatorにはselect文のみを許可し、adminに全ての命令を許可しています。public（公共）指定されてないテーブルにアクセスするためには、許可されたユーザーでpsqlにコネクトする必要があります。

PHPやPerl等のプログラムからアクセスする場合、アクセス権のあるユーザーとパスワードをコネクトの命令の引数に入れないといけません。その際のユーザー名とパスワードは外部から見えないファイルに置かないといけません。（ブラウザでＵＲＬを叩いたりソース表示で見えてしまっては元も子もありません）

先頭に戻る　メニューに戻る

　ALTER句

PostgreSQL6.xxの頃は列の変更ができなかったため、後から列を追加したい時には、いったんpg_dump -Dで列名付きでバックアップして、テーブルを再構築してからリストアするという作業が必要でした。しかし、PostgreSQL7.xxよりalter句がサポートされ、後から列を追加できるようになりました。

・列の追加

[例];
alter table denpyo add tanka int;

denpyoというテーブルに、tankaという列を数値型で追加します。

・列名の変更

[例]
alter table denpyo rename kingaku to zeikomi;

denpyoというテーブルの、kingakuという列名をzeikomiという列名に変更します。

・列名の削除

[例]
alter table denpyo drop uchizei;

denpyoというテーブルから、uchizeiという列を削除します。

・テーブル名の変更

[例]
alter table denpyo rename to uriage;

denpyoというテーブル名を、uriageに変更します。

・データ型の変更

[例]
alter table denpyo alter column kingaku type int using kingaku::int;

kingakuという列の型をint型にします。数値が入るべき列を間違ってtextにしてしまった場合などに使います。

int型をtext型にするのであれば、using句は不要なのですが、text型をint型にする場合はusing句を使って明示的にキャストしないとエラーになってしまうようです。

・古いバージョンのALTER句の問題点

PostgreSQL7.1までは、途中から列を増やしたり減らしたりすると \copyやpg_dumpでバックアップしたデーターを戻す時にエラーになる場合がありました。\copyやpg_dumpで出力されるデーターに、後から追加/削除したフィールドが反映されなかったためです。

現在のバージョン（7.2以降）ではそのような心配はなくなりましたが、古いバージョンのpostgreSQLを使っている場合は、dumpに-Dをつけてinsert文としてダンプさせる必要があります。

先頭に戻る　メニューに戻る

　insert句

行にデーターを挿入します。

[例]
insert into denpyo (tokucode,tokmei,hiduke) values ('1','aufheben software','2004-08-01');

伝票テーブルに行を挿入します。得意先コード:１、得意先名:aufheben software、日付:2004-08-01を入れます。

列名と値の型は合っている必要があります。例えば、int型の列に文字列を挿入しようとするとエラーになります。また、列の数と値の数（つまり、valuesの前後のカッコ内）の要素の数が同じでないとエラーになります。

PostgreSQL7.2までは、int型に''（空文字）を入れるとNULLとみなされてましたが、7.3以降は''（空文字）とNULLは明確に区別されるようになったため、int型に''を入れるとエラーになります。古いシステムのＤＢのバージョンを上げる時は、それを利用しているプログラムの修正が必要です。

ここで指定しなかった列には、デフォルト値が適用されます。create句でデフォルト値を指定しなかった時はNULLが入ります。serial型の列には、デフォルト値として連番が入ります。

先頭に戻る　メニューに戻る

 　update句

行の特定の列を変更します。

[例]
update denpyo set tokucode='2' where number='100';

伝票番号１００の得意先コードを２に変更します。

シーケンシャルファイルやISAMファイルの特定の部分のみを書き換えるのは難しい問題がありました。というのも、同時に何プロセスもアクセスがあった場合に、いったんファイルやレコードをロックして、メモリに読み込んで、変更のあった場所だけ書き換えて、ファイルをリライトしなければなりませんでした。

データーベースサーバーでは、update句を使うことで特定の行の特定の列のみを書き換えてくれます。当然、同時に何プロセスもアクセスがあった場合でも、プログラマーレベルで特に気にする必要がありません。

update句で注意しなければならないのは、whereで書き換える行の条件を指定し忘れることです。指定を忘れてしまうとエラーになるのではなく、全ての行が書き換わってしまいます。

先頭に戻る　メニューに戻る

　delete句

行を削除します。

[例]
delete from denpyo where number='100';

伝票番号１００番を削除します。

ここで注意しなければならないのは、whereで条件を指定するのを忘れると、エラーにならずに全部の行が消えてしまいます。もしまかりまちがって本稼動中にやってしまうと、ユーザーが大迷惑します。（というか、かなりの損失になります）

なので、delete句には必ずwhereをつけるという風に常に意識しておいた方が安全です。

先頭に戻る　メニューに戻る

  select句

データーを取り出します。

データーを取り出す際に、さまざまな指定をすることができますが、文法（書式）だけを並べてもわかりにくい文章になってしまうので、ここでは例をあげてみていきましょう。

このようなテーブルがあったとして、例をあげてみていきましょう。

[例1]
select * from denpyo where number='100';

伝票番号１００番の列を全て取り出します。列名に*を指定すると、列を全て取り出すことができます。where句でnumber='100'を指定することで、伝票番号１００番を指定しています。

[例2]
select * from denpyo order by number;

伝票番号の若い順に、全ての列、行を取り出します。order by を使い番号の若い順に出すことを指示しています。

[例3]
select * from denpyo order by number desc;

伝票番号の大きい順（最新の伝票順）に全ての列、行を取り出します。order by descを使い、番号の大きい順に出すことを指示しています。

[例4]
select * from denpyo order by number limit 100;

伝票番号の若い順に、最初から１００件目までを取り出します。limit 100 で１００件まで出すことを指示しています。

[例5]
select * from denpyo order by number offset 100;

伝票番号の若い順に、１０１件目〜最後まで取り出します。offset 100 は、「１００件目から」という意味ではなく、「最初の１００件をとばして」という意味です。

[例6]
select * from denpyo order by number limit 100 offset 100;

伝票番号の若い順に、１０１件目〜２００件目までを取り出します。limitとoffsetを同時に指定することで、行の途中〜途中を取り出すことができます。

[例7]
select count(number) as count from denpyo;

伝票の数を数えます。count()句を使うと、指定した列の数を数えることができます。ここで、count(*)とすると全ての行数を数えます。count(列名）とすると、指定した列のうちのNULLを除いた数を返します。

as count と指定することで、結果が「count」という列名として返ります。

[例8]
select sum(kingaku) as total from denpyo;

伝票の合計金額を求めます。

余談ですが、COBOL+ISAMファイルの頃、合計値を求めるためにはループ命令で全レコードを取得して足し算する必要があり、その分「処理中」という待ち時間が必要でした。ＳＱＬでは合計値は１コマンドで即求められますので、とても便利です。

[例9]
select sum(kingaku) as total from denpyo where hiduke>='2004-05-01' and hiduke<'2004-06-01';

５月の売上合計金額を求めます。where句で日付を指定することで、月単位の合計金額を求めることができます。

where句の条件式として、不等号（＜＞）を使う事ができます。>=で同じか大きい、<で未満を指示しています。３１日まである月と、２８日、２９日、３０日までしかない月があるので、翌月の１日未満という指定をしています。

[例10]
select avg(kingaku) as avg from denpyo;

１回の出荷の平均金額を求めます。avg(kingaku)はkingakuの平均値、すなはち、合計値sum(kingaku)を件数count(kingaku)で割ったものが取得できます。件数が０の時は値はNULLになります。

[例11]
select date_part('year',age('now',birthday)) from member;

memberテーブル内にあるbirthdayフィールド（timestamp型）の、現在との年数差（つまり年齢）を取得します。ageは第一引数と第二引数との時間差を求めますが、第一引数にnowを指定する事で、現在との差、つまり年齢を求めることができます。

時間差は年月日時分秒と細かく取得できますが、date_partでyearを指定する事で、年だけを取得しています。

先頭に戻る　メニューに戻る

  シーケンスの操作

denpyoというテーブル内で、 number serialと定義した場合、denpyo_number_seq（テーブル名_列名_seq）という連番を管理するテーブルが作成されます。これをシーケンスといいます。

シーケンスは、他のプロセスと絶対にだぶる事のない連番を与えます。例えば、全国２００店舗あるチェーン店がオンラインで結ばれていて、店舗に関係なく取引が発生した順番での連番が必要だった場合などに使います。

　シーケンスの作成

serial型を使わず、別途シーケンスだけを作成する事もできます。

create sequence シーケンス名;

シーケンス名の所には、半角英数でシーケンス名を指定してください。

increment 増量 を指定すると、１ずつではなく増量分ずつシーケンスが増えていきます。maxvalue 最大値、minvalue 最小値でそれぞれ最大値と最小値を指定する事もできます。省略すると64ビット長の最大の値が最大値になります。（バージョン7.3未満では３２ビット長）

start 初期値　を指定すると、最初にその初期値からシーケンスが開始されます。省略すると１からになります。

cycleを指定すると、増量がプラスの時はmaxvalue、マイナスの時はminvalueまで達すると初期値に戻ります。指定しないと最大値または最小値まで達するとエラーになります。

　直前に発行されたシーケンス番号を取得する

たとえば、insert文で伝票テーブルに行を挿入した直後に、「今挿入した伝票番号を取得したい」という事があるでしょう。その場合、直後に伝票番号の最大値を取得するという方法では、他のプロセスが一瞬後に伝票を発行した場合にバグになってしまいます。

そこで、currval句を使います。

[例]
select currval('denpyo_number_seq') as currval;

この例では、直前にinserした行の伝票番号（number)が、currvalという列名で取得できます。

「直前でinsertした」といっても、正確には「このプロセスが直前でinsertした」です。なので、同時に何プロセスも実行中だった場合でも、異なるプロセス間で同じ伝票番号を取得してしまう事はありません。

　次のシーケンス番号を取得する

伝票番号をserial型にせずに、普通にint型で取っておいて、シーケンスは別に管理する事ができます。この方法では、serial型をサポートしていない別のデーターベースサーバーとの互換性を持たすことができます。（もっとも、別のデーターベース向けにする時には、シーケンスの取得方法を変更する必要がありますが）

この場合、まずシーケンス番号を取得しておいて、その番号をinsert文で指定します。

[例]
select nextval('denpyo_number_seq') as nextval;
               :
insert into denpyo (number,… ) values ('伝票番号',…);

この場合、次の伝票番号をあらかじめ取得しておき、それをinsert文で代入しています。

取得した地点でdenpyo_number_seqが繰り上がるため、他のプロセスが同時にこのコマンドを実行しても同じ番号が異なるプロセス間で取得される事はありません。

　シーケンス値を初期設定する

シーケンスの値は、特に指定しないと1から始まるのですが、伝票番号を10000番から始めたい時はシーケンス値を初期設定することができます。

[例]
select setval('denpyo_number_seq', 10000);

のようにすると、伝票番号の現在の値を10000にすることができます。このコマンドは、もうすでにシステムが本稼動している時に実行してしまうと、伝票番号が突然若返ったりして大変なトラブルになります。なので、このコマンドは、初期設定時に１回だけ使うのが普通です。

ところで、この例では、伝票番号の次の値は10001になりますが、10000から始めたい場合は9999とする事でしょう。では、１からにしたい場合はどうすれば良いのでしょう？ setval('denpyo_number_seq', 0)のように０を指定することはできません。

そこで、第三引数としてis_calledフラグをセットします。これは、既にこの番号が呼ばれたかどうかというフラグで、trueなら呼ばれ済、falseならまだ呼ばれてないという事になります。指定をしない時はtrueとみなされます。

つまり、select setval('denpyo_number_seq', 1, false);という風にして、第三引数にfalseを指定する事で、この１番というシーケンスはまだ呼ばれてないという事になり、次に出てくるシーケンス値は１になります。以降は同じで、２、３、４…と連番が取り出せます。

先頭に戻る　メニューに戻る

 　JOIN句

得意先マスターのテーブルには郵便番号、住所、電話番号などの情報が入りますが、他に請求金額や得意先別売り上げ金額などが必要でしょう。ただ、それらが全部１つのテーブルに入っていると、名前だけが欲しいのに売上データーも一緒にselectしたり、逆に売上げ金額だけが欲しいのに住所なども一緒にselectしてしまったりして、余分な負荷が発生します。

そこで、マスターには住所、電話番号といった基本情報と、売上げのような頻繁に変動する情報は別々のテーブルにしておいて、必要に応じて結合して取り出すのが一般的です。そこでJOIN句を使います。

　MS-DOS時代と、クライアント／サーバーの違い

MS-DOS時代、メモリは640ＫＢしか使うことができず、そのためメモリ上にはなるべく情報を残しておかないようにプログラミングしました。

例えば、

５月度売り上げ明細

という表を一覧表示させる際には、レコードを１件ずつ呼んで、その都度ハードディスクから「得意先名」「商品名」を引き出したでしょう。これは、「得意先名」「商品名」といった"文字列"は、数値と違いメモリを食うので、オンメモリ（とりわけ、コンベンショナルメモリー）に全部を読み込む事ができなかったためです。

ところが、この方式をＳＱＬのようなクライアント／サーバータイプに当てはめると、ＤＢサーバーに大変な負荷になります。ＤＢサーバーは常に何十、何百台からの要求を受け付けているため、全ての端末が頻繁にselectを発行するとＤＢサーバーの処理がおっつかなくなります。

このように、クライアント／サーバータイプのプログラムは、サーバーからデーターを引き出す回数（ＳＱＬ文を発行する回数）をできる限り少なくしなければなりません。むしろ１回で済むのであれば１回が一番いいです。

また、ＭＳ−ＤＯＳ時代では、メモリは640ＫＢしか使えませんでしたが、現在においてデーターベースサーバーからデーターを引き出す端末（ワークステーション）は最低でも１ギガ、一般的には２ギガ〜４ギガ程度のメモリは搭載されています。なので、１万件〜２万件ぐらいのデーターなら、全部オンメモリに読み込んでも全然余裕です。

というわけで、ここでは１回のＳＱＬ文でできる限り多くの情報を引き出すプログラムをしましょう。

このようなテーブルがあったとして、１回のＳＱＬ文で全部を読み込むために、全部のテーブルをくっつけてしまいます。これがＪＯＩＮ句です。

例）
select m.得意先コード,t.得意先名,m.商品コード,h.商品名,m.数量,m.単価,m.金額 from 伝票明細 m
left join 得意先テーブル t on 得意先コード=得意先コード
left join 商品テーブル h on 商品コード=商品コード;

これで、全部のテーブルがつながりました。こうする事で、データーベースサーバーへのアクセスは１回で済み、サーバーへの負荷が少なくなります。

　ＬＥＦＴ　ＪＯＩＮ

表の左に表をくっつけます。この場合、あとからくっつける方に存在しない列はnullになります。
例）得意先コード003が存在しない場合、LEFT JOINすると・・・

　INNER JOIN

項目が完全に一致する行だけくっつけます。ない行は出ません。
例）得意先コード003が存在しない場合、INNER JOINすると・・・

上のようにどちらかの表に存在しないものは結果に出ません。

また、この事を逆手にとって、テーブルＢに存在する得意先だけをテーブルＡから探せという場合にINNER JOINを使うことができます。select * from テーブルＡ where 得意先コード in （selete 得意先コード in テーブルＢ） のように、where in 句を使うより、select * from テーブルＡ innser join テーブルＢ using (得意先コード)のようにINNER JOINを使ったほうが高速にデーターが取り出せます。

　サブクエリーとINNER JOIN

複数の表をいったんＬＥＦＴ　ＪＯＩＮで連結してからwhere句で条件を絞ると、データーベースサーバーに負担がかかります。そこで、データーベースサーバーへの負担を少なくするために、連結する表の条件を絞ってからINNER JOINするようにします。

select 出席番号,氏名,宿題提出費 from クラス名簿 inner join (select 出席番号,宿題提出日 from 宿題提出テーブル where 宿題提出日<=2005-09-01) using （出席番号）;

この場合、もしクラス名簿ではなく全校生徒だった場合など、LEFT JOIN してからWHEREで条件を絞ると検索に時間がかかります。そこで、あらかじめサブクエリで宿題提出日が９月１日までの人を絞り込んでから、INNER JOINすることで全ての名簿を連結しなくなり、処理が早くなります。

特に、生徒数が数千人いて、宿題提出者が１０人に満たない、なんて場合に効果を発揮します。

先頭に戻る　メニューに戻る

 　ハッシュ変数との併用

もし、あなたが学校の先生だったとして、事務に行って３年Ｊ組の出席番号３番の生徒の電話番号を調べたいとします。事務の人は、全校生徒の名簿から３年Ｊ組を探し、そこから出席番号３番の生徒の情報を調べるでしょう。数分後、もう一度事務に行き、今度は１０番の生徒の電話番号を聞いたとします。事務の人は、また全校生徒の名簿から３年Ｊ組を探し、そこから出席番号10番の生徒の情報を調べるでしょう。数分後、今度は20番の生徒の電話番号を聞いたとします。事務の人は、「もう、あなたのクラスの名簿コピーして持っていってください」と答えるでしょう。

というように、検索対象が大きければ大きいほど、その中の１つを見つけるのは手間がかかります。最初から３年Ｊ組からしか探さないとわかっていれば、最初に３年Ｊ組の名簿をコピーしてそこからだけ探したほうがあきらかに探しやすいです。同様に、データーベースサーバーも、データーが肥大化すれば肥大化するほどSELECTする際の負荷が大きくなり、SELECT文を発行する回数が増えれば増えるほど負荷が高くなります。それでも、個人サイトや小規模の企業サイトであれば特に問題はないのですが、会員10万人のサイトから会員１人の情報を探すとなるとSELECTを発行する回数は大幅に減らさねばなりません。

そこで、PHPやPerl等のハッシュ変数を活用します。昔はCOBOLのISAMとかで、 READ 生徒マスター INVALID KEY とかやってコードから名前を引き出してたのですが、現在はPHPやPerlでハッシュ変数という便利なものがあります。フロントエンドサーバー（Webサーバーとか）の能力にもよりますが、まず最初に候補を1000件以内に絞って、そのデーターを全てハッシュ変数に入れてしまいましょう。

例えば、上記の例でいくと、まず３年Ｊ組の出席番号と電話番号をSELECTして、ハッシュ変数に入れておきます。

SELECT 出席番号, 電話番号 from 生徒名簿 where クラス='3年J組';
PHPなら、
$seito[$num]="xxx-xxx-xxxx";
Perlなら
$seito{$num}="xxx-xxx-xxxx";

みたいにハッシュ変数に登録しておきます。（注：出席番号みたいな連番の数値しか入らないものであれば配列変数でも良いのですが、この例では汎用性も考えてハッシュ変数にしています。）

こうしておけば、使いたいときに$numに出席番号をセットしておいて、
print $seito[$num];
みたいにすれば、この後3年J組の生徒の電話番号を何回調べようが、データーベースサーバーへの問い合わせは１回で済みます。

　この手法を使う場合の注意点

�@絞り込む件数に注意
現在は100件でも将来的に1,000件以上に膨れ上がる可能性があるような絞込みはしないようにします。でないと、テスト稼動は問題なかったシステムが、稼動してしばらくして突然動かなくなったりします。

３年Ｊ組など、１クラスの生徒はどんなに多くても100人以上にはならないでしょうが、男性の会員とか、東京都の会員のような将来的に1000件を越える可能性のあるような絞込みをすると、フロントエンドサーバーがダウンする可能性があります。この手法を使う場合は、最初の絞込みをいかに漏れなく1,000件以内にするかがポイントです。

なお、最近ではフロントエンドの性能もあがっているため、この1,000という数値は厳密ではありません。現在はフロントエンドサーバーでも3ギガ4ギガは当たり前なので、1,000件どころか10,000件でも大丈夫かもしれません。各自お使いのフロントエンドサーバーでテストして、絞り込む件数を判断してください。

�A未登録/NULL/空文字列の判別は不可
この手法だと、出席番号に対するデーターが、「ない」のか「あるけどNULLが登録されてる」のか「あるけど空文字列が登録されている」のかの判断はできません。printさせたり、他の変数に代入すると、いずれでも空文字列になってしまうと思います。

未登録かどうかは、別途出席番号存在テーブルのようなものを作るなどで判別します。また、NULL/空文字列を判断したい時は、別途 where句で is nullやis not nullを追加してNULLなものテーブルやNULLでないものテーブルのようなものを作って判別します。

とはいえ、単に一覧を表示や印刷をする場合では、大抵の場合は未登録とNULLと空文字を区別する必要もないと思います。

�BSELECTしたデーターはその場ですぐに使う
この手法では、SELECTしてから表示や印刷するまでの間に生徒の名簿に変更があった場合でも、SELECTした時の情報が出てきます。電話番号のように頻繁に変更にならないものであれば問題はありませんし、SELECTしてから印刷するまでの間の時間が著しく短い（この場合だと１秒以内？）であれば問題はありません。

一度データーベースからSELECTしたものをフロントエンドのハードディスクに取っておいて恒久的に使う・・・みたいな設計にはしないようにします。この手法は、SELECTしたデーターを短時間以内に使う場合に限ると良いでしょう。

先頭に戻る　メニューに戻る

 　トランザクション

データーベースシステムでは、COBOLのＩＳＡＭにはない重要な機能があります。それがトランザクションです。トランザクションとは、簡単に言えば一連の処理の全部を、１つの処理とみなす事です。

１つの売上げ処理において、売上げ金額の発生、請求金額の発生、在庫の引き落とし、の全部が１つの処理であり、途中でエラーがあった場合には全部を差し戻ししなければならないでしょう。また、売上げ金額だけ発生していて、在庫の引き落と処理がまだできてない状態を、他のユーザーが見えてしまうと矛盾が発生してしまうため、途中経過は別のプロセスに見えてはいけません。

このように、セットで扱わなければならない一連の処理を、トランザクションといいます。

 　ロールバックとは

かなり昔、「さらば宇宙戦艦ヤマト」という映画がありました。簡単にストーリーを説明すると、宇宙から地球を侵略に来た「彗星帝国」に立ち向かうため、宇宙戦艦ヤマトが地球を旅立った。そして、テレザート星のテレサの協力を得て、彗星を撃破し、白兵戦で帝国を壊滅状態に追い込みました。ところが、帝国から出てきたのは、ヤマトの数十倍の大きさを持つ巨大戦艦でした。ヤマトは最後の力をふりしぼって、巨大戦艦に特攻を試みました。

と、こういう話で、最後に敵の戦艦へ特攻し、ヤマトは主人公もろとも大爆発。相打ちとなり、地球は救われました・・・・救われましたが、この後さらに続編を希望する声が高まり、映画とは別にテレビシリーズが作られました。しかも、テレビシリーズでは、この映画はなかった事になり、乗組員はほとんど死なないで終わる話になりました。つまり、この作品の世界観がロールバックしたわけです。

ちょっと昔、「マシュランボー」というアニメがありました。主人公と仲間２人が、マトリクサーという悪人と戦うアニメでしたが、話の途中で主人公が目指す目的地を破壊してしまうという暴挙に出てしまい、話が行き詰ってしまいました。そこで、途中から話を一番初めの状態に戻しリスタートしました。つまり、話がロールバックしたわけです。

のように、やたらアニメ作品が話をロールバックされてしまうと困るのですが、業務ソフトの世界では、このように都合が悪くなったらすぐにロールバックできないと困ります。なにしろ、コンピューターにはアクシデントがつきものです。ハードディスクはいつ故障するかわからないし、回線だっていつ切れるかわかりません。また、人間がいつ間違って変なデーターを入れてしまうかわからないわけです。

そこで、データーベースでは、ある一定時点からの変更をなかったことにする事ができます。これがロールバックです。

 　別のトランザクションとは

昔、Kanonというゲームがありました。このゲームでは、主人公相沢祐一（プレイヤー）の行動の選択により話がどんどん分岐していきます。したがって、お昼休みに中庭で栞とアイスクリームを食べている相沢祐一もいれば、お昼休みに階段の踊り場で舞や佐祐理さんとお弁当を食べる相沢祐一もいるわけです。お昼休みに同じ人間が同時に二箇所に存在するわけにはいかないので、この時点で２つの世界は別々に分離したわけです。つまり、別のトランザクションが走ってるわけです。

アニメ「ドラゴンボールＺ」で、未来から来た青年トランクスは言います。「過去の世界を変えても、現在には何も影響はない」と。したがって、物語の進んでいる時代にセルを殺したからといって、未来からセルがいなくなるわけではないのです。つまり、現在と未来は別のトランザクションなのです。

同様なものに、仮面ライダー電王があります。この話では、２００７年１月に世界は破壊され、桜井さんや愛理さんは消滅したはずですが、ゼロノスの力で世界は修復され、何事もなかったようになりました。ただ、特異点である主人公良太郎とハナさん以外は・・・。こうして、世界が崩壊後にイマジンに占拠された世界と、人間が普通に生活している世界と、２つの未来が存在してしまったわけです。つまり、未来は２つのトランザクションにわかれてしまったのです。

　 トランザクションを使う例

話がだいぶ脱線してしまったので、比喩から現実に戻しますと、あるコンビニの管理システムがあったとします。お弁当が１個売れると、まず在庫から１個引きます。次に、売上金額にお弁当代を追加します。最後に、３０代男性が買ったというデーターを入れます。

しかし、コンピューターは同時に誰がデーターを覗いているかわかりません。例えば、社長とか店長とか、責任者が店の総売上をチェックするような時に、売上額と在庫の一覧を表示（やプリント）させる際に、運悪くコンピューターが在庫１個引き落としの処理をした後、売上げ金額を増やす処理をしてる最中に売上げや在庫をチェックしたとします。すると、在庫が１つなくなっているのにもかかわらず、売上げが増えてしません。つまり、見た目、お弁当１個万引きされたのと同じ状態になってしまいます。

なので、お弁当の売上げ処理を行っているプロセスと、それ以外のプロセスでは、データーベースの世界観が変わってなければなりません。つまり、別のトランザクションになっていなければなりません。

お弁当の在庫を１つ減らし、売上げに計上し、３０代男性が買ったという情報が全て終わるまでは、他のトランザクションからはお弁当の売上げの処理がまったく行われてない状態に見えてないといけないわけです。

  テーブルロックとは

コンビニの在庫管理であれば、お店に現物がなければ買えないので問題はないのですが、通信販売などでは、別のトランザクションと、今そのプロセスが張ってるトランザクションが完全な相互不干渉では困る事があります。

たとえば、チケットを通信販売したとします。チケットには数に限りがあります。誰かが購入の手続きをして、購入が確定するまでの間の別の人が購入手続きを完了させてしまうかもしれません。この場合、購入できなかった人の処理は最初からなかった事にしなければなりません。つまりロールバックしなければならないわけですが、トランザクションを張ってる間は、別のプロセスから見ると購入されてないように見えますから、このままでは１つしかないものが２人や３人に売れてしまう事になります。つまり、ワールドカップのチケットのような「空売り」になってしまうわけです。

そこで、テーブルロックを使います。先に購入処理のトランザクションを開始したプロセスが、在庫のテーブルをロックします。すると、一瞬遅いプロセスは、在庫テーブルを読むことができず、待たされることになります。この間に、不幸にも一瞬遅くなってしまった人の画面は、 ブラウザのロード中の画面が表示されたままになるか、あるいはタイムアウトして「ただいま混みあっております。」とか表示される事でしょう。

そして、一瞬早くテーブルをロックできた人が購入処理を終了した後、テーブルのロックが解除されます。その後、一瞬遅い人が購入処理をした時には「在庫切れです」が表示される事になります。

  PostgreSQLのトランザクション処理

PostgreSQLでは、トランザクションを開始するために、beginという命令を使います。

begin;
でトランザクションの開始が宣言されます。この瞬間からコミットするまでは、他のプロセスへ干渉を与えなくなります。

ただし、
select フィールド from テーブル where 条件 for update;
のように、for update句をつけることにより、テーブルのロックが行われます。for update、つまり、「更新するために読む」という意味です。これを宣言すると、”このテーブルはまもなく変更されるので他のプロセスはちょっと待て”という事になります。なので、他のプロセスがこのテーブルを読もうとすると、このプロセスの処理が終わるまでは待たされるわけです。

その後、update句を使い、テーブルを更新します。在庫を引き落とす処理もここでやります。一連の処理が終わったら最後に、

commit;
とします。（end;ともいう）コミットされて初めてbegin以降commit;までの処理が他のプロセスまで有効になります。そして、ロックが解除され、待たされていたプロセスがテーブルを読めるようになります。

もし、テーブルをロックするのが一瞬遅かったために在庫切れとなってしまった場合、例えば・・
・なんとかスーパーポイントの加算を無効にする
・購入履歴への追加を無効にする

クレジットカード会社のデーターベースは別サーバーなのでロールバックできないため、購入が完全に確定した後でなければ請求処理をしない場合がほとんどですが、ポイントや履歴への加算は随時同じデーターベースで行われるでしょう。

この場合、加算したのをまた引くとかするのではなく、それまでの変更自体をなかったことにします。つまり、ロールバックします。

rollback;
とする事で、begin;以降の変更が全て無効になります。また、他のプロセスに対してロックしていたテーブルは、ロックが解除されます。

  特異点

特異点である良太郎君は過去からのいかなる干渉にも左右されないのと同じく（？）、シーケンスはトランザクションにとって「特異点」であり、あらゆるロールバックの影響を受けません。

もともとシーケンスとは、絶対に重複しない、ユニークである事が保障されるもので、行を一意に確定するためのキーをつけるためのものです。なので、一度取得したシーケンスが、エラーなどでロールバックされてしまうと、その間に別のプロセスが既に次のシーケンス番号を取得している可能性があり、ユニークではなくなってしまいます。

なので、ロールバックが発生しても、一度nextvaｌやserial型でのinsertでカウントアップされたシーケンスは元に戻りません。

逆に言えば、シーケンスは連番である事は保障されません。つまり、欠番が発生する可能性があります。欠番をできるだけ避けたいのであれば、トランザクションをコミット後に別途連番用のシーケンスを取得しましょう。

先頭に戻る　メニューに戻る

 　最後に

PostgreSQL入門、いかがだったでしょうか。

この章で取り上げたＳＱＬ文は、ＳＱＬ文法全体のほんのさわりの部分でしかありません。しかしながら、基本的にはここで説明したものだけでも十分業務アプリを作成することはできるでしょう。

ここで取り上げたＳＱＬ文を全てマスターした方は、さらに上をめざしてみるのも良いでしょう。

戻る