r28228 - in /trunk/calendar: inc/ setup/

[ralfbecker]

Author: ralfbecker
Date: Sat Oct 31 15:47:16 2009
New Revision: 28228

URL: http://www.egroupware.org/viewvc/egroupware?rev=28228&view=rev
Log:
Added calendar table for timezones:
- timezone data is imported from SQLite DB from Thunderbird Lighting 1.0pre
- contains iCal VTIMEZONE component
- also contains not yet used latitude and longitude for timezone
- methods to convert between TZID string, nummeric tz_id and VTIMEZONE
  iCal component
--> preparation to store timezone information for each events
    (using tz_id as foreing key into egw_cal_timezones table)

Added:
    trunk/calendar/inc/class.calendar_timezones.inc.php   (with props)
    trunk/calendar/setup/timezones.sqlite   (with props)
Modified:
    trunk/calendar/inc/class.calendar_hooks.inc.php
    trunk/calendar/setup/default_records.inc.php
    trunk/calendar/setup/setup.inc.php
    trunk/calendar/setup/tables_current.inc.php
    trunk/calendar/setup/tables_update.inc.php

Modified: trunk/calendar/inc/class.calendar_hooks.inc.php
URL: http://www.egroupware.org/viewvc/egroupware/trunk/calendar/inc/class.calendar_hooks.inc.php?rev=28228&r1=28227&r2=28228&view=diff
==============================================================================
--- trunk/calendar/inc/class.calendar_hooks.inc.php (original)
+++ trunk/calendar/inc/class.calendar_hooks.inc.php Sat Oct 31 15:47:16 2009
@@ -111,10 +111,11 @@
  static function admin()
  {
  $file = Array(
- 'Site Configuration' => $GLOBALS['egw']->link('/index.php','menuaction=admin.uiconfig.index&appname=calendar'),
- 'Custom fields' => $GLOBALS['egw']->link('/index.php','menuaction=admin.customfields.edit&appname=calendar'),
- 'Calendar Holiday Management' => $GLOBALS['egw']->link('/index.php','menuaction=calendar.uiholiday.admin'),
- 'Global Categories' => $GLOBALS['egw']->link('/index.php','menuaction=admin.uicategories.index&appname=calendar')
+ 'Site Configuration' => egw::link('/index.php','menuaction=admin.uiconfig.index&appname=calendar'),
+ 'Custom fields' => egw::link('/index.php','menuaction=admin.customfields.edit&appname=calendar'),
+ 'Calendar Holiday Management' => egw::link('/index.php','menuaction=calendar.uiholiday.admin'),
+ 'Global Categories' => egw::link('/index.php','menuaction=admin.uicategories.index&appname=calendar'),
+ 'Update timezones' => egw::link('/index.php','menuaction=calendar.calendar_timezones.update'),
  );
  display_section('calendar','calendar',$file);
  }
@@ -125,10 +126,10 @@
  static function preferences()
  {
  $file = array(
- 'Preferences'     => $GLOBALS['egw']->link('/index.php','menuaction=preferences.uisettings.index&appname=calendar'),
- 'Grant Access'    => $GLOBALS['egw']->link('/index.php','menuaction=preferences.uiaclprefs.index&acl_app=calendar'),
- 'Edit Categories' => $GLOBALS['egw']->link('/index.php','menuaction=preferences.uicategories.index&cats_app=calendar&cats_level=True&global_cats=True'),
- 'Import CSV-File' => $GLOBALS['egw']->link('/calendar/csv_import.php'),
+ 'Preferences'     => egw::link('/index.php','menuaction=preferences.uisettings.index&appname=calendar'),
+ 'Grant Access'    => egw::link('/index.php','menuaction=preferences.uiaclprefs.index&acl_app=calendar'),
+ 'Edit Categories' => egw::link('/index.php','menuaction=preferences.uicategories.index&cats_app=calendar&cats_level=True&global_cats=True'),
+ 'Import CSV-File' => egw::link('/calendar/csv_import.php'),
  );
  display_section('calendar','calendar',$file);
  }

Added: trunk/calendar/inc/class.calendar_timezones.inc.php
URL: http://www.egroupware.org/viewvc/egroupware/trunk/calendar/inc/class.calendar_timezones.inc.php?rev=28228&view=auto
==============================================================================
--- trunk/calendar/inc/class.calendar_timezones.inc.php (added)
+++ trunk/calendar/inc/class.calendar_timezones.inc.php Sat Oct 31 15:47:16 2009
@@ -1,0 +1,209 @@
+<?php
+/**
+ * eGroupWare - Calendar's timezone information
+ *
+ * Timezone information get imported from SQLite database, "borrowed" of Lighting TB extension.
+ *
+ * @link http://www.egroupware.org
+ * @package calendar
+ * @author Ralf Becker <RalfBecker-AT-outdoor-training.de>
+ * @copyright (c) 2009 by RalfBecker-At-outdoor-training.de
+ * @license http://opensource.org/licenses/gpl-license.php GPL - GNU General Public License
+ * @version $Id$
+ */
+
+/**
+ * Class for timezone information
+ *
+ * This class serves two purposes:
+ * - convert between TZID strings and nummeric tz_id's stored in database
+ * - get iCal VTIMEZONE component for a TZID (data from Lighting extension)
+ */
+class calendar_timezones
+{
+ /**
+ * Methods callable via menuation
+ *
+ * @var array
+ */
+ public $public_functions = array(
+ 'update' => true,
+ );
+
+ /**
+ * Name of timezone table
+ */
+ const TABLE = 'egw_cal_timezones';
+
+ /**
+ * Cached timezone data (reference to session created by init_static)
+ *
+ * @var array id => data
+ */
+ protected static $tz_cache = array();
+
+ /**
+ * Cached timezone data (reference to session created by init_static)
+ *
+ * @var array tzid => id
+ */
+ protected static $tz2id = array();
+
+ /**
+ * Get the nummeric id (or other data) for a given TZID
+ *
+ * Examples:
+ * - calendar_timezone::tz2id('Europe/Berlin') returns nummeric id for given TZID
+ * - calendar_timezone::tz2id('Europe/Berlin','component') returns VTIMEZONE component for given TZID
+ *
+ * @param string $tzid TZID
+ * @param string $what='id' what to return, default id, null for whole array
+ * @return int tz_id or null if not found
+ */
+ public static function tz2id($tzid,$what='id')
+ {
+ $id =& self::$tz2id[$tzid];
+
+ if (!isset($id))
+ {
+ if (($data = $GLOBALS['egw']->db->select(self::TABLE,'*',array(
+ 'tz_tzid' => $tzid,
+ ),__LINE__,__FILE__,false,'','calendar')->fetch()))
+ {
+ $id = $data['tz_id'];
+ self::$tz_cache[$id] = egw_db::strip_array_keys($data,'tz_');
+ }
+ }
+ if (isset($id) && $what != 'id')
+ {
+ return self::id2tz($id,$what);
+ }
+ return $id;
+ }
+
+ /**
+ * Get timezone data for a given nummeric id
+ *
+ * if NOT tzid or alias queried, we automatically resolve an evtl. alias
+ *
+ * Example:
+ * - calendar_timezone::id2tz($id) returns TZID
+ * - calendar_timezone::id2tz($id,'component') returns VTIMEZONE component for the given id
+ *
+ * @param int $id
+ * @param string $what='tzid' what data to return or null for whole data array, with keys 'id', 'tzid', 'component', 'alias', 'latitude', 'longitude'
+ * @return mixed false: if not found
+ */
+ public static function id2tz($id,$what='tzid')
+ {
+ $data =& self::$tz_cache[$id];
+
+ if (!isset($data))
+ {
+ if (($data = $GLOBALS['egw']->db->select(self::TABLE,'*',array(
+ 'tz_id' => $id,
+ ),__LINE__,__FILE__,false,'','calendar')->fetch()))
+ {
+ $data = egw_db::strip_array_keys($data,'tz_');
+ self::$tz2id[$data['tzid']] = $id;
+ }
+ }
+ // if not tzid queried, resolve aliases automatically
+ if ($data && $data['alias'] && $what != 'tzid' && $what != 'alias')
+ {
+ $data = self::is2tz($data['alias'],null);
+ }
+ return !$data ? $data : ($what ? $data[$what] : $data);
+ }
+
+ /**
+ * Init static variables for session
+ *
+ * As we use the returned reference, we do NOT need to care about storing the information in the session
+ */
+ public static function init_static()
+ {
+ self::$tz_cache =& egw_cache::getSession(__CLASS__,'tz_cache');
+ self::$tz2id =& egw_cache::getSession(__CLASS__,'tz2id');
+ }
+
+ /**
+ * Import timezones from sqlite file
+ *
+ * @param string $file='calendar/setup/timezones.sqlite' filename relative to EGW_SERVER_ROOT
+ * @param boolean $check_version=true true: check version and only act, if it's different
+ * @return string message about update
+ * @throws egw_exception_wrong_parameter if $file is not readable or wrong format/version
+ * @throws egw_exception_assertion_failed if no PDO sqlite support
+ */
+ public static function import_sqlite($file='calendar/setup/timezones.sqlite',$check_version=true)
+ {
+ $path = EGW_SERVER_ROOT.'/'.$file;
+
+ if (!file_exists($path) || !is_readable($path))
+ {
+ throw new egw_exception_wrong_parameter(__METHOD__."('$file') not found or readable!");
+ }
+ if (!check_load_extension('pdo') || !check_load_extension('pdo_sqlite'))
+ {
+ throw new egw_exception_assertion_failed(__METHOD__."('$file') required SQLite support (PHP extension pdo_sqlite) missing!");
+ }
+ $pdo = new PDO('sqlite:'.$path);
+ if ($pdo->query('SELECT version FROM tz_schema_version')->fetchColumn() != 1)
+ {
+ throw new egw_exception_wrong_parameter(__METHOD__."('$file') only schema version 1 supported!");
+ }
+ $tz_version = $pdo->query('SELECT version FROM tz_version')->fetchColumn();
+ $tz_db_version = $GLOBALS['egw']->db->query("SELECT config_value FROM egw_config WHERE config_name='tz_version' AND config_app='phpgwapi'",__LINE__,__FILE__)->fetchColumn();
+ //echo "<p>tz_version($path)=$tz_version, tz_db_version=$tz_db_version</p>\n";
+ if ($tz_version === $tz_db_version)
+ {
+ return lang('Nothing to update, version is already %1.',$tz_db_version); // we already have the right
+ }
+ $tz2id = array();
+ foreach($pdo->query('SELECT * FROM tz_data ORDER BY alias') as $data)
+ {
+ if ($data['alias'])
+ {
+ $data['alias'] = $tz2id[$data['alias']];
+ if (!$data['alias']) continue; // there's no such tzid
+ }
+ $GLOBALS['egw']->db->insert('egw_cal_timezones',array(
+ 'tz_alias' => $data['alias'],
+ 'tz_latitude' => $data['latitude'],
+ 'tz_longitude' => $data['longitude'],
+ 'tz_component' => $data['component'],
+ ),array(
+ 'tz_tzid' => $data['tzid'],
+ ),__LINE__,__FILE__,'calendar');
+
+ if (!($tz2id[$data['tzid']] = $GLOBALS['egw']->db->get_last_insert_id('egw_cal_timezones','tz_id')))
+ {
+ $tz2id[$data['tzid']] = $GLOBALS['egw']->db->select('egw_cal_timezones','tz_id',array(
+ 'tz_tzid' => $data['tzid'],
+ ),__LINE__,__FILE__,false,'','calendar')->fetchColumn();
+ }
+ }
+ $GLOBALS['egw']->db->insert('egw_config',array('config_value' => $tz_version),array(
+ 'config_name' => 'tz_version',
+ 'config_app' => 'phpgwapi',
+ ),__LINE__,__FILE__,'phpgwapi');
+
+ //_debug_array($tz2id);
+ return lang('Timezones updated to version %1 (%2 records updated).',$tz_version,count($tz2id));
+ }
+
+ /**
+ * Admin >> Update timezones
+ *
+ */
+ public function update()
+ {
+ if (!$GLOBALS['egw_info']['user']['apps']['admin'])
+ {
+ throw new egw_exception_no_permission_admin();
+ }
+ $GLOBALS['egw']->framework->render('<h3>'.self::import_sqlite()."</h3>\n",lang('Update timezones'),true);
+ }
+}
+calendar_timezones::init_static();

Propchange: trunk/calendar/inc/class.calendar_timezones.inc.php
------------------------------------------------------------------------------
    svn:eol-style = native

Propchange: trunk/calendar/inc/class.calendar_timezones.inc.php
------------------------------------------------------------------------------
    svn:keywords = Author Id Revision

Modified: trunk/calendar/setup/default_records.inc.php
URL: http://www.egroupware.org/viewvc/egroupware/trunk/calendar/setup/default_records.inc.php?rev=28228&r1=28227&r2=28228&view=diff
==============================================================================
--- trunk/calendar/setup/default_records.inc.php (original)
+++ trunk/calendar/setup/default_records.inc.php Sat Oct 31 15:47:16 2009
@@ -1,26 +1,27 @@
 <?php
- /**************************************************************************\
- * eGroupWare - Setup / Calendar                                            *
- * http://www.egroupware.org                                                *
- * --------------------------------------------                             *
- *  This program is free software; you can redistribute it and/or modify it *
- *  under the terms of the GNU General Public License as published by the   *
- *  Free Software Foundation; either version 2 of the License, or (at your  *
- *  option) any later version.                                              *
- \**************************************************************************/
+/**
+ * eGroupWare - Calendar setup
+ *
+ * @link http://www.egroupware.org
+ * @package calendar
+ * @author Ralf Becker <RalfBecker-AT-outdoor-training.de>
+ * @license http://opensource.org/licenses/gpl-license.php GPL - GNU General Public License
+ * @version $Id$
+ */
 
- /* $Id$ */
+// enable auto-loading of holidays from localhost by default
+foreach(array(
+ 'auto_load_holidays' => 'True',
+ 'holidays_url_path'  => 'localhost',
+) as $name => $value)
+{
+ $oProc->insert($GLOBALS['egw_setup']->config_table,array(
+ 'config_value' => $value,
+ ),array(
+ 'config_app' => 'phpgwapi',
+ 'config_name' => $name,
+ ),__FILE__,__LINE__);
+}
 
- // enable auto-loading of holidays from localhost by default
- foreach(array(
- 'auto_load_holidays' => 'True',
- 'holidays_url_path'  => 'localhost',
- ) as $name => $value)
- {
- $oProc->insert($GLOBALS['egw_setup']->config_table,array(
- 'config_value' => $value,
- ),array(
- 'config_app' => 'phpgwapi',
- 'config_name' => $name,
- ),__FILE__,__LINE__);
- }
+// import timezone data from sqlite database
+calendar_timezones::import_sqlite();

Modified: trunk/calendar/setup/setup.inc.php
URL: http://www.egroupware.org/viewvc/egroupware/trunk/calendar/setup/setup.inc.php?rev=28228&r1=28227&r2=28228&view=diff
==============================================================================
--- trunk/calendar/setup/setup.inc.php (original)
+++ trunk/calendar/setup/setup.inc.php Sat Oct 31 15:47:16 2009
@@ -10,7 +10,7 @@
  */
 
 $setup_info['calendar']['name']    = 'calendar';
-$setup_info['calendar']['version'] = '1.7.002';
+$setup_info['calendar']['version'] = '1.7.003';
 $setup_info['calendar']['app_order'] = 3;
 $setup_info['calendar']['enable']  = 1;
 $setup_info['calendar']['index']   = 'calendar.calendar_uiviews.index';
@@ -31,6 +31,7 @@
 $setup_info['calendar']['tables'][] = 'egw_cal_user';
 $setup_info['calendar']['tables'][] = 'egw_cal_extra';
 $setup_info['calendar']['tables'][] = 'egw_cal_dates';
+$setup_info['calendar']['tables'][] = 'egw_cal_timezones';
 
 /* The hooks this app includes, needed for hooks registration */
 $setup_info['calendar']['hooks']['admin'] = 'calendar_hooks::admin';
@@ -58,6 +59,12 @@
  'func' => 'pear_check',
  'from' => 'Calendar (iCal import+export)',
  ),
+ // check if PDO SQLite support is available
+ 'pdo_sqlite' => array(
+ 'func' => 'extension_check',
+ 'from' => 'Calendar',
+ ),
 );
 
 
+

Modified: trunk/calendar/setup/tables_current.inc.php
URL: http://www.egroupware.org/viewvc/egroupware/trunk/calendar/setup/tables_current.inc.php?rev=28228&r1=28227&r2=28228&view=diff
==============================================================================
--- trunk/calendar/setup/tables_current.inc.php (original)
+++ trunk/calendar/setup/tables_current.inc.php Sat Oct 31 15:47:16 2009
@@ -102,5 +102,19 @@
  'fk' => array(),
  'ix' => array(),
  'uc' => array()
+ ),
+ 'egw_cal_timezones' => array(
+ 'fd' => array(
+ 'tz_id' => array('type' => 'auto','nullable' => False),
+ 'tz_tzid' => array('type' => 'varchar','precision' => '128','nullable' => False),
+ 'tz_alias' => array('type' => 'int','precision' => '4','comment' => 'tz_id for data'),
+ 'tz_latitude' => array('type' => 'int','precision' => '4'),
+ 'tz_longitude' => array('type' => 'int','precision' => '4'),
+ 'tz_component' => array('type' => 'text','comment' => 'iCal VTIMEZONE component')
+ ),
+ 'pk' => array('tz_id'),
+ 'fk' => array(),
+ 'ix' => array('tz_alias'),
+ 'uc' => array('tz_tzid')
  )
 );

Modified: trunk/calendar/setup/tables_update.inc.php
URL: http://www.egroupware.org/viewvc/egroupware/trunk/calendar/setup/tables_update.inc.php?rev=28228&r1=28227&r2=28228&view=diff
==============================================================================
--- trunk/calendar/setup/tables_update.inc.php (original)
+++ trunk/calendar/setup/tables_update.inc.php Sat Oct 31 15:47:16 2009
@@ -1703,3 +1703,26 @@
  return $GLOBALS['setup_info']['calendar']['currentver'] = '1.7.002';
 }
 
+
+function calendar_upgrade1_7_002()
+{
+ $GLOBALS['egw_setup']->oProc->CreateTable('egw_cal_timezones',array(
+ 'fd' => array(
+ 'tz_id' => array('type' => 'auto','nullable' => False),
+ 'tz_tzid' => array('type' => 'varchar','precision' => '128','nullable' => False),
+ 'tz_alias' => array('type' => 'int','precision' => '4','comment' => 'tz_id for data'),
+ 'tz_latitude' => array('type' => 'int','precision' => '4'),
+ 'tz_longitude' => array('type' => 'int','precision' => '4'),
+ 'tz_component' => array('type' => 'text','comment' => 'iCal VTIMEZONE component')
+ ),
+ 'pk' => array('tz_id'),
+ 'fk' => array(),
+ 'ix' => array('tz_alias'),
+ 'uc' => array('tz_tzid')
+ ));
+ // import timezone data, throw exception if no PDO sqlite support
+ calendar_timezones::import_sqlite();
+
+ return $GLOBALS['setup_info']['calendar']['currentver'] = '1.7.003';
+}
+

Added: trunk/calendar/setup/timezones.sqlite
URL: http://www.egroupware.org/viewvc/egroupware/trunk/calendar/setup/timezones.sqlite?rev=28228&view=auto
==============================================================================
Binary file - no diff available.

Propchange: trunk/calendar/setup/timezones.sqlite
------------------------------------------------------------------------------
    svn:mime-type = application/octet-stream


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
eGroupWare-cvs mailing list
eGroupWare-cvs@...
https://lists.sourceforge.net/lists/listinfo/egroupware-cvs