Revision: 2496
https://sourceforge.net/p/mrbs/code/2496/
Author: cimorrison
Date: 2012-10-14 12:28:51 +0000 (Sun, 14 Oct 2012)
Log Message:
-----------
Upgraded database, converting monthly_sameday repeats into monthly repeats with
a month_relative field (eg "2TU" or "-1SU").
Modified Paths:
--------------
mrbs/branches/improved_repeat_interface/tables.my.pre41.sql
mrbs/branches/improved_repeat_interface/tables.my.sql
mrbs/branches/improved_repeat_interface/tables.pg.pre73.sql
mrbs/branches/improved_repeat_interface/tables.pg.sql
mrbs/branches/improved_repeat_interface/web/dbsys.inc
mrbs/branches/improved_repeat_interface/web/functions.inc
Added Paths:
-----------
mrbs/branches/improved_repeat_interface/web/upgrade/34/
mrbs/branches/improved_repeat_interface/web/upgrade/34/mysql.sql
mrbs/branches/improved_repeat_interface/web/upgrade/34/pgsql.sql
mrbs/branches/improved_repeat_interface/web/upgrade/34/post.inc
mrbs/branches/improved_repeat_interface/web/upgrade/35/
mrbs/branches/improved_repeat_interface/web/upgrade/35/mysql.sql
mrbs/branches/improved_repeat_interface/web/upgrade/35/pgsql.sql
Modified: mrbs/branches/improved_repeat_interface/tables.my.pre41.sql
===================================================================
--- mrbs/branches/improved_repeat_interface/tables.my.pre41.sql 2012-10-14
11:53:34 UTC (rev 2495)
+++ mrbs/branches/improved_repeat_interface/tables.my.pre41.sql 2012-10-14
12:28:51 UTC (rev 2496)
@@ -118,6 +118,7 @@
description text,
rep_num_weeks smallint NULL,
month_absolute smallint DEFAULT NULL,
+ month_relative varchar(4) DEFAULT NULL,
status tinyint unsigned NOT NULL DEFAULT 0,
reminded int,
info_time int,
@@ -162,6 +163,6 @@
);
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ( 'db_version', '33');
+ VALUES ( 'db_version', '35');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ( 'local_db_version', '1');
Modified: mrbs/branches/improved_repeat_interface/tables.my.sql
===================================================================
--- mrbs/branches/improved_repeat_interface/tables.my.sql 2012-10-14
11:53:34 UTC (rev 2495)
+++ mrbs/branches/improved_repeat_interface/tables.my.sql 2012-10-14
12:28:51 UTC (rev 2496)
@@ -118,6 +118,7 @@
description text CHARACTER SET utf8 COLLATE utf8_general_ci,
rep_num_weeks smallint NULL,
month_absolute smallint DEFAULT NULL,
+ month_relative varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT
NULL,
status tinyint unsigned NOT NULL DEFAULT 0,
reminded int,
info_time int,
@@ -162,6 +163,6 @@
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ( 'db_version', '33');
+ VALUES ( 'db_version', '35');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ( 'local_db_version', '1');
Modified: mrbs/branches/improved_repeat_interface/tables.pg.pre73.sql
===================================================================
--- mrbs/branches/improved_repeat_interface/tables.pg.pre73.sql 2012-10-14
11:53:34 UTC (rev 2495)
+++ mrbs/branches/improved_repeat_interface/tables.pg.pre73.sql 2012-10-14
12:28:51 UTC (rev 2496)
@@ -114,7 +114,8 @@
type char DEFAULT 'E' NOT NULL,
description text,
rep_num_weeks smallint DEFAULT NULL NULL,
- month_absolute smallint,
+ month_absolute smallint DEFAULT NULL,
+ month_relative varchar(4) DEFAULT NULL,
status smallint DEFAULT 0 NOT NULL,
reminded int,
info_time int,
@@ -151,6 +152,6 @@
);
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ('db_version', '33');
+ VALUES ('db_version', '35');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ('local_db_version', '1');
Modified: mrbs/branches/improved_repeat_interface/tables.pg.sql
===================================================================
--- mrbs/branches/improved_repeat_interface/tables.pg.sql 2012-10-14
11:53:34 UTC (rev 2495)
+++ mrbs/branches/improved_repeat_interface/tables.pg.sql 2012-10-14
12:28:51 UTC (rev 2496)
@@ -127,7 +127,8 @@
type char DEFAULT 'E' NOT NULL,
description text,
rep_num_weeks smallint DEFAULT 0 NULL,
- month_absolute smallint,
+ month_absolute smallint DEFAULT NULL,
+ month_relative varchar(4) DEFAULT NULL,
status smallint DEFAULT 0 NOT NULL,
reminded int,
info_time int,
@@ -164,6 +165,6 @@
);
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ('db_version', '33');
+ VALUES ('db_version', '35');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ('local_db_version', '1');
Modified: mrbs/branches/improved_repeat_interface/web/dbsys.inc
===================================================================
--- mrbs/branches/improved_repeat_interface/web/dbsys.inc 2012-10-14
11:53:34 UTC (rev 2495)
+++ mrbs/branches/improved_repeat_interface/web/dbsys.inc 2012-10-14
12:28:51 UTC (rev 2496)
@@ -16,7 +16,7 @@
$tbl_zoneinfo = $db_tbl_prefix . "zoneinfo";
-$db_schema_version = 33;
+$db_schema_version = 35;
$local_db_schema_version = 1;
Modified: mrbs/branches/improved_repeat_interface/web/functions.inc
===================================================================
--- mrbs/branches/improved_repeat_interface/web/functions.inc 2012-10-14
11:53:34 UTC (rev 2495)
+++ mrbs/branches/improved_repeat_interface/web/functions.inc 2012-10-14
12:28:51 UTC (rev 2496)
@@ -363,6 +363,22 @@
}
+// Returns the BYDAY value for a given timestamp, eg 4SU for fourth Sunday in
+// the month, or -1MO for the last Monday.
+function date_byday($timestamp)
+{
+ $days_of_week = array('SU', 'MO', 'TU', 'WE', 'TH', 'FR', 'SA');
+ $dow = $days_of_week[date('w', $timestamp)];
+ $dom = date('j', $timestamp);
+ $ord = intval(($dom - 1)/7) + 1;
+ if ($ord == 5)
+ {
+ $ord = -1;
+ }
+ return $ord . $dow;
+}
+
+
// Returns TRUE if the time $hm1 is before $hm2
// $hm1 and $hm2 are associative arrays indexed by 'hours' and 'minutes'.
// The indices are chosen to allow the result of the PHP getdate() function
Added: mrbs/branches/improved_repeat_interface/web/upgrade/34/mysql.sql
===================================================================
--- mrbs/branches/improved_repeat_interface/web/upgrade/34/mysql.sql
(rev 0)
+++ mrbs/branches/improved_repeat_interface/web/upgrade/34/mysql.sql
2012-10-14 12:28:51 UTC (rev 2496)
@@ -0,0 +1,15 @@
+# $Id$
+
+# Add a month_relative column so that monthly sameday repeats can be converted
to the
+# new format. The conversion itself is done in post.inc
+
+# We save the current value of the timestamp before updating and restore it
+# afterwards because we do not want the timestamp to be changed by this
operation.
+# The restoration is done in the next upgrade because we need to operate on
the table
+# first using post.inc
+ALTER TABLE %DB_TBL_PREFIX%repeat
+ADD COLUMN saved_ts DATETIME;
+UPDATE %DB_TBL_PREFIX%repeat SET saved_ts=timestamp;
+
+ALTER TABLE %DB_TBL_PREFIX%repeat
+ADD COLUMN month_relative varchar(4) DEFAULT NULL;
Property changes on:
mrbs/branches/improved_repeat_interface/web/upgrade/34/mysql.sql
___________________________________________________________________
Added: svn:keywords
+ Id
Added: svn:eol-style
+ native
Added: mrbs/branches/improved_repeat_interface/web/upgrade/34/pgsql.sql
===================================================================
--- mrbs/branches/improved_repeat_interface/web/upgrade/34/pgsql.sql
(rev 0)
+++ mrbs/branches/improved_repeat_interface/web/upgrade/34/pgsql.sql
2012-10-14 12:28:51 UTC (rev 2496)
@@ -0,0 +1,7 @@
+-- $Id$
+
+-- Add a month_relative column so that monthly repeats can be converted to the
+-- new format. The conversion itself is done in post.inc
+
+ALTER TABLE %DB_TBL_PREFIX%repeat
+ADD COLUMN month_relative varchar(4) DEFAULT NULL;
Property changes on:
mrbs/branches/improved_repeat_interface/web/upgrade/34/pgsql.sql
___________________________________________________________________
Added: svn:keywords
+ Id
Added: svn:eol-style
+ native
Added: mrbs/branches/improved_repeat_interface/web/upgrade/34/post.inc
===================================================================
--- mrbs/branches/improved_repeat_interface/web/upgrade/34/post.inc
(rev 0)
+++ mrbs/branches/improved_repeat_interface/web/upgrade/34/post.inc
2012-10-14 12:28:51 UTC (rev 2496)
@@ -0,0 +1,57 @@
+<?php
+
+// $Id$
+
+// Convert MONTHLY_SAMEDAY repeats into MONTHLY repeats with a specified
month_relative (eg '2TU')
+
+require "standard_vars.inc.php"; // to ensure the timezone is set
+
+global $tbl_repeat, $tbl_area, $tbl_room;
+
+// Get all the monthly sameday repeats.
+$sql = "SELECT T.id, T.start_time, A.timezone
+ FROM $tbl_repeat T, $tbl_area A, $tbl_room M
+ WHERE T.rep_type=" . REP_MONTHLY_SAMEDAY . "
+ AND T.room_id=M.id
+ AND M.area_id=A.id";
+
+$res = sql_query($sql);
+
+if ($res === FALSE)
+{
+ trigger_error(sql_error(), E_USER_WARNING);
+ fatal_error(FALSE, get_vocab("fatal_db_error"));
+}
+
+// Save the current default timezone so that we can restore it at the end
+$old_tz = mrbs_default_timezone_get();
+
+$mods = array();
+
+for ($i = 0; ($row = sql_row_keyed($res, $i)); $i++)
+{
+ // We need to make sure that we have the correct timezone set when we work
out
+ // the day of the month that this series repeats on
+ if (!empty($row['timezone']))
+ {
+ mrbs_default_timezone_set($row['timezone']);
+ }
+ $mods[$row['id']] = date_byday($row['start_time']);
+}
+
+foreach ($mods as $id => $month_relative)
+{
+ $sql = "UPDATE $tbl_repeat
+ SET month_relative='" . sql_escape($month_relative) . "',
+ rep_type=" . REP_MONTHLY . "
+ WHERE id=$id";
+ if (sql_command($sql) < 0)
+ {
+ trigger_error(sql_error(), E_USER_WARNING);
+ fatal_error(FALSE, get_vocab("fatal_db_error"));
+ }
+}
+
+// Restore the original timezone
+mrbs_default_timezone_set($old_tz);
+?>
\ No newline at end of file
Property changes on:
mrbs/branches/improved_repeat_interface/web/upgrade/34/post.inc
___________________________________________________________________
Added: svn:mime-type
+ text/x-php
Added: svn:keywords
+ Id
Added: svn:eol-style
+ native
Added: mrbs/branches/improved_repeat_interface/web/upgrade/35/mysql.sql
===================================================================
--- mrbs/branches/improved_repeat_interface/web/upgrade/35/mysql.sql
(rev 0)
+++ mrbs/branches/improved_repeat_interface/web/upgrade/35/mysql.sql
2012-10-14 12:28:51 UTC (rev 2496)
@@ -0,0 +1,10 @@
+# $Id$
+
+# Restore the original timestamp at the beginning of the previous upgrade
+
+# We save the current value of the timestamp before updating and restore it
+# afterwards because we do not want the timestamp to be changed by this
operation
+
+UPDATE %DB_TBL_PREFIX%repeat SET timestamp=saved_ts;
+ALTER TABLE %DB_TBL_PREFIX%repeat
+DROP COLUMN saved_ts;
Property changes on:
mrbs/branches/improved_repeat_interface/web/upgrade/35/mysql.sql
___________________________________________________________________
Added: svn:keywords
+ Id
Added: svn:eol-style
+ native
Added: mrbs/branches/improved_repeat_interface/web/upgrade/35/pgsql.sql
===================================================================
--- mrbs/branches/improved_repeat_interface/web/upgrade/35/pgsql.sql
(rev 0)
+++ mrbs/branches/improved_repeat_interface/web/upgrade/35/pgsql.sql
2012-10-14 12:28:51 UTC (rev 2496)
@@ -0,0 +1,4 @@
+-- $Id$
+
+-- This is an empty file because PostgreSQL does not have automatic updating of
+-- timestamps, which is all that ios being done in this upgrade
Property changes on:
mrbs/branches/improved_repeat_interface/web/upgrade/35/pgsql.sql
___________________________________________________________________
Added: svn:keywords
+ Id
Added: svn:eol-style
+ native
------------------------------------------------------------------------------
Don't let slow site performance ruin your business. Deploy New Relic APM
Deploy New Relic app performance management and know exactly
what is happening inside your Ruby, Python, PHP, Java, and .NET app
Try New Relic at no cost today and get our sweet Data Nerd shirt too!
http://p.sf.net/sfu/newrelic-dev2dev
_______________________________________________
Mrbs-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
