書籍「SQL実践入門」のサンプルコードをMySQLとPostgreSQLで試してみる

定期的に開催していた[「理論から学ぶ データベース実践入門」読書会](http://riron-db.connpass.com/)が無事に最終回を迎えました。次の読書会として[SQL実践入門 読書会](http://sqljissen.connpass.com/)を予定しており、こちらはSQLのサンプルコードが出てくる実務寄りな内容の書籍となっています。

 * [ SQL実践入門 読書会(1)](http://sqljissen.connpass.com/event/29260/)

読書会で使用する「[SQL実践入門──高速でわかりやすいクエリの書き方](http://gihyo.jp/book/2015/978-4-7741-7301-6)」の[サポートページ](http://gihyo.jp/book/2015/978-4-7741-7301-6/support)では書籍内で使用しているDBテーブルスキーマやデータが[サンプルコード](http://image.gihyo.co.jp/assets/files/book/2015/978-4-7741-7301-6/download/SQL_practical_guide_samplecode.zip)として公開されています。

読書会でもこれらのサンプルコードを実際に動かして議論したいと考え、RDBMSの設定とサンプルコードをDBに投入するまでの手順をまとめてみました。

BSD系とLinuxでの手順を示したかったのですが、まずはLinux(CentOS)における手順をまとめてみました。

# CentOS7における設定例

CentOS7での設定手順です。書籍ではRDBMSとしてPostgreSQLを想定していますが、サンプルとして示されているSQLは標準的な構文で記述しているとのことで、MySQLでも動作するようです。

複数のRDBMSで試してみたいので、MySQL/PostgreSQLの両方を設定してみます。

## RDBMSのインストール

MySQL,PostgreSQLのいずれもパッケージで提供されており、インストール自体はサクッと完了します。
※ただしMySQLは実際のところMariaDBパッケージとして提供されていますが、便宜上「MySQL」と表記して説明します。

### MySQL

yumでMySQL(MariaDB)をインストールします。unzipは書籍のサンプルコード(ZIPファイル)を展開するためにインストールします。

```
$ sudo yum install mariadb-server unzip
$ rpm -qa | grep -i maria 
mariadb-5.5.47-1.el7_2.x86_64
mariadb-server-5.5.47-1.el7_2.x86_64
mariadb-libs-5.5.47-1.el7_2.x86_64
```

MySQLのデフォルト文字コードはLatin-1になっているので、my.cnfを編集し、文字コードをUTF8に変更します。

```
$ rpm -ql mariadb-libs-5.5.47-1.el7_2 | grep my.cnf$
/etc/my.cnf
$ diff -upr /etc/my.cnf.ORIG /etc/my.cnf
--- /etc/my.cnf.ORIG    2016-04-04 06:55:47.676146685 +0900
+++ /etc/my.cnf 2016-04-04 07:03:25.819152470 +0900
@@ -17,3 +17,14 @@ pid-file=/var/run/mariadb/mariadb.pid
 #
 !includedir /etc/my.cnf.d
 
+[mysqld]
+character-set-server=utf8
+skip-character-set-client-handshake
+default-storage-engine=INNODB
+
+[mysqldump]
+default-character-set=utf8
+
+[mysql]
+default-character-set=utf8
+
```

起動時にMySQLが起動するよう設定します。

```
$ systemctl list-unit-files | grep maria 
mariadb.service                             disabled
$ sudo systemctl enable mariadb.service
$ systemctl list-unit-files | grep maria 
mariadb.service                             enabled 
```

MySQLサーバを手動で起動します。

```
$ systemctl status mariadb.service | grep Active: 
   Active: inactive (dead)
$ sudo systemctl start mariadb.service
$ systemctl status mariadb.service | grep Active: 
   Active: active (running) since 月 2016-04-04 07:09:27 JST; 2s ago
```

DBのrootユーザについてパスワードを設定します。

```
$ mysql -u root -p
Enter password: 
MariaDB [(none)]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('sqljissen') ;
Query OK, 0 rows affected (0.00 sec)
```

データベースとユーザを作成します。以下の例ではDB名を「sqljissen_db」、ユーザ名は「sqljissen」にしています。

```
$ mysql -u root -p
MariaDB [(none)]> CREATE DATABASE sqljissen_db CHARACTER SET UTF8 ;
MariaDB [(none)]> CREATE USER 'sqljissen'@'localhost' IDENTIFIED BY 'sqljissen' ;
MariaDB [(none)]> GRANT ALL PRIVILEGES ON sqljissen_db.* TO 'sqljissen'@'localhost' ;
```

データベースに接続してみます。MySQLについてはこれで設定完了です。

```
$ mysql sqljissen_db -u sqljissen -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.47-MariaDB MariaDB Server

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

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

MariaDB [sqljissen_db]> 
```

### PostgreSQL

続いてPostgreSQLの設定です。MySQLの場合と同じく、パッケージからインストールします。

```
$ sudo yum install postgresql-server postgresql
$ rpm -qa | grep postgres 
postgresql-9.2.15-1.el7_2.x86_64
postgresql-libs-9.2.15-1.el7_2.x86_64
postgresql-server-9.2.15-1.el7_2.x86_64
```

起動時にPostgreSQLサーバが起動するように設定します。

```
$ systemctl list-unit-files | grep postgres 
postgresql.service                          disabled
$ sudo systemctl enable postgresql.service
$ systemctl list-unit-files | grep postgres 
postgresql.service                          enabled
```

initdbでデータベースを初期化します。CentOS7では、この手順を抜かしてしまうとpostgres.service起動時にエラーが発生してしまいます。

```
$ sudo postgresql-setup initdb
Initializing database ... OK
$ sudo tail -n6 /var/lib/pgsql/initdb.log
成功しました。以下を使用してデータベースサーバを起動することができます。

    /usr/bin/postmaster -D /var/lib/pgsql/data
または
    /usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start
```

手動でPostgreSQLサーバを起動します。

```
$ systemctl status postgresql.service | grep Active:
   Active: inactive (dead)
$ sudo systemctl start postgresql.service
$ systemctl status postgresql.service | grep Active:
   Active: active (running) since 月 2016-04-04 08:17:54 JST; 4s ago
```

MySQLの場合と同じく、データベースを作成します。パッケージのインストール時にDBのrootユーザとしてpostgresが作成されており、このユーザ権限で作業します。DB名は「sqljissen_db」です。

```
$ sudo -i -u postgres bash
$ psql
psql (9.2.15)
"help" でヘルプを表示します.

postgres=# CREATE USER sqljissen
postgres-#   WITH
postgres-#     ENCRYPTED PASSWORD 'sqljissen'
postgres-#     LOGIN 
postgres-#     NOCREATEDB NOCREATEROLE NOCREATEUSER ;
CREATE ROLE
postgres=# CREATE DATABASE sqljissen_db
  WITH
    OWNER sqljissen
    ENCODING 'UTF8' ;
CREATE DATABASE
```

DBユーザと同名のUNIXユーザを作成します(CREATE USERだけではダメなの？とか、このあたりの理由は調べ切れていないです...)。

```
$ sudo groupadd sqljissen
$ sudo useradd -g sqljissen -m -s /bin/bash sqljissen
$ sudo passwd sqljissen
```

データベースに接続してみます。これでPostgreSQLについても設定完了です。

```
$ sudo -i -u sqljissen bash 
$ psql sqljissen_db sqljissen
psql (9.2.15)
"help" でヘルプを表示します.

sqljissen_db=> 
```

## サンプルデータを用意する

MySQL,PostgreSQL両方の設定が完了したので、次はサンプルデータを投入してみます。

### サンプルデータのダウンロード

[書籍](http://gihyo.jp/book/2015/978-4-7741-7301-6)の[サポートページ](http://gihyo.jp/book/2015/978-4-7741-7301-6/support)から[サンプルコード](http://image.gihyo.co.jp/assets/files/book/2015/978-4-7741-7301-6/download/SQL_practical_guide_samplecode.zip)をダウンロードして展開します。

```
$ curl -O http://image.gihyo.co.jp/assets/files/book/2015/978-4-7741-7301-6/download/SQL_practical_guide_samplecode.zip
$ unzip SQL_practical_guide_samplecode.zip
```

### サンプルデータの投入

サンプルデータテキストファイルになっており、SQL例が記載されています。Code1.txtを元にDBテーブルの作成とデータ投入を行ってみます。

```
$ head -n 10 Code1.txt 

■「図1.8::店舗テーブルのサンプル」を作成
CREATE TABLE Shops (
 shop_id    CHAR(5) NOT NULL,
 shop_name  VARCHAR(64),
 rating     INTEGER,
 area       VARCHAR(64),
   CONSTRAINT pk_shops PRIMARY KEY (shop_id));

INSERT INTO Shops (shop_id, shop_name, rating, area) VALUES ('00001', '○○商店', 3, '北海道');
```

#### MySQLの場合

実行例は以下のようになります。

```
$ mysql sqljissen_db -u sqljissen -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.47-MariaDB MariaDB Server

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

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

MariaDB [sqljissen_db]> CREATE TABLE Shops (
    ->  shop_id    CHAR(5) NOT NULL,
    ->  shop_name  VARCHAR(64),
    ->  rating     INTEGER,
    ->  area       VARCHAR(64),
    ->    CONSTRAINT pk_shops PRIMARY KEY (shop_id));
Query OK, 0 rows affected (0.06 sec)

MariaDB [sqljissen_db]> 
MariaDB [sqljissen_db]> INSERT INTO Shops (shop_id, shop_name, rating, area) VALUES ('00001', '○○商店', 3, '北海道');
Query OK, 1 row affected (0.00 sec)

MariaDB [sqljissen_db]> SELECT * FROM Shops ;
+---------+--------------+--------+-----------+
| shop_id | shop_name    | rating | area      |
+---------+--------------+--------+-----------+
| 00001   | ○○商店       |      3 | 北海道    |
+---------+--------------+--------+-----------+
1 row in set (0.00 sec)

MariaDB [sqljissen_db]>  \q
Bye
$ 
```

#### PostgreSQLの場合

実行例は以下のようになります(MySQL,PostgreSQLいずれでも同じ振る舞いになるのは当たり前ですね...)。

```
$ sudo -i -u sqljissen bash 
$ psql sqljissen_db sqljissen
sqljissen_db=> CREATE TABLE Shops (
sqljissen_db(>  shop_id    CHAR(5) NOT NULL,
sqljissen_db(>  shop_name  VARCHAR(64),
sqljissen_db(>  rating     INTEGER,
sqljissen_db(>  area       VARCHAR(64),
sqljissen_db(>    CONSTRAINT pk_shops PRIMARY KEY (shop_id));
NOTICE:  CREATE TABLE / PRIMARY KEYはテーブル"shops"に暗黙的なインデックス"pk_shops"を作成します
CREATE TABLE
sqljissen_db=> 
sqljissen_db=> INSERT INTO Shops (shop_id, shop_name, rating, area) VALUES ('00001', '○○商店', 3, '北海道');
INSERT 0 1
sqljissen_db=> SELECT * FROM Shops ;
 shop_id | shop_name | rating |  area  
---------+-----------+--------+--------
 00001   | ○○商店    |      3 | 北海道
(1 行)

sqljissen_db=> \q
$
```

これで書籍内のSQLサンプルの説明と照らし合わせながら実際にDBを触ってみるための準備が整いました。

# まとめ

[SQL実践入門 読書会](http://sqljissen.connpass.com/event/29260/)でサンプルコードを動かすための手順をMySQL,PostgreSQLの両方で実施してみました。設定手順が若干煩わしいので、Dockerイメージで上記の環境を提供できればと考えています。

また、読書会の第一回目はすでに参加多数となっておりますが、ご興味のある方は二回目以降の参加をご検討いただければと思います。

 * [ SQL実践入門 読書会(1)](http://sqljissen.connpass.com/event/29260/)

# 参考URL

 * [PostgreSQL 9.2.4 付属ドキュメント](https://www.postgresql.jp/document/9.2/index.html)