как правильно и оптимлаьно построить сложный запрос?

[drug]

как правильно и оптимлаьно построить сложный запрос?

господа, сижу уже давно. сам неосилю..
существует ряд таблиц:

CREATE TABLE `TBL_EVENT` (
`id` int(11) NOT NULL auto_increment,
`organization_id` int(11) NOT NULL default '0',
`calendar_id` int(11) NOT NULL default '0',
`dtstamp` datetime NOT NULL default '0000-00-00 00:00:00',
`summary` tinytext collate latin1_bin NOT NULL,
`description` text collate latin1_bin,
`evtype` enum('onetime','recurrent') collate latin1_bin NOT NULL default 'onetime',
PRIMARY KEY (`id`),
KEY `EVENT` (`organization_id`,`calendar_id`,`evtype`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=1 ;

CREATE TABLE `TBL_ONETIME` (
`event_id` int(11) NOT NULL default '0',
`dtstart` datetime NOT NULL default '0000-00-00 00:00:00',
`dtend` datetime NOT NULL default '0000-00-00 00:00:00',
KEY `ONETIME` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

CREATE TABLE `TBL_RRULE1` (
`event_id` int(11) NOT NULL default '0',
`duration` datetime NOT NULL default '0000-00-00 00:00:00',
`frequency` enum('daily','weekly','monthly','yearly') collate latin1_bin default 'daily',
`until` datetime NOT NULL default '0000-00-00 00:00:00',
`count` int(11) default '1',
`interval` int(11) default '1',
KEY `RRULE1` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

CREATE TABLE `TBL_RRULE2` (
`event_id` int(11) NOT NULL default '0',
`rule` enum('byminute','byhour','byday','bymonthday','byyearday','byweekno','bymonth') collate latin1_bin NOT NULL default 'byminute',
`value` int(11) NOT NULL default '0',
KEY `RRULE2` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;


Это календарь событий.
Необходимо вывести ТБЛ1 если:

1. TBL_EVENT.evtype == 'onetime', то СМ. пункт 2., TBL_EVENT.evtype == 'recurrent', то СМ. пункт 3.

2. NOW(текущая дата_время) находится в интервале TBL_ONETIME.dtstart .... TBL_ONETIME.dtend

я это реализовал так:
SELECT * FROM TBL_ONETIME WHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(dtstart)) <= (UNIX_TIMESTAMP(dtend)-UNIX_TIMESTAMP(dtstart)))
уверен, что можно и оптимальнее, подскажите как

3. событие с продолжительностью 'duration', частотой 'frequency', до даты_времени 'until', c числом повторов 'count' и иентервалом 'interval', проходящее по 'rule' со значением 'value' охватывало NOW.

"проходящее по 'rule' со значением 'value' охватывало NOW" сделал так:
....WHERE rule = 'byminute' AND UNIX_TIMESTAMP($now) BETWEEN UNIX_TIMESTAMP($year.$month.$day.$hour.value) AND (UNIX_TIMESTAMP($now)+TIME_TO_SEC($duration))
как бы получше?


вот такой вот непростой запросик! =)
phpclub - последняя надежда..

 

[zerkms]

(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(dtstart)) <= (UNIX_TIMESTAMP(dtend)-UNIX_TIMESTAMP(dtstart))
очень забавно, это раз

....WHERE rule = 'byminute' AND UNIX_TIMESTAMP($now) BETWEEN UNIX_TIMESTAMP($year.$month.$day.$hour.value) AND (UNIX_TIMESTAMP($now)+TIME_TO_SEC($duration))

--- не запрос mysql, это два

1. TBL_EVENT.evtype == 'onetime' AND пункт 2 OR TBL_EVENT.evtype == 'recurrent' AND пункт3

2. текущая дата >= стартовая И текущая дата <= конечная

3. аналогично + ... AND `rule` = 'value'

ps: есть мнение, что после того как ты напишешь этот запрос, его будет весьма непросто оптимизировать под использование индексов, так что делаю предположение о неверной организации хранения данных

 

[drug]

2. текущая дата >= стартовая И текущая дата <= конечная

конечно! я только всё усложнил.. =))

3. аналогично + ... AND `rule` = 'value'

т.е. ...WHERE
(rule = 'byminute' AND UNIX_TIMESTAMP($now) BETWEEN UNIX_TIMESTAMP($year.$month.$day.$hour.value) AND UNIX_TIMESTAMP($now)+TIME_TO_SEC($duration)))
AND
(rule = 'byhour' AND UNIX_TIMESTAMP($now) BETWEEN UNIX_TIMESTAMP($year.$month.$day.value.$minute) AND UNIX_TIMESTAMP($now)+TIME_TO_SEC($duration)))
...
???
а как быть с частотой 'frequency', до даты_времени 'until', c числом повторов 'count' и интервалом 'interval'?

ps: есть мнение, что после того как ты напишешь этот запрос, его будет весьма непросто оптимизировать под использование индексов, так что делаю предположение о неверной организации хранения данных

если не так, то как правильно организовать?

вопорос жизненно важен! умельцы, поможите!!!

 

[Andreika]

drug
1. названия таблиц у тя какие то странные..
2. UNIX_TIMESTAMP() возвращает число... число это INT, а не DATETIME
3. первым запросом мы выбираем все ID шники событий, которые попадают во временные рамки (вместе с инфой о самих событиях)

[sql] SELECT * FROM tbl_event e LEFT JOIN tbl_onetime o ON (e.id=o.event_id)
WHERE e.evtype='onetime' AND UNIX_TIMESTAMP() BETWEEN o.dtstart AND o.dtend
[/sql]

[sql]
select * from tbl_event e LEFT JOIN tbl_rrule1 r ON (e.id=r.event_id) WHERE e.evtype='recurrent' AND r.until>UNIX_TIMESTAMP() AND r.count>0 AND MOD(UNIX_TIMESTAMP()-dtstamp, interval)<duration
[/sql]
это если dtstamp - время с которого идет отсчет, то UNIX_TIMESTAMP()-dtstamp - кол-во секунд, прошедших с этого времени, MOD(...) - кол-во секунд прошедших после того, как последний раз событие должно было произойти.. рискнул предположить, что duration это что-то вроде погрешности.. т.е. если duration 10*60, а скрипт запустится в 10:04, то он должен искать все события с 09:54 до 10:04

TBL_RRULE1 и TBL_ONETIME запросто объединяются в одну

CREATE TABLE `TBL_RRULE1` (
`event_id` int(11) NOT NULL default '0',
`duration` datetime NOT NULL default '0000-00-00 00:00:00',
`frequency` enum('daily','weekly','monthly','yearly') collate latin1_bin default 'daily',
`dtstart` datetime NOT NULL default '0000-00-00 00:00:00',
`dtend` datetime NOT NULL default '0000-00-00 00:00:00',
`count` int(11) default '1',
`interval` int(11) default '1',
KEY `RRULE1` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

что есть 'frequency' и что есть интервал непонятно.. по сути одно и тоже и хранить 'frequency' смысла нет

4. после каждого из запросов выбираем все данные и отдельно все найденные idшники

5. проверяем какие из них подходят под правила

 

[drug]

duration - время, которое длится событие

пронмаю, что TBL_RRULE1 и TBL_ONETIME. а надо ли оно? БД не оптмизирована будет (т.к. если есть dtstart, dtend, то нет всех значений из tbl_rrule1 и наоборот)

'frequency' и 'interval' не одно и то же, смотри
RRULE:FREQ=YEARLY;INTERVAL=4;BYMONTH=11;BYDAY=TU;BYMONTHDAY=2,3,4,5,6,7,8
событие происходит 'каждый' год (YEARLY) с интервалом в 4 года (2000, 2004, 2008..)

как быть с числом повторений события COUNT??

спасибо за помощь!

-~{}~ 29.04.06 11:13:

в запросе
...AND MOD( UNIX_TIMESTAMP( ) - dtstamp, frequency * interval ) < duration

FREQUENCY принимает значение day, week, month, year использовать FREQUENCY в секундах НЕ ВЕРНО, т.к. в каждом месяце (весокосном году)число секунд РАЗНОЕ!

как быть?

 

[Andreika]

да, действительно.. тада идем с обратной стороны

SELECT * WHERE проверка на подходящий интервал AND (id,rules_count)=SELECT event_id,COUNT(DISTINCT rule) WHERE ((rule='byhour' AND value=12) OR ...