aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorNils Adermann <naderman@naderman.de>2014-05-02 14:46:25 +0200
committerNils Adermann <naderman@naderman.de>2014-05-02 14:46:25 +0200
commit4f17d0653ab9eaa51c730f2e1f5ad6fbcfc279ba (patch)
treeb56f05d28b7b75ead75a2297c643455e78a04863
parent5e9581550350b6a1f47a0969ed7c5962eb206d90 (diff)
parent46c5ab64d4bafb04fe062cca89b2d872567f53c1 (diff)
downloadforums-4f17d0653ab9eaa51c730f2e1f5ad6fbcfc279ba.tar
forums-4f17d0653ab9eaa51c730f2e1f5ad6fbcfc279ba.tar.gz
forums-4f17d0653ab9eaa51c730f2e1f5ad6fbcfc279ba.tar.bz2
forums-4f17d0653ab9eaa51c730f2e1f5ad6fbcfc279ba.tar.xz
forums-4f17d0653ab9eaa51c730f2e1f5ad6fbcfc279ba.zip
Merge branch 'exreaction/ticket/11881' into develop-ascraeus
* exreaction/ticket/11881: [ticket/11881] Move convert_timezones_test to migrator [ticket/11881] Fix test filename [ticket/11881] Limit to 500 [ticket/11881] Make sure user_timezone isn't converted twice [ticket/11881] Better split the timezone conversion into chunks; add test [ticket/11881] Timezone migration can take a long time
Diffstat
-rw-r--r--phpBB/phpbb/db/migration/data/v310/timezone.php45
-rw-r--r--tests/migrator/convert_timezones_test.php94
-rw-r--r--tests/migrator/fixtures/convert_timezones.xml80
3 files changed, 209 insertions, 10 deletions
diff --git a/phpBB/phpbb/db/migration/data/v310/timezone.php b/phpBB/phpbb/db/migration/data/v310/timezone.php
index c1da2f4998..2efedd4514 100644
--- a/phpBB/phpbb/db/migration/data/v310/timezone.php
+++ b/phpBB/phpbb/db/migration/data/v310/timezone.php
@@ -39,23 +39,48 @@ class timezone extends \phpbb\db\migration\migration
);
}
- public function update_timezones()
+ public function update_timezones($start)
{
- // Update user timezones
- $sql = 'SELECT user_dst, user_timezone
- FROM ' . $this->table_prefix . 'users
- GROUP BY user_timezone, user_dst';
- $result = $this->db->sql_query($sql);
+ $start = (int) $start;
+ $limit = 500;
+ $converted = 0;
+
+ $update_blocks = array();
+ $sql = 'SELECT user_id, user_timezone, user_dst
+ FROM ' . $this->table_prefix . 'users
+ ORDER BY user_id ASC';
+ $result = $this->db->sql_query_limit($sql, $limit, $start);
while ($row = $this->db->sql_fetchrow($result))
{
+ $converted++;
+
+ // In case this is somehow run twice on a row.
+ // Otherwise it would just end up as UTC on the second run
+ if (is_numeric($row['user_timezone']))
+ {
+ $update_blocks[$row['user_timezone'] . ':' . $row['user_dst']][] = (int) $row['user_id'];
+ }
+ }
+ $this->db->sql_freeresult($result);
+
+ // Update blocks of users who share the same timezone/dst
+ foreach ($update_blocks as $timezone => $user_ids)
+ {
+ $timezone = explode(':', $timezone);
+ $converted_timezone = $this->convert_phpbb30_timezone($timezone[0], $timezone[1]);
+
$sql = 'UPDATE ' . $this->table_prefix . "users
- SET user_timezone = '" . $this->db->sql_escape($this->convert_phpbb30_timezone($row['user_timezone'], $row['user_dst'])) . "'
- WHERE user_timezone = '" . $this->db->sql_escape($row['user_timezone']) . "'
- AND user_dst = " . (int) $row['user_dst'];
+ SET user_timezone = '" . $this->db->sql_escape($converted_timezone) . "'
+ WHERE " . $this->db->sql_in_set('user_id', $user_ids);
$this->sql_query($sql);
}
- $this->db->sql_freeresult($result);
+
+ if ($converted == $limit)
+ {
+ // There are still more to convert
+ return $start + $limit;
+ }
// Update board default timezone
$sql = 'UPDATE ' . $this->table_prefix . "config
diff --git a/tests/migrator/convert_timezones_test.php b/tests/migrator/convert_timezones_test.php
new file mode 100644
index 0000000000..a1eed1dbdf
--- /dev/null
+++ b/tests/migrator/convert_timezones_test.php
@@ -0,0 +1,94 @@
+<?php
+/**
+*
+* @package testing
+* @copyright (c) 2013 phpBB Group
+* @license http://opensource.org/licenses/gpl-2.0.php GNU General Public License v2
+*
+*/
+
+class phpbb_migrator_convert_timezones_test extends phpbb_database_test_case
+{
+ protected $notifications, $db, $container, $user, $config, $auth, $cache;
+
+ public function getDataSet()
+ {
+ $this->db = $this->new_dbal();
+ $db_tools = new \phpbb\db\tools($this->db);
+
+ // user_dst doesn't exist anymore, must re-add it to test this
+ $db_tools->sql_column_add('phpbb_users', 'user_dst', array('BOOL', 1));
+
+ return $this->createXMLDataSet(dirname(__FILE__) . '/fixtures/convert_timezones.xml');
+ }
+
+ public function revert_schema()
+ {
+ return array(
+ 'drop_columns' => array(
+ $this->table_prefix . 'users' => array(
+ 'user_dst',
+ ),
+ ),
+ );
+ }
+
+ public function update_schema()
+ {
+ return array(
+ 'add_columns' => array(
+ $this->table_prefix . 'users' => array(
+ 'user_dst' => array('BOOL', 0),
+ ),
+ ),
+ );
+ }
+
+ protected function setUp()
+ {
+ parent::setUp();
+
+ global $phpbb_root_path, $phpEx;
+
+ $this->db = $this->new_dbal();
+
+ $this->migration = new \phpbb\db\migration\data\v310\timezone(
+ new \phpbb\config\config(array()),
+ $this->db,
+ new \phpbb\db\tools($this->db),
+ $phpbb_root_path,
+ $phpEx,
+ 'phpbb_'
+ );
+ }
+
+ protected $expected_results = array(
+ //user_id => user_timezone
+ 1 => 'Etc/GMT+12',
+ 2 => 'Etc/GMT+11',
+ 3 => 'Etc/GMT-3',
+ 4 => 'Etc/GMT-4',
+ 5 => 'America/St_Johns',
+ 6 => 'Australia/Eucla',
+ );
+
+ public function test_convert()
+ {
+ $this->migration->update_timezones(0);
+
+ $sql = 'SELECT user_id, user_timezone
+ FROM phpbb_users
+ ORDER BY user_id ASC';
+ $result = $this->db->sql_query($sql);
+ while ($row = $this->db->sql_fetchrow($result))
+ {
+ $this->assertEquals($this->expected_results[$row['user_id']], $row['user_timezone']);
+ }
+ $this->db->sql_freeresult($result);
+
+ $db_tools = new \phpbb\db\tools($this->db);
+
+ // Remove the user_dst field again
+ $db_tools->sql_column_remove('phpbb_users', 'user_dst');
+ }
+}
diff --git a/tests/migrator/fixtures/convert_timezones.xml b/tests/migrator/fixtures/convert_timezones.xml
new file mode 100644
index 0000000000..ce941d8b74
--- /dev/null
+++ b/tests/migrator/fixtures/convert_timezones.xml
@@ -0,0 +1,80 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<dataset>
+ <table name="phpbb_users">
+ <column>user_id</column>
+ <column>username</column>
+ <column>username_clean</column>
+ <column>user_permissions</column>
+ <column>user_sig</column>
+ <column>user_occ</column>
+ <column>user_interests</column>
+ <column>user_timezone</column>
+ <column>user_dst</column>
+ <row>
+ <value>1</value>
+ <value>1</value>
+ <value>1</value>
+ <value></value>
+ <value></value>
+ <value></value>
+ <value></value>
+ <value>-12</value>
+ <value>0</value>
+ </row>
+ <row>
+ <value>2</value>
+ <value>2</value>
+ <value>2</value>
+ <value></value>
+ <value></value>
+ <value></value>
+ <value></value>
+ <value>-12</value>
+ <value>1</value>
+ </row>
+ <row>
+ <value>3</value>
+ <value>3</value>
+ <value>3</value>
+ <value></value>
+ <value></value>
+ <value></value>
+ <value></value>
+ <value>3</value>
+ <value>0</value>
+ </row>
+ <row>
+ <value>4</value>
+ <value>4</value>
+ <value>4</value>
+ <value></value>
+ <value></value>
+ <value></value>
+ <value></value>
+ <value>3</value>
+ <value>1</value>
+ </row>
+ <row>
+ <value>5</value>
+ <value>5</value>
+ <value>5</value>
+ <value></value>
+ <value></value>
+ <value></value>
+ <value></value>
+ <value>-3.5</value>
+ <value>0</value>
+ </row>
+ <row>
+ <value>6</value>
+ <value>6</value>
+ <value>6</value>
+ <value></value>
+ <value></value>
+ <value></value>
+ <value></value>
+ <value>8.75</value>
+ <value>0</value>
+ </row>
+ </table>
+</dataset>
generated by cgit v1.2.1 (git 2.21.0) at 2024-11-21 17:55:01 +0000