1/** 2 * @version v1.6 RC5 3 * @signature e342f869c7a537ab3ee937fb6e21cdd4 4 * 5 * Upgrade from 1.6 RC1-4 to 1.6 RC5 6 * 7 */ 8 9ALTER TABLE `%TABLE_PREFIX%config` 10 CHANGE `default_priority` `default_priority_id` TINYINT( 2 ) UNSIGNED NOT NULL DEFAULT '2', 11 CHANGE `default_template` `default_template_id` TINYINT( 4 ) UNSIGNED NOT NULL DEFAULT '1', 12 CHANGE `default_email` `default_email_id` TINYINT( 4 ) UNSIGNED NOT NULL DEFAULT '0', 13 CHANGE `default_dept` `default_dept_id` TINYINT( 3 ) UNSIGNED NOT NULL DEFAULT '0', 14 CHANGE `enable_pop3_fetch` `enable_mail_fetch` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0', 15 CHANGE `api_key` `api_passphrase` VARCHAR( 125 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL; 16 17ALTER TABLE `%TABLE_PREFIX%config` 18 ADD `note_alert_active` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `message_alert_dept_manager`, 19 ADD `note_alert_laststaff` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '1' AFTER `note_alert_active`, 20 ADD `note_alert_assigned` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '1' AFTER `note_alert_laststaff`, 21 ADD `note_alert_dept_manager` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `note_alert_assigned`, 22 ADD `alert_email_id` TINYINT( 4 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `overdue_grace_period`, 23 ADD `default_smtp_id` TINYINT( 4 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `default_template_id`, 24 ADD `spoof_default_smtp` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `default_smtp_id`, 25 ADD `log_level` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '2' AFTER `random_ticket_ids`, 26 ADD `staff_max_logins` TINYINT UNSIGNED NOT NULL DEFAULT '4' AFTER `enable_daylight_saving`, 27 ADD `staff_login_timeout` INT UNSIGNED NOT NULL DEFAULT '2' AFTER `staff_max_logins`, 28 ADD `client_max_logins` TINYINT UNSIGNED NOT NULL DEFAULT '4' AFTER `staff_session_timeout`, 29 ADD `client_login_timeout` INT UNSIGNED NOT NULL DEFAULT '2' AFTER `client_max_logins`, 30 ADD `show_answered_tickets` TINYINT( 1 ) NOT NULL DEFAULT '0' AFTER `show_assigned_tickets`, 31 ADD `hide_staff_name` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `show_answered_tickets`, 32 ADD `log_graceperiod` INT UNSIGNED NOT NULL DEFAULT '12' AFTER `log_level`; 33 34ALTER TABLE `%TABLE_PREFIX%email` 35 ADD `userid` VARCHAR( 125 ) NOT NULL AFTER `name` , 36 ADD `userpass` VARCHAR( 125 ) NOT NULL AFTER `userid`, 37 ADD `mail_active` TINYINT( 1 ) NOT NULL DEFAULT '0' AFTER `userpass` , 38 ADD `mail_host` VARCHAR( 125 ) NOT NULL AFTER `mail_active` , 39 ADD `mail_protocol` ENUM( 'POP', 'IMAP' ) NOT NULL AFTER `mail_host` , 40 ADD `mail_encryption` ENUM( 'NONE', 'SSL' ) NOT NULL AFTER `mail_protocol` , 41 ADD `mail_port` INT( 6 ) NULL AFTER `mail_encryption` , 42 ADD `mail_fetchfreq` TINYINT( 3 ) NOT NULL DEFAULT '5' AFTER `mail_port` , 43 ADD `mail_fetchmax` TINYINT( 4 ) NOT NULL DEFAULT '30' AFTER `mail_fetchfreq` , 44 ADD `mail_delete` TINYINT( 1 ) NOT NULL DEFAULT '0' AFTER `mail_fetchmax` , 45 ADD `mail_errors` TINYINT( 3 ) NOT NULL DEFAULT '0' AFTER `mail_delete` , 46 ADD `mail_lasterror` DATETIME NULL AFTER `mail_errors` , 47 ADD `mail_lastfetch` DATETIME NULL AFTER `mail_lasterror` , 48 ADD `smtp_active` TINYINT( 1 ) NOT NULL AFTER `mail_lastfetch` , 49 ADD `smtp_host` VARCHAR( 125 ) NOT NULL AFTER `smtp_active` , 50 ADD `smtp_port` INT( 6 ) NULL AFTER `smtp_host` , 51 ADD `smtp_auth` TINYINT( 1 ) NOT NULL DEFAULT '1' AFTER `smtp_port` ; 52 53-- Transfer old POP3 settings to "new" email table 54UPDATE `%TABLE_PREFIX%email` as T1 JOIN `%TABLE_PREFIX%email_pop3` as T2 ON(T1.email_id = T2.`email_id`) 55 SET 56 `updated`=NOW(), 57 `mail_protocol`='POP', 58 `mail_encryption`='NONE', 59 `mail_port`=110, 60 `mail_active`=0, 61 `mail_delete`=T2.`delete_msgs`, 62 `mail_host`=T2.`pophost`, 63 `mail_fetchfreq`=T2.`fetchfreq`, 64 `userid`=T2.`popuser`, 65 `userpass`=T2.`poppasswd`; 66 67-- Transfer alert email configuration 68INSERT INTO `%TABLE_PREFIX%email` (`created`, `updated`, `priority_id`, 69 `dept_id`, `name`, `email`) 70 SELECT NOW(), NOW(), 2, COALESCE(`default_dept_id`, 1), 'osTicket Alerts', 71 `alert_email` 72 FROM `%TABLE_PREFIX%config` WHERE `id`=1; 73 74UPDATE `%TABLE_PREFIX%config` SET `alert_email_id` = last_insert_id() 75 WHERE id=1; 76 77ALTER TABLE `%TABLE_PREFIX%department` 78 ADD `autoresp_email_id` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `email_id`, 79 ADD `tpl_id` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `dept_id`, 80 ADD INDEX ( `tpl_id` ), 81 ADD INDEX ( `autoresp_email_id` ) ; 82 83-- Transfer no-reply email configuration 84INSERT INTO `%TABLE_PREFIX%email` (`created`, `updated`, `priority_id`, 85 `dept_id`, `name`, `email`) 86 SELECT NOW(), NOW(), 2, COALESCE(`default_dept_id`, 1), 'No Reply', 87 `noreply_email` 88 FROM `%TABLE_PREFIX%config` WHERE `id`=1; 89 90UPDATE `%TABLE_PREFIX%department` SET `autoresp_email_id` = last_insert_id() 91 WHERE noreply_autoresp=1; 92 93ALTER TABLE `%TABLE_PREFIX%groups` ADD `can_edit_tickets` TINYINT UNSIGNED NOT NULL DEFAULT '0' AFTER `dept_access` ; 94 95UPDATE `%TABLE_PREFIX%groups` SET `can_edit_tickets`=1 WHERE `can_delete_tickets`=1; 96 97ALTER TABLE `%TABLE_PREFIX%ticket` 98 ADD `phone_ext` VARCHAR( 8 ) NULL DEFAULT NULL AFTER `phone`, 99 ADD `topic` VARCHAR(64) NULL DEFAULT NULL AFTER `subject`, 100 ADD `duedate` DATETIME NULL AFTER `isoverdue`, 101 ADD `isanswered` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `isoverdue`, 102 ADD `lastmessage` DATETIME NULL AFTER `closed`, 103 ADD `lastresponse` DATETIME NULL AFTER `lastmessage`, 104 ADD INDEX ( `duedate` ) ; 105 106ALTER TABLE `%TABLE_PREFIX%ticket` 107 CHANGE `source` `source` ENUM( 'Web', 'Email', 'Phone', 'Other' ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'Other' ; 108 109ALTER TABLE `%TABLE_PREFIX%email_template` 110 ADD `note_alert_subj` VARCHAR( 255 ) NOT NULL AFTER `message_alert_body` , 111 ADD `note_alert_body` TEXT NOT NULL AFTER `note_alert_subj`, 112 ADD `notes` TEXT NULL AFTER `name`; 113 114UPDATE `%TABLE_PREFIX%email_template` SET `note_alert_subj` = 'New Internal Note Alert', 115 `note_alert_body` = '%staff,\r\n\r\nInternal note appended to ticket #%ticket\r\n\r\n----------------------\r\nName: %name\r\n\r\n%note\r\n-------------------\r\n\r\nTo view/respond to the ticket, please login to the support ticket system.\r\n\r\nYour friendly,\r\n\r\nCustomer Support System - powered by osTicket.'; 116 117-- Update path and variables on email templates 118UPDATE `%TABLE_PREFIX%email_template` 119 SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, 'view.php', 'ticket.php'), 120 `message_autoresp_body` = REPLACE(`message_autoresp_body`, 'view.php', 'ticket.php'), 121 `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, 'view.php', 'ticket.php'), 122 `ticket_reply_body` = REPLACE( 123 REPLACE(`ticket_reply_body`, 'view.php', 'ticket.php'), 124 '%message', '%response'); 125 126ALTER TABLE `%TABLE_PREFIX%ticket_message` 127 ADD `messageId` VARCHAR( 255 ) NULL AFTER `ticket_id`, 128 ADD INDEX ( `messageId` ) ; 129 130DROP TABLE IF EXISTS `%TABLE_PREFIX%api_key`; 131CREATE TABLE `%TABLE_PREFIX%api_key` ( 132 `id` int(10) unsigned NOT NULL auto_increment, 133 `isactive` tinyint(1) NOT NULL default '1', 134 `ipaddr` varchar(16) NOT NULL, 135 `apikey` varchar(255) NOT NULL, 136 `updated` datetime NOT NULL default '0000-00-00 00:00:00', 137 `created` datetime NOT NULL default '0000-00-00 00:00:00', 138 PRIMARY KEY (`id`), 139 UNIQUE KEY `ipaddr` (`ipaddr`) 140) DEFAULT CHARSET=utf8; 141 142DROP TABLE IF EXISTS `%TABLE_PREFIX%syslog`; 143CREATE TABLE `%TABLE_PREFIX%syslog` ( 144 `log_id` int(11) unsigned NOT NULL auto_increment, 145 `log_type` enum('Debug','Warning','Error') NOT NULL, 146 `title` varchar(255) NOT NULL, 147 `log` text NOT NULL, 148 `logger` varchar(64) NOT NULL, 149 `ip_address` varchar(16) NOT NULL, 150 `created` datetime NOT NULL default '0000-00-00 00:00:00', 151 `updated` datetime NOT NULL default '0000-00-00 00:00:00', 152 PRIMARY KEY (`log_id`), 153 KEY `log_type` (`log_type`) 154) DEFAULT CHARSET=utf8; 155 156UPDATE `%TABLE_PREFIX%config` 157 SET `ostversion`='1.6 RC5'; 158