繰り返し予定をデータベースで表現する
スケジューラとかを作ってていつも悩むのが繰り返し予定をRDBに保存する方法。
色々なWEBアプリのスケジューラを見た感じではiCalendarのrruleでやるのが多いみたいだけど、何となくDBとの相性が悪そうな気がしてならない。
繰り返し予定の要件としては、
- 繰り返し期間を指定できる
- 月を指定できる（例：1,3,5月）
- 週を指定できる（例：月の1週目と3週目）
- 曜日を指定できる（例1：月、火曜、例2：第２、第３土曜日）
- 日を指定できる（例：1,11,21,31日）
くらいできれば組み合わせ次第で大体の繰り返し表現はできるんじゃないかと思う。
これをDBに保存する場合検索時の効率の良さを考えるとどうするのが一番良いんだろうか？その辺が悩みどころ。
とりあえず正解ではないかもしれないけど、自分なりにコレだと思うやり方でやってみることにする。
まず、それぞれの繰り返し表現をビットフラグに置き換える。
1,3,5月の第2月,火,水曜日の予定の場合は以下のようになる。
- 月：101010000000（1,3,5月）
- 週：11111（全ての週）
- 曜日：0101010（月,火,水曜）
- 曜日(X番目)：01000（2番目）
- 日：1111111111111111111111111111111（全ての日）
で、これらを10進数に置き換えてをDB(Mysql)に保存する。テーブルは以下のような感じで設計
CREATE TABLE `event_recurrence` ( id integer primary key auto increment, title varchar(255) not null, content text, start date not null, until date, month integer unsigned not null default 0, week intrger unsigned not null default 0, day integer unsigned not null default 0, dnum integer unsigned not null default 0, date integer unsigned not null default 0, index (start,until,month,week,day,dnum,date) );
今回は大して関係ないがunsingedないと整数範囲が狭くなるので一応つけておく。
準備ができたらそれぞれ10進数に変換してレコードを追加する。
INSERT INTO `event_recurrence` (title,content,start,until,month,wee,day,dnum,date)
VALUES ('test','test','2008-2-16','2009-2-15',2688,31,42,8,2147483647);検索は対象となる日を同じようにビット->10進数に変換し、ビット演算で行う。
たとえば、2008年3月10日の場合、
- 3月：001000000000 => 512
- 3週目：00100 => 4
- 月曜日：0100000 => 32
- 2番目の曜日：01000 => 8
- 10日：0000000001000000000000000000000 => 2097152
となるので以下のようなクエリを発行する
SELECT * FROM `event_recurrence` WHERE start <= '2008-3-10' AND until >= '2008-3-10' AND (month & 512) = 512 AND (week & 4) = 4 AND (day & 32) = 32 AND (dnum & 8) = 8 AND (date & 2097152) = 2097152; +----+-------+---------+---------------------+---------------------+------------+-----+------+------+-------+ | id | title | content | start | until | date | day | dnum | week | month | +----+-------+---------+---------------------+---------------------+------------+-----+------+------+-------+ | 1 | test | test | 2008-02-18 00:00:00 | 2009-02-17 00:00:00 | 2147483647 | 42 | 8 | 31 | 2688 | +----+-------+---------+---------------------+---------------------+------------+-----+------+------+-------+ 1 row in set (0.00 sec)
まぁ良い感じに検索できた。対象日をビットに変換するのなんてDBのインデックスの恩恵を考えれば大したことじゃないので割と実用的な気がしないでもない。
あー、でも対象日が複数の場合は1回のクエリで検索しようとすると困るかもしれない。どうしたものか。。
結局の所、繰り返し日をすべて展開してDBに放り込む方がいいのかもしれない。ふむ。
他にもっと良い方法があれば教えてください。
ちなみに今回変換したのプログラムは以下のような感じ。
<?php /** * month,week,day,dnum,date ot date to bit * * [2008/3/10] * -month: Mar => 001000000000 => 512 * -week: 3rd => 00100 => 4 * -day: Mon => 0100000 => 32 * -dnum: 2nd => 01000 => 8 * -date: 10 => 000000000100000000000000000000 => 1048576 * * @param string $date string of date (ex. 2008-3-10||2008/3/10) * * @return object (ex. month=512, week=4, day=32, dnum=8, date=1048576) */ function date_to_bit($date_string, $week_start = 0) { list($year,$month,$date,$day) = explode('-', date('Y-n-j-w', strtotime($date_string))); $first_day = date('w', mktime(0,0,0,$month,1,$year)); $week = ceil(($date+$first_day-$week_start)/7); $week = ($first_day<$week_start)?$week+1:$week; $dnum = ceil($date/7); $week = bindec(substr_replace('00000', '1', $week-1, 1)); $dnum = bindec(substr_replace('00000', '1', $dnum-1, 1)); $day = bindec(substr_replace('0000000', '1', $day, 1)); $month = bindec(substr_replace('000000000000', '1', $month-1, 1)); $date = bindec(substr_replace('0000000000000000000000000000000', '1', $date-1, 1)); return (object)array('month'=>$month,'week'=>$week,'day'=>$day,'dnum'=>$dnum,'date'=>$date); } /** * array or string to bitFlag => integer * * -month: Jan,Feb,Dec => array(1,2,12) => 110000000001 => 3073 * -week: 1st,3rd => array(1,3) => 10100 => 20 * -day: Sun,Wed => array(0,2) => 1001000 => 72 * -dnum: 1st,3rd => array(1,3) => 10100 => 20 * -date: 11,21,31 => array(11,21,31) => 000000000010000000001000000001 => 524801 * * @param string $type month||week||day||dnum||date * @param array||string $values array(1,3,5)||'1,3,5' * * @return integer */ function array_to_bit($type, $values) { if(is_string($values)) { return array_to_bit($type, explode(',', $values)); } elseif(is_array($values)) { $values = array_map('trim',array_values($values)); $bit = ''; $start = $end = 1; switch($type) { case 'week': case 'dnum': $end = 5; break; case 'month': $end = 12; break; case 'day': $start = 0; $end = 6; break; case 'date': $end = 31; break; } for($i=$start;$end>=$i;$i++){ $bit.=(in_array($i,$values))?'1':'0'; } return bindec($bit); } else { return 0; } } /** * integer to bitFlag => array * * -month: 3073 => 110000000001 => array(1,2,12) * -week: 20 => 10100 => array(1,3) * -day: 72 => 1001000 => array(0,2) * -dnum: 20 => 10100 => array(1,3) * -date: 524801 => 10000000001000000001 => array(11,21,31) * * @param string $type month||week||day||dnum||date * @param integer $int * * @return array */ function bit_to_array($type, $int) { $values = array(); if(is_numeric($int)) { $bit = (string)decbin((int)$int); $end = $num = 0; switch($type) { case 'week': case 'wnum': $end = 5; $num = 1; break; case 'month': $end = 12; $num = 1; break; case 'wday': $end = 7; break; case 'day': $end = 31; $num = 1; break; } $bit = str_pad($bit, $end, '0', STR_PAD_LEFT); for($i=0;$end>$i;$i++){ if($bit[$i]=='1') $values[] = $i+$num; } } return $values; }
