ꦫꦣꦺꦤ꧀ꦄꦤ꧀ꦠꦱꦺꦤ

PATH: home / centosnipponia / public_html / ticketing.nipponia.com / include / upgrader / streams / core

/**
 * @signature 1ee831c854fe9f35115a3e672916bb91
 * @version v1.10.0
 * @title Make editable content translatable and add queues
 *
 * This patch adds support for translatable administratively editable
 * content, such as help topic names, department and group names, site page
 * and faq content, etc.
 *
 * This patch also transitions from the timezone table to the Olson timezone
 * database available in PHP 5.3.
 */

ALTER TABLE `%TABLE_PREFIX%attachment`
    ADD `lang` varchar(16) AFTER `inline`;

ALTER TABLE `%TABLE_PREFIX%staff`
    ADD `lang` varchar(16) DEFAULT NULL AFTER `signature`,
    ADD `timezone` varchar(64) default NULL AFTER `lang`,
    ADD `locale` varchar(16) DEFAULT NULL AFTER `timezone`,
    ADD `extra` text AFTER `default_paper_size`;

ALTER TABLE `%TABLE_PREFIX%user_account`
    ADD `timezone` varchar(64) DEFAULT NULL AFTER `status`,
    ADD `extra` text AFTER `backend`;

CREATE TABLE `%TABLE_PREFIX%translation` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `object_hash` char(16) CHARACTER SET ascii DEFAULT NULL,
  `type` enum('phrase','article','override') DEFAULT NULL,
  `flags` int(10) unsigned NOT NULL DEFAULT '0',
  `revision` int(11) unsigned DEFAULT NULL,
  `agent_id` int(10) unsigned NOT NULL DEFAULT '0',
  `lang` varchar(16) NOT NULL DEFAULT '',
  `text` mediumtext NOT NULL,
  `source_text` text,
  `updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `type` (`type`,`lang`),
  KEY `object_hash` (`object_hash`)
) DEFAULT CHARSET=utf8;

-- Transition the current Timezone configuration to Olsen database

CREATE TABLE `%TABLE_PREFIX%_timezones` (
    `offset` int,
    `dst` tinyint(1) unsigned,
    `south` tinyint(1) unsigned default 0,
    `olson_name` varchar(32)
) DEFAULT CHARSET=utf8;

INSERT INTO `%TABLE_PREFIX%_timezones` (`offset`, `dst`, `olson_name`) VALUES
    -- source borrowed from the jstz project
    (-720, 0, 'Pacific/Majuro'),
    (-660, 0, 'Pacific/Pago_Pago'),
    (-600, 1, 'America/Adak'),
    (-600, 0, 'Pacific/Honolulu'),
    (-570, 0, 'Pacific/Marquesas'),
    (-540, 0, 'Pacific/Gambier'),
    (-540, 1, 'America/Anchorage'),
    (-480, 1, 'America/Los_Angeles'),
    (-480, 0, 'Pacific/Pitcairn'),
    (-420, 0, 'America/Phoenix'),
    (-420, 1, 'America/Denver'),
    (-360, 0, 'America/Guatemala'),
    (-360, 1, 'America/Chicago'),
    (-300, 0, 'America/Bogota'),
    (-300, 1, 'America/New_York'),
    (-270, 0, 'America/Caracas'),
    (-240, 1, 'America/Halifax'),
    (-240, 0, 'America/Santo_Domingo'),
    (-240, 1, 'America/Santiago'),
    (-210, 1, 'America/St_Johns'),
    (-180, 1, 'America/Godthab'),
    (-180, 0, 'America/Argentina/Buenos_Aires'),
    (-180, 1, 'America/Montevideo'),
    (-120, 0, 'America/Noronha'),
    (-120, 1, 'America/Noronha'),
    (-60,  1, 'Atlantic/Azores'),
    (-60,  0, 'Atlantic/Cape_Verde'),
    (0,    0, 'UTC'),
    (0,    1, 'Europe/London'),
    (60,   1, 'Europe/Berlin'),
    (60,   0, 'Africa/Lagos'),
    (120,  1, 'Asia/Beirut'),
    (120,  0, 'Africa/Johannesburg'),
    (180,  0, 'Asia/Baghdad'),
    (180,  1, 'Europe/Moscow'),
    (210,  1, 'Asia/Tehran'),
    (240,  0, 'Asia/Dubai'),
    (240,  1, 'Asia/Baku'),
    (270,  0, 'Asia/Kabul'),
    (300,  1, 'Asia/Yekaterinburg'),
    (300,  0, 'Asia/Karachi'),
    (330,  0, 'Asia/Kolkata'),
    (345,  0, 'Asia/Kathmandu'),
    (360,  0, 'Asia/Dhaka'),
    (360,  1, 'Asia/Omsk'),
    (390,  0, 'Asia/Rangoon'),
    (420,  1, 'Asia/Krasnoyarsk'),
    (420,  0, 'Asia/Jakarta'),
    (480,  0, 'Asia/Shanghai'),
    (480,  1, 'Asia/Irkutsk'),
    (525,  0, 'Australia/Eucla'),
    (525,  1, 'Australia/Eucla'),
    (540,  1, 'Asia/Yakutsk'),
    (540,  0, 'Asia/Tokyo'),
    (570,  0, 'Australia/Darwin'),
    (570,  1, 'Australia/Adelaide'),
    (600,  0, 'Australia/Brisbane'),
    (600,  1, 'Asia/Vladivostok'),
    (630,  1, 'Australia/Lord_Howe'),
    (660,  1, 'Asia/Kamchatka'),
    (660,  0, 'Pacific/Noumea'),
    (690,  0, 'Pacific/Norfolk'),
    (720,  1, 'Pacific/Auckland'),
    (720,  0, 'Pacific/Tarawa'),
    (765,  1, 'Pacific/Chatham'),
    (780,  0, 'Pacific/Tongatapu'),
    (780,  1, 'Pacific/Apia'),
    (840,  0, 'Pacific/Kiritimati');

-- XXX:
-- These zone have opposite DST interpretations and also have norther
-- hemisphere counterparts
INSERT INTO `%TABLE_PREFIX%_timezones` (`offset`, `dst`, `south`, `olson_name`) VALUES
    (-360, 1, 1, 'Pacific/Easter'),
    (60,   1, 1, 'Africa/Windhoek'),
    (600,  1, 1, 'Australia/Sydney');

UPDATE `%TABLE_PREFIX%staff` A1
    JOIN `%TABLE_PREFIX%timezone` A2 ON (A1.`timezone_id` = A2.`id`)
    JOIN `%TABLE_PREFIX%_timezones` A3 ON (A2.`offset` * 60 = A3.`offset`
        AND A1.`daylight_saving` = A3.`dst`
        AND A3.`south` = 0)
    SET A1.`timezone` = A3.`olson_name`;

UPDATE `%TABLE_PREFIX%user_account` A1
    JOIN `%TABLE_PREFIX%timezone` A2 ON (A1.`timezone_id` = A2.`id`)
    JOIN `%TABLE_PREFIX%_timezones` A3 ON (A2.`offset` * 60 = A3.`offset`
        AND A1.`dst` = A3.`dst`
        AND A3.`south` = 0)
    SET A1.`timezone` = A3.`olson_name`;

-- Update system default timezone
SET @default_timezone_id = (
    SELECT `value` FROM `%TABLE_PREFIX%config` A1
    WHERE A1.`key` = 'default_timezone_id'
      AND A1.`namespace` = 'core'
);
SET @enable_daylight_saving = (
    SELECT `value` FROM `%TABLE_PREFIX%config` A1
    WHERE A1.`key` = 'enable_daylight_saving'
      AND A1.`namespace` = 'core'
);

UPDATE `%TABLE_PREFIX%config` A1
    JOIN `%TABLE_PREFIX%timezone` A2 ON (@default_timezone_id = A2.`id`)
    JOIN `%TABLE_PREFIX%_timezones` A3 ON (A2.`offset` * 60 = A3.`offset`
        AND @enable_daylight_saving = A3.`dst`
        AND A3.`south` = 0)
    SET A1.`value` = A3.`olson_name`
    WHERE A1.`key` = 'default_timezone_id'
      AND A1.`namespace` = 'core';

UPDATE `%TABLE_PREFIX%config` A1
    SET A1.`key` = 'default_timezone'
    WHERE A1.`key` = 'default_timezone_id'
      AND A1.`namespace` = 'core';

DROP TABLE %TABLE_PREFIX%_timezones;

ALTER TABLE `%TABLE_PREFIX%ticket`
    ADD `est_duedate` datetime default NULL AFTER `duedate`,
    ADD `lastupdate` datetime default NULL AFTER `lastresponse`;

UPDATE `%TABLE_PREFIX%ticket` A1
    LEFT JOIN `%TABLE_PREFIX%sla` A2 ON (A1.sla_id = A2.id)
    SET A1.`est_duedate` =
        COALESCE(A1.`duedate`, A1.`created` + INTERVAL A2.`grace_period` HOUR),
      A1.`lastupdate` =
        CAST(GREATEST(IFNULL(A1.lastmessage, 0), IFNULL(A1.closed, 0), IFNULL(A1.reopened, 0), A1.created) as DATETIME);

CREATE TABLE `%TABLE_PREFIX%queue` (
  `id` int(11) unsigned not null auto_increment,
  `parent_id` int(11) unsigned not null default 0,
  `flags` int(11) unsigned not null default 0,
  `staff_id` int(11) unsigned not null default 0,
  `sort` int(11) unsigned not null default 0,
  `title` varchar(60),
  `config` text,
  `created` datetime not null,
  `updated` datetime not null,
  primary key (`id`)
) DEFAULT CHARSET=utf8;

-- Add flags field to form field
ALTER TABLE  `%TABLE_PREFIX%form_field`
    ADD  `flags` INT UNSIGNED NOT NULL DEFAULT  '1' AFTER  `form_id`;

-- Flag field stored in the system elsewhere as nonstorable locally.
UPDATE `%TABLE_PREFIX%form_field` A1 JOIN `%TABLE_PREFIX%form` A2 ON(A2.id=A1.form_id)
    SET A1.`flags` = 3
    WHERE A2.`type` = 'U' AND A1.`name` IN('name','email');

UPDATE `%TABLE_PREFIX%form_field` A1 JOIN `%TABLE_PREFIX%form` A2 ON(A2.id=A1.form_id)
    SET A1.`flags`=3
    WHERE A2.`type`='O' AND A1.`name` IN('name');

-- Thread entry field is stored externally
UPDATE `%TABLE_PREFIX%form_field` A1 JOIN `%TABLE_PREFIX%form` A2 ON(A2.id=A1.form_id)
    SET A1.`flags`=3
    WHERE A2.`type`='T' AND A1.`name` IN ('message');

-- Coalesce to zero here in case the config option has never been saved
set @client_edit = coalesce(
    (select value from `%TABLE_PREFIX%config` where `key` =
    'allow_client_updates'), 0);

-- Transfer previous visibility and requirement settings to new flag field
UPDATE `%TABLE_PREFIX%form_field` SET `flags` = `flags` |
     CASE WHEN `private` = 0 and @client_edit = 1 THEN CONV(3300, 16, 10)
          WHEN `private` = 0 and @client_edit = 0 THEN CONV(3100, 16, 10)
          WHEN `private` = 1 THEN CONV(3000, 16, 10)
          WHEN `private` = 2 and @client_edit = 1 THEN CONV(300, 16, 10)
          WHEN `private` = 2 and @client_edit = 0 THEN CONV(100, 16, 10) END
   | CASE WHEN `required` = 0 THEN 0
          WHEN `required` = 1 THEN CONV(4400, 16, 10)
          WHEN `required` = 2 THEN CONV(400, 16, 10)
          WHEN `required` = 3 THEN CONV(4000, 16, 10) END
   | IF(`edit_mask` & 1, CONV(20, 16, 10), 0)
   | IF(`edit_mask` & 2, CONV(40000, 16, 10), 0)
   | IF(`edit_mask` & 4, CONV(10000, 16, 10), 0)
   | IF(`edit_mask` & 8, CONV(20000, 16, 10), 0)
   | IF(`edit_mask` & 16, CONV(10, 16, 10), 0)
   | IF(`edit_mask` & 32, CONV(40, 16, 10), 0);

-- Detect inline images not recorded as inline
CREATE TABLE `%TABLE_PREFIX%_unknown_inlines` AS
  SELECT A2.`attach_id`
  FROM `%TABLE_PREFIX%file` A1
  JOIN `%TABLE_PREFIX%ticket_attachment` A2 ON (A1.id = A2.file_id)
  JOIN `%TABLE_PREFIX%ticket_thread` A3 ON (A3.ticket_id = A2.ticket_id)
  WHERE A1.`type` LIKE 'image/%' AND A2.inline = 0
    AND A3.body LIKE CONCAT('%"cid:', A1.key, '"%');

UPDATE `%TABLE_PREFIX%ticket_attachment` A1
  JOIN %TABLE_PREFIX%_unknown_inlines A2 ON (A1.attach_id = A2.attach_id)
  SET A1.inline = 1;

DROP TABLE `%TABLE_PREFIX%_unknown_inlines`;

-- Finished with patch
UPDATE `%TABLE_PREFIX%config`
    SET `value` = '1ee831c854fe9f35115a3e672916bb91'
    WHERE `key` = 'schema_signature' AND `namespace` = 'core';

[+] ..
[-] 16fcef4a-d51f303a.patch.sql [edit]
[-] e2b4e5cb-f0d2e14d.cleanup.sql [edit]
[-] 61c9d5d7-6de40a4d.patch.sql [edit]
[-] 15af7cd3-98ae1ed2.patch.sql [edit]
[-] ed60ba20-934954de.patch.sql [edit]
[-] 32de1766-852ca89e.patch.sql [edit]
[-] 5cd0a25a-2d590ffa.task.php [edit]
[-] e2b4e5cb-f0d2e14d.task.php [edit]
[-] 740428f9-8aeda901.patch.sql [edit]
[-] 9ef33a06-8f99b8bf.patch.sql [edit]
[-] 8f99b8bf-03ff59bf.patch.sql [edit]
[-] 852ca89e-740428f9.patch.sql [edit]
[-] 9f3b454c-c0fd16f4.patch.sql [edit]
[-] 36f6b328-5cd0a25a.task.php [edit]
[-] e7038ce9-ddbe2e76.patch.sql [edit]
[-] c0fd16f4-d959a00e.patch.sql [edit]
[-] 00ff231f-9f3b454c.patch.sql [edit]
[-] 98ae1ed2-e342f869.task.php [edit]
[-] 15b30765-dd0022fb.task.php [edit]
[-] d51f303a-dad45ca2.patch.sql [edit]
[-] d51f303a-dad45ca2.task.php [edit]
[-] 6de40a4d-1b0fce99.patch.sql [edit]
[-] c37e1656-e2b4e5cb.task.php [edit]
[-] 15719536-dd0022fb.patch.sql [edit]
[-] 2e20a0eb-98ae1ed2.patch.sql [edit]
[-] 5cd0a25a-2d590ffa.cleanup.sql [edit]
[-] 435c62c3-2e7531a2.cleanup.sql [edit]
[-] 70921d5c-26fd79dc.patch.sql [edit]
[-] 98ae1ed2-e342f869.patch.sql [edit]
[-] 1da1bcba-15b30765.patch.sql [edit]
[-] 03ff59bf-b26f29a6.patch.sql [edit]
[-] add62892-c37e1656.patch.sql [edit]
[-] b26f29a6-1ee831c8.task.php [edit]
[-] 9143a511-0d6099a6.cleanup.sql [edit]
[-] 15b30765-dd0022fb.cleanup.sql [edit]
[-] e2b4e5cb-f0d2e14d.patch.sql [edit]
[-] 1b0fce99-ed60ba20.patch.sql [edit]
[-] 934954de-f1ccd3bb.task.php [edit]
[-] c00511c7-7be60a84.patch.sql [edit]
[-] 26fd79dc-00c949a6.patch.sql [edit]
[-] 1ee831c8-36f6b328.cleanup.sql [edit]
[-] e7038ce9-ddbe2e76.task.php [edit]
[-] dad45ca2-61c9d5d7.cleanup.sql [edit]
[-] 61c9d5d7-6de40a4d.task.php [edit]
[-] c2d2fabf-aa4664af.patch.sql [edit]
[-] 0d6099a6-98ad7d55.cleanup.sql [edit]
[-] 9143a511-0d6099a6.patch.sql [edit]
[-] 934b8db8-ad9d0a5f.patch.sql [edit]
[-] 98ad7d55-934b8db8.patch.sql [edit]
[-] 87d4a323-4bd47d94.patch.sql [edit]
[-] dd0022fb-f4da0c9b.patch.sql [edit]
[-] cce1ba43-e7dfe821.patch.sql [edit]
[-] 36f6b328-5cd0a25a.patch.sql [edit]
[-] dad45ca2-61c9d5d7.task.php [edit]
[-] 8aeda901-16fcef4a.task.php [edit]
[-] 8f99b8bf-03ff59bf.cleanup.sql [edit]
[-] bbb021fb-49478749.patch.sql [edit]
[-] ad9d0a5f-0ca85857.patch.sql [edit]
[-] f0d2e14d-83a22ba2.patch.sql [edit]
[-] 03ff59bf-b26f29a6.cleanup.sql [edit]
[-] f8856d56-abe9c0cb.patch.sql [edit]
[-] 0d6099a6-98ad7d55.patch.sql [edit]
[-] e342f869-c00511c7.patch.sql [edit]
[-] 00c949a6-8b923d61.patch.sql [edit]
[-] 00c949a6-8b923d61.task.php [edit]
[-] dad45ca2-61c9d5d7.patch.sql [edit]
[-] f0d2e14d-83a22ba2.task.php [edit]
[-] e6978154-cc6d1b03.patch.sql [edit]
[-] 86707325-526c601b.patch.sql [edit]
[-] c00511c7-7be60a84.cleanup.sql [edit]
[-] b26f29a6-1ee831c8.cleanup.sql [edit]
[-] 4bd47d94-e7038ce9.cleanup.sql [edit]
[-] 934954de-f1ccd3bb.patch.sql [edit]
[-] 1ee831c8-36f6b328.task.php [edit]
[-] d0e37dca-1da1bcba.patch.sql [edit]
[-] ddbe2e76-add62892.patch.sql [edit]
[-] f1ccd3bb-f5692e24.patch.sql [edit]
[-] 36f6b328-5cd0a25a.cleanup.sql [edit]
[-] b19dc97d-435c62c3.patch.sql [edit]
[-] abe9c0cb-bbb021fb.patch.sql [edit]
[-] 87d4a323-4bd47d94.task.php [edit]
[-] f1ccd3bb-f5692e24.task.php [edit]
[-] 1ee831c8-36f6b328.patch.sql [edit]
[-] cc6d1b03-914098f4.patch.sql [edit]
[-] f5692e24-4323a6a8.task.php [edit]
[-] b26f29a6-1ee831c8.patch.sql [edit]
[-] aee589ab-98ae1ed2.patch.sql [edit]
[-] 60fcbee1-f8856d56.patch.sql [edit]
[-] 8b923d61-9b5550da.patch.sql [edit]
[-] 86707325-526c601b.cleanup.sql [edit]
[-] 914098f4-87d4a323.patch.sql [edit]
[-] 435c62c3-2e7531a2.patch.sql [edit]
[-] 2d590ffa-9143a511.patch.sql [edit]
[-] 8f99b8bf-03ff59bf.task.php [edit]
[-] 49478749-c2d2fabf.patch.sql [edit]
[-] aa4664af-b19dc97d.patch.sql [edit]
[-] f5692e24-4323a6a8.patch.sql [edit]
[-] f1ccd3bb-f5692e24.cleanup.sql [edit]
[-] 0ca85857-86707325.patch.sql [edit]
[-] 4bd47d94-e7038ce9.patch.sql [edit]
[-] 26fd79dc-00c949a6.cleanup.sql [edit]
[-] 526c601b-cce1ba43.patch.sql [edit]
[-] 522e5b78-02decaa2.patch.sql [edit]
[-] 15b30765-dd0022fb.patch.sql [edit]
[-] d959a00e-32de1766.patch.sql [edit]
[-] 9b5550da-e6978154.patch.sql [edit]
[-] 4bd47d94-e7038ce9.task.php [edit]
[-] 934b8db8-ad9d0a5f.task.php [edit]
[-] f0d2e14d-83a22ba2.cleanup.sql [edit]
[-] c37e1656-e2b4e5cb.patch.sql [edit]
[-] 435c62c3-2e7531a2.task.php [edit]
[-] 5cd0a25a-2d590ffa.patch.sql [edit]
[-] 8aeda901-16fcef4a.patch.sql [edit]
[-] 98ae1ed2-e342f869.cleanup.sql [edit]
[-] 26fd79dc-00c949a6.task.php [edit]
[-] 0d6099a6-98ad7d55.task.php [edit]
[-] 2e7531a2-d0e37dca.patch.sql [edit]
[-] 4323a6a8-9ef33a06.patch.sql [edit]
[-] e7dfe821-70921d5c.patch.sql [edit]
[-] f4da0c9b-00ff231f.patch.sql [edit]
[-] 02decaa2-60fcbee1.patch.sql [edit]
[-] 7be60a84-522e5b78.patch.sql [edit]
[-] c00511c7-7be60a84.task.php [edit]
[-] 9143a511-0d6099a6.task.php [edit]
[-] 83a22ba2-5fb92bef.patch.sql [edit]
[-] a67ba35e-98ae1ed2.patch.sql [edit]