NullPointer's

SQL アンチパターンをパラパラめくっていて、ナイーブツリーの項で

SQL-99標準では、WITHキーワードの後に共通テーブル式（CTE：common table expression）を指定する形式の再帰クエリ構文を定義しています。

という一文を目にし、ふとMySQL 8からCTEが使えるようになった事を思い出したので今更試してみた。

データを入れる

create table family (id, parent_id, name) ;

insert into family (id, parent_id, name) values 
(1, NULL, '家康'),
(2, 1, '秀忠'),
(3, 1, '義直'),
(4, 1, '頼信'),
(5, 1, '頼房'),
(6, 2, '家光'),
(7, 6, '家綱'),
(8, 6, '綱重'),
(9, 6, '綱吉'),
(10, 8, '家宣'),
(11, 4, '光貞'),
(12, 11, '吉宗');

秀忠の子孫を取る

with recursive family_tree (id, parent_id, name, depth) as (
  select *, 0 as depth from family where id = 2
  union all
  select f.*, ft.depth + 1 as depth from family_tree ft inner join family f on ft.id = f.parent_id
)
select * from family_tree;

+------+-----------+--------+-------+
| id   | parent_id | name   | depth |
+------+-----------+--------+-------+
|    2 |         1 | 秀忠   |     0 |
|    6 |         2 | 家光   |     1 |
|    7 |         6 | 家綱   |     2 |
|    8 |         6 | 綱重   |     2 |
|    9 |         6 | 綱吉   |     2 |
|   10 |         8 | 家宣   |     3 |
+------+-----------+--------+-------+
6 rows in set (0.03 sec)

吉宗のご先祖様を取る

with recursive family_tree (id, parent_id, name, depth) as (
  select *, 0 as depth from family where id = 12
  union all
  select f.*, ft.depth - 1 as depth from family_tree ft  inner join family f on ft.parent_id = f.id
)
select * from family_tree;

+------+-----------+--------+-------+
| id   | parent_id | name   | depth |
+------+-----------+--------+-------+
|   12 |        11 | 吉宗   |     0 |
|   11 |         4 | 光貞   |    -1 |
|    4 |         1 | 頼信   |    -2 |
|    1 |      NULL | 家康   |    -3 |
+------+-----------+--------+-------+
4 rows in set (0.00 sec)

とりあえずexplainしてみる

mysql> explain with recursive family_tree (id, parent_id, name, depth) as (select *, 0 as depth from family where id = 2   union all     select f.*, ft.depth + 1 as depth from family_tree ft inner join family f on ft.id = f.parent_id ) select * from family_tree;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL                                               |
|  2 | DERIVED     | family     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    10.00 | Using where                                        |
|  3 | UNION       | ft         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Recursive                                          |
|  3 | UNION       | f          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

あっ、Using join buffer (Block Nested Loop)、parent_idにindex付けないと

ディレクトリを漁っていたら、10年ぐらい前に書いたHTML CanvasとJavaScriptによるバイリニア法の画像拡大のコードが出てきた。これが酷いコードだったので最近の文法も使って書き直し、ついでなのでバイキュービック法とランチョス法による拡大縮小も書いてみたり。

バイキュービック法とランチョス法ではバイリニア法よりシャープな感じに仕上がる。

最初にこのバイリニア法のコードを書いた頃、Firefoxのバージョンは3、Chromeはまだ存在しなかった（Windows版はあったらしい）。当時のブラウザとマシンでは180 x 180の画像を縦横2倍に拡大するだけで10秒程度かかっていたのが今や一瞬で完了。CPUとJSエンジンの進化、凄まじい。

ところで先日、こんな記事が話題になっていた。

さようなら ImageMagick - Cybozu Inside Out | サイボウズエンジニアのブログ

ImageMagickのセキュリティ問題多いのでサムネイル作成処理をGoで書き直したという話だが、今どきのJSエンジンとCPUパワーならば、サムネイルの作成程度ならばフロントでも処理できてしまう。canvasをpng画像として取り出す方法もあるので、いっそフロントでサムネイルを作ってサーバに送信し、バックエンド側はバリデーションだけってのもアリかもしれないっすよ？

自分でバイナリ弄るコードを書かなくてもcanvasのscaleメソッドを呼ぶだけでも縮小処理は可能だし、ガチで最適化かけた超速いサムネイル作成ライブラリとか探せばあるかもしれないっすよー。知らんけど。

NullPointer's

MySQLのCTE

HTML Canvasでバイリニア法、バイキュービック法、ランチョス法