2009-08-07

ユーザーデータを集計して、ランキングを取りたかった時のお話し。

userテーブルのcountカラムを集計して、rankテーブルにデータを登録（UPDATE）するsqlです。

---- 上のデータをrankテーブルにアップデート
UPDATE ( 
db.rank r
left join(
	SELECT r1.uid, r1.count, COUNT( r2.uid ) +1 as rank
	FROM (SELECT uid, count FROM user) AS r1
	LEFT JOIN 
	 (SELECT uid, count FROM user) AS r2
	 ON r1.count < r2.count
	GROUP BY r1.uid
	) b
	 on  r.uid = b.uid 
)
SET r.battle = b.rank;

こういうsql作ってると、Mysqlって不便な事がまだまだありますね。
oracleならもーちっと楽ちんだった気が、、、
たしか、「rank() over(order by count)」で順位付けできたです。

あと、UPDATE文の構造も話をややこしくしていると思う。
http://dev.mysql.com/doc/refman/4.1/ja/update.html
この構造は僕の頭には負荷が高すぎるorz

続き、、、以外と簡単に解決したお話し。

2009-08-04

データベースを行単位で他のテーブルにコピーするSQL文のお話し。

DB sql

テーブルをコピーする時にこんな書き方をするかと思います。

INSERT INTO `table`.`test_copy` SELECT * FROM `table`.`test`;

要は、insert文のvalueをselectで埋めてる感じ。
このやり方を初めて見た時はちょっと感動だったのが懐かしです。

行単位にコピーする場合は、まぁ行を指定するだけですね。
where句を追加して、

INSERT INTO `table`.`test_copy` SELECT * FROM `table`.`test` 
WHERE `id` = 123456;

これで指定のidを持つ行だけコピーできます。

INSERT INTO `table`.`test_copy` SELECT * FROM `table`.`test` 
WHERE `create` > '2009-08-01 00:00:00';

上の様に日付を指定すると、8/1以降に制作されたデータのみコピーとかもできます。

この書き方の何が良いかというと、やはり速度ですね。
プログラムでinsertをloopさせて投げるのと比べると、圧倒的に速い！

10,000件を超えるデータのinsertをphpでちまちまforで行うのはさすがにできませんよね。
でもDB側で処理を完結できるなら、十数カラム程度のデータを数万件コピーするぐらいなら、ごく普通にできます。

実用例だと

INSERT INTO `table`.`mail_list` SELECT null, {$id}, user_id, 0 FROM `table`.`users` 
WHERE `active` = 1 and `mail_error` < 2 limit 0, 10000;

こんな感じでユーザー情報をメール配信先リストにコピーしたりするのに使えます。
selectにjoinとか使ったり、軽い演算なんかも使えるので、かなり幅広い使い方ができます。たまにsql文を考えてると面白いですね。

2009-07-24

携帯メールアドレスの正規表現

php メール正規表現携帯

簡単に3キャリアの有効な携帯メールアドレスかチェックしたい時のお話し。

// 携帯のアドレスかチェックする。簡易版。
$mobileAddress = '^([a-z0-9])+([a-z0-9\._-])*@(docomo|ezweb|softbank|([c-t]{1}\.vodafone)|disney).ne.jp$';
if (!preg_match("/$mobileAddress/i", $mailFrom)) {
    // 携帯ではなかったら、終了。
    exit;
}

＠の前については、キャリアの仕様が終わってるので、無視です。
だいたい認証用のメールを送って有効性の確認は行うであろうという前提で、ドメインさえチェックすればOKではないでしょうか。

2009-07-14

暗号化・復号ライブラリ「mcrypt」をwindows環境にインストールする

php 復号 mcrypt

mcryptをローカル環境でも動かしたかったので、インストールしてみた時のお話し。
ちなみに環境は、32bit、XP、ZendCore2.5(php5.2.6 Apache/2.2.10(Win32))です。

コンパイル済みの mcrypt バイナリを以下からダウンロードする。

http://files.edin.dk/php/win32/mcrypt/

これを、「C:\WINDOWS\system32」に配置。
php.iniに設定を追記。

extension=php_mcrypt.dll
;mcrypt.algorithms_dir="C:\Program Files\Zend\Core\lib\php"
;mcrypt.modes_dir="C:\Program Files\Zend\Core\lib\php"

最後のコメントアウトされた２行は必須ではないようなので、外してあります。

これで再起動したらOKでした。

2009-07-14

Mcrypt 関数で暗号化して、復号してみた

php mcrypt 復号

ハッシュ化したデータを元に戻したかったので、mcryptを使ってみたお話し。

ハッシュを元に戻す方法が見あたらないので、暗号化と復号で対応してみることに。
以下、動くコード。

echo 'date: '.$data.'<br>';
$base64_data = base64_encode($data);
$td = mcrypt_module_open(MCRYPT_BLOWFISH, '',  MCRYPT_MODE_CBC, '');
// win は「MCRYPT_RAND」を使用する
$iv = mcrypt_create_iv(mcrypt_get_iv_size(MCRYPT_BLOWFISH, MCRYPT_MODE_CBC), MCRYPT_DEV_RANDOM);
$ks = mcrypt_enc_get_key_size($td);
$key = substr(md5('very secret key'), 0, $ks);
mcrypt_generic_init($td, $key, $iv);
echo '暗号化 : ';
echo $encrypted = mcrypt_generic($td, $base64_data);
mcrypt_generic_deinit($td);
echo '<br>暗号化enc : '.$aaa = base64_encode($encrypted);
echo '<br>暗号化 dec: '.$bbb = base64_decode($aaa);
echo '<br >';
echo 'デコードあってる？ : ' . ($encrypted == $bbb ? 'true' : 'false');
echo '<br >';
// 復号処理
mcrypt_generic_init($td, $key, $iv);
echo 'decrypted : ';
echo $decrypted = base64_decode(mdecrypt_generic($td, $encrypted));
echo '<br>元通り？ : ' . ($data == $decrypted ? 'true' : 'false');
mcrypt_generic_deinit($td);
mcrypt_module_close($td);

コピペのしまくって、動くことまで確認できました。

しかし、暗号化されるデータをエンコードしてみると、「VqNla0iQ/paVp+QhcaqZYg==」こんなんとかで、あまりよろしくない文字列になっている。

このデータをURLに使いたいわけです。はい。

2009-07-14

ZendFrameworkのルーターを使わないルーティング

Zend Framework tips

モジュールごとにルーターを作るのがめんどくさいので、コントローラー側でルーティングを実装させてみた話。

普通なら、

$route = new Zend_Controller_Router_Route(
        'blog/archives/:id/*',
        array(
            'module' => 'blog',
            'controller' => 'archives',
            'action'     => 'view',
            'id'         => 'default'
        )
);
$front->getRouter()->addRoute('default', $route);

こうやってルートを追加するわけで、モジュールごとに変なルーティングさせたくてしょうがない時は、10個ぐらいルートを書くことになって大変なので、動的にモジュールの設定を読み込んでアクセスされたモジュールのルーターのみ有効になるようにしてるんだけど、この辺はまた別のお話。

結局、modulesディレクトリ以下に設定を記述しても、いろいろめんどくさい訳です。
無駄なことが大好きな、面倒くさがり屋な自分は、モジュールごとにルーティング方法を変えてみたりしているうちに、ルーターに飽きてしまいました。

そして、コントローラの中で完結できるルーティング方法を考えてみました。

上のルートの設定の様に、「http://domain.com/blog/archives/{articleId}/」でアクセスできる様にします。もちろんルーターを使わずに。

とりあえず、コントローラはこんな感じ。

class blog_archivesController extends ll_ActionController {

    public function init()
    {
        $this->_forward('default');
        $this->_articleId = $this->getRequest()->getUserParam('action');
    }

    public function default()
    {
        // $this->_articleIdの記事を取得する適当な処理
    }
}

以上。終わり。

エラーが出ない理由とかは知りませんが、アクションにidを突っ込んで、actionをinitで書き換えて、全部defaultに飛ばしてしまいます。
getActionでは'default'が取得されてしまうので、getUserParam('action')でgetRequest()の'action'の値をarticleIdとします。

ちなみにコントローラもdefaultで指定すれば、「http://domain.com/blog/{articleId}/」の形でも動く、かも。

※l_ActionControllerはZendのコントローラのラッパーです。特に何もしてません。

2009-07-02

夏ですね〜 twitter始めました

はじめました

http://twitter.com/shinjuku_pg