やりたいこと

MySQLのTime型は

'-838:59:59' から '838:59:59'
にまでしか対応していません。

なので仮にDBに○○した時間をtime型で保存し、合計を取り出そうとすると上限に当たる可能性がありませす

集計方法

MySQLのTime_TO_SEC関数を使います。
その名の通りこれは、Timeを秒数に直してくれます。
秒数に直したものを合計し、それを取得します。

取得した秒数をPHPでHH:MMに整形してあげれば、上限を超えた状態になります。

TestController.php

public function summary()
{
    $user = User::selectRaw('SUM(TIME_TO_SEC('sleep_time')) as total_sleep_time')
      ->where('name','太郎')
      ->first();

    $time = $user->total_sleep_time;
    // HH:MM
    sprintf('%02d:%02d', ($time / 3600),($time / 60 % 60))
}

前回

前回の記事https://qiita.com/QiitaD/items/d605b07e849e3bec0722
で発生していたエラーだが、警告であるらしい。DBとの接続に成功していても出るようだ。

接続の解決法

MySQLにAndroidからアクセスする権限を追加することでうまくいった。

//Androidからアクセスするユーザ名とパスワード、AndroidのIPアドレスを指定。
create user 'username'@'localhost' identified by 'password';

//アクセス権限を追加
grant all privileges on *.* to 'username'@'localhost' with grant option;

flush privileges;

以下の記事が参考になる。
・ユーザ権限の追加
https://code.i-harness.com/ja-jp/q/17cd93
https://proengineer.internous.co.jp/content/columnfeature/6638

注意点

・Androidからアクセスするとき、ユーザ名をrootにしているとアクセスできない
・MySQLとAndroidは異なるアドレスなので、ループバックアドレスだとうまくいかない。（よくあるっぽい）

これらに気づかずかなり時間を取られたので、気を付けて頂きたい。

感想

解決は思ったより簡単だったが、かなり時間を取られた。記事を読まれた方々は気を付けて頂きたい。

MySQL

SELECT count(id),
       `date`,
       SUBDATE(`date`, WEEKDAY(`date`)) AS beginning
FROM test
GROUP BY beginning;

解説

WEEKDAY(date)

mysql> SELECT WEEKDAY('2008-02-03 22:23:00');
        -> 6
mysql> SELECT WEEKDAY('2007-11-06');
        -> 1

dateに対応する曜日インデックス(0 = Monday、1 = Tuesday、…6 = Sunday)を返します。

SUBDATE(expr,days)

-- 加算
mysql> SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
        -> '2007-12-02'

-- 減算
mysql> SELECT SUBDATE('2008-01-02 12:00:00', 31);
        -> '2007-12-02 12:00:00'

第1引数から第2引数分を、足したり引いたりします。
第2引数をINTERVAL形式で書くと加算し、数字だけ指定すると減算されます。

今回の場合は、WEEKDAY() の戻り値を渡しているので、dateから曜日インデックス分を引いています。

WEEKDAY('2020-01-13') -> 0 -> SUBDATE('2020-01-15', 0) -> '2020-01-13'
WEEKDAY('2020-01-16') -> 3 -> SUBDATE('2020-01-15', 3) -> '2020-01-13'
WEEKDAY('2020-01-19') -> 6 -> SUBDATE('2020-01-19', 6) -> '2020-01-13'

2020-01-13は月曜日のため、これでそれぞれの週の頭の日付けがわかります。
あとはGROUP BYで同じ日付をまとめればおしまいです。

参照：http://sqlfiddle.com/#!9/389f85/2

はじめに

業務で見かけたテーブルの結合を３つまとめてみました。

具体的な内容

大きく分けてLEFT JOIN（外部結合）、INNER JOIN（内部結合）、CROSS JOIN（クロス結合）の３つを紹介します。
今回利用するテーブルは下記のpostテーブルのカラム名「post_user_id 」とそれにカラム名「user_id」で紐づくuserテーブルになります。

userテーブル

user_id	user_name
1	Aさん
2	Bさん
3	Cさん

postテーブル

post_id	post_user_id	post_name
1	1	日記1
2	2	日記2
3	3	日記3
4	1	日記4
5		日記5

LEFT JOIN

LEFT JOINはFROM側のテーブルを軸に、紐づいていない（一致しない場合）もデータとして取得して結果を出力します。
今回の場合ですとpostテーブルのpost_idの5はuserテーブルと紐づいていませんが結果として、
userテーブルのuser_nameとuser_idがnullのまま出力されています。

select *
from sample_post
  Left join sample_user
  on sample_post.post_user_id = sample_user.user_id

結果

post_id	post_user_id	post_name	user_id	user_name
1	1	日記1	1	Aさん
2	2	日記2	2	Bさん
3	3	日記3	3	Cさん
4	1	日記4	1	Aさん
5		日記5

INNER JOIN

INNER JOINはON で指定した条件で絞り込んだ結果を出力します。
今回は「on sample_post.post_user_id = sample_user.user_id」でそれぞれの紐づくidがあるものだけを
絞り込み出力するため,post_idの5は出力されません。

select *
from sample_post
  Inner join sample_user
  on sample_post.post_user_id = sample_user.user_id

結果

post_id	post_user_id	post_name	user_id	user_name
1	1	日記1	1	Aさん
2	2	日記2	2	Bさん
3	3	日記3	3	Cさん
4	1	日記4	1	Aさん

CROSS JOIN

あまり見かけないですが、CROSS JOINは2つのテーブルを交差結合させてデータを取得します。
全てのカラムをかけ合わせ、すべての組み合わせ作成するイメージになります。
以下の例でwhere句で絞り込まない場合、全パターンの20レコードが結果として出力されますが、長くなりすぎるのでpost_idが5の場合に指定した場合の結果を記載しました。post_idが5のレコードが5つ出来上がり、それぞれ異なるレコードになっているのがわかります。

select *
from sample_post
 CROSS JOIN  sample_user
 where sample_post.post_id = 5

結果

post_id	post_user_id	post_name	user_id	user_name
5		日記5	1	Aさん
5		日記5	2	Bさん
5		日記5	3	Cさん
5		日記5

終わりに

joinにもさまざまな種類があることがわかりました。
用途に分けて使い分けっていきたいですね。（個人的にはCROSS JOINの面白い使い方を考えたいところです。）
訂正点などあればご指摘いただけると幸いです。

はじめに

Rails 6 に追加された新機能を試す第116段。 今回は、MySQL データベース存在チェック 編です。
Rails 6 では、MySQLのデータベースを存在するかどうかをチェックする方法が少し変わりました。
データベースが存在しないときに、 bin/rails db:migrate を実行した場合、 MySQLのエラーメッセージが英語以外でも、 ActiveRecord::NoDatabaseError が発生するようになりました。

Ruby 2.6.5, Rails 6.0.2.1, Rails 5.2.4.1 MySQL 8.0.16 で確認しました。 (Rails 6.0.0 でこの修正が入っています。)

$ rails --version
Rails 6.0.2.1

今回は、MySQL のエラーメッセージを ja_JP にして起動して、 bin/rails db:migrate コマンドを使って Rails 6.0.2.1 と Rails 5.2.4.1 の違いを確認してみます。

今回、MySQL のエラーメッセージを日本語に切り変えるために mysqld コマンドに --lc_messages_dir=/usr/share/mysql-8.0 --lc_messages=ja_JP オプションを追加しています。

MySQL 側で直接、データベースが存在しないことを確認する

mysql コマンドでデータベースが存在しないことを確認します。
ここで、メッセージの先頭が 1049 となっていることに注意してください。

$ mysql -u root app_development
ERROR 1049 (42000): 'app_development' は不明なデータベースです。

db:migrate を実行する

エラーを確認するために、 db:create しないで db:migrate を実行すると ActiveRecord::NoDatabaseError が発生します。

$ bin/rails db:migrate
rails aborted!
ActiveRecord::NoDatabaseError: 'app_development' は不明なデータベースです。
/usr/local/bundle/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/mysql2_adapter.rb:28:in `rescue in mysql2_connection'

ちなみに MySQL のエラーメッセージが英語の場合も ActiveRecord::NoDatabaseError となります。

$ bin/rails db:migrate
rails aborted!
ActiveRecord::NoDatabaseError: Unknown database 'app_development'
/usr/local/bundle/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/mysql2_adapter.rb:28:in `rescue in mysql2_connection'

Rails 5 では

MySQL のエラーメッセージが日本語の場合は、 Mysql2::Error となります。 英語の場合は、 ActiveRecord::NoDatabaseError になります。

日本語の場合：

$ bin/rails db:migrate
rails aborted!
Mysql2::Error: 'app_development' は不明なデータベースです。
/usr/local/bundle/gems/mysql2-0.5.2/lib/mysql2/client.rb:90:in `connect'

英語の場合

$ bin/rails db:migrate
rails aborted!
ActiveRecord::NoDatabaseError: Unknown database 'app_development'
/usr/local/bundle/gems/activerecord-5.2.4.1/lib/active_record/connection_adapters/mysql2_adapter.rb:26:in `rescue in mysql2_connection'

何が変わったのか

Rails 5 までは、 エラーメッセージに Unknown database が含まれた場合に ActiveRecord::NoDatabaseError を発生させていました。
Rails 6 では、 エラーメッセージではなく、エラーの番号が 1049 (最初に mysqlコマンドでデータベースが存在しないことを確認したときの値)であるときに、 ActiveRecord::NoDatabaseError を発生させるようにしています。

試したソース

https://github.com/suketa/rails_sandbox/tree/try116_mysql_locale

参考情報

• MySQL: Check error number instead of a message
• MySQL 10.12 Setting the Error Message Language

Group Byしたらエラーになった

akb_controller.rb

class AkbController < ApplicationController
  def index
    @idol = Akb.group(:area)
  end
end

# 実行されたSQL
SELECT `akb`.* FROM `akb` GROUP BY `kakaku_products`.`area`

# コンソールに出力されたエラー
Mysql2::Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'akimoto.akb.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因はMySQL5.7からデフォルトになったONLY_FULL_GROUP_BY

GROUP BYで使うカラムはSELECTでちゃんと指定しましょうよ。ということらしい。
参考：MySQL5.7にアップデートしたらonly_full_group_byでエラーになった

ということで修正。

hoge.rb

class AkbController < ApplicationController
  def index
    # .select(:area)を追加
    @idol = Akb.group(:area).select(:area) 
  end
end

なんだか冗長だ。

mysqlを使った簡易的な掲示板を作りました。

概要

・ID、名前、コメント、タイムスタンプを掲示板に表示する。
・名前かコメントが未入力だとエラーメッセージがポップアップする。

苦労したところ

・DBに格納したデータを取り出す方法がわからなかった。
最初は配列で別々にID、名前、コメント、タイムスタンプを取り出そうとしたが上手くできなかった。
ArrayListならインスタンスまるごと格納できる！

・ArrayListの中身をjspで取り出す方法がわからなかった。
EL式で取り出そうとしたが上手くできなかった。
JSTLのcoreタグライブラリでforEachを使って取り出すことができた。

・mysql接続時にtime zoneエラーが出る。
下記を参考に接続URLを変更。日本時間にしたい場合は末尾をJSTに変更する。
Grails 3 + MySQLでrun-app時に「The server time zone value」でエラーが出る場合の対処

ソースコード

model

Board.java

package model;

import java.io.Serializable;
import java.sql.Timestamp;

public class Board implements Serializable {
    private int id;
    private String name;
    private String comment;
    private Timestamp time;


    public Timestamp getTime() {
        return time;
    }
    public void setTime(Timestamp time) {
        this.time = time;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getComment() {
        return comment;
    }
    public void setComment(String comment) {
        this.comment = comment;
    }

}

FindCommentDAO.java

package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class FindCommentDAO {

    public List<Board> findcomment() {

        // id,name,commentを格納するリスト
        List<Board> list = new ArrayList<>();

        final String jdbcId = "id";
        final String jdbcPass = "password";
        final String jdbcUrl = "jdbc:mysql://localhost:3306/dbname?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=JST";

        Connection con = null;

        try {

            con = DriverManager.getConnection(jdbcUrl, jdbcId, jdbcPass);

            System.out.println("Connected....");

            try {
                Statement st = con.createStatement();
                String sql = "select * from board";

                try {
                    // sqlを送信
                    ResultSet rs = st.executeQuery(sql);

                    while (rs.next()) {
                        // DBから取り出したid,name,commentをJavaBeansにset
                        Board bo = new Board();
                        bo.setId(rs.getInt("id"));
                        bo.setName(rs.getString("name"));
                        bo.setComment(rs.getString("comment"));
                        bo.setTime(rs.getTimestamp("time"));

                        // リストに1個ずつ格納。末尾に要素が追加されていく。
                        list.add(bo);
                    }

                    rs.close();
                    st.close();
                    con.close();

                } catch (SQLException e) {
                    e.printStackTrace();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                // データベース接続の切断
                if (con != null) {
                    try {
                        con.close();

                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Connection Failed.");
            return null;
        }
        return list;

    }

}

FindCommentLogic.java

package model;

import java.util.List;

public class FindCommentLogic {
    public List<Board> executeFindComment() {
        FindCommentDAO fcdao = new FindCommentDAO();
        List<Board> list = fcdao.findcomment();
        return list;
    }

}

AddCommentDAO.java

package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class AddCommentDAO {

    // DBにid,name,commentを加えるメソッド
    public AddCommentDAO(Board bo) {

        if(bo.getName().isEmpty()) {
            bo.setName( "名無し");
        }
        if(bo.getComment().isEmpty()) {
            bo.setComment( "コメント無し");
        }


        final String jdbcId = "id";
        final String jdbcPass = "password";
        final String jdbcUrl = "jdbc:mysql://localhost:3306/dbname?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=JST";

        Connection con = null;

        try {

            con = DriverManager.getConnection(jdbcUrl, jdbcId, jdbcPass);

            System.out.println("Connected....");

            try {

                PreparedStatement ps = con.prepareStatement("INSERT INTO board (name, comment) VALUES (?, ?)");

                ps.setString(1, bo.getName());
                ps.setString(2, bo.getComment());

                // ひな形を送信
                int r = ps.executeUpdate();

                if (r != 0) {
                    System.out.println(r + "件の書き込みを追加しました。");
                } else {
                    System.out.println("書き込みできませんでした。");
                }

                ps.close();
                con.close();

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                // データベース接続の切断
                if (con != null) {
                    try {
                        con.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        } catch (SQLException e) {

            e.printStackTrace();
            System.out.println("Connection Failed.");

        }

    }

}

AddCommentLogic

package model;

public class AddCommentLogic {
    public void executeAddComment(Board bo) {
        AddCommentDAO acdao = new AddCommentDAO(bo);
    }
}

view

main.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">


<script type="text/javascript">

function check(){
    var flag = 0;

    if(document.form1.name.value == ""){
        flag = 1;
    }
    else if(document.form1.comment.value == ""){
        flag = 1;
    }

    if(flag){
        window.alert('名前とコメントを入力してください'); 
        return false; // 送信を中止
    }
    else{
        return true; // 送信を実行
    }
}
</script>

<title>掲示板</title>
</head>
<body>
<form action="/board/BoardServlet" method="post" name="form1" onSubmit="return check()">
<p>名前:<input type="text" name="name"></p>
<p>コメント:<br>
<textarea name="comment" rows="5" cols="40"></textarea>
</p>
<p><input type="submit" value="送信"><input type="reset" value="リセット">
</p>
</form>

<c:forEach var="list" items="${listAttribute}">
<p>ID:<c:out value="${list.id}"/>　名前:<c:out value="${list.name}"/>　日付:<c:out value="${list.time}"/><br>
<c:out value="${list.comment}"/></p>
</c:forEach>

</body>
</html>

controller

BoardServlet.java

package servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import model.AddCommentLogic;
import model.Board;
import model.FindCommentLogic;

/**
 * Servlet implementation class BoardServlet
 */
@WebServlet("/BoardServlet")
public class BoardServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public BoardServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        //リスナークラスに移動したい
        request.setCharacterEncoding("UTF-8");

        // 既存のコメントを確認
        FindCommentLogic fcl = new FindCommentLogic();
        List<Board> list = fcl.executeFindComment();

        // セッションスコープにコメントリストを保存
        HttpSession session = request.getSession();
        session.setAttribute("listAttribute", list);

        RequestDispatcher rd =request.getRequestDispatcher("/WEB-INF/jsp/main.jsp");
        rd.forward(request, response);
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        request.setCharacterEncoding("UTF-8");

        // 入力された値を取得
        String name = request.getParameter("name");
        String comment = request.getParameter("comment");

        //JavaBeansに格納
        Board bo = new Board();
        bo.setName(name);
        bo.setComment(comment);

        // mysqlに格納
        AddCommentLogic acl = new AddCommentLogic();
        acl.executeAddComment(bo);

        // 今入力されたコメントと既存のコメントをmysqlから取得
        FindCommentLogic fcl = new FindCommentLogic();
        List<Board> list = fcl.executeFindComment();

        // セッションスコープにコメントリストを保存
        HttpSession session = request.getSession();
        session.setAttribute("listAttribute", list);

        RequestDispatcher rd =request.getRequestDispatcher("/WEB-INF/jsp/main.jsp");
        rd.forward(request, response);

    }

}

はじめに

Rails アプリケーションを Elastic Beanstalk を使ってデプロイするときにハマって時間がかかってしまったことを箇条書きにしてみました。
"Elastic Beanstalkはデプロイがすぐできる。"なんて嘘だーと一瞬思いましたが、ポイントさえ押さえておけば、本当に一瞬でデプロイできるようになりますので、ぜひ使ってみてください。

関連リンク

Elastic Beanstalk 関連のリンクを下記に載せておくので、必要であれば参考にしてください。。

• ElasticBeanstalk Blue-Green Deployment
  • https://qiita.com/syukan3/items/28eba7462c26409773e6
• AWS ElasticBeanstalk 環境を切り替える方法（EB CLI）
  • https://qiita.com/syukan3/items/1a44c879ab44e45ca748

Elastic Beanstalk

ハマったところ

• 文字コード（日本語の場合、utf8 へ変更必要）
  • RDSに作成したMySqlのDatabaseに日本語が登録出来ない問題 - Qiita
• セキュリティグループ (Mysql2::Error: Can't connect to MySQL server on '**********************' (4))
  • EC2 と RDS を別のVPC/サブネット上に置く方法
  • RDS のセキュリティグループに EC2 からのアクセスを許可する。

• rails db:createをやってくれない？
  (Mysql2::Error: Unknown Databese'********')

  • 下記コマンドにて、自分でMySQLに接続して、DB作成。
  • MySQL への接続（EC2上で（eb ssh））
    • mysql -h ****MySQLのエンドポイント？（RDS）***** -P 3306 -u sakaes -p
  • DB作成
    • create database **************;

• initializers/carrierwave.rb 用に beanstalk へS3設定を追記

まとめ

ただエラーを時系列に羅列しただけになってしまいましたが、同じような羅列された情報に自分が助けられたことがあるので、
困っている他の方の役に立てばと思い投稿させていただきました。