1/** 2 * @version v1.10.0 3 * @title Add collaborators to tasks 4 * @signature 9143a511719555e8f8f09b49523bd022 5 * 6 * This patch renames the %ticket_lock table to just %lock, which allows for 7 * it to be considered more flexible. Instead, it joins the lock to the 8 * ticket and task objects directly. 9 * 10 * It also redefines the collaborator table to link to a thread rather than 11 * to a ticket, which allows any object in the system with a thread to 12 * theoretically have collaborators. 13 */ 14 15ALTER TABLE `%TABLE_PREFIX%ticket` 16 ADD `lock_id` int(11) unsigned NOT NULL default '0' AFTER `email_id`; 17 18RENAME TABLE `%TABLE_PREFIX%ticket_lock` TO `%TABLE_PREFIX%lock`; 19ALTER TABLE `%TABLE_PREFIX%lock` 20 DROP COLUMN `ticket_id`, 21 ADD `code` varchar(20) AFTER `expire`; 22 23-- Drop all the current locks as they do not point to anything now 24TRUNCATE TABLE `%TABLE_PREFIX%lock`; 25 26CREATE TABLE `%TABLE_PREFIX%thread_collaborator` ( 27 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 28 `isactive` tinyint(1) NOT NULL DEFAULT '1', 29 `thread_id` int(11) unsigned NOT NULL DEFAULT '0', 30 `user_id` int(11) unsigned NOT NULL DEFAULT '0', 31 -- M => (message) clients, N => (note) 3rd-Party, R => (reply) external authority 32 `role` char(1) NOT NULL DEFAULT 'M', 33 `created` datetime NOT NULL, 34 `updated` datetime NOT NULL, 35 PRIMARY KEY (`id`), 36 UNIQUE KEY `collab` (`thread_id`,`user_id`), 37 KEY `user_id` (`user_id`) 38) DEFAULT CHARSET=utf8; 39 40-- Drop zombie collaborators from tickets which were deleted and had 41-- collaborators and the collaborators were not removed 42INSERT INTO `%TABLE_PREFIX%thread_collaborator` 43 (`id`, `isactive`, `thread_id`, `user_id`, `role`, `created`, `updated`) 44 SELECT t1.`id`, t1.`isactive`, t2.`id`, t1.`user_id`, t1.`role`, t2.`created`, t1.`updated` 45 FROM `%TABLE_PREFIX%ticket_collaborator` t1 46 JOIN `%TABLE_PREFIX%thread` t2 ON (t2.`object_id` = t1.`ticket_id` and t2.`object_type` = 'T'); 47 48DROP TABLE `%TABLE_PREFIX%ticket_collaborator`; 49 50ALTER TABLE `%TABLE_PREFIX%task` 51 ADD `lock_id` int(11) unsigned NOT NULL DEFAULT '0' AFTER `team_id`; 52 53-- Finished with patch 54UPDATE `%TABLE_PREFIX%config` 55 SET `value` = '9143a511719555e8f8f09b49523bd022' 56 WHERE `key` = 'schema_signature' AND `namespace` = 'core'; 57