スキーマ復習 - かずきのBlog@hatena

どうもDB弱者です。今日は必要にかられてPostgreSQLのスキーマについて勉強します。

スキーマは？

ネストできないフォルダみたいなもの。こんなSQL文で作れる。

create schema schema_name;

テーブル作ったりは？

スキーマ名.テーブル名でアクセスするの以外は普段通りできる。

create table schema_name.Table1 (
    id serial primary key,
    name varchar(255) not null
);
insert into schema_name.Table1(name) values ('tanaka1');
select * from schema_name.Table1;

現在のスキーマの取得

show search_path;

デフォではpublicが返ってきます。

スキーマ名なしでテーブルアクセス

スキーマを指定しなくてもよくなる方法。要はテーブルを検索するときに、どのスキーマから探すか指定するみたい。

set search_path to schema_name,public;

これでschea_nameとpublicから検索してくれる。これを実行すると

select * from Table1;

とするだけでschema_name.Table1にアクセスできる。

ということでスキーマわけで出来ること

publicスキーマに共通でアクセスできるようなテーブルを置いておいて、テナントごとに同じ構造のテーブルをきってSQL発行前にset search_path toでテナントのスキーマとpublicスキーマを検索パスに追加してからクエリを発行することで、特にクエリ内でテナントの違いを意識しなくてもよくなる？
ということで実験。

tenant1とtenant2というスキーマを作っておく。これが今回のテナント。んで、publicにはrankというユーザーの権限を定義したようなテーブルを持たせておく。
あとは、tenant1とtenant2にusersというテーブルを作ってrankに外部キーを張って、適当なデータをつっこんでおきます。

-- スキーマを追加で作成
create schema tenant1;
create schema tenant2;

-- publicスキーマに共通でアクセス可能なテーブルを作成
create table public.rank (
  id integer primary key,
  name varchar(255)
);
insert into public.rank(id, name) values
  (1, 'user'), (2, 'super user');

-- テナント1用のユーザテーブル
create table tenant1.users (
  id integer primary key,
  name varchar(255),
  rank_id integer,
  foreign key(rank_id) references public.rank(id)
);
insert into tenant1.users(id, name, rank_id) values
  (1, 'tanaka', 1), (2, 'kimura', 2);

-- テナント2用のユーザーテーブル
create table tenant2.users (
  id integer primary key,
  name varchar(255),
  rank_id integer,
  foreign key(rank_id) references public.rank(id)
);
insert into tenant2.users(id, name, rank_id) values
  (1, '井上', 2), (2, '鈴木', 2), (3, '星', 1);

SQLを叩いてみます。

-- テナント1の場合
set search_path to tenant1,public;
select u.id, u.name, r.name from users u
join rank r on u.rank_id = r.id;

実行結果は以下のような感じ

id	name	name
1	tanaka	user
2	kimura	super user

次はテナント2を想定してset search_pathだけ変えて実行してみます。

set search_path to tenant2,public;
select u.id, u.name, r.name from users u
join rank r on u.rank_id = r.id;

id	name	name
1	井上	super user
2	鈴木	super user
2	星	user

うん。ばっちり。