10 データベース入門

メモ

先に $ gem install sqlite3（時間かかるかもなので）
Slackについて
- 登録
- カンタンな使い方の説明など
夏休み中
- なんらかのテーマを与えます
- テーマに向けてなんか作ってもらいます
- Slackでなんでも聞いていいよ！
- 夏休み明け、どこかで発表してもらいます。
  - 情処のイベントにお邪魔するか、ソフテクも人数増えたし勝手にやっちゃうか？
夏休み明け
- なんらかの小さめのコンテストに応募してみる（予定）
  - もしかするとET班と合流してやるかも？（ぱくとまが結構乗り気）
  - 候補
    - LODチャレンジ2017 （例年、10月〜1月が応募期間、わりといけそう？）
    - キャンパスベンチャーグランプリ北海道 2017（応募期間が10月までなので厳しめ？）
    - その他、なんか知ってる？
- プログラミングそのもの以外の技術についても学んでいきたい
  - Git/GitHubやUNIXツール群、各種エディタやIDEなど
  - （ET班や競技班と合同でやる？）

はじめに

プログラムでデータを保持するには変数を使いますが、変数に格納したデータはコンピュータのメモリ上に存在するので、プログラムを終了したり、コンピュータをシャットダウンすると消えてしまいます。プログラムが終了してもデータが残るようにすることを、「データの永続化」といいます。

データの永続化の手段として、C言語ではファイルを扱いました。参照: 16 ファイル処理
また、Wordは文書をdocxファイルに、Excelはxlsxファイルに、XMindはXMINDファイルに保存しています。

今回は、別の手段として、「データベース」というものを利用します。

データベース入門

データベースとは

データベースは、データの集まりの事です。また、そのデータの集まりを管理するためのシステムを「データベース管理システム」といいますが、それを指してデータベースということも多いです。たいていのデータベースは、独立したアプリケーションとして動作しています。データベースを利用するようなアプリケーションでは、必要に応じてデータベースと通信を行い、データを保存したり、データを取り出したりしています。

データベースとプログラムの通信

データベース管理システム（以下データベース）にはいくつかの種類がありますが、よく使われるのはリレーショナルデータベース（RDB; Relational Database）です。RDBでは、データを表のように見立てて登録、管理します。また、異なった表のデータ間に関連性(Relation)をもたせることで、データ同士のつながりを表現しています。

データベースにデータを登録したり、データを取り出したりするためには、SQLと呼ばれる言語を利用します。

データベース用語

データベース用語を確認していきましょう。まず、データベースで扱うデータの一番大きな単位が、「データベース」です。データベースは、ふつうアプリケーション毎に作成します。Twitter社には、twitterというデータベースがあると思います。

RDBは、データの集まりを表に見立てて表現しています。この表の事を、「テーブル」といいます。ふつう、データの種類毎にテーブルを作成します。Twitterで扱うデータは、ユーザやツイート、リストなどがあるので、それぞれusersテーブル、tweetsテーブル、listsテーブルといった感じでしょう。

テーブルのひとつひとつのデータの事を、レコード(Record)または行(Row)といいます。また、データを保存するための領域の事を、Fieldまたは列(Column、カラム)といいます。

リレーショナルデータベースの基本

SQLite 3

リレーショナルデータベースにも、様々な種類があります。今回は、SQLite 3というデータベースを利用します。SQLite 3は、無料で利用できる軽量なデータベースです。

公式サイト

SQLite 3 Website

jocalcにもインストールされています。sqlite3というコマンドで起動することができます。

SQL

SQLとは、データベースとの通信を行うための言語です。言語なので、文法があります。SQLは、そのための本が数冊書けるほど奥が深いものですが、ここでは基本のみを扱うこととします。

ではさっそく、SQLを書いてみましょう。Tera Termを起動して学科内サーバにログインしてください。いつも活動で使っているディレクトリに移動して、以下のコマンドを入力して下さい。

$ sqlite3 intro_sqlite3.db

これで、SQLite 3のコンソールに入りました。

SQLを扱うには、とりあえず以下の基本の5つの構文を抑えておくと良いでしょう。

CREATE TABLE文
INSERT文
SELECT文
UPDATE文
DELETE文

順番に見ていきます。なお、SQLの文の終わりには、セミコロン ; をつけます。

まずは、CREATE TABLE文からです。
テーブルを作成するには、CREATE TABLE文を利用します。例えば、ユーザ情報を格納するusersテーブルを作成するには以下のようなSQLを記述します。なお、idは主キー（プライマリキー）です。自動で連番が振られます。

CREATE TABLE users (
    id              INTEGER PRIMARY KEY,
    screen_name     TEXT,
    name            TEXT,
    tweet_count     TEXT
);

次に、レコードの作成をするINSERT文です。
usersテーブルに、nameが’えむけー’、screen_nameが’mktakuya’、ツイート数が238008のレコードを登録するには、以下のようなSQLを記述します。

INSERT INTO users (name, screen_name, tweet_count) VALUES ('えむけー', 'mktakuya', 238008);

レコードの検索を行うSELECT文です。
usersテーブルから、すべてのidとscreen_name、nameを取り出すには、以下のようなSQLを記述します。

SELECT id, screen_name, name FROM users;

また、screen_nameが’mktakuya’なユーザのidとnameは、以下のように取り出せます。

SELECT id, name FROM users WHERE screen_name = 'mktakuya';

レコードの更新を行うUPDATE文です。
usersテーブルの、screen_nameが’mktakuya’であるレコードのnameを’えむけーちゃん’に、ツイート数を230010に更新するには、以下のようなSQLを記述します。

UPDATE users SET name = 'えむけーちゃん', tweet_count = 230010 WHERE screen_name='mktakuya';

レコードの削除はDELETE文で行います。
usersテーブルから、screen_nameが’mktakuya’であるレコードを削除するには、以下のようなSQLを記述します。

DELETE FROM users WHERE screen_name = 'mktakuya';

以上が、SQLの基本の5つの構文です。

SQLite 3のコンソールから抜け出すには、Ctrl + dを押します。

Rubyとデータベースを繋いでみる

はじめに

プログラムからデータベースを扱ってみましょう。RubyプログラムからSQLite 3を扱うには、sqlite3-rubyというgemを利用します。

sqlite3-ruby gem
- SQLite 3をRubyから扱うためのgem
- 公式サイト: sparklemotion/sqlite3-ruby
- インストール方法
  - $ gem install sqlite3

インストールは、以下のコマンドで実行します。

$ gem install sqlite3

データベースのオープンまたは新規作成は、以下のように行います。

1
2
3
4
5
6 require 'sqlite3'

db = SQLite3::Database.new 'test.db'

# プログラムを終了する際、データベースとの接続を切断する。
db.close

プログラムを終了する際は、db.closeでデータベースとの接続を切断するようにしてください。

intro_sqlite3.dbのusersテーブルで遊んでみる

先程SQLite 3入門のために作成したintro_sqlite3.dbデータベースのusersテーブルを例に、sqlite3 gemの使い方を学びましょう。

まずは、intro_sqlite3.dbに接続しましょう。Tera Termを起動し、intro_sqlite3.dbと同じディレクトリに移動してから、irbまたはpryを起動し、以下のコードを入力して下さい。

1
2
3 require 'sqlite3'

db = SQLite3::Database.new './intro_sqlite3.db'

SQLの実行は、db.execute()メソッドに文字列を渡して行います。例えば、Rubyから新しいユーザを追加するには、以下のようなコードを書きます。

1 db.execute("INSERT INTO users (screen_name, name, tweet_count) VALUES ('fk2763owl', 'ふっくん', 28570);")

なお、変数の値をSQL文に埋め込みたい時は、以下のようにします。埋め込みたい箇所を?にして、SQL文字列の後に?に埋め込みたい変数を配列で渡します。
なお、Rubyの文字列の式展開 #{変数名}はSQLを実行する時は絶対に使わないでください。

1
2
3
4
5
6
7
8
9
10
11
12
13 # キーボードの入力を、screen_name変数に格納する
screen_name = STDIN.gets()
screen_name.chomp! # 末尾の改行コードを削除する

# キーボードの入力を、name変数に格納する
name = STDIN.gets()
name.chomp!

# キーボードの入力を、tweet_count変数に格納する
tweet_count = STDIN.gets()
tweet_count = tweet_count.to_i # 文字列としての数字が格納されているので、`to_i`メソッドで整数に変換する。

db.execute("INSERT INTO users (screen_name, name, tweet_count) VALUES (?, ?, ?);", [screen_name, name, tweet_count])

また、SELECT文を実行すると、結果がレコードの配列で返ってきます。また、レコード自体も[id, screen_name, name, tweet_count]の配列です。

1
2
3
4 results = db.execute('SELECT * FROM users;')
results.each do |result|
  puts result[1]
end

最後に、データベースとの接続を切断しましょう。

1 db.close

データベースを使ったWebアプリケーションを作ってみる

前回作成したじゃんけんAppに、ランキング機能をつけましょう。仕様は、以下のとおりとします。

グーチョキパーの選択画面に、ユーザ名入力欄を追加する
ユーザ名(name)と勝利数(count)を記録する。
データベース情報
- usersテーブル
  - id INTEGER PRIMARY KEY
  - name TEXT
  - count INTEGER
ユーザがじゃんけんに勝利した時
- ユーザ名が存在していなければ、INSERT文を実行し、ユーザのレコードを新規作成、勝利数を1にする。
- ユーザ名が既に存在していればUPDATE文を実行し勝利数をインクリメントする。
ユーザの勝敗の記録は、とりあえずSQLite3コンソールから確認する。

なお、アプリ名は、jankenapp2としましょう。前回作ったjankenappのコードを使いまわせそうなので、フォルダごとコピーしてjankenapp2というフォルダを作りましょう。（Tera Term上でやるなら以下のコマンドを実行して下さい。）

# jankenappがあるディレクトリに移動してから
$ cp -R jankenapp jankenapp2
$ cd jankenapp2

データベースの作成とテーブルの定義をしましょう。データベース名は、janken.dbとします。

$ sqlite3 janken.db

これでsqlite3のコンソールに入りました。以下のSQL文を入力してusersテーブルを作成しましょう。

CREATE TABLE users (
    id      INTEGER PRIMARY KEY,
    name    TEXT,
    count   INTEGER
);

これでデータベースの作成とテーブルの定義が完了しました。

では、次にUIを変更しましょう。views/index.erbを以下のように変更しましょう。変更箇所は、titleと、formの中、そしてページ下部のランキング機能へのリンクだけです。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27 <!DOCTYPE html>
<html>
  <head>
    <title>じゃんけんApp 2</title>
    <meta charset="UTF-8">
  </head>
  <body>
    <h1>じゃんけんApp</h1>

    <form action="/janken" method="GET">
      <p>お名前は？</p>
      <input type="text" name="name">

      <p>どれにする？</p>
      <input type="radio" name="janken" value="stone"> グー
      <br>
      <input type="radio" name="janken" value="paper"> パー
      <br>
      <input type="radio" name="janken" value="scissors"> チョキ
      <br>

      <input type="submit" value="送信する">
    </form>

    <a href="/ranking">ランキングへ</a>
  </body>
</html>

こんな感じになっていればOKです。

jankenapp2_index

では、app.rbを編集してやりましょう。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38 require 'sinatra'
require 'sqlite3'

get '/' do
  erb :index
end

get '/janken' do
  @ruby = ['stone', 'paper', 'scissors'].sample
  @user = params[:janken]

  if @user == 'stone'
  # 勝敗判定のif文が続く……（省略）
  end

  # ----- ここから下を追加 -----
  # フォームに入力された名前を @name 変数に格納
  @name = params[:name]

  # データベースとの接続
  db = SQLite3::Database.new('janken.db')
  record = db.execute('SELECT * FROM users WHERE name = ?;', [ @name ])

  if @result == '勝ち'
    # SELECT文で該当するレコードが存在しない時は、空の配列が返ってくる。
    # Array#empty? メソッドは、配列が空の時trueを、そうでない時falseを返す。
    if record.empty?
      db.execute('INSERT INTO users (name, count) VALUES (?, 1);', [ @name ])
    else
      db.execute('UPDATE users SET count = count + 1 WHERE name = ?;', [ @name ])
    end
  end

  # dbのクローズを忘れずに
  db.close

  erb :janken
end

勝敗結果を表示するviews/janken.erbも少し変更してやりましょう。変更箇所は、titleと、”あなたの選択”を<%= @name %>さんの選択にしただけです。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 <!DOCTYPE html>
<html>
  <head>
    <title>じゃんけんApp 2</title>
    <meta charset="UTF-8">
  </head>
  <body>
    <h1>じゃんけんApp</h1>

    <p><%= @name %>さんの選択: <%= @user %> </p>
    <p>Rubyの選択: <%= @ruby %> </p>

    <p>結果: <%= @result %></p>

    <a href="/">戻る</a>
  </body>
</html>

さて、いよいよランキング機能を作っていきましょう。

app.rbの末尾に、以下のコードを追加して下さい。

1
2
3
4
5
6
7
8
9
10
11
12 get '/ranking' do
  db = SQLite3::Database.new 'janken.db'

  # `SELECT * FROM テーブル名 ORDER BY カラム名 DESC`
  # で、カラム名の降順（DESC）でレコードを取得できる。（昇順の場合はASC）
  @users = db.execute('SELECT * FROM users ORDER BY count DESC;')

  # dbのクローズを忘れずに
  db.close

  erb :ranking
end

また、ランキング表示用のviewを作成しましょう。views/ranking.erbを作成してください。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 <!DOCTYPE html>
<html>
  <head>
    <title>じゃんけんApp 2</title>
    <meta charset="UTF-8">
  </head>
  <body>
    <h1>じゃんけんApp ランキング</h1>

    <ol>
      <% @users.each do |user| %>
        <li><%= user[1] %>さん(<%= user[2]%>勝)
      <% end %>
    </ol>

    <a href="/">戻る</a>
  </body>
</html>

完成したじゃんけんApp 2は、こんな感じになります。

Janken App 2 Overview

なお、HTMLのolタグは、番号付きリストを表します。

また、erbファイル内で配列にeachメソッドを適用するには、以下のようにコードを記述します。

1
2
3 <% ary.each do |item| %>
  <li><%= item %></li>
<% end %>

erbについての詳細は、以下のページをどうぞ。

Rubyist Magazine - 標準添付ライブラリ紹介【第 10 回】 ERB