From b672fc7ae113c0e01f1d7ce4ffae3eb26e57b586 Mon Sep 17 00:00:00 2001 From: Oleg Pudeyev Date: Tue, 26 Apr 2011 22:11:45 -0400 Subject: [feature/new-tz-handling] Started on database updater changes. The changes are tricky since we need to drop user_dst column from users table, but we need it during the data migration process to properly calculate effective timezones for all users. The change here converts user_timezone to vchar and computes timezone identifiers from the offsets. It uses database-specific functions for building SQL conditionals and concatenations which need to be implemented, probably in a separate ticket. As a result the current code is not functional. PHPBB3-9558 --- phpBB/install/database_update.php | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index fa0fba9dc7..9d550cf938 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -1090,6 +1090,9 @@ function database_update_info() GROUPS_TABLE => array( 'group_legend' => array('UINT', 0), ), + USERS_TABLE => array( + 'user_timezone' => array('VCHAR:100', ''), + ), ), 'drop_columns' => array( STYLES_TABLE => array( @@ -2352,6 +2355,25 @@ function change_database_data(&$no_updates, $version) set_config('teampage_memberships', '1'); } + // Update timezones + // user_dst is 0 if not in dst and 1 if in dst; + // this happens to be exactly the correction that should be added to the timezone offset + // to obtain dst offset. + // Parenthesize here because we operate on this value later. + $active_offset = '(user_timezone + user_dst)'; + // Now we have a tricky problem of forcing the plus sign into the expression. + // Build it via a conditional since there cannot be a portable printf equivalent in databases. + // Note that active offset is not an absolute value here - it is an expression that will + // be evaluated by the database during query execution. + // We don't print - here because it will come from active offset. + $sign = $db->conditional_sql("$active_offset < 0", '', '+'); + // Use database-specific escaping because strings are quoted differently by different databases. + $new_value = $db->concatenate_sql($db->sql_escape('GMT'), $sign, $active_offset); + $sql = 'UPDATE ' . USERS_TABLE . ' + SET user_timezone = ' . $new_value; + _sql($sql, $errored, $error_ary); + // After we have calculated the timezones we can delete user_dst column from user table. + $no_updates = false; break; } -- cgit v1.2.1 From 3c6272ff0475dc19cc67553f370ce227214d0613 Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Mon, 4 Jun 2012 23:28:48 +0200 Subject: [feature/new-tz-handling] Remove appearances of board_dst and user_dst PHPBB3-9558 --- phpBB/install/database_update.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 9758ad3b2b..b6f7f82785 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -1971,7 +1971,7 @@ function change_database_data(&$no_updates, $version) 'user_email' => '', 'user_lang' => $config['default_lang'], 'user_style' => $config['default_style'], - 'user_timezone' => 0, + 'user_timezone' => 'UTC', 'user_dateformat' => $config['default_dateformat'], 'user_allow_massemail' => 0, ); -- cgit v1.2.1 From 902a79bfdabb2c459f2385cff80b5fa38099d2f7 Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Wed, 20 Jun 2012 00:50:33 +0200 Subject: [feature/new-tz-handling] Readd Database updater changes from Oleg The change here converts user_timezone to vchar and computes timezone identifiers from the offsets. It uses database-specific functions for building SQL conditionals and concatenations which need to be implemented, probably in a separate ticket. As a result the current code is not functional. The changes are tricky since we need to drop user_dst column from users table, but we need it during the data migration process to properly calculate effective timezones for all users. PHPBB3-9558 --- phpBB/install/database_update.php | 27 +++++++++++++++++++++++++++ 1 file changed, 27 insertions(+) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index b6f7f82785..dd2d04d3a6 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -2588,6 +2588,33 @@ function change_database_data(&$no_updates, $version) $config->set('assets_version', '1'); } + // If the column exists, we did not yet update the users timezone + if ($db_tools->sql_column_exists(USERS_TABLE, 'user_dst')) + { + // Update timezones + // user_dst is 0 if not in dst and 1 if in dst; + // this happens to be exactly the correction that should be added to the timezone offset + // to obtain dst offset. + // Parenthesize here because we operate on this value later. + $active_offset = '(user_timezone + user_dst)'; + + // Now we have a tricky problem of forcing the plus sign into the expression. + // Build it via a conditional since there cannot be a portable printf equivalent in databases. + // Note that active offset is not an absolute value here - it is an expression that will + // be evaluated by the database during query execution. + // We don't print - (minus) here because it will come from active offset. + $sign = $db->sql_conditional("$active_offset < 0", '', '+'); + + // Use database-specific escaping because strings are quoted differently by different databases. + $new_value = $db->sql_concatenate($db->sql_escape('GMT'), $sign, $active_offset); + $sql = 'UPDATE ' . USERS_TABLE . ' + SET user_timezone = ' . $new_value; + _sql($sql, $errored, $error_ary); + + // After we have calculated the timezones we can delete user_dst column from user table. + $db_tools->sql_column_remove(USERS_TABLE, 'user_dst'); + } + break; } } -- cgit v1.2.1 From 7ce66deca8dbe8187b86e130e8011be998580b4e Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Wed, 18 Jul 2012 14:48:28 +0200 Subject: [feature/new-tz-handling] Correctly update user and board timezones on update PHPBB3-9558 --- phpBB/install/database_update.php | 37 ++++++++++++++++++------------------- 1 file changed, 18 insertions(+), 19 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 360e12e0b6..a3a51324a9 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -2613,25 +2613,24 @@ function change_database_data(&$no_updates, $version) // If the column exists, we did not yet update the users timezone if ($db_tools->sql_column_exists(USERS_TABLE, 'user_dst')) { - // Update timezones - // user_dst is 0 if not in dst and 1 if in dst; - // this happens to be exactly the correction that should be added to the timezone offset - // to obtain dst offset. - // Parenthesize here because we operate on this value later. - $active_offset = '(user_timezone + user_dst)'; - - // Now we have a tricky problem of forcing the plus sign into the expression. - // Build it via a conditional since there cannot be a portable printf equivalent in databases. - // Note that active offset is not an absolute value here - it is an expression that will - // be evaluated by the database during query execution. - // We don't print - (minus) here because it will come from active offset. - $sign = $db->sql_conditional("$active_offset < 0", '', '+'); - - // Use database-specific escaping because strings are quoted differently by different databases. - $new_value = $db->sql_concatenate($db->sql_escape('GMT'), $sign, $active_offset); - $sql = 'UPDATE ' . USERS_TABLE . ' - SET user_timezone = ' . $new_value; - _sql($sql, $errored, $error_ary); + // Update user timezones + $sql = 'SELECT user_dst, user_timezone + FROM ' . USERS_TABLE . ' + GROUP BY user_timezone, user_dst'; + $result = $db->sql_query($sql); + + while ($row = $db->sql_fetchrow($result)) + { + $sql = 'UPDATE ' . USERS_TABLE . " + SET user_timezone = '" . $db->sql_escape(_convert_phpbb30_timezone($row['user_timezone'], $row['user_dst'])) . "' + WHERE user_timezone = '" . $db->sql_escape($row['user_timezone']) . "' + AND user_dst = " . (int) $row['user_dst']; + _sql($sql, $errored, $error_ary); + } + $db->sql_freeresult($result); + + // Update board default timezone + set_config('board_timezone', _convert_phpbb30_timezone($config['board_timezone'], $config['board_dst'])); // After we have calculated the timezones we can delete user_dst column from user table. $db_tools->sql_column_remove(USERS_TABLE, 'user_dst'); -- cgit v1.2.1 From 8078a04f3a3816b4515da01eb3635ae8141db26b Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Wed, 18 Jul 2012 14:52:51 +0200 Subject: [feature/new-tz-handling] Add function to update the timezone PHPBB3-9558 --- phpBB/install/database_update.php | 99 +++++++++++++++++++++++++++++++++++++++ 1 file changed, 99 insertions(+) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index a3a51324a9..43363babe1 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -809,6 +809,105 @@ function _add_modules($modules_to_install) $_module->remove_cache_file(); } +/** +* Determinate the new timezone for a given phpBB 3.0 timezone and +* "Daylight Saving Time" option +* +* @param $timezone float Users timezone in 3.0 +* @param $dst int Users daylight saving time +* @return string Users new php Timezone which is used since 3.1 +*/ +function _convert_phpbb30_timezone($timezone, $dst) +{ + $offset = $timezone + $dst; + + switch ($timezone) + { + case '-12': + return 'Etc/GMT' . $offset; //'[UTC - 12] Baker Island Time' + case '-11': + return 'Etc/GMT' . $offset; //'[UTC - 11] Niue Time, Samoa Standard Time' + case '-10': + return 'Etc/GMT' . $offset; //'[UTC - 10] Hawaii-Aleutian Standard Time, Cook Island Time' + case '-9.5': + return 'Pacific/Marquesas'; //'[UTC - 9:30] Marquesas Islands Time' + case '-9': + return 'Etc/GMT' . $offset; //'[UTC - 9] Alaska Standard Time, Gambier Island Time' + case '-8': + return 'Etc/GMT' . $offset; //'[UTC - 8] Pacific Standard Time' + case '-7': + return 'Etc/GMT' . $offset; //'[UTC - 7] Mountain Standard Time' + case '-6': + return 'Etc/GMT' . $offset; //'[UTC - 6] Central Standard Time' + case '-5': + return 'Etc/GMT' . $offset; //'[UTC - 5] Eastern Standard Time' + case '-4.5': + return 'America/Caracas'; //'[UTC - 4:30] Venezuelan Standard Time' + case '-4': + return 'Etc/GMT' . $offset; //'[UTC - 4] Atlantic Standard Time' + case '-3.5': + return 'America/St_Johns'; //'[UTC - 3:30] Newfoundland Standard Time' + case '-3': + return 'Etc/GMT' . $offset; //'[UTC - 3] Amazon Standard Time, Central Greenland Time' + case '-2': + return 'Etc/GMT' . $offset; //'[UTC - 2] Fernando de Noronha Time, South Georgia & the South Sandwich Islands Time' + case '-1': + return 'Etc/GMT' . $offset; //'[UTC - 1] Azores Standard Time, Cape Verde Time, Eastern Greenland Time' + case '0': + return (!$dst) ? 'UTC' : 'Etc/GMT+1'; //'[UTC] Western European Time, Greenwich Mean Time' + case '1': + return 'Etc/GMT+' . $offset; //'[UTC + 1] Central European Time, West African Time' + case '2': + return 'Etc/GMT+' . $offset; //'[UTC + 2] Eastern European Time, Central African Time' + case '3': + return 'Etc/GMT+' . $offset; //'[UTC + 3] Moscow Standard Time, Eastern African Time' + case '3.5': + return 'Asia/Tehran'; //'[UTC + 3:30] Iran Standard Time' + case '4': + return 'Etc/GMT+' . $offset; //'[UTC + 4] Gulf Standard Time, Samara Standard Time' + case '4.5': + return 'Asia/Kabul'; //'[UTC + 4:30] Afghanistan Time' + case '5': + return 'Etc/GMT+' . $offset; //'[UTC + 5] Pakistan Standard Time, Yekaterinburg Standard Time' + case '5.5': + return 'Asia/Kolkata'; //'[UTC + 5:30] Indian Standard Time, Sri Lanka Time' + case '5.75': + return 'Asia/Kathmandu'; //'[UTC + 5:45] Nepal Time' + case '6': + return 'Etc/GMT+' . $offset; //'[UTC + 6] Bangladesh Time, Bhutan Time, Novosibirsk Standard Time' + case '6.5': + return 'Indian/Cocos'; //'[UTC + 6:30] Cocos Islands Time, Myanmar Time' + case '7': + return 'Etc/GMT+' . $offset; //'[UTC + 7] Indochina Time, Krasnoyarsk Standard Time' + case '8': + return 'Etc/GMT+' . $offset; //'[UTC + 8] Chinese Standard Time, Australian Western Standard Time, Irkutsk Standard Time' + case '8.75': + return 'Australia/Eucla'; //'[UTC + 8:45] Southeastern Western Australia Standard Time' + case '9': + return 'Etc/GMT+' . $offset; //'[UTC + 9] Japan Standard Time, Korea Standard Time, Chita Standard Time' + case '9.5': + return 'Australia/ACT'; //'[UTC + 9:30] Australian Central Standard Time' + case '10': + return 'Etc/GMT+' . $offset; //'[UTC + 10] Australian Eastern Standard Time, Vladivostok Standard Time' + case '10.5': + return 'Australia/Lord_Howe'; //'[UTC + 10:30] Lord Howe Standard Time' + case '11': + return 'Etc/GMT+' . $offset; //'[UTC + 11] Solomon Island Time, Magadan Standard Time' + case '11.5': + return 'Pacific/Norfolk'; //'[UTC + 11:30] Norfolk Island Time' + case '12': + return 'Etc/GMT+12'; //'[UTC + 12] New Zealand Time, Fiji Time, Kamchatka Standard Time' + case '12.75': + return 'Pacific/Chatham'; //'[UTC + 12:45] Chatham Islands Time' + case '13': + return 'Pacific/Tongatapu'; //'[UTC + 13] Tonga Time, Phoenix Islands Time' + case '14': + return 'Pacific/Kiritimati'; //'[UTC + 14] Line Island Time' + default: + return 'UTC'; + } +} + /**************************************************************************** * ADD YOUR DATABASE SCHEMA CHANGES HERE * *****************************************************************************/ -- cgit v1.2.1 From 14a07b11936bc061b5c82daa6387eb4645974fac Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Wed, 18 Jul 2012 17:18:10 +0200 Subject: [feature/new-tz-handling] Move update helper function to new class PHPBB3-9558 --- phpBB/install/database_update.php | 105 ++------------------------------------ 1 file changed, 4 insertions(+), 101 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 43363babe1..6e99fd56a0 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -809,105 +809,6 @@ function _add_modules($modules_to_install) $_module->remove_cache_file(); } -/** -* Determinate the new timezone for a given phpBB 3.0 timezone and -* "Daylight Saving Time" option -* -* @param $timezone float Users timezone in 3.0 -* @param $dst int Users daylight saving time -* @return string Users new php Timezone which is used since 3.1 -*/ -function _convert_phpbb30_timezone($timezone, $dst) -{ - $offset = $timezone + $dst; - - switch ($timezone) - { - case '-12': - return 'Etc/GMT' . $offset; //'[UTC - 12] Baker Island Time' - case '-11': - return 'Etc/GMT' . $offset; //'[UTC - 11] Niue Time, Samoa Standard Time' - case '-10': - return 'Etc/GMT' . $offset; //'[UTC - 10] Hawaii-Aleutian Standard Time, Cook Island Time' - case '-9.5': - return 'Pacific/Marquesas'; //'[UTC - 9:30] Marquesas Islands Time' - case '-9': - return 'Etc/GMT' . $offset; //'[UTC - 9] Alaska Standard Time, Gambier Island Time' - case '-8': - return 'Etc/GMT' . $offset; //'[UTC - 8] Pacific Standard Time' - case '-7': - return 'Etc/GMT' . $offset; //'[UTC - 7] Mountain Standard Time' - case '-6': - return 'Etc/GMT' . $offset; //'[UTC - 6] Central Standard Time' - case '-5': - return 'Etc/GMT' . $offset; //'[UTC - 5] Eastern Standard Time' - case '-4.5': - return 'America/Caracas'; //'[UTC - 4:30] Venezuelan Standard Time' - case '-4': - return 'Etc/GMT' . $offset; //'[UTC - 4] Atlantic Standard Time' - case '-3.5': - return 'America/St_Johns'; //'[UTC - 3:30] Newfoundland Standard Time' - case '-3': - return 'Etc/GMT' . $offset; //'[UTC - 3] Amazon Standard Time, Central Greenland Time' - case '-2': - return 'Etc/GMT' . $offset; //'[UTC - 2] Fernando de Noronha Time, South Georgia & the South Sandwich Islands Time' - case '-1': - return 'Etc/GMT' . $offset; //'[UTC - 1] Azores Standard Time, Cape Verde Time, Eastern Greenland Time' - case '0': - return (!$dst) ? 'UTC' : 'Etc/GMT+1'; //'[UTC] Western European Time, Greenwich Mean Time' - case '1': - return 'Etc/GMT+' . $offset; //'[UTC + 1] Central European Time, West African Time' - case '2': - return 'Etc/GMT+' . $offset; //'[UTC + 2] Eastern European Time, Central African Time' - case '3': - return 'Etc/GMT+' . $offset; //'[UTC + 3] Moscow Standard Time, Eastern African Time' - case '3.5': - return 'Asia/Tehran'; //'[UTC + 3:30] Iran Standard Time' - case '4': - return 'Etc/GMT+' . $offset; //'[UTC + 4] Gulf Standard Time, Samara Standard Time' - case '4.5': - return 'Asia/Kabul'; //'[UTC + 4:30] Afghanistan Time' - case '5': - return 'Etc/GMT+' . $offset; //'[UTC + 5] Pakistan Standard Time, Yekaterinburg Standard Time' - case '5.5': - return 'Asia/Kolkata'; //'[UTC + 5:30] Indian Standard Time, Sri Lanka Time' - case '5.75': - return 'Asia/Kathmandu'; //'[UTC + 5:45] Nepal Time' - case '6': - return 'Etc/GMT+' . $offset; //'[UTC + 6] Bangladesh Time, Bhutan Time, Novosibirsk Standard Time' - case '6.5': - return 'Indian/Cocos'; //'[UTC + 6:30] Cocos Islands Time, Myanmar Time' - case '7': - return 'Etc/GMT+' . $offset; //'[UTC + 7] Indochina Time, Krasnoyarsk Standard Time' - case '8': - return 'Etc/GMT+' . $offset; //'[UTC + 8] Chinese Standard Time, Australian Western Standard Time, Irkutsk Standard Time' - case '8.75': - return 'Australia/Eucla'; //'[UTC + 8:45] Southeastern Western Australia Standard Time' - case '9': - return 'Etc/GMT+' . $offset; //'[UTC + 9] Japan Standard Time, Korea Standard Time, Chita Standard Time' - case '9.5': - return 'Australia/ACT'; //'[UTC + 9:30] Australian Central Standard Time' - case '10': - return 'Etc/GMT+' . $offset; //'[UTC + 10] Australian Eastern Standard Time, Vladivostok Standard Time' - case '10.5': - return 'Australia/Lord_Howe'; //'[UTC + 10:30] Lord Howe Standard Time' - case '11': - return 'Etc/GMT+' . $offset; //'[UTC + 11] Solomon Island Time, Magadan Standard Time' - case '11.5': - return 'Pacific/Norfolk'; //'[UTC + 11:30] Norfolk Island Time' - case '12': - return 'Etc/GMT+12'; //'[UTC + 12] New Zealand Time, Fiji Time, Kamchatka Standard Time' - case '12.75': - return 'Pacific/Chatham'; //'[UTC + 12:45] Chatham Islands Time' - case '13': - return 'Pacific/Tongatapu'; //'[UTC + 13] Tonga Time, Phoenix Islands Time' - case '14': - return 'Pacific/Kiritimati'; //'[UTC + 14] Line Island Time' - default: - return 'UTC'; - } -} - /**************************************************************************** * ADD YOUR DATABASE SCHEMA CHANGES HERE * *****************************************************************************/ @@ -1225,6 +1126,8 @@ function change_database_data(&$no_updates, $version) { global $db, $errored, $error_ary, $config, $phpbb_root_path, $phpEx, $db_tools; + $update_helpers = new phpbb_update_helpers(); + switch ($version) { case '3.0.0': @@ -2721,7 +2624,7 @@ function change_database_data(&$no_updates, $version) while ($row = $db->sql_fetchrow($result)) { $sql = 'UPDATE ' . USERS_TABLE . " - SET user_timezone = '" . $db->sql_escape(_convert_phpbb30_timezone($row['user_timezone'], $row['user_dst'])) . "' + SET user_timezone = '" . $db->sql_escape($update_helpers->convert_phpbb30_timezone($row['user_timezone'], $row['user_dst'])) . "' WHERE user_timezone = '" . $db->sql_escape($row['user_timezone']) . "' AND user_dst = " . (int) $row['user_dst']; _sql($sql, $errored, $error_ary); @@ -2729,7 +2632,7 @@ function change_database_data(&$no_updates, $version) $db->sql_freeresult($result); // Update board default timezone - set_config('board_timezone', _convert_phpbb30_timezone($config['board_timezone'], $config['board_dst'])); + set_config('board_timezone', $update_helpers->convert_phpbb30_timezone($config['board_timezone'], $config['board_dst'])); // After we have calculated the timezones we can delete user_dst column from user table. $db_tools->sql_column_remove(USERS_TABLE, 'user_dst'); -- cgit v1.2.1