From e1239b455fb4e8c5fcc80e9890c501e70ff8f1e4 Mon Sep 17 00:00:00 2001 From: Nils Adermann Date: Tue, 10 Sep 2013 14:17:47 +0200 Subject: [ticket/11700] Namespaces and class names should not start with digits PHPBB3-11700 --- .../phpbb/db/migration/data/v310/softdelete_p1.php | 173 +++++++++++++++++++++ 1 file changed, 173 insertions(+) create mode 100644 phpBB/phpbb/db/migration/data/v310/softdelete_p1.php (limited to 'phpBB/phpbb/db/migration/data/v310/softdelete_p1.php') diff --git a/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php b/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php new file mode 100644 index 0000000000..0418d5cc2b --- /dev/null +++ b/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php @@ -0,0 +1,173 @@ +db_tools->sql_column_exists($this->table_prefix . 'posts', 'post_visibility'); + } + + static public function depends_on() + { + return array('\phpbb\db\migration\data\v310\dev'); + } + + public function update_schema() + { + return array( + 'add_columns' => array( + $this->table_prefix . 'forums' => array( + 'forum_posts_approved' => array('UINT', 0), + 'forum_posts_unapproved' => array('UINT', 0), + 'forum_posts_softdeleted' => array('UINT', 0), + 'forum_topics_approved' => array('UINT', 0), + 'forum_topics_unapproved' => array('UINT', 0), + 'forum_topics_softdeleted' => array('UINT', 0), + ), + $this->table_prefix . 'posts' => array( + 'post_visibility' => array('TINT:3', 0), + 'post_delete_time' => array('TIMESTAMP', 0), + 'post_delete_reason' => array('STEXT_UNI', ''), + 'post_delete_user' => array('UINT', 0), + ), + $this->table_prefix . 'topics' => array( + 'topic_visibility' => array('TINT:3', 0), + 'topic_delete_time' => array('TIMESTAMP', 0), + 'topic_delete_reason' => array('STEXT_UNI', ''), + 'topic_delete_user' => array('UINT', 0), + 'topic_posts_approved' => array('UINT', 0), + 'topic_posts_unapproved' => array('UINT', 0), + 'topic_posts_softdeleted' => array('UINT', 0), + ), + ), + 'add_index' => array( + $this->table_prefix . 'posts' => array( + 'post_visibility' => array('post_visibility'), + ), + $this->table_prefix . 'topics' => array( + 'topic_visibility' => array('topic_visibility'), + 'forum_vis_last' => array('forum_id', 'topic_visibility', 'topic_last_post_id'), + ), + ), + ); + } + + public function revert_schema() + { + return array( + 'drop_columns' => array( + $this->table_prefix . 'forums' => array( + 'forum_posts_approved', + 'forum_posts_unapproved', + 'forum_posts_softdeleted', + 'forum_topics_approved', + 'forum_topics_unapproved', + 'forum_topics_softdeleted', + ), + $this->table_prefix . 'posts' => array( + 'post_visibility', + 'post_delete_time', + 'post_delete_reason', + 'post_delete_user', + ), + $this->table_prefix . 'topics' => array( + 'topic_visibility', + 'topic_delete_time', + 'topic_delete_reason', + 'topic_delete_user', + 'topic_posts_approved', + 'topic_posts_unapproved', + 'topic_posts_softdeleted', + ), + ), + 'drop_keys' => array( + $this->table_prefix . 'posts' => array('post_visibility'), + $this->table_prefix . 'topics' => array('topic_visibility', 'forum_vis_last'), + ), + ); + } + + public function update_data() + { + return array( + array('custom', array(array($this, 'update_post_visibility'))), + array('custom', array(array($this, 'update_topic_visibility'))), + array('custom', array(array($this, 'update_topic_forum_counts'))), + + array('permission.add', array('f_softdelete', false)), + array('permission.add', array('m_softdelete', false)), + ); + } + + public function update_post_visibility() + { + $sql = 'UPDATE ' . $this->table_prefix . 'posts + SET post_visibility = post_approved'; + $this->sql_query($sql); + } + + public function update_topic_visibility() + { + $sql = 'UPDATE ' . $this->table_prefix . 'topics + SET topic_visibility = topic_approved'; + $this->sql_query($sql); + } + + public function update_topic_forum_counts() + { + $sql = 'UPDATE ' . $this->table_prefix . 'topics + SET topic_posts_approved = topic_replies + 1, + topic_posts_unapproved = topic_replies_real - topic_replies + WHERE topic_visibility = ' . ITEM_APPROVED; + $this->sql_query($sql); + + $sql = 'UPDATE ' . $this->table_prefix . 'topics + SET topic_posts_approved = 0, + topic_posts_unapproved = (topic_replies_real - topic_replies) + 1 + WHERE topic_visibility = ' . ITEM_UNAPPROVED; + $this->sql_query($sql); + + $sql = 'SELECT forum_id, topic_visibility, COUNT(topic_id) AS sum_topics, SUM(topic_posts_approved) AS sum_posts_approved, SUM(topic_posts_unapproved) AS sum_posts_unapproved + FROM ' . $this->table_prefix . 'topics + GROUP BY forum_id, topic_visibility'; + $result = $this->db->sql_query($sql); + + $update_forums = array(); + while ($row = $this->db->sql_fetchrow($result)) + { + $forum_id = (int) $row['forum_id']; + if (!isset($update_forums[$forum_id])) + { + $update_forums[$forum_id] = array( + 'forum_posts_approved' => 0, + 'forum_posts_unapproved' => 0, + 'forum_topics_approved' => 0, + 'forum_topics_unapproved' => 0, + ); + } + + $update_forums[$forum_id]['forum_posts_approved'] += (int) $row['sum_posts_approved']; + $update_forums[$forum_id]['forum_posts_unapproved'] += (int) $row['sum_posts_unapproved']; + + $update_forums[$forum_id][(($row['topic_visibility'] == ITEM_APPROVED) ? 'forum_topics_approved' : 'forum_topics_unapproved')] += (int) $row['sum_topics']; + } + $this->db->sql_freeresult($result); + + foreach ($update_forums as $forum_id => $forum_data) + { + $sql = 'UPDATE ' . FORUMS_TABLE . ' + SET ' . $this->db->sql_build_array('UPDATE', $forum_data) . ' + WHERE forum_id = ' . $forum_id; + $this->sql_query($sql); + } + } +} -- cgit v1.2.1 From a7af736b9776d724fd2ca77b84b6623eba3edaef Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Sun, 14 Jul 2013 10:35:19 -0400 Subject: [ticket/11691] Stagger the convertion of soft delete updates PHPBB3-11691 --- .../phpbb/db/migration/data/v310/softdelete_p1.php | 26 +++++++++++++++++++--- 1 file changed, 23 insertions(+), 3 deletions(-) (limited to 'phpBB/phpbb/db/migration/data/v310/softdelete_p1.php') diff --git a/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php b/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php index 0418d5cc2b..27bf5b6a57 100644 --- a/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php +++ b/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php @@ -101,7 +101,8 @@ class softdelete_p1 extends \phpbb\db\migration\migration return array( array('custom', array(array($this, 'update_post_visibility'))), array('custom', array(array($this, 'update_topic_visibility'))), - array('custom', array(array($this, 'update_topic_forum_counts'))), + array('custom', array(array($this, 'update_topics_post_counts'))), + array('custom', array(array($this, 'update_forums_topic_and_post_counts'))), array('permission.add', array('f_softdelete', false)), array('permission.add', array('m_softdelete', false)), @@ -122,7 +123,7 @@ class softdelete_p1 extends \phpbb\db\migration\migration $this->sql_query($sql); } - public function update_topic_forum_counts() + public function update_topics_post_counts() { $sql = 'UPDATE ' . $this->table_prefix . 'topics SET topic_posts_approved = topic_replies + 1, @@ -135,15 +136,24 @@ class softdelete_p1 extends \phpbb\db\migration\migration topic_posts_unapproved = (topic_replies_real - topic_replies) + 1 WHERE topic_visibility = ' . ITEM_UNAPPROVED; $this->sql_query($sql); + } + + public function update_forums_topic_and_post_counts($start) + { + $start = (int) $start; + $limit = 2; + $i = 0; $sql = 'SELECT forum_id, topic_visibility, COUNT(topic_id) AS sum_topics, SUM(topic_posts_approved) AS sum_posts_approved, SUM(topic_posts_unapproved) AS sum_posts_unapproved FROM ' . $this->table_prefix . 'topics GROUP BY forum_id, topic_visibility'; - $result = $this->db->sql_query($sql); + $result = $this->db->sql_query_limit($sql, $start, $limit); $update_forums = array(); while ($row = $this->db->sql_fetchrow($result)) { + $i++; + $forum_id = (int) $row['forum_id']; if (!isset($update_forums[$forum_id])) { @@ -169,5 +179,15 @@ class softdelete_p1 extends \phpbb\db\migration\migration WHERE forum_id = ' . $forum_id; $this->sql_query($sql); } + + + if ($i < $limit) + { + // There are no more topics, we are done + return; + } + + // There are still more topics to query, return the next start value + return $start + $limit; } } -- cgit v1.2.1 From a7cf62decf4e4e4d2c73ad7b4f91d40c681784fb Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Fri, 16 Aug 2013 17:07:31 +0200 Subject: [ticket/11691] Fix some more problems with softdelete update * wrong order of arguments on sql_query_limit * avoid "BIGINT UNSIGNED value is out of range" errors * increase limit for the loop PHPBB3-11691 --- phpBB/phpbb/db/migration/data/v310/softdelete_p1.php | 13 +++++++++---- 1 file changed, 9 insertions(+), 4 deletions(-) (limited to 'phpBB/phpbb/db/migration/data/v310/softdelete_p1.php') diff --git a/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php b/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php index 27bf5b6a57..cdf61569b8 100644 --- a/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php +++ b/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php @@ -125,15 +125,20 @@ class softdelete_p1 extends \phpbb\db\migration\migration public function update_topics_post_counts() { + /* + * Using sql_case here, to avoid "BIGINT UNSIGNED value is out of range" errors. + * As we update all topics in 2 queries, one broken topic would stop the conversion + * for all topics and the surpressed error will cause the admin to not even notice it. + */ $sql = 'UPDATE ' . $this->table_prefix . 'topics SET topic_posts_approved = topic_replies + 1, - topic_posts_unapproved = topic_replies_real - topic_replies + topic_posts_unapproved = ' . $this->db->sql_case('topic_replies_real > topic_replies', 'topic_replies_real - topic_replies', '0') . ' WHERE topic_visibility = ' . ITEM_APPROVED; $this->sql_query($sql); $sql = 'UPDATE ' . $this->table_prefix . 'topics SET topic_posts_approved = 0, - topic_posts_unapproved = (topic_replies_real - topic_replies) + 1 + topic_posts_unapproved = (' . $this->db->sql_case('topic_replies_real > topic_replies', 'topic_replies_real - topic_replies', '0') . ') + 1 WHERE topic_visibility = ' . ITEM_UNAPPROVED; $this->sql_query($sql); } @@ -141,13 +146,13 @@ class softdelete_p1 extends \phpbb\db\migration\migration public function update_forums_topic_and_post_counts($start) { $start = (int) $start; - $limit = 2; + $limit = 10; $i = 0; $sql = 'SELECT forum_id, topic_visibility, COUNT(topic_id) AS sum_topics, SUM(topic_posts_approved) AS sum_posts_approved, SUM(topic_posts_unapproved) AS sum_posts_unapproved FROM ' . $this->table_prefix . 'topics GROUP BY forum_id, topic_visibility'; - $result = $this->db->sql_query_limit($sql, $start, $limit); + $result = $this->db->sql_query_limit($sql, $limit, $start); $update_forums = array(); while ($row = $this->db->sql_fetchrow($result)) -- cgit v1.2.1 From e7c43dbb6796d75445b50b0062d6e32cf650b3cd Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Fri, 16 Aug 2013 18:53:05 +0200 Subject: [ticket/11691] Fix some minor comments PHPBB3-11691 --- phpBB/phpbb/db/migration/data/v310/softdelete_p1.php | 7 +++---- 1 file changed, 3 insertions(+), 4 deletions(-) (limited to 'phpBB/phpbb/db/migration/data/v310/softdelete_p1.php') diff --git a/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php b/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php index cdf61569b8..a93171d931 100644 --- a/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php +++ b/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php @@ -147,7 +147,7 @@ class softdelete_p1 extends \phpbb\db\migration\migration { $start = (int) $start; $limit = 10; - $i = 0; + $converted_forums = 0; $sql = 'SELECT forum_id, topic_visibility, COUNT(topic_id) AS sum_topics, SUM(topic_posts_approved) AS sum_posts_approved, SUM(topic_posts_unapproved) AS sum_posts_unapproved FROM ' . $this->table_prefix . 'topics @@ -157,7 +157,7 @@ class softdelete_p1 extends \phpbb\db\migration\migration $update_forums = array(); while ($row = $this->db->sql_fetchrow($result)) { - $i++; + $converted_forums++; $forum_id = (int) $row['forum_id']; if (!isset($update_forums[$forum_id])) @@ -185,8 +185,7 @@ class softdelete_p1 extends \phpbb\db\migration\migration $this->sql_query($sql); } - - if ($i < $limit) + if ($converted_forums < $limit) { // There are no more topics, we are done return; -- cgit v1.2.1 From eace91af203e21fa82d054d42fc2117fa1230763 Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Mon, 19 Aug 2013 10:30:23 +0200 Subject: [ticket/11691] Add order by to the query PHPBB3-11691 --- phpBB/phpbb/db/migration/data/v310/softdelete_p1.php | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'phpBB/phpbb/db/migration/data/v310/softdelete_p1.php') diff --git a/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php b/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php index a93171d931..14bd5f52fb 100644 --- a/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php +++ b/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php @@ -151,7 +151,8 @@ class softdelete_p1 extends \phpbb\db\migration\migration $sql = 'SELECT forum_id, topic_visibility, COUNT(topic_id) AS sum_topics, SUM(topic_posts_approved) AS sum_posts_approved, SUM(topic_posts_unapproved) AS sum_posts_unapproved FROM ' . $this->table_prefix . 'topics - GROUP BY forum_id, topic_visibility'; + GROUP BY forum_id, topic_visibility + ORDER BY forum_id, topic_visibility'; $result = $this->db->sql_query_limit($sql, $limit, $start); $update_forums = array(); -- cgit v1.2.1 From 3b7abb98e1178733054d91d260e8a6329ddaecca Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Mon, 19 Aug 2013 10:31:05 +0200 Subject: [ticket/11691] Fix typo in comment PHPBB3-11691 --- phpBB/phpbb/db/migration/data/v310/softdelete_p1.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'phpBB/phpbb/db/migration/data/v310/softdelete_p1.php') diff --git a/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php b/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php index 14bd5f52fb..f080c78c50 100644 --- a/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php +++ b/phpBB/phpbb/db/migration/data/v310/softdelete_p1.php @@ -126,7 +126,7 @@ class softdelete_p1 extends \phpbb\db\migration\migration public function update_topics_post_counts() { /* - * Using sql_case here, to avoid "BIGINT UNSIGNED value is out of range" errors. + * Using sql_case here to avoid "BIGINT UNSIGNED value is out of range" errors. * As we update all topics in 2 queries, one broken topic would stop the conversion * for all topics and the surpressed error will cause the admin to not even notice it. */ -- cgit v1.2.1