SQLQL

## 概要

- GraphQL で柔軟なリクエストができるようになれば、サーバー側の実装をせずとも新しい機能を効率よく作れて便利みたいな話を見かけた
- GraphQL では独自のクエリ言語でサーバーに対してデータを要求するが、クエリ言語と言えば SQL というものがあるので、どうせなら SQL をサーバーに投げつけるようにすれば、似たようなことが出来るのではないかと考えたのでやってみた

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

### ソースコード

- [sqlql_sample](https://github.com/yancya/sqlql_sample)

### 仕様

#### ユーザー

- ユーザーは 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 によるリクエスト

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

```json
[
  {
    "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` なユーザーだが、自分自身なので参照できる

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

```json
[
  {
    "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 の「いいね」が「匿名」という名前で出てきている

```sql
           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
```

```sh
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
```

```json
[
  {
    "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 を上書きしている

```rb
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` な行だけ（自分を除く）にアクセス出来るようにしている

```rb
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 してる）

```rb
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 の例

```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 になるんじゃないかとは思った