データベース(PostgreSQL)

• PostgreSQLを用いてリレーショナルデータベースについて学ぶ
• DBを設計する

Aさん
Bさん
Cさん

1,Aさん,19
2,Bさん,21
3,Cさん,25

ここではリレーショナル型データモデルを採用しているPostgreSQL を用いて講義を行う。

1件のデータを複数の項目(フィールド)の集合として表現し、データの集合をテーブ ルと呼ばれる表で表す方式。ID番号や名前などのキーとなるデータ を利用して、データの結合や抽出を容易に行なうことができる。中 小規模のデータベースでは最も一般的な方法。データベースの操作 にはSQL(Structured Query Language)と呼ばれる言語を使うのが一般的。

(OSの出荷数を保持するためのテーブル)

RDBを管理するシステムのことをRDBMS(Relational DataBase Management System)という。 世の中にはさまざまなRDBMSが存在する。

• Oracle(オラクル)
• DB2(IBM)
• SQL Server(Microsoft)
• Access(Microsoft)
• MySQL (OSSのDBではシェアNo1だが、Oracleに買収されシェアが落ちている)
• PostgreSQL (MySQLのシェアが落ち、OSSで今後シェアNo1になりそう)
• SQLite (持ち運びがしやすい)
• H2 (持ち運びがしやすい、Javaと相性が良い)

どのRDBMSを使うべきかは用途や様々な都合により異なる。

PostgreSQLはオープンソースで開発されているRDBMS。 誰でも無償で自由に使えるだけでなく、商用データベースと比べても遜色無い機能を持っている。

SQL(Structured Query Language)はデータベースの定義や操作などを行うためのデータベース言語。
ISOによって標準化が進められている。参考 SQL (wikipedia)
SQLは大きく3つの種類に別けられる。

分類	意味	主な機能
DDL(Data Definition Language)	データ定義言語	create(テーブル作成),alter(テーブル定義変更),drop(テーブル削除)
DML(Data Manipulation Language)	データ操作言語	select(検索),insert(挿入),update(更新),delete(削除)
DCL(Data Control Language)	データ制御言語	grant(権限付与),revoke(権限取消),commit(処理の確定),rollback(処理の取消)

データベースに保持される実際のデータには必ず型を定義する。 RDBMSによって型の種類や名前に若干の違いはあるが、以下のような型を定義できる。

SQLで定義されているデータ型(PostgreSQLとは限らない)

• CHARACTER STRING
  • 文字列を表現する型
  • 固定長と可変長があり、可変長はCHARACTER VARYINGと定義される
  • それぞれ、CHAR、VARCHARと省略される
  • 要するに1バイトの文字
• NATIONAL CHARACTER
  • もっとも一般的に使用される言語に対応することを除けば CHARACTER STRINGとほぼ同じ
  • NCHAR と省略される
  • 要するに2バイト以上の文字(ひらがな、カタカナ、漢字等)
• BIT STRING
  • 0と1のシーケンスで構成されるバイナリ数
  • 固定長と可変長があり、可変長はBIT VARYINGと定義される
  • 要するにバイナリ
• EXACT NUMERIC
  • 精度とスケールを指定し、厳密な数を表す
  • 次の4つがある
    • NUMERIC:指定された精度とスケールを持つ数値を定義する
    • DECIMAL:指定されたもの以上の精度とスケールを持つ数値を定義する
    • INTEGER:指定された精度を持つ整数値を定義する
    • SMALLINT:精度がINTEGERよりも低いことを除けばINTEGERと同じ
  • 要するに数字を表す
• APPROXIMATE NUMERIC
  • 精度だけを持つ数値を表す
  • 次の3つがある
    • FLOAT:精度の指定は2進数。指定される値以上の精度を持つ
    • REAL:精度の指定は10進数で、実装時に定義される
    • DOUBLE PRECISION:REALと同様だが、REALの精度よりも高い精度を持つ
  • 要するに数字を表す
• DATETIME
  • 日付または時刻を表現する、DATE, TIME, TIMESTAMPの三つの型がある
    • DATE:日付を表現するデータ型
    • TIME:時刻を表現するデータ型
    • TIMESTAMP:DATEとTIMEの組み合わせ
  • 要するに日付や時間を表す
• INTERVAL
  • 時間間隔を表現するデータ型
  • 要するに時間の間隔を表す

PostgreSQLで定義されているデータ型(主要なものだけ紹介)
詳細は、PostgreSQLのドキュメントを参照。

• 数値データ型
  • smallint
    • サイズは2バイト
    • 範囲は-32768 〜 +32767
  • integer
    • サイズは4バイト
    • 範囲は-2147483648 〜 +2147483647
  • bigint
    • サイズは8バイト
    • 範囲は-9223372036854775808 〜 9223372036854775807
  • decimal
    • サイズは可変長
    • 範囲は無制限
  • serial
    • サイズは4バイト
    • 範囲は1 〜 2147483647
    • 自動増加整数
    • 2147483647までは数字がかぶらない
    • 要するに1から始まる連番
  • bigserial
    • サイズは8バイト
    • 範囲は1 〜 9223372036854775807
    • 自動増加整数
• 文字データ型
  • character(数字n)またはchar(数字n)
    • 固定文字列
    • 数字nでで指定された分の長さの文字列を格納
    • 例えばchar(20)だと20文字が制限
    • 例えばchar(20)に15文字の文字列を格納すると、5個のスペースが挿入される
  • character varying(数字n), varchar(数字n)
    • 可変長文字列
    • 数字nでで指定された分の長さの文字列を格納
    • 制限付き可変長文字列
    • 例えばvarchar(20)だと20文字が制限
  • text
    • 文字列を格納
    • 制限無し可変長文字列
• 日付データ型
  • timestamp
    • 日付と時刻
  • interval
    • 日付/時刻の間隔
  • date
    • 日付のみ
  • time
    • 時刻のみ

Mac OS X で PostgreSQL を使ってみようの手順で Mac OS X に PostgreSQL をインストールします。

2ページ目以降は実行しなくて結構です。

PostgreSQLのインストールが完了したら、ターミナルを起動し、以下の設定を行ってください。

$ sudo su - postgres $ createdb mydb -E UTF8 データベースに接続してみよう。 $ psql mydb Welcome to psql 8.1.0, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit mydb=# \q $ wget http://dandydot.homeip.net/~dot/presen/ospi/postgresql/sql/os.create_table.sql $ wget http://dandydot.homeip.net/~dot/presen/ospi/postgresql/sql/os.insert.sql

create文はテーブル作成のために使います。

create文の文法
CREATE TABLE テーブル名 (列名 データ型 , ......);

実際にPostgreSQLでテーブルを作成してみましょう。 以下のcreate文を用いてテーブルを作成して下さい。

os.create_table.sql

os.insert.sql

CREATE TABLE "os" (
	"id" serial NOT NULL,
	"name" text,
	"number" bigint,
	"copyright" text,
	"version" text,
	"p_date" date,
	PRIMARY KEY ("id")
);
				

(NULLとは未定義、空という意味)

OSテーブルの構造

カラム名	データ型	制約	説明
id	serial	NOT NULL,PRIMARY KEY	プライマリキー,id,自動増加整数
name	text		OSの名前
number	bigint		OSの出荷数
copyright	text		OSのコピーライト
version	text		OSのバージョン
p_date	date		OSの出荷日

$ psql mydb

mydb=# \i os.create_table.sql

mydb=# \i os.insert.sql

mydb=# \d os

psqlとはPostgreSQLに付属している対話型のデータベース操作ツールです。 SQL文を使ってユーザ登録、表作成、データ追加、変更、削除、検索などの機能を使うことが出来ます。

問題

1. psqlのコマンド"\i"や"\d"について調べてください

主キーとは、テーブルの各行を一意に識別するための 1つ以上の列のグループのこと。主キーはNULLを持たず、 Unique(一意)であることを保障する。主キーは無くても良いが、以下の理由により有ったほうが良い場合が多い。

主キーの存在意義

1. 検索を高速に行う
2. 検索を高速に行うには、行(レコード)を一意に識別できるフィールドが必要

osテーブルを例にとると、idが主キーとして最も妥当である。なぜなら、serial型はその値が一意であり、かつ、NULLで無いことが既に保証されているからである。

しかしながら、例えば「nameとversion」を主キーとすることも可能ではある。

主キーはテーブル内に1つしか定義できない。(主キーの一意性を実現するため)

2つ以上の主キーを定義したくなるようなテーブルは、後に説明する正規化を行うべきである。

表のデータを制限する機能を制約といいます。 表定義ではデータ値に制約を持たせることで、登録されるデータが常に正しい状態を保つことができます。

• NOT NULL 制約
  • 列にNULL値を許容しない
• UNIQUE 制約
  • 重複値を許さない
  • Unique(一意)である
  • NULLは許容する
  • ある列でNULL値である行が複数あることも許容する(つまり、NULL値は制約から除外される)
  • 1つのテーブルに対して複数のUNIQ制約を定義できる
• PRIMARY KEY(主キー)制約
  • 1つのテーブルに対して1つの主キーしか定義できない
  • NULL値は許容されない
  • つまり、NOT NULLかつUNIQUE
  • 複数の列をセットにして主キーを定義することが出来る
  • 複数の列で定義される主キーのことを「複合プライマリキー、複合主キー」と言う
• CHECK 制約
  • 列に入る値を制限する
  • 例えば「出荷数の値は必ず0より大きくなくてはならい"CHECK (number > 0 )"など
• FOREIGN KEY(外部キー)制約
  • 値が主キーの値、または別テーブルの主キーと一致するように制限する

select文はデータベースの表からデータを取り出す(検索)ために使います。

select分の文法
SELECT 列名,列名,... FROM テーブル名;

"列名,列名,..."の部分を"*"とすると全ての項目という意味になります。

osテーブルでselect文を実行し、データを検索してみましょう。

mydb=# select id from os;

mydb=# select id,name from os;

mydb=# select * from os;

select文でデータの検索を行っているときに、例えば「idが5より小さいものだけを検索したい」 といった要求が発生することがあります。そういった場合はwhere句を用いて検索対象を絞ります。

mydb=# select * from os where id < 5;

mydb=# select * from os where name = 'Linux';

mydb=# select * from os where name = 'Linux' and number > 10;

mydb=# select * from os where name = 'Linux' or name = 'Windows';

where句で用いることのできる演算子を以下に示します。

記号	意味
=	等しい
<>	等しくない
>=	より小さいか等しい
>	より小さい
<=	より大きい
<	より大きいか等しい
AND	かつ
OR	または
is null	nullである
is not null	nullでない

as句は指定したフィールド名や式を別名で表示する際に用いるSQL構文です。

mydb=# select id,name,number as 出荷数 from os;

group by句を用いると、同じ項目の値の中で同じ値を持つデータごとに集合化するすることができます。 以下のように使います。

mydb=# select name,avg(number) as 出荷平均 from os group by name;

集合化をを行った対象にSQL関数を適用する例です。

検索結果をある項目の値で整列したい場合があります。そのときにはorder by句を使います。

mydb=# select id,name,number as 出荷数 from os;

mydb=# select name,avg(number) as 出荷平均 from os group by name order by 出荷平均;

mydb=# select name,avg(number) as 出荷平均 from os group by name order by 出荷平均 desc;

order by句はデフォルトでは昇順に整列します。降順に整列する場合は order by 項目名 descとします。

明示的に昇順に整列する場合は order by 項目名 ascとします。

SQLにはavg()のように1つの列グループに対して施すことのできる演算機能があります。 それをSQL関数といいます。 RDBMSによって使用できるSQL関数は異なります。 PostgreSQLで使用可能なSQL関数(集約関数)の例。

関数名	意味
sum()	合計を返す
max()	最大値を返す
min()	最小値を返す
avg()	平均を返す
count()	検索結果の行数を返す

SQL関数を用いていろいろな操作を行ってみてください。

PostgreSQL 8.1.3ドキュメントから上記以外の関数を探して、実際に実行してみて下さい。

insert文はテーブルにデータを挿入するための文です。

insert分の文法
INSERT INTO テーブル名 (列名,列名,...) VALUES (値,値,...);

osテーブルにデータを挿入してみましょう。

mydb=# INSERT INTO os (name,copyright,number,version,p_date) VALUES ('Windows','Microsoft',6,'Server 2003','2003-05-29');

mydb=# SELECT * FROM os;

問題 2:idはinsertしていないにも関わらず、なにやら値が自動で入っています。なぜですか?

update文は既に存在するデータの一部を更新するための文です。

update分の文法
UPDATE テーブル名 SET 列名 = 更新する値 WHERE 条件;

mydb=# UPDATE os SET number = 11 WHERE id = 4;

もしwhere句を省略するとテーブル内の全ての行が更新の対象となる。

delete文は既に存在するデータを削除するための文です。

delete分の文法
DELTE FROM テーブル名 WHERE 条件;

mydb=# DELETE FROM os WHERE name = 'Windows';

もしwhere句を省略するとテーブル内の全ての行が削除の対象となる。

DBに対する処理で切り離すことの出来ない一連の処理の単位をトランザクション(transaction) といいます。DBにおいてトランザクションは必ず「全て処理した」「全て処理しなかった」 のどちらかの状態しかとらないことが保証されます。

トランザクション特性(ACID属性)

Atomicity(原始性)	トランザクションは切り離せない最小の単位	トランザクションは必ずCOMMITかROLLBACKで終わる
Consistency(一貫性)	トランザクション完了時に全てのデータが一貫した状態である	データベースが無矛盾
Isolation(分離性)	トランザクション実行後の状態は、それを逐次実行した時の結果と等しい	他のトランザクションに影響を受けない
Durability(持続性)	トランザクション完了後、その結果はシステム内で持続する	システム障害が発生しても変更結果は有効

PostgreSQLでは"BEGIN;"と"COMMIT;"で囲んだ範囲をトランザクションとして 処理することができます。処理が確定しなかった場合は"ROLLBACK;"を用いて BEGIN以降の処理を無効にします。"BEGIN;"と"COMMIT;"で囲まれた部分を トランザクションブロックといいます。実はPostgreSQLでは単一のSQL(例えば "select * from os;")でも暗黙のうちにトランザクションとして処理されます。 "select * from os;"は"BEGIN; select * from os; COMMIT;"とまったく同じ意味 と見なされるのです。

ここまでの機能でトランザクションの原始性は達成されました。しかし、まだ問題 があります。

複数の処理を含むトランザクションがあり、それを実行している途中になにかしらの 意図しない結果になってしまうことがあります。 そういうときには「これまで実行した処理をなかったことにする」とするのが妥当でしょう。 なぜなら、トランザクションを部分的に実行しただけで終了するとデータベースに矛盾が生じる 可能性があるからです。つまり、トランザクションの一貫性が保たれないのです。

そこで、「これまで実行した処理をなかったことにする」を実現するのがROLLBACKです。

ROLLBACKは「前回COMMITした地点まで状態を戻す」ことができます。

下の図では、ROLLBACKを発行すると、「DELETE os where name = 'Windows'」を実行した直後の状態に戻る。

実際に「削除をなかったことにする」を実行してみましょう。

mydb=# SELECT * FROM os;

mydb=# BEGIN;

mydb=# DELETE FROM os WHERE id = 2;

mydb=# SELECT * FROM os;

mydb=# ROLLBACK;

mydb=# SELECT * FROM os;

排他制御無しの図では、当初20万円だった預金が引き下ろしてみると、28万円に なっていました。どうしてでしょうか?

(排他制御無しの図)

排他制御とはあるデータにロックをかけて、他からのそのデータへのアクセス できないようにする処理のことをいいます。 PostgreSQLでは

"LOCK テーブル名 [ IN LOCKMODE MODE ];"
でテーブルにロックをかけることができます。。ロックは、一度取得されると現行 のトランザクションが完了するまで保持されます。

ロックには大きく別けて二種類のロックがあります。

• 共有ロック(Shared Lock)
  • データ読み出し時にかけられるロック
  • データ更新はしないでくれという意味
  • データを処理中でも他のユーザがデータを読み込むことは許す
  • 共有ロックがかかっているデータに対して、他のユーザは共有ロックをかけることができる
  • 共有ロックがかかっているデータに対して、排他ロックをかけることはできない
• 排他ロック(Exclusive Lock)
  • データ更新時にかけられるロック
  • データ読み出しもデータ更新もしないでくれという意味
  • 他のユーザのアクセスを許さない
  • 排他ロックがかかっているデータに対して、共有ロックをかけることはできない
  • 排他ロックがかかっているデータに対して、排他ロックをかけることはできない

PostgreSQLにおけるLOCKMODEの種類(参考)

ACCESS SHARE	ALTER TABLE、DROPTABLE そして VACUUM FULL コマンドの同時実行によってテーブルが変更されないように保護	ACCESS EXCLUSIVEと競合
ROW SHARE	行の共有ロック	EXCLUSIVE と ACCESS EXCLUSIVE ロックモードに競合
ROW EXCLUSIVE	行の排他ロック	SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVEモードに競合
SHARE UPDATE EXCLUSIVE	このモードはスキーマの変更と VACUUM の同時実行からテーブルを保護	SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE モードに競合
SHARE	CREATE INDEX によって獲得されるロック	ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE モードに競合
SHARE ROW EXCLUSIVE	PostgreSQL コマンドでは、このロックモードを自動的には獲得しません	ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE モードに競合
EXCLUSIVE	ACCESS SHARE 以外を排他	ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE モードに競合
ACCESS EXCLUSIVE	どのような場合でもこの権限を持っているトランザクションのみがテーブルにアクセスできます	すべてのロックモードと競合

(排他制御有りの図)

psqlを二つ起動しlockしたテーブルにアクセスできないことを確認して下さい。

psql(1)

mydb=# BEGIN;

mydb=# LOCK os IN ACCESS EXCLUSIVE MODE;

mydb=# SELECT * FORM os;

psql(2)

mydb=# SELECT * FORM os;

psql(1)

mydb=# COMMIT;

行レベルロック(SELECT FOR UPDATE)

上記のテーブルロック以外のロックとして、行単位のロックがあります。これは行が更新(または削除、または更新対象としてマーク)されると、自動的に対象行がロックされます。このロックは、トランザクションがコミットまたはロールバックされるまで保持します。行レベルロックは、SELECT(データ参照)に関しては影響を与えませんが、ロックしている行を更新(UPDATE, DELETE)しようとすると、対象行のロックが解除されまで待ち状態になります。

これも、psqlを二つ起動し、行レベルロックを確認して下さい。

SELECT文の後に、FOR UPDATEを付加します。

psql(1)

mysql=# BEGIN;

mysql=# SELECT * FROM os WHERE id = 1 FOR UPDATE;

mysql=# UPDATE os set name='LINUX2' WHERE id = 1;

mysql=# SELECT * FROM os WHERE id = 1;

psql(2)

mysql=# SELECT * FROM os WHERE id = 1;

mysql=# UPDATE os set name='HOGEHOGE' WHERE id = 1;

psql(1)

mysql=# COMMIT;

mysql=# SELECT * FROM os WHERE id = 1;

CREATE TABLE "student_id" (
	"id" integer NOT NULL,
	"name" text,
	PRIMARY KEY ("id")
);