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