Go to Qiita Advent Calendar 2024 Top
LoginSignup
75
36

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@yancya

SQLQL

Last updated at Posted at 2017-09-20

概要

  • 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, likes の INSERT, 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 が指すユーザーの privacy が true である場合がある
    • LEFT OUTER JOIN と COALESCE を使う事によって、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

  • users の id, 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

  • comments の id, 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
    • これなら結果が０件になるが
  • 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 になるんじゃないかとは思った
75
36
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
75
36

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?