はかせだけど博士じゃない

無職が就活しないでプログラミングとかする

N予備校のプログラミング入門メモ16

はじめに

男子スキークロス見てたら開設の人のニーデラー推しにハマってしまい予定より遅い開始になりました。

4章 実践サーバーサイドプログラミング

10.RDBMSSQL

PostgreSQL って「ポストグレ」だったのか「ポストグル」って読んでた。

sudo su - postgrespostgresユーザを開始して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 ${テーブル名};でテーブル自体を削除する。

\qPostgreSQLのフロントエンドを終了。そのままexitpostgresユーザも終了する。

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 diary2diary2に接続した状態でフロントエンドを開く。

CREATE TABLE users (userid SERIAL PRIMARY KEY, name VARCHAR(16), gender CHAR(1));IDを1,2,3...とするためにuseridSERIAL型で定義している。

CHARVARCHARの違いは、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);とするとscoresscore列にインデックスが作成されて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)を行う。これでユーザーを永続化できた。

ちょっと疲れてきたので休憩。