「Webサービスを（少しずつ）構築する」第3回

3. データベースとORM

道具

データベースエンジン
- アプリケーションとは別プログラムとして動作させることが多い
  - 可動させるサーバも別マシンを前提としている
  - ということは、アプリケーションからはネットワーク経由でアクセスすることが前提。
- 大別すると以下の２種類が使われている。
  - リレーショナルデータベース（RDBMS)。
  - noSQL と分類されるデータベース。KVS（Key-Value Store）の形のものが多い。
  両者の特徴や長所短所についてはここでは省略する（各自で調べておいて下さい）。
- ここでは、MySQLを使うことにする。
  - 「LAMP」の一角を占める（オープンソースでは）最も著名なデータベース
ORM（Object-Relational Mapper)
- Node.js から RDBにアクセスするためのライブラリが最低限必要。
  - もちろん自分でSQLを文字列として構成するコードを書いてもいいが（このときは mysql2 ライブラリなどを使うことになるだろう）かなり煩雑になるだろう。
- 「node.js ORM」で検索すると、最近は Sequelize のヒット率が高いようだ。これを使ってみよう。

話の流れ

MySQLの準備（管理者サイド；ここは説明するだけにとどめておく）
データベース（RDBMS用語としての）へのアクセスを確認
ORMのインストール
ORM経由でのデータの読み書き（のためのコードを書く）

1. MySQLの準備

（本章は管理者が行う作業）

インストール：
- サーバ(CentOSなどRedHat系のLinux)上で新たなアプリケーション（などのパッケージ）をインストールする時は、 yum コマンド（パッケージマネージャ）を（管理者権限で）使うことになる。
- 一般的には、
```
 sudo yum install mysql
```
  でいいのだが、ここでは、MySQLとほぼ互換の（かつ、Oracleの配下に入っていない）MariaDB を使うことにした（ので、その過程も参考のため記しておく）。
- CentOS7 では MariaDBが採用されているが CentOS6では未採用なので、リポジトリを指定してからの yumコマンドが必要になる。
- リポジトリ指定は、/etc/yum.repos.d/mariadb.repo ファイルで行う（yum のリポジトリ指定の標準的記法）。
  - このファイルに書くべき内容として、様々なサイトにサンプルが掲載されているが、それらは古い可能性があるので、現時点で有効な指定内容を生成させるようにしよう。
    
    MariaDBのオフィシャルサイトから、repo生成ページを見つけ出して右図のように選択したものをコピペで入力した。
  - その上で、
```
 sudo yum install MariaDB-devel MariaDB-client MariaDB-server –enablerepo=MariaDB
```
    - さっき作ったrepoファイルの名前を enablerepoオプションで指定している（大文字小文字の区別はないようだ）。
    - サーバークライアント開発用ツール（個別のパッケージになっている）の３つ（と、依存関係で自動的にインストールされるものも含めて）をインストールしている。
```
 runlevel      # => N 3 のような値を返すだろう
               #    この右側の 3 が現在のランレベル
 chkconfig -a
 # または
 chkconfig --list mysql
          
          
 sudo /etc/init.d/mysql start
               # これで起動される
 netstat -a | grep mysql
               # ポート3306（サービス名 mysql）
```
- （すでにお気づきかもしれないが）MariaDBは、ソフトウェア名（およびパッケージ名）はMariaDBだが、インストールしてしまうと、ほぼMySQLとして振る舞う。以下の解説でMySQLと書いた部分は（特記がなければ）MariaDBにも共通のものである。
設定
- 自動起動：
  
  ディレクトリ /etc/init.d/ に mysql というファイルが置かれていれば、この先、mysql（またはMariaDB）サーバは自動の対象になっている筈なので、特に何もしなくていい。 * 現在、サーバが動作しているかの確認は以下のようなコマンドで（一般ユーザでも）できる。
```
  ps aux | grep mysql
  netstat -an | grep 3306
```
- アクセス権、パスワード等：
  - MySQLはUnix/Linuxの本体のユーザ名とは別に独自でアカウント（ユーザ名/パスワード）を管理している。
  - MySQLの管理者のユーザ名もUnix/Linux本体と同じ root である。root の（MySQL用）パスワードも別途設定しておくことが必要になる（ここでは省略）。
    - ただし、Unix/Linuxのroot権限でmysqlクライアントに入ると、mysqlでのrootとしての操作は可能になる。
```
 sudo mysql
```
- パケットフィルター：
  - Linuxでは（CentOS6では）iptables という仕組みでパケットフィルタリングが行われている。
  - 外部から入ってきた 3306番portへのアクセスは、初期状態では REJECT（遮断）される。
  - 以下のように対処しよう。
```
 sudo vi /etc/sysconfig/iptables
```
    で編集し、以下のような行を適切な場所に挿入（似たような行を、キー操作 yy p で複製してから編集すればいい）。
```
   -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
```
    編集できたら、サービス iptables を再起動する。
```
 sudo /etc/init.d/iptables restart
```
- なお、MySQL, MariaDB に関するその他の設定は、/etc/my.conf で行われることになっている（必要に応じて調べながら対処していくことにしよう）。

データベースの作成と、権限委譲（mysqlの管理者）

   CREATE DATABASE db名 ;
   # のあと、権限委譲を行う。
   GRANT ALL PRIVILEGES ON db名.* TO ユーザ名@localhost IDENTIFIED BY 'パスワード' WITH GRANT OPTION ;
   GRANT ALL PRIVILEGES ON db名.* TO ユーザ名@% IDENTIFIED BY 'パスワード'  ;
   # 同じサーバ機上のクライアントからの接続のための
   パスワードと、
   # それ以外（の全部、を表すためにワイルドカード文字として
   #   % で表現している。シェルやコマンドプロンプトの
   #   * に相当するもの）のためのパスワード、
   # と２回に分けて入れる必要があるようだ。

なお、priviledes などというスペルの難しい単語も現れる（正しくその通りに入力しないとエラーになるのが SQLの厄介なところ）ので辛抱しながら作業しよう。
GRANT OPTION については、ここでは省略します。

2. （MySQL上の）データベースへのアクセス

以下の情報が必要になる
1. DBサーバ（の、ホスト名またはIPアドレス）
  - デフォルトでは localhost（127.0.0.1）が設定されることが多いが、
  - 我々は、ゼミのサーバを使いましょう。
  - アドレスは、（忘れた人は個別に問い合わせて下さい。このサイトは公開サイトなのでセキュリティに関わる情報はここに掲載することは控えます。）
2. データベース名
  - とりあえず各自の学籍番号（jは小文字）の名前のついたデータベースを設定しておきます
3. ユーザ名
  - MySQLのユーザ名は、Unix/Linuxでシェルを使うときのユーザ名とは別物で、MySQLが独自に管理する名前です。
  - でもここでは同じ名前にしておきます。
4. パスワード：
  
  データベースごとに、アクセス権設定ができます。
  - このユーザ名に対してはどんな権限が付与される、という細かい設定ができるようになっています（があんまり細かくすると煩雑になるので適度な細かさで使う必要がある）。
  - パスワードも個別に設定できます
    （パスワードはオフラインで通知します）。
  - grant all previlages することで、そのユーザに、そのデータベースに対する全権を委譲することが可能。

3.4. Sequelizeのインストールと利用

参考になるサイトが多数ある（たとえばここは「node.js ORM mysql」でググると上のほうに現れる）ので参照してみて下さい。

インストール
- 以下のインストール作業は、アプリケーションディレクトリで行うことを前提にしている
  （ので –save オプションをつけている）。
```
 npm install -g sequelize-cli
 npm install --save sequelize
```
- 次節後半 sequelize db:create あたりで発覚すると思うが、
  mysql2 ライブラリも必要になるだろう（これも手動インストール）。
```
 npm install mysql2
```
Sequeilzeの設定（コマンドを使って）
- 初期化：アプリケーションディレクトリで
```
 sequelize init
```
  - そこで生成されるディレクトリとその中身もチェックしておくようにしよう。
  - 既存の（express-generator が express コマンドにより作成した）ディレクトリ群に加えて、config, migration, models が作られている筈。
- 接続設定：
  - config/config.json に、データベースアクセスのための情報を以下のような書式で設定する。
```
"host": ... ,
"database": ... ,
"username": ... ,
"password": ... ,
```
    - この ... 部分に必要な情報を文字列として（ "" で囲んで）書き込む。
  - 上記の４項目は（名称から推察できる筈だが）前節の４項目に（この順で）対応している。
```
"dialect": mysql, 
```
  - ここはこのままでいい。それに加えて、
```
"operatorsAliases": false
```
    - この一行を入れておくといいようだ（参照記事）。
- データベース生成
```
 sequeilize db:create
```
  - この操作により、DBサーバ内で CREATE DATABASE（SQL文）が行われることになっている。
  - DBサーバへの接続確認のために一度これを実行しておくといい（接続失敗すると何かのエラーが表示されるだろう）。
  - ただし、ゼミのサーバでは（あるいは大抵の組織の運用形態で）、CREATE DATABASE は、DBサーバ管理者の権限（またはサーバOSのroot権限）で行うことが求められるので、
  - すでに作成済のDATABASEに対してこのコマンドを発行した時は、接続とDATABASEの存在確認だけ行われ、予め別の権限で CREATE DATABASE しておく（またはしてもらう）必要がある。特にエラーは発生しないようだ。
- モデル生成
```
 sequelize model:create --underscored ^
  --name memo ^
  --attributes "memo:string, date:date"
```
  - 上記のようなコマンド（行末のキャレット ^は継続行を表している）を入れると、
```
 Sequelize CLI [Node: 10.13.0, CLI: 5.3.0, ORM: 4.41.2]
        
 New model was created at [パス名] 
 New migration was created at [パス名] 
```
    のようなメッセージが出力され、２つのディレクトリが生成される（詳細はそこに置かれたファイルをざっと読んでみて下さい）。
    - この段階では、DBサーバへのアクセスは発生せず、アプリケーションディレクトリの中だけの準備だけが行われる。
  - データベースへの反映（マイグレート）
```
sequelize db:migrate
```
```
Sequelize CLI [Node: 10.13.0, CLI: 5.3.0, ORM: 4.41.2]
        
Loaded configuration file "config\config.json".
Using environment "development".
== 20181122234537-create-memo: migrating =======
== 20181122234537-create-memo: migrated (0.211s)
```
    このとき、DBサーバ上では、前項で定義したモデルに従って CREATE TABLE（SQL文）が実行されている。
Sequelizeを使うコード
- 方針の概略：
  - 現時点では１つのサーバアプリケーションの中で、このDBサーバへのアクセスが発生する route が１つだけ（routes/memo）。
    
    なので、下記のコードの変更はいずれも routes/memo.js の中で行えばいいだろう。
  - アプリケーションが多機能化して、複数の route で DBサーバを使うようならば、
    
    App.js （またはそこから読み込まれるソース・ファイル）に、下記のコードを移し、必要に応じてモジュール相互間でのデータの受け渡しを行う必要が出てくるだろう。
- 初期化：
```
 let db = require('../models/index')
 // このファイルはモデル生成に際して
 // 作成された index.js
 // （routes/index.js とは別）
 // プログラムの中ではsequelize の大元になる
 // オブジェクトを生成する。
```
  - これは先頭部。
  - NeDBを使うために書き込んであったコードはコメントアウト（左端に //)するか行ごと削除してもいい（この後の項目も同様）。
  - App.js から requireする時と、（ディレクトリの）１階層下の routes/memo.js からrequireする時とで、
    
    ファイルの指示の仕方が変わってくる（起点が違うので）ことに留意されたい。 * この例では routes が起点なので ../ から始まる。
- データ抽出：
```
function showdb(res) {
  db.memo.findAll({}).then((docs)=>{
    res.render('memo', {list:docs})})
}
```
  - データベースを表象するオブジェクト db を起点に、モデル名 memo （の名のついた属性、の値として設定されているオブジェクト）を経て、
  - findAll（NeDBでは find だったがメソッド名はライブラリによってこの程度違う）を呼ぶ。
  - findAll が掴んで来る抽出結果（オブジェクトの配列の形）に対してコールバックを呼ぶための書き方として、（Sequelizeでは）thenメソッド（次項でも述べる）があるので、これをメソッドチェーンの最後の段で呼ぶ。
  - この呼出によって、DBサーバに対して、SELECT * FROM memos; のようなSQL文が送られることになるだろう。
- データ追加：（router.post()関数の中に）
```
db.memo.create({
  date: new Date().toLocaleDateString(),
  memo: req.body.memo
}).then((created)=>{
  console.log(created)
})
```
  - 前項と同様、モデル名と同名のオブジェクト（ここでは memo）を経由し、 createメソッドでデータの追加ができる。
  - 現在のSequelizeでは、Promise という仕組みで非同期処理が記述できるようになっていて、 createメソッド（前項のfindAllでもそうだが）に無名関数を（ダブルアロー）を渡すのではなく、メソッドチェーンの後段で thenメソッドに無名関数を渡す書き方をする。
  - この呼出によって、DBサーバに対して、INSERT INTO memos (...) VALUES (...); のようなSQL文が送られることになるだろう。
- 並び替え：
  - findAll では、SQLの SELECT文に用意されている様々なオプションをJavaScriptのオブジェクトに包んで（第１引数）指定することができる。
  - データを指定順序で表示させるために、データ列を受け取ってからJavaScript側でソートするのではなく、
    
    SQLの ORDER BY 句で並び順を指定したほうが効率的。
```
 findAll({order: [['id','ASC']]})
```
  - findAll にこのようにオプションを渡してみよう。
  - order属性の値として与えるのは、タプルの配列（上の例では要素１つの配列だが）。並び替えに使う（データベースの）フィールド名（と、昇順降順の区別、をタプルにしたもの）を優先度順に与える。
    - ここで「タプル」と呼んだが、JavaScriptのデータ構造としては単なる２要素の配列なので、ブラケット [] で囲んである。
  - id というフィールドは Sequelizeが自動的に生成するレコード番号に相当するもの。生成順に大きくなるので、これで並び替えても機能的にはOKだろう。
  - もちろん date フィールドで整列させてもいい。
  - ‘ASC’ を指定すると（SQLのSELECT文のORDER BY句の文法に対応している）昇順（ascendant）を意味するので上から古い順にmemo項目が表示される。
    - 新しい順に表示させるなら ‘DESC’ （= Descendant 降順）を指定すればいい。

3.5 いくつかのトラブルとその対策

日本語データについて
- 実はMysql（およびMariaDB）では、（ネット界で重要な位置を占めるに至ってはいるがWebだけがRDBMSの用途ではないという立場なのかどうか）インストール直後の標準文字セットは Latin1 （要するに半角英数字等からなるASCII文字セットのことと思っていい）に設定されている。
  - そのことは、作ったデータベースやテーブルについて、
```
 SHOW CREATE DATABASE DB名 ;
 SHOW CREATE TABLE memos ;
```
    のように入れてみると確認できる（それぞれについて DEFAULT CHARACTER SET が UTF8 ではなく LATIN1 になっていれば日本語文字を受理しない）。
- 以下のように解決させよう。
  1. サーバの設定を変える（と。その後に作成するデータベースやテーブルはそのように扱われる）。
  設定ファイル（おそらく /etc/my.conf）に、以下のような行を追加する（システム管理者）。
```
  [mysqld]
  ...
  character-set-server=utf8 #mysqldセクションの末尾に追加
```
  1. デフォルト文字セットを使用してのデータベース/テーブル生成。
    - 前項1.の方法がすぐに使えない場合は、データベース生成時に
      
      CREATED DATABASE DB名 DEFAULT CHARACTER SET utf8 ;
    で作成する。
  2. すでに作成済のデータベースやテーブルについては、
```
ALTER DATABASE DB名 DEFAULT CHARACTER SET  utf8 ;

ALTER TABLE memos CONVERT TO CHARACTER SET  utf8 ;
```
    のように変更すればいい。
  - なお、こういった問題については、
    
    適切な検索でヒントが見つかるだろう。
    
    今回は「AWS RDSのMySQLの文字コードをutf-8に変更」、「mysqlで文字コードをutf8にセットする」を参考にした。
入力結果が画面にすぐに反映されない問題

（と、結果表示画面のURLの問題）
- おそらく Sequelize の動作がこのようになっているのだろうと推測されるが、INSERT INTO の直後に続けて SELECT * （いずれもSQL文）を発行した場合に、先のデータ挿入が出力に反映されないようだ。
- 間を置いてリクエストを出したり、間に COMMIT の動作を明示的に挟んだり、といった解決策もあるだろうが、
  
  ここでは、もう１つ、入力を実行した直後の画面（でアドレスバーに表示される）のURLが、 POSTメソッドでデータを受理するAPIのアドレスのまま残っているという問題も解決してしまおう。
- routes/memo.js の route.post(‘/add’) で、データをデータベースサーバに書き込む処理が終了したら、結果表示を自前で行わずに、代わりに
```
 res.redirect('/memo')
```
  で最初のURLに飛ばしてしまうことにする。
  - 前回、関数 showdb を作ってそれを２箇所から呼ぶ、という作り方をしたが、この関数自体も１箇所から呼ぶだけのものになるので、関数を解体して処理を該当する場所に展開しても大丈夫。
と、現時点で route/memo.js は以下のようなコードになる。
```
 var express = require('express');
 var router = express.Router();
 let db = require('../models/index');
 // Sequelize and mysql version

 router.get('/', (req, res, next)=> {
   db.memo.findAll({order: [['id','DESC']]}).then((docs)=>{
     res.render('memo', {list:docs})})
 });

 router.post('/add',(req,res)=>{
   // console.log(req.body.memo)
   db.memo.create({
     date: new Date().toLocaleDateString(),
     memo: req.body.memo
   }).then((created)=>{
      console.log(`new data ${created.dataValues.memo}`)
   })
   res.redirect('/memo')
 })
 module.exports = router;
```
アクセスできない問題
- 当ページで扱ったプログラムは、アプリケーションサーバとデータベースサーバが別マシンで動くというモデルで動作するもので、
- アプリケーションサーバからデータベースサーバへの接続が確保されていることが前提となる。
- ところが、大学のキャンパス内にあるPC上でアプリケーションを動作させた時は、学内LANのフィルタリングによって特定のサービス（HTTP SSH等）に向けた通信以外は遮断されてしまうので、
  
  学外のデータベース（たとえばポート3306）への通信ができない。
- 回避策として、HTTPやSSHを使ったトンネリングという技術もあるが、ここでは省略します（興味のある人は調べてみて下さい）。
- 当面は、（当ページの動作確認については）自宅のネットなど、学外の環境で行っていただき、
- 次のステップ（アプリケーションもゼミのサーバで動作させる）に進むことにしましょう。