sqliteを利用した日本語での全文検索実験

■概要

ひょんな事から、sqliteでの全文検索について調べました。
すると全文検索用に、fts3/4指定ができる事を発見したのですが、予想通り"スペース"での、単語区切りしかできないようです。
日本語を処理したかったので、自分でn-gramしたデータを登録してテストしてみました。
確かに高速に検索できます。

※ MeCabを利用したtokenizerとかはandroidでの利用を考え考察していません。

■テストデータ

郵便番号データを下記からダウンロードしました。
http://www.post.japanpost.jp/zipcode/dl/kogaki.html

全国版データを利用しました。
ちなみにcentos6.xではlhaコマンドを入手できなかったので、http://d.hatena.ne.jp/pcmaster/20100211/p3 を参考にインストールしました。

$ lha e ken_all.lzh
$ ruby gensql.rb ken_all.csv > ken_all.sql # 下記スクリプトで分割済みのフィールドを準備

■実験

$ sqlite3 a.sqlite
> create virtual table zip_codes using fts3
(
  code text
,  address text
,  search_field text
);

> begin;
> .read ken_all.sql
> commit;

> .timer ON;
> select code, address from zip_codes where address like ('%東成区%');
CPU Time: user 0.272959 sys 0.024997

> select count(*) from zip_codes where address like ('%東成区%');
CPU Time: user 0.262960 sys 0.032995

> select code, address from zip_codes where search_field match '東成 成区';
CPU Time: user 0.001000 sys 0.000000

> select count(*) from zip_codes where search_field match '東成 成区';
CPU Time: user 0.001000 sys 0.000000

なんと数値上は300倍近く高速化されましたw！
ちなみに検索文字列もn-gram分割する必要があります。

■(参考)データ加工用スクリプト

require 'csv'

# なんちゃって2-gram。英数字は特別扱いするとか正規化するとか工夫してください。
# javaで組めば幸せになるはず（？）
def gen_2_gram(org)
  result = []
  (org.length - 1).times { |i| result << org[i, 2] }
  result.join(" ")
end

# *.csvファイルを処理する。
ARGV.each do |file|
  CSV.foreach(file, "r:sjis") do |row|
    zip     = row[0]
    address = (row[6] + row[7] + row[8]).encode("utf-8")
    puts "insert into zip_codes (code, address, search_field) values (#{zip}, '#{address}', '#{gen_2_gram(address)}');"
  end
end

※"以下に掲載がない場合"とかは実験目的なので特に処理していません。

(初心者向け)SELECT文を考える時には最終アウトプットを強く意識する。

SELECT文を考える時、何となくjoinやgroup byとか使ってませんか？もしくはメインとなるテーブルを意識していますか？

SELECT文は非常に便利なので気づかないのですが裏で実行されている事は（非常に簡単に言うと）

　・縦＊横の二次元の表を付きあわせ、新たな縦＊横の二次元の表を作成

しているだけなのです。

その際に、一行の粒度が変わる事に注意が必要です。多重度が大きい側にjoinすると、自ずと一行の粒度が変わってしまう（行が増えてしまう）為、初心者のうちは自分が何をしているのか分からなくなります。

例えば次のER図があり、

最終的に次のような表が欲しい場合、どういうSQLを書きますか？

私は概ね次のステップでSELECT文を考えます。

・最終アウトプットの一行の粒度を確認する。

  この場合注文明細の行数が最終アウトプットの行数と同じ

・その粒度にあったテーブルをメインの表にする。

  from 注文明細を起点に考える

・そこに枝葉のテーブルを加える。その時に行の粒度を変えない（膨らまさない）。

  from 注文明細
    inner join 注文 on 注文.注文番号 = 注文明細.注文番号
    inner join 顧客 on 顧客.顧客NO = 注文.顧客NO
    inner join 商品 on 商品.商品NO = 注文明細.商品NO

from 注文からはじめる人も多いと思いますが、注文明細をjoinした時に行が膨れるため、考えを単純化する目的で、こんな簡単な場合でも徹底して明細側をメインテーブルにします。

あまりにも意図せず膨らましてしまい、group by/distinctでお茶を濁そうとする人が多そうなので記事にしてみました。もちろん集計が入ってくるとこの限りでは無いのですが、「最終アウトプットの一行の粒度」を意識する事は非常に重要です。

Amazon EC2でOracle-XEを動かす時の注意点

先日諸般の事情で、Amazon EC2にOracle11gXEをインストールして利用する必要があったのですが、一つ困った事があったのでメモ。

■問題
インストールも簡単にでき重宝していたのですが、EC2インスタンスをStop -> Startすると外部から接続できなくなりました。

FWとかSecurity Groupとか確認したのですが問題を発見できず。
サーバにログインしておもむろにプロセスを確認すると、何と ”リスナー” が起動していないのです！

■原因＆対処方法
EC2インスタンスをStop -> StartするとIPおよびホスト名が変わります。ところがOracle11gXEをインストールすると、listener.ora内にインストール時点のホスト名が埋まってしまいます。よって再起動後は前のホスト名に対してリスナーを起動しようとする為、上手くいかなります。

ホスト名を変更し、/etc/init.d/oracle-xe stop / startすると、めでたく起動できました！

■追記
↑な事をtwitterでぼやいていたら、Rails contributor仲間のyahondaさんより、「Host名を空にする」という方法を教えて頂きました。これだとインスタンス起動毎に変更しなくてよいので楽ちんです。

ActiveRecord自体のテストケースをOracleで動かす

■概要

ActiveRecord自体のテストケースは通常sqlite3/mysql/postgresqlで実行しますが、oracleで実行したくなり調べましたが少し手こずりました。手順を残しておきます。

■oracle-xeインストール準備

・swap領域が足りないので追加

# dd if=/dev/zero of=/swap.extended bs=1M count=1024
# mkswap /swap.extended
# swapon /swap.extended

# cat /proc/swaps # 確認
Filename                        Type            Size    Used    Priority
/dev/vda2                               partition       2096472 19024   -1
/swap.extended                          file            1048568 0       -2

# vim /etc/fstab # 再起動時のおまじない
...
/swap.extended          swap                    swap    defaults        0 0

■oracle関係インストール

・ここからoracle11g xeをダウンロード
  http://www.oracle.com/technetwork/database/enterprise-edition/overview/index.html

・oracle-xeインストール

# unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip
# cd Disk1
# rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm
# /etc/init.d/oracle-xe configure
入力は、9080(8080は他の用途で利用している...) => 1521 => パスワード => y
# /etc/init.d/oracle-xe status

・oracle clientインストール
  http://www.oracle.com/technetwork/jp/topics/index-099943-ja.html

# rpm -ivh oracle-instantclient11.2-basic-11.2.0.2.0.x86_64.rpm
# rpm -ivh oracle-instantclient11.2-devel-11.2.0.2.0.x86_64.rpm
# rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.2.0.x86_64.rpm

■ユーザ設定
・環境変数を設定

$ vim ~/.bash_profile
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib/:$LD_LIBRARY_PATH
export PATH=/usr/lib/oracle/11.2/client64/bin/:$PATH
export NLS_LANG=JAPANESE_JAPAN.AL32UTF8
$ source ~/.bash_profile

・テスト用ユーザ作成

$ sqlplus system/パスワード@localhost:1521/XE
SQL> create user arunit identified by arunit default tablespace USERS temporary tablespace TEMP;
SQL> create user arunit2 identified by arunit2 default tablespace USERS temporary tablespace TEMP;
SQL> grant connect,resource to arunit;
SQL> grant create session to arunit;
SQL> grant create synonym to arunit;
SQL> grant connect,resource to arunit2;
SQL> grant create session to arunit2;
SQL> grant create synonym to arunit2;

■gem準備

$ gem install ruby-oci8
$ git clone git://github.com/rsim/oracle-enhanced.git
$ git checkout -b rails4 origin/rails4
$ cd /home/kennyj/rails/activerecord
$ ORACLE_ENHANCED_PATH=/home/kennyj/oracle-enhanced bundle update

■テスト実行

$ ARUNIT_DB_NAME=localhost:1521/XE ARCONN=oracle \
  ORACLE_ENHANCED_PATH=/home/kennyj/oracle-enhanced \
  ruby -Itest:lib test/cases/schema_dumper_test.rb

ruby/rails勉強会54thで、ライトニングトークスさせて頂きました！

 2012/05/26に、Ruby関西様主催の「ruby/rails勉強会54th」で、ライトニングトークスをさせて頂きました。

スライドはこちら！

発表の是非は皆様のご判断を仰ぐとして、今後の為に思ったことを記載しておきます。

■練習・リハーサルは重要

• 前日に3時間程度練習しました。少ない！と言われるかもしれませんが、必要以上に緊張せずにはすみました。
• リハーサルにて時間が長すぎる事が分かったので、結果9枚(1/4程度)スライドを捨てました。中心的なメッセージ以外のくだりは、思い切って外しました。

■前回某所でのLTでは、練習では5分丁度だったのに、実際には  3.5分程度になってしまった。

• 練習をしてくるのは当然だが、使える機能は利用すべきと思った。
• マルチモニタ機能で、プレゼン画面と発表者用をわける。
• ちら見程度でも、時間の把握と次のスライドがわかるとよい。次のスライドが分かれば、先に話を進めてからスライドを切り替えるという、進め方を確実にする事が可能。
• もっと時間をかけて練習すれば、不要なのは理解しているのですが現実との兼ね合いで妥協。
• 音楽における”暗譜”と似てますね。

■反省点

• 5分という制約の中で、この題材は適切だったのか少し反省。
• 話題を詰め込みすぎて、喋りが慌ただしかったと思う。
• リモコン？が無いので、画面切り替え時にPCを操作しなければいけなかった。結果目線を落とす必要があり、また体を自由に使って表現が難しかった。
• スライドは自分のしゃべりを支える物と考えているのですが、いざslideshareにあげてみると、よく分からんだろうなと思いました。
• 「プレゼンテーションzen」の影響うけてますよね？と感想を頂いた。全くその通りで指摘頂き嬉しい半面、さらに精進して影響を消化したいなと思いました。

rails自体の開発環境を作る2012

■概要
さくらvpsを新プランに乗り換えたので、またまた"rails自体"の開発環境を作ります。

■パッケージ管理・導入(centos6.2 x86_64 2Gプラン)

○パッケージの最新化
# yum -y update
(yum -y upgrade)

○必要そうなパッケージをインストール
# yum -y install git nmap telnet

# yum -y install screen zsh

(# yum -y install httpd httpd-devel)

# rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
# yum --enablerepo=remi,remi-test -y install mysql mysql-server mysql-devel

# yum -y install sqlite sqlite-devel

# rpm -Uvh http://yum.postgresql.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm
# yum -y install postgresql91 postgresql91-contrib postgresql91-devel postgresql91-libs postgresql91-server

# yum --disablerepo=pgdg91 -y install libevent libevent-devel memcached memcached-devel

# yum -y install openssl-devel curl-devel readline-devel zlib-devel libxml2 libxml2-devel libxslt-devel libyaml-devel libffi-devel
# yum -y install java-1.6.0-openjdk java-1.6.0-openjdk-devel java-1.6.0-openjdk-src

(参考) http://www.if-not-true-then-false.com/2010/install-mysql-on-fedora-centos-red-hat-rhel/
(参考) http://www.oss-d.net/postgresql/9.1

■設定

○日本語化
# vim /etc/sysconfig/i18n
LANG="C" から LANG="ja_JP.UTF-8" に変更

○不要デーモンの停止設定
# chkconfig auditd off
# chkconfig haldaemon off
# chkconfig mdmonitor off
# chkconfig messagebus off
# chkconfig netfs off
# chkconfig restorecond off
# chkconfig smartd off

(参考) http://tanaka.sakura.ad.jp/archives/001065.html

○FW設定
# system-config-firewall-tui
SSH/HTTPS/HTTP/3000(tcp)
後は未設定

○時間同期設定
(さくらVPSでは最初から設定されていました)
# yum -y install ntp
# vim /etc/ntp.conf
# ntpdate ntp.nict.jp (とりあえず一旦近づけておく)
# /etc/init.d/ntpd start
# chkconfig ntpd on

○selinux停止
(さくらVPSでは最初から設定されていました)
# vim /etc/sysconfig/selinux
SELINUX=disabled
# reboot

○作業用ユーザ作成
# useradd kennyj
# passwd kennyj

○sudo設定
# /usr/sbin/visudo
(一番下に追記)
kennyj  ALL=(ALL)       ALL

○ssh設定
# vim /etc/ssh/sshd_config
(42行目)
PermitRootLogin no
(47行目)
RSAAuthentication yes
PubkeyAuthentication yes
AuthorizedKeysFile .ssh/authorized_keys
(65行目)
PermitEmptyPasswords no
PasswordAuthentication no

# /etc/init.d/sshd restart

# su - kennyj
$ ssh-keygen -t rsa
$ mv ~/.ssh/id_rsa.pub ~/.ssh/authorized_keys
(秘密鍵をローカルにダウンロード。秘密鍵のみでログインできる事を確認する。)
$ rm ~/.ssh/id_rsa
$ exit

○postgresql準備
# service postgresql-9.1 initdb

○mysql準備
# vim /etc/my.cnf
(文字列エンコーディングをUTF-8に設定)
[mysqld]
...
character-set-server = utf8

[mysqld_safe]
...
character-set-server = utf8

[mysql]
...
default-character-set = utf8

# mysql_install_db
# service mysqld start
# mysql_secure_installation

○必要に応じてサービス起動設定
(# chkconfig httpd on)
# chkconfig mysqld on
# chkconfig postgresql-9.1 on
# chkconfig memcached on

○再起動
# reboot

○サービス稼動確認
# ps aux | grep -E 'http|postgre|mysql|memcache'

■rails環境構築

○javascript関係インストール
# wget http://nodejs.tchol.org/repocfg/el/nodejs-stable-release.noarch.rpm
# yum localinstall --nogpgcheck nodejs-stable-release.noarch.rpm
# yum install nodejs-compat-symlinks npm

(# npm install express -g)
(# npm install jade -g)
(# npm install socket.io -g)
# npm install coffee-script -g

(参考) https://github.com/joyent/node/wiki/Installing-Node.js-via-package-manager

○ruby関係インストール

(kennyjユーザで)
$ curl https://raw.github.com/wayneeseguin/rvm/master/binscripts/rvm-installer | bash -s stable
$ source ~/.rvm/scripts/rvm
$ rvm install ree-1.8.7-2012.02
$ rvm install 1.9.3-p194
$ rvm install jruby-1.6.7

$ rvm use --default 1.9.3
($ rvm ree,1.9.3,jruby do gem install rails --no-rdoc --no-ri)

■rails開発環境

○mysqlの設定
$ mysql -u root
mysql> GRANT ALL PRIVILEGES ON activerecord_unittest.*  to 'rails'@'localhost';
mysql> GRANT ALL PRIVILEGES ON activerecord_unittest2.* to 'rails'@'localhost';
mysql> flush privileges;
mysql> quit

○postgresqlの設定
$ sudo -u postgres createuser --superuser $USER

○github公開鍵の準備
(参考) http://help.github.com/win-set-up-git/

○gitの設定
$ git config --global user.name kennyj # 名前
$ git config --global user.email kennyj＠gmail.com # メールアドレス

○gitの補完機能設定
$ cd $HOME
$ wget https://raw.github.com/gitster/git/master/contrib/completion/git-completion.bash -O .git-completion.bash
$ vim $HOME/.bashrc
source $HOME/.git-completion.bash
PS1='\w$(__git_ps1 "(%s)")\$ '

$ souce ~/.bashrc

○rails開発環境
$ git clone git://github.com/(自分)/rails.git (先にgithub上でforkしておく)
$ cd rails
$ gem install pg -- --with-pg-config=/usr/pgsql-9.1/bin/pg_config
$ bundle update
$ bundle exec rake mysql:build_databases
$ bundle exec rake postgresql:build_databases
○hstore準備
$ psql activerecord_unittest
> CREATE EXTENSION hstore;
> \q
$ psql activerecord_unittest2
> CREATE EXTENSION hstore;
> \q
○テスト稼動確認
$ cd ..
$ bundle exec rake (テスト稼動確認)

○docrailsも準備
$ cd $HOME
$ git clone git://github.com/lifo/docrails.git

○適宜関連gemの開発環境
$ git clone git://github.com/(自分)/mail.git
$ git clone git://github.com/(自分)/journey.git
$ git clone git://github.com/(自分)/sprockets.git
$ git clone git://github.com/(自分)/sprockets-rails.git
...

ActiveRecord自体のテストケースを実行する際にDBを選ぶ方法

■概要

ActiveRecord自体のテストケースを、"個別に"実行する際のデフォルトDBは、sqlite3です。これを切り替える方法が、長らくわからず時間を浪費していました。

■方法

わかってしまえば簡単です。ARCONNで指定できます。

$ cd activerecord
$ ARCONN=mysql2 ruby -Ilib:test test/cases/base_test.rb