日本語諸方言コーパスをDB化して遊ぶ (5) データベースの移行とモデルの作成

連載記事です。これからは、前回ガワだけ作成した Laravel アプリの中身を作りこんでいきます。第５回は PostgreSQL への移行、モデルの作成を行ないます。完全に自分用の作業メモで、説明もいろいろ足りていないと思いますが、ご容赦ください。

• 第１回： 日本語諸方言コーパスをDB化して遊ぶ (1) 構成を考える
• 第２回： 日本語諸方言コーパスをDB化して遊ぶ (2) SQLite3 で DB 化
• 第３回： 日本語諸方言コーパスをDB化して遊ぶ (3) PHP Laravel で操作する
• 第４回： 日本語諸方言コーパスをDB化して遊ぶ (4) サービスの全体像を決める
• 第５回： 日本語諸方言コーパスをDB化して遊ぶ (5) データベースの移行とモデルの作成　←今ここ
• 第６回： 日本語諸方言コーパスをDB化して遊ぶ (6) 談話ごとの発話総覧を作る
• 第７回： 日本語諸方言コーパスをDB化して遊ぶ (7) 話者ごとの発話総覧を作る
• 第８回： 日本語諸方言コーパスをDB化して遊ぶ (8) ファイル形式変換機能をつける
• 第９回： 日本語諸方言コーパスをDB化して遊ぶ (9) Heroku でデプロイする

PostgreSQL の設定

Heroku の準備

第２回は開発用に SQLite でデータベースを作成していましたが、本番では PostgreSQL に移行します。本番環境のビルド直前に移行なんてしていると、思いもよらない面倒な事態が発生して工程をさかのぼりかねないので（１敗）、本番環境で使う技術を決めたら、下見がてら先に構築しておきます。

さて Heroku PostgreSQL の無料枠を使用したいので、この段階で Heroku に登録してウェブアプリ用のプロジェクトを作成しておきましょう。同時に Heroku CLI もインストールしておきます。

• 【herokuコマンド】createからdestroyまで | Qiita

heroku login
heroku create my-app-name

git 管理やビルドの詳細設定については今後解説しますが、この段階でプロジェクトは git 管理しておくとよいでしょう。

DB の作成・接続

作成が済んだら Heroku PostgreSQL の無料枠（Hobby-Dev Free プラン）の準備をしましょう。Addon として Heroku PostgreSQL を追加します。プロジェクトページから数クリックで作成できます。Settings ページにデータベースの Credentials 情報があるので、今後はそれを利用してデータベースに接続することになります。とりあえず開発環境から¹テスト接続するために、環境変数を .env に書きこんでおきましょう。

.env

DB_CONNECTION=pgsql
DB_HOST=*****
DB_PORT=*****
DB_DATABASE=*****
DB_USERNAME=*****
DB_PASSWORD=*****
DATABASE_URL=postgres://*****

CLI を使いたい場合は以下の参考リンクをご覧ください。

• Laravel5 HerokuでPostgreSqlを使う設定 | Qiita

また、PHP の設定ファイル php.ini 内で以下の行が ; でコメントアウトされていたら、これを外します。こうすることで PostgreSQL 用のドライバが有効化されます。

php.ini

extension=pdo_pgsql

これで接続準備ができました。以下のようにして DB に接続できます。

heroku pg:psql --app my-app-name

データ移行

データの移行は、SQLite のダンプデータを出力して、それを PostgreSQL DB に流し込んでもいいのですが、Heroku CLI 経由だとめっちゃ時間がかかるのと、両 SQL 間の微妙な文法差に対応したりするのが面倒くさいです（１敗）。

• sqliteからpostgresへの移行 | Qiita

cmd

// export as dump
sqlite3 my-db-name .dump > my-db-dump.sql

// import dump
heroku pg:psql --app my-app-name < my-db-dump.sql 

そこで、今回はダンプをそのまま利用せずに段階を踏んで移行することにしました。具体的には、PostgreSQL DB に空のテーブルを定義して、そこに SQLite DB を CSV 出力したものを読み込んでいきます。

まずテーブルを作成していきます。第２回で使用した CREATE TABLE 文がだいたい流用できますが²、いくつかマイナーチェンジが必要です。

• INTEGER PRIMARY KEY AUTOINCREMENT を PostgreSQL 式表記の SERIAL PRIMARY KEY に変更
• テーブル名に英大文字を使うと PostgreSQL は少し面倒なので、命名は小文字に統一
• xmin, xmax が PostgreSQL の予約語なので tmin, tmax に変更
• location もよくないので place に変更

また、PostgreSQL は SQLite よりいろいろな意味で厳密なので、SQLite では発覚しなかった定義ミスがこの段階で発覚したりします。最終的に次のような命令を入れました。

cojads_create_table.sql

CREATE TABLE IF NOT EXISTS prefecture(
  prefecturenum INTEGER PRIMARY KEY, 
  prefecturename TEXT
);
CREATE TABLE IF NOT EXISTS place(
  prefecturenum INTEGER, 
  placeid TEXT,
  placename TEXT, 
  PRIMARY KEY (prefecturenum, placeid),
  FOREIGN KEY (prefecturenum) REFERENCES prefecture(prefecturenum)
);
CREATE TABLE IF NOT EXISTS speaker(
  speakerid TEXT PRIMARY KEY, 
  speakersex TEXT, 
  speakerbirthyear TEXT
);
CREATE TABLE IF NOT EXISTS discourse(
  discourseid TEXT PRIMARY KEY, 
  prefecturenum INTEGER NOT NULL, 
  placeid TEXT NOT NULL, 
  fileid TEXT NOT NULL, 
  reference TEXT, 
  recorddate TEXT,
  recordplace TEXT, 
  recorder TEXT, 
  editor TEXT, 
  topic TEXT,
  genre TEXT,
  FOREIGN KEY (prefecturenum, placeid) REFERENCES place(prefecturenum, placeid)
);
CREATE TABLE IF NOT EXISTS utterance(
  discourseid TEXT, 
  utteranceid INTEGER, 
  speakerid TEXT,
  tmin REAL,
  tmax REAL,
  dialecttext TEXT NOT NULL,
  standardtext TEXT,
  PRIMARY KEY (discourseid, utteranceid),
  FOREIGN KEY (discourseid) REFERENCES discourse(discourseid),
  FOREIGN KEY (speakerid) REFERENCES speaker(speakerid)
);
CREATE TABLE IF NOT EXISTS changelog(
  updateid SERIAL PRIMARY KEY,
  discourseid TEXT NOT NULL, 
  utteranceid INTEGER NOT NULL, 
  olddialecttext TEXT, 
  oldstandardtext TEXT, 
  newdialecttext TEXT, 
  newstandardtext TEXT, 
  updatedtime TEXT,
  FOREIGN KEY (discourseid, utteranceid) REFERENCES utterance(discourseid, utteranceid)
);

さくっと実行します。

cmd

heroku pg:psql --app cojads-tools < cojads_create_table.sql

次にテーブルにレコードを入れていきます。まずは SQLite からテーブルごとに CSV 出力していきます。ヘッダー付きで出力しましょう。

cmd

sqlite>　.headers on
sqlite>　.mode csv
sqlite>　.output tablename.csv
sqlite>　SELECT * FROM tablename;

先ほどの CREATE TABLE 文に合わせてヘッダーの命名を微調整して、文字コードが UTF-8 なら Shift-JIS に変更して、改行が CR/LF なら LF と変更しておきます。また Heroku PostgreSQL の無料枠は 10,000 レコードが上限 なので、50,000 件以上ある utterance.csv はゴリゴリ削っておきましょう。今回は各談話につき ID200 までとしました。

cmd

sqlite>　SELECT * FROM utterance WHERE utteranceid < 201;

準備が出来たら、外部キーに依存しないテーブルから順にインポートしていきます。ローカルの CSV ファイルを Heroku PostgreSQL に読ませるにはメタコマンド \copy を使用します。ファイル名は " ではなく ' で囲みます。

cmd

heroku pg:psql --app my-app-name
# \copy prefecture from 'prefecture.csv' with csv header
# \copy place from 'place.csv' with csv header
# \copy speaker from 'speaker.csv' with csv header
# \copy discourse from 'discourse.csv' with csv header
# \copy utterance from 'utterance.csv' with csv header
# \copy changelog from 'changeLog.csv' with csv header

そうしたらマイグレーションしておきます（必要？）。

cmd

php artisan migrate

Model の作成

さて、それでは Laravel の開発に戻ります。今回データベース関係の機能はほとんど Controller に持たせるので、Model は第３回でやったように適当に最低限の構造だけ持たせます。たとえば、discourse テーブルに対して以下のようなモデルを作ります。

App/Models/Discourse.php

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Discourse extends Model{
    protected $table = 'discourse';
    protected $guarded = array('discourseid');
    public $timestamps = false;

    public function getData(){
        $data = DB::table($this->table)->get();
        return $data;
    }
}

次回

第１の機能「談話ごとの発話総覧」を作りこんでいきます。

• 第６回： 日本語諸方言コーパスをDB化して遊ぶ (6) 談話ごとの発話総覧を作る

1. .env 自体は git push 対象とはせず、あとで別途 heroku に環境変数を直接設定することになります。 ↩

2. もっと言うと SQLite ダンプデータの CREATE TABLE 文を流用すればよい。 ↩