定期的に開催していた「理論から学ぶ データベース実践入門」読書会が無事に最終回を迎えました。次の読書会としてSQL実践入門 読書会を予定しており、こちらはSQLのサンプルコードが出てくる実務寄りな内容の書籍となっています。
読書会で使用する「SQL実践入門──高速でわかりやすいクエリの書き方」のサポートページでは書籍内で使用しているDBテーブルスキーマやデータがサンプルコードとして公開されています。
読書会でもこれらのサンプルコードを実際に動かして議論したいと考え、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両方の設定が完了したので、次はサンプルデータを投入してみます。
サンプルデータのダウンロード
書籍のサポートページからサンプルコードをダウンロードして展開します。
$ 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実践入門 読書会でサンプルコードを動かすための手順をMySQL,PostgreSQLの両方で実施してみました。設定手順が若干煩わしいので、Dockerイメージで上記の環境を提供できればと考えています。
また、読書会の第一回目はすでに参加多数となっておりますが、ご興味のある方は二回目以降の参加をご検討いただければと思います。