とみたさんの記事を見てUDFにトライ

とみたさんの UDF を作ろう記事を、そのまま試してみました。

tmtms.hatenablog.com

ほとんど、書かれているとおりにソースコードをコピーして gcc かけて プラグイン動作させれば良いです。が、少しだけ悩んだ点があるので、そこだけ補足的メモ。

gcc がなかった！

　結構、素のままの ubuntu 18.04 だったので、gcc すら入っていませんでした。入れた。

$ sudo apt-get install gcc

include フォルダがなかった！

　私の環境では、mysql の include フォルダが見当たらず。いろいろ操作した結果入ったので、どれが効いていたのか確証ないのだけど、たぶん、これ。

$ sudo apt-get install libmysqld-dev

　includeフォルダの位置も、とみたさんの記事とは異なっていたので、以下のように捜索。

$ find / -type d -name mysql
/usr/include/mysql
/usr/include/mysql/mysql
:

プラグインフォルダの確認方法

　念のため、MySQL の プラグインフォルダの確認も。

mysql> show variables like '%plugin%';
+-------------------------------+------------------------+
| Variable_name                 | Value                  |
+-------------------------------+------------------------+
| default_authentication_plugin | caching_sha2_password  |
| plugin_dir                    | /usr/lib/mysql/plugin/ |
+-------------------------------+------------------------+

環境が揃っていれば

　やることは、これだけ。

   $  gcc -shared -I /usr/include/mysql/ fib.c -o fib.so
   $  sudo cp fib.so /usr/lib/mysql/plugin/

mysql> create function fib returns integer soname 'fib.so';
mysql> select fib(10);
mysql> drop function fib;

メモ：MySQLのサーバサイドでのプリペアド・ステートメント

通常は各プログラム言語側で プレースホルダ含みのSQLを書いたりするけど、
サーバ側での（というかSQLレベルでの）プリペアド・ステートメントの書き方に触れる機会があったので、メモとして記す。

テーブルの用意

　とりあえず、id と name を格納するテーブルを作ってみる。

mysql> CREATE TABLE presample (id integer, name varchar(10));                                                                   

プリペアドステートメント

　PREPARE 文にて、SQL文を宣言しておく。FROMっていうのが、なんだかムズムズするけど、こういう構文。

mysql> PREPARE ins_stmt FROM "INSERT INTO presample VALUES (? , ?)";
Query OK, 0 rows affected (0.02 sec)
Statement prepared

　
　実行には EXECUTE を使う。値は USING で与える。

mysql> SET @a=1; SET @b="SAMPLE";

mysql> EXECUTE ins_stmt USING @a, @b;
Query OK, 1 row affected (0.00 sec)

　もう一件、入れておく。特に意味はないけど1件では寂しいので。

mysql> SET @a=2; SET @b="TEST";

mysql> EXECUTE ins_stmt USING @a, @b;
Query OK, 1 row affected (0.00 sec)

　中身を見れば、当然、入っている。

mysql> SELECT * FROM presample;
+------+--------+
| id   | name   |
+------+--------+
|    1 | SAMPLE |
|    2 | TEST   |
+------+--------+

　ちなみに、今私がわかっている範囲では、即値を与えることはできない模様。　

mysql> EXECUTE ins_stmt USING (1, "SAMPLE");
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1, "SAMPLE")' at line 1

　プリペアド・ステートメントは、メモリ上にSQL文（の内容）を保持しておく物だから、もう使わないのならば解放しておくよろし。

mysql> DEALLOCATE PREPARE ins_stmt;
Query OK, 0 rows affected (0.00 sec)

　
　このステートメントを直接使うシーンというのが、私には余り思い浮かばないのだけど、ストアド・プロシージャを書くときなどに便利になるのかなぁ。

baserCMS 4.3.3を新規サーバに入れるメモ(MySQL8.0使用)

baserCMS を初めてインストールしたのですが、細かいところで色々ハマったので、記録として書いておきます。
basercms.net

環境

　さくらインターネットで借りているVPS に、Ubuntu 18.04 amd64 をインストール。
　ドメイン名は別途持っているものがあったので、予めこのサーバにDNS設定で向けておく（ここでは svr1.example.com とする）。
　準備が出来たら、ubuntuユーザでログインして、いざ Go!

基本設定

　rootユーザで作業せずに コマンドごとに sudo で実行するのが流儀っぽいけど、ここではroot作業が続くので
sudo su - しちゃう。　よい子のみなさんは、上品な行動を心がけてくださいね。

ファイアウォール

# ufw status
Status: inactive

　で確認して、inactiveだったので、有効化して、22,80,443番を開けておく。

# ufw enable 
# ufw allow 22
# ufw allow 80
# ufw allow 443

　結果、良い感じ。22が開いているのでもう大丈夫と思うけど、念のため、いま作業中とは別のsshセッションをもうひとつ、新たに張れることを確認（その後の作業でも使うので、そちらのコネクションも開けっぱなしで）。

# ufw status
Status: active

To                         Action      From
--                         ------      ----
22                         ALLOW       Anywhere                  
80                         ALLOW       Anywhere                  
443                        ALLOW       Anywhere                  
22 (v6)                    ALLOW       Anywhere (v6)             
80 (v6)                    ALLOW       Anywhere (v6)             
443 (v6)                   ALLOW       Anywhere (v6)        

アップデート

# apt update
# apt upgrade -y 

その他基本的なもののインストール

# apt install nmap zip postfix -y 

Apache インストール

# sudo apt install apache2 apache2-bin apache2-data libapache2-mod-wsgi -y
# sudo a2enmod ssl 
# sudo a2enmod headers
# sudo systemctl restart apache2

Apacheの設定をしておく。

~# vi /etc/apache2/sites-available/000-default.conf 

        DocumentRoot /var/www/html の下に以下のブロックを追加
        <Directory /var/www/html>
          Options FollowSymlinks
          AllowOverride All
          AddType text/html .html
          AddType text/html .htm
          Require all granted
        </Directory>

　設定に書き誤りがないことを確認。

# apachectl configtest
Syntax OK

　ドメイン用の設定

# cp /etc/apache2/sites-available/default-ssl.conf /etc/apache2/sites-available/svr1.example.com.conf 
# vi !$
                DocumentRoot /var/www/html の下に先ほどと同じブロックを追記

/etc/apache2/sites-available# a2ensite svr1.example.com
Enabling site svr1.example.com.
To activate the new configuration, you need to run:
  systemctl reload apache2

# systemctl restart apache2 

ssl化

# apt install software-properties-common certbot python-certbot-apache -y
# apt update
# systemctl restart apache2  

　以下、色々質問に答えていく。重要なポイントは httpをhttpsにリダイレクトする(2)と答えるところ。

#certbot --apache -d study01.wazawosi.com 

MySQL

　MySQLの最新リポジトリのURLを確認しておく。それを wget。
https://dev.mysql.com/downloads/repo/apt/

# wget https://dev.mysql.com/get/mysql-apt-config_0.8.15-1_all.deb
# sudo dpkg -i ./mysql-apt-config_0.8.15-1_all.deb 
# apt update
# apt install mysql-server

いよいよ baserCMS

　必要なツールとライブラリ群のインストール。

# apt install php libapache2-mod-php php-mysql -y
# apt install php7.2-mbstring php7.2-gd php7.2-xml -y

　MySQLの設定。baserが使うデータベースとユーザを作成。baserの現バージョンではゼロ日付を使用している部分が残っているようなので、NO_ZERO_DATE 系のSQLモードを外しておくべし。あと、MySQL 8.0 の強力な（新しい）パスワードシステムだと php からアクセスできないので、 native_password でユーザを作ることもポイント。

$ mysql -uroot -p 
mysql> CREATE DATABASE baser;
mysql> CREATE USER baseruser@localhost IDENTIFIED WITH mysql_native_password by 'baserpass';                                  
mysql> GRANT ALL ON baser.* TO baseruser@localhost;

mysql> SHOW VARIABLES LIKE 'SQL_MODE';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+

 ※ NO_ZERO_IN_DATE,NO_ZERO_DATE を抜いた残りのモードをセット

mysql> SET GLOBAL sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';           

　ようやく baser を取ってきて、展開、配置。

$ wget https://basercms.net/packages/download/basercms/latest_version -O baser_latest.zip
$ unzip baser_latest.zip
# cp basercms/ /var/www/html/baser -r

　今回、実際はあとから変更したのだけど（忘れていた）、オーナーも変えておくが吉。

# pwd
/var/www/html
# sudo chown -R www-data.www-data baser/

ブラウザで https://study01.wazawosi.com/baser/ にアクセス

インストーラを進めると、フォルダのパーミッション設定について指示されるので対応する。

# cd /var/www/html/baser/                                                                                    
# chmod 777 app/Config/ app/Plugin/ app/tmp app/View/Pages/ files theme img css js

　めでたく baserCMS がインストールされました。あとはブラウザから baserの各種管理機能を使ってあれこれ遊べますね。

ポイント

• baserCMSは、phpのmbstringが入っていないと、ブラウザで開いても最初のインストール画面すら出て来ない（今回はサブフォルダに配置しましたが、そこにアクセスすると、ドメインのトップページに飛ばされてしまった）。予め必要なライブラリは分かっている（この日記に書いている）ので、apt で入れておくのが吉
• MySQL 8.0 は以前のバージョンに比べていろいろストリクトになっています。ゼロ日付を入れられないとか、ユーザ認証が堅牢になったとか。この日記に書いているとおり、SQLモードを変更することと、ユーザ作成の際にnative_password で作るようにすることさえ守ればOK。
• うまく動かない時に、いろいろ検索して、いくつかのファイルを書き換えたりしてみたけど、結局、どこも書き換える必要はなかった。baserは 2,3,4 と色々な情報が混在しているので、検索したときには注意。

お礼

　ブラウザで最初の画面すら出ないときに、キャッチアップの江頭さんには大変お世話になりました。手取り足取り、だけでなく、手も足も出してくださって、mbstring がない場合に発生する事象であることを突き止めて頂きました。私じゃぁ絶対こんな短時間でたどり着けなかったです。心より御礼申し上げます。　こんど東京いらした時に、お肉、ご招待します！

追記 2020/08/13

　別の環境にインストールしようとしたときに、rewrite モジュールが必要になったので、もしかしたら最初から必要だったのかも（詳細は今回追っていない）。

# a2enmod rewrite
Enabling module rewrite.
To activate the new configuration, you need to run:
systemctl restart apache2<<

MySQLで今月の日付一覧を得る(追記あり)

ふと、今月の日付一覧が欲しくなって、MySQLでやってみた。以前は、カマセデータ用に一時的にテーブルを作る必要があったけど、今は VALUES文のおかげで、随分ラクになった、、、、かな。

　今月ついたちの日付を得るのは、色々なやり方があるけれども、これがシンプル。

mysql> SELECT DATE_FORMAT(NOW(), '%Y/%m/01');
+--------------------------------+
| DATE_FORMAT(NOW(), '%Y/%m/01') |
+--------------------------------+
| 2020/04/01                     |
+--------------------------------+
1 row in set (0.00 sec)

これに適当な日数を加えれば、任意の日付を得られるので、例えば4日加えるのは、こう。

mysql> SELECT DATE_ADD(
    ->            DATE_FORMAT(NOW(), '%Y/%m/01')
    ->        ,INTERVAL 4 DAY) d;
+------------+
| d          |
+------------+
| 2020-04-05 |
+------------+
1 row in set (0.01 sec)

ほしいのは4日後ひとつだけじゃなくて、1ヶ月ぶんなので、とりあえず10日ぶん加えるのは、ここでVALUES文を使うと便利（以前は、加える日数だけを入れたテーブルを別途用意していた）。

mysql> WITH n(num) AS (VALUES ROW(0), ROW(1), ROW(2), ROW(3), ROW(4), ROW(5), ROW(6), ROW(7), ROW(8), ROW(9))
    -> SELECT DATE_ADD(
    ->            DATE_FORMAT(NOW(), '%Y/%m/01')
    ->        ,INTERVAL n.num DAY) d FROM n;
+------------+
| d          |
+------------+
| 2020-04-01 |
| 2020-04-02 |
| 2020-04-03 |
| 2020-04-04 |
| 2020-04-05 |
| 2020-04-06 |
| 2020-04-07 |
| 2020-04-08 |
| 2020-04-09 |
| 2020-04-10 |
+------------+
10 rows in set (0.01 sec)

　ということで、10日ぶんを得ることができたので、あとはこのまま VALUES に 31まで並べればいいんだね。　というのは、間違いではないけど、かしこくもない発想。演算で求めるのが知恵を使ったやり方。とりあえずちょっと多めだけど 100日ぶん、やってみる。見やすさのために ORDER BY も付けてみた。

mysql> WITH n(num) AS (VALUES ROW(0), ROW(1), ROW(2), ROW(3), ROW(4), ROW(5), ROW(6), ROW(7), ROW(8), ROW(9))
    -> SELECT DATE_ADD(
    ->            DATE_FORMAT(NOW(), '%Y/%m/01')
    ->        ,INTERVAL n1.num*10+n2.num DAY) d
    ->   FROM n n1, n n2
    ->  ORDER BY d;
+------------+
| d          |
+------------+
| 2020-04-01 |
| 2020-04-02 |
| 2020-04-03 |
| 2020-04-04 |
| 2020-04-05 |
：　　　　　：
| 2020-07-08 |
| 2020-07-09 |
+------------+
100 rows in set (0.00 sec)

　ポイントは、0～9の数字を持った n というテーブルがあるので、これを2つ使えば99までの数字を表せるということに気づけるかどうか。

　さて、ここで欲しいのは100日間ではなく今月の日数だけなので、月末の日付を取って、そこまでの日数だけのものを加えるようにすれば完成。
　今月の最終日付を取る関数 LAST_DAY() と、そこから日付部分だけの数字を得る DAYOFMONTH() の実行例は以下のとおり。

mysql> SELECT LAST_DAY(NOW());
+-----------------+
| LAST_DAY(NOW()) |
+-----------------+
| 2020-04-30      |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFMONTH(LAST_DAY(NOW()));
+-----------------------------+
| DAYOFMONTH(LAST_DAY(NOW())) |
+-----------------------------+
|                          30 |
+-----------------------------+
1 row in set (0.01 sec)

　
　さっきは100迄を加えていたのを、この関数を使って月の日数未満のものだけを加えるようにすれば完成。ついでに、NOW()が複数箇所に出てくるのがイヤだったので、変数化したよ。

mysql> SET @dt=NOW();
Query OK, 0 rows affected (0.00 sec)

mysql> WITH n(num) AS (VALUES ROW(0), ROW(1), ROW(2), ROW(3), ROW(4), ROW(5), ROW(6), ROW(7), ROW(8), ROW(9))
    -> SELECT DATE_ADD(
    ->            DATE_FORMAT(@dt, '%Y/%m/01')
    ->        ,Interval n1.num*10+n2.num day) d
    ->   FROM n n1, n n2
    ->  WHERE n1.num*10+n2.num < DAYOFMONTH(LAST_DAY(@dt))
    ->        ORDER BY d;
+------------+
| d          |
+------------+
| 2020-04-01 |
| 2020-04-02 |
| 2020-04-03 |
：　　　　：
| 2020-04-29 |
| 2020-04-30 |
+------------+
30 rows in set (0.00 sec)

　変数化したおかげで、任意の日付を与えやすくなった。

mysql> SET @dt='2020/05/05';
Query OK, 0 rows affected (0.00 sec)

mysql> WITH n(num) AS (VALUES ROW(0), ROW(1), ROW(2), ROW(3), ROW(4), ROW(5), ROW(6), ROW(7), ROW(8), ROW(9))
    -> SELECT DATE_ADD(
    ->            DATE_FORMAT(@dt, '%Y/%m/01')
    ->        ,Interval n1.num*10+n2.num day) d
    ->   FROM n n1, n n2
    ->  WHERE n1.num*10+n2.num < DAYOFMONTH(LAST_DAY(@dt))
    ->  ORDER BY d;
+------------+
| d          |
+------------+
| 2020-05-01 |
| 2020-05-02 |
| 2020-05-03 |
：　　　　　：
| 2020-05-30 |
| 2020-05-31 |
+------------+
31 rows in set (0.01 sec)

　ちゃんと 5月なら31日までが得られていることがわかる。

この機能、べつに年月日を与える必要はなくて(日の部分は使っていない)、つまり、「年」と「月」だけを与えて動作するように改良することもできるよね。興味のある人は、やってみてください。

追記(2020/04/30) @tom__bo さんのフォロー記事を受けて

　本記事を受けて、@tom__boさんが、別の角度からの解法を示してくださいました。
tombo2.hatenablog.com

　たまたま、ほんの少しだけ古いバージョン（8.0.17）を使っていたことで、私のクエリで使用していた VALUES文が動作しなかったとのこと。その部分を 再帰CTEに書き換えたのと、日付自体をダイレクトに繰り返し処理しているところが工夫点。

特に 私も、0から9の数字を羅列するのに、今の VALUES の書き方はスマートじゃないなぁと（特に ROW()の記述が必要な点）思っていたので、再帰の部分だけアイデアを拝借して、私のクエリをブラッシュアップ。

SET @dt='2100/02/15';

WITH RECURSIVE n(num) AS 
 ( SELECT 0 UNION ALL SELECT num+1 FROM n WHERE num<9 )
SELECT DATE_ADD( DATE_FORMAT(@dt, '%Y/%m/01')
                ,Interval n1.num*10+n2.num day) d
  FROM n n1, n n2
 WHERE n1.num*10+n2.num < DAYOFMONTH(LAST_DAY(@dt))
 ORDER BY d;

　ROW(0), ROW(1)... のように書いているよりもずっとスマート。ぱっと見てこれが 0から9の10件を生成しているって読み取れるかどうかは別の話だけど。

　@tom__boさんのクエリに刺激を受けて、そちらのクエリを更に書き換えてみました。ポイントは、日付（@dt）が1回しか出てこないようにした、というところです（あとカラム名にわざわざ予約語を使っているのが個人的にはイヤだったので(笑)そこも変更）

SET @dt='2020/02/15';

WITH RECURSIVE rec(D) AS (
  SELECT LAST_DAY(@dt)
 UNION ALL
  SELECT D - INTERVAL 1 DAY
    FROM rec
   WHERE DAY(D) > 1
)
SELECT D as dt FROM rec ORDER BY dt;

　文字通りの「逆転の発想」。

.

MySQLとの戯れ：カラムの別名とグルーピングの動作について

　この日記は、少し時間が取れるようになった私が自由気ままにMySQLと戯れた結果を、つらつらと書き殴ったものです。明確な目的もなく、ただただMySQLと会話をして（そう、MySQLには「対話モード」があるのですよ）、楽しかったぁ、という日記です:-)

　今回は、MySQLのカラムの別名について遊んでみました。特に結論はなく、こういう動きをするのかぁという読み物として見ていただければと思います。原理や内部構造の追加解説は歓迎します。

準備と基本事項の確認

　とりあえず、遊ぶためのテーブルとデータを作成します。

CREATE TABLE t1 (c1 VARCHAR(10), c2 VARCHAR(10));
INSERT INTO t1 VALUES ("TEST11", "TEST12");
INSERT INTO t1 VALUES ("TEST21", "TEST22");
INSERT INTO t1 VALUES ("TEST31", "TEST32");

　2つのカラム、c1 と c2 をSELECTしてみます。普通の動作ですね。

mysql> SELECT c1, c2 FROM t1;
+--------+--------+
| c1     | c2     |
+--------+--------+
| TEST11 | TEST12 |
| TEST21 | TEST22 |
| TEST31 | TEST32 |
+--------+--------+
3 rows in set (0.00 sec)

　同じカラムを何度も取得してみても、まったく問題ありません（ただし、プログラムから呼ばれる時に、この結果をどのように扱えるかについて、問題は発生しそう）。

mysql> SELECT c1, c1 FROM t1;
+--------+--------+
| c1     | c1     |
+--------+--------+
| TEST11 | TEST11 |
| TEST21 | TEST21 |
| TEST31 | TEST31 |
+--------+--------+
3 rows in set (0.00 sec)

別名についての戯れ

　じゃぁ、本当は別のカラムなのに同じ名前にして取得することってできるのかな？　ねぇ、MySQLさん、できる？

mysql> SELECT c1, c2 c1 FROM t1;
+--------+--------+
| c1     | c1     |
+--------+--------+
| TEST11 | TEST12 |
| TEST21 | TEST22 |
| TEST31 | TEST32 |
+--------+--------+
3 rows in set (0.00 sec)

　できるみたい。 どっちが本当の c1 か分からないけど、どっちも c1 として別々の値が取得できています。
もちろん、意味もなく使うひとを混乱させるだけの、こんなことだってできます。c1はc2でc2はc1なのでc2という結果は元はc1でc1だと言っているものは元はc2ですからc1が(以下略)

mysql> SELECT c1 c2, c2 c1 FROM t1;
+--------+--------+
| c2     | c1     |
+--------+--------+
| TEST11 | TEST12 |
| TEST21 | TEST22 |
| TEST31 | TEST32 |
+--------+--------+
3 rows in set (0.00 sec)

グルーピングしてみる

　ここまでは、ただ別名を付けただけで、そのまま出力に供される程度のものでした。付けた別名をGROUP BY で使用するケースではどうなるでしょうか。別名を GROUP BY で使えるという MySQL ならではのお遊びです。

　まず基本事項の確認。 c1 でグルーピングするのは、こんなふうに書けます。

mysql> SELECT c1, MAX(c2) FROM t1 GROUP BY c1;
+--------+---------+
| c1     | MAX(c2) |
+--------+---------+
| TEST11 | TEST12  |
| TEST21 | TEST22  |
| TEST31 | TEST32  |
+--------+---------+
3 rows in set (0.00 sec)

　c1に対して別名を付けても、GROUP BY にもとのカラム名（c1）を指定して問題なく動作します。

mysql> SELECT c1 c2, MAX(c2) FROM t1 GROUP BY c1;
+--------+---------+
| c2     | MAX(c2) |
+--------+---------+
| TEST11 | TEST12  |
| TEST21 | TEST22  |
| TEST31 | TEST32  |
+--------+---------+
3 rows in set (0.00 sec)

　c1という名前が、「元々 c1 だったカラム」を明確に示しているので、エイリアスではなく元々のカラム名と解釈してくれているように見えます。
　では、c1という別名を持つカラムがある場合はどう？　混乱する？

mysql> SELECT c1 c2, MAX(c2) c1 FROM t1 GROUP BY c1;
+--------+--------+
| c2     | c1     |
+--------+--------+
| TEST11 | TEST12 |
| TEST21 | TEST22 |
| TEST31 | TEST32 |
+--------+--------+
3 rows in set, 1 warning (0.00 sec)

　「もともとのc1」でグルーピングしてくれますね。すごいや MySQL。
でもよく見ると、ワーニングが出ています。なんだ、言いたいことがあるなら、そう言ってくれればいいのに。（言ってる）

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1052 | Column 'c1' in group statement is ambiguous |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

　c1が ambiguous だって言ってますね、「わかんないよー」って。　でも、分からないのに結果は一応返してくれるMySQLさん、素敵。

エラーにしてみる（いじめ）

　
　ここからは、MySQLさんにもっと無茶振りしてみます。もはや、いじめの様相。
でも、無理なものは無理って言ってくれるので、安心です（なにが）。

　まず、グルーピングカラムに別名をつけて、その別名でグルーピングしてみます。付けた別名は他のカラムに実在の "c2"。

mysql> SELECT c1 c2, MAX(c2) FROM t1 GROUP BY c2;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t1.c1' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

　別名をつけているのに、c2 を別名ではなく実在カラムのほうとして解釈して、エラーとなっているようですね。
念のため（実在カラムとは異なる名前の）別名を付けてみて、別名でグルーピングできること自体は認識誤りでないことを確認してみます。

SELECT c1 a2, MAX(c2) FROM t1 GROUP BY a2;
mysql> SELECT c1 a2, MAX(c2) FROM t1 GROUP BY a2;
+--------+---------+
| a2     | MAX(c2) |
+--------+---------+
| TEST11 | TEST12  |
| TEST21 | TEST22  |
| TEST31 | TEST32  |
+--------+---------+
3 rows in set (0.00 sec)

　a2 の a は alias の a のつもりですが、そんなのどうでもいいです。ちなみに、c1 の c は column ですし、t1 の t は table です。どうでもいいです。
　ここで大事なのは、 GROUP BY a2 という、GROUP BY に別名を与える書き方自体には問題がないことが確認できたことです。

　じゃぁ a2 っていう別名なら動作するので、集約カラムに対しても a2 って名前を付けてみたら、どう？　どっちの a2 だと理解してくれるのかな。
　

mysql> SELECT c1 a2, MAX(c2) a2 FROM t1 GROUP BY a2;
ERROR 1056 (42000): Can't group on 'a2'

　わかんないんだって。そりゃそうですよね。
そもそも、 1056 のエラーってほとんど見たことがない気がする。新たな出会いがあった気がして、少し嬉しい。

その他のエラー

　あまり見たことがない（気がする）エラーを見て、面白くなってきたので、グルーピングの他のエラーを試みたくなってきました。
MySQL のエラーリストを見ながら、GROUP BY に関係しそうなものを探して、トライしてみることにします。
https://dev.mysql.com/doc/refman/8.0/en/server-error-reference.html

　グループ関数の使い方がおかしい（WHEREで使おうとしている）場合に出るエラー 1111。

mysql> SELECT c1, AVG(c2) a2 FROM t1 WHERE AVG(c2)=0 GROUP BY c1;
ERROR 1111 (HY000): Invalid use of group function

　念のため紹介しておくと、別名を使えばいいとかそういう話ではありません。処理順序の話（SELECT句の別名が決まるのは結構アトのほう）をイメージすると良いと思います。たぶん。ちなみに、この時にはまた別のエラーコードになっていることにも注目。

mysql> SELECT c1, AVG(c2) a2 FROM t1 WHERE a2=0 GROUP BY c1;
ERROR 1054 (42S22): Unknown column 'a2' in 'where clause'

　もうひとつ。1140 エラーを出してみます。書かなければならないGROUP BY を省略した時のエラーです。

mysql> SELECT c1,MAX(c2) FROM t1;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.t1.c1'; this is incompatible with sql_mode=only_full_group_by

フィナーレ

　あぁ、楽しかった。楽しかったね MySQLさん。
そろそろ飽きてきたから今日は帰るね。あ、そうそう。最後になっちゃったけど、MySQLさん、自己紹介しておく？　
じゃぁね。

mysql> STATUS
--------------
mysql  Ver 8.0.19 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          7
Current database:       test
Current user:           root@
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.19 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/run/mysqld/mysqld.sock
Binary data as:         Hexadecimal
Uptime:                 2 days 15 hours 15 min 1 sec

Threads: 1  Questions: 72  Slow queries: 0  Opens: 117  Flush tables: 3  Open tables: 53  Queries per second avg: 0.000
--------------

mysql> select @@session.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Microsoft Universal Mobile Keyboard が充電できなくなった

Microsoft の Universal Mobile Keyboard というのがありましてね。質実剛健でありながらスリムなボディに惚れて、一時期、よく使っていたわけですよ。むかし。

　最近、しばらく使っていなかったこいつを引っ張り出して、使って見ようと思ったのですが、電源が入らない。まぁバッテリーからっぽだよなと思い、充電すると、充電しながらならば使用できるけど、充電ケーブルを外すと使えなくなる（十分な時間、ケーブルを接続した後でさえも）。

　バッテリーが逝っちゃったようです。　ケーブルを接続しながらならば使えるとは言え、それじゃモバイルキーボードの意味がないですよね。軽く検索してみて、自前でバッテリー交換するのは無理そうな話ばかりだし、生産終了品なので修理も再入手もできないようなので、諦めることにしました。ありがとう、私の Universal Mobile Keyboard。

　お別れの餞に、分解の儀式です。

この薄い筐体の中に、私を苦しめたバッテリーは一体どこにあるのか。別れの前にお目にかかって、ひとことご挨拶申し上げたいじゃないですか。もちろん、一度分解したら元に戻せないことを承知の上で、です。

　まずびっくりしたのが、フタ。　おまえ、磁石でくっついていたんか！ 長らく持ち歩いていたのに気づかなかったよ。

　次にキーボード部分を外します。軽く力を入れて反らすとツメが外れて浮く部分があるので、そこをスタートとしてどんどんツメを外していきます。

　外周（のうち上辺を除く３辺）を外したら、中に進みます。はめ殺しって言うんですかね、プラスチック溶かしてそのままくっついているので、後戻りできません。ブチブチ切りながら、外していきます。外したところが、この写真。小さくて見にくいかもしれませんが、キーのあいだあいだに、ブチブチした痕跡が見えます。

　このキーボードは、のり付けされているようです。大きめのマイナスドライバーを挟み込みながら、強引に剥がしていきます。剥がすと現れる鉄の板。ここで初めてネジに出会います。小さいプラスねじで留められた鉄の板。本丸は近い。

　ネジを外すと、ついにご対面。私を悲しませてくれたバッテリーさんです。
交換するわけでもなく、ご対面を果たして満足をしたので、今までの感謝を心で述べて、サヨナラとなりました。いままでありがとう！

　そんなわけで、本機種はバッテリーがヘバると、交換は結構大変（というか多分、戻すには相当の力量がいる）というお話でした。「もとに戻さなくて良い分解」って、気楽で楽しいですね(笑)。

　ちなみに最近は、Bookey Stickというものを 持ち歩いています。頻繁に使うものではないのだけど、それでもカバンの中に入れておいて邪魔にならないコンパクトさが素晴らしいです。MS Universal Mobile Keyboardに負けずしっかりしたタッチで、気に入っています。

JTT Online (スタンド内蔵/折りたたみ式) Bookey Stick (ブラック) iPad/iPhone/Android対応 マルチキーボード スティック状に折りたたみ可能 持ち運びに便利なポータブルタイプ Bluetooth ワイヤレス IPBKYSKWH

iClever Bluetoothキーボード 折りたたみ式 二つ折り スタンド一体型 軽量 薄型 ワイヤレスキーボード iPad＆iphone用 IOS/Android/Windowsに対応 IC-BK11 (シルバー)

個性的なcsvからデータを取り出した話

コンピュータシステム周辺に関わっているとCSVファイルとのお付き合いは避けて通れないものと言えるでしょう。みなさんはどんなCSVとお付き合いしたことがありますか。
　セパレータが明確でない（というか明確なのだけど例外例外の積み重ねが意外とややこしい）のがcsvとのお付き合いで気を使うところですよね。

　単純に考えれば、コンマが出てきたら次の項目、改行により次のレコードだと思うじゃないですか。ところが、コンマが出てきても次の項目に移らなかったり、1つのレコードが複数行に亘って記述されたりすることもあります。そう、クォーテーションされている場合ですね。

　でも、この日記で紹介しようとしているのは、そんなレベルではない「個性的なCSVファイル」です。実際には「クソCSV」と呼んでいたのですが、日記に書くには品のない言葉なので、ここでは「個性的なCSV」と呼ぶことにします。　

ことの起こり

　先日参加したハッカソン、Geospatial Hackers Japan での開発で、名古屋地区の観光地情報が必要となり、教えてもらいました。ハッカソン参加に関する日記は、以下。

sakaik.hateblo.jp

　このファイルから、名古屋市内の観光地情報を抜き出して使える状態にする、というのが私の役割。
最低限、名称と緯度経度は取得したいが、状況に応じて他の項目も抜き出せるよう、「抜き出し作業」を行うことを目的とせずに、「抜き出すしくみ」を作ることにしました。　ハッカソンという限られた時間内での作業ですので、最高最適な方法であるかよりも、時間内での実現性を優先しての作業となります。

ザ・データの取得

　私を楽しませてくれることになるCSVファイルは、総務省の「公共クラウドシステム」で公開しているものです。このページは

本サイトは、2020年３月末日をもって閉鎖となりますので、ご利用にあたってはご注意願います。

　ということで、とりあえずデータを実際にみてみたいかたは、ダウンロードを急げ！

公共クラウドシステム | API公開サイト

ザ・データの概要

　CSVファイルです。ざっと雰囲気だけキャプチャ画像で紹介すると、こんな感じ。

　テキストでも紹介しておきます（膨大なので、間引き加工しています。画像のほうは間引きなし）。

"tourspots[0]","name","name1","written",,,,,,"夏まつり桂川２０２０"
"tourspots[0]","name","name1","spoken",,,,,,"なつまつりけいせん２０２０"
"tourspots[0]","genres[0]","L",,,,,,,"イベント"
"tourspots[0]","views[0]","copyright",,,,,,,"有"
"tourspots[0]","views[0]","fid",,,,,,,"base/40/421/40421KANKO2080556001.jpg"
"tourspots[0]","place","coordinates","longitude",,,,,,"130.6788888"
"tourspots[0]","place","coordinates","latitude",,,,,,"33.5791666"
"tourspots[0]","place","postal_code",,,,,,,"820-0696"
"tourspots[0]","place","pref","written",,,,,,"福岡県"
"tourspots[0]","place","city","written",,,,,,"嘉穂郡桂川町"
"tourspots[0]","place","street","written",,,,,,"大字土居424番地8"
"tourspots[0]","place","street","spoken",,,,,,"おおあざとい424ばんち8"
"tourspots[0]","visit","guide","note",,,,,,"8/22（土）【昨年内容】18：00王塚太鼓～
8/23（日）【昨年内容】16：50吹奏楽～17：20戦隊ヒーロー～18：05SUPER・BOWL～ 20：00閉会式"
"tourspots[0]","visit","service","periods[0]","type",,,,,"公開"
"tourspots[0]","visit","service","periods[0]","st_date",,,,,"2020年8月22日"
"tourspots[0]","visit","service","periods[0]","day_of_week",,,,,"土"
"tourspots[0]","visit","service","periods[0]","hours",,,,,"18:00~20:40"
"tourspots[0]","visit","service","periods[0]","note",,,,,"王塚太鼓演奏、盆踊り大会、お化け屋敷"
"tourspots[0]","wifi","established",,,,,,,"無し"
"tourspots[0]","mng","lgcode",,,,,,,"40421"
"tourspots[0]","mng","refbase",,,,,,,"40421KANKO2080556"
"tourspots[0]","mng","refsub",,,,,,,"2080556"
"tourspots[0]","mng","status","update",,,,,,"2019-10-23 13:46:46"
"tourspots[0]","mng","data_source",,,,,,,"福岡県嘉穂郡桂川町"
"tourspots[1]","name","name1","written",,,,,,"土師の獅子舞＜土師老松神社＞"
"tourspots[1]","name","name1","spoken",,,,,,"はじのししまい＜はじおいまつじんじゃ＞"
"tourspots[1]","genres[0]","L",,,,,,,"イベント"
"tourspots[1]","genres[0]","M",,,,,,,"祭事"
"tourspots[1]","genres[0]","S",,,,,,,"行事・祭事"
"tourspots[1]","views[0]","name","written",,,,,,"獅子舞（2014春　上土師地区当番）"
"tourspots[1]","views[0]","name","spoken",,,,,,"ししまい(2014はる　かみはじちくとうばん)"
"tourspots[1]","views[0]","where",,,,,,,"土師老松神社"
"tourspots[1]","views[0]","copyright",,,,,,,"有"
"tourspots[1]","views[0]","fid",,,,,,,"base/40/421/40421KANKO2059825001.jpg"

　tourspots[] ごとに1つの観光地を表しています。ナニコレCSV。

とりあえずMySQLに入れる

　いやもう、「こんなCSVがあるよ」と紹介したかっただけなので、続きを書くモチベーションもさほど高くないのだけど、折角なので、苦労話の一端だけでも書いておこうと思います。まずは、とりあえずMySQLに突っ込むことを目標にしました。 bash上で加工する手も考えましたが、「とりあえずRDBMSに突っ込めばなんとかなる」という自信というか、なんとなくSQL文は頭に浮かんでいたからです。

　そのためには、まずこの「個性的なCSV」をプレ処理する必要があります。改行が含まれていたりクォーテーション文字列内にコンマが入っているようなCSVファイルに対しては、Excelが非常に強力です。いきなりCSVファイルをダブルクリックしちゃだめですよ。「テキストファイルの読み込み」機能を使います。
　読み込ませたら、改行が含まれている項目の改行コードを除去して、INSERT文を作ります。この作業はさくっと使い捨てVBAマクロを書いて実施。

Sub MakeIns()
  Dim o As Worksheet
  Set o = ActiveWorkbook.Worksheets(2)
  Set ws = ActiveWorkbook.Worksheets(1)
  For i = 2 To 553444
    s = "INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ("
    s = s + "'" + ws.Cells(i, 1) + "',"
    s = s + "'" + ws.Cells(i, 2) + "',"
    s = s + "'" + ws.Cells(i, 3) + "',"
    s = s + "'" + ws.Cells(i, 4) + "',"
    s = s + "'" + ws.Cells(i, 5) + "',"
    s = s + "'" + ws.Cells(i, 6) + "',"
    tmpstr = Replace(ws.Cells(i, 10), vbCrLf, "\n")
    s = s + "'" + tmpstr + "');"
    o.Cells(i, 1) = s
  Next
  
  Set o = Nothing
  Set ws = Nothing
End Sub

できあがったSQLの例(冒頭数行)：

INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','name','name1','written','','','夏まつり桂川２０２０');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','name','name1','spoken','','','なつまつりけいせん２０２０');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','genres[0]','L','','','','イベント');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','genres[0]','M','','','','祭事');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','genres[0]','S','','','','行事・祭事');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','views[0]','copyright','','','','有');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','views[0]','fid','','','','base/40/421/40421KANKO2080556001.jpg');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','views[1]','name','written','','','夏まつり桂川　盆踊り');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','views[1]','copyright','','','','有');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','views[1]','fid','','','','base/40/421/40421KANKO2080556002.jpg');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','place','coordinates','longitude','','','130.6788888');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','place','coordinates','latitude','','','33.5791666');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','place','postal_code','','','','820-0696');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','place','pref','written','','','福岡県');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','place','pref','spoken','','','ふくおかけん');

　このINSERT文のファイルを UTF-8 で保存し、目視でざーっと眺めて、おかしな気がするところを修正します（1行まるごとが クォーテーションされているものがいくつかあったり、無駄な改行が除去しきれていなかったものがあったりしました）。このファイルは約58万行、スポット数で2万2千件ほどありますので、作業のやり方としては、テキストファイルを高速でスクロールさせながら視界の中に入る「なんかへんだぞ」を発見していく感じになります。少し経験が必要になる作業かもしれませんが、見落としても大概後続の処理でエラーになってくれるだけなので、適当なところでやめて、先に進みます。

　以下のテーブルを作って、作成したINSERT文を流し込みます。エラーが出たら原因を調べてやり直し、の繰り返し。

DROP TABLE IF EXISTS kanko;
CREATE TABLE kanko (
  c1 varchar(40),
  c2 varchar(40),
  c3 varchar(40),
  c4 varchar(40),
  c5 varchar(40),
  c6 varchar(40),
  c10 text);
CREATE INDEX idxc1 ON kanko (c1);

　なお、LOAD DATA を使わないのか、と思った人もいるかもしれませんが、（私にとっては）未知のデータに対しては DMLを使って実施したほうが、問題点の把握と追跡に利があると判断してのことです。ロードツールを使うと、エラーになったときに、何が気に入らなくてエラーにされたのかが見えにくいケースがあり、今回はその泥沼リスクを少しでも低減させる意図もあり、DMLを使いました（今回に限らず、SQL文を作成して作業することが比較的多いですね、私は）。

データの取り出し

　よく考えたら全然 CTE 使う必要がない（サブクエリで十分）だったり、もっとスマートな書き方がありそうだったりするので、我こそはという方はぜひお試しいただきたいところですが、私の考えた「とりあえずデータを取得できるSQL例」はこんな感じになります。

WITH CTE_name AS (SELECT * FROM kanko WHERE c2='name' AND c3='name1' AND c4='written')
    ,CTE_lon  AS (SELECT * FROM kanko WHERE c2='place' AND c3='coordinates' AND c4='longitude')
    ,CTE_lat  AS (SELECT * FROM kanko WHERE c2='place' AND c3='coordinates' AND c4='latitude')
SELECT n.c1, substr(n.c10,1,15) name, lon.c10 longitude, lat.c10 latitude
  FROM CTE_name n
    LEFT OUTER JOIN CTE_lon lon ON (n.c1=lon.c1)
    LEFT OUTER JOIN CTE_lat lat ON (n.c1=lat.c1);

　最低限の項目(番号と名前と緯度経度）だけを取得する例ですが、もっと必要な項目がある場合は適宜、CTE/SELECT句/JOIN を増やせば良いです。

いざデータ活用へ

　冒頭の話を覚えているでしょうか。今回は、この中から名古屋市の観光地情報だけが欲しかったのでした。ということで住所欄に「名古屋市」が含まれているものだけを抽出対象にすれば良いです。SQL、ラクですね。万歳。
　・・・・・あれ？　ない。。。1件もマッチしない。。。？？
　SQLの書き方を間違えたのかと驚くも、もとのテキストファイルをgrepもしてみて、原因判明。

　名古屋市のデータ、入ってない！！

　想像するに、どうも総務省のこのデータ、各自治体に「データ提出依頼」でも出したりして、それに乗っかってくれた自治体の情報だけをそのまま載せたのではないかという気がします。名古屋市は誘いに乗らなかったのではないかと。

　教訓。　データを加工する前に、中に欲しいデータが含まれているかを確認しよう。