Version 2 (modified by benoitg, 13 years ago)

A few comments

Contributors: Robin Jones, Benoit Grégoire, Last update: 2007-12-23 Feel free to contribute and/or format better

Supporting hotspot opening hours

I would like to get the ball rolling on the implementation of an Opening Hours ruleset:


First stab at the WiFidog?-postgress-schema.sql

CREATE TABLE hotspotopenhours
         node_id text NOT NULL,
         open_time text NOT NULL,
         close_time text NOT NULL,
         day_of_week text DEFAULT 0 NOT NULL,                    //0 - 7,  0 = all
         day_of_month text DEFAULT 0 NOT NULL,                   //0 - 31, 0 = all
         month_of_year text DEFAULT 0 NOT NULL,                  //0 - 12, 0 = all
         specific_date date

B: Not a bad start. Ideally, we want a little more expressiveness to allow for some holidays defined as the second monday of october, or the monday before may 25th). One (bad) way I can think of is to say that if both day_of_month and day_of_week are defined, the date will be the first day_of_week that is on or after day_of_month. But the SQL to calculate that may be quite ugly. Also, while the UI will provide an opening and closing time, we probably do not want to store the closing time. Instead, we want to store the opening duration. That way, we can deal cleanly with places that are open across a date change (such as bars which may be open from 3pm to 3am).


And my probably awfull and incorrect stab at an sql statement to process the rule! I have not gone through the code enough to find where this statement should be put and also the Vars are probably incorrect but hopefully readable!

SELECT * FROM hotspotopenhours 
WHERE $nodeid = node_id 
AND ($timestamp >= open_time 
AND $timestamp < close_time) 
    $dayofweek = day_of_week 
    AND $dayofmonth = day_of_month 
    AND $monthofyear = month_of_year 
    OR date = now) 
THEN true 
ELSE false

B: Is see some issues with that query:

  • While that query may do for now, we need a much more complex query than that if we want this to scale well: we need to find the date of the next transition form open to closed (and ideally vice-versa). That way, once we have tokens, we won't have to re-run the algorithm at every ping.

The only other thing I can see that is wrong is it would be prefered that specified dates would have priority, then day of week, month etc with a value greater than 0 and lastly those with a value of 0. This may be done through an IF statement.

if specified_date is not null then select return  
 end if  
 If day_of_week OR dat_of_month OR month_of_year < 0 then return  
 end if 

B: It may be simpler and faster to do it through clever use of ORDER BY and LIMIT 1

Bypassing opening hours for some users

lastly I thought about an administrator/MAC passthrough rule, but don't know the best way of achieving this yet!

B: This is easy with the new role system, by simply adding a NODE_BYPASS_OPENING_HOURS permission.