はじめに

以前、２回に分けてSQLで使う単語をざっくりまとめていました。
一つにまとめます。
意味は自分がわかるように、大分ざっくりしているので、ご了承ください。

単語帳

単語	意味
SELECT	検索
UPDATE	更新
INSERT	挿入
DELETE	削除
FROM	テーブル指定
INTO	INSERT文でテーブル指定
VALUES	INSERT文で値を入力
SET	UPDATE文で列名と値を入力
WHERE	絞り込み条件(必ず真偽値)
AS	別名をつける
IS NULL	NULLかどうか判別
IS NOT NULL	NULLではないかどうか判別
LIKE	パターンマッチング
IN	いずれかに一致
NOT IN	どれとも一致しない
BETWEEN A AND B	AとBの間
ANY	いずれかと一致すれば真
ALL	全てと一致すれば真
A AND B	AかつB
A OR B	AかB
NOT A	Aではない
DISTINCT	重複を除外
ORDER BY	順番を変える
ASC	昇順
DESC	降順
UNION	和集合
EXCEPT	差集合
INTERSECT	積集合
CASE	条件と一致したら値を変換
LENGTH	文字数
SUM	合計値
AVG	平均値
MAX	最大値
MIN	最小値
COUNT	行数を数える
ROUND	四捨五入
TRUNC	切り捨て
POWER	冪乗
CURRENT_DATE	現在の日にち
CURRENT_TIME	現在の時刻
CAST	データ型を変換
TRIM	空白削除
REPLACE	指定文字を変換
CONCAT	文字列を連結
SUBSTRING	一部を抽出
COALESCE	最初に登場するNULLでない値を返す
GROUP BY	検索結果のグループ化
HAVING	集計結果に対して絞り込み条件
JOIN 〜 ON	テーブル結合
CREATE TABLE	テーブル作成
DROP TABLE	テーブル削除
DEFAULT	デフォルトの値指定
ALTER TABLE 〜 ADD	列の追加
ALTER TABLE 〜 DROP	列の削除
NOT NULL	NULLを入れない
UNIQUE	重複した値を入れない
CHECK	条件に合わない値を入れない
PRIMARY KEY	主キーに指定
REFERENCES	外部キーに指定
CREATE INDEX 〜 ON	インデックス作成
DROP INDEX	インデックス削除
BEGIN; 〜 COMMIT;	トランザクション
CREATE VIEW	ビュー作成
DROP VIEW	ビュー削除
AUTO_INCREMENT	自動連番
GRANT 〜 TO	権限付与
REVOKE 〜 FROM	権限剥奪

• PythonフレームワークFlaskとMySQLを利用したRESTful API(登録、取得のみ)の作成方法についてメモする。

準備

検証用MySQLコンテナ準備

• docker-composeを使用して、MySQLコンテナを起動する。
  • 以下のdocker-compose.ymlファイルの配置しているフォルダでdocker-compose upコマンドを実行する。
  • 127.0.0.1:3307でMySQLコンテナが起動する。

version: "3"
services:
  db:
    image: mysql:5.7
    container_name: db
    environment:
      MYSQL_ROOT_PASSWORD: rootpass
      MYSQL_DATABASE: sample_db
      MYSQL_USER: mysqluser
      MYSQL_PASSWORD: mysqlpass
    volumes:
      - ./db/data:/var/lib/mysql
    ports:
      - 3307:3307
    command: --port 3307

• 以下のSQLを上記DBに実行し、テスト用テーブルusersを作成する。

  CREATE TABLE `sample_db`.`users` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(45) NOT NULL,
    `email_address` VARCHAR(128) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `id_UNIQUE` (`id` ASC),
    UNIQUE INDEX `name_UNIQUE` (`name` ASC))
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8mb4;

モジュールインストール

pip install flask, SQLAlchemy, Flask-SQLAlchemy, marshmallow, marshmallow-sqlalchemy, flask-marshmallow, pymysql

DB接続設定

• 検証用MySQLコンテナとの接続設定

from flask import Flask, request, jsonify, make_response
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://mysqluser:mysqlpass@127.0.0.1:3307/sample_db'
db = SQLAlchemy(app)

モデル＋コントローラー

• 検証用コード全体app.py

from flask import Flask, request, jsonify, make_response
from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow
from marshmallow_sqlalchemy import ModelSchema
import json
app = Flask(__name__)
## DB接続設定 
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://mysqluser:mysqlpass@127.0.0.1:3307/sample_db'
db = SQLAlchemy(app)
ma = Marshmallow()

## モデル
class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(45), nullable=False)
    email_address = db.Column(db.String(128), nullable=True)

    def __repr__(self):
        return "<User(name='%s',email_address='%s')>" % (self.name, self.email_address)

    # ユーザー情報登録
    def registUser(user):
        record = User(
            name=user['name'],
            email_address=user['email_address'],
        )
        db.session.add(record)
        db.session.commit()
        return user

    # ユーザー情報取得
    def getUserByUserId(uid):
        user = User.query.get(uid)
        return user


class UserSchema(ma.ModelSchema):
    class Meta:
        model = User
        fields = ('id', 'name', 'email_address')

## コントローラー
# ユーザー情報登録
@app.route('/users/', methods=['POST'])
def registUser():
    reqData = json.dumps(request.json)
    userData = json.loads(reqData)
    user = User.registUser(userData)
    user_schema = UserSchema()
    return jsonify(user_schema.dump(user))

# ユーザー情報取得
@app.route('/users/<string:uid>/', methods=['GET'])
def getUser(uid):
    user = User.getUserByUserId(uid)
    user_schema = UserSchema()
    if user == {}:
        return jsonify({})
    return make_response(jsonify(user_schema.dump(user)))


if __name__ == "__main__":
    app.run(debug=True)

動作確認

• localhost:5000で起動する。

python app.py

ユーザー情報登録

リクエスト

POST /users/ HTTP/1.1
Host: localhost:5000
Content-Type: application/json
Content-Length: 70

{
    "name":"test3 user",
    "email_address":"test3@example.com"
}

レスポンス

{
    "email_address": "test3@example.com",
    "name": "test3 user"
}

ユーザー情報取得

リクエスト

GET /users/3/ HTTP/1.1
Host: localhost:5000
Content-Type: application/json

レスポンス

{
    "email_address": "test3@example.com",
    "id": 3,
    "name": "test3 user"
}

参考情報

• Create a CRUD Restful Service API using Flask + Mysql [in 7 minutes!]

概要

以前作成したRailsアプリを削除して、再び同じ名前で同様のアプリ作成しました。
そしてrails db:migrate実行後、ActiveRecord::PendingMigrationErrorが発生した。

結論から言うと、過去アプリのデータベースが残っており
作成したアプリ上のdb/migrateディレクトリ内のファイルをコマンドdb:migrateにて反映させようとした際、ファイルのバージョンが異なるためエラーが発生しました。

開発環境

・データベース：PostgreSQL
・railsのバージョン：6.1.3
・PC：macbook pro

エラー発生から解決までの流れ

1. 元々あったrailsアプリを削除する。(rails db:create,rails db migrate実行済み)
2. 元々あったrailsアプリと同じ名前のrailsアプリを再び作成する。(rails db:create実行済み)
3. rails db:migrateを実行後、rails sにてサーバを立ち上げてブラウザを見ると、ActiveRecord::PendingMigrationErrorが発生する。
4. rails db:versionを実行すると、terminal上のファイルとアプリmigrationファイル(db/migrate)のバージョンが異なる。
5. rails db:resetを実行してrails db:migrateを行うと、問題なくアプリケーションを実行できる。
   また、rails db:versionにてファイルを確認すると、terminal上とrailsアプリのmigrationファイルが一致していることが確認できた。
6. rails db:migrateを行ってアプリを起動してみると、問題なく動作した。

エラーが発生した原因

原因は私の理解不足によるもので、アプリファイルを削除すると一緒にデータベースも消すことができると思っていたためです。
(アプリファイルとデータベースは、同一の場所にあると思っていた）
実際は、下記ディレクトリにデータベースが保存されています。
また、データベース削除とversion確認コマンドは、下記になります。

PostgreSQL：… /usr/local/var/postgres 内
MySQL：… /usr/local/var/mysql 内

データベース削除：rails db:reset　(データベース内の情報は消えるので注意！)
データベースversion確認：rails db:version

最後に

正直なところ、データベースについてまだまだわかっていない点がたくさんあります。
もし間違っている点等がある場合、ご指摘頂けると大変ありがたいです。
最後まで読んで頂きありがとうございました。

laravelで、DBのカラムがfirst_nameとlast_nameに分かれている。
しかし検索キーワードはいろんなパターンに対応させるように検索させたい場合。
でも全文検索は使えない。

そんな時に役立つかもしれない記事です。

DBの例

id	last_name	fist_name
1	山田	太郎
2	田中	花子

ユーザーからの検索クエリ

1. 山田 太郎
2. 山田太郎
3. 山田
4. 太郎

という感じで、どんなパターンでも検索をヒットさせたい。
でも全文検索は使わない場合は、このように書けば解決できます。

解決コードのEuloquent Where節部分です

前提
usersには検索したいEuloquentモデルが入っているものとします。
keywordsには検索クエリが入っているものとします。

$users->Where(function ($query) use ($keywords) {
     $splited_keywords = extractKeywords($keywords);
     foreach($splited_keywords as $keyword){
        $query->orWhere('last_name', 'LIKE', "%{$keyword}%")
              ->orWhere('first_name', 'LIKE', "%{$keyword}%")
              ->orWhereRaw('CONCAT(last_name, "", first_name) LIKE ? ', '%' . $keyword . '%');
      }
});

という感じで、送られてきたkeywordを空白文字列で分割しながら、各列を検索し、かつ、CONCATで結合したカラムを検索します。どれかにHITすればOK。

空白文字列が含まれる配列を分割するのはこちらの記事を参考にしました。
https://qiita.com/mpyw/items/a704cb900dfda0fc0331

function extractKeywords(string $input, int $limit = -1): array
    {
        return array_values(array_unique(preg_split('/[\p{Z}\p{Cc}]++/u', $input, $limit, PREG_SPLIT_NO_EMPTY)));
    }

この記事があなたの生産性を少しでも上げることができたならば
LGTMボタンお願いします！

概要

データベースにテーブルを作成するためのマイグレーションですが、テーブルの構造を定義、更新、削除するためのコマンドがいくつもあります。
ここではmigrateコマンドとして一覧でまとめてみました。

migrateコマンド一覧

• マイグレーションファイルを作成する

$ php artisan make:migration create_テーブル名_table

• マイグレーションファイルを実行する

$ php artisan migrate

• migrationsテーブルを作成する

$ php artisan migrate:install

• マイグレーションを再実行してテーブルを再構築する。テーブルとデータの初期化

$ php artisan migrate:refresh

• すべてのマイグレーション操作を元に戻す（全削除）

$ php artisan migrate:reset

• 1つ前のマイグレーション操作した情報に戻す

$ php artisan migrate:rollback

• マイグレーションファイルと実行状態を確認できる

$ php artisan migrate:status

expressの環境設定

expressを用いたバックエンド環境を構築

npm install express-generator -g

webpackの環境設定

css style-loaderと
Bootstrapを動かすための必要なライブラリをインストール

npm i -D webpack webpack-cli css-loader style-loader autoprefixer
npm i -S bootstrap jquery popper.js

expressとwebpackの連動

参考：
Typescript + Express + Webpackで作る、node.jsサーバー環境構築入門

webpackのメモ

webpackの実行　＞＞　expressの起動の順番になるように
package.jsonの記述を追加
1行目：ファイル名に縛られたくない場合や、rootに置きたくないなどの場合にはwebpackコマンド実行時に場所を指定して実行
2行目：npm run startでwebpackのビルドが実行されつつ、そのファイルを元にnodeサーバーが立ち上がるように設定
（※webpackの実行時のアウトプットファイルの出力先の指定も必ずしておく）

  "scripts": {
    "build": "webpack --config webpack.config.js",
    "start": "npm run build && node ./bin/www"
  },

Dockerへの移行

アプリケーション側への環境としては準備が出来たので、一旦Dcokerへ移行を行う
アプリケーション側DockerFile

# node.js の環境変数を定義する
FROM node:12      　　　//イメージの指定
ENV TZ=Asia/Tokyo
ENV DEBUG=app:*
WORKDIR /app　　　　　　
//命令はワークディレクトリを設定します。 
//Dockerfile 内にてその後に続く RUN、CMD、ENTRYPOINT、COPY、ADD の各命令において利用することができます。 
//WORKDIR が存在しないときは生成されます。 これはたとえ、この後にワークディレクトリが利用されていなくても生成されます。

COPY ./ ./
//COPY <src>... <dest>
//COPY ["<src>",... "<dest>"] （パスにホワイトスペースを含む場合にこの書式が必要）
//COPY 命令は <src> からファイルやディレクトリを新たにコピーして、コンテナ内のファイルシステムのパス <dest> に追加します。

RUN npm install
EXPOSE 3000

Docker composeファイル

適用する前に
イメージをビルドする参照
build: サービスの構築または再構築

docker-compose up -d --build   
docker-compose up -d

メモ書きではあるが、
docker-compose コンテナの停止、ネットワーク・名前付きボリューム・コンテナイメージ・未定義サービスコンテナを削除
dockerの停止はこちらを活用している。
細かく理解が必要であるが、一部のボリュームを残していたりすると作業が増えたり、ゴミファイルが貯まるので、、、一旦、全リセットという意味で

docker-compose down --volumes --rmi all --remove-orphans

開発環境

• Ruby(3.0.0)
• Ruby on Rails(6.1.3)
• MySQL(8.2.3)

エラーになった背景

heroku run rails db:migrate

上記のコマンドを入力した時に、下記のエラーが発生。

Mysql2::Error::ConnectionError: Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

エラーの原因

HerokuのデフォルトのデータベースがPostgresqlであるため、MySQLを使う場合は下準備が必要であるため。

解決方法

ClearDBというアドオンを使う

heroku addons:create cleardb:ignite

上記のコマンドを入力します。
すると、ClearDBというアドオンを使うことができるようになります。

Mysqlへの接続

次に、Rails側でそのMySQLデータベースの情報を知る必要がありますが、Railsは

DATABASE_URL

という環境変数が設定されていると、その情報を使ってデータベースに接続するという仕様になっているため、

heroku config:add DATABASE_URL='mysql2://<ユーザー名>:<パスワード>@<ホスト名>/<データベース名>?reconnect=true'

でMySQLの情報を設定してあげると、その情報を使ってMySQLに接続できる、ということになります。