#78 RDBMSのJSON型 & アプリ内課金の話

どうも、esuiです。
先日から社内勉強会の配信をappear.inなどで試行錯誤しているのですが、
今日はGoogleHangoutを試してみました。
appear.inだと人数制限が厳しいですが、GoogleHangoutの方が余裕がありそうかな、と・・・




RDBMSのJSON型



1本目はDBにJSONを入れたいということで、
新しい機能、新しいデータなどを作成したい時に、テーブルをカッチリ作っていると場所が無い
都度カラム追加するのは大変だし、連番カラムのようなものはもっと危険な香りがしますね、と。

シーサーブログの場合、
- blog_property テーブルの textarea カラム
- 様々なテーブルの propertydata カラム
などが用意してあり、
site_view_partsというテーブルでは、専用のサブクラスやそれ用のテーブルを作る替わりにJSONを格納するような仕組みとなっています。

Perl擬似カラムユーティリティがあり、JSONオブジェクトの中身に普通のカラムと同じようにアクセスできるにしています。

SELECT title, propertydata FROM site_view_parts WHERE id = 547;
-- +--------------+---------------------------------------+
-- | 自由形式 | {"free_text":"自由なテキスト"} |
-- +--------------+---------------------------------------+


my $site_view_parts = Bl::Data::SiteViewParts->retrieve(547);
$site_view_parts->title; # => '自由形式'
$site_view_parts->free_text; # => '自由なテキスト'


何故JSONなのか


- DBでバイナリデータは面倒
- 可変長となるとテキストがよい
- 独自テキストフォーマットは論外
- XMLは面倒
- Data::Dumper出力は language-independent でない(perlに依存してしまう)

JSONに対応するRDBMS


# PostgreSQL
http://www.postgresql.jp/document/current/html/release-9-2.html
2012–09–10のリリース9.2 からJSONデータ型が使用可能となっています。

# MySQL
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-8.html
MySQL 5.7.8 (2015–08–03, Release Candidate)からJSON型がサポートされています。

今すぐ使えるJSON機能


JSON型カラムをいきなり導入しなくてもJSON関連の関数は使えるようになっています。

- 何も使わない場合
手作業でDBを調査する作業に追い込まれる

直にSQLを打つ

テキスト型カラムにJSONが入ってる

目視パース
改行がない、キーがソートされてない
JSON内のフラグをwhere句で指定できない

つらい

# SQLでJSONの中身を調べる
- select
-- In MySQL 5.7.9 and later
SELECT id, subject,
propertydatalight->'$.accept_comment' AS ac_json,
propertydatalight->>'$.accept_comment' AS ac_text
FROM article_shard4;

2017-03-03 14.27.00.jpg

- where
SELECT id, subject
FROM article_shard4
WHERE propertydatalight->'$.accept_comment' in(0, '0');
SELECT id, subject
FROM article_shard4
WHERE propertydatalight->>'$.accept_comment' = '0';


- perlからも使用可能
my @article_shards =
Bl::Data::Slave::ArticleShard4->retrieve_from_sql(
q( propertydatalight->>'$.accept_comment' = ? ), 0);


- 深い階層のデータをselectしたい
SELECT
j->'$.aaa',
j->'$.bbb',
j->'$.ccc[2]',
j->'$.ccc[3].eee',
j->'$.ccc[3].ggg'
FROM (SELECT cast('
{"aaa":111,
"ccc":[222, 333, 444, {"ddd":555,
"eee":"fff"}]}' AS json) AS j) AS t;

2017-03-03 14.30.20.jpg

# PostgreSQLの場合
SELECT
j->'aaa',
j->'bbb',
j#>'{ccc,2}',
j#>'{ccc,3,eee}',
j#>'{ccc,3.ggg}'
FROM (SELECT '
{"aaa":111,
"ccc":[222, 333, 444, {"ddd":555,
"eee":"fff"}]}'::json AS j) AS t;
SELECT '["zero","one","two"]'::json->2;


SQLでJSONを組み立てる


JSONではないカラムのデータをSQLだけでJSONにまとめることができます。
# PostgreSQL
CREATE TABLE article (
id serial PRIMARY KEY,
blog_id integer NOT NULL DEFAULT 0,
subject text NOT NULL DEFAULT '',
body text NOT NULL DEFAULT '',
property jsonb NOT NULL DEFAULT '{}'::jsonb);

\d article
INSERT INTO article(blog_id, subject, body, property) VALUES
(1, 'sub_1_a', 'bod_1_a', '{"accept_tb" : 0}'),
(1, 'sub_1_b', 'bod_1_b', '{"accept_tb" : 1}'),
(2, 'sub_2_a', 'bod_2_a', '{"accept_tb" : 1}'),
(2, 'sub_2_b', 'bod_2_b', '{"accept_tb" : 0}'),
(2, 'sub_2_c', 'bod_2_c', '{"accept_tb" : 1}');

SELECT *
FROM article;
SELECT jsonb_build_object('blog_id', blog_id, 'subject', subject)
FROM article;
SELECT jsonb_agg(
jsonb_build_object('blog_id', blog_id, 'subject', subject))
FROM article;
SELECT jsonb_object_agg(
id, jsonb_build_object('blog_id', blog_id, 'subject', subject))
FROM article;


- jsonb_prettyで表示を見やすく
SELECT jsonb_pretty(
jsonb_object_agg(
id, jsonb_build_object('blog_id', blog_id, 'subject', subject)))
FROM article
WHERE property->>'accept_tb' = '1';

2017-03-03 14.35.42.jpg

- group by で集約する例
SELECT jsonb_pretty(jsonb_build_object(
'count_blog', count(*),
'count_article', sum(article_count),
'data', jsonb_agg(jsonb_build_object(
'blog_id', blog_id,
'count_article', article_count,
'summary', summary))))
FROM (SELECT blog_id,
count(*) AS article_count,
jsonb_agg(jsonb_build_object(
'subject', subject,
'accept_tb', property->'accept_tb')) AS summary
FROM article
GROUP BY blog_id) AS t;


- JSONの内容を更新する
SELECT
jsonb_pretty( j ) AS original,
jsonb_pretty(jsonb_concat(j, '{"bbb":999}'::jsonb)) AS concat,
jsonb_pretty(jsonb_delete(j, 'ccc' )) AS delete,
jsonb_pretty(jsonb_set( j, '{ccc,3}', '5' )) AS set
FROM (SELECT '
{"aaa":111,
"ccc":[222, 333, 444, {"ddd":555,
"eee":"fff"}]}'::jsonb AS j) AS t;
-- 画面の幅が足りないなら「\x on」


SQL直打ちでサクっと更新できるので、便利ですね・・。

# MySQL のJSON集約関数は 8.0 (5.7の次)からとなっているようです。
https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html
JSON_ARRAYAGG()   Return result set as a single JSON array
JSON_OBJECTAGG()  Return result set as a single JSON object

jsonb


実は「json」型ではない
http://www.postgresql.jp/document/9.6/html/datatype-json.html

jsonデータ型
入力テキストのコピーで格納
処理関数を実行するたび再解析

jsonbデータ型
分解されたバイナリ形式で格納
処理するときは再解析がないので高速

> jsonb型の重要な利点はインデックスをサポートしていることです

CREATE TABLE json_index_sample (
jsonb_with_index jsonb NOT NULL DEFAULT '{}',
jsonb_without_index jsonb NOT NULL DEFAULT '{}',
json_plain json NOT NULL DEFAULT '{}');
CREATE INDEX jis_idx
ON json_index_sample
USING GIN (jsonb_with_index);
---
EXPLAIN ANALYZE
SELECT json_plain
FROM json_index_sample
WHERE json_plain->>'author' = 'abc123';
EXPLAIN ANALYZE
SELECT json_plain
FROM json_index_sample
WHERE jsonb_without_index @> '{"author": "abc123"}';
EXPLAIN ANALYZE
SELECT json_plain
FROM json_index_sample
WHERE jsonb_with_index @> '{"author": "abc123"}';
---
Seq Scan on json_index_sample
Filter: ((json_plain ->> 'author'::text) = 'abc123'::text)

Execution time: 369.145 ms
Seq Scan on json_index_sample
Filter: (jsonb_without_index @> '{"author": "abc123"}'::jsonb)

Execution time: 115.215 ms
Bitmap Heap Scan on json_index_sample
-> Bitmap Index Scan on jis_idx
Index Cond: (jsonb_with_index @> '{"author": "abc123"}'::jsonb)

Execution time: 0.193 ms

-- レコードが無い場合
EXPLAIN ANALYZE ... WHERE jsonb_without_index @> '{"key": "val"}'
Seq Scan
Execution time: 104.597 ms
 
EXPLAIN ANALYZE ... WHERE jsonb_with_index @> '{"key": "val"}'
Bitmap Heap Scan
Execution time: 0.073 ms

jsonbではインデックスが使えるので圧倒的に早いですね。
アプリ側で適当に追加したKeyがDBの操作なしにすぐにインデックスとして使えるのは魅力的です。
が、B-tree でないので件数が多い中から ORDER BY … LIMIT …する際には効かないのに注意、とのこと。

ORM と jsonb


参考: http://qiita.com/kumazo/items/19361acffddb6be10ac2
オペレーター(「->>」など)の対応が面倒らしいとのことです。

# Rubyの例
docker pull ruby
docker run -it --net=host ruby:latest bash
gem install activerecord
gem install pg


require 'active_record'
require 'pp'

ActiveRecord::Base.establish_connection(
adapter: "postgresql", host: "localhost",
username: "postgres", database: "my_db"
)
class Article < ActiveRecord::Base
self.table_name = 'article'
end

articles = Article.where("property->>'accept_tb' = ?", '1')
pp articles
pp articles[1].property['accept_tb']

article = articles[1]
article.property['from_ruby'] = [111, 222]
article.save


# DB側でJSONを組み立てるViewを作りActiveRecordで取得
CREATE TABLE blog (
id serial PRIMARY KEY,
name text NOT NULL,
description text NOT NULL DEFAULT '');
INSERT INTO blog(name) VALUES
('blog_one'),
('SecondBlog');
--
CREATE VIEW recent_subjects AS
SELECT b.id AS blog_id, -- id だと ActiveRecord 連携がうまくいかない
b.name AS blog_name,
COALESCE(
(SELECT jsonb_agg(subject)
FROM (SELECT subject
FROM article as a
WHERE a.blog_id = b.id
ORDER by a.id desc
LIMIT 10) AS t), -- 0件だとNULL
'[]'::jsonb
) AS subjects
FROM blog AS b;
--
\d recent_subjects
--
-- View "public.recent_subjects"
--
-- Column | Type | Modifiers
-- -----------+---------+-----------
-- blog_id | integer |
-- blog_name | text |
-- subjects | jsonb |

SELECT * FROM recent_subjects WHERE blog_id = 1;


require 'active_record'
require 'pp'
ActiveRecord::Base.establish_connection(adapter: "postgresql", host: "localhost", username: "postgres", database: "my_db")

class RecentSubject < ActiveRecord::Base
end

rss = RecentSubject.all()
rs = RecentSubject.find_by(blog_id: 2)
pp rss
pp rs

print "ブログ“#{rs.blog_name}”の最近の投稿は、" +
rs.subjects.map{|s| "「#{s}」"}.join('、') +
"の#{rs.subjects.size}本です。" + $/

[ #,
# ]

#

ブログ“SecondBlog”の最近の投稿は、「sub_2_c」、「sub_2_b」、「sub_2_a」の3本です。


# Perlの例
use DBD::Pg;

package Article {
use base qw(Class::DBI::Pg);
__PACKAGE__->set_db(Main => 'dbi:Pg:database=my_db;host=127.0.0.1;port=5432',
'postgres', '');
__PACKAGE__->set_up_table('article');
}

sub main {
my @articles = Article->retrieve_from_sql("property->>'accept_tb' = ?", 0);
print Dumper($articles[0]->subject,
$articles[1]->property);
print Dumper([@articles]);
}
~~
$VAR1 = 'sub_1_a';
$VAR2 = '{"accept_tb": 0}';
$VAR1 = [ bless( { '__triggers' => {}, '_class_trigger_results' => [],
'id' => 12,
'blog_id' => 1,
'subject' => 'sub_1_a',
'body' => 'bod_1_a',
'property' => '{"accept_tb": 0}' }, 'Article' ),
bless( { '__triggers' => {}, '_class_trigger_results' => [],
'id' => 15,
'blog_id' => 2,
'subject' => 'sub_2_b',
'body' => 'bod_2_b',
'property' => '{"accept_tb": 0}' }, 'Article' ) ];


# さらにPerlの例
package RecentSubject {
use base qw(Class::DBI::Pg);
__PACKAGE__->set_db(...);

__PACKAGE__->set_up_table('recent_subjects', {Primary => ['blog_id']});
# 要 0.09 ( cpan install で入るのは 0.08 )
}

sub main {
my @recent_subjects = RecentSubject->retrieve_all();
print Dumper([@recent_subjects]);
}
--
$VAR1 = [ bless( {
'blog_id' => 1,
'blog_name' => 'blog_one',
'subjects' => '["sub_1_b", "sub_1_a"]',
... }, 'RecentSubject' ),
bless( {
'blog_id' => 2,
'blog_name' => 'SecondBlog',
'subjects' => '["sub_2_c", "sub_2_b", "sub_2_a"]',
... }, 'RecentSubject' ) ];


文字列として扱えればどうとでもなります、とのことでした。
その他、まだまだ掘り下げられており、かなりの力作でしたが、密度が濃くて紹介しきれませんでした…。(っていうかこれはやはり本人に書いてもらえればと…w)




アプリ内課金(主に定期購入)の話



ということでモバイルアプリのマネタイズ、アプリの課金まわりのお話でした。

モバイルアプリのマネタイズ


モバイルアプリのマネタイズには、大きく分けて以下の3つに分かれています。

■有料アプリ
- (ユーザ側)購入までのハードル高
- (開発者側)販売後の収益低、結局無料版も…

■無料アプリ+広告
- (ユーザ側)広告がうざい、成果達成が面倒になってくる
- (開発者側)インストール数、成果地点達成ユーザが少なければ収益困難

■無料アプリ+アプリ内課金
- (ユーザ側)無料試用してから課金を検討
- (開発者側)課金率の高いユーザを確保すれば継続的に収益可能

また、有料アプリ+アプリ内課金といったパターンもあります。

課金モデル


■消費型:複数回購入可能
- バーチャル通貨、追加アイテム
某魔法石や某ポケコインなどにあたるものですね。

■非消費型:一度だけ購入
- 無制限アイテム、ステージ追加、広告解除
マリオランのように買い切りのもの。

■定期購入型:一定期間ごとに自動購入
- 電子書籍定期購読、月額制プレミアム機能
サブスクリプション型のものですね。

今回はこの定期購入型アプリ課金について掘り下げられていました。

iOSの定期購入型アプリ内課金


■課金プラン
- 週単位・月単位(1/2/3/6ヶ月)・年単位などがあります。

■返金対応
- 即時の返金手段なし(「問題を報告する」から個別に可能かも)

■App Store経由以外の決済は不可能(審査リジェクト対象)

■リストア(復元)機能必須(未実装の場合審査リジェクト対象)

Androidの定期購入型アプリ内課金


■課金プラン
- 週単位・月単位(1/3/6ヶ月)・年単位・シーズン単位(毎年任意の一定期間)

■返金対応
- 無条件(2時間以内)→Google対応(48時間以内)→開発者対応(48時間以降)

■Play Store以外の外部決済も利用可能(手数料30%を抑えられるかも)

Androidの方がだいぶゆるい感じでしょうか。

アプリ内課金の流れ


実際のアプリ内課金フローについて、詳細な説明をされていました。

決済結果を検証するレシート検証用のAPIがあり、
スクリーンショット 2017-03-20 02.01.09.png

本物かどうか、期限は切れてないかどうかなどの情報をストア側と突き合わせを行い、
スクリーンショット 2017-03-20 02.03.26.png
iOSであれば検証結果の取引ID(original_transaction_id, transaction_id)の確認、
Androidであればデジタル署名の検証、developerPayloadにユーザ識別情報を設定するなどで妥当性の検証を行うようになっているそうです。

自動更新について


定期購読の更新は有効期限の前日などに、サーバ側から最新のレシートをStoreに問い合わせをして購読情報を更新し、
スクリーンショット 2017-03-20 02.11.43.png
アプリからはサーバに対して課金情報を問い合わせすることによって自動更新成功となるようです。
スクリーンショット 2017-03-20 02.11.58.png

また、最近では仲介サーバを置かずにアプリ自身でストア側と直接レシート検証(課金状態問い合わせ)を行うことも可能となっているそうです。

ということで、
アプリ内課金のモデルはいろいろありますが、
定期購読型は若干複雑であるものの、うまく固定ユーザを掴めれば安定するので注目ですね、との事でまとめとされていました。

この記事へのコメント