hakatashi/sig-web-03.md

## sig-web-03.md

      
    Raw
  

              sig-web-03.md
            
          
    TSG 第3回Web分科会 カンペ

データベースの初歩に触れ、簡単なWebアプリケーションの制作を体験する。
MySQLのインストール

Windows

XAMPPをインストールする。
ちなみに最近のXAMPPだとMySQLという名前のMariaDBがインストールされるが、特に問題ない。
OS X

XAMPP, MAMP, もしくは
brew install mysql

Ubuntu, Debian

apt-get install mysql-server

CentOS

yum install mysql-sever

その他

がんばれ
MySQLの起動

XAMPP, MAMP

「XAMPP Control Panel」を立ち上げる。
MySQLという欄の横にある「Start」ボタンをクリック。
Macだと若干手順が違うかもしれない。
OS X

mysql.server start

Ubuntu, Debian Wheezy

service mysql start

Debian Jessie, CentOS

systemctl start mysql

その他

がんばれ
Hello, World!

ターミナルを立ち上げて以下のコマンドを打ち、
+---------------+
| Hello, World! |
+---------------+
| Hello, World! |
+---------------+

みたいなのが表示されれば成功。
XAMPP on Windows

PATH=C:\xampp\mysql\bin;%PATH%
mysql -u root -e "SELECT 'Hello, World!'"

XAMPP on OS X

export PATH=$PATH:/Applications/XAMPP/xamppfiles/bin
mysql -u root -e "SELECT 'Hello, World!'"

その他

mysql -u root -e "SELECT 'Hello, World!'"

MySQLとは

MySQLは、関連データベースの一種である。
「関連」データベースという概念を理解するのは理論的背景を知らないと難しいが、要はウェブアプリケーションなどで用いられるデータを、保存し、管理し、取り出す仕組みだと思ってよい。
MySQLはデータを単なる「ファイル」として保存するだけではない。もちろんデータを永続的に保存するためにディスクにデータを書き込むのだが、それだけではなく、メモリ上にデータを保持しておき、適宜それを整理したり勘定したりすることによってアプリケーションから高速に読み書きできる仕組みになっている。なのでMySQLはアプリケーションを動かす間常時起動しておく必要がある。
MySQLのデータは以下のような構造になっている。

「サーバー」: 起動されたMySQLのプロセスの1つ1つ。通常1マシンに1つのサーバーを置く。
「データベース」: サーバーの下に置かれる、アプリケーションが管理する一連のデータ。通常1つのアプリケーションにつき1つのデータベースを使用する。
「テーブル」: データベースの下に置かれる、1「種類」のデータを管理・保存する記録単位。
「レコード」: テーブルに記録される、1件1件のデータ。

SQL文

MySQLなどのSQL系のデータベースは、SQLと呼ばれる言語のテキストで操作する。
例えば、appという名前のデータベースを作るときには、以下のようなSQL文を実行する。
CREATE DATABASE app;
MySQLに限らず、データベースに対して実行されるこのようなテキストを「クエリ」と呼ぶ。
大文字になっている部分は、他のブログラミング言語で言うところの「予約語」である。見た目をわかりやすくするために大文字で書くことが多いが、別に小文字でも構わない。
MySQLサーバーに対してSQL文を実行する方法はたくさんあるが、一番簡単なのはmysqlコマンドを使う方法である。
先ほど Hello, World! でやったような、コマンドライン引数から直接打ち込む方法の他に、対話環境(REPL)を利用する方法がある。
$ mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.7.11 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> SELECT 'Hello, World!';
+---------------+
| Hello, World! |
+---------------+
| Hello, World! |
+---------------+
1 row in set (0.00 sec)
この対話環境を使って、今後の作業に必要なデータベースを作ってみる。
CREATE DATABASE app CHARACTER SET utf8;

USE app;

CREATE TABLE messages (name VARCHAR(255), body TEXT, time DATETIME);
これでappデータベースと、その下のmessagesテーブルが生成される。
テーブルはそれぞれのレコードが持つべきデータとして、いくつかのフィールドをあらかじめ決めておく必要がある。これらのフィールドを「カラム」と呼び、このようなテーブルの構造情報のことを「スキーマ」と呼ぶ。
今回作成したmessagesテーブルには、id, name, body, timeの4つのカラムが存在する。
試しにここにデータを突っ込んでみる。
SELECT * FROM messages;

INSERT INTO messages (name, body, time) VALUES ('user1', 'nice to meet you', '2016-05-25 19:00:00');
INSERT INTO messages (name, body, time) VALUES ('user2', 'fuck you', '2016-05-25 19:20:00');

SELECT * FROM messages;
という感じである。SQL文は言語と呼ぶだけあってクエリレベルで非常に複雑なことができるのが特徴であり、文の中で取り出すレコードを絞ったり、並び替えたり、計算したり、他のテーブルを結合したりできる。
近年はこのような多機能すぎるクエリ設計に対する反発としてNoSQLという種類のデータベースが流行りを見せているが、まずはSQLを勉強するのが筋であると思われる。
相変わらずこの講義はMySQL分科会ではなくWeb分科会なのでMySQLの細かい文法には触れないが、困ったときは下のようなサイトで学習するとよいだろう。

MySQL 5.6 リファレンスマニュアル 第3章 チュートリアル: 公式リファレンスのチュートリアル。翻訳が微妙であまり分かりやすいとは思えないが、最も信頼できる文書なので目を通しても良いと思う。
DBOnline - MySQLの使い方: 初心者向けに構成されたMySQL入門の鉄板。特にWindowsユーザーのサポートが充実しており、実例とスクリーンショットを豊富に取り入れたコースは非常に分かりやすい。基本的事項はこれで一通り身につく。
atmarkIT - 今から始めるMySQL入門

また、MySQLは関係データベースという理論的バックアップにもとづいて実装されたデータベースである。この理論を正しく理解するためには以下のような文献を参照するとよい。

データベース実践講義: 関係データベース理論を詳しく解説した古典的名著。
情報処理教科書 データベーススペシャリスト: デスペと呼ばれる資格の教科書。読んだらついでに資格も取ろう。
理論から学ぶデータベース実践入門: 最近出た本。関係データベース理論の基礎を実践的に学習できる。

作ってみよう

今回は、PHPからデータベースにアクセスして、Twitterのようにメッセージを投稿して時間順に閲覧することができるサイトを作ってみる。
まずは、ウェブページの見た目を整える手間を抑えるため、HTML5 Boilerplate を使ったテンプレートを利用する。


Initializr.comにアクセスし、「Bootstrap」をクリック。オプションはいじらずに「Download it!」をクリックしてZIPファイルをダウンロードする。


ダウンロードしたZIPファイルを解凍して、initializrフォルダの中身を前回触ったhtdocsフォルダの中にまるごと移動する。前回作ったファイルはどこかに退避させておくと良い。


index.htmlをindex.phpにリネームする。


http://localhost/にアクセスして、Hello, World! と書かれたウェブサイトが見れたら成功。
データを取得する

MySQLに接続する

index.phpの先頭に以下のようなPHPコードを記述する。
<?php

$db = new mysqli('localhost', 'root', '', 'app');
$db->set_charset('utf8');

$db->close();

?>
localhostを開いてエラーが出なければ接続成功。
クエリを発行する。

<?php

$db = new mysqli('localhost', 'root', '', 'app');
$db->set_charset('utf8');

$result = $db->query('SELECT * FROM messages');
$messages = $result->fetch_all(MYSQLI_ASSOC);
$result->free();

$db->close();

?>
$messages変数にデータベースから取得したメッセージが配列として格納された。var_dump($messages);と書くと$messagesの中身を確認することができる。
メッセージを表示する

ファイルの中ほど、<div class="container">と書いてある下の<div class="row">を10行ほどごっそりと削除して、以下のコードを記述する。
<?php foreach ($messages as $message) { ?>
    <p class="bg-info lead">
        <?= $message['name'] ?>: <?= $message['body'] ?> <small><?= $message['time'] ?></small>
    </p>
<?php } ?>
実践課題

このアプリケーションに投稿機能を実装しよう。
ヒント


メッセージの投稿には前回行った<form>要素によるPHPへのテキスト入力とパラメーターの受け取りを使えばよい。


Bootstrapで指定されたclass名を使うとフォームの見た目を整えることができる。こんな感じ。
<form class="form-inline text-center" method="POST">
    <label>名前</label>
    <input class="form-control" type="text" name="name">
    <label>本文</label>
    <input class="form-control" type="text" name="body">
    <button class="btn btn-default" type="submit">送信</button>
</form>


時刻を挿入するには'2016-05-25 00:00:00'と言った形式の文字列をINSERT文に指定する。

現在時刻を挿入する必要がある。PHPだとdate('Y-m-d H:i:s')という感じで生成できる。
MySQLの関数を使用する方法もある。


データを取得するわけではないので、fetch_allしたりfreeしたりする必要はない。


余力があったら


たぶん古いメッセージが上に表示されているので、これを新しいメッセージが上に表示されるように並び替える。

PHP側で並び替えてもいいが、SQL文で並び替える方法もある。ORDER BY 句を参照。


SQLインジェクション対策
XSS対策
データベースに接続できなかった場合にエラーページを表示する。
usersテーブルを作成し、messagesテーブルと関連付ける。

JOINする


時間を日付時刻ではなく「n分前」みたいな形式で表示する

実装例

<?php

$db = new mysqli('localhost', 'root', '', 'app');
$db->set_charset('utf8');

if ($_SERVER["REQUEST_METHOD"] === "POST") {
    $name = $_POST['name'];
    $body = $_POST['body'];
    $time = date('Y-m-d H:i:s');

    $sql = "
        INSERT INTO messages (name, body, time)
        VALUES ('$name', '$body', '$time')
    ";

    $db->query($sql);
}

$result = $db->query('SELECT * FROM messages');
$messages = $result->fetch_all(MYSQLI_ASSOC);
$result->free();

$db->close();

?>
NOW()関数を使う方法

if ($_SERVER["REQUEST_METHOD"] === "POST") {
    $name = $_POST['name'];
    $body = $_POST['body'];

    $sql = "
        INSERT INTO messages (name, body, time)
        VALUES ('$name', '$body', NOW())
    ";

    $db->query($sql);
}
日付順に並び替える

$result = $db->query('SELECT * FROM messages ORDER BY time DESC');
SQLインジェクション対策

if ($_SERVER["REQUEST_METHOD"] === "POST") {
    $name = $_POST['name'];
    $body = $_POST['body'];

    // SQLインジェクション対策
    $name = $db->real_escape_string($name);
    $body = $db->real_escape_string($body);

    ...
}
XSS対策

<?php foreach ($messages as $message) { ?>
    <p class="bg-info lead">
        <?= htmlspecialchars($message['name']) ?>: <?= htmlspecialchars($message['body']) ?> <small><?= htmlspecialchars($message['time']) ?></small>
    </p>
<?php } ?>