Changes between Version 1 and Version 2 of doc/developer/NetworkOpenHours

Show
Ignore:
Timestamp:
12/23/07 19:35:44 (13 years ago)
Author:
benoitg
Comment:

A few comments

Legend:

Unmodified
Added
Removed
Modified
  • doc/developer/NetworkOpenHours

    v1 v2  
    1 I would like to get the ball rolling on implimentation of Open Hours ruleset: [[BR]] 
     1Contributors: Robin Jones, Benoit Grégoire, Last update: 2007-12-23 
     2Feel free to contribute and/or format better 
     3[[PageOutline]] 
     4= Supporting hotspot opening hours = 
     5I would like to get the ball rolling on the implementation of an Opening Hours ruleset: 
    26 
    3    
    4    
    5 sorry if this is terrible coding but I haven't done SQL since I left college... but I wanted to keep the thread alive and will continue to develop the code (with all your help of course :)) [[BR]] 
     7= Schema = 
     8First stab at the WiFidog-postgress-schema.sql 
    69 
    7    
    8    
    9    
    10 my first stab at the WiFidog-postgress-schema.sql [[BR]] 
    11  
    12    
    1310 
    1411{{{ 
     
    2017         day_of_month text DEFAULT 0 NOT NULL,                   //0 - 31, 0 = all 
    2118         month_of_year text DEFAULT 0 NOT NULL,                  //0 - 12, 0 = all 
    22          specified_date date, 
    23          rule_active boolean DEFAULT false NOT NULL, 
    24          adminpassthrough boolean DEFAULT true NOT NULL 
     19         specific_date date 
    2520}}} 
    2621  
    27    
    28    
    29    
    30 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!  
     22B: 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). 
     23 
     24= Query = 
     25And 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!  
    3126   
    3227 
    3328{{{ 
    3429IF  
    35 SELECT * FROM hotspotopenhours WHERE rule_active = true AND $nodeid = node_id AND ($timestamp >= open_time AND $timestamp < close_time) AND ($dayofweek = day_of_week AND $dayofmonth = day_of_month AND $monthofyear = month_of_year OR date = now)  
    36  
     30SELECT * FROM hotspotopenhours  
     31WHERE $nodeid = node_id  
     32AND ($timestamp >= open_time  
     33AND $timestamp < close_time)  
     34AND ( 
     35    $dayofweek = day_of_week  
     36    AND $dayofmonth = day_of_month  
     37    AND $monthofyear = month_of_year  
     38    OR date = now)  
    3739THEN true  
    38  
    3940ELSE false 
    4041}}} 
    41   
    42    
    43    
    44 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.  
    4542 
    46 This may be done through an IF statement...  
    47    
    48    
    49   
     43B: Is see some issues with that query: 
     44 * 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. 
     45 
     46 
     47The 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. 
    5048{{{ 
    5149if specified_date is not null then select return   
     
    5856else 
    5957}}} 
    60   
     58B: It may be simpler and faster to do it through clever use of ORDER BY and LIMIT 1 
    6159 
     60= Bypassing opening hours for some users = 
     61lastly I thought about an administrator/MAC passthrough rule, but don't know the best way of achieving this yet!  
    6262 
    63 lastly I thought about an administrator/MAC passthrough rule, but don't know the best way of achiving this yet!  
     63B:  This is easy with the new role system, by simply adding a NODE_BYPASS_OPENING_HOURS permission.