diff options
| author | David Colón <david@davidiq.com> | 2017-02-13 14:03:54 -0500 |
|---|---|---|
| committer | David Colón <david@davidiq.com> | 2017-03-01 23:09:27 -0500 |
| commit | e5eb7025141238a3486a0395c42c55411dafb259 (patch) | |
| tree | 7821b5b89336ab4b6886b627b612406d41b3f47f /phpBB/phpbb/db/migration/data | |
| parent | 9b4d455726babf0385c84817e66477134d8afd3d (diff) | |
| download | forums-e5eb7025141238a3486a0395c42c55411dafb259.tar forums-e5eb7025141238a3486a0395c42c55411dafb259.tar.gz forums-e5eb7025141238a3486a0395c42c55411dafb259.tar.bz2 forums-e5eb7025141238a3486a0395c42c55411dafb259.tar.xz forums-e5eb7025141238a3486a0395c42c55411dafb259.zip | |
[ticket/14992] Add indexes to user_notifications table
[ticket/14992] Change how index length is checked
[ticket/14992] Remove duplicates from user_notifications table
[ticket/14992] Add unique index to user_notifications table
[ticket/14992] Shorten unique index name
[ticket/14992] Shorten another index for user notifications
Diffstat (limited to 'phpBB/phpbb/db/migration/data')
3 files changed, 151 insertions, 0 deletions
diff --git a/phpBB/phpbb/db/migration/data/v32x/user_notifications_table_indexes.php b/phpBB/phpbb/db/migration/data/v32x/user_notifications_table_indexes.php new file mode 100644 index 0000000000..86a3891b46 --- /dev/null +++ b/phpBB/phpbb/db/migration/data/v32x/user_notifications_table_indexes.php @@ -0,0 +1,50 @@ +<?php +/** + * + * This file is part of the phpBB Forum Software package. + * + * @copyright (c) phpBB Limited <https://www.phpbb.com> + * @license GNU General Public License, version 2 (GPL-2.0) + * + * For full copyright and license information, please see + * the docs/CREDITS.txt file. + * + */ + +namespace phpbb\db\migration\data\v32x; + +class user_notifications_table_indexes extends \phpbb\db\migration\migration +{ + static public function depends_on() + { + return array( + '\phpbb\db\migration\data\v32x\cookie_notice_p2', + ); + } + + public function update_schema() + { + return array( + 'add_index' => array( + $this->table_prefix . 'user_notifications' => array( + 'user_id' => array('user_id'), + 'user_id_item_id' => array('user_id', 'item_id'), + 'user_itm_type_id' => array('user_id', 'item_type', 'item_id'), + ), + ), + ); + } + + public function revert_schema() + { + return array( + 'drop_keys' => array( + $this->table_prefix . 'user_notifications' => array( + 'user_id', + 'user_id_item_id', + 'user_itm_type_id', + ), + ), + ); + } +} diff --git a/phpBB/phpbb/db/migration/data/v32x/user_notifications_table_remove_duplicates.php b/phpBB/phpbb/db/migration/data/v32x/user_notifications_table_remove_duplicates.php new file mode 100644 index 0000000000..f5f0305fda --- /dev/null +++ b/phpBB/phpbb/db/migration/data/v32x/user_notifications_table_remove_duplicates.php @@ -0,0 +1,55 @@ +<?php +/** + * + * This file is part of the phpBB Forum Software package. + * + * @copyright (c) phpBB Limited <https://www.phpbb.com> + * @license GNU General Public License, version 2 (GPL-2.0) + * + * For full copyright and license information, please see + * the docs/CREDITS.txt file. + * + */ + +namespace phpbb\db\migration\data\v32x; + +class user_notifications_table_remove_duplicates extends \phpbb\db\migration\migration +{ + static public function depends_on() + { + return array( + '\phpbb\db\migration\data\v32x\user_notifications_table_indexes', + ); + } + + public function update_data() + { + return array( + array('custom', array(array($this, 'remove_duplicates'))), + ); + } + + public function remove_duplicates() + { + $insert_buffer = new \phpbb\db\sql_insert_buffer($this->db, $this->table_prefix . 'user_notifications'); + + $sql = "SELECT item_type, item_id, user_id, method, MAX(notify) AS notify + FROM {$this->table_prefix}user_notifications + GROUP BY item_type, item_id, user_id, method + HAVING COUNT(item_type) > 1"; + + $result = $this->sql_query($sql); + while ($row = $this->db->sql_fetchrow($result)) + { + // Delete the duplicate entries + $this->sql_query("DELETE FROM {$this->table_prefix}user_notifications + WHERE user_id = {$row['user_id']} + AND item_type = '{$row['item_type']}' + AND method = '{$row['method']}'"); + + // And re-insert as a single one + $insert_buffer->insert($row); + } + $this->db->sql_freeresult($result); + } +} diff --git a/phpBB/phpbb/db/migration/data/v32x/user_notifications_table_unique_index.php b/phpBB/phpbb/db/migration/data/v32x/user_notifications_table_unique_index.php new file mode 100644 index 0000000000..925197a02b --- /dev/null +++ b/phpBB/phpbb/db/migration/data/v32x/user_notifications_table_unique_index.php @@ -0,0 +1,46 @@ +<?php +/** + * + * This file is part of the phpBB Forum Software package. + * + * @copyright (c) phpBB Limited <https://www.phpbb.com> + * @license GNU General Public License, version 2 (GPL-2.0) + * + * For full copyright and license information, please see + * the docs/CREDITS.txt file. + * + */ + +namespace phpbb\db\migration\data\v32x; + +class user_notifications_table_unique_index extends \phpbb\db\migration\migration +{ + static public function depends_on() + { + return array( + '\phpbb\db\migration\data\v32x\user_notifications_table_remove_duplicates', + ); + } + + public function update_schema() + { + return array( + 'add_unique_index' => array( + $this->table_prefix . 'user_notifications' => array( + 'itm_usr_mthd' => array('item_type', 'item_id', 'user_id', 'method'), + ), + ), + ); + } + + public function revert_schema() + { + return array( + 'drop_keys' => array( + $this->table_prefix . 'user_notifications' => array( + 'itm_usr_mthd', + ), + ), + ); + } +} |