[TikiWiki-commits] [Git][tikiwiki/tiki][tiki-fix-database-update-tiki-comments] [FIX] tiki_comments: rename no_repeats index and align migrations for legacy/new index names

Sammy Ndabo pushed to branch tiki-fix-database-update-tiki-comments at Tiki Wiki CMS Groupware / Tiki


Commits:
24958270 by Sammy Ndabo at 2026-02-12T14:20:24+02:00
[FIX] tiki_comments: rename no_repeats index and align migrations for legacy/new index names

- - - - -


3 changed files:

- db/tiki.sql
- installer/schema/20251104_update_messageid_from_tiki_comments_tiki.sql
- installer/schema/20251128_reduce_tiki_comments_index_prefix_lenght_tiki.sql


Changes:

=====================================
db/tiki.sql
=====================================
@@ -724,7 +724,7 @@ CREATE TABLE `tiki_comments` (
   `approved` char(1) NOT NULL default 'y',
   `locked` char(1) NOT NULL default 'n',
   PRIMARY KEY (`threadId`),
-  KEY `no_repeats` (`parentId`, `userName`(20), `title`(30), `commentDate`, `message_id`(30), `in_reply_to`(30)),
+  KEY `idx_comments_message_lookup` (`parentId`, `userName`(20), `title`(30), `commentDate`, `message_id`(30), `in_reply_to`(30)),
   KEY `title` (`title`(191)),
   KEY `data` (`data`(191)),
   KEY `hits` (hits),
@@ -3649,7 +3649,7 @@ UPDATE tiki_menu_options SET icon = 'list' WHERE name = 'List PeerTube Media';
 UPDATE tiki_menu_options SET icon = 'upload' WHERE name = 'Upload PeerTube Video';
 UPDATE tiki_menu_options SET icon = 'tasks' WHERE name = 'Queued Tasks';
 
-UPDATE tiki_menus SET use_items_icons = 'y' WHERE menuId = 42; 
+UPDATE tiki_menus SET use_items_icons = 'y' WHERE menuId = 42;
 
 DROP TABLE IF EXISTS `tiki_plugin_security`;
 CREATE TABLE `tiki_plugin_security` (


=====================================
installer/schema/20251104_update_messageid_from_tiki_comments_tiki.sql
=====================================
@@ -3,5 +3,5 @@ ALTER TABLE `tiki_comments`
     DROP INDEX `no_repeats`,
     MODIFY `message_id` TEXT default NULL,
     MODIFY `in_reply_to` varchar(255) default NULL,
-    ADD KEY `no_repeats` (`parentId`,`userName`(40),`title`(43),`commentDate`,`message_id`(40),`in_reply_to`(40)),
+    ADD KEY `idx_comments_message_lookup` (`parentId`,`userName`(40),`title`(43),`commentDate`,`message_id`(40),`in_reply_to`(40)),
     ADD KEY `threaded` (`message_id`(89),`in_reply_to`(88),`parentId`);


=====================================
installer/schema/20251128_reduce_tiki_comments_index_prefix_lenght_tiki.sql
=====================================
@@ -1,4 +1,19 @@
 ALTER TABLE `tiki_comments` DROP INDEX `threaded`;
 ALTER TABLE `tiki_comments` ADD KEY `threaded` (`message_id`(40),`in_reply_to`(40),`parentId`);
-ALTER TABLE tiki_comments DROP INDEX no_repeats;
-ALTER TABLE tiki_comments ADD UNIQUE KEY no_repeats ( parentId, userName(20), title(30), commentDate, message_id(30), in_reply_to(30));
\ No newline at end of file
+SET @comments_lookup_index := (
+    SELECT INDEX_NAME
+    FROM information_schema.STATISTICS
+    WHERE TABLE_SCHEMA = DATABASE()
+      AND TABLE_NAME = 'tiki_comments'
+      AND INDEX_NAME IN ('idx_comments_message_lookup', 'no_repeats')
+    LIMIT 1
+);
+SET @drop_comments_lookup_index_sql := IF(
+    @comments_lookup_index IS NULL,
+    'SELECT 1',
+    CONCAT('ALTER TABLE `tiki_comments` DROP INDEX `', @comments_lookup_index, '`')
+);
+PREPARE drop_comments_lookup_index_stmt FROM @drop_comments_lookup_index_sql;
+EXECUTE drop_comments_lookup_index_stmt;
+DEALLOCATE PREPARE drop_comments_lookup_index_stmt;
+ALTER TABLE tiki_comments ADD KEY idx_comments_message_lookup ( parentId, userName(20), title(30), commentDate, message_id(30), in_reply_to(30));



View it on GitLab: https://gitlab.com/tikiwiki/tiki/-/commit/24958270f6478428858d0942affb333ac85a82c6

-- 
View it on GitLab: https://gitlab.com/tikiwiki/tiki/-/commit/24958270f6478428858d0942affb333ac85a82c6
You're receiving this email because of your account on gitlab.com.

_______________________________________________
TikiWiki-cvs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/tikiwiki-cvs