Rails
PostgreSQL


概要


  • GraphQL で柔軟なリクエストができるようになれば、サーバー側の実装をせずとも新しい機能を効率よく作れて便利みたいな話を見かけた

  • GraphQL では独自のクエリ言語でサーバーに対してデータを要求するが、クエリ言語と言えば SQL というものがあるので、どうせなら SQL をサーバーに投げつけるようにすれば、似たようなことが出来るのではないかと考えたのでやってみた


サンプルアプリケーション


ソースコード


仕様


ユーザー


  • ユーザーは key(Token) を持っていて、リクエストの時にヘッダーに Token をつける

  • privacy という属性を持つ。privacy が true のユーザーは他のユーザーからは不可視となる


コメント


  • ユーザーが投稿する事ができる

  • privacy という属性を持つ。privacy が true のコメントはコメントのオーナーユーザー以外からは不可視となる


いいね


  • ユーザーがコメントに付与することができる


テーブル定義


users

Column
Type

id
bigint

name
text

key
text

privacy
boolean

created_at
timestamp without time zone

updated_at
timestamp without time zone


comments

Column
Type

id
bigint

user_id
bigint

content
text

privacy
boolean

created_at
timestamp without time zone

updated_at
timestamp without time zone


likes

Column
Type

id
bigint

user_id
bigint

comment_id
bigint

created_at
timestamp without time zone

updated_at
timestamp without time zone


設計


Routes



  • users, comments, likesINSERT, UPDATE, DELETE は REST API を作る


  • SELECT は全て /haute_couture という URL に SQL を送信することとする

Prefix
Verb
URI Pattern
Controller#Action

comment_likes
POST
/comments/:comment_id/likes(.:format)
likes#create

comments
POST
/comments(.:format)
comments#create

comment
PATCH
/comments/:id(.:format)
comments#update

PUT
/comments/:id(.:format)
comments#update

DELETE
/comments/:id(.:format)
comments#destroy

like
DELETE
/likes/:id(.:format)
likes#destroy

users
POST
/users(.:format)
users#create

haute_couture
GET
/haute_couture(.:format)
haute_couture#show


サンプルレコード


users

id
name
key
privacy
created_at
updated_at

10
yancya
6f916ae6-8472-463a-9808-6af19e459541
f
2017-09-18 04:59:38.507022
2017-09-18 04:59:38

11
testuser
5a05a425-2bb3-4c2c-87a1-5c5513492bfb
f
2017-09-18 05:09:13.531304
2017-09-18 05:09:13

14
secretman
4f3326a4-b900-4624-af58-87e8f363dee6
t
2017-09-19 02:26:57.195464
2017-09-19 02:26:57


comments

id
user_id
content
privacy
created_at
updated_at

8
10
hoge
f
2017-09-18 05:00:11.05575
2017-09-18 05:00:11.05575

9
10
fuga
f
2017-09-18 05:00:16.522117
2017-09-18 05:00:16.522117

10
10
piyo
f
2017-09-18 05:00:21.613141
2017-09-18 05:00:21.613141

11
10
it is secret
t
2017-09-18 05:00:45.634707
2017-09-18 05:00:45.634707

12
11
it is secret
t
2017-09-18 05:09:52.393868
2017-09-18 05:09:52.393868

13
11
it is not secret
f
2017-09-18 05:10:05.927487
2017-09-18 05:10:05.927487


likes

id
user_id
comment_id
created_at
updated_at

8
10
11
2017-09-18 05:04:19.505836
2017-09-18 05:04:19.505836

9
10
10
2017-09-18 05:04:23.85745
2017-09-18 05:04:23.85745

10
10
9
2017-09-18 05:04:26.740116
2017-09-18 05:04:26.740116

11
10
8
2017-09-18 05:04:29.185687
2017-09-18 05:04:29.185687

12
10
9
2017-09-18 05:04:32.062099
2017-09-18 05:04:32.062099

13
10
10
2017-09-18 05:04:33.455075
2017-09-18 05:04:33.455075

14
10
8
2017-09-18 05:04:34.871388
2017-09-18 05:04:34.871388

15
10
10
2017-09-18 05:04:36.449477
2017-09-18 05:04:36.449477

16
10
12
2017-09-18 05:11:41.174168
2017-09-18 05:11:41.174168

17
10
12
2017-09-18 05:11:41.914313
2017-09-18 05:11:41.914313

18
10
12
2017-09-18 05:11:42.387759
2017-09-18 05:11:42.387759

19
10
12
2017-09-18 05:11:43.713585
2017-09-18 05:11:43.713585

20
10
12
2017-09-18 05:11:44.244417
2017-09-18 05:11:44.244417

21
10
13
2017-09-18 05:11:48.028928
2017-09-18 05:11:48.028928

22
10
13
2017-09-18 05:11:50.945295
2017-09-18 05:11:50.945295

23
10
13
2017-09-18 05:13:34.048887
2017-09-18 05:13:34.048887

24
10
13
2017-09-18 05:15:50.857694
2017-09-18 05:15:50.857694

25
11
9
2017-09-18 05:23:01.764913
2017-09-18 05:23:01.764913

26
11
10
2017-09-18 05:23:07.392301
2017-09-18 05:23:07.392301

27
11
11
2017-09-18 05:23:10.6083
2017-09-18 05:23:10.6083

28
11
12
2017-09-18 05:23:13.162275
2017-09-18 05:23:13.162275

29
11
13
2017-09-18 05:23:15.535289
2017-09-18 05:23:15.535289

30
14
8
2017-09-20 05:10:20.676045
2017-09-20 05:10:20.676045

31
14
9
2017-09-20 05:10:23.789931
2017-09-20 05:10:23.789931


/haute_couture へのアクセス例


users への SELECT


  • testuser によるリクエスト

curl -X GET \

-H 'Authorization: Token 6f916ae6-8472-463a-9808-6af19e459541' \
-d "haute_couture[query]=SELECT * FROM users" \
localhost:3000/haute_couture | jq

[

{
"id": 10,
"name": "yancya",
"created_at": "2017-09-18T04:59:38.507022",
"updated_at": "2017-09-18T04:59:38.507022"
},
{
"id": 11,
"name": "testuser",
"created_at": "2017-09-18T05:09:13.531304",
"updated_at": "2017-09-18T05:09:13.531304"
}
]


  • secretman によるリクエスト


    • secretman は privacy = true なユーザーだが、自分自身なので参照できる



curl -X GET \

-H 'Authorization: Token 4f3326a4-b900-4624-af58-87e8f363dee6' \
-d "haute_couture[query]=SELECT * FROM users" \
localhost:3000/haute_couture | jq

[

{
"id": 10,
"name": "yancya",
"created_at": "2017-09-18T04:59:38.507022",
"updated_at": "2017-09-18T04:59:38.507022"
},
{
"id": 11,
"name": "testuser",
"created_at": "2017-09-18T05:09:13.531304",
"updated_at": "2017-09-18T05:09:13.531304"
},
{
"id": 14,
"name": "secretman",
"created_at": "2017-09-19T02:26:57.195464",
"updated_at": "2017-09-19T02:26:57.195464"
}
]


テーブルを JOIN した SELECT


  • yancya によるアクセスなので secretman のデータは参照できていない


  • JSON_AGG 関数を使う事で、入れ子構造の結果を返すことができている


  • likes は全件参照できるが、likes が持っている user_id が指すユーザーの privacytrue である場合がある



    • LEFT OUTER JOINCOALESCE を使う事によって、privacy = true なユーザーを"匿名"という名前で取得している

    • 具体的には secretman の「いいね」が「匿名」という名前で出てきている



           WITH comments AS (

SELECT comments.id
, content
, users.name AS user_name
, comments.created_at
FROM comments
JOIN users ON comments.user_id = users.id
)

, likes AS (
SELECT COALESCE(users.name, '匿名') AS user_name
, likes.created_at
, comment_id
FROM likes
LEFT OUTER JOIN users ON likes.user_id = users.id
)

SELECT comments.id
, content
, comments.user_name
, JSON_AGG(likes ORDER BY likes.created_at DESC) AS liked_by
FROM comments
JOIN likes ON likes.comment_id = comments.id
GROUP BY 1, 2, 3

curl -X GET \

-H 'Authorization: Token 6f916ae6-8472-463a-9808-6af19e459541' \
-d "haute_couture[query]=WITH comments AS (SELECT comments.id, content, users.name AS user_name, comments.created_at FROM comments JOIN users ON comments.user_id = users.id), likes AS (SELECT COALESCE(users.name, '匿名') AS user_name, likes.created_at, comment_id FROM likes LEFT OUTER JOIN users ON likes.user_id = users.id) SELECT comments.id, content, comments.user_name, JSON_AGG(likes ORDER BY likes.created_at DESC) AS liked_by FROM comments JOIN likes ON likes.comment_id = comments.id GROUP BY 1, 2, 3" \
localhost:3000/haute_couture | jq

[

{
"id": 8,
"content": "hoge",
"user_name": "yancya",
"liked_by": [
{
"user_name": "匿名",
"created_at": "2017-09-20T05:10:20.676045",
"comment_id": 8
},
{
"user_name": "yancya",
"created_at": "2017-09-18T05:04:34.871388",
"comment_id": 8
},
{
"user_name": "yancya",
"created_at": "2017-09-18T05:04:29.185687",
"comment_id": 8
}
]
},
{
"id": 9,
"content": "fuga",
"user_name": "yancya",
"liked_by": [
{
"user_name": "匿名",
"created_at": "2017-09-20T05:10:23.789931",
"comment_id": 9
},
{
"user_name": "testuser",
"created_at": "2017-09-18T05:23:01.764913",
"comment_id": 9
},
{
"user_name": "yancya",
"created_at": "2017-09-18T05:04:32.062099",
"comment_id": 9
},
{
"user_name": "yancya",
"created_at": "2017-09-18T05:04:26.740116",
"comment_id": 9
}
]
},
{
"id": 10,
"content": "piyo",
"user_name": "yancya",
"liked_by": [
{
"user_name": "testuser",
"created_at": "2017-09-18T05:23:07.392301",
"comment_id": 10
},
{
"user_name": "yancya",
"created_at": "2017-09-18T05:04:36.449477",
"comment_id": 10
},
{
"user_name": "yancya",
"created_at": "2017-09-18T05:04:33.455075",
"comment_id": 10
},
{
"user_name": "yancya",
"created_at": "2017-09-18T05:04:23.85745",
"comment_id": 10
}
]
},
{
"id": 11,
"content": "it is secret",
"user_name": "yancya",
"liked_by": [
{
"user_name": "testuser",
"created_at": "2017-09-18T05:23:10.6083",
"comment_id": 11
},
{
"user_name": "yancya",
"created_at": "2017-09-18T05:04:19.505836",
"comment_id": 11
}
]
},
{
"id": 13,
"content": "it is not secret",
"user_name": "testuser",
"liked_by": [
{
"user_name": "testuser",
"created_at": "2017-09-18T05:23:15.535289",
"comment_id": 13
},
{
"user_name": "yancya",
"created_at": "2017-09-18T05:15:50.857694",
"comment_id": 13
},
{
"user_name": "yancya",
"created_at": "2017-09-18T05:13:34.048887",
"comment_id": 13
},
{
"user_name": "yancya",
"created_at": "2017-09-18T05:11:50.945295",
"comment_id": 13
},
{
"user_name": "yancya",
"created_at": "2017-09-18T05:11:48.028928",
"comment_id": 13
}
]
}
]


実装の要点


共通表式(CTE) WITH


  • WITH はクエリー毎のワンタイムの VIEW を作るような仕組み

  • 既存のテーブルと同名のリレーションを作成することができる


HauteCouture


  • user がアクセス出来るリレーションで users や comments を上書きしている

module HauteCouture

def self.find_by_sql(query:, user:)
ActiveRecord::Base.connection.execute(<<~SQL).first['result'] || '[]'
WITH users AS (
#{user.for_haute_couture_sql})
, comments AS (
#{Comment.for_haute_couture(user).to_sql})
, t AS (
#{query})
SELECT JSON_AGG(t) AS result FROM t
SQL
end
end


User#for_haute_couture_sql



  • usersid, name, created_at, updated_at にだけアクセス出来るようにしている



    • key, privacy が見えないようにしている




  • privacy = false な行だけ(自分を除く)にアクセス出来るようにしている

def for_haute_couture_sql

base = User.select(:id, :name, :created_at, :updated_at)
base.where(id: id).or(base.where(privacy: false)).to_sql
end


Comment scope for_haute_couture



  • commentsid, content, user_id, created_at, updated_at にだけアクセス出来るようにしている



    • privacy が見えないようにしている




  • privacy = false な行だけ(自分を除く)にアクセス出来るようにしている

  • scope なので AR::Relation が返ってくる(なので呼び出し元で to_sql してる)

scope :for_haute_couture, -> (user) {

base = Comment.select(:id, :content, :user_id, :created_at, :updated_at)
base.where(privacy: false).or(base.where(user: user))
}


まだやってないこと


Mutations を殺す


  • INSERT, UPDATE, DELETE, CREATE, TRUNCATE etc...

  • DB の状態を変えてしまう操作は一切してほしくないので、なんとかして殺す


スキーマ修飾を殺す



  • WITH users AS (SELECT * FROM users WHERE false) SELECT * FROM users


    • これなら結果が0件になるが




  • WITH users AS (SELECT * FROM users WHERE false) SELECT * FROM public.users


    • こうすると id, name, key, privacy, created_at, updated_at が全件見えてしまう

    • スキーマ名で修飾すると、大元のテーブルにアクセス出来てしまうので、スキーマ修飾も殺さないといけない




無限ループを殺す


  • 再帰クエリを使うと無限ループを表現できてしまい、殺意のあるリクエストを送り込まれる危険がある

  • 再帰クエリが使える方が嬉しいので、無限ループだけを殺したいけど、方法が思いつかない


    • そんなことが出来るくらいなら、最初から RDBMS に無限ループをガードする仕組みが入っているはずだし




無限ループ SQL の例

     WITH RECURSIVE r AS (

SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM r)
seleCT * FROM r;


流すとこうなる

psql(34605,0x7fff9c0ef3c0) malloc: *** mach_vm_map(size=18446744056529682432) failed (error code=3)

*** error: can't allocate region
*** set a breakpoint in malloc_error_break to debug


System Catalogs Table へのアクセスを殺す


  • PostgreSQL の場合、pg_ ではじまるテーブルが System Catalogs になっている

  • めっちゃ沢山あるけど、あんまり普通の人にアクセスして欲しくないテーブルもあるっぽいので、ひとまず全部ブラックリストに入れたい

pg_aggregate                     pg_locks                         pg_stat_user_functions

pg_am pg_matviews pg_stat_user_indexes
pg_amop pg_namespace pg_stat_user_tables
pg_amproc pg_opclass pg_stat_wal_receiver
pg_attrdef pg_operator pg_stat_xact_all_tables
pg_attribute pg_opfamily pg_stat_xact_sys_tables
pg_auth_members pg_pltemplate pg_stat_xact_user_functions
pg_authid pg_policies pg_stat_xact_user_tables
pg_available_extension_versions pg_policy pg_statio_all_indexes
pg_available_extensions pg_prepared_statements pg_statio_all_sequences
pg_cast pg_prepared_xacts pg_statio_all_tables
pg_catalog. pg_proc pg_statio_sys_indexes
pg_class pg_range pg_statio_sys_sequences
pg_collation pg_replication_origin pg_statio_sys_tables
pg_config pg_replication_origin_status pg_statio_user_indexes
pg_constraint pg_replication_slots pg_statio_user_sequences
pg_conversion pg_rewrite pg_statio_user_tables
pg_cursors pg_roles pg_statistic
pg_database pg_rules pg_stats
pg_db_role_setting pg_seclabel pg_tables
pg_default_acl pg_seclabels pg_tablespace
pg_depend pg_settings pg_temp_1.
pg_description pg_shadow pg_timezone_abbrevs
pg_enum pg_shdepend pg_timezone_names
pg_event_trigger pg_shdescription pg_toast.
pg_extension pg_shseclabel pg_toast_temp_1.
pg_file_settings pg_stat_activity pg_transform
pg_foreign_data_wrapper pg_stat_all_indexes pg_trigger
pg_foreign_server pg_stat_all_tables pg_ts_config
pg_foreign_table pg_stat_archiver pg_ts_config_map
pg_group pg_stat_bgwriter pg_ts_dict
pg_index pg_stat_database pg_ts_parser
pg_indexes pg_stat_database_conflicts pg_ts_template
pg_inherits pg_stat_progress_vacuum pg_type
pg_init_privs pg_stat_replication pg_user
pg_language pg_stat_ssl pg_user_mapping
pg_largeobject pg_stat_sys_indexes pg_user_mappings
pg_largeobject_metadata pg_stat_sys_tables pg_views


速度


  • ベンチマークを取っていないので、普通に作った場合と比べて遅かったり速かったりするかどうか、まだわからない


感想


  • もし敵を全部殺せれば、SQL の表現力を生かしたメリットのある API になるんじゃないかとは思った