portal shit!

公開している Lokka の DB (MySQL) のデータを、手もとの Lokka の DB (SQLite) に取り込むときに毎回やり方を忘れるので書いておきます。

今回は MySQL to Sqlite converter という gist のコメント欄に書いてある以下の方法を試した。

sudo gem install sequel
sudo gem install sqlite3
sudo gem install mysql
rbenv rehash
sequel mysql://user:password@host/database -C sqlite://db.sqlite

これでデータを MySQL から SQLite に変換できた。Lokka をローカルで起動してアクセスしてみるが記事が表示されない。DB の中にはちゃんとデータが入ってるし、管理画面から記事一覧を表示するとちゃんと記事が表示される。どうも Post.published が [] を返すみたい。

手でクエリを投げてみると

sqlite> select * from entries order by created_at desc limit 1;
             id = 960
        user_id = 1
    category_id = 5
           slug = eye-pro-is-shit
          title = Eye-Fi Pro を買ったけどクソだった
           body = [hitode909さんの日記](http://hitode909.hatenablog.com/entry/2013/05/24/093749 "hitode909の日記")を...
           type = Post
          draft = 0
     created_at = 2013-10-06 07:28:00.000000
     updated_at = 2013-10-06 13:11:23.000000
frozen_tag_list =
         markup = redcarpet

draft = 0 なので表示されそうなもんなんだけど、よくよく調べてみると SQLite には Boolean 型がなくて、DataMapper は SQLite に Boolean 型のデータを保存するときは t/f の文字列で保存するみたい。これは盲点だった。

結局 draft = 0 を draft = 'f' に変えてあげればすべての記事を表示できるようになった。

一月くらい前から Lokka の master ブランチを自分のブログ用のブランチに merge してサイトをデプロイすると謎の白画面が出るようになっていて困っていた。現象は極めて謎で、ローカルの開発環境（RACK_ENV='development'）では見られず、本番（RACK_ENV='production'）だけで発生した。HTTP ステータスコードは 1054 が返ってきたりする。なんか変な gem でも入れてしまったかなと休みの日に動作検証したりしていたんだけどついぞ分からなかった。

SQL 弱者なので気がついてなかったんだけど、2月15日の commit で Site モデルに新しいフィールドが追加されていた（Add default markup in admin/site/edit · 2243dc5 · lokka/lokka ※この機能便利すね）。なので bundle exec rake db:migrate しないといけなかったわけだった。ローカルで動いている開発環境（データベースは SQLite）では Migration なんて行ってないんだけどエラーは出なかった。本番は MySQL で動いていて、こちらでだけエラーが出るようだった。

しかしいざ migrate しようとすると失敗する。

bundle exec rake db:migrate
Upgrading Database...
rake aborted!
Invalid default value for 'updated_at'

のようなエラーが出る。updated_at のデフォルト値がおかしいらしい。このときのテーブルの構造を見てみると以下のような感じだった。

mysql> desc entries;
+-----------------+--------------+------+-----+---------------------+-----------------------------+
| Field           | Type         | Null | Key | Default             | Extra                       |
+-----------------+--------------+------+-----+---------------------+-----------------------------+
| id              | int(11)      | NO   | PRI | NULL                | auto_increment              |
| user_id         | int(11)      | YES  |     | NULL                |                             |
| category_id     | int(11)      | YES  |     | NULL                |                             |
| slug            | varchar(255) | YES  |     | NULL                |                             |
| title           | varchar(255) | YES  |     | NULL                |                             |
| body            | text         | YES  |     | NULL                |                             |
| type            | text         | NO   |     | NULL                |                             |
| draft           | tinyint(1)   | YES  |     | 0                   |                             |
| created_at      | timestamp    | NO   |     | 0000-00-00 00:00:00 |                             |
| updated_at      | timestamp    | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| frozen_tag_list | text         | YES  |     | NULL                |                             |
| markup          | varchar(255) | YES  |     | NULL                |                             |
+-----------------+--------------+------+-----+---------------------+-----------------------------+

調べてみたところ MySQL の Mode が NO_ZERO_DATE になっている場合、MySQL は timestamp 型のフィールドのデフォルト値に 0000-00-00 00:00:00 みたいな値を設定することを許さないらしい。 mysql - Invalid default value for 'create_date' timestamp field - Stack Overflow

検証用に別にテーブルを用意して bundle exec rake db:setup してみたところ、以下のような構造のテーブルができた。

mysql> desc entries;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| id              | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id         | int(11)          | YES  |     | NULL    |                |
| category_id     | int(11)          | YES  |     | NULL    |                |
| slug            | varchar(255)     | YES  |     | NULL    |                |
| title           | varchar(255)     | YES  |     | NULL    |                |
| body            | text             | YES  |     | NULL    |                |
| markup          | varchar(255)     | YES  |     | NULL    |                |
| type            | varchar(50)      | NO   |     | NULL    |                |
| draft           | tinyint(1)       | YES  |     | 0       |                |
| created_at      | datetime         | YES  |     | NULL    |                |
| updated_at      | datetime         | YES  |     | NULL    |                |
| frozen_tag_list | text             | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

created_at と updated_at は datetime 型になるらしい。なので以下のような ALTER 文を実行した。

mysql> alter table entries modify column created_at datetime, modify column updated_at datetime;
mysql> desc entries;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id         | int(11)      | YES  |     | NULL    |                |
| category_id     | int(11)      | YES  |     | NULL    |                |
| slug            | varchar(255) | YES  |     | NULL    |                |
| title           | varchar(255) | YES  |     | NULL    |                |
| body            | text         | YES  |     | NULL    |                |
| type            | text         | NO   |     | NULL    |                |
| draft           | tinyint(1)   | YES  |     | 0       |                |
| created_at      | datetime     | YES  |     | NULL    |                |
| updated_at      | datetime     | YES  |     | NULL    |                |
| frozen_tag_list | text         | YES  |     | NULL    |                |
| markup          | varchar(255) | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

これで最新のコードをデプロイしても真っ白画面になることはなくなった。以前遭遇した更新時に created_at の値が更新されてしまう問題 もフィールドの型が timestamp だったのが原因なのだと思う。SQLite から MySQL への移行は一筋縄では行かないことが分かった。

DataMapper はソースコード内の記述内容から動的に Migration を行えるけど、ActiveRecord みたいに $APP_ROOT/db/ ディレクトリに Migration ファイルを作ってくれたりしないので DB スキーマの変更が必要なことに気がつきにくい。便利だけど不便な感じがする。Rails で $APP_ROOT/db/ 以下にアホみたいにファイルが出来ていくの嫌だと思っていたけど、スキーマ変更に気がつかずコードをデプロイしてウェブアプリケーション停止みたいな自体は防げると思った。

ブログ書こうと思ってパソコン開いて「ついでに最新版の変更を取り込むか」とかやるとデプロイできなくなったりして書きたかった記事が書けず残念な感じになる。はてなブログでブログ書いてて画面が真っ白になったらひとでくんさんに不具合報告して直してもらえば良いので楽だと思う。

一個前の記事で書いた「記事の更新時に updated_at ではなく created_at が更新されてしまう」問題、原因はプログラム側にあるのではなく、MySQL の設定の問題だった。

mysql> desc entries;
+-----------------+--------------+------+-----+---------------------+-----------------------------+
| Field           | Type         | Null | Key | Default             | Extra                       |
+-----------------+--------------+------+-----+---------------------+-----------------------------+
| id              | int(11)      | NO   | PRI | NULL                | auto_increment              |
| user_id         | int(11)      | YES  |     | NULL                |                             |
| category_id     | int(11)      | YES  |     | NULL                |                             |
| slug            | varchar(255) | YES  |     | NULL                |                             |
| title           | varchar(255) | YES  |     | NULL                |                             |
| body            | text         | YES  |     | NULL                |                             |
| type            | text         | NO   |     | NULL                |                             |
| draft           | tinyint(1)   | YES  |     | 0                   |                             |
| created_at      | timestamp    | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| updated_at      | timestamp    | NO   |     | 0000-00-00 00:00:00 |                             |
| frozen_tag_list | text         | YES  |     | NULL                |                             |
| markup          | varchar(255) | YES  |     | NULL                |                             |
+-----------------+--------------+------+-----+---------------------+-----------------------------+
12 rows in set (0.00 sec)

updated_at と created_at の設定値が逆になってたのが原因。ALTER 文でカラムの情報を入れ替えておいた。

MySQL に流し込んだ Sqlite3 のデータを dump したやつの CREATE TABLE entries を見ると以下のようになってた。

CREATE TABLE `entries` (`id` integer PRIMARY KEY AUTO_INCREMENT, `user_id` integer, `category_id` integer, `slug` varchar(255), `title` varchar(255), `body` text, `type` text NOT NULL, `draft` boolean DEFAULT '0', `created_at` timestamp, `updated_at` timestamp, `frozen_tag_list` text, `markup` VARCHAR(255));

なんでこれが created_at に対して更新時に現在時間を上書きし、 updated_at は作成時のみ現在時刻が入るようになるのかは分からない。

それにしても日頃 MongoDB ばかり使っていて、データにおかしなことがあるのはほぼ間違いなくコードのせいだと思う癖がついていることが今回よくわかった。データベースのスキーマを見てみるまでに3日くらい時間がかかった。とほほ。

一方でデータベース内のデータ構造のことをあまり考えなくてよく、開発のみに集中すればよい MongoDB はやっぱり便利だなと思った。規模がでかくなったり高速な読み書きをさばかなければならない状況だと MongoDB にも色々問題があるのでしょうけどね。

Jekyllを使いだしてから気がつくと一年経ってました。いろいろ便利に使えており気に入っております。

PygmentでコードをシンタックスハイライトしたりLSIで関連記事表示したりと結構手を入れてはいたんだけど、いわゆる世間の一般のブログにあるようなカテゴリ一覧表示機能と、カテゴリごとの記事アーカイブ機能がなくて、それを若干不便に思っておりました。

ググってみたところ、プログラマー向けなブログツールなだけあっていろんな方法が出てきました。以下そのまとめ。

カテゴリ一覧

JekyllのLiquid (テンプレート言語) には {{ "{{ sites.categories " }}}} みたいタグがあるんだけど、こいつが意図したとおりに動かない。普通のRuby使いの感覚からすると site.categories ってカテゴリを沢山持った配列になってそうな気がするんだけどこれが違う。

<ul>
{{ "{{ for category in sites.categories " }}}}
  <li>{{ "{{ category.name " }}}}</li>
{{ "{{ endfor " }}}}
</ul>

↑みたいな感じのコード書くと何も表示されない。 site.categoires はHashで、{ "カテゴリ名" => カテゴリ内の記事一覧 } みたいな構造になってる。LiquidでHashのキーを取りだす方法が分からず、どうにもこうにもいかなかったので他の人が作っているプラグインを利用することにした。

my_jekyll_extensions/tag_category_iterator/tag_category_iterator.rb at master · rfelix/my_jekyll_extensions

Portable Jekyll extensions I use for my website (to be used with jekyll_ext) - rfelix/my_jekyll_extensions

github.com

↑のファイルを JEKYLL_ROOT/_plugins にコピーする。（_plugins というディレクトリがなければ作る）。そんでテンプレートを変更する。↑のやつを↓みたいにする。

<ul>
{{ "{{ for category in sites.iterable.categories " }}}}
  <li>{{ "{{ category.name " }}}}</li>
{{ "{{ endfor " }}}}
</ul>

2行目のところが変更点です。これでカテゴリ一覧表示ができるようになる。

カテゴリごとの記事一覧

カテゴリごとの記事一覧を表示する方法だけど、こういうのを発見した。

印コーディングサービス | 格安ハイクオリティーのインドに発注 | 完全日本人がサポートします | 完全日本人がサポートします | コーディングサービ

印コーディングサービス | デザインを入稿するだけで、誰でもHP制作、システム開発、アプリ開発ができます、日本人が完全サポート！インドへアウトソーシングすることで日本ではあり得なかった価格と技術を実現！

recursive-design.com

ここの generate_categories.rb を使えばカテゴリ内の記事一覧を作成できる。こんな感じ。

Ruby

森井ゴンザレス（ morygonzalez ）のブログ。 ブログを書くこと、プログラミング、インターネット、 Mac の便利なソフト、働くこと、ランニングについて書いています。

tech.portalshit.net

これもさっきのと同じように、JEKYLL_ROOT/_plugins にファイルをコピーする。そんでLiquidテンプレートを書き換えるんだけど詳細はプラグイン内の記事をご確認くだしあ。

ポータルシットをLokkaに置き換えたくていろいろやってる。ポータルシットの過去記事をYAMLでエクスポートし、それをLokkaのDBに取り込む作業をやってる。TDD BootCampに参加したので、テストファーストしながらの作業。RSpecでテストコードを書き、ログが正しくインポートできることを確認する。テスト終了時 after(:all) フックで、取り込んだログを削除してる。コードはこんな感じ。ちなみにLokkaはDataMapperをORMに採用してるので以下はDataMapperでの話。

after(:all) do
  Category.all.destroy
  Entry.all.destroy
end

しかしこれでは AUTO INCREMENT の値がリセットされず、テストを繰り返す度に AUTO INCREMENT の値が増えていってうざかった。

DataMapperの機能で AUTO INCREMENT 値をリセットするのってないのかなと5秒くらい探してみたけど見つからなかったので、SQLを直接実行する方法を採用した。

• DataMapperでSQLを直接実行する - Hello, world! - s21g

ちなみにRDBMSに採用してるのはSQLite3。SQLiteでは UPDTE sqlite_sequence SET seq=0 WHERE name='テーブル名'; みたいなコードで AUTO INCREMENT 値を任意の値に設定できるみたい。

• [悪徳商法？支店]: auto_incrementの値を1に戻す方法

最終的な after(:all) フックはこんな感じ。

  after(:all) do
    Category.all.destroy
    Entry.all.destroy
    Entry.repository.adapter.execute('update sqlite_sequence set seq=0 where name="entries";')
  end

テスト実行後にはすべてのデータがデータベースから削除されて、AUTO INCREMENT の値もリセットされる。人畜無害なテストコード万歳。

CakePHP、ちょこっと使ってみるだけのつもりだったんだけど、結構深くつきあってしまった。三つほどCakePHPでサイトつくりました。一つは社内用のウェブアプリケーションで、一つはまだ正式リリース前のものだけど、最後の一個はページビュー5000/日くらいあるサイトで実際に動いてます。ついこの前まで無職だったのに。スゲー。

PHPで素人がつくったサイトは危ないみたいな記事がこの前（というか定期的に）はてブでホッテントリに入ってた（る）けど、セキュリティのこととか分からない初心者こそCakePHPとかでサイトつくった方が楽だし安全だと思った。難しいことはフレームワークがやってくれるので。Bakeすればものの数分でウェブアプリケーションが出来てしまう。もちろんどんなフレームワークにも脆弱性がないわけじゃないだろうから100%安心というわけじゃないけど、少なくとも素人が自分でなんかやるよりも安全だと感じる。

とはいえ、フレームワークで万事オッケーなわけでもなかったりする。ちょこちょこっとカスタマイズするみたいのがフレームワークは難しい。特にCakePHPは規約がすごく重視されるから、データベースへのクエリでちょっと変わったことしようとすると結構難しくなる。というかはまる。サイト内検索をつくろうとして結構苦労した。土台が出来上がるまでは速いんだけど、そこからブラッシュアップさせていくときに結構停滞してしまう。それでも自分で一からつくるよりはかなり迅速に作れるんだけど、規約に縛られるのが窮屈に感じることもないではないですね。

で、タイトルの件なんだけど、真面目にエロサイトを作ってみた【プログラマ編】｜ASTRODEO という記事がおもしろかった。はてブで1200以上ブックマークされますが1ゲットは僕です。すごいでしょ。いや僕は全然すごくないですね。書いてある内容がすごい。

確かにエロとかスクレイピングとかまぁきわどい内容ではありますが、僕はCakePHPでこんだけのことをやったということに素直に驚いた。

例えばCakePHPには hasAndBelongsToMany というのがある。ブログ記事があったとして、これが一つのカテゴリーを持つ場合は、 Post テーブルと Category テーブルを結びつけてやるだけでOKなんだけど（ Post は一つの Category に所属し、 Category は複数の Post を持つ）、 Tag のような複数持てるし複数に所属する概念のモデルが存在する場合、 hasAndBelongsToMany じゃないとデータの整合性というか組み合わせをきちんと保つことが出来ない。

で、自分はこういうのの組み合わせは手が空いてる人に頼んで人力でやってもらったんだけど、このエロサイトの場合は、動画と動画の関連性の判定をプログラムにやらせてる。150件そこそこのデータの整合性を保つのも大変なのに、10000件とかそれ以上のデータを、しかも自動処理で関連づけるってまじすげーと思った次第です。

エロコンテンツなのに年齢確認がないとか著作権がらみの問題とかスクレイピングでよそのサイトに負荷かけるとかいろいろあるけど、僕は率直にこういうサイトをつくったのはスゲーなと思いました。こんなことまで出来るんだー、っていう素直な驚き。読んでて楽しかったしわくわくした。

今後もCakePHPを使い続ける分からんけど、自分もなんかおもしろいもんつくってみたいなーってすごく触発されました。

蛇足

はてブのコメント欄に「技術的には大したことない」みたいなコメント書いてる人が何人かいるけど、ほんとに大したことないんですかね。データベースを保存用と参照用で分けたり、スクレイピングしてきたデータの保存処理とか結構難しいと思うんだけど。これをすごいって感じるのはピヨピヨプログラマーだけなのかな？