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