1/** 2 * @signature 2d590ffab4a6a928f08cc97aace1399e 3 * @version v1.10.0 4 * @title Make fields disable-able per help topic 5 * 6 * This patch adds the ability to associate more than one extra form with a 7 * help topic, allows specifying the sort order of each form, including the 8 * main ticket details forms, and also allows disabling any of the fields on 9 * any of the associated forms, including the issue details field. 10 * 11 * This patch migrates the columnar layout of the %filter table into a new 12 * %filter_action table. The cleanup portion of the script will drop the old 13 * columns from the %filter table. 14 */ 15 16DROP TABLE IF EXISTS `%TABLE_PREFIX%filter_action`; 17CREATE TABLE `%TABLE_PREFIX%filter_action` ( 18 `id` int(11) unsigned NOT NULL auto_increment, 19 `filter_id` int(10) unsigned NOT NULL, 20 `sort` int(10) unsigned NOT NULL default 0, 21 `type` varchar(24) NOT NULL, 22 `configuration` text, 23 `updated` datetime NOT NULL, 24 PRIMARY KEY (`id`), 25 KEY `filter_id` (`filter_id`) 26) DEFAULT CHARSET=utf8; 27 28INSERT INTO `%TABLE_PREFIX%filter_action` 29 (`filter_id`, `type`, `configuration`, `updated`) 30 SELECT `id`, 'reject', '', `updated` 31 FROM `%TABLE_PREFIX%filter` 32 WHERE `reject_ticket` != 0; 33 34INSERT INTO `%TABLE_PREFIX%filter_action` 35 (`filter_id`, `type`, `configuration`, `updated`) 36 SELECT `id`, 'replyto', '{"enable":true}', `updated` 37 FROM `%TABLE_PREFIX%filter` 38 WHERE `use_replyto_email` != 0; 39 40INSERT INTO `%TABLE_PREFIX%filter_action` 41 (`filter_id`, `type`, `configuration`, `updated`) 42 SELECT `id`, 'noresp', '{"enable":true}', `updated` 43 FROM `%TABLE_PREFIX%filter` 44 WHERE `disable_autoresponder` != 0; 45 46INSERT INTO `%TABLE_PREFIX%filter_action` 47 (`filter_id`, `type`, `configuration`, `updated`) 48 SELECT `id`, 'canned', CONCAT('{"canned_id":',`canned_response_id`,'}'), `updated` 49 FROM `%TABLE_PREFIX%filter` 50 WHERE `canned_response_id` != 0; 51 52INSERT INTO `%TABLE_PREFIX%filter_action` 53 (`filter_id`, `type`, `configuration`, `updated`) 54 SELECT `id`, 'dept', CONCAT('{"dept_id":',`dept_id`,'}'), `updated` 55 FROM `%TABLE_PREFIX%filter` 56 WHERE `dept_id` != 0; 57 58INSERT INTO `%TABLE_PREFIX%filter_action` 59 (`filter_id`, `type`, `configuration`, `updated`) 60 SELECT `id`, 'pri', CONCAT('{"priority_id":',`priority_id`,'}'), `updated` 61 FROM `%TABLE_PREFIX%filter` 62 WHERE `priority_id` != 0; 63 64INSERT INTO `%TABLE_PREFIX%filter_action` 65 (`filter_id`, `type`, `configuration`, `updated`) 66 SELECT `id`, 'sla', CONCAT('{"sla_id":',`sla_id`,'}'), `updated` 67 FROM `%TABLE_PREFIX%filter` 68 WHERE `sla_id` != 0; 69 70INSERT INTO `%TABLE_PREFIX%filter_action` 71 (`filter_id`, `type`, `configuration`, `updated`) 72 SELECT `id`, 'team', CONCAT('{"team_id":',`team_id`,'}'), `updated` 73 FROM `%TABLE_PREFIX%filter` 74 WHERE `team_id` != 0; 75 76INSERT INTO `%TABLE_PREFIX%filter_action` 77 (`filter_id`, `type`, `configuration`, `updated`) 78 SELECT `id`, 'agent', CONCAT('{"staff_id":',`staff_id`,'}'), `updated` 79 FROM `%TABLE_PREFIX%filter` 80 WHERE `staff_id` != 0; 81 82INSERT INTO `%TABLE_PREFIX%filter_action` 83 (`filter_id`, `type`, `configuration`, `updated`) 84 SELECT `id`, 'topic', CONCAT('{"topic_id":',`topic_id`,'}'), `updated` 85 FROM `%TABLE_PREFIX%filter` 86 WHERE `topic_id` != 0; 87 88INSERT INTO `%TABLE_PREFIX%filter_action` 89 (`filter_id`, `type`, `configuration`, `updated`) 90 SELECT `id`, 'status', CONCAT('{"status_id":',`status_id`,'}'), `updated` 91 FROM `%TABLE_PREFIX%filter` 92 WHERE `status_id` != 0; 93 94ALTER TABLE `%TABLE_PREFIX%form` 95 ADD `pid` int(10) unsigned DEFAULT NULL AFTER `id`, 96 ADD `name` varchar(64) NOT NULL DEFAULT '' AFTER `instructions`; 97 98ALTER TABLE `%TABLE_PREFIX%form_entry` 99 ADD `extra` text AFTER `sort`; 100 101CREATE TABLE `%TABLE_PREFIX%help_topic_form` ( 102 `id` int(11) unsigned NOT NULL auto_increment, 103 `topic_id` int(11) unsigned NOT NULL default 0, 104 `form_id` int(10) unsigned NOT NULL default 0, 105 `sort` int(10) unsigned NOT NULL default 1, 106 `extra` text, 107 PRIMARY KEY (`id`), 108 KEY `topic-form` (`topic_id`, `form_id`) 109) DEFAULT CHARSET=utf8; 110 111-- Handle A4 / A3 / A2 / A1 help topics. For these, consider the forms 112-- associated with each, which should sort above the ticket details form, as 113-- the graphical interface rendered it suchly. Then, consider cascaded 114-- forms, where the parent form was specified on a child. 115insert into `%TABLE_PREFIX%help_topic_form` 116 (`topic_id`, `form_id`, `sort`) 117 select A1.topic_id, case 118 when A3.form_id = 4294967295 then A4.form_id 119 when A2.form_id = 4294967295 then A3.form_id 120 when A1.form_id = 4294967295 then A2.form_id 121 else COALESCE(A4.form_id, A3.form_id, A2.form_id, A1.form_id) end as form_id, 1 as `sort` 122 from `%TABLE_PREFIX%help_topic` A1 123 left join `%TABLE_PREFIX%help_topic` A2 on (A2.topic_id = A1.topic_pid) 124 left join `%TABLE_PREFIX%help_topic` A3 on (A3.topic_id = A2.topic_pid) 125 left join `%TABLE_PREFIX%help_topic` A4 on (A4.topic_id = A3.topic_pid) 126 having `form_id` > 0 127 union 128 select A2.topic_id, id as `form_id`, 2 as `sort` 129 from `%TABLE_PREFIX%form` A1 130 join `%TABLE_PREFIX%help_topic` A2 131 where A1.`type` = 'T'; 132 133-- Finished with patch 134UPDATE `%TABLE_PREFIX%config` 135 SET `value` = '2d590ffab4a6a928f08cc97aace1399e' 136 WHERE `key` = 'schema_signature' AND `namespace` = 'core'; 137