MUGIJIRU.JP

Webエンジニアの雑談ブログ

MySQL集計関数の小技(GROUP_CONCAT)

MySQLには「GROUP_CONCAT」という関数があります。
これには、複数のレコードを1行の文字列にカンマ区切りでつなげる能力があって
調査とか分析などでもたまに利用します。

どういう関数なのかは下記などよくまとめてくださっている方の記事をご参考に・・・
https://qiita.com/kyuu1999/items/93b02128f07c577b3e48

小技の紹介

この結果セットがカンマ区切りで文字として取れるのは周知のことと思いますが
実はこの関数、このまとまった文字列に対してソートとか重複排除ができたりします。

# 結果セットをidを基準に昇順で並べる
GROUP_CONCAT(bind_value ORDER BY id ASC)

# 結果セットを重複排除してユニークにする
GROUP_CONCAT(DISTINCT bind_value)

# 結果セットをユニークにしてidを基準に降順にする
GROUP_CONCAT(DISTINCT bind_value ORDER BY id DESC)

ちなみに、DISTINCTはCOUNTなどでも利用できます。

便利ですね!

Laravel5.3 以降 バージョンアップ MIddreware ConvertEmptyStringsToNullと日付系Validation

表題の件です。

アップグレードガイド 5.3 Laravel

配列、論理型、整数、数値、文字列をバリデートする場合、新しいnullableルールを指定していない限り、有効な数値として判断されなくなりました。

結論から言うと
Laravel5.3以降はバリデーションルールのrequireの逆は指定なしではなくnullable
という話です。

Laravel5.5のデフォルトのグローバルミドルウェア構成

下記のとおりです。

<?php

namespace App\Http;

use Illuminate\Foundation\Http\Kernel as HttpKernel;

class Kernel extends HttpKernel
{
    /**
     * The application's global HTTP middleware stack.
     *
     * These middleware are run during every request to your application.
     *
     * @var array
     */
    protected $middleware = [
        \Illuminate\Foundation\Http\Middleware\CheckForMaintenanceMode::class,
        \Illuminate\Foundation\Http\Middleware\ValidatePostSize::class,
        \App\Http\Middleware\TrimStrings::class,
        \Illuminate\Foundation\Http\Middleware\ConvertEmptyStringsToNull::class,
        \App\Http\Middleware\TrustProxies::class,
    ];
...

ConvertEmptyStringsToNull とは何をしているミドルウェアなのか

読んだところ、リクエストパラメータが文字列かつカラ文字だったらnullに変換する処理ということがわかります。

<?php

namespace Illuminate\Foundation\Http\Middleware;

class ConvertEmptyStringsToNull extends TransformsRequest
{
    /**
     * Transform the given value.
     *
     * @param  string  $key
     * @param  mixed  $value
     * @return mixed
     */
    protected function transform($key, $value)
    {
        return is_string($value) && $value === '' ? null : $value;
    }
}

Validationにおいて何が起こるのか

現在確認できている限り、日付系のバリデーションルールにおいて
常にfalseを返す動きを取るようになってしまうことが分かっています。

他のルールでも影響があるかもしれませんが全ては追っていません。

日付の範囲をフォームから受け取る、こんなバリデーションルールがあったとします。

<?php
$rules = [
    'date-from' => 'required|date',
    'date-to'   => 'date|after:date-from'
];

本来であれば

  • date_fromというパラメータは必須で、日付形式である必要がある。
  • date_toというパラメータは省略可能であるが、入力がある場合は日付形式かつdate_from以降である必要がある。

という動きをするはずです。

ですが、このConvertEmptyStringsToNullというミドルウェアが挟まると
afterのルールが入力の有無に関わらず常にfalseとなってしまいます!

なぜか

このConvertEmptyStringToNullミドルウェアを通ると

?date-from=2018-06-20&date-to=

というパラメータが

<?php
[
    'date-from' => '2018-06-20',
    'date-to'   => ''
];

ではなく

<?php
[
    'date-from' => '2018-06-20',
    'date-to'   => NULL
];

に変換されます。

すると、日付形式のバリデーション「compareDates」を利用するルールにおいては
値にNullが入っていると常にfalseを返すようになってしまいます。

<?php
# Illuminate/Validation/Concerns/ValidatesAttributes
...
    /**
     * Compare a given date against another using an operator.
     *
     * @param  string  $attribute
     * @param  mixed  $value
     * @param  array  $parameters
     * @param  string  $operator
     * @return bool
     */
    protected function compareDates($attribute, $value, $parameters, $operator)
    {
        if (! is_string($value) && ! is_numeric($value) && ! $value instanceof DateTimeInterface) {
            return false;
        }

        if ($format = $this->getDateFormat($attribute)) {
            return $this->checkDateTimeOrder(
                $format, $value, $this->getValue($parameters[0]) ?: $parameters[0], $operator
            );
        }

        if (! $date = $this->getDateTimestamp($parameters[0])) {
            $date = $this->getDateTimestamp($this->getValue($parameters[0]));
        }

        return $this->compare($this->getDateTimestamp($value), $date, $operator);
    }

Laravel5.3以降は、このミドルウェアによって入力のないパラメータはNullとなり
DB更新と親和性が高まります。
パラメータの存在有無は、従来通りhasで確認することになるでしょう。
その代わり、requiredの逆にnullableを指定しないと、一部のルールが常にfalseを返すようになります。

Laravel5.2以前の動きに戻したい場合、このミドルウェアコメントアウトすれば元の挙動に戻ります。

バージョンアップ中に当現象に遭遇し、ソースを読んで変な時間を取ることになったので
似たケースで困る方の助けになれば幸いです。

csvファイルの処理

csvファイルの処理はシステム開発ではポピュラーなものですが
Microsoft Excelなどで吐き出されるような

・セル内改行を含んでいたり
・ダブルクォートで値が囲まれていたり

といったデータの処理については、初見ではどうすればいいのか悩むものでした。

私はいつも使う関数を持っているのですが、これが随分と信頼性があり
業務システムにおいてこの関数を介して処理したデータでトラブルが起きたことがありません。

fgetcsv, splfileobject, explodeによるお手製処理など
選択肢はいろいろありますが、どれも何らかトラブルが起きることが多い中で
この関数は安定感があるようです。

貼っておきます。

<?php
/**
 * CSVファイルの1行を配列で取得
 * 複数行対応
 *
 * @param object $fh fopen()などで得られたファイルハンドラ
 * @param string $file_encode 処理するファイルの文字コード
 */
function ml_fgetcsv(&$fh, $file_encode = 'UTF-8')
{
    $values = array();

    if (feof($fh)) {
        return false;
    }

    while ($values === array()) {
        $csv = '';

        while (!feof($fh)) {
            $csv .= fgets($fh);

            if (((preg_match_all('/"/', $csv, $matches))%2) == 0) {
                break;
            }
        }

        if (mb_strlen(trim($csv)) === 0) {
            continue;
        }

        $temp = preg_replace('/(?:\x0D\x0A|[\x0D\x0A])?$/', ',', trim($csv), 1);

        preg_match_all('/("[^"]*(?:""[^"]*)*"|[^,]*),/', $temp, $matches);

        for ($i = 0 ; $i < count($matches[1]); $i ++) {
            if (preg_match('/^"(.*)"$/s', $matches[1][$i], $m)){
                $matches[1][$i] = preg_replace('/""/', '"', $m[1]);
            }

            if (strtoupper($file_encode) !== 'UTF-8') {
                $values[] = mb_convert_encoding($matches[1][$i], 'UTF-8', $file_encode);
            } else {
                $values[] = $matches[1][$i];
            }
        }
    }

    return $values;
}

MySQL InnoDB Auto Increment

Auto Incrementは、Primary Keyのオプションです。
insertしたときに、自動的に増えていくアレです。

今回は、InnoDBにおけるこのAuto Incrementについて
認識しておいたほうがいいことをいくつか紹介します。

1. Auto Incrementは、原則1ずつ増える

id name
1 いちご
2 みかん

ここに、「りんご」をidの指定なしで追加するとidは自動的に3となります。

id name
1 いちご
2 みかん
3 りんご

※いくつずつ増えるかは、実は設定で変更できますが、めったに使わないのでここでは触れません。

2. Auto Incrementは、最大値 + 1ずつふえる

id name
1 いちご
2 みかん

ここに「ぶどう」をid = 5で追加すると、idは5になります。

id name
1 いちご
2 みかん
5 ぶどう

ここで、idの指定なしで「りんご」を追加すると、idは自動的に6となります。
3と4が空いていますが、ここを埋めるような動きはしません。

id name
1 いちご
2 みかん
5 ぶどう
6 りんご

3. Auto Incrementの最大値は、起動中は削除の影響を受けない。原則メモリが持っている。

id name
1 いちご
2 みかん
5 ぶどう

ここで、5のみかんを物理削除します。

id name
1 いちご
2 みかん

この状態で、idの指定なしで「りんご」を追加すると、idは自動的に6となります。
1,2しかありませんが、最大値はテーブル自体が持っているということです。

id name
1 いちご
2 みかん
6 りんご

4. テーブル自体が持っているAuto Incrementの値は、サーバの再起動でリセットされる!

これが要注意です!再起動が挟まると、3. の動きが変化します。

id name
1 いちご
2 みかん
5 ぶどう

ここで、5のみかんを物理削除します。

id name
1 いちご
2 みかん

ここで、サーバを再起動してから、idの指定なしで「りんご」を追加すると・・・
idは、6になってほしいところですが、なんと3になります。

id name
1 いちご
2 みかん
3 りんご

これはヤバい。(知らないと)
ユニークである必要があり、外部キーとして重要な意味を持つデータとして使われている場合は
予期しない動きを誘発することになるでしょう。

何この動き。

MySQL8.0で修正されたそうです。
5.7まではこの動きをします。要注意!

対策の候補

あまりありません。
・8.0にする
・外部キー制約を使う
レコードが物理削除されるテーブルではAuto Incrementに頼らない設計、実装にする

サーバの再起動のタイミングは厳密にコントロールできるものではありませんので
性質を理解して使う必要がありますね。要注意です!

Seederでレコードをループしながら色々やるときの話

LaravelのSeederの小ネタ。

データの変換やら何やらで、特定のテーブルの行をループしながら処理をするという場面はたくさんあると思います。
・レコード数が多いと全行getではメモリが爆発するのでページングする機会も多いはず。
・なんか実行中に進捗やら何やら出したいときもあるはず。

そんな時の書き方の例を紹介します。

<?php
use Illuminate\Database\Seeder;
use Illuminate\Database\Eloquent\Builder;

class SampleSeeder extends Seeder
{
    // 実行
    public function run()
    {
        $this->command->info('[!]レコードをループします');

        // データを取得するためのbuilder
        $builder = \App\Shouhin::query();

        // 当バッチ専用の絞り込み条件とか
        $scope = function(Builder $builder) {
            return $builder->withTrashed();
        };

        // 進捗バーの生成
        $progress_bar = $this->command
            ->getOutput()
            ->createProgressBar($this->pagerCount($builder, $scope));

        // ページング
        $page = 1;

        while(($rows = $this->pager($page, $builder, $scope))->count()) {
            // 行ごとのループ処理
            foreach ($rows as $row) {
                // 何かやるならここで。

                // 進捗バー
                $progress_bar->advance();
            }

            $page ++;
        }

        // 進捗バー終了
        $progress_bar->finish();
        $this->command->line(' done.');

        $this->command->info('[!]レコードのループが終わりました');
    }

    /**
     * ページング処理
     *
     * @param integer $page       ページ(1始まり)
     * @param object  $builder    Builder
     * @param object  $scope      第一引数にbuilderを取るクロージャ(where句の追加等)
     * @param integer $per_page   1ページあたりのレコード数
     *
     * @return Collection ページごとのレコード群
     */
    public function pager($page, Builder $builder, Closure $scope = null, $per_page = 5000)
    {
        return $this->addPagerScope($builder, $scope)
            ->skip(($page - 1) * $per_page)
            ->take($per_page)
            ->get();
    }

    /**
     * 行数カウント処理
     *
     * @param object $builder Builder
     * @param object $scope   第一引数にbuilderを取るクロージャ(where句の追加等)
     *
     * @return integer 件数
     */
    public function pagerCount(Builder $builder, Closure $scope = null)
    {
        return $this->addPagerScope($builder, $scope)->count();
    }

    /**
     * builderにスコープを当てる
     *
     * @param object $builder Builder
     * @param object $scope   第一引数にbuilderを取るクロージャ(where句の追加等)
     *
     * @return scope適用後のBuilder
     */
    public function addPagerScope(Builder $builder, Closure $scope = null)
    {
        if ($scope !== null) {
            $scope($builder);
        }

        return $builder;
    }
}

で、実行するとこんな感じ

$ php artisan db:seed --class=SampleSeeder
[!]レコードをループします
 1518/1518 [▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓] 100% done.
[!]レコードのループが終わりました
$ 

応用していって、builder, scope および一行ごとの処理を差し替えるような構造にすれば
制御に関する行がぐっと減ります。
Modelやら何やらごとの処理を集中してサクサク作っていけるのではないかと思います。

シェルスクリプトをバックグラウンド実行するnohupの紹介

Linuxにおいて、長時間にわたるバッチ処理をシェルで叩くとき、シェルのタイムアウト
ネットワーク切断による中断の憂いと対峙した経験は誰しもあると思います。


今回は、シェルをバックグラウンド実行
(=クライアントが切断されても停止しない実行)
の手法の一つを紹介します。

nohup というコマンドがあります。

使い方は簡単。いつも叩いているシェルコマンドの先頭に「nohup」とつけるだけ。

$ nohup {実行シェル}
# 例
$ nohup /usr/local/bin/php huge_batch.php

これを実行すると、nohupを叩いた時点のカレンドティレクトリに
「nohup.out」というファイルができ、ここにシェルの実行結果が書き込まれるという
超シンプルな機能です。

クライアントを切断しても実行され続けます。
(ただし、nohup中に意図的にCtrl+Cなどでnohupをキャンセルしてしまうと普通に止まるので注意)

nohup.outのファイル名、出力位置を変更したい場合は下記のように出力先を明示してやれば良いです。

$ nohup {実行コマンド} > /var/log/my_nohup_result.txt

うっかりnohupをつけずに長時間の処理を実行しちゃった時

途中からnohupに回すこともできるようです。
下記記事が参考になると思います。
http://blog.glidenote.com/blog/2013/09/26/bg-and-disown/

ぜひご活用ください。

出張についてのあれこれ

2018年になりました。今年もよろしくお願いいたします。

ところで、去年から2泊以上の出張に出る機会が多々あり
なんだか慣れてきたので、出張に関することについての勘所や持ち物について書きたいと思います。

到着日時、宿泊先の調整

まず、お客様と日程、日時調整を十分に行います。
出先に伺うならば、重要なのは何時ごろにお客様のところに到着するかです。
少なくとも直前の営業日には「予定通り伺いますのでよろしくお願いいたします」
程度の挨拶をしておくべきでしょう。

また、夜はお付き合いがありますので、宿泊先も重要です。
お客様が帰りにくい方面にホテルを取ってしまうと気を遣わせてしまうことがあります
事前に軽く相談したりしておくと良いと思います。
(良いホテルを紹介してもらえることもあります)

当然のことですが、会社の人にもしっかり連絡しておきましょう。最低限、
1. いつからオフィスにいない
2. いつからオフィスに戻る
3. いない間の自分に関する事の段取り

の3点を連絡しておきましょう。

ホテルを取る

経費と立地のバランスを取りましょう。
ネットで満室でも電話すると空いてたりするので、ネットで取れなければ電話を。
なるべく早く取ると良いと思います。

交通機関のチケットを取る

当日買いは早朝すぎると困難だったり、乗り換えの連結時間が短すぎてハラハラする事があります。
事前に買えるものは買っておきましょう。

パッキング

直前まで詰められないもの以外は、前日に用意を終わらせておくと良いです。
構成は、キャリーケース x 1 普段の仕事で使えるレベルのサイズのバッグ x 1
程度が無駄がないでしょう。後者はチェックイン後のメインバッグになります。
キャリーケースに載せられるやつが良いでしょう。

貴重品

財布、鍵、免許証などなど。最初に確認しておくと良いです。

現金

出先によってはATMがない場面が無くはないので、多めに持っていきましょう。
夜のお付き合いがあるならばなおさら。

持ち込む資料・筆記具

説明資料、見積書、契約書等。あるなら絶対に忘れずに。

ノートPC

充電しておきましょう。

充電アダプタとケーブル

純正でも良いですが、軽くてポートが多く、ケーブルが長いほうが万能です。
下記とかおすすめ。5ポート、軽量小型でありながらMacBookPro15インチでもしっかり充電されます。
Amazon CAPTCHA

テザリング端末

携帯電話なら忘れることはないと思います。VPN接続が必要なら、設定が大丈夫か確認を

モバイルバッテリー

携帯用です。あるとないでは安心感が違います

USBメモリ

いざという時あるとスムーズな場面があります。

着替え

スーツであれば下着とYシャツの3点構成になると思います。
機内持ち込み可能なサイズで3泊ぐらいまでの着替えなら余裕ですね。
衣類圧縮袋が超おすすめ。3点を1袋に入れて圧縮するとかなりサイズが落ち運用しやすいです。
(入れて、zipで閉じて、丸めるだけで空気が抜けるやつが個人的にはベスト)
あと、用意されている浴衣は寒いことがあるので、スウェットを入れておくと色々良いです。

エチケットセット

ヘアクリーム、ブラシ、デオドラントや香水、マウスケア用品など。
普段使っているものを持っていくと精神的に安心する印象です。
コンビニが少ない地域に行く場合は、綿棒を入れておくと色々捗ります。
(アニメティにないホテルが多いため)

あと、風呂でナイロンタオルを使う人は出張用に1枚用意すると超良いです
ホテルの普通のタオルで体を洗うことの面倒臭さを痛感すると思います。

薬とか

普段使うことのあるものは用意しておくと良いでしょう。
私は鼻づまり用の点鼻薬を必ず用意しています。

娯楽用品

本、ミュージックプレイヤー、イヤホン等。
(大半はスマホで事足りると思いますが)
音楽を聴く人はイヤホンを忘れると結構辛いはずです。忘れずに。
bluetoothの充電式であれば、充電ケーブルも忍ばせておきます。
モバイルバッテリーで充電できるUSB式であればベスト。

          • -

以上、随分と出張慣れしてしまった身からの小話でした。