こんにちは。

記事を閲覧いただきありがとうございます。

プログラミングの勉強を始めてしばらく経ちましたが、SQLについてまともに学習したことがなかったため、復習がてら記事にしていきたいと思います。

学習に関してはドットインストールで行いました。

MySQLとは

○ オープンソースのデータベースシステム。

MySQLへ接続

○ MySQLの動作確認

$ sudo service mysql status

○ 以下のように表示で操作中

mysqld (pid  17137) を実行中...

○ rootユーザーでログイン

$ mysql -u root

○ 接続を終了

$ quit;

データベースの操作

○ データベースの作成

$ create database 'データベース名'

○ データベースの削除

$ drop database 'データベース名'

○ データベースの表示

$ show databases;

○ 操作対象のデータベース確認

select databese();

○ データベースを操作対象にする

$ use 'データベース名'；

作業用ユーザーの作成

○ まずルートユーザーでログイン

$ mysql -u root

以下コマンドで作業用ユーザー作成

＄　create user 'ユーザーネーム'@'どこからアクセスしてくるか？'　identified by 'パスワード';

全権限を与える

$ grant all on 'データベース名'.* to '作成したユーザー';

ルートユーザーから抜けて作業用ユーザーでログイン

$ mysql -u '作成したユーザー名'　-p

パスワード入力を求められるので入力すると接続できる

ユーザーの削除

$ drop user 'ユーザー名';

外部ファイルの実行

○ sqlファイルを作成

myapp.sql:

#該当のデータベースがあれば削除
drop database if exists 'データベース名';

#データベース作成
create database 'データベース名';

＃権限付与＆作業用ユーザーの作成
grant all on 'データベース名'.* '作成する作業用ユーザー'　identified by 'パスワード';

○ ファイルの実行
実行の方法は二つある

リダイレクションを使う方法

$ mysql -u root < 'ファイル名'

mySQLサーバにログインして行う方法
rootでログイン
source もしくは \. を使う

$ source ./'ファイル名'

テーブルの作成

○ 外部ファイルを作成

create_table.sql:

#テーブルの作成
create table 'テーブル名' (
  #'カラム名' 'データ型',
  id int unsigned,  　
  name varchar(20),　 
  score float,
);

○ データ型について

number ー 数値
  int           #整数
  float         #浮動小数点
  double        #浮動小数点より精度の高いデータ型
  int unsigned  # ＋ のみ使用

string ー 文字列
  char('文字数')　　 #固定長
  varchar（'文字数'）　#可変長　データによって長さが変わるもの
  text　　　　　　　  #可変長　どれくらいの長さになるかわからないもの

date/time ー 日時
  date
  time
  datatime

true/false ー 真偽値
  true = 1
  false = 0

テーブルにデータを挿入

テーブルにデータを挿入
insert into 'テーブル名' ('カラム名1','カラム名2') value ('値１',''値２)

テーブルの確認
＄ select * from 'テーブル名';


○ カラムに入力制限をかける

not null  #NUllを許さない
default   #デフォルトの値を指定
primary key #重複しない値にする　auto_incrementを付けると連番になる

例）
create table users (
  id int unsigned primary key auto_increment,
  name varchar(20) unique,
  score float default 0.0
);

結果：
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)      | YES  | UNI | NULL    |                |
| score | float            | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

○ カラムを後から追加

alter table 'テーブル名' add colmun '追加するカラム名' '型';

○ カラムの削除

alter table drop colmun 'カラム名';

○ 型の変更

alter table chage 'カラム名' '新しいカラム名' '型'；

まとめ

基本中の基本ですがSQLは覚えておく必要があるので引き続き勉強します！

• 2019/7/12 修正

Heroku にデプロイするまでにかなり時間がかかってしまったので，備忘録として。初心者なので，ミスや無駄があればコメントいただけるとありがたいです。

開発環境

• macOS Mojave 10.14.5

公式ドキュメント

1. Docker Documentation

2. Docker ドキュメント日本語化プロジェクト

3. Dockerfile のベストプラクティス

4. Heroku Dev Center

参考

1. 丁寧すぎる Docker-compose による rails + MySQL on Docker の環境構築

2. Docker で Rails5.2.1 と MySQL8 を動かす git プロジェクトを作る方法

3. Rails アプリで heroku を使うときの DB を MySQL に変更する

手順

1. インストール・新規登録

• 以下は，Homebrew, git, Docker desktopなどのインストール, GitHubの新規登録, Herokuの新規登録・クレジットカード登録などの初期作業を行っている前提とします

2. Dockerコンテナの作成・開始

• 次のシェルスクリプトを作成して実行
  • (7/10 修正) シェルスクリプトを分解して記事を書いていましたが，docker-compose upの後にエラーが発生したので，使用しているシェルスクリプトをそのまま掲載します。

docker1.sh

#!/bin/bash

# make Dockerfile
mkdir docker
cat <<'EOF' > ./docker/Dockerfile
FROM ruby:2.6.3
ENV LANG C.UTF-8
RUN apt-get update -qq \
  && apt-get install -y --no-install-recommends \
    build-essential \
    libpq-dev \
    nodejs \
  && rm -rf /var/lib/apt/lists/*
RUN gem install bundler
WORKDIR /tmp
COPY Gemfile Gemfile
COPY Gemfile.lock Gemfile.lock
RUN bundle install
ENV APP_HOME /my_app
RUN mkdir -p $APP_HOME
WORKDIR $APP_HOME
COPY . $APP_HOME
EOF

# make docker-compose.yml
cat <<'EOF' > docker-compose.yml
version: '3.2'
services:
  db:
    image: mysql:8.0.16
    command: mysqld --default-authentication-plugin=mysql_native_password
    volumes:
      - ./db/mysql_data:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: root
    ports:
      - "3306:3306"
  web:
    build:
      context: .
      dockerfile: ./docker/Dockerfile
    command: bundle exec rails s -p 3000 -b '0.0.0.0'
    volumes:
      - .:/my_app
    ports:
      - "3000:3000"
    links:
      - db
    tty: true
    stdin_open: true
EOF

# make Gemfile
cat <<'EOF' > Gemfile
source 'https://rubygems.org'
gem 'rails'
EOF

# make Gemfile.lock
touch Gemfile.lock

echo "docker pull ruby:2.6.3"
docker pull ruby:2.6.3

echo "docker pull mysql:8.0.16"
docker pull mysql:8.0.16

echo "docker-compose run web rails new . --force --database=mysql --skip-bundle"
docker-compose run web rails new . --force --database=mysql --skip-bundle

docker-compose build

# fix config/database.yml
echo "fix config/database.yml"
cat <<'EOF' > config/database.yml
default: &default
  adapter: mysql2
  encoding: utf8
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: <%= ENV.fetch("MYSQL_USERNAME", "root") %>
  password: <%= ENV.fetch("MYSQL_PASSWORD", "root") %>
  host: <%= ENV.fetch("MYSQL_HOST", "db") %>

development:
  <<: *default
  database: my_app_development

test:
  <<: *default
  database: my_app_test

production:
  <<: *default
  database: my_app_production
EOF

echo "docker-compose up"
docker-compose up

ターミナルで実行

bash docker1.sh

3. データベースの作成

• 「Command + N」などで新しいターミナルを開き，作業ディレクトリまで移動

ターミナルで実行

docker-compose exec web rails db:create

• この作業で「NoDatabaseError」が解消され，ブラウザからhttp://localhost:3000にアクセスすると，「Yay! You’re on Rails!」が見られる。

4. git の準備

ターミナルで実行

git init
git add .
git commit -m ”first commit”

5. Heroku の準備

• ブラウザから Heroku にログインしておく

ターミナルで実行

heroku login

• 適当なキーを押して，ログインボタンを押す

ターミナルで実行

heroku create <アプリ名>

• 補足

  • この作業で「Heroku」にアプリが作成されると同時に，「heroku」という git のリモートリポジトリが作成される。
  • <アプリ名>は自由。<>をターミナルに入力しないこと。
  • すでに使用されているアプリ名は使用できない。
  • アプリ名に「アルファベット大文字」は使えない。
  • アプリ名を入力しなくてもよい（その場合はランダムに決定される）

• データベースの設定を MySQL に変更する

ターミナルで実行

heroku addons:add cleardb
heroku config | grep CLEARDB_DATABASE_URL

• 表示されたmysql://〜〜〜の部分を取り出し，「mysql」を「mysql2」に変更
• 次のDATABASE_URL=の後にmysql2://〜〜〜を貼り付けたものをターミナルで実行

ターミナルで実行

heroku config:set DATABASE_URL=

【補足】
ブラウザからアプリを選択し，「Setting」の「Reveal Config Vars」のページから編集してもよい。
「CLEARDB_DATABASE_URL」の
「CLEARDB_DATABASE_URL」を「DATABASE_URL」
「mysql」を「mysql2」に変更したものを追加すればよい。

6. Heroku にデプロイ

ターミナルで実行

git push heroku master
heroku run rake db:migrate
heroku open

ここまでで最低限度の作業は終了です。お疲れ様でした。

7. おまけ

• 終了するときは，ブラウザを閉じるだけでなく，Heroku のログアウトと Docker コンテナの停止をしておくこと

ターミナルで実行

heroku logout
docker-compose down

• コマンドの変更点

dockerを使用しないときのコマンドの例

rails console
rails g controller home top
rails g model Post content:text
rake db:migrate

docker-composeコマンドの例

docker-compose exec web rails console
docker-compose exec web rails g controller home top
docker-compose exec web rails g model Post content:text
docker-compose exec web rake db:migrate

Rubyだけでmysqlに接続する

今までRailsの力に頼ってきたのでrubyだけでmysqlに実行する方法を知らなかった

まずシステムにgemを入れる

gem install mysql2

require 'mysql2'　//requireする

client = Mysql2::Client.new(host: "******", username: "******", password: '******', database: '******')

result = client.query("select call_duration from talk_histories")　//SQLここに書く

result.each do |r|
　puts r["call_duration"]
end

複数のモデルにまたがって存在しているモデルの扱い

1対多、多対多の関係性のあるモデルについて考えます。

本(book)モデルと
コメント(comment)モデルがあったとしましょう。

• 本に対してコメントをつけられる事ができます。
• 本に対してコメントは複数つける事ができます。

ここで、本の執筆者である
著者(author)モデルを考えてみましょう。

• 著者に対してコメントをつけられる事ができます。
• 著者に対してコメントは複数つける事ができます。

このとき、コメントモデルには外部キーが2つ出現しています。
book_id と author_id です。
この2つのキーが被ってしまう恐れがあるので
単純にテーブル結合をしてデータを取り出すことはできなくなります。

ポリモーフィック関連

上記のパターンになったときに、思いつかれる解決策があります。
それがポリモーフィック関連と言います。

単純に book_id と author_id を扱う場合、
コメントテーブルに列をそれぞれ作成すれば良いです。
しかし、他にもコメントがつけられるようなモデルが新たに作成されたらどうなるでしょうか。
例えば本だけでなく、映画、テレビ、などなど・・・。
それぞれ movie_id　や tv_id など、何かしらNULLの値が入ってしまう列を作ってしまうことになります。

それでは、コメントをつけるモデルのidと、その種類分けを定義した列、
comment_type と commented_id
を追加したらどうなるでしょうか。

ある行では

comment_type = book
commented_id = 2

また、ある行では

comment_type = author
commented_id = 3

というように、表す事ができるようになります。

デメリット

しかしポリモーフィック関連を使って設計すると
外部キーとして扱っている commented_id が、実際には外部キー宣言をせずして扱うことになってしまいます。外部キーは1つのテーブルにしか紐づける事ができないからです。
そのため、その外部キーがあるテーブルと紐づいたときに、値が存在しているのかを保証するための参照整合性制約を設ける事ができなくなります。

また、コメントテーブルのデータを取得する場合、
外部結合で複数のモデルを結合し、結合する必要があります。

ポリモーフィック関連を使うと、DBのでデータ制約を利用する事ができなくなるので
アプリケーション側で調整する必要があります。

Railsはこの問題に対応している

フレームワークによってはこの問題に対応しているものもあります。
例えばRailsでは、polymorphic属性を宣言する事で、1つのモデルを複数のモデルに紐づけて外部キーを設定する事ができます。

ポリモーフィック関連を使う場面

外部キーのように紐づけたいモデルが、複数存在し、かつ、どのようなモデルが現れうるのか、将来的に不透明であるという確度が高いときには利用せざるを得ないかもしれません。

ポリモーフィック関連の代替策

交差テーブルを作成する

最初の例を元に説明すると
books_comments テーブルと
authors_comments テーブルを作成する方法です。
交差テーブルが存在する事で、どのモデルを示すかを表していた comment_type を利用する必要が無くなります。

注意点としては、UNIQE制約を利用しないと、自然と多対多の関連づけが作成されてしまう事です。
また、両方の交差テーブルに同じ comment_idが存在しうるので、
アプリケーション側で対応する必要もあります。

共通の親テーブルを作成する

book , author, comment の全ての親となる reputation モデルを作成したとします。
book , author, comment の全てが外部キーとしてreputation_idという擬似キーを持たせる事で、コメントモデルを元に同じreputation_idに紐づいたbook、authorモデルのデータを引っ張る事が可能になります。逆も然りで、bookから同じreputation_idに紐づいたcommentデータを引っ張る事もできるようになります。
交差テーブルを作らなくても、擬似キーだけでデータの整合性制約を利用した設計をする事が可能になります。