Revision: 1696
http://mrbs.svn.sourceforge.net/mrbs/?rev=1696&view=rev
Author: cimorrison
Date: 2010-12-13 16:23:37 +0000 (Mon, 13 Dec 2010)
Log Message:
-----------
- fixed bug that meant that series that had an individual entry changed and
were exported from view_entry were not imported into some calendars (eg Google
calendar) properly
- bug fixed by adding a recurrence id column to the entry tables.my.sql
- added uid's and recurrence id's for all existing entries on upgrade
Modified Paths:
--------------
mrbs/branches/ics_attachments/tables.my.sql
mrbs/branches/ics_attachments/tables.pg.pre73.sql
mrbs/branches/ics_attachments/tables.pg.sql
mrbs/branches/ics_attachments/web/dbsys.inc
mrbs/branches/ics_attachments/web/edit_entry.php
mrbs/branches/ics_attachments/web/edit_entry_handler.php
mrbs/branches/ics_attachments/web/functions_ical.inc
mrbs/branches/ics_attachments/web/internalconfig.inc.php
mrbs/branches/ics_attachments/web/mrbs_sql.inc
Added Paths:
-----------
mrbs/branches/ics_attachments/web/upgrade/24/
mrbs/branches/ics_attachments/web/upgrade/24/mysql.sql
mrbs/branches/ics_attachments/web/upgrade/24/pgsql.sql
mrbs/branches/ics_attachments/web/upgrade/24/post.inc
Modified: mrbs/branches/ics_attachments/tables.my.sql
===================================================================
--- mrbs/branches/ics_attachments/tables.my.sql 2010-12-11 20:49:12 UTC (rev
1695)
+++ mrbs/branches/ics_attachments/tables.my.sql 2010-12-13 16:23:37 UTC (rev
1696)
@@ -83,6 +83,7 @@
info_text text,
ical_uid varchar(255) DEFAULT '' NOT NULL,
ical_sequence smallint DEFAULT 0 NOT NULL,
+ ical_recur_id varchar(16) DEFAULT '' NOT NULL,
PRIMARY KEY (id),
KEY idxStartTime (start_time),
@@ -137,6 +138,6 @@
);
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ( 'db_version', '23');
+ VALUES ( 'db_version', '24');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ( 'local_db_version', '1');
Modified: mrbs/branches/ics_attachments/tables.pg.pre73.sql
===================================================================
--- mrbs/branches/ics_attachments/tables.pg.pre73.sql 2010-12-11 20:49:12 UTC
(rev 1695)
+++ mrbs/branches/ics_attachments/tables.pg.pre73.sql 2010-12-13 16:23:37 UTC
(rev 1696)
@@ -64,46 +64,51 @@
CREATE TABLE mrbs_entry
(
- id serial primary key,
- start_time int DEFAULT 0 NOT NULL,
- end_time int DEFAULT 0 NOT NULL,
- entry_type int DEFAULT 0 NOT NULL,
- repeat_id int DEFAULT 0 NOT NULL,
- room_id int DEFAULT 1 NOT NULL,
- timestamp timestamp DEFAULT current_timestamp,
- create_by varchar(80) DEFAULT '' NOT NULL,
- name varchar(80) DEFAULT '' NOT NULL,
- type char DEFAULT 'E' NOT NULL,
- description text,
- status smallint DEFAULT 0 NOT NULL,
- reminded int,
- info_time int,
- info_user varchar(80),
- info_text text
+ id serial primary key,
+ start_time int DEFAULT 0 NOT NULL,
+ end_time int DEFAULT 0 NOT NULL,
+ entry_type int DEFAULT 0 NOT NULL,
+ repeat_id int DEFAULT 0 NOT NULL,
+ room_id int DEFAULT 1 NOT NULL,
+ timestamp timestamp DEFAULT current_timestamp,
+ create_by varchar(80) DEFAULT '' NOT NULL,
+ name varchar(80) DEFAULT '' NOT NULL,
+ type char DEFAULT 'E' NOT NULL,
+ description text,
+ status smallint DEFAULT 0 NOT NULL,
+ reminded int,
+ info_time int,
+ info_user varchar(80),
+ info_text text,
+ ical_uid varchar(255) DEFAULT '' NOT NULL,
+ ical_sequence smallint DEFAULT 0 NOT NULL,
+ ical_recur_id varchar(16) DEFAULT '' NOT NULL
);
create index mrbs_idxStartTime on mrbs_entry(start_time);
create index mrbs_idxEndTime on mrbs_entry(end_time);
CREATE TABLE mrbs_repeat
(
- 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,
- end_date int DEFAULT 0 NOT NULL,
- rep_opt varchar(32) DEFAULT '' NOT NULL,
- room_id int DEFAULT 1 NOT NULL,
- timestamp timestamp DEFAULT current_timestamp,
- create_by varchar(80) DEFAULT '' NOT NULL,
- name varchar(80) DEFAULT '' NOT NULL,
- type char DEFAULT 'E' NOT NULL,
- description text,
- rep_num_weeks smallint DEFAULT NULL NULL,
- status smallint DEFAULT 0 NOT NULL,
- reminded int,
- info_time int,
- info_user varchar(80),
- info_text text
+ 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,
+ end_date int DEFAULT 0 NOT NULL,
+ rep_opt varchar(32) DEFAULT '' NOT NULL,
+ room_id int DEFAULT 1 NOT NULL,
+ timestamp timestamp DEFAULT current_timestamp,
+ create_by varchar(80) DEFAULT '' NOT NULL,
+ name varchar(80) DEFAULT '' NOT NULL,
+ type char DEFAULT 'E' NOT NULL,
+ description text,
+ rep_num_weeks smallint DEFAULT NULL NULL,
+ status smallint DEFAULT 0 NOT NULL,
+ reminded int,
+ info_time int,
+ info_user varchar(80),
+ info_text text,
+ ical_uid varchar(255) DEFAULT '' NOT NULL,
+ ical_sequence smallint DEFAULT 0 NOT NULL
);
CREATE TABLE mrbs_variables
@@ -124,6 +129,6 @@
);
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ('db_version', '23');
+ VALUES ('db_version', '24');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ('local_db_version', '1');
Modified: mrbs/branches/ics_attachments/tables.pg.sql
===================================================================
--- mrbs/branches/ics_attachments/tables.pg.sql 2010-12-11 20:49:12 UTC (rev
1695)
+++ mrbs/branches/ics_attachments/tables.pg.sql 2010-12-13 16:23:37 UTC (rev
1696)
@@ -94,7 +94,8 @@
info_user varchar(80),
info_text text,
ical_uid varchar(255) DEFAULT '' NOT NULL,
- ical_sequence smallint DEFAULT 0 NOT NULL
+ ical_sequence smallint DEFAULT 0 NOT NULL,
+ ical_recur_id varchar(16) DEFAULT '' NOT NULL
);
create index mrbs_idxStartTime on mrbs_entry(start_time);
create index mrbs_idxEndTime on mrbs_entry(end_time);
@@ -141,6 +142,6 @@
);
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ('db_version', '23');
+ VALUES ('db_version', '24');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ('local_db_version', '1');
Modified: mrbs/branches/ics_attachments/web/dbsys.inc
===================================================================
--- mrbs/branches/ics_attachments/web/dbsys.inc 2010-12-11 20:49:12 UTC (rev
1695)
+++ mrbs/branches/ics_attachments/web/dbsys.inc 2010-12-13 16:23:37 UTC (rev
1696)
@@ -15,7 +15,7 @@
$tbl_variables = $db_tbl_prefix . "variables";
-$db_schema_version = 23;
+$db_schema_version = 24;
$local_db_schema_version = 1;
Modified: mrbs/branches/ics_attachments/web/edit_entry.php
===================================================================
--- mrbs/branches/ics_attachments/web/edit_entry.php 2010-12-11 20:49:12 UTC
(rev 1695)
+++ mrbs/branches/ics_attachments/web/edit_entry.php 2010-12-13 16:23:37 UTC
(rev 1696)
@@ -221,6 +221,7 @@
$original_room_id = $row['room_id'];
case 'ical_uid':
case 'ical_sequence':
+ case 'ical_recur_id':
case 'entry_type':
$$column = $row[$column];
break;
@@ -1192,6 +1193,7 @@
echo "<input type=\"hidden\" name=\"original_room_id\"
value=\"$original_room_id\">\n";
echo "<input type=\"hidden\" name=\"ical_uid\" value=\"$ical_uid\">\n";
echo "<input type=\"hidden\" name=\"ical_sequence\"
value=\"$ical_sequence\">\n";
+ echo "<input type=\"hidden\" name=\"ical_recur_id\"
value=\"$ical_recur_id\">\n";
}
if(isset($id) && !isset($copy))
{
Modified: mrbs/branches/ics_attachments/web/edit_entry_handler.php
===================================================================
--- mrbs/branches/ics_attachments/web/edit_entry_handler.php 2010-12-11
20:49:12 UTC (rev 1695)
+++ mrbs/branches/ics_attachments/web/edit_entry_handler.php 2010-12-13
16:23:37 UTC (rev 1696)
@@ -18,6 +18,7 @@
$original_room_id = get_form_var('original_room_id', 'int');
$ical_uid = get_form_var('ical_uid', 'string');
$ical_sequence = get_form_var('ical_sequence', 'int');
+$ical_recur_id = get_form_var('ical_recur_id', 'string');
$returl = get_form_var('returl', 'string');
$rep_id = get_form_var('rep_id', 'int');
$edit_type = get_form_var('edit_type', 'string');
@@ -586,7 +587,17 @@
}
else
{
- // Mark changed entry in a series with entry_type:
+ if ($repeat_id > 0)
+ {
+ // Mark changed entry in a series with entry_type:
+ $data['entry_type'] = ENTRY_RPT_CHANGED;
+ // Keep the same recurrence id (this never changes once an entry has
been made)
+ $data['ical_recur_id'] = $ical_recur_id;
+ }
+ else
+ {
+ $data['entry_type'] = ENTRY_SINGLE;
+ }
$data['entry_type'] = ($repeat_id > 0) ? ENTRY_RPT_CHANGED :
ENTRY_SINGLE;
$data['repeat_id'] = $repeat_id;
}
Modified: mrbs/branches/ics_attachments/web/functions_ical.inc
===================================================================
--- mrbs/branches/ics_attachments/web/functions_ical.inc 2010-12-11
20:49:12 UTC (rev 1695)
+++ mrbs/branches/ics_attachments/web/functions_ical.inc 2010-12-13
16:23:37 UTC (rev 1696)
@@ -3,9 +3,6 @@
// $Id$
-define ('RFC5545_FORMAT', 'Ymd\THis'); // Format for expressing iCalendar
dates
-define ('ICAL_EOL', "\r\n"); // Lines must be terminated by CRLF
-
// "Folds" lines longer than 75 octets. Multi-byte safe.
//
// "Lines of text SHOULD NOT be longer than 75 octets, excluding the line
@@ -171,7 +168,7 @@
// If this is an individual member of a series then set the recurrence id
if ($data['entry_type'] == ENTRY_RPT_CHANGED)
{
- $results[] = "RECURRENCE-ID:" . gmdate(RFC5545_FORMAT . '\Z',
$data['start_time']);
+ $results[] = "RECURRENCE-ID:" . $data['ical_recur_id'];
}
if ($confirmation_enabled)
{
Modified: mrbs/branches/ics_attachments/web/internalconfig.inc.php
===================================================================
--- mrbs/branches/ics_attachments/web/internalconfig.inc.php 2010-12-11
20:49:12 UTC (rev 1695)
+++ mrbs/branches/ics_attachments/web/internalconfig.inc.php 2010-12-13
16:23:37 UTC (rev 1696)
@@ -128,6 +128,14 @@
define('REP_N_WEEKLY', 6);
+/*************************************************
+ * ICALENDAR CONSTANTS - internal use, do not change
+ *************************************************/
+
+define ('RFC5545_FORMAT', 'Ymd\THis'); // Format for expressing iCalendar
dates
+define ('ICAL_EOL', "\r\n"); // Lines must be terminated by CRLF
+
+
/****************************************************************
* DATABASE TABLES - internal use, do not change
****************************************************************/
@@ -157,7 +165,8 @@
'info_user',
'info_text',
'ical_uid',
- 'ical_sequence');
+ 'ical_sequence',
+ 'ical_recur_id');
$standard_fields['repeat'] = array('id',
'start_time',
Modified: mrbs/branches/ics_attachments/web/mrbs_sql.inc
===================================================================
--- mrbs/branches/ics_attachments/web/mrbs_sql.inc 2010-12-11 20:49:12 UTC
(rev 1695)
+++ mrbs/branches/ics_attachments/web/mrbs_sql.inc 2010-12-13 16:23:37 UTC
(rev 1696)
@@ -265,6 +265,7 @@
case 'type':
case 'description':
case 'ical_uid':
+ case 'ical_recur_id':
$sql_col[] = $key;
$sql_val[] = "'" . addslashes($data[$key]) . "'";
break;
@@ -358,6 +359,13 @@
// into effect
if ($data['end_time'] > $data['start_time'])
{
+ // If we're about to create an individual member of a series for the first
time
+ // then give it a recurrence-id equivalent to the start time. It should
always
+ // keep this value, even if the start time is subsequently changed.
+ if ($data['entry_type'] == ENTRY_RPT_ORIGINAL)
+ {
+ $data['ical_recur_id'] = gmdate(RFC5545_FORMAT . '\Z',
$data['start_time']);
+ }
$result = mrbsCreateEntry($tbl_entry, $data);
return $result;
}
@@ -796,9 +804,10 @@
{
switch ($field['name'])
{
- // repeat_id and entry_type only exist in the entry table
+ // these fields only exist in the entry table
case 'entry_type':
case 'repeat_id':
+ case 'ical_recur_id':
array_push($terms, $field['name']);
break;
Added: mrbs/branches/ics_attachments/web/upgrade/24/mysql.sql
===================================================================
--- mrbs/branches/ics_attachments/web/upgrade/24/mysql.sql
(rev 0)
+++ mrbs/branches/ics_attachments/web/upgrade/24/mysql.sql 2010-12-13
16:23:37 UTC (rev 1696)
@@ -0,0 +1,6 @@
+# $Id$
+
+# Add RECURRENCE-ID column for use with iCalendar
+
+ALTER TABLE %DB_TBL_PREFIX%entry
+ADD COLUMN ical_recur_id varchar(16) DEFAULT '' NOT NULL;
Property changes on: mrbs/branches/ics_attachments/web/upgrade/24/mysql.sql
___________________________________________________________________
Added: svn:keywords
+ Id
Added: svn:eol-style
+ native
Added: mrbs/branches/ics_attachments/web/upgrade/24/pgsql.sql
===================================================================
--- mrbs/branches/ics_attachments/web/upgrade/24/pgsql.sql
(rev 0)
+++ mrbs/branches/ics_attachments/web/upgrade/24/pgsql.sql 2010-12-13
16:23:37 UTC (rev 1696)
@@ -0,0 +1,6 @@
+-- $Id$
+
+-- Add RECURRENCE-ID column for use with iCalendar
+
+ALTER TABLE %DB_TBL_PREFIX%entry
+ADD COLUMN ical_recur_id varchar(16) DEFAULT '' NOT NULL;
Property changes on: mrbs/branches/ics_attachments/web/upgrade/24/pgsql.sql
___________________________________________________________________
Added: svn:keywords
+ Id
Added: svn:eol-style
+ native
Added: mrbs/branches/ics_attachments/web/upgrade/24/post.inc
===================================================================
--- mrbs/branches/ics_attachments/web/upgrade/24/post.inc
(rev 0)
+++ mrbs/branches/ics_attachments/web/upgrade/24/post.inc 2010-12-13
16:23:37 UTC (rev 1696)
@@ -0,0 +1,154 @@
+<?php
+
+// $Id$
+
+// This upgrade assings a UID to all existing bookings and a RECURRENCE-ID to
all
+// existing members of a series. However the RECURRENCE-ID is not
necessarily going
+// to be correct if the entry is a modified entry (ie
entry_type=ENTRY_RPT_CHANGED).
+// That's because the RECURRENCE_ID is supposed to be the *original* start
date and
+// time of that entry, and this is information that we no longer have. (We do
know
+// the time, as we have the start time in the repeat table, but it is not
trivial to
+// take advantage of it as we'd have to account for DST changes and we still
don't
+// have the date. So I've taken the view here that it's not worth bothering
about
+// as this upgrade procedure is only used once for existing entries).
+
+function get_microtime()
+{
+ if (function_exists('microtime'))
+ {
+ list($usec, $sec) = explode(" ", microtime());
+ return ((float)$usec + (float)$sec);
+ }
+ else
+ {
+ return time();
+ }
+}
+
+function do_sql_command($sql, $step)
+{
+ if (sql_command($sql) < 0)
+ {
+ // echo "$sql<br>\n"; // Debug line
+ // echo sql_error() . "<br>"; // Debug line
+ echo "<span class=\"error\">Step $step failed</span><br>";
+ // exit; // Debug line
+ }
+}
+
+global $dbsys, $tbl_entry, $tbl_repeat;
+
+// There's the option here to display some timing information as this upgrade
+// could take a while
+$display_timing = FALSE;
+
+$n_entry = sql_query1("SELECT COUNT(*) FROM $tbl_entry");
+$n_repeat = sql_query1("SELECT COUNT(*) FROM $tbl_repeat");
+if ($display_timing)
+{
+ echo "Upgrade 24: $n_entry entry rows and $n_repeat repeat rows";
+}
+
+$start_clock = get_microtime();
+
+// MySQL (mysql and mysqli)
+// ------------------------
+if ($dbsys != "pgsql")
+{
+ // Give all the individual entries, that haven't already got one, an ical uid
+ $sql = "UPDATE $tbl_entry SET ical_uid=CONCAT(CAST(id AS char), '-', UUID())
WHERE ical_uid='' AND repeat_id=0";
+ do_sql_command($sql, 1);
+
+ // Give everything in the repeat table, that doesn't already have one, an
ical uid
+ $sql = "UPDATE $tbl_repeat SET ical_uid=CONCAT(CAST(id AS char), '-',
UUID()) WHERE ical_uid=''";
+ do_sql_command($sql, 2);
+
+ // Now go through the entry table and give all entries, that don't have an
ical uid
+ // and are members of a series, the ical_uid from the corresponding uid from
the
+ // repeat table
+ $sql = "UPDATE $tbl_entry E, $tbl_repeat R
+ SET E.ical_uid=R.ical_uid
+ WHERE E.ical_uid=''
+ AND E.repeat_id=R.id";
+ do_sql_command($sql, 3);
+
+ // Finally give a recurrence id to any entry in the entry table that hasn't
got one
+ // and should have one - ie if it is a member of a series
+ $sql = "UPDATE $tbl_entry
+ SET
ical_recur_id=DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(start_time),
@@session.time_zone, '+0:00'), '%Y%m%dT%H%i%sZ')
+ WHERE repeat_id!=0
+ AND ical_recur_id=''";
+ do_sql_command($sql, 4);
+}
+
+// PostgreSQL
+// ----------
+else
+{
+ // PostgreSQL doesn't have a UUID() function as standard, so we have to
construct
+ // our own UUID.
+ //
+ // We will generate a uid of the form "mrbs-uniqid-md5h...@domain_name"
+ // where uniqid is time based and is generated by uniqid() and the
+ // MD5hash is the first 8 characters of the MD5 hash of $str concatenated
+ // with a random number. [This is the same process used by the MRBS function
+ // generate_global_uid()]
+
+ if (empty($_SERVER['SERVER_NAME']))
+ {
+ $domain_name .= 'MRBS';
+ }
+ elseif (strpos($_SERVER['SERVER_NAME'], 'www.') === 0)
+ {
+ $domain_name .= substr($_SERVER['SERVER_NAME'], 4);
+ }
+ else
+ {
+ $domain_name .= $_SERVER['SERVER_NAME'];
+ }
+
+ // Give all the individual entries, that haven't already got one, an ical uid
+ $sql = "UPDATE $tbl_entry
+ SET ical_uid='MRBS-' || CAST(id AS varchar(255)) || '-' ||
CURRENT_DATE || CURRENT_TIME || '-' || SUBSTRING((MD5(name || CAST(RANDOM() AS
varchar(255)))) from 1 for 8) || '@$domain_name'
+ WHERE ical_uid=''
+ AND repeat_id=0";
+ do_sql_command($sql, 1);
+
+ // Give everything in the repeat table, that doesn't already have one, an
ical uid
+ $sql = "UPDATE $tbl_repeat
+ SET ical_uid='MRBS-' || CAST(id AS varchar(255)) || '-' ||
CURRENT_DATE || CURRENT_TIME || '-' || SUBSTRING((MD5(name || CAST(RANDOM() AS
varchar(255)))) from 1 for 8) || '@$domain_name'
+ WHERE ical_uid=''";
+ do_sql_command($sql, 2);
+
+ // Now go through the entry table and give all entries, that don't have an
ical uid
+ // and are members of a series, the ical_uid from the corresponding uid from
the
+ // repeat table. (The SQL is slightly different from the MySQL case)
+ $sql = "UPDATE $tbl_entry
+ SET ical_uid=R.ical_uid
+ FROM $tbl_repeat AS R
+ WHERE $tbl_entry.ical_uid=''
+ AND $tbl_entry.repeat_id=R.id";
+ do_sql_command($sql, 3);
+
+ // Finally give a recurrence id to any entry in the entry table that hasn't
got one
+ // and should have one - ie if it is a member of a series (The SQL is
slightly
+ // different from the MySQL case)
+ $sql = "UPDATE $tbl_entry
+ SET ical_recur_id=TO_CHAR(TIMESTAMP 'epoch' + start_time *
INTERVAL '1 second', 'YYYYMMDD\"T\"HH24MISS\"Z\"')
+ WHERE repeat_id!=0
+ AND ical_recur_id=''";
+ do_sql_command($sql, 4);
+}
+
+$stop_clock = get_microtime();
+$clock_diff = $stop_clock - $start_clock;
+if (is_float($start_clock))
+{
+ $clock_diff = sprintf('%.3f', $clock_diff);
+}
+if ($display_timing)
+{
+ echo " processed in $clock_diff seconds<br>\n";
+}
+
+?>
\ No newline at end of file
Property changes on: mrbs/branches/ics_attachments/web/upgrade/24/post.inc
___________________________________________________________________
Added: svn:mime-type
+ text/x-php
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.
------------------------------------------------------------------------------
Oracle to DB2 Conversion Guide: Learn learn about native support for PL/SQL,
new data types, scalar functions, improved concurrency, built-in packages,
OCI, SQL*Plus, data movement tools, best practices and more.
http://p.sf.net/sfu/oracle-sfdev2dev
_______________________________________________
Mrbs-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
