miyupaca log

2020-08-29 yps学習記録その5〜追加課題編〜

2020/08/29

SQLまわりの追加課題が出ていたのでやっていきます。
テーブルの事前用意などがあるのでここまでのypsの課題を進めておくことが前提になっています。

かなり足踏みと失敗しながら試行錯誤しているので、追走する方は本家を参照くださいね。

やったこと

  • 課題1:リストに自分のデータを追加する&得点王にする
  • 課題2:二人いる酒井選手をフルネームにする
  • 課題3:登録した自分の選手データ以外を論理削除
  • 課題4:phpMyAdminの導入

今回のお品書き

最初に設定

まずはmysql cliにて日本語入力できるように設定。

$ sudo vi /etc/my.cnf

[client]
#default-character-set=utf8mb4

$ sudo systemctl restart mysqld

課題1:リストに自分のデータを追加する&得点王にする

INSERTする課題！

まずはmysql cliに入る。

$ mysql -u root -p -D worldcup2014db

mysql> show tables;
+--------------------------+
| Tables_in_worldcup2014db |
+--------------------------+
| countries                |
| goals                    |
| goals_tmp                |
| pairings                 |
| pairings_tmp             |
| players                  |
| players_tmp              |
| total_goals              |
+--------------------------+
8 rows in set (0.00 sec)

選手テーブルの構成を見て登録することを確認。

mysql> desc players;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field       | Type        | Null | Key | Default           | Extra                       |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id          | int(11)     | NO   | PRI | NULL              | auto_increment              |
| country_id  | int(11)     | YES  |     | NULL              |                             |
| uniform_num | int(11)     | YES  |     | NULL              |                             |
| position    | varchar(2)  | YES  |     | NULL              |                             |
| name        | varchar(50) | YES  |     | NULL              |                             |
| club        | varchar(50) | YES  |     | NULL              |                             |
| birth       | date        | YES  |     | NULL              |                             |
| height      | int(11)     | YES  |     | NULL              |                             |
| weight      | int(11)     | YES  |     | NULL              |                             |
| expired_at  | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| deleted_at  | timestamp   | YES  |     | NULL              |                             |
| updated_at  | timestamp   | YES  |     | NULL              |                             |
| created_at  | timestamp   | YES  |     | NULL              |                             |
+-------------+-------------+------+-----+-------------------+-----------------------------+
13 rows in set (0.00 sec)

イメージを掴むためにID1の選手を見てみる。

mysql> select * from players where id = 1;
+----+------------+-------------+----------+--------------------------+-----------------------------+------------+--------+--------+---------------------+------------+------------+------------+
| id | country_id | uniform_num | position | name                     | club                        | birth      | height | weight | expired_at          | deleted_at | updated_at | created_at |
+----+------------+-------------+----------+--------------------------+-----------------------------+------------+--------+--------+---------------------+------------+------------+------------+
|  1 |          1 |          12 | GK       | ジュリオセザール         | トロント（カナダ）          | 1979-09-03 |    186 |     79 | 2020-08-22 02:28:50 | NULL       | NULL       | NULL       |
+----+------------+-------------+----------+--------------------------+-----------------------------+------------+--------+--------+---------------------+------------+------------+------------+
1 row in set (0.00 sec)

国のIDを確認したいのでcountriesテーブルを見てみる。

mysql> select * from countries WHERE name = '日本';
+----+--------+---------+------------+---------------------+------------+------------+------------+
| id | name   | ranking | group_name | expired_at          | deleted_at | updated_at | created_at |
+----+--------+---------+------------+---------------------+------------+------------+------------+
| 12 | 日本   |      46 | C          | 2020-08-22 02:28:50 | NULL       | NULL       | NULL       |
+----+--------+---------+------------+---------------------+------------+------------+------------+
1 row in set (0.00 sec)

日本は12。確認できたので早速自分の選手データを登録。

mysql>  INSERT INTO players(country_id,uniform_num,position,name,club,birth,height,weight) VALUES(12,55,'FW','miyupaca','東京','2000-01-01',185,80);
Query OK, 1 row affected (0.00 sec)


mysql> select * from players where name = 'miyupaca';
+-----+------------+-------------+----------+----------+--------+------------+--------+--------+---------------------+------------+------------+------------+
| id  | country_id | uniform_num | position | name     | club   | birth      | height | weight | expired_at          | deleted_at | updated_at | created_at |
+-----+------------+-------------+----------+----------+--------+------------+--------+--------+---------------------+------------+------------+------------+
| 737 |         12 |          55 | FW       | miyupaca | 東京   | 2000-01-01 |    185 |     80 | 2020-08-29 14:48:31 | NULL       | NULL       | NULL       |
+-----+------------+-------------+----------+----------+--------+------------+--------+--------+---------------------+------------+------------+------------+
1 row in set (0.00 sec)

idが自動再番されて自分が無事登録されていることを確認。

あとは得点王にする作業。

mysql> INSERT INTO goals(player_id) VALUES(737);

最初これでいくら追加してもtotalgoalsの数は増えず。。。
試行錯誤していたら、goaltimesの設定もして初めて計上されるらしい。

mysql> INSERT INTO goals(player_id,goal_time) VALUES(737,'後半5分');

このように何度か追加してみたら登録選手のゴール数が増やせた。
(模範解答を見たら既にある他の選手のゴールデータを利用していた。なるほど。)

課題2:二人いる酒井選手をフルネームにする

UPDATEする課題！

登録されている酒井選手を確認してみる。

mysql> select name,club,birth from players where name like '酒井%';
+--------+--------------------------+------------+
| name   | club                     | birth      |
+--------+--------------------------+------------+
| 酒井   | ハノーバー               | 1990-04-12 |
| 酒井   | シュツットガルト         | 1991-03-14 |
+--------+--------------------------+------------+
2 rows in set (0.00 sec)

今回の失敗その1：最初、先の結果だけ見てハノーバー所属の選手を酒井宏樹にするとしたら酒井宏樹が３名になってしまったorz 

mysql> UPDATE players SET name = '酒井宏樹' WHERE club = 'ハノーバー';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from players where name like '酒井%';
+-----+------------+-------------+----------+--------------+--------------------------+------------+--------+--------+---------------------+------------+------------+------------+
| id  | country_id | uniform_num | position | name         | club                     | birth      | height | weight | expired_at          | deleted_at | updated_at | created_at |
+-----+------------+-------------+----------+--------------+--------------------------+------------+--------+--------+---------------------+------------+------------+------------+
| 243 |         11 |          13 | MF       | 酒井宏樹     | ハノーバー               | 1984-02-25 |    174 |     67 | 2020-08-29 15:19:17 | NULL       | NULL       | NULL       |
| 532 |         25 |          12 | GK       | 酒井宏樹     | ハノーバー               | 1989-02-12 |    188 |     83 | 2020-08-29 15:19:17 | NULL       | NULL       | NULL       |
| 723 |         12 |          21 | DF       | 酒井宏樹     | ハノーバー               | 1990-04-12 |    184 |     70 | 2020-08-29 15:19:17 | NULL       | NULL       | NULL       |
| 724 |         12 |           3 | DF       | 酒井         | シュツットガルト         | 1991-03-14 |    176 |     74 | 2020-08-22 02:28:50 | NULL       | NULL       | NULL       |
+-----+------------+-------------+----------+--------------+--------------------------+------------+--------+--------+---------------------+------------+------------+------------+
4 rows in set (0.00 sec)

所属選手まで酒井に更新してしまった。
その後元データを見て気合いで間違えて変更してしまった名前を元に戻し、IDを確認して確実に変更を加えた。

mysql> select * from players where name like '酒井%';
+-----+------------+-------------+----------+--------+--------------------------+------------+--------+--------+---------------------+------------+------------+------------+
| id  | country_id | uniform_num | position | name   | club                     | birth      | height | weight | expired_at          | deleted_at | updated_at | created_at |
+-----+------------+-------------+----------+--------+--------------------------+------------+--------+--------+---------------------+------------+------------+------------+
| 723 |         12 |          21 | DF       | 酒井   | ハノーバー               | 1990-04-12 |    184 |     70 | 2020-08-22 02:28:50 | NULL       | NULL       | NULL       |
| 724 |         12 |           3 | DF       | 酒井   | シュツットガルト         | 1991-03-14 |    176 |     74 | 2020-08-22 02:28:50 | NULL       | NULL       | NULL       |
+-----+------------+-------------+----------+--------+--------------------------+------------+--------+--------+---------------------+------------+------------+------------+
2 rows in set (0.00 sec)

mysql> UPDATE players SET name = '酒井宏樹' WHERE id = 723;
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE players SET name = '酒井高徳' WHERE id = 724;
Query OK, 1 row affected (0.00 sec)

変更の確認。

mysql>  select name,club,birth from players where name like '酒井%';
+--------------+--------------------------+------------+
| name         | club                     | birth      |
+--------------+--------------------------+------------+
| 酒井宏樹     | ハノーバー               | 1990-04-12 |
| 酒井高徳     | シュツットガルト         | 1991-03-14 |
+--------------+--------------------------+------------+
2 rows in set (0.00 sec)

課題3:登録した自分の選手データ以外を論理削除

DELETEする課題！…ではなかった。

論理削除というものを初めて知ったので調べてみる。

いわゆる普通の削除は「物理削除」でデータそのものがなくなるのに対して、「論理削除」はデータは残したまま削除したとみなす、存在がなくなったとみなすこと。
削除されてるというフラグを立てるイメージ。

[1分でわかる論理削除 -メリットとデメリットを考える- - Qiita] (https://qiita.com/wanko5296/items/f1af9c7bf020e867c2dd)

論理削除はテーブルに論理削除のフラグ値を格納する列が必要なので追加 。。。と思ったら既にあるdeleted_atカラムを使えばよかったらしい。 無駄に列追加をしてしまった(今回の失敗その2)
テーブル構成を元に戻して、モデルにソフトデリートの設定を加える。
[Laravelのソフトデリートについてメモ - Qiita] (https://qiita.com/4roro4/items/017b47b0b88835e689ec)

Country,Goals,Pearing,Playerそれぞれのモデルにソフトデリートの設定を加える。

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes; //ここ

class Country extends Model
{
    //
    use SoftDeletes; //ここ
    
    //
    protected $table = "countries";
    protected $dates = ["expired_at", "deleted_at", "updated_at", "created_at"];
}

Countryのモデル。全部で4つ分同じ設定を加える。

あとはplayerテーブルのレコードの自分の選手データ以外のdeleted_atカラムに日時を入れる。

mysql> UPDATE players SET deleted_at = NOW();
mysql> UPDATE players SET deleted_at = NULL WHERE id = 737;


mysql> select id,name,deleted_at from players where id = 1 OR id = 737;
+-----+--------------------------+---------------------+
| id  | name                     | deleted_at          |
+-----+--------------------------+---------------------+
|   1 | ジュリオセザール         | 2020-08-29 20:07:18 |
| 737 | miyupaca                 | NULL                |
+-----+--------------------------+---------------------+
2 rows in set (0.00 sec)

ここでブラウザ見てもエラーを吐かれた。原因がわからず、参考に先に学習進めていたメンバーのツイートをカンニングしていると、Playerモデルの中身を更新する必要があったらしい。こちら をコピーさせてもらう。
ソフトデリートを使う際にテーブル名を探しても見つからない状態になっていたらしい。 ここまで行い無事、論理削除した状態の自分だけ表示されたページが出てきた。

課題4:phpMyAdminの導入

GUIでSQLを触るためのもの。こちらは最初から本家のログを参考に進めた。

yum -y install --enablerepo=remi,remi-php73 phpMyAdmin

// ディレクトリ名変更
sudo mv /usr/share/phpMyAdmin /usr/share/pma

// シンボリックリンク
sudo ln -s /usr/share/pma/ /var/www/html/yps/public/pma

// sessionディレクトリのオーナー変更
sudo chown nginx:nginx /var/lib/php/session

IP制限の設定もしておく。(実はここで結構ハマった)

sudo vi /etc/nginx/conf.d/default.conf

===
location /設定したディレクトリ名 {
    root /usr/share/;
    index index.php index.html index.htm;

    allow 自分のIPアドレス;
    deny all;
}
===

再起動したら設定完了。

sudo systemctl restart php-fpm
sudo systemctl restart nginx

SQLもLaravelもわかってないところが多くてへろへろしながら、なんとかここまで。
特にSQLはへなちょこのままの言い訳ができなくなるので。。。復習復習m(_ _)m
お粗末様でした。

miyupacaの学習記録ブログです。