Changeset:
e0bb76236687
https://sourceforge.net/p/mrbs/hg-code/ci/e0bb762366876d6203c77ab96e6414e312b3bdce
Author:
Campbell Morrison <[email protected]>
Date:
Thu Oct 22 12:36:07 2015 +0100
Log message:
Added upgrade script to give an ical_uid to those entries that don't have one
(see SF Bugs #334)
diffstat:
tables.my.sql | 2 +-
tables.pg.sql | 2 +-
web/dbsys.inc | 2 +-
web/upgrade/47/mysql.sql | 1 +
web/upgrade/47/post.inc | 182 +++++++++++++++++++++++++++++++++++++++++++++++
5 files changed, 186 insertions(+), 3 deletions(-)
diffs (225 lines):
diff -r 6c316a697ab5 -r e0bb76236687 tables.my.sql
--- a/tables.my.sql Wed Oct 21 17:03:13 2015 +0100
+++ b/tables.my.sql Thu Oct 22 12:36:07 2015 +0100
@@ -189,6 +189,6 @@
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ( 'db_version', '46');
+ VALUES ( 'db_version', '47');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ( 'local_db_version', '1');
diff -r 6c316a697ab5 -r e0bb76236687 tables.pg.sql
--- a/tables.pg.sql Wed Oct 21 17:03:13 2015 +0100
+++ b/tables.pg.sql Thu Oct 22 12:36:07 2015 +0100
@@ -180,6 +180,6 @@
);
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ('db_version', '46');
+ VALUES ('db_version', '47');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ('local_db_version', '1');
diff -r 6c316a697ab5 -r e0bb76236687 web/dbsys.inc
--- a/web/dbsys.inc Wed Oct 21 17:03:13 2015 +0100
+++ b/web/dbsys.inc Thu Oct 22 12:36:07 2015 +0100
@@ -23,7 +23,7 @@
}
-$db_schema_version = 46;
+$db_schema_version = 47;
$local_db_schema_version = 1;
// Include the abstraction configured to be used for the default MRBS
diff -r 6c316a697ab5 -r e0bb76236687 web/upgrade/47/mysql.sql
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/web/upgrade/47/mysql.sql Thu Oct 22 12:36:07 2015 +0100
@@ -0,0 +1,1 @@
+#
diff -r 6c316a697ab5 -r e0bb76236687 web/upgrade/47/post.inc
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/web/upgrade/47/post.inc Thu Oct 22 12:36:07 2015 +0100
@@ -0,0 +1,182 @@
+<?php
+
+// $Id$
+
+// Assigns a UID to all existing bookings that don't already have one. They
should
+// do in theory, but a bug (https://sourceforge.net/p/mrbs/bugs/334/) resulted
in some
+// not doing so. The code is based on Upgrade 24.
+
+// 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)
+{
+ static $step = 0;
+
+ $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 47: $n_entry entry rows and $n_repeat repeat rows";
+}
+
+$start_clock = get_microtime();
+
+
+// In these queries we set timestamp=timestamp to prevent it being
automatically set
+// to the current time (only applies to MySQL - PostgreSQL timestamps don't
update)
+
+// MySQL (mysql and mysqli)
+// ------------------------
+if ($dbsys != "pgsql")
+{
+ // 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()),
+ timestamp=timestamp
+ WHERE ical_uid=''";
+ do_sql_command($sql);
+
+ // 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,
+ E.timestamp=E.timestamp,
+ R.timestamp=R.timestamp
+ WHERE E.ical_uid=''
+ AND E.repeat_id=R.id";
+ do_sql_command($sql);
+
+ // 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'),
+ timestamp=timestamp
+ WHERE repeat_id!=0
+ AND ical_recur_id=''";
+ do_sql_command($sql);
+
+ // Give all the individual entries, that haven't already got one, an ical uid
+ // (There shouldn't be any of these as the bug was only affecting repeats)
+ $sql = "UPDATE $tbl_entry
+ SET ical_uid=CONCAT(CAST(id AS char), '-', UUID()),
+ timestamp=timestamp
+ WHERE ical_uid='' AND repeat_id IS NULL";
+ do_sql_command($sql);
+
+ // Sequence numbers were being generated correctly under the bug, so we
don't need to worry about them
+}
+
+// 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-MD5hash@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 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);
+
+ // 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 E
+ SET ical_uid=R.ical_uid
+ FROM $tbl_repeat AS R
+ WHERE E.ical_uid=''
+ AND E.repeat_id=R.id";
+ do_sql_command($sql);
+
+ // 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);
+
+ // Give all the individual entries, that haven't already got one, an ical uid
+ // (There shouldn't be any of these as the bug was only affecting repeats)
+ $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 IS NULL";
+ do_sql_command($sql);
+
+ // Sequence numbers were being generated correctly under the bug, so we
don't need to worry about them
+}
+
+
+
+$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";
+}
+
------------------------------------------------------------------------------
_______________________________________________
Mrbs-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
