Revision: 1862
http://mrbs.svn.sourceforge.net/mrbs/?rev=1862&view=rev
Author: cimorrison
Date: 2011-08-07 17:27:39 +0000 (Sun, 07 Aug 2011)
Log Message:
-----------
Added checking of the web for more up to date versions of the VTIMEZONE
components used in iCalendar notifications. (Useful if countries change their
timezone or DST rules). Also added caching of the latest version in the
database. Web checking will timeout if there is no connection, and it can
also be disabled completely.
Modified Paths:
--------------
mrbs/trunk/tables.my.pre41.sql
mrbs/trunk/tables.my.sql
mrbs/trunk/tables.pg.pre73.sql
mrbs/trunk/tables.pg.sql
mrbs/trunk/web/config.inc.php
mrbs/trunk/web/dbsys.inc
mrbs/trunk/web/functions_ical.inc
mrbs/trunk/web/systemdefaults.inc.php
Added Paths:
-----------
mrbs/trunk/web/upgrade/28/
mrbs/trunk/web/upgrade/28/mysql.sql
mrbs/trunk/web/upgrade/28/pgsql.sql
Modified: mrbs/trunk/tables.my.pre41.sql
===================================================================
--- mrbs/trunk/tables.my.pre41.sql 2011-08-05 10:56:25 UTC (rev 1861)
+++ mrbs/trunk/tables.my.pre41.sql 2011-08-07 17:27:39 UTC (rev 1862)
@@ -126,6 +126,17 @@
PRIMARY KEY (id)
);
+CREATE TABLE mrbs_zoneinfo
+(
+ id int NOT NULL auto_increment,
+ timezone varchar(255) DEFAULT '' NOT NULL,
+ outlook_compatible tinyint unsigned NOT NULL DEFAULT 0,
+ vtimezone text,
+ last_updated int NOT NULL DEFAULT 0,
+
+ PRIMARY KEY (id)
+);
+
CREATE TABLE mrbs_users
(
/* The first four fields are required. Don't remove. */
@@ -139,6 +150,6 @@
);
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ( 'db_version', '27');
+ VALUES ( 'db_version', '28');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ( 'local_db_version', '1');
Modified: mrbs/trunk/tables.my.sql
===================================================================
--- mrbs/trunk/tables.my.sql 2011-08-05 10:56:25 UTC (rev 1861)
+++ mrbs/trunk/tables.my.sql 2011-08-07 17:27:39 UTC (rev 1862)
@@ -126,6 +126,17 @@
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE mrbs_zoneinfo
+(
+ id int NOT NULL auto_increment,
+ timezone varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci
DEFAULT '' NOT NULL,
+ outlook_compatible tinyint unsigned NOT NULL DEFAULT 0,
+ vtimezone text CHARACTER SET utf8 COLLATE utf8_general_ci,
+ last_updated int NOT NULL DEFAULT 0,
+
+ PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
CREATE TABLE mrbs_users
(
/* The first four fields are required. Don't remove. */
@@ -139,6 +150,6 @@
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ( 'db_version', '27');
+ VALUES ( 'db_version', '28');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ( 'local_db_version', '1');
Modified: mrbs/trunk/tables.pg.pre73.sql
===================================================================
--- mrbs/trunk/tables.pg.pre73.sql 2011-08-05 10:56:25 UTC (rev 1861)
+++ mrbs/trunk/tables.pg.pre73.sql 2011-08-07 17:27:39 UTC (rev 1862)
@@ -119,6 +119,15 @@
variable_content text
);
+CREATE TABLE mrbs_zoneinfo
+(
+ id serial primary key,
+ timezone varchar(255) DEFAULT '' NOT NULL,
+ outlook_compatible smallint NOT NULL DEFAULT 0,
+ vtimezone text,
+ last_updated int NOT NULL DEFAULT 0
+);
+
CREATE TABLE mrbs_users
(
/* The first four fields are required. Don't remove. */
@@ -130,6 +139,6 @@
);
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ('db_version', '27');
+ VALUES ('db_version', '28');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ('local_db_version', '1');
Modified: mrbs/trunk/tables.pg.sql
===================================================================
--- mrbs/trunk/tables.pg.sql 2011-08-05 10:56:25 UTC (rev 1861)
+++ mrbs/trunk/tables.pg.sql 2011-08-07 17:27:39 UTC (rev 1862)
@@ -103,7 +103,7 @@
CREATE TABLE mrbs_repeat
(
- id serial primary key,
+ id serial primary key,
start_time int DEFAULT 0 NOT NULL,
end_time int DEFAULT 0 NOT NULL,
rep_type int DEFAULT 0 NOT NULL,
@@ -132,6 +132,15 @@
variable_content text
);
+CREATE TABLE mrbs_zoneinfo
+(
+ id serial primary key,
+ timezone varchar(255) DEFAULT '' NOT NULL,
+ outlook_compatible smallint NOT NULL DEFAULT 0,
+ vtimezone text,
+ last_updated int NOT NULL DEFAULT 0
+);
+
CREATE TABLE mrbs_users
(
/* The first four fields are required. Don't remove. */
@@ -143,6 +152,6 @@
);
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ('db_version', '27');
+ VALUES ('db_version', '28');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ('local_db_version', '1');
Modified: mrbs/trunk/web/config.inc.php
===================================================================
--- mrbs/trunk/web/config.inc.php 2011-08-05 10:56:25 UTC (rev 1861)
+++ mrbs/trunk/web/config.inc.php 2011-08-07 17:27:39 UTC (rev 1862)
@@ -11,6 +11,10 @@
* Booking System", "room" and "area").
**************************************************************************/
+/**********
+ * Timezone
+ **********/
+
// The timezone your meeting rooms run in. It is especially important
// to set this if you're using PHP 5 on Linux. In this configuration
// if you don't, meetings in a different DST than you are currently
Modified: mrbs/trunk/web/dbsys.inc
===================================================================
--- mrbs/trunk/web/dbsys.inc 2011-08-05 10:56:25 UTC (rev 1861)
+++ mrbs/trunk/web/dbsys.inc 2011-08-07 17:27:39 UTC (rev 1862)
@@ -13,9 +13,10 @@
$tbl_room = $db_tbl_prefix . "room";
$tbl_users = $db_tbl_prefix . "users";
$tbl_variables = $db_tbl_prefix . "variables";
+$tbl_zoneinfo = $db_tbl_prefix . "zoneinfo";
-$db_schema_version = 27;
+$db_schema_version = 28;
$local_db_schema_version = 1;
Modified: mrbs/trunk/web/functions_ical.inc
===================================================================
--- mrbs/trunk/web/functions_ical.inc 2011-08-05 10:56:25 UTC (rev 1861)
+++ mrbs/trunk/web/functions_ical.inc 2011-08-07 17:27:39 UTC (rev 1862)
@@ -8,36 +8,188 @@
// and the calendar
$vtimezone = get_vtimezone($timezone);
+
+// Extracts a VTIMEZONE component from a VCALENDAR.
+function extract_vtimezone($vcalendar)
+{
+ // The VTIMEZONE components are enclosed in a VCALENDAR, so we want to
+ // extract the VTIMEZONE component.
+ $vtimezone = strstr($vcalendar, "BEGIN:VTIMEZONE");
+ // Would be simpler to use strstr() again, but the optional
+ // third parameter was only introduced in PHP 5.3.0
+ $pos = strpos($vtimezone, "END:VTIMEZONE");
+ if ($pos !== FALSE)
+ {
+ $vtimezone = substr($vtimezone, 0, $pos);
+ if (!empty($vtimezone))
+ {
+ $vtimezone .= "END:VTIMEZONE";
+ return $vtimezone;
+ }
+ }
+ return ''; // There wasn't a VTIMEZONE component
+}
+
+
+// download the contents of the web page at $url and return it as a string
+function download($url)
+{
+ global $proxy;
+
+ $connect_timeout = 2; // seconds
+ $transfer_timeout = 2; // seconds
+ $curlopt_timeout = 3; // seconds
+
+ // If we can we use cURL to download the page because it allows us to
+ // set a timeout on establishing a connection as well as on the transfer
time.
+ // (We can only set a timeout on transfer time with file_get_contents()).
The
+ // connection timeout is important because MRBS may be being used in sites
+ // where direct access to the internet is not allowed.
+ if (function_exists('curl_init'))
+ {
+ $ch = curl_init($url);
+ curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, $connect_timeout);
+ curl_setopt($ch, CURLOPT_LOW_SPEED_TIME, $transfer_timeout);
+ curl_setopt($ch, CURLOPT_TIMEOUT, $curlopt_timeout);
+ curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
+ $contents = curl_exec($ch);
+ curl_close($ch);
+ }
+
+ // If there's no cURL then use file_get_contents()
+ else
+ {
+ // Create the stream context so that we can (a) make sure the connection
is closed,
+ // because file_get_contents() does not return the contents when all the
bytes
+ // have been sent, but when the connection is closed and (b) set a timeout
in case
+ // the transfer is slow
+ $context = stream_context_create(array('http' =>
array('header'=>'Connection: close',
+ 'timeout' =>
$transfer_timeout)));
+ // Suppress any timeout warnings
+ $contents = @file_get_contents($url);
+ }
+
+ return $contents;
+}
+
+
// Get a VTIMEZONE component for the timezone $tz.
// If none available returns FALSE.
+//
+// We cache the latest VTIMEZONE component in the database. If it has expired
+// we go to the web for the latest version or if there's nothing in the
database
+// in the first place we try and populate it from the VTIMEZONE definitions in
+// the filesystem.
function get_vtimezone($tz)
{
- $tz_dir = "tzurl/zoneinfo-outlook"; // We use the Outlook compatible
versions
- $vcalendar_file = "$tz_dir/$tz.ics";
- if (file_exists($vcalendar_file))
+ global $tbl_zoneinfo, $zoneinfo_update, $zoneinfo_expiry;
+
+ $outlook_compatible = TRUE; // We use the Outlook compatible versions
+ $tz_dir = ($outlook_compatible) ? "tzurl/zoneinfo-outlook" :
"tzurl/zoneinfo";
+ $tz_file = "$tz_dir/$tz.ics"; // A fallback for a VTIMEZONE definition
+
+ $vtimezone = FALSE; // Default value in case we don't find one
+
+ // Convert booleans into 1 or 0 (necessary for the database)
+ $outlook_compatible = ($outlook_compatible) ? 1 : 0;
+
+ // Acquire a mutex to lock out others who might be accessing
+ // the table, to stop somebody inserting a record at the same
+ // time as us (we want just one entry per timezone)
+ if (!sql_mutex_lock("$tbl_zoneinfo"))
{
- $vcalendar = file_get_contents($vcalendar_file);
+ fatal_error(TRUE, get_vocab("failed_to_acquire"));
+ }
+
+ // Look and see if there's a component in the database
+ $sql = "SELECT vtimezone, last_updated
+ FROM $tbl_zoneinfo
+ WHERE timezone='" . addslashes($tz) . "'
+ AND outlook_compatible=$outlook_compatible
+ LIMIT 1";
+ $res = sql_query($sql);
+ if ($res === FALSE)
+ {
+ trigger_error(sql_error(), E_USER_WARNING);
+ fatal_error(FALSE, get_vocab("fatal_db_error"));
+ }
+ elseif (sql_count($res) > 0)
+ {
+ // If there's something in the database and it hasn't expired, then
+ // that's it.
+ $row = sql_row_keyed($res, 0);
+ $vtimezone = $row['vtimezone'];
+ if ($zoneinfo_update && ((time() - $row['last_updated']) >=
$zoneinfo_expiry))
+ {
+ // Otherwise, provided that the config variable $zoneinfo_update
+ // is true, we get the URL of the latest version on the web,
+ // and update the database.
+ //
+ // (Note that a VTIMEZONE component can contain a TZURL property which
+ // gives the URL of the most up to date version. Calendar applications
+ // should be able to check this themselves, but we might as well give
them
+ // the most up to date version in the first place).
+ $properties = explode("\r\n", ical_unfold($vtimezone));
+ foreach ($properties as $property)
+ {
+ if (strpos($property, "TZURL:") === 0)
+ {
+ $tz_url = substr($property, 6); // 6 is the length of "TZURL:"
+ }
+ }
+
+ $vcalendar = download($tz_url);
+
+ if ($vcalendar)
+ {
+ $new_vtimezone = extract_vtimezone($vcalendar);
+ if (!empty($new_vtimezone))
+ {
+ // We've got a valid VTIMEZONE, so we can overwrite $vtimezone
+ $vtimezone = $new_vtimezone;
+ // Update the database
+ $sql = "UPDATE $tbl_zoneinfo
+ SET vtimezone='" . addslashes($vtimezone) . "',
+ last_updated=" . time() . "
+ WHERE timezone='" . addslashes($tz) . "'
+ AND outlook_compatible=$outlook_compatible";
+ if (sql_command($sql) < 0)
+ {
+ trigger_error(sql_error(), E_USER_WARNING);
+ fatal_error(FALSE, get_vocab("fatal_db_error"));
+ }
+ }
+ }
+ }
+ }
+ // There's nothing in the database, so try and get a VTIMEZONE component
+ // from the filesystem.
+ else
+ {
+ $vcalendar = file_get_contents($tz_file);
+
if ($vcalendar)
{
- // The VTIMEZONE components are enclosed in a VCALENDAR, so we want to
- // extract the VTIMEZONE component.
- $vtimezone = strstr($vcalendar, "BEGIN:VTIMEZONE");
- // Would be simpler to use strstr() again, but the optional
- // third parameter was only introduced in PHP 5.3.0
- $pos = strpos($vtimezone, "END:VTIMEZONE");
- if ($pos !== FALSE)
+ $vtimezone = extract_vtimezone($vcalendar);
+ if (!empty($vtimezone))
{
- $vtimezone = substr($vtimezone, 0, $pos);
- if (!empty($vtimezone))
+ $sql = "INSERT INTO $tbl_zoneinfo
+ (timezone, outlook_compatible, vtimezone, last_updated)
+ VALUES ('" . addslashes($tz) . "',
+ $outlook_compatible,
+ '" . addslashes($vtimezone) . "', " .
+ time() . ")";
+ if (sql_command($sql) < 0)
{
- $vtimezone .= "END:VTIMEZONE";
- return $vtimezone;
+ trigger_error(sql_error(), E_USER_WARNING);
+ fatal_error(FALSE, get_vocab("fatal_db_error"));
}
}
}
}
-
- return FALSE;
+ // Tidy up and return
+ sql_mutex_unlock("$tbl_zoneinfo"); // Release the mutex
+ return $vtimezone;
}
@@ -108,6 +260,59 @@
return $result;
}
+
+// Reverse the RFC 5545 folding process, which splits lines into groups
+// of max 75 octets separated by 'CRLFspace' or 'CRLFtab'
+function ical_unfold($str)
+{
+ // Deal with the trivial cases
+ if (!isset($str))
+ {
+ return NULL;
+ }
+ if ($str == '')
+ {
+ return $str;
+ }
+
+ // We've got a non-zero length string
+ $result = '';
+ $byte_index = 0;
+
+ while (isset($byte_index))
+ {
+ // Get the next character
+ $char = utf8_seq($str, $byte_index);
+ // If it's a CR then look ahead to the following character, if there is one
+ if (($char == "\r") && isset($byte_index))
+ {
+ $char = utf8_seq($str, $byte_index);
+ // If that's a LF then look ahead to the next character, if there is one
+ if (($char == "\n") && isset($byte_index))
+ {
+ $char = utf8_seq($str, $byte_index);
+ // If that's a space or a tab then ignore it because we've just had a
fold
+ // sequence. Otherwise add the characters into the result string.
+ if (($char != " ") && ($char != "\t"))
+ {
+ $result .= "\r\n" . $char;
+ }
+ }
+ else
+ {
+ $result .= "\r" . $char;
+ }
+ }
+ else
+ {
+ $result .= $char;
+ }
+ }
+
+ return $result;
+}
+
+
// Escape text for use in an iCalendar text value
function ical_escape_text($str)
{
Modified: mrbs/trunk/web/systemdefaults.inc.php
===================================================================
--- mrbs/trunk/web/systemdefaults.inc.php 2011-08-05 10:56:25 UTC (rev
1861)
+++ mrbs/trunk/web/systemdefaults.inc.php 2011-08-07 17:27:39 UTC (rev
1862)
@@ -12,6 +12,10 @@
*
**************************************************************************/
+/**********
+ * Timezone
+ **********/
+
// The timezone your meeting rooms run in. It is especially important
// to set this if you're using PHP 5 on Linux. In this configuration
// if you don't, meetings in a different DST than you are currently
@@ -24,6 +28,21 @@
// The following line must be uncommented by removing the '//' at the beginning
//$timezone = "Europe/London";
+// If you are using iCalendar notifications of bookings (see the mail settings
below)
+// then the iCalendar attachment includes a definition of your timezone in
+// VTIMEZONE format. This defines the timezone, including the rules for
Daylight
+// Saving Time transitions. This information is included in the MRBS
distribution.
+// However, as governments can change the rules periodically, MRBS will check
from
+// time to time to see if there is a later version available on the web. If
your
+// site prevents external access to the web, this check will time out. However
+// you can avoid the timeout and stop MRBS checking for up to date versions by
+// setting $zoneinfo_update = FALSE;
+$zoneinfo_update = TRUE;
+
+// The VTIMEZONE definitions are cached in the database with an expiry time
+// of $zoneinfo_expiry seconds
+$zoneinfo_expiry = 60*60*24*28; // 28 days
+
/*******************
* Database settings
******************/
@@ -803,7 +822,8 @@
//(b) whether there are addresses to send email to and (c) the result of the
mail
// sending operation.
$mail_settings['debug'] = FALSE;
-
+
+
/**********
* Language
**********/
Property changes on: mrbs/trunk/web/upgrade/28
___________________________________________________________________
Added: bugtraq:number
+ true
Added: mrbs/trunk/web/upgrade/28/mysql.sql
===================================================================
--- mrbs/trunk/web/upgrade/28/mysql.sql (rev 0)
+++ mrbs/trunk/web/upgrade/28/mysql.sql 2011-08-07 17:27:39 UTC (rev 1862)
@@ -0,0 +1,12 @@
+# $Id$
+
+CREATE TABLE %DB_TBL_PREFIX%zoneinfo
+(
+ id int NOT NULL auto_increment,
+ timezone varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci
DEFAULT '' NOT NULL,
+ outlook_compatible tinyint unsigned NOT NULL DEFAULT 0,
+ vtimezone text CHARACTER SET utf8 COLLATE utf8_general_ci,
+ last_updated int NOT NULL DEFAULT 0,
+
+ PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Property changes on: mrbs/trunk/web/upgrade/28/mysql.sql
___________________________________________________________________
Added: svn:keywords
+ Id
Added: svn:eol-style
+ native
Added: mrbs/trunk/web/upgrade/28/pgsql.sql
===================================================================
--- mrbs/trunk/web/upgrade/28/pgsql.sql (rev 0)
+++ mrbs/trunk/web/upgrade/28/pgsql.sql 2011-08-07 17:27:39 UTC (rev 1862)
@@ -0,0 +1,10 @@
+-- $Id$
+
+CREATE TABLE %DB_TBL_PREFIX%zoneinfo
+(
+ id serial primary key,
+ timezone varchar(255) DEFAULT '' NOT NULL,
+ outlook_compatible smallint NOT NULL DEFAULT 0,
+ vtimezone text,
+ last_updated int NOT NULL DEFAULT 0
+);
Property changes on: mrbs/trunk/web/upgrade/28/pgsql.sql
___________________________________________________________________
Added: svn:keywords
+ Id
Added: svn:eol-style
+ native
This was sent by the SourceForge.net collaborative development platform, the
world's largest Open Source development site.
------------------------------------------------------------------------------
BlackBerry® DevCon Americas, Oct. 18-20, San Francisco, CA
The must-attend event for mobile developers. Connect with experts.
Get tools for creating Super Apps. See the latest technologies.
Sessions, hands-on labs, demos & much more. Register early & save!
http://p.sf.net/sfu/rim-blackberry-1
_______________________________________________
Mrbs-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
