You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
118 lines
4.4 KiB
118 lines
4.4 KiB
/**
|
|
* Merge ticket thread tables into one
|
|
*
|
|
* Replace the ticket_{message,response,note} tables with a single
|
|
* ticket_thread table that will contain data for all three current message
|
|
* types. This simplifies much of the ticket thread code and paves the way
|
|
* for other types of messages in the future.
|
|
*
|
|
* This patch automagically moves the data from the three federated tables
|
|
* into the one combined table.
|
|
*/
|
|
DROP TABLE IF EXISTS `%TABLE_PREFIX%ticket_thread`;
|
|
CREATE TABLE `%TABLE_PREFIX%ticket_thread` (
|
|
`id` int(11) unsigned NOT NULL auto_increment,
|
|
`pid` int(11) unsigned NOT NULL default '0',
|
|
`ticket_id` int(11) unsigned NOT NULL default '0',
|
|
`staff_id` int(11) unsigned NOT NULL default '0',
|
|
`thread_type` enum('M','R','N') NOT NULL,
|
|
`poster` varchar(128) NOT NULL default '',
|
|
`source` varchar(32) NOT NULL default '',
|
|
`title` varchar(255),
|
|
`body` text NOT NULL,
|
|
`ip_address` varchar(64) NOT NULL default '',
|
|
`created` datetime NOT NULL,
|
|
`updated` datetime NOT NULL,
|
|
`old_pk` int(11) unsigned NOT NULL,
|
|
`old_pid` int(11) unsigned,
|
|
PRIMARY KEY (`id`),
|
|
KEY `ticket_id` (`ticket_id`),
|
|
KEY `staff_id` (`staff_id`),
|
|
KEY `old_pk` (`old_pk`),
|
|
KEY `created` (`created`)
|
|
) DEFAULT CHARSET=utf8;
|
|
|
|
DROP TABLE IF EXISTS `%TABLE_PREFIX%ticket_email_info`;
|
|
CREATE TABLE `%TABLE_PREFIX%ticket_email_info` (
|
|
`message_id` int(11) unsigned NOT NULL,
|
|
`email_mid` varchar(255) NOT NULL,
|
|
`headers` text,
|
|
KEY `message_id` (`email_mid`)
|
|
) DEFAULT CHARSET=utf8;
|
|
|
|
-- Transfer messages
|
|
INSERT INTO `%TABLE_PREFIX%ticket_thread`
|
|
(`ticket_id`, `thread_type`, `body`, `ip_address`,
|
|
`created`, `updated`, `old_pk`)
|
|
SELECT `ticket_id`, 'M', `message`, `ip_address`,
|
|
`created`, COALESCE(`updated`, NOW()), `msg_id`
|
|
FROM `%TABLE_PREFIX%ticket_message`;
|
|
|
|
-- Transfer responses
|
|
INSERT INTO `%TABLE_PREFIX%ticket_thread`
|
|
(`ticket_id`, `staff_id`, `thread_type`, `poster`, `body`, `ip_address`,
|
|
`created`, `updated`, `old_pk`, `old_pid`)
|
|
SELECT `ticket_id`, `staff_id`, 'R', `staff_name`, `response`, `ip_address`,
|
|
`created`, COALESCE(`updated`, NOW()), `response_id`, `msg_id`
|
|
FROM `%TABLE_PREFIX%ticket_response`;
|
|
|
|
-- Connect responses to (new) messages
|
|
CREATE TABLE `%TABLE_PREFIX%T_resp_links`
|
|
SELECT `id`, `old_pk`
|
|
FROM `%TABLE_PREFIX%ticket_thread`
|
|
WHERE `thread_type` = 'M';
|
|
|
|
-- Add an index to speed up the linking process
|
|
ALTER TABLE `%TABLE_PREFIX%T_resp_links` ADD KEY `old_pk` (`old_pk`, `id`);
|
|
|
|
UPDATE `%TABLE_PREFIX%ticket_thread`
|
|
SET `pid` = ( SELECT T2.`id` FROM `%TABLE_PREFIX%T_resp_links` T2
|
|
WHERE `old_pid` = T2.`old_pk` )
|
|
WHERE `thread_type` = 'R'
|
|
AND `old_pid` IS NOT NULL;
|
|
|
|
DROP TABLE `%TABLE_PREFIX%T_resp_links`;
|
|
|
|
-- Transfer notes
|
|
INSERT INTO `%TABLE_PREFIX%ticket_thread`
|
|
(`ticket_id`, `staff_id`, `thread_type`, `body`, `title`,
|
|
`source`, `poster`, `created`, `updated`, `old_pk`)
|
|
SELECT `ticket_id`, N.staff_id, 'N', `note`, `title`,
|
|
`source`, CONCAT_WS(' ', S.`firstname`, S.`lastname`),
|
|
N.created, NOW(), `note_id`
|
|
FROM `%TABLE_PREFIX%ticket_note` N
|
|
LEFT JOIN `%TABLE_PREFIX%staff` S ON(S.staff_id=N.staff_id);
|
|
|
|
-- Transfer email information from messages
|
|
INSERT INTO `%TABLE_PREFIX%ticket_email_info`
|
|
(`message_id`, `email_mid`, `headers`)
|
|
SELECT ( SELECT T2.`id` FROM `%TABLE_PREFIX%ticket_thread` T2
|
|
WHERE `msg_id` = T2.`old_pk`
|
|
AND `thread_type` = 'M' ),
|
|
`messageId`, `headers`
|
|
FROM `%TABLE_PREFIX%ticket_message`
|
|
WHERE `messageId` IS NOT NULL AND `messageId` <>'';
|
|
|
|
-- Change collation to utf8_general_ci - to avoid Illegal mix of collations error
|
|
ALTER TABLE `%TABLE_PREFIX%ticket_attachment`
|
|
CHANGE `ref_type` `ref_type` ENUM('M','R','N') CHARACTER
|
|
SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'M';
|
|
|
|
-- Update attachment table
|
|
UPDATE `%TABLE_PREFIX%ticket_attachment`
|
|
SET `ref_id` = ( SELECT T2.`id` FROM `%TABLE_PREFIX%ticket_thread` T2
|
|
WHERE `ref_id` = T2.`old_pk`
|
|
AND `ref_type` = T2.`thread_type` );
|
|
|
|
-- Drop temporary columns
|
|
ALTER TABLE `%TABLE_PREFIX%ticket_thread` DROP COLUMN `old_pk`;
|
|
ALTER TABLE `%TABLE_PREFIX%ticket_thread` DROP COLUMN `old_pid`;
|
|
|
|
-- Drop old tables
|
|
DROP TABLE `%TABLE_PREFIX%ticket_message`;
|
|
DROP TABLE `%TABLE_PREFIX%ticket_response`;
|
|
DROP TABLE `%TABLE_PREFIX%ticket_note`;
|
|
|
|
-- Finished with patch
|
|
UPDATE `%TABLE_PREFIX%config`
|
|
SET `schema_signature`='abe9c0cb845be52c10fcd7b3e626a589';
|