去年 Qiita PostgreSQL 9.4 の JSON サポートについて紹介させてもらったが、まだ書きかけで jsonb の応用例としてや軽い検証記事や SQL イデオムを追記する予定だったが、整理しきれず放置したままになっていた。
そうこうしている内に早くも次期バージョン PostgreSQL 9.5 の情報がちらほらと出始めてきてしまった。
もうまとめるのは諦めることにして、その時のメモや雑多なコードから動くものを本稿に残しておく。
興味とヒマのある方は拾い読みしてほしい。
1. 開発言語からみた jsonb
プログラマの立場からみて、普段の開発で使うORM/フレームワークと jsonb の相性はどうなのか。
ここでは代表的なプログラミング言語環境でさらっとイメージだけつかんでおきたい。
個人的に Docker がマイブーム(死語)なので、検証環境に Docker を活用したい。
postgresSQL の検証環境は DockerHub 公式イメージの library/postgres で、現時点での latest はバージョン 9.4.3 。
お試しなので Data Volume は切らず、パスワードなし、文字コードはデフォルト (en_US.UTF8) で問題なし。
$ docker run --name pg -p 5432:5432 -d postgres
73346173b0b54b99062d154d9eb9962fdde9d9b3e580b992315d59d9bade9f33
$ docker stop pg
pg
$ docker start pg
pg
$ docker exec -it pg psql -U postgres
psql (9.4.3)
Type "help" for help.
postgres=#
楽ちん、ちょー便利。
postgres の docker コンテナにクライアントコンテナからリンクすると、接続情報が環境変数として取得できる。その内容はだいたい以下のようになるはず。
$ docker run --rm --link pg:db postgres printenv | grep DB | sort
DB_ENV_LANG=en_US.utf8
DB_ENV_PGDATA=/var/lib/postgresql/data
DB_ENV_PG_MAJOR=9.4
DB_ENV_PG_VERSION=9.4.3-1.pgdg70+1
DB_NAME=/hopeful_wilson/db
DB_PORT=tcp://172.17.0.4:5432
DB_PORT_5432_TCP=tcp://172.17.0.4:5432
DB_PORT_5432_TCP_ADDR=172.17.0.4
DB_PORT_5432_TCP_PORT=5432
DB_PORT_5432_TCP_PROTO=tcp
サンプルとして使用するテーブルスキーマは以下の通り。
-- 投稿記事的ありがちテーブル
drop table posts;
create table posts (
id serial primary key,
jbpost jsonb, -- スキーマレスな JSON で何でも詰め込む
jbtags jsonb -- タグを JSON 配列で持つ
);
insert into posts (jbpost, jbtags) values
('{"author":"キー太", "message":"日本語でおk"}',
'["hoge", "Qiita"]'
),
('{ "author": "Snafkin",
"title": "Postgres 入門",
"content": "はじめに...",
"comments": ["1get!", "2get!"] }',
'["PostgreSQL", "JSON"]'
);
SQL 文を psql にコピペする場合、-nオプションをつけておかないと日本語がはじかれるのは相変わらずだ。
1.1. JavaScript/Node.js
そもそも JSON は、JavaScript の初期化子(ObjectとArrayのリテラル)を起源とするデータ表現であり、Node.js との親和性は高いはずだ。
Node.js 環境には DockerHub 公式 (library/node) を使ってみる。
$ docker pull node
$ docker run --rm node node --version
v0.12.6
# npm で pg モジュールをローカルインストールする。
# boot2docker などで Windows のフォルダを共有している場合、
# --no-bin-links をつけないとうまくいかない。
$ docker run --rm -it -v "$PWD":/usr/src/myapp -w /usr/src/myapp node npm install pg
pg@4.4.0 node_modules/pg
├── packet-reader@0.2.0
├── pg-connection-string@0.1.3
├── buffer-writer@1.0.0
├── generic-pool@2.1.1
├── pg-types@1.7.0
├── semver@4.3.6
└── pgpass@0.0.3 (split@0.3.3)
$ ls
node_modules/
取りあえず SELECT だけしてみよう。
//console.log(process.env);
var pg = require('pg');
pg.defaults.user = "postgres";
var conString = process.env.DB_PORT_5432_TCP + "/postgres";
var client = new pg.Client(conString);
client.on('drain', client.end.bind(client));
client.connect(function(err) {
if(err) {
return console.error('could not connect to postgres', err);
}
var filter = { author: "Snafkin" };
client.query('SELECT * FROM posts WHERE jbpost @> $1 ', [ filter ], function(err, result) {
if(err) {
return console.error('error running query', err);
}
console.log(result.rows);
console.log(result.rows[0].jbpost.title);
console.log(result.rows[0].jbpost.comments);
console.log(result.rows[0].jbpost.comments[1]);
client.end();
});
});
$ ls
pg_json.js node_modules
$ docker run --rm -it --link pg:db -v "$PWD":/usr/src/myapp -w /usr/src/myapp node node pg_json.js
[ { id: 2,
jbpost:
{ title: 'Postgres 入門',
author: 'Snafkin',
content: 'はじめに...',
comments: [Object] },
jbtags: [ 'PostgreSQL', 'JSON' ] } ]
Postgres 入門
[ '1get!', '2get!' ]
2get!
シームレスだよ、おっ母さん！
なんと jsonb カラムは JavaScript オブジェクトツリーとして返されている。
パラメータに渡した JavaScript の Object はそのまま JSON として使われている。
すばらしい。
パラメータに JSON を文字列で渡しても OK だ。
しかしなぜだか Array ではエラーになる。
// var filter = ["JSON"]; // error running query { [error: invalid input syntax for type json]
var filter = '["JSON"]'; // 文字列 OK
client.query('SELECT * FROM posts WHERE jbtags @> $1 ', [filter]...
INSERT 文などでパラメータの型によって気を使いたくない。
いっそのことパラメータを全部 JSON 文字列にまとめると jsonb_populate_record() で楽できる。
// エンティティクラス
function Post(author, title, content) {
this.author = author;
this.title = title;
this.content = content;
}
var post = new Post("Moomin", "My Love", "のんのおぉぉぉぉーー!");
var tags = '["Nonon"]';
var client = new pg.Client(conString);
client.on('drain', client.end.bind(client));
client.connect();
// データを JSON 文字列にまとめて jsonb_populate_record() を通す
var data = JSON.stringify( {jbpost: post, jbtags: tags} );
var insert = client.query(
"INSERT INTO posts (jbpost, jbtags) SELECT jbpost, jbtags FROM jsonb_populate_record(null::posts, $1) RETURNING * ",
[ data ]
);
insert.on("error", function(err){console.log(err);});
insert.on("row", function(row, result) {
console.log(row);
});
UPDATE 文ではそのような救いはなく、カラムごとの対応が避けられない。
(この点は 9.5 で改善される予定)
var update = client.query(
"UPDATE posts SET (jbpost, jbtags) = ($2, $3) WHERE id = $1 RETURNING * ",
[id, post, JSON.stringify(tags)]
);
update.on('error', function(err){console.log(err);});
update.on("row", function(row, result) {
console.log(row);
});
また、JSON データ内部の差分だけを更新することはできず、丸ごと上書きとなる。
まあ、深入りしないで次に行こう。
【参考】
- brianc/node-postgres - GitHub
https://github.com/brianc/node-postgres
1.2. Ruby/ActiveRecord
Rails は PostgreSQL をよく見ている。
Rails 4.2 で jsonb がサポートされている。
- Ruby on Rails 4.0 リリースノート 2013/06/27
- PostgreSQLの配列型サポートが追加されました。配列カラムの作成時に任意のデータ型を使用できます。それらのデータ型はフルマイグレーションやスキーマダンプでもサポートされます。
- PostgreSQL hstoreレコードを作成できるようになりました。
- Ruby on Rails 4.1 リリースノート 2014/04/08
- json値が書き込み時に型変換されます。これにより値がデータベースからの読み出し時と一貫します。
- [Ruby on Rails 4.2 リリースノート] (http://railsguides.jp/4_2_release_notes.html) 2014/12/19
- PostgreSQLアダプターでPostgreSQL 9.4+のjsonbデータタイプがサポートされました。
つまり jsonb 型を明示的に指定できるということ。
create_table :hoge do |t|
t.jsonb :jb_data
end
これは期待できる。
Docker 環境で試してみよう。
# ActiveRecord と postgres アダプタの gem が入ったイメージを用意する。
# DockerHub 公式の ruby:onbuild を使うと、Gemfile を用意するだけで
# その内容をインストールした ruby 環境イメージを簡単に作成することができる。
$ docker pull ruby
$ docker run --rm ruby ruby --version
ruby 2.2.2p95 (2015-04-13 revision 50295) [x86_64-linux]
$ ls
Gemfile Dockerfile
$ cat Gemfile
source "https://rubygems.org"
gem "activerecord"
gem "pg"
$ cat Dockerfile
FROM ruby:onbuild
$ docker run --rm -v $PWD:/usr/src/app -w /usr/src/app ruby bundle install
$ ls
Gemfile Gemfile.lock Dockerfile
# イメージをビルドする。名前は適当
$ docker build -t pgruby .
$ docker run --rm pgruby bundle list
Gems included by the bundle:
* activemodel (4.2.1)
* activerecord (4.2.1)
* activesupport (4.2.1)
:
* pg (0.18.2)
:
PostgreSQL にアクセスしてみよう。
require 'active_record'
require 'logger'
require 'pp'
#pp ENV
ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.establish_connection(
adapter: "postgresql",
host: ENV["DB_PORT_5432_TCP_ADDR"],
username: "postgres"
)
class Post < ActiveRecord::Base
end
filter = '{"author": "Snafkin"}'
posts = Post.where("jbpost @> ?", filter)
pp posts
posts.each do |post|
puts post.jbpost.class
puts post.jbtags.class
puts post.jbpost['title']
puts post.jbtags[0]
end
$ ls
pg_json.rb
$ docker run -it --rm --link pg:db -v $PWD:/usr/src/app -w /usr/src/app pgruby ruby pg_json.rb
D, [2015-07-01T00:00:00.000000 #1] DEBUG -- : Post Load (1.2ms) SELECT "posts ".* FROM "posts" WHERE (jbpost @> '{"author": "Snafkin"}')
[#<Post:0x007fb52c544f50
id: 2,
jbpost:
{"title"=>"Postgres \u5165\u9580",
"author"=>"Snafkin",
"content"=>"\u306F\u3058\u3081\u306B...",
"comments"=>["1get!", "2get!"]},
jbtags: ["PostgreSQL", "JSON"]>]
Post
Hash
Array
Postgres 入門
PostgreSQL
すばらしい。
jsonb が Ruby の Hash と Array にそのまま変換されている。
ただし where() に渡すパラメータには、いろいろ試してみたが、JSON の文字列でしか指定できないようだ。
とりあえず Hash などを渡す時にはあらかじめ JSON 文字列に変換しておこう。
また where() にネイティブな @> 演算子がそのまま記述できているが、? 演算子などはパラメータのプレースホルダ '?' と干渉するという。
で、一個問題があって、JSONB用の一部の演算子に"?“ が使われていて、これがActiveRecordのプレースホルダ用の演算子(”?“)とバッティングしています。
なんとかエスケープとかできたいところだが、とりあえず、ハッシュ渡し（名前付きプレースホルダ）の方で回避できる。
posts = Post.where("tags ? ?", 'hoge') # NG
posts = Post.where("tags ? :tag", {tag: 'hoge'}) # OK
でもこれは Rails だけの問題じゃなはずだ。
データの更新にはハッシュや配列をそのまま代入するか、JSON文字列を渡してもいい。
JSON 文字列を設定してもパースされず、だだの文字列値として保存される。
post.jbpost['title'] = "How to breed Nyoro-nyoro for beginners"
post.jbpost['meta'] = {modified_at: DateTime.now()} # Hash
post.jbpost['comments'] = ["Good", "Bad"] # Array
post.jbpost['style'] = '{"color": "#FF0000"}' # JSON 文字列 は文字列のまま
post.save
create でもハッシュや配列がそのまま渡せる。
Ruby 2.2 で拡張された Hash のリテラル記法はネイティブ JSON とほとんど区別がつかない。
Post.create(
{
"jbpost": {
"author": "Moomin",
"title": "Ruby on Rails 入門",
"content": {"chapters": []},
},
"jbtags": ["Ruby", "ActiveRecord"]
}
)
【参考】
-
RailsでPostgreSQLのJSONBデータ型を使う | 日々雑記
http://y-yagi.tumblr.com/post/110683307855/rails-postgresql-jsonb -
Active Record and PostgreSQL — Ruby on Rails Guides
http://edgeguides.rubyonrails.org/active_record_postgresql.html -
Ruby 2.2.0 リファレンスマニュアル > リテラル > ハッシュ式
http://docs.ruby-lang.org/ja/2.2.0/doc/spec=2fliteral.html#hash
1.3. PHP/PDO
どんどん行こう、次はPHP/PDO だ。
# DockerHub 公式の php はほぼスッピンだが、イメージ内に
# docker-php-ext-install というツールが用意されている。
# これに extention 名を指定して実行するだけでビルド・インストールされる。
# インストールできる extension は /usr/src/php/ext 配下に置かれている。
$ docker pull php
$ docker run --rm php php -v
PHP 5.6.10 (cli) (built: Jun 12 2015 21:13:26)
Copyright (c) 1997-2015 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2015 Zend Technologies
$ ls
Dockerfile
$ cat Dockerfile
FROM php:latest
RUN apt-get update && apt-get install -y libpq-dev && docker-php-ext-install pdo_pgsql
# イメージをビルド
$ docker build -t pgphp .
$ docker run --rm pgphp php -m | grep -i pdo
PDO
pdo_pgsql
pdo_sqlite
<?php
$host = getenv('DB_PORT_5432_TCP_ADDR');
$port = getenv('DB_PORT_5432_TCP_PORT');
$user = 'postgres';
$pass = 'postgres';
$dsn = "pgsql:dbname=postgres;host={$host};port={$port}";
try {
$pdo = new PDO($dsn, $user, $pass);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = 'SELECT * from posts where jbpost @> ? ';
$stmt = $pdo->prepare($sql);
$stmt->execute(array('{"author":"Snafkin"}'));
$rows = $stmt->fetchall();
var_dump($rows);
print $rows[0]["jbpost"] . "\n";
$pdo = null;
} catch (PDOException $e) {
print($e->getMessage() . "\n");
}
$ ls
pg_json.php
$ docker run -it --rm --link pg:db -v $PWD:/usr/src/app -w /usr/src/app pgphp php pg_json.php
array(1) {
[0]=>
array(3) {
["id"]=>
int(2)
["jbpost"]=>
string(111) "{"title": "Postgres 入門", "author": "Snafkin", "content": "はじめに...", "comments": ["1get!", "2get!"]}"
["jbtags"]=>
string(22) "["PostgreSQL", "JSON"]"
}
}
{"title": "Postgres 入門", "author": "Snafkin", "content": "はじめに...", "comme
nts": ["1get!", "2get!"]}
PDO はパラメータもカラム値の戻りも JSON を文字列として扱われる。
Node.js や Rails のように、JSON をオブジェクトツリーで操作したいだろうか。
PHP 標準の JSON 関数(json_encode()/json_decode())を使えば、stdClass や連想配列に変換できる。
json_encode() で自前のクラスでも JSON に変換可能だが、逆のマッピングはそう簡単にはいかない。
class Post {
public $id = null;
public $jbpost = array("title" => "Hello PDO");
public $jbtags = array("PDO", "JSON");
};
$post = new Post;
// 行全体を JSON でインサート
$sql = 'INSERT INTO posts(jbpost, jbtags) SELECT jbpost, jbtags FROM jsonb_populate_record(null::posts, ?)';
$stmt = $pdo->prepare($sql);
$stmt->execute(array(json_encode($post)));
// いったん行全体を JSON にして返す
$id = $pdo->lastInsertId('posts_id_seq');
$sql = 'SELECT row_to_json(posts) from posts where id = ? ';
$stmt = $pdo->prepare($sql);
$stmt->execute(array($id));
// stdClass オブジェクトに変換する
foreach ($stmt->fetchall(PDO::FETCH_FUNC, 'json_decode') as $row) {
print $row->jbpost->author . "\n";
print $row->jbtags[0] . "\n";
}
1.4. Java/JDBC
Java/JDBC での jsonb に関しては、すでにネット上にいくつかの検証記事が公開されていて、単純にはうまくいかないことがわかっている。
以下の記事に詳しい。
- [PostgreSQL 9.4 の新機能 JSONB 型を使ってみよう]
(https://www.postgresql.jp/events/jpugpgcon2014files/B5_PGCon2014-JSONB-datatype) - PGCon2014/ぬこ＠横浜氏講演資料PDF
JSON データを受け取るだけなら String 扱いで可能だが、逆に PostrgeSQL に送る場合、可変長文字列から jsonb への型変換で拒否されてしまうようだ。
Caused by: org.postgresql.util.PSQLException: ERROR: column "jbpost" is of type jsonb but expression is of type character varying
上記記事などによれば、JDBC PreparedStatement に引き渡すオブジェクトに jsonb 型を設定した org.postgresql.util.PGobject を指定することで解決できるという。
// コードは上記記事参照
PDO でもただの文字列渡しなだけのように見えるが、そんな細かい対応がされているのだろうか？
まあ、そこは深入りせず現実的な対応の方を考えよう。
生 JDBC ではなく、ORM フレームワークなどを利用する場合、どこかにカラム型を変換する拡張を仕込まなければならない。
すでにその実例もいくつか見られる。
-
JPA の@AttributeConverterの実装で解決した例
* [Glassfish JPA JSONB - @//メモ]
(http://hondou.homedns.org/pukiwiki/pukiwiki.php?Glassfish%20JPA%20JSONB) -
Hibernate の UserType として実装した例
-
pires/hibernate-postgres-jsonb - GitHub
-
[Mapping postgreSQL JSON column to Hibernate value type - Stack Overflow]
(http://stackoverflow.com/questions/15974474/mapping-postgresql-json-column-to-hibernate-value-type)
Java 開発者は環境や実装に依存してコードが汚されるのを嫌う。
JPA などの標準に従った開発プロジェクトによっては、エンティティクラスをフレームワーク固有のアノーテーションなどで汚されるのは許しがたいかもしれない。
もしネイティブ SQL の使用が許されるならパラメータにキャストを仕込むという安直な方法もある。
INSERT INTO posts(jbpost) values (?::jsonb);
INSERT INTO posts(jbpost) values (CAST( ? AS jsonb));
大抵のフレームワークは要所でネイティブ SQL を差し込む口を用意している。
その SQL 文を外出しにできる事を示せれば、潔癖症のママも許してくれるかもしれない。
ここでは、その方向を模索してみる。
1.4.1. JPA/Hibernate
たとえば Hibernate はカスタム SQL という拡張機能を持つ。
カスタム SQL を使うと INSERT や UPDATE、DELETE までネイティブな SQL 文で差し替えることができる。
カスタム SQL ではネイティブ SQL を@SQLInsertといったアノーテーションや、XMLのマッピングファイルに記述することができる。
package hoge;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
//org.hibernate:hibernate-entitymanager:4.3.8.Final
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQuery;
import javax.persistence.Persistence;
import javax.persistence.Table;
import org.hibernate.annotations.SQLInsert;
import org.hibernate.annotations.SQLUpdate;
//org.projectlombok:lombok:1.16.2
import lombok.Data;
/** エンティティクラス */
@Data
@Entity()
@Table(name = "posts")
// Hibernate カスタム SQL。 XMLファイルでも定義可。
@SQLInsert(sql = "INSERT INTO posts(jbpost, jbtags) VALUES (?::jsonb, ?::jsonb)", callable = true)
@SQLUpdate(sql = "UPDATE posts SET (jbpost, jbtags) = (?::jsonb, ?::jsonb) where id = ?", callable = true)
// JPQL
@NamedQuery(name = "find", query = "select p from Post p where p.id = :id")
class Post {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
Integer id;
String jbPost; // JSON テキスト
String jbTags; // JSON テキスト
}
/** デモ */
public class JpaHibernatePgJsonDemo {
public static void main(String[] args) {
final EntityManagerFactory emf = getEntityManagerFactory();
final EntityManager em = emf.createEntityManager();
EntityTransaction tx = em.getTransaction();
try {
Post post = new Post();
post.setJbPost("{\"title\":\"カスタムSQL\"}");
post.setJbTags("[\"Hibernate\", \"Custom SQL\"]");
// INSERT
tx.begin();
em.persist(post);
tx.commit();
System.out.println(post);
// UPDATE
tx.begin();
post.setJbPost("{\"author\":\"Snafkin\"}");
tx.commit();
// ネイティブクエリ
@SuppressWarnings("unchecked")
List<Post> posts = em.createNativeQuery(
"select * from posts where jbpost @> cast(:filter as jsonb)", Post.class)
.setParameter("filter", "{\"author\":\"Snafkin\"}")
.getResultList();
for (Post p : posts) {
System.out.println(p);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
em.close();
}
emf.close(); // これを忘れるとスレッドが残る。
}
/** PostgreSQL セッション初期化 */
private static EntityManagerFactory getEntityManagerFactory() {
Map<String, Object> props = new HashMap<>();
props.put("javax.persistence.jdbc.url",
"jdbc:postgresql://localhost:5432/postgres");
props.put("javax.persistence.jdbc.user", "postgres");
props.put("javax.persistence.jdbc.password", "postgres");
return Persistence.createEntityManagerFactory("default", props);
}
}
<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0">
<persistence-unit name="default" transaction-type="RESOURCE_LOCAL">
<properties>
<property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
<property name="hibernate.show_sql" value="true"/>
<property name="hibernate.format_sql" value="true"/>
</properties>
</persistence-unit>
</persistence>
# 省略
取りあえず jsonb キャスト作戦で JSON が String として扱えることは確認できたがかなり苦労した。
少なくとも Hibernate はお勧めしない。
ます ネイティブクエリではパラメータのプレースホルダが干渉しまくる。
? 演算子は仕方ないとしても、::jsonb キャストが名前付きパラメータのプレースホルダと認識されてエラーになる。
JPA 的には、名前付きパラメータはサポートしないはずなのだが、親切なことだ。
カスタム SQL にもクセがあって、 SQL のパラメータの順序が強制的に固定されてしまい、しかもその規則が定義されない。
マニュアルの Tips によれば、パラメータの順序を知るにはログレベルを DEBUG にした上で、出力された SQL 文から実際の順序を確認しろという。(ドイヒー)
これでは運用保守に余計な負担をかけ、Hibernate の将来の実装で変わるリスクも抱えてしまう。
まったく、愛のない話だ。
1.4.2. MyBatis
そもそも JPA のような DB を隠蔽し抽象化し標準化する理想主義的なフレームワーク上で、非標準的なことをやろうとするから無理が出る。
その点、現実主義の MyBatis なら遠慮はいらない。
MyBatis がオブジェクトにマッピングするのはテーブルではなくネイティブな SQL 文そのものだ。
package hoge;
import java.util.List;
//'org.projectlombok:lombok:1.16.2'
import lombok.Data;
//'org.mybatis:mybatis:3.2.8'
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
//'org.postgresql:postgresql:9.4-1200-jdbc41'
import org.postgresql.ds.PGSimpleDataSource;
public class MyBatisPgJsonDemo {
/** エンティティ クラス */
@Data
public static class Post {
Integer id;
String jbPost; // JSON テキスト
String jbTags; // JSON テキスト
}
/** データアクセスマッパ */
public static interface PostMapper {
// SQL の記述はアノーテーションより外部XMLファイルの方が扱いやすい。
@Insert("INSERT INTO posts (jbpost, jbtags) VALUES (#{jbPost}::jsonb, #{jbTags}::jsonb)")
@Options(useGeneratedKeys = true, keyProperty = "id")
public int create(Post book);
@Update("UPDATE posts SET (jbpost, jbtags) = (#{jbPost}::jsonb, #{jbTags}::jsonb) where id = #{id}")
public int save(Post post);
@Select("SELECT * FROM posts WHERE id = #{id}")
public Post find(@Param("id") int id);
@Select("SELECT * FROM posts WHERE jbPost @> #{filter}::jsonb ")
public List<Post> findBy(@Param("filter") String json_filter);
}
/** デモ */
public static void main(String[] args) {
try (SqlSession session = openPgSession()) {
PostMapper mapper = session.getMapper(PostMapper.class);
Post post = new Post();
post.setJbPost("{\"author\":\"Snafkin\", \"title\": \"MyBatis 入門\"}");
post.setJbTags("[]");
mapper.create(post);
System.out.println(post);
post.setJbTags("[\"MyBatis\", \"Postgres\"]");
mapper.save(post);
List<Post> posts = mapper.findBy("{\"author\":\"Snafkin\"}");
for (Post p : posts) {
System.out.println(p);
}
session.rollback();
} catch(Exception e) {
e.printStackTrace();
}
}
/** PostgreSQL セッション初期化 */
private static SqlSession openPgSession() {
org.apache.ibatis.logging.LogFactory.useStdOutLogging();
PGSimpleDataSource ds = new PGSimpleDataSource();
ds.setServerName("localhost");
ds.setDatabaseName("postgres");
ds.setUser("postgres");
ds.setPassword("postgres");
Environment env = new Environment("devel",
new JdbcTransactionFactory(), ds);
Configuration conf = new Configuration(env);
conf.addMapper(PostMapper.class);
return new SqlSessionFactoryBuilder().build(conf).openSession();
}
}
# 省略
JSON データは String でマッピングされる。
JSON を String ではなく Map やクラスなどの Java オブジェクトにマッピングしたいとなると面倒だ。
MyBatis にも DB と Java のデータ変換をカスタマイズするめの仕掛け(TypeHandlerなど)が用意さているので、それを実装して好きな JSON ライブラリを使って、ドメインクラスへのバインディングなり、ベーシックな Map のツリーに変換することもやればできる。
そこで MOXy のような JSON をサポートした JAXB 実装を利用すれば、XML バインディングを使ったレガシーなクラスをそのまま JSON ベースに移行させることなんかもできるかもしれない。
もっとも 近頃 DB にアクセスするのはサービス API とかバックエンド側とか呼ばれるレイヤーであり、大抵 RESTFul 的な JSON API を提供しているだろう。
JSON をクラスに詰め替えてまた JSON に変換するというはちょっと無駄な感じもする。
それならいっそドメインロジックを SQL に寄せてサービスレイヤは極薄にしておき、JSON を String のまま丸投げしあうような設計にするというのも MyBatis ならアリだ。
結論としては Java で jsonb をそのまま使うのは面倒で、フレームワークなどによって個別の検証と配慮が必要になり、本格活用には時期尚早といえるだろう。
jsonb が今後 Java のフレームワークで対応されるようになるには、 JSON が SQL 標準に採用される日を待たねばならない。
- [次のSQL標準は何が盛り込まれる？ －第2回DBオフライン ：EnterpriseZine（エンタープライズジン）]
(http://enterprisezine.jp/dbonline/detail/6841)（要登録）
【参考】
-
PostgreSQL 9.4 の新機能 JSONB 型を使ってみよう- PGCon2014/ぬこ＠横浜氏講演資料PDF
https://www.postgresql.jp/events/jpugpgcon2014files/B5_PGCon2014-JSONB-datatype
jsonb まとめとして必読資料 -
PostgreSQLのJSON列にJavaからデータを登録する - Symfoware
http://symfoware.blog68.fc2.com/blog-entry-1246.html -
Hibernate Reference Documentation 4.3.9.Final 18.3. Custom SQL for create, update and delete
http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch18.html#querysql-cud -
MyBatis – MyBatis 3 | 設定
https://mybatis.github.io/mybatis-3/ja/configuration.html#typeHandlers -
知っているようで知らないプリペアードクエリ | yohgaki's blog
http://blog.ohgaki.net/postgresql-prepared-query-explained
Data Type の OID を使用するかは API 側の実装によるらしい。
2. JSON for SQL
SQL で JSON が使えると何ができるのか。
NoSQL、ドキュメント指向、スキーマレスなデータストアといった新たなパラダイムとの融合が開く可能性に妄想は膨らむが、現実にはちょっと便利な拡張カラムと言ったところか。
しかし視点を変えれば、jsonb で許された柔軟性と操作性は、SQL の持つレガシーな問題に新たな攻略法を提供する強力アイテムにもなるかもしれない。
2.1. ページネーションをなんとかする
一覧表示のページ分けで、指定範囲のデータと総件数を１回のクエリで取りたい。
DB 側で JSON にまとめて返してもらえるとありがたいのだが。
以下のようなイメージで出力できたい。
{
"total": 123456,
"offset": 20,
"count": 10,
"rows": [
{"id": 2, "product_name": "かにバン", "product_code": "X123",・・・・・・},
{"id": 6, "product_name": "２色バン", "product_code": "X456", ・・・・・・},
・
・
・
]
}
json_build_object()やjson_agg()を使えばページ情報と検索結果のデータを一個の JSON にまとめることができる。
WITH hits AS (
-- 検索条件と位置による絞り込み
-- ここでは Index Only Scan になるよう心掛ける。
SELECT
p.id, -- 検索結果、データは ID のみ
count(p.id) OVER() AS _total, -- 総件数
row_number() OVER( ORDER BY #{sort_cond} ) -- ソート条件
AS _position -- 行番号
FROM
products AS p
INNER JOIN makers AS m ON p.id = m.product_id
INNER JOIN ・・・
INNER JOIN ・・・
・ -- 悪夢の連鎖
・
WHERE TRUE
#{search_cond} -- 邪悪な呪文
LIMIT #{page_size} -- １ページあたりの行数
OFFSET #{offset} -- 開始位置
),
rows AS (
-- 検索結果から一覧項目を収集する。
SELECT p.id, p.product_name, p.product_code, m.maker_name, ・・・
FROM hits
INNER JOIN products AS p ON hits.id = p.id
INNER JOIN makers AS ON p.id = m.product_id
INNER JOIN ・・ -- サボテンモンスター
ORDER BY hits._position
)
SELECT
json_build_object(
'total', hits._total, -- 総件数
'offset', min(hits._position - 1), -- 先頭行の位置
'count', count(hits.id), -- 取得件数
'rows', json_agg(rows) -- 検索結果リスト
) AS result
FROM hits LEFT JOIN rows USING(id)
GROUP BY hits._total
;
2.2. EAV を JSON でなんとかする
EAV（Entity-Attribute－Value） は SQL アンチパターンの一つとして知られる。
json_object_agg() を使うと、EAV なデータを JSON としてまとめることができる。
-- EAV なテーブル設計
CREATE TABLE issues ( issue_id SERIAL PRIMARY KEY);
CREATE TABLE issue_attributes (
issue_id int references issues(issue_id),
attr_name varchar(100) NOT NULL,
attr_value varchar(100)
);
-- JSONにまとめてマテビューで固める
-- MATERIALIZED VIEW は 9.3 からのサポート
CREATE MATERIALIZED VIEW json_issues AS
SELECT issue_id, json_object_agg(attr_name, attr_value) AS attrs
FROM issue_attributes
GROUP BY issue_id
;
SELECT * FROM json_issues
WHERE attrs->>'issue_type' = 'BUG'
;
issue_id | at
trs
----------+---------------------------------------------------------------------
----------------------------------------------------------------------
1 | { "reported_by" : "101", "product_id" : "201", "issue_type" : "BUG",
"status" : "OPEN", "severity" : "HIGH", "version_affected" : "1.0" }
2 | { "reported_by" : "102", "product_id" : "202", "issue_type" : "BUG",
"status" : "OPEN", "severity" : "LOW", "version_affected" : "1.2" }
(2 rows)
EAV に対するソリューションの一つに「シリアライズ LOB パターン」というのがある。
サブタイプに固有の非共通データはまとめて LOB カラムに詰め込んでしまう。
LOB のフォーマットには、JSON がふさわしい。
json_object_agg() を使って、EAV を シリアライズ LOB なテーブルに変換してみよう。
-- シリアライズ LOB なテーブル
CREATE TABLE lob_issues (
-- 共通カラム
issue_id int PRIMARY KEY,
reported_by int,
product_id int,
priority varchar(20),
version_resolved varchar(20),
status varchar(20),
issue_type varchar(10),
-- JSON LOB カラム
attributes jsonb
);
-- EAV テーブルを LOB テーブルに変換する
with cols(name) as (
-- LOB テーブルのカラム名を収集
SELECT * FROM json_object_keys(row_to_json(
jsonb_populate_record(null::lob_issues, '{}')
))
),
src(id, name, value) AS (
-- ネタとなる EAV データ
SELECT issue_id, attr_name, attr_value FROM issue_attributes
UNION ALL
SELECT issue_id, 'issue_id', issue_id::text FROM issue_attributes
),
base(id, name, value) AS (
-- 共通カラムのデータを仕分ける
SELECT src.* FROM src INNER JOIN cols USING(name)
),
ext(id, name, value) AS (
-- 共通カラム以外のデータを仕分ける
SELECT src.* FROM src LEFT JOIN cols USING(name)
WHERE cols.name IS NULL
),
data(id, jb_data) AS (
-- いったんすべてのカラムをjsonbにまとめる（INSERTで楽をするため）
SELECT id, json_object_agg(name, value)::jsonb as data
FROM (
SELECT id, name, to_json(value) AS value FROM base
UNION ALL
SELECT id, 'attributes' AS name, json_object_agg(name, value) AS value -- LOB
FROM ext GROUP BY id
) AS x
GROUP BY id
)
INSERT INTO lob_issues
SELECT rec.*
FROM data, jsonb_populate_record(NULL::lob_issues, data.jb_data) AS rec
;
長い SQL になったが、実行するのは一回だけなのでこれでよしとしよう。
SELECT
issue_id,
product_id,
attributes->>'severity' AS severity,
attributes->>'version_affected' AS version_affected
FROM lob_issues
WHERE status <> 'CLOSED'
AND issue_type = 'BUG'
;
issue_id | product_id | severity | version_affected
----------+------------+----------+------------------
1 | 201 | HIGH | 1.0
2 | 202 | LOW | 1.2
(2 rows)
参考
- SQLアンチパターン - Bill Karwin オライリー・ジャパン
http://www.oreilly.co.jp/books/9784873115894/
2.3. ナイーブツリーを JSON でなんとかする
レコードに親子関係のあるテーブルを、再帰的な階層構造をもつ一個の JSON に変換してみる。
出力イメージ
{
"id":1,
"content":"1. aaaaaaaaa",
"children":[
{
"id":2,
"content":"1.1. bbbbbbbbb",
"children":[]
},
{
"id":3,
"content":"1.2. ccccccccc",
"children":[
{
"id":4,
"content":"1.2.1. dddddddd",
"children":[
:
:
:
]
}
-- ナイーブなテーブル設計
CREATE TABLE comments (comment_id int PRIMARY KEY, parent_id int, comment text);
-- ナイーブなサンプルデータ
INSERT INTO comments VALUES
(1, NULL, '1'), (2, 1, '1-1'), (3, 1, '1-2'), (4, 2, '1-1-1'),
(5, 2, '1-1-2'), (6, 5, '1-1-2-3'), (7, 1, '1-3'), (8, NULL, '2')
;
-- ナイーブツリーを JSON 階層に変換する。
-- 引数で指定した ID のノード以下を取得する。
CREATE FUNCTION json_comments_tree(pid int) RETURNS json
AS $$
WITH sub_tree(children) AS (
SELECT array_agg( json_comments_tree( comment_id ) ) -- 再帰
FROM comments WHERE parent_id = pid
),
tree AS (
SELECT node.*, children
FROM comments AS node, sub_tree
WHERE node.comment_id = pid
)
SELECT row_to_json(tree, FALSE) -- 整形は役に立たない。
FROM tree
;
$$ LANGUAGE SQL;
再帰 CTE ではうまくいかず断念。
ストアドファンクションで再帰関数として作成した。
SELECT json_comments_tree(2)->'children'->1;
?column?
-------------------------------
{"comment_id":5, +
"parent_id":2, +
"comment":"1-1-2", +
"children":[{"comment_id":6,+
"parent_id":5, +
"comment":"1-1-2-3", +
"children":null}]}
(1 row)
SELECT json_comments_tree(1)#>>'{children,0,children,1,comment}';
?column?
----------
1-1-2
(1 row)
参考
- SQLアンチパターン - Bill Karwin オライリー・ジャパン
http://www.oreilly.co.jp/books/9784873115894/
2.4. OTLT を JSON でなんとかする
いわゆるコードマスターテーブルは OTLT（One True Lookup Table）という SQL アンチパターンに該当する。
クエリによってはこれを何個も JOIN するハメになる。
コードマスターを一個の JSON に変換しておけば、クエリ内では連想配列として扱えるはずだ。
-- OTLT なテーブル設計
CREATE TABLE code_master (
id int,
code_type varchar(20),
code varchar(10),
value varchar(40)
);
-- JSONにまとめてマテビューに固める
-- MATERIALIZED VIEW は 9.3 からのサポート
CREATE MATERIALIZED VIEW code_lookup (get)
AS
SELECT json_object_agg( code_type, code_values )::jsonb AS get
FROM (
SELECT code_type, json_object_agg(code, value) AS code_values
FROM code_master GROUP BY code_type
) AS m
;
SELECT
t.title,
l.get->'task'->>t.task_code AS task,
l.get->'status'->>t.status_code AS task_status,
l.get->'status'->>t.review_status_code AS review_status,
l.get->'status'->>t.release_status_code AS release_status,
l.get->'priority'->>t.priority_code AS priority
FROM
tickets AS t,
code_lookup AS l
;
-- パラメータ化して実行時に指定可能
SELECT
l.get->#{code_type}->>#{code} AS code_value,
FROM
code_lookup AS l
;
2.5. ログちゃんぽん
スキーマレス(Schemaless)なストアとは、スキーマを選ばない(Schema Independent)ストアであるともいえる。
たとえば Fluentd の出力を PostgreSQL に保存することを考える。
Fluentd の出力するログレコードは JSON 形式なのでそのまま jsonb 型のカラムに格納できる。
しかもたった一つのテーブルで、スキーマの異なる複数のサービスのログを保存できる。
JSON 型で格納する ProstreSQL 用の出力プラグインはすでにあって、たとえばfluent-plugin-pgjsonでは以下の様なシンプルなテーブルを定義になる。
CREATE TABLE fluentd (
tag Text
,time Timestamptz
,record jsonb
);
このログレコードは SQL で集計・分析ができる。
以下のようなクエリで、まとめてエラーログを取得するここともできる。
問題が発生したときに、各サービスレイヤーからのログファイルを慌ててかき集めずとも、一括でログレコードが時系列に並べられてうれしいかもしれない。
-- 直近15秒のエラーを検索する
SELECT * FROM fluentd
WHERE stamp > date_trunc('sec', now()) - interval(' 15 sec ')
AND ( record @> '{"code":500}'
OR record @> '{"level":"error"}'
OR record @> '{"priority":"ERR"}'
OR record @> '{"error_severity":"ERROR"}'
)
ORDER BY stamp DESC
;
参考
-
choplin/fluent-plugin-pgjson - github
https://github.com/choplin/fluent-plugin-pgjson -
PostgreSQLのログをFluentdで収集する - Tech-Sketch
http://tech-sketch.jp/2014/07/postgresqlfluentd.html
2.6. 監査ログテーブル
スキーマレスなデータのちゃんぽんが役に立ちそうなもう一つの例として監査ログがある。
どんなテーブルのデータでも JSON にシリアライズして保存すれば、汎用的な監査テーブルひとつで十分になる。
-- 汎用監査履歴テーブル
DROP TABLE IF EXISTS audit_history;
CREATE TABLE audit_history (
tstamp timestamptz,
event text,
user_name text,
table_name text,
data jsonb
);
-- トリガーで更新を記録する
CREATE OR REPLACE FUNCTION auditor() RETURNS trigger
AS $$
DECLARE
jb_data jsonb;
BEGIN
jb_data := CASE TG_OP
WHEN 'INSERT' THEN row_to_json(NEW)
WHEN 'UPDATE' THEN row_to_json(NEW)
WHEN 'DELETE' THEN row_to_json(OLD) -- NEWなし
ELSE NULL
END;
INSERT INTO audit_history VALUES(
current_timestamp, TG_OP, current_user, TG_TABLE_NAME, jb_data
);
RETURN NULL; -- AFTERトリガー用
END;
$$ LANGUAGE plpgsql;
監査関数はどのテーブルにも適用でき、 jsonb_populate_record() で元のテーブルスキーマに復元できる。
CREATE TRIGGER foo_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON foo
FOR EACH ROW EXECUTE PROCEDURE auditor();
CREATE TRIGGER bar_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON bar
FOR EACH ROW EXECUTE PROCEDURE auditor();
-- 監査履歴を参照するときに、テーブルのレコード型に戻す
SELECT a.stamp, a.event, f.*
FROM audit_history AS a, jsonb_populate_record(null::foo, a.data) AS f
ORDER BY stamp
これでは満足できない。
ここやはり、更新レコード全体ではなく、その都度の差分だけ保存できるようにしないとディスクがモッタイナイと考えるのが日本人の血統だ。
jsonb には hstore とは違って操作演算子・関数が不十分なので、差分を抽出する操作が複雑になる。
-- 汎用監査履歴テーブル
DROP TABLE IF EXISTS audit_history;
CREATE TABLE audit_history (
tstamp timestamptz,
txid bigint, -- トランザクションID
event text, -- INSERT, UPDATE, DELETE
app_name text, -- クライアント接続時に指定できる。
user_name text, -- ユーザ名
rspec jsonb, -- レコード特定情報 {"schema":"public", "table":"emp", "pkey":{"emp_id":1234}}
diff jsonb -- 変更差分
);
-- 更新レコードから差分カラムだけを監査ログに記録するトリガー
CREATE OR REPLACE FUNCTION auditor() RETURNS trigger
AS $auditor$
DECLARE
jb_data jsonb; -- 更新レコード
jb_diff jsonb; -- 差分データ
jb_rspec jsonb; -- レコード特定情報
app_name text;
BEGIN
-- AFTER 専用
-- 変更内容はBEFOREよりAFTERのほうが確実。
IF TG_WHEN <> 'AFTER' THEN
RAISE EXCEPTION 'Trigger %: Table %: Ignored when %. ', TG_NAME, TG_TABLE_NAME, TG_WHEN;
END IF;
CASE TG_OP
WHEN 'INSERT' THEN
jb_data := to_json(NEW)::jsonb;
jb_diff := jb_data;
WHEN 'UPDATE' THEN
jb_data := to_json(NEW)::jsonb;
-- 更新なら NEW - OLD の差分を取得し、JSON にまとめる
-- TODO 関数化
SELECT json_object_agg(key, value)::jsonb
FROM (
SELECT * FROM jsonb_each( to_json(NEW)::jsonb)
EXCEPT ALL
SELECT * FROM jsonb_each( to_json(OLD)::jsonb)
) AS kv
INTO jb_diff;
WHEN 'DELETE' THEN
jb_data := to_json(OLD)::jsonb;
jb_diff := jb_data;
ELSE
RAISE WARNING 'Trigger %: Ignored event %. ', TG_NAME, TG_OP;
RETURN NULL;
END CASE;
-- 差分データからはプライマリキーが失われてしまうので別に取っておく必要がある。
-- TODO 関数化
WITH pk (col) AS (
-- プライマリキーのカラム名を取得する。
SELECT cu.column_name
FROM pg_constraint AS c
INNER JOIN information_schema.constraint_column_usage AS cu
ON (c.contype, c.conname) = ('p', cu.constraint_name)
WHERE c.conrelid = TG_RELID
),
pkv (key, val) AS (
-- プライマリキーの値を取得する
SELECT pk.col, jb_data->pk.col FROM pk
),
rspec AS (
SELECT
TG_TABLE_SCHEMA AS "schema",
TG_TABLE_NAME AS "table",
json_object_agg(pkv.key, pkv.val) AS pkey
FROM pkv
)
SELECT to_json(rspec)::jsonb FROM rspec
INTO jb_rspec;
-- {"schema":"public", "table":"emp", "pkey":{"emp_id":1234}}
-- アプリケーション名を取得
SELECT setting FROM pg_settings WHERE name = 'application_name'
INTO app_name;
-- 差分レコードを追加
INSERT INTO audit_history VALUES(
current_timestamp, txid_current(), TG_OP, app_name, current_user, jb_rspec, jb_diff
);
RETURN NULL; -- AFTER
END;
$auditor$ LANGUAGE plpgsql;
トリガーにしては重そうすぎるな。
-- サンプルテーブルを定義し、監査トリガーを設定。
CREATE TABLE abc (
abc_id serial PRIMARY KEY,
aaa text, bbb text, ccc text
);
CREATE TRIGGER abc_audit
AFTER INSERT OR UPDATE OR DELETE ON abc
FOR EACH ROW EXECUTE PROCEDURE auditor();
CREATE TABLE xyz (
xyz_id serial PRIMARY KEY,
xxx int, yyy int, zzz int
);
CREATE TRIGGER xzy_audit
AFTER INSERT OR UPDATE OR DELETE ON xyz
FOR EACH ROW EXECUTE PROCEDURE auditor();
-- テーブルの更新
INSERT INTO abc(aaa, bbb, ccc) VALUES('boo', 'foo', 'woo');
INSERT INTO xyz(xxx, yyy, zzz) VALUES(1, 2, 3);
INSERT INTO abc(aaa, bbb, ccc) VALUES('AAA', 'BBB', 'CCC');
UPDATE abc SET aaa = 'BOO' WHERE abc_id = 1;
UPDATE abc SET (bbb, ccc) = ('FOO', NULL) WHERE abc_id = 1;
UPDATE abc SET bbb = 'FOO' WHERE abc_id = 1; -- No effect
DELETE FROM abc WHERE abc_id = 1;
UPDATE xyz SET zzz = 100 WHERE xyz_id = 1;
-- 更新履歴の差分のみが JSON で保存されている。
SELECT rspec->>'table' AS "table", diff
FROM audit_history
ORDER BY tstamp;
table | diff
-------+---------------------------------------------------------
abc | {"aaa": "boo", "bbb": "foo", "ccc": "woo", "abc_id": 1}
xyz | {"xxx": 1, "yyy": 2, "zzz": 3, "xyz_id": 1}
abc | {"aaa": "AAA", "bbb": "BBB", "ccc": "CCC", "abc_id": 2}
abc | {"aaa": "BOO"}
abc | {"bbb": "FOO", "ccc": null}
abc | {}
abc | {"aaa": "BOO", "bbb": "FOO", "ccc": null, "abc_id": 1}
xyz | {"zzz": 100}
(8 rows)
-- 差分から更新履歴を復元する。
-- 関数化したかったが断念。
WITH
df AS (
SELECT tstamp, event, diff FROM audit_history
WHERE rspec @> '{"table": "abc", "pkey":{"abc_id":1}}' -- テーブル名とキーを指定する。
),
jb AS (
SELECT DISTINCT
df.*,
json_object_agg(kv.key, kv.value) OVER (ORDER BY df.tstamp) ::jsonb AS rec
FROM df, jsonb_each(df.diff) AS kv
)
SELECT jb.tstamp, jb.event, r.*
FROM jb, jsonb_populate_record( NULL::abc, jb.rec) AS r -- テーブルの型を指定する
ORDER BY jb.tstamp
;
tstamp | event | abc_id | aaa | bbb | ccc
-------------------------------+--------+--------+-----+-----+-----
2014-09-21 11:58:28.729355+09 | INSERT | 1 | boo | foo | woo
2014-09-21 11:58:28.779535+09 | UPDATE | 1 | BOO | foo | woo
2014-09-21 11:58:28.799599+09 | UPDATE | 1 | BOO | FOO |
2014-09-21 11:58:28.829915+09 | DELETE | 1 | BOO | FOO |
(4 rows)
2.7. ポリシーカラム
状況によってカラムへのアクセスを制御したい場合がある。
たとえば、セキュリティ上ユーザ権限によって特定カラムの値を取得しないようにしたい。
もちろんアプリ側のプログラムロジックで制御すればいいのだが、json_populate_record() 系関数を使えば JSON でマスクすることもできる。
マスク用の JSON は実行時にパラメータで指定できるので、設定ファイル等で管理できる。
-- private
SELECT s.* FROM users, json_populate_record(users, '{}') AS s
WHERE users.id = 1;
id | nick_name | real_name | password | email
---+-----------+-----------+------------+-------------------
1 | Qiita | キー太 | 1234565678 | qiita@example.com
1 row)
-- protected
SELECT s.* FROM users, json_populate_record(users, '{"password":"****"}') AS s
WHERE users.id = 1;
id | nick_name | real_name | password | email
----+-----------+-----------+----------+-------------------
1 | Qiita | キー太 | **** | qiita@example.com
(1 row)
-- public
SELECT s.* FROM users, json_populate_record(users, '{"password":null, "real_name":null}') AS s
WHERE users.id = 1;
id | nick_name | real_name | password | email
----+-----------+-----------+----------+-------------------
1 | Qiita | | | qiita@example.com
(1 row)
逆に、テーブルの特定カラムの内容をダミーにしておいて、JSON に持たせたデータでカラムを上書きするという使い方も考えられる。
-- ポリシーで暗号化カラムを指定する
CREATE TABLE user_info (
id int PRIMARY KEY,
name text NOT NULL,
email text,
phone text,
address text,
cc_name text,
cc_number text,
:
:
secret bytea -- 任意の機密カラムをまとめてJSON化したうえで暗号化しておく。
);
-- 機密カラムにはダミーを入れておく。
SELECT *
FROM user_info WHERE id = 1111
;
id | name | ... | cc_name | cc_number | ... | secret
------+------+-----+---------+-----------+ ----+-------------------
1111 | hoge | ... |**** |**** | ... | \xe5a4aae9838e...
-- 暗号化されたJSONを復号し、カラムを上書きする。
SELECT cf.*
FROM user_info,
json_populate_record(user_info,
pgp_sym_decrypt(user_info.secret, 'key123')
) AS cf
WHERE user_info.id = 1111
;
id | name | ... | cc_name | cc_number | ...
------+------+-----+----------+------------------+---
1111 | hoge | ... | niconico | 1234567812345678 | ...
ただし、データの更新や管理が簡単にはいかない。
2.8. データ交換
3. SQL for JSON
次期リリース PostgreSQL 9.5 では jsonb の連結やフィールドの追加削除もできるようになるようだ。
- PostgreSQL: Documentation: 9.5: JSON Functions and Operators
http://www.postgresql.org/docs/9.5/static/functions-json.html
新機能を五月雨式に拡充するやり方がポスグレ流なのかは知らないが、これでようやく一通りの操作に十分な機能そろうことになる。
プログラマ的にはもはや、JSON が使える SQL というより、SQL が使える JSON と言っていい。
しかもナンチャッテではない本物の SQL が使える JSON プラットフォームだ。
その自由度は、ある種の技術的臨界を超えて新たなフレームを PostgreSQL 上に出現させる可能性をも秘める。
その辺の妄想については、9.5 のリリースを待って次回検証の機会に持ち越そう。
4. まとめ
なし
参考
各セクション内に追記した。