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

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

/**
 * @version v1.9.0
 * @signature 4323a6a81c35efbf7722b7fc4e475440
 * @title Add client login feature
 *
 */

ALTER TABLE `%TABLE_PREFIX%session`
  CHANGE `user_id` `user_id` varchar(16) NOT NULL default '0' COMMENT 'osTicket staff/client ID';

ALTER TABLE `%TABLE_PREFIX%staff`
  CHANGE `signature` `signature` text NOT NULL;

ALTER TABLE `%TABLE_PREFIX%department`
  CHANGE `dept_signature` `dept_signature` text NOT NULL;

DROP TABLE IF EXISTS `%TABLE_PREFIX%email_account`;
CREATE TABLE `%TABLE_PREFIX%email_account` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `protocol` varchar(64) NOT NULL DEFAULT '',
  `host` varchar(128) NOT NULL DEFAULT '',
  `port` int(11) NOT NULL,
  `username` varchar(128) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `options` varchar(512) DEFAULT NULL,
  `errors` int(11) unsigned DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `lastconnect` timestamp NULL DEFAULT NULL,
  `lasterror` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

ALTER TABLE `%TABLE_PREFIX%faq_category`
  CHANGE `created` `created` datetime NOT NULL,
  CHANGE `updated` `updated` datetime NOT NULL;

-- There was a major goof for osTicket 1.8.0 where the installer created a
-- `form_id` column in the `%filter` table; however, the upgrader neglected
-- to add the column. Therefore, users who have upgraded from a version
-- previous to 1.8.0 will not have the `form_id` column in their database
-- whereas users who installed osTicket >= v1.8.0 and upgraded will have the
-- column. Since MySQL has no concept of `ADD COLUMN IF NOT EXISTS`, this
-- dynamic query will assist with adding the column if it doesn't exist.
SET @s = (SELECT IF(
    (SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = '%TABLE_PREFIX%filter'
        AND table_schema = DATABASE()
        AND column_name = 'form_id'
    ) > 0,
    "SELECT 1",
    "ALTER TABLE `%TABLE_PREFIX%filter` ADD `form_id` int(11) unsigned NOT NULL default '0' AFTER `sla_id`"
));
PREPARE stmt FROM @s;
EXECUTE stmt;

ALTER TABLE `%TABLE_PREFIX%filter`
  ADD `topic_id` int(11) unsigned NOT NULL default '0' AFTER `form_id`;

ALTER TABLE `%TABLE_PREFIX%email`
  ADD `topic_id` int(11) unsigned NOT NULL default '0' AFTER `dept_id`;

ALTER TABLE `%TABLE_PREFIX%help_topic`
  ADD `sort` int(10) unsigned NOT NULL default '0' AFTER `form_id`;

RENAME TABLE `%TABLE_PREFIX%page` TO `%TABLE_PREFIX%content`;
ALTER TABLE `%TABLE_PREFIX%content`
  CHANGE `type` `type` varchar(32) NOT NULL default 'other';

DROP TABLE IF EXISTS `%TABLE_PREFIX%user_account`;
CREATE TABLE `%TABLE_PREFIX%user_account` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `org_id` int(11) unsigned NOT NULL,
  `status` int(11) unsigned NOT NULL DEFAULT '0',
  `timezone_id` int(11) NOT NULL DEFAULT '0',
  `dst` tinyint(1) NOT NULL DEFAULT '1',
  `lang` varchar(16) DEFAULT NULL,
  `username` varchar(64) DEFAULT NULL,
  `passwd` varchar(128) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `backend` varchar(32) DEFAULT NULL,
  `registered` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `%TABLE_PREFIX%organization`;
CREATE TABLE `%TABLE_PREFIX%organization` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL DEFAULT '',
  `staff_id` int(10) unsigned NOT NULL DEFAULT '0',
  `created` timestamp NULL DEFAULT NULL,
  `updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

DELETE FROM `%TABLE_PREFIX%config` where `namespace`='core'
    AND `key` = 'show_related_tickets';

-- Transfer access link template
INSERT INTO `%TABLE_PREFIX%content`
    (`name`, `body`, `type`, `isactive`, `created`, `updated`)
    SELECT A1.`subject`, A1.`body`, 'access-link', 1, A1.`created`, A1.`updated`
    FROM `%TABLE_PREFIX%email_template` A1
    WHERE A1.`tpl_id` = (SELECT `value` FROM `%TABLE_PREFIX%config` A3
        WHERE A3.`key` = 'default_template_id' and `namespace` = 'core')
    AND A1.`code_name` = 'user.accesslink';

-- Transfer staff password reset link
INSERT INTO `%TABLE_PREFIX%content`
    (`name`, `body`, `type`, `isactive`, `created`, `updated`)
    SELECT A1.`subject`, A1.`body`, 'pwreset-staff', 1, A1.`created`, A1.`updated`
    FROM `%TABLE_PREFIX%email_template` A1
    WHERE A1.`tpl_id` = (SELECT `value` FROM `%TABLE_PREFIX%config` A3
        WHERE A3.`key` = 'default_template_id' and `namespace` = 'core')
    AND A1.`code_name` = 'staff.pwreset';

-- No longer saved in the email_template table
DELETE FROM `%TABLE_PREFIX%email_template`
    WHERE `code_name` IN ('staff.pwreset', 'user.accesslink');

-- The original patch for d51f303a-dad45ca2.patch.sql migrated all the
-- thread entries from text to html. Now that the format column exists in
-- the ticket_thread table, we opted to retroactively add the format column
-- to the dad45ca2 patch. Therefore, anyone upgrading from osTicket < 1.8.0
-- to v1.9.0 and further will alreay have a `format` column when they arrive
-- at this patch. In such a case, we'll just change the default to 'html'
SET @s = (SELECT IF(
    (SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = '%TABLE_PREFIX%ticket_thread'
        AND table_schema = DATABASE()
        AND column_name = 'format'
    ) > 0,
    "ALTER TABLE `%TABLE_PREFIX%ticket_thread` CHANGE `format` `format` varchar(16) NOT NULL default 'html'",
    "ALTER TABLE `%TABLE_PREFIX%ticket_thread` ADD `format` varchar(16) NOT NULL default 'html' AFTER `body`"
));
PREPARE stmt FROM @s;
EXECUTE stmt;

-- Finished with patch
UPDATE `%TABLE_PREFIX%config`
    SET `value` = '4323a6a81c35efbf7722b7fc4e475440'
    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]