Postgresql のスキーマとは何か、なぜスキーマが重要なのか、スキーマを使ってデータベース実装をより堅牢で保守しやすくするにはどうしたらいいのか、疑問に思っていませんか？ この記事では、Postgresqlにおけるスキーマの基本を紹介し、いくつかの基本的な例でスキーマを作成する方法を紹介します。
最初に、潜在的な用語の混乱を解消するために、Postgresql の世界では “スキーマ” という用語は残念ながら多用されていることを理解しておいてください。 リレーショナル データベース管理システム (RDBMS) の広い文脈では、「スキーマ」という用語は、データベースの全体的な論理的または物理的な設計、つまり、データベース定義を構成するすべてのテーブル、列、ビュー、および他のオブジェクトの定義を指すと理解されるかもしれません。 そのような広い文脈では、スキーマはエンティティ関係（ER）図またはアプリケーションデータベースをインスタンス化するために使用されるデータ定義言語（DDL）ステートメントのスクリプトで表現されるかもしれません。 実際、Postgresql のシステム テーブルでは、スキーマは “名前空間” と呼ばれるテーブル列に記録されており、IMHO では、この方がより正確な用語であると考えています。 実際問題として、私は Postgresql のコンテキストで「スキーマ」を見るたびに、黙って「名前空間」と言っていると解釈しています。 一般に、名前空間とは、情報を名前によって整理し、識別するためのかなり柔軟な手段です。 たとえば、スミス家のアリスとボブ、ジョーンズ家のボブとキャシーという2つの隣家を想像してください（図1参照）。 もし、名字だけだと、ボブといえば誰のことかわからなくなるかもしれない。 しかし、スミスやジョーンズという姓を加えることで、どの人を指しているのか一意に特定することができるのです。 これにより、例えばインターネット ドメイン名システムのように、膨大な量の情報を非常に細かい構造に効率的に分類することができます。 トップレベルでは、”.com”, “.net”, “.org”, “.edu “などで広い名前空間を定義し、その中に特定の実体の名前が登録されています。例えば、” severalnines.com” や “postgresql.org” は一意に定義されています。 しかし、それぞれの下に、たとえば “www”、”mail”、”ftp” などの多くの共通サブドメインがあり、それだけでは重複していますが、それぞれの名前空間内ではユニークです。
Postgresql スキーマは、整理と識別の同じ目的を果たしますが、上記の2番目の例と異なり、Postgresql スキーマは階層でネストすることができません。 データベースは多くのスキーマを含むことができますが、レベルは1つだけなので、データベース内ではスキーマ名は一意でなければなりません。 また、すべてのデータベースは少なくとも1つのスキーマを含まなければなりません。 新しいデータベースがインスタンス化されるたびに、”public “という名前のデフォルトスキーマが作成されます。 スキーマの内容には、テーブル、ビュー、ストアドプロシージャ、トリガなど、他のすべてのデータベースオブジェクトが含まれます。 図 2 は、マトリョーシカ人形のような入れ子で、スキーマが Postgresql データベースの構造に適合するところを示しています。
データベース オブジェクトをより管理しやすくするために論理グループに単に整理するだけではなく、スキーマは名前の衝突を回避する実用上の目的も果たします。 1 つの運用パラダイムでは、データベース ユーザーごとにスキーマを定義し、ある程度の分離、つまり、ユーザーが互いに干渉することなく独自のテーブルおよびビューを定義できるスペースを提供します。 また、サードパーティーのツールやデータベース拡張機能を個々のスキーマにインストールし、関連するコンポーネントをすべて論理的にまとめるというアプローチもあります。 このシリーズの後の記事では、堅牢なアプリケーション設計への新しいアプローチについて詳しく説明します。スキーマを間接的な手段として採用し、データベースの物理設計の露出を制限し、代わりに合成キーを解決するユーザー インターフェイスを提示し、システム要件の進化に伴う長期メンテナンスと構成管理を容易にします。
データベース内にスキーマを作成する最も単純なコマンドは
CREATE SCHEMA hollywood;このコマンドはデータベースの作成権限を必要とし、新しく作成されたスキーマ “hollywood” はコマンドを実行したユーザーによって所有されることになります。 より複雑な呼び出しは、異なる所有者を指定するオプションの要素を含み、さらにスキーマ内のデータ・ベース・オブジェクトをインスタンス化するDDL文をすべて1つのコマンドに含むことができます！
一般的なフォーマットは
CREATE SCHEMA schemaname ]です。”username “はスキーマを所有する人、”schema_element “は特定のDDLコマンド（詳細はPostgresqlドキュメントを参照）の一つかもしれません。 AUTHORIZATIONオプションを使用するには、スーパーユーザー権限が必要です。
例えば、1つのコマンドで “films “というテーブルと “winner “というビューを含む “hollywood “というスキーマを作成するには、
CREATE SCHEMA hollywood CREATE TABLE films (title text, release date, awards text) CREATE VIEW winners AS SELECT title, release FROM films WHERE awards IS NOT NULL;追加データベースオブジェクトはその後直接作成されます。例えば追加テーブルは
CREATE TABLE hollywood.actors (name text, dob date, gender text);上記の例の中でテーブル名の前にスキーマ名を付けていることに注意してください。 これは、デフォルトで、つまり、明示的なスキーマ指定がない場合、新しいデータベース オブジェクトは、次に説明する現在のスキーマの範囲内で作成されるためです。 しかし、SmithとJonesのそれぞれの世帯の中では、それぞれの家族は「Bob」がその特定の世帯に属するものを指すと理解しています。 たとえば、それぞれの家庭のコンテキストで、アリスは彼女の夫をボブ・ジョーンズと呼ぶ必要はなく、キャシーは彼女の夫をボブ・スミスと呼ぶ必要はなく、それぞれ「ボブ」と言うだけでよいのです。 現在のスキーマのオブジェクトは無条件に参照できますが、他のスキーマで同じような名前のオブジェクトを参照するには、上記のようにスキーマ名をプレフィックスとして名前を修飾する必要があります。 このパラメータにはスキーマ名のカンマ区切りリストが格納され、
SHOW search_path;コマンドで調べるか、
SET search_path TO schema ;で新しい値を設定します。リストの最初のスキーマ名は「現在のスキーマ」で、スキーマ名修飾なしで指定した場合、新しいオブジェクトが作成される場所です。 たとえば、Smith and Jones の近所に戻ると、”Bob” という名前の最初の住人が見つかるまで、各世帯を訪問する必要があります。 ただし、これは意図した受取人ではないかもしれません。 Postgresqlの場合も同じような論理です。 システムはsearch_pathの順にスキーマ内のテーブル、ビュー、その他のオブジェクトを検索し、最初に見つかった名前に一致するオブジェクトが使用されます。
デフォルトの構成では、search_path構成変数をクエリするとこの値が表示されます
SHOW search_path; Search_path-------------- "$user", publicシステムは上に示した最初の値を現在のログインしたユーザー名として解釈し、各ユーザーが他のユーザーとは別の作業空間のためにユーザー名の付いたスキーマを割り当てられる前述の使用ケースに対応します。 そのようなユーザー名のスキーマが作成されていない場合、そのエントリは無視され、「public」スキーマが新しいオブジェクトが作成される現在のスキーマになります。
したがって、以前の “hollywood.actors” テーブルを作成する例に戻って、テーブル名をスキーマ名で修飾しなかった場合、テーブルはパブリック スキーマで作成されていたことでしょう。 特定のスキーマ内にすべてのオブジェクトを作成することを想定している場合、search_path変数を
SET search_path TO hollywood,public;のように設定すると、データベースオブジェクトを作成またはアクセスするために修飾されていない名前をタイプする省略記法を容易にすることができます。
select current_schema();また、現在のスキーマをクエリで返すシステム情報関数もある
select current_schema();スペルを太くする場合、スキーマの所有者は、ユーザーがデータベースの作成権限も持っていれば
ALTER SCHEMA old_name RENAME TO new_name;そして最後に、データベースからスキーマを削除するために。 DROPコマンドはスキーマにオブジェクトが含まれていると失敗しますので、最初にオブジェクトを削除する必要があります。また、CASCADEオプション
DROP SCHEMA schema_name CASCADE;でスキーマの内容をすべて再帰的に削除することも可能です。