/include/upgrader/streams/core/c00511c7-7be60a84.patch.sql
SQL | 311 lines | 253 code | 36 blank | 22 comment | 0 complexity | 95a54dd647c2a64dd0cbd9e2330fb751 MD5 | raw file
- /**
- * @version v1.7
- *
- * @schema c00511c7c1db65c0cfad04b4842afc57
- */
- -- Add a table to contain the attachment file contents
- DROP TABLE IF EXISTS `%TABLE_PREFIX%file`;
- CREATE TABLE `%TABLE_PREFIX%file` (
- `id` int(11) NOT NULL auto_increment,
- `type` varchar(255) NOT NULL default '',
- `size` varchar(25) NOT NULL default '',
- `hash` varchar(125) NOT NULL,
- `name` varchar(255) NOT NULL default '',
- `filedata` longblob NOT NULL,
- `created` datetime NOT NULL,
- PRIMARY KEY (`id`),
- KEY `hash` (`hash`)
- ) DEFAULT CHARSET=utf8;
- -- update ticket attachments ref. table.
- ALTER TABLE `%TABLE_PREFIX%ticket_attachment`
- CHANGE `ref_type` `ref_type` ENUM( 'M', 'R', 'N' ) NOT NULL DEFAULT 'M',
- ADD `file_id` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `ticket_id`,
- ADD INDEX ( `file_id` );
- -- Add Team ID and 'API' as a valid ticket source
- ALTER TABLE `%TABLE_PREFIX%ticket`
- ADD `team_id` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `staff_id`,
- ADD INDEX ( `team_id` ),
- CHANGE `source` `source` ENUM(
- 'Web', 'Email', 'Phone', 'API', 'Other') NOT NULL DEFAULT 'Other';
- -- Add table for ticket history (statistics) tracking
- DROP TABLE IF EXISTS `%TABLE_PREFIX%ticket_event`;
- CREATE TABLE `%TABLE_PREFIX%ticket_event` (
- `ticket_id` int(11) unsigned NOT NULL default '0',
- `staff_id` int(11) unsigned NOT NULL,
- `team_id` int(11) unsigned NOT NULL,
- `dept_id` int(11) unsigned NOT NULL,
- `topic_id` int(11) unsigned NOT NULL,
- `state` enum('created','closed','reopened','assigned','transferred','overdue') NOT NULL,
- `staff` varchar(255) NOT NULL default 'SYSTEM',
- `timestamp` datetime NOT NULL,
- KEY `ticket_state` (`ticket_id`, `state`, `timestamp`),
- KEY `ticket_stats` (`timestamp`, `state`)
- ) DEFAULT CHARSET=utf8;
- ALTER TABLE `%TABLE_PREFIX%config`
- ADD `passwd_reset_period` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `staff_session_timeout`,
- ADD `default_timezone_id` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `default_template_id`,
- ADD `default_sla_id` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `default_dept_id`,
- CHANGE `spoof_default_smtp` `allow_email_spoofing` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
- CHANGE `enable_mail_fetch` `enable_mail_polling` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
- ADD `max_user_file_uploads` TINYINT UNSIGNED NOT NULL AFTER `max_file_size`,
- ADD `max_staff_file_uploads` TINYINT UNSIGNED NOT NULL AFTER `max_user_file_uploads`,
- ADD `assigned_alert_active` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1' AFTER `overdue_alert_dept_members`,
- ADD `assigned_alert_staff` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1' AFTER `assigned_alert_active`,
- ADD `assigned_alert_team_lead` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER `assigned_alert_staff`,
- ADD `assigned_alert_team_members` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER `assigned_alert_team_lead`,
- ADD `transfer_alert_active` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `note_alert_dept_manager` ,
- ADD `transfer_alert_assigned` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `transfer_alert_active` ,
- ADD `transfer_alert_dept_manager` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '1' AFTER `transfer_alert_assigned` ,
- ADD `transfer_alert_dept_members` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `transfer_alert_dept_manager`,
- ADD `send_sys_errors` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1' AFTER `enable_email_piping`,
- ADD `enable_kb` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER `use_email_priority`,
- ADD `enable_premade` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1' AFTER `enable_kb`,
- ADD `show_related_tickets` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1' AFTER `auto_assign_reopened_tickets`,
- ADD `schema_signature` CHAR( 32 ) NOT NULL AFTER `ostversion`;
- -- copy over timezone id - based on offset.
- UPDATE `%TABLE_PREFIX%config` SET default_timezone_id =
- (SELECT id FROM `%TABLE_PREFIX%timezone` WHERE offset = `%TABLE_PREFIX%config`.timezone_offset);
- ALTER TABLE `%TABLE_PREFIX%staff`
- ADD `passwdreset` DATETIME NULL DEFAULT NULL AFTER `lastlogin`;
- DROP TABLE IF EXISTS `%TABLE_PREFIX%sla`;
- CREATE TABLE IF NOT EXISTS `%TABLE_PREFIX%sla` (
- `id` int(11) unsigned NOT NULL auto_increment,
- `isactive` tinyint(1) unsigned NOT NULL default '1',
- `enable_priority_escalation` tinyint(1) unsigned NOT NULL default '1',
- `disable_overdue_alerts` tinyint(1) unsigned NOT NULL default '0',
- `grace_period` int(10) unsigned NOT NULL default '0',
- `name` varchar(64) NOT NULL default '',
- `notes` text,
- `created` datetime NOT NULL,
- `updated` datetime NOT NULL,
- PRIMARY KEY (`id`),
- UNIQUE KEY `name` (`name`)
- ) DEFAULT CHARSET=utf8;
- -- Create a default SLA
- INSERT INTO `%TABLE_PREFIX%sla` (`isactive`, `enable_priority_escalation`,
- `disable_overdue_alerts`, `grace_period`, `name`, `notes`, `created`, `updated`)
- VALUES (1, 1, 0, 48, 'Default SLA', NULL, NOW(), NOW());
- -- Create a TEAM table
- DROP TABLE IF EXISTS `%TABLE_PREFIX%team`;
- CREATE TABLE IF NOT EXISTS `%TABLE_PREFIX%team` (
- `team_id` int(10) unsigned NOT NULL auto_increment,
- `lead_id` int(10) unsigned NOT NULL default '0',
- `isenabled` tinyint(1) unsigned NOT NULL default '1',
- `noalerts` tinyint(1) unsigned NOT NULL default '0',
- `name` varchar(125) NOT NULL default '',
- `notes` text,
- `created` datetime NOT NULL,
- `updated` datetime NOT NULL,
- PRIMARY KEY (`team_id`),
- UNIQUE KEY `name` (`name`),
- KEY `isnabled` (`isenabled`),
- KEY `lead_id` (`lead_id`)
- ) DEFAULT CHARSET=utf8;
- -- Create a default TEAM
- INSERT INTO `%TABLE_PREFIX%team` (`lead_id`, `isenabled`, `noalerts`, `name`, `notes`, `created`, `updated`)
- VALUES (0, 1, 0, 'Level I Support', '', NOW(), NOW());
- DROP TABLE IF EXISTS `%TABLE_PREFIX%team_member`;
- CREATE TABLE IF NOT EXISTS `%TABLE_PREFIX%team_member` (
- `team_id` int(10) unsigned NOT NULL default '0',
- `staff_id` int(10) unsigned NOT NULL,
- `updated` datetime NOT NULL,
- PRIMARY KEY (`team_id`,`staff_id`)
- ) DEFAULT CHARSET=utf8;
- ALTER TABLE `%TABLE_PREFIX%department`
- ADD sla_id INT UNSIGNED NOT NULL DEFAULT '0' AFTER tpl_id;
- ALTER TABLE `%TABLE_PREFIX%staff`
- ADD `notes` TEXT NULL DEFAULT NULL AFTER `signature`,
- ADD `assigned_only` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER `onvacation`,
- ADD `show_assigned_tickets` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER `assigned_only`,
- ADD `timezone_id` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `dept_id`,
- ADD `default_signature_type` ENUM( 'none', 'mine', 'dept' ) NOT NULL DEFAULT 'none' AFTER `auto_refresh_rate`;
- -- Copy over time zone offet to tz_id
- UPDATE `%TABLE_PREFIX%staff` SET timezone_id =
- (SELECT id FROM `%TABLE_PREFIX%timezone` WHERE offset = `%TABLE_PREFIX%staff`.timezone_offset);
- ALTER TABLE `%TABLE_PREFIX%groups`
- CHANGE `can_manage_kb` `can_manage_premade` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
- ADD `can_manage_faq` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER `can_manage_premade`,
- ADD `can_assign_tickets` TINYINT( 1 ) UNSIGNED NOT NULL default '1' AFTER `can_close_tickets`,
- ADD notes TEXT NULL AFTER can_manage_faq;
- -- Add new columns to the templates table
- ALTER TABLE `%TABLE_PREFIX%email_template`
- ADD `isactive` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER `cfg_id`,
- ADD `transfer_alert_subj` VARCHAR( 255 ) NOT NULL AFTER `assigned_alert_body`,
- ADD `transfer_alert_body` TEXT NOT NULL AFTER `transfer_alert_subj`;
- -- Insert default text for the new messaage tpl + make templates active (all records are updated).
- UPDATE `%TABLE_PREFIX%email_template` SET updated=NOW() ,isactive=1, transfer_alert_subj='Ticket Transfer #%ticket - %dept',transfer_alert_body='%staff,\r\n\r\nTicket #%ticket has been transferred to %dept department.\r\n\r\n----------------------\r\n\r\n%note\r\n\r\n-------------------\r\n\r\nTo view/respond to the ticket, please login to the support ticket system.\r\n\r\n%url/scp/ticket.php?id=%id\r\n\r\n- Your friendly Customer Support System - powered by osTicket.';
- ALTER TABLE `%TABLE_PREFIX%help_topic`
- ADD ispublic TINYINT(1) UNSIGNED NOT NULL DEFAULT '1' AFTER isactive,
- ADD notes TEXT NULL DEFAULT NULL AFTER topic,
- ADD staff_id INT UNSIGNED NOT NULL DEFAULT '0' AFTER dept_id,
- ADD team_id INT UNSIGNED NOT NULL DEFAULT '0' AFTER staff_id,
- ADD sla_id INT UNSIGNED NOT NULL DEFAULT '0' AFTER team_id,
- ADD INDEX ( staff_id , team_id ),
- ADD INDEX ( sla_id );
- ALTER TABLE `%TABLE_PREFIX%email`
- ADD mail_archivefolder VARCHAR(255) NULL AFTER mail_fetchmax,
- ADD notes TEXT NULL DEFAULT NULL AFTER smtp_auth,
- ADD smtp_spoofing TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER smtp_auth;
- ALTER TABLE `%TABLE_PREFIX%api_key`
- ADD notes TEXT NULL DEFAULT NULL AFTER apikey,
- ADD UNIQUE (apikey);
- ALTER TABLE `%TABLE_PREFIX%ticket`
- ADD sla_id INT UNSIGNED NOT NULL DEFAULT '0' AFTER dept_id,
- ADD INDEX ( sla_id );
- DROP TABLE IF EXISTS `%TABLE_PREFIX%email_filter`;
- CREATE TABLE `%TABLE_PREFIX%email_filter` (
- `id` int(11) unsigned NOT NULL auto_increment,
- `execorder` int(10) unsigned NOT NULL default '99',
- `isactive` tinyint(1) unsigned NOT NULL default '1',
- `match_all_rules` tinyint(1) unsigned NOT NULL default '0',
- `stop_onmatch` tinyint(1) unsigned NOT NULL default '0',
- `reject_email` tinyint(1) unsigned NOT NULL default '0',
- `use_replyto_email` tinyint(1) unsigned NOT NULL default '0',
- `disable_autoresponder` tinyint(1) unsigned NOT NULL default '0',
- `email_id` int(10) unsigned NOT NULL default '0',
- `priority_id` int(10) unsigned NOT NULL default '0',
- `dept_id` int(10) unsigned NOT NULL default '0',
- `staff_id` int(10) unsigned NOT NULL default '0',
- `team_id` int(10) unsigned NOT NULL default '0',
- `sla_id` int(10) unsigned NOT NULL default '0',
- `name` varchar(32) NOT NULL default '',
- `notes` text,
- `created` datetime NOT NULL,
- `updated` datetime NOT NULL,
- PRIMARY KEY (`id`),
- KEY `email_id` (`email_id`)
- ) DEFAULT CHARSET=utf8;
- -- Copy banlist to a new email filter
- INSERT INTO `%TABLE_PREFIX%email_filter` (`execorder`, `isactive`,
- `match_all_rules`, `stop_onmatch`, `reject_email`, `use_replyto_email`,
- `disable_autoresponder`, `email_id`, `priority_id`, `dept_id`, `staff_id`,
- `team_id`, `sla_id`, `name`, `notes`) VALUES
- (99, 1, 0, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 'SYSTEM BAN LIST',
- 'Internal list for email banning. Do not remove');
- DROP TABLE IF EXISTS `%TABLE_PREFIX%email_filter_rule`;
- CREATE TABLE `%TABLE_PREFIX%email_filter_rule` (
- `id` int(11) unsigned NOT NULL auto_increment,
- `filter_id` int(10) unsigned NOT NULL default '0',
- `what` enum('name','email','subject','body','header') NOT NULL,
- `how` enum('equal','not_equal','contains','dn_contain') NOT NULL,
- `val` varchar(255) NOT NULL,
- `isactive` tinyint( 1 ) UNSIGNED NOT NULL DEFAULT '1',
- `notes` tinytext NOT NULL,
- `created` datetime NOT NULL,
- `updated` datetime NOT NULL,
- PRIMARY KEY (`id`),
- KEY `filter_id` (`filter_id`),
- UNIQUE `filter` (`filter_id`, `what`, `how`, `val`)
- ) DEFAULT CHARSET=utf8;
- -- SYSTEM BAN LIST was the first filter created, with ID of '1'
- INSERT INTO `%TABLE_PREFIX%email_filter_rule` (`filter_id`, `what`, `how`, `val`)
- SELECT LAST_INSERT_ID(), 'email', 'equal', email FROM `%TABLE_PREFIX%email_banlist`;
- -- Create table session
- DROP TABLE IF EXISTS `%TABLE_PREFIX%session`;
- CREATE TABLE `%TABLE_PREFIX%session` (
- `session_id` varchar(32) collate utf8_unicode_ci NOT NULL default '',
- `session_data` longtext collate utf8_unicode_ci,
- `session_expire` datetime default NULL,
- `session_updated` datetime default NULL,
- `user_id` int(10) unsigned NOT NULL default '0',
- `user_ip` varchar(32) collate utf8_unicode_ci NOT NULL,
- `user_agent` varchar(255) collate utf8_unicode_ci NOT NULL,
- PRIMARY KEY (`session_id`),
- KEY `updated` (`session_updated`),
- KEY `user_id` (`user_id`)
- ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
- -- Create tables for FAQ + attachments.
- DROP TABLE IF EXISTS `%TABLE_PREFIX%faq`;
- CREATE TABLE IF NOT EXISTS `%TABLE_PREFIX%faq` (
- `faq_id` int(10) unsigned NOT NULL auto_increment,
- `category_id` int(10) unsigned NOT NULL default '0',
- `ispublished` tinyint(1) unsigned NOT NULL default '0',
- `question` varchar(255) NOT NULL,
- `answer` text NOT NULL,
- `keywords` tinytext,
- `notes` text,
- `created` date NOT NULL,
- `updated` date NOT NULL,
- PRIMARY KEY (`faq_id`),
- UNIQUE KEY `question` (`question`),
- KEY `category_id` (`category_id`),
- KEY `ispublished` (`ispublished`)
- ) DEFAULT CHARSET=utf8;
- DROP TABLE IF EXISTS `%TABLE_PREFIX%faq_attachment`;
- CREATE TABLE IF NOT EXISTS `%TABLE_PREFIX%faq_attachment` (
- `faq_id` int(10) unsigned NOT NULL,
- `file_id` int(10) unsigned NOT NULL,
- PRIMARY KEY (`faq_id`,`file_id`)
- ) DEFAULT CHARSET=utf8;
- -- Add support for attachments to canned responses
- DROP TABLE IF EXISTS `%TABLE_PREFIX%canned_attachment`;
- CREATE TABLE IF NOT EXISTS `%TABLE_PREFIX%canned_attachment` (
- `canned_id` int(10) unsigned NOT NULL,
- `file_id` int(10) unsigned NOT NULL,
- PRIMARY KEY (`canned_id`,`file_id`)
- ) DEFAULT CHARSET=utf8;
- -- Rename kb_premade to canned_response
- ALTER TABLE `%TABLE_PREFIX%kb_premade`
- CHANGE `premade_id` `canned_id` int(10) unsigned NOT NULL auto_increment,
- CHANGE `title` `title` VARCHAR( 255 ) NOT NULL DEFAULT '',
- CHANGE `answer` `response` TEXT NOT NULL,
- ADD `notes` TEXT NOT NULL AFTER `response`,
- DROP INDEX `title`;
- DROP TABLE IF EXISTS `%TABLE_PREFIX%canned_response`;
- ALTER TABLE `%TABLE_PREFIX%kb_premade` RENAME TO `%TABLE_PREFIX%canned_response`;
- DROP TABLE IF EXISTS `%TABLE_PREFIX%faq_category`;
- CREATE TABLE IF NOT EXISTS `%TABLE_PREFIX%faq_category` (
- `category_id` int(10) unsigned NOT NULL auto_increment,
- `ispublic` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0',
- `name` varchar(125) default NULL,
- `description` TEXT NOT NULL,
- `notes` tinytext NOT NULL,
- `created` date NOT NULL,
- `updated` date NOT NULL,
- PRIMARY KEY (`category_id`),
- KEY (`ispublic`)
- ) DEFAULT CHARSET=utf8;
- DROP TABLE IF EXISTS `%TABLE_PREFIX%faq_topic`;
- CREATE TABLE IF NOT EXISTS `%TABLE_PREFIX%faq_topic` (
- `faq_id` int(10) unsigned NOT NULL,
- `topic_id` int(10) unsigned NOT NULL,
- PRIMARY KEY (`faq_id`,`topic_id`)
- ) DEFAULT CHARSET=utf8;
- UPDATE `%TABLE_PREFIX%config`
- SET `schema_signature`='7be60a8432e44989e782d5914ef784d2';