N予備校のプログラミング入門メモ16
はじめに
男子スキークロス見てたら開設の人のニーデラー推しにハマってしまい予定より遅い開始になりました。
4章 実践サーバーサイドプログラミング
10.RDBMS と SQL
PostgreSQL って「ポストグレ」だったのか「ポストグル」って読んでた。
sudo su - postgres
でpostgres
ユーザを開始してpsql
。以前やったのと同じ。
CREATE DATABASE diary_1;
でdiary_1
というDBを作成し、\c diary_1
で接続。
CREATE TABLE pages (write_date DATE PRIMARY KEY, body TEXT);
でpages
というテーブルを作成。ここでは列名、型、主キーを設定している。
この辺は以前の講座でsequelize? だったかっていうモジュールを使ってJSで定義を書いたのと同じことを直接やっている。
\dt
でテーブル一覧を表示する。何の略だろ。すこーしだけググったけど出てこなかった。
INSERT INTO pages (write_date, body) VALUES ('2016-02-22', '最初の日記');
でデータを挿入する。
SELECT body FROM pages;
でpages
から取得してbody
を表示する。SQL Serverなら触ったことあるので簡単なSELECT文ならたぶん分かる。細かい文法の差とかはあるんだろうけど。
複数のデータを入れたり主キーがダブるように入れたりして挙動を見た。
N予備校のプログラミング入門を始めるにあたって実はUS配列のキーボードも一緒に始めたのだけど、シングルクォーテーション(')の入力がEnterの隣にあっていつか誤ってEnter押しちゃうなーって思ってたらSQLのDELETE文で押しちゃってヒヤッとした。最後にセミコロン(;)を入力するまで実行されないという動きもこれで初めて確認できた。間違ってEnterした時の取り消し(セミコロンを入力してない状態を解除する)ってどうすればいいんだろうか。とりあえず明らかに誤った文法を入力してエラーにして回避した。
DELETE FROM pages WHERE write_date='2016-02-29';
してデータを消したりした。
SELECT * FROM pages WHERE body LIKE '%寝た%';
さっき簡単なSELECT文ならたぶん分かるって書いたけどLIKE句初めて見た(小声)。
DROP TABLE ${テーブル名};
でテーブル自体を削除する。
\q
でPostgreSQLのフロントエンドを終了。そのままexit
でpostgres
ユーザも終了する。
RDBのデータの定義を扱うものをDDL(Data Define Language)、テーブルのデータ自体を扱うものをDML(Data Manipulation Language)、DBをコントロールするものをDCL(Data Control Language)という。試験問題みたいだあ。
'UPDATE pages SET body='三月になった' WHERE write_date='2016-03-01';'で本文を更新した。
11.データモデリング
データモデリングのお話。非正規形であるテーブルを正規形に直したりする。今度ゆっくり読む。
psql diary2
でdiary2
に接続した状態でフロントエンドを開く。
CREATE TABLE users (userid SERIAL PRIMARY KEY, name VARCHAR(16), gender CHAR(1));
IDを1,2,3...とするためにuserid
をSERIAL
型で定義している。
CHAR
とVARCHAR
の違いは、CHAR
はスペースパディングすること。CHAR(5)
にa
を入れたら、a____
みたいにになる(アンダーバーはスペース)。
ALTER TABLE ${テーブル名} ADD ${追加する列の名前} ${データ型};
で既存のテーブルに列を追加できる。
12.テーブルの結合
SELECT * FROM diaries JOIN users ON diaries.userid = users.userid;
JOIN
句でテーブルを結合。
内部結合と外部結合がある。内部結合はどちらのテーブルにも存在したものを結果に含める。AND条件みたいな感じ。
INNER JOIN
と書くと、動作は同じだが内部結合であることが読み取りやすくなる。
外部結合は以下がある。どのテーブルを残すかの違い。 - 左外部結合(LEFT JOIN) - 右外部結合(RIGHT JOIN) - 完全外部結合(FULL JOIN)
SELECT id, body, comment FROM diaries LEFT JOIN comments ON diaries.id = comments.diary_id;
でdiaries
が残るような結果になる。さらにORDER BY
句を使ってソートする。
SELECT id, body, comment FROM diaries LEFT JOIN comments ON diaries.id = comments.diary_id ORDER BY id;
13.インデックス
RDBのパフォーマンスについて。
EXPLAIN ANALYZE SELECT * FROM scores WHERE score = 100;
のようにEXPLAIN ANALYZE
をつけると実行時間が計れる。
CREATE INDEX ON scores (score);
とするとscores
のscore
列にインデックスが作成されてscore
列に対する処理が早くなる。ほかの列も早くしたい場合は別途インデックスを作成する必要がある。
インデックスがない場合はシーケンシャルスキャン(1行ずつなめるスキャン)をしていたが、インデックスを作成するとすべてを調べる必要がなくなるので早くなる。
B木、二分探索木に似てるなと思って調べたら二分探索木→AVL木→B木のように改良されていった感じだった。
14.集計とソート
AVG``COUNT``MIN``MAX``SUM
等が使える。
GROUP BY
句でグループ化できる。以下のようにすると名前ごとに集計したデータが表示される。
SELECT name, MAX(stage), AVG(score), COUNT(score) FROM scores INNER JOIN users ON scores.user_id = users.user_id GROUP BY name;
数が10000未満のユーザだけ取得したい、という場合にWHERE
句にCOUNT(score) < 10000
というようには記述できない。WHRER
句は行についての条件を書く句であるため。
グループについての条件を書くHAVING
句を使う。
SELECT name, MAX(stage), AVG(score), COUNT(score) FROM scores INNER JOIN users ON scores.user_id = users.user_id GROUP BY name HAVING COUNT(score) < 10000;
15.「予定調整くん」の設計
- 要件を出す
- 用語を定義する
- 英語表記も用意するとコード書くときに便利
- URL設計
- ページ構成
- Web APIのURL設計
- モジュール設計
自分でアプリケーションを作る時にも参考になりそう。
最後にGitHubでアプリケーションを登録した。
16.認証の実装とテスト
Expressでひな形を準備。
express <project-name>
ディレクトリを移動して一連の作業。
echo "node_modules/" > .gitignore git init git add . git commit -am "first commit"
helmetをインストール。
yarn add helmet@1.1.0
app.js
にhelmetを読み込んでapp.use(helmet());
することで有効化する。
./routes
にRouterモジュールのファイル群を作成する(login.js
とかlogout.js
とか)
認証に必要なモジュールをインストール
yarn add passport@0.3.2 yarn add passport-github2@0.1.9 yarn add express-session@1.13.0
GitHub認証の実装は以前とほぼ同じ。
Routerオブジェクトをテストする。
yarn add mocha@3.2.0 --dev yarn add supertest@1.2.0 --dev yarn add passport-stub@1.1.1 --dev
supertest
はRouterオブジェクトをテストするモジュール、passport-stub
は認証のスタブ。
supertest
と作っているアプリ../app
を読み込んでテストを記述する。
'use strict'; const request = require('supertest'); const app = require('../app'); describe('/login', () => { it('ログインのためのリンクが含まれる', (done) => { request(app) .get('/login') .expect('Content-Type', 'text/html; charset=utf-8') .expect(/<a href="\/auth\/github"/) .expect(200, done); }); });
上記ではヘッダが含まれているか(文字列を2つ渡す)、GitHubへのリンクが含まれているか(正規表現を渡す)をテストして、最後にステータスコードとdone
関数を渡す。
認証に関するテストを書く。
const passportStub = require('passport-stub'); describe('/login', () => { before(() => { passportStub.install(app); passportStub.login({ username: 'testuser' }); }); after(() => { passportStub.logout(); passportStub.uninstall(app); }); // 中略 it('ログイン時はユーザー名が表示される', (done) => { request(app) .get('/login') .expect(/testuser/) .expect(200, done); }); });
before``after
関数はそれぞれdescribe
の前後に処理される。ここではスタブにアプリをインストールしてテストユーザでログイン、終了後にログアウトしてアプリをアンインストールしている。
17.ユーザーの保存
sequelizeモジュールを利用してDBを扱う。
yarn add sequelize@3.25.0 yarn add pg@6.1.0 yarn add pg-hstore@2.3.2
それからschedule_arranger
というDBを作成。
複数のモデルを別ファイルに記述したいのでsequelize読み込み用のmodels/sequelize-loader.js
を用意する。
'use strict'; const Sequelize = require('sequelize'); const sequelize = new Sequelize( 'postgres://postgres:postgres@localhost/schedule_arranger', { logging: true }); module.exports = { database: sequelize, Sequelize: Sequelize };
あとはひたすらモデルを作成するけどモデルの写経がなかなかしんどい。どういうときにインデックスを作成して、こんな場合にはインデックスを作成しなくてもよいみたいな話学びがある。
sequelizeはエンティティ同士の関係を定義しておくとテーブルを結合して取得ができるらしい。
// モデルの読み込み var User = require('./models/user'); var Schedule = require('./models/schedule'); var Availability = require('./models/availability'); var Candidate = require('./models/candidate'); var comment = require('./models/comment'); User.sync().then(() => { Schedule.belongsTo(User, {foreignKey: 'createdBy'}); Schedule.sync(); Comment.belongsTo(User, {foreignKey: 'userId'}); Comment.sync(); Availability.belongsTo(User, {foreignKey: 'userId'}); Candidate.sync().then(() => { Availability.belongsTo(Candidate, {foreignKey: 'candidateId'}); Availability.sync(); }); });
各モデルを読み込み、sync
関数でモデルに合わせてDBのテーブルを作成する。作成が終わった後に実行したい処理をthen
でつないでいる。なのでUserのテーブルを作成→外部キーを設定してテーブルを作成→外部キーを設定して……みたいな流れで各テーブルを作成している。
認証が実行されたときに呼び出される処理のところに以下を記述
process.nextTick(function () { User.upsert({ userId: profile.id, username: profile.username }).then(() => { done(null, profile); }); });
upsert
関数でUPDATE(無ければINSERT)を行う。これでユーザーを永続化できた。
ちょっと疲れてきたので休憩。