aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorDavid Colón <david@davidiq.com>2017-02-13 14:03:54 -0500
committerDavid Colón <david@davidiq.com>2017-03-01 23:09:27 -0500
commite5eb7025141238a3486a0395c42c55411dafb259 (patch)
tree7821b5b89336ab4b6886b627b612406d41b3f47f
parent9b4d455726babf0385c84817e66477134d8afd3d (diff)
downloadforums-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
-rw-r--r--phpBB/phpbb/db/migration/data/v32x/user_notifications_table_indexes.php50
-rw-r--r--phpBB/phpbb/db/migration/data/v32x/user_notifications_table_remove_duplicates.php55
-rw-r--r--phpBB/phpbb/db/migration/data/v32x/user_notifications_table_unique_index.php46
-rw-r--r--phpBB/phpbb/db/tools/tools.php14
4 files changed, 157 insertions, 8 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',
+ ),
+ ),
+ );
+ }
+}
diff --git a/phpBB/phpbb/db/tools/tools.php b/phpBB/phpbb/db/tools/tools.php
index 9273d69cd6..4218addb8e 100644
--- a/phpBB/phpbb/db/tools/tools.php
+++ b/phpBB/phpbb/db/tools/tools.php
@@ -1487,17 +1487,17 @@ class tools implements tools_interface
{
$statements = array();
- $this->check_index_name_length($table_name, $index_name);
-
switch ($this->sql_layer)
{
case 'oracle':
case 'sqlite3':
+ $this->check_index_name_length($table_name, $table_name . '_' . $index_name);
$statements[] = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
break;
case 'mysql_40':
case 'mysql_41':
+ $this->check_index_name_length($table_name, $index_name);
$statements[] = 'ALTER TABLE ' . $table_name . ' ADD UNIQUE INDEX ' . $index_name . '(' . implode(', ', $column) . ')';
break;
}
@@ -1512,8 +1512,6 @@ class tools implements tools_interface
{
$statements = array();
- $this->check_index_name_length($table_name, $index_name);
-
// remove index length unless MySQL4
if ('mysql_40' != $this->sql_layer)
{
@@ -1524,6 +1522,7 @@ class tools implements tools_interface
{
case 'oracle':
case 'sqlite3':
+ $this->check_index_name_length($table_name, $table_name . '_' . $index_name);
$statements[] = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
break;
@@ -1539,6 +1538,7 @@ class tools implements tools_interface
}
// no break
case 'mysql_41':
+ $this->check_index_name_length($table_name, $index_name);
$statements[] = 'ALTER TABLE ' . $table_name . ' ADD INDEX ' . $index_name . ' (' . implode(', ', $column) . ')';
break;
}
@@ -1554,11 +1554,9 @@ class tools implements tools_interface
*/
protected function check_index_name_length($table_name, $index_name)
{
- $table_prefix = substr(CONFIG_TABLE, 0, -6); // strlen(config)
- if (strlen($table_name . $index_name) - strlen($table_prefix) > 24)
+ if (strlen($index_name) > 30)
{
- $max_length = strlen($table_prefix) + 24;
- trigger_error("Index name '{$table_name}_$index_name' on table '$table_name' is too long. The maximum is $max_length characters.", E_USER_ERROR);
+ trigger_error("Index name '$index_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);
}
}
generated by cgit v1.2.1 (git 2.21.0) at 2024-09-25 13:55:17 +0000