<font color=blue><b>Upgrade from v2.0x</blue></font>///<font color=blue>Upgrade from v2.0x (Upgrade <b>from previous v2.0x (like v2.065, v2.04, etc.)</b> to v3)</font>
CREATE TABLE `perlDesk_accesslog` (`id` int(11) unsigned NOT NULL auto_increment, `user` varchar(255) default NULL, `ip` varchar(255) default NULL, `time` varchar(255) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_activitylog` (`id` int(11) unsigned NOT NULL auto_increment, `cid` varchar(255) NOT NULL, `date` varchar(255) NOT NULL, `user` varchar(255) NOT NULL, `action` varchar(255) NOT NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_announce` (`id` int(11) unsigned NOT NULL auto_increment, `author` varchar(255) default NULL, `subject` varchar(255) default NULL, `message` text default NULL, `staff` char(1) default NULL, `users` char(1) default NULL, `time` varchar(16) default NULL, `display_until` varchar(255) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_blocked_email` (`id` int(11) unsigned NOT NULL auto_increment, `address` varchar(255) NOT NULL, `type` varchar(255) NOT NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_call_fields` (`id` int(11) unsigned NOT NULL auto_increment, `cid` varchar(255) default NULL, `fid` varchar(255) default NULL, `value` text default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_callrules` (`id` int(11) unsigned NOT NULL auto_increment, `rule` int(11) NOT NULL, `type` text default NULL, `value` text DEFAULT NULL, `param` text DEFAULT NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_calls` (`id` int(11) unsigned NOT NULL auto_increment, `status` enum('OPEN','CLOSED','HOLD','DELETED') NOT NULL, `username` varchar(30) default NULL, `email` varchar(255) default NULL, `priority` char(1) default NULL, `category` varchar(255) default NULL, `subject` varchar(255) default NULL, `description` text default NULL, `time` varchar(20) default NULL, `ownership` varchar(255) default NULL, `closedby` varchar(255) default NULL, `method` char(2) default NULL, `track` varchar(50) NOT NULL, `active` varchar(50) NOT NULL, `aw` char(1) default NULL, `an` char(1) NOT NULL, `time_spent` varchar(255) NOT NULL, `sub_status` text default NULL, `is_locked` int(11) NOT NULL, `ikey` varchar(255) NOT NULL, `close_timer` int(11) default NULL, `lock_time` int(11) default NULL, `due_date` datetime default NULL, `color` varchar(255) default NULL, PRIMARY KEY (`id`), INDEX (`status`));
CREATE TABLE `perlDesk_chat` (`id` int(11) unsigned NOT NULL auto_increment, `session_id` int(11) unsigned, `time` TIMESTAMP NOT NULL, `username` VARCHAR(30) default NULL, `staffname` VARCHAR(20) DEFAULT NULL, `department` VARCHAR(255) DEFAULT NULL, `direction` int NOT NULL DEFAULT 0, `type` int NOT NULL DEFAULT 0, `status` int NOT NULL DEFAULT 0, `message` text, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_chatsession` (`id` int(11) unsigned NOT NULL auto_increment, `time` TIMESTAMP NOT NULL, `activity` TIMESTAMP NOT NULL, `username` VARCHAR(30) default NULL, `staffname` VARCHAR(20) DEFAULT NULL, `department` VARCHAR(255) DEFAULT NULL,  `state` text, `message` text, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_contacts` (`id` int(11) unsigned NOT NULL auto_increment, `staff` int(11) NOT NULL, `name` varchar(50) NOT NULL, `company` varchar(50) NOT NULL, `address` varchar(255) NOT NULL, `email` varchar(90) NOT NULL, `phone` varchar(19) default NULL, `url` varchar(90) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_cronrules` (`id` int(11) unsigned NOT NULL auto_increment, `period` int(11) NOT NULL, `action` text default NULL, `lastrun` INT NOT NULL, `lastcheck` INT NOT NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_departments` (`id` int(11) unsigned NOT NULL auto_increment, `level` varchar(255) default NULL, `from_name` text default NULL, `from_email` text default NULL, `assign_staff` text default NULL, `internal` int(1) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_downloadcats` (`id` int(11) unsigned NOT NULL auto_increment, `parent_id` int(11) default NULL, `name` varchar(255) default NULL, `assign_staff` text default NULL, `datetime` timestamp , PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_downloadfiles` (`id` int(11) unsigned NOT NULL auto_increment, `filename` varchar(255) default NULL, `parent_id` int(11) unsigned NOT NULL, `subject` text NOT NULL, `keywords` text default NULL, `description` text default NULL, `pd_file_id` int(11) NOT NULL, `owner` int(11) default NULL, `datetime` timestamp , PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_em_forwarders` (`id` int(11) unsigned NOT NULL auto_increment, `address` varchar(255) default NULL, `category` varchar(255) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_em_floodprotect` (`id` int(11) NOT NULL auto_increment, `sender` varchar(255) default NULL, `timestamp` varchar(255) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_emaillog` (`id` int(11) unsigned NOT NULL auto_increment, `time` TIMESTAMP NOT NULL, `from` varchar(255), `msgid` varchar(255), `subject` varchar(255), `log` varchar(255), PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_files` (`id` int(11) unsigned NOT NULL auto_increment, `cid` int(11) default NULL, `nid` int(11) default NULL, `filename` varchar(255) NOT NULL, `file` varchar(255) NOT NULL, `kb` int(11) default NULL, `type` text default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_kb_acronym` (`id` int(11) unsigned NOT NULL auto_increment, `letter` char(1) default NULL, `acronym` varchar(255) default NULL, `acronymname` varchar(255) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_kb_cat` (`id` int(11) unsigned NOT NULL auto_increment, `category` varchar(255) default NULL, `parent_category` int(11) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_kb_comments` (`id` int(11) unsigned NOT NULL auto_increment, `article_id` int(11) default NULL, `name` varchar(255) default NULL, `email` varchar(255) default NULL, `comments` text default NULL, PRIMARY KEY (`id`), INDEX (`article_id`));
CREATE TABLE `perlDesk_kb_entries` (`id` int(11) unsigned NOT NULL auto_increment, `category` varchar(255) default NULL, `catid` int(11) default NULL, `author` varchar(255) default NULL, `subject` varchar(255) default NULL, `keywords` text default NULL, `description` text default NULL, `views` varchar(50) default NULL, `stamp` timestamp , `filetype` varchar(255) default NULL, `fileid` int(11) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_kb_glossary` (`id` int(11) unsigned NOT NULL auto_increment, `letter` char(1) default NULL, `shortname` varchar(255) default NULL, `description` text default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_kb_ratings` (`id` int(11) unsigned NOT NULL auto_increment, `article_id` int(11) default NULL, `word_id` int(11) default NULL, `rating` int(2) default NULL, PRIMARY KEY (`id`), INDEX (`article_id`), INDEX (`word_id`));
CREATE TABLE `perlDesk_kb_stats` (`id` int(11) unsigned NOT NULL auto_increment, `article_id` int(11) default NULL, `number` int(11) default NULL, `date` date default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_kb_words` (`id` int(11) unsigned NOT NULL auto_increment, `word` varchar(255) default NULL, PRIMARY KEY (`id`), UNIQUE INDEX (`word`));
CREATE TABLE `perlDesk_mailrules` (`id` int(11) unsigned NOT NULL auto_increment, `rule_id` int(11) default NULL, `rule` int(5) default NULL, `action` int(5) default NULL, `text` text default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_messages` (`id` int(11) unsigned NOT NULL auto_increment, `touser` varchar(50) NOT NULL, `sender` varchar(50) NOT NULL, `date` varchar(20) NOT NULL, `subject` varchar(255) NOT NULL, `message` text NOT NULL, `stamp` varchar(20) NOT NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_notedrafts` (`id` int(11) unsigned NOT NULL auto_increment, `owner` char(1) default NULL, `visible` char(1) default NULL, `action` varchar(255) default NULL, `call` int(11) default NULL, `author` varchar(255) default NULL, `time` varchar(20) default NULL, `ikey` varchar(255) default NULL, `comment` text default NULL, `poster_ip` varchar(40) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_notes` (`id` int(11) unsigned NOT NULL auto_increment, `owner` char(1) default NULL, `visible` char(1) default NULL, `action` varchar(255) default NULL, `call` int(11) default NULL, `author` varchar(255) default NULL, `time` varchar(20) default NULL, `ikey` varchar(255) default NULL, `comment` text default NULL, `poster_ip` varchar(40) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_popservers` (`id` int(11) unsigned NOT NULL auto_increment, `pop_host` varchar(255) NOT NULL, `pop_user` varchar(255) NOT NULL, `pop_password` varchar(255) NOT NULL, `pop_port` varchar(5) NOT NULL, `stay_emails` int(3) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_preans` (`id` int(11) unsigned NOT NULL auto_increment, `author` varchar(25) default NULL, `subject` varchar(255) default NULL, `text` text default NULL, `date` varchar(20) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_reviews` (`id` int(11) unsigned NOT NULL auto_increment, `owner` varchar(255) default NULL, `staff` varchar(255) default NULL, `nid` varchar(255) default NULL, `rating` varchar(255) default NULL, `comments` text default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_settings` (`setting` varchar(50) NOT NULL, `value` varchar(225) default NULL, PRIMARY KEY (`setting`));
CREATE TABLE `perlDesk_settings_extra` (`setting` varchar(50) NOT NULL, `value` text default NULL, PRIMARY KEY (`setting`));
CREATE TABLE `perlDesk_signup_fields` (`id` int(11) unsigned NOT NULL auto_increment, `name` varchar(255) default NULL, `value` varchar(255) default NULL, `dorder` varchar(10) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_signup_values` (`id` int(11) unsigned NOT NULL auto_increment, `cid` varchar(10) default NULL, `sid` varchar(10) default NULL, `value` varchar(255) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_staff` (`id` int(11) unsigned NOT NULL auto_increment, `username` varchar(20) default NULL, `password` varchar(255) default NULL, `name` varchar(50) default NULL, `email` varchar(50) default NULL, `access` varchar(255) default NULL, `notify` varchar(1) default NULL, `responsetime` varchar(20) default NULL, `callsclosed` varchar(10) default NULL, `signature` text default NULL, `rkey` char(2) default NULL, `lpage` varchar(255) default NULL, `play_sound` char(1) default NULL, `llogin` varchar(255) default NULL, `address` text default NULL, `city` text default NULL, `state` text default NULL, `zip` text default NULL, `country` text default NULL, `phone` text default NULL, `mobile` text default NULL, `comments` text default NULL, `template` text default NULL, `number_new_main` int(11) default NULL, `awatar` int(11) default NULL, `report_access` int(1) default NULL, `redirect_answer` int(1) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_staffaccess` (`staff_id` int(11) default NULL, `dep_id` int(11) default NULL);
CREATE TABLE `perlDesk_staffactive` (`username` varchar(20) NOT NULL, `date` varchar(20) NOT NULL);
CREATE TABLE `perlDesk_stafflogin` (`username` varchar(50) default NULL, `date` varchar(20) default NULL);
CREATE TABLE `perlDesk_staffread` (`username` varchar(50) NOT NULL, `date` varchar(20) NOT NULL);
CREATE TABLE `perlDesk_survey` (`id` int(11) unsigned NOT NULL auto_increment, `order` int(11) default NULL, `text` varchar(30) NOT NULL, PRIMARY KEY (`id`) );
CREATE TABLE `perlDesk_taskbar` (`id` int(11) unsigned NOT NULL auto_increment, `username` varchar(50) default NULL, `last_call` varchar(50) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_tasks` (`id` int(11) NOT NULL auto_increment, `user_id` int(11) default NULL, `name` varchar(50) default NULL, `details` varchar(255) default NULL, `time` datetime default NULL, `notify` tinyint(4) default NULL, `notify_type` tinyint(4) default NULL, `notify_earlier` int(11) default NULL, `notify_email` varchar(255) default NULL, `your_period` int(11) default NULL, `status` tinyint(4) default NULL, `stamp` timestamp , `callid` int(11) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_templates` (`id` int(11) NOT NULL auto_increment, `user_dir` text default NULL, `staff_dir` text default NULL, `admin_dir` text default NULL, `name` text default NULL, `img_dir` text default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_ticket_fields` (`id` int(11) unsigned NOT NULL auto_increment, `name` varchar(255) default NULL, `value` varchar(255) default NULL, `dorder` varchar(10) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_tshooter` (`id` int(11) NOT NULL auto_increment, `name` varchar(50) default NULL, `first_question_id` int(11) default NULL, `stamp` timestamp , PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_tshooter_archive` (`id` int(11) NOT NULL auto_increment, `tshooter_id` int(11) default NULL, `parent_id` int(11) default NULL, `name` varchar(255) default NULL, `question` text default NULL, `answer` text default NULL, `level` tinyint(4) default NULL, `action_type` tinyint(4) default NULL, `stamp` timestamp , PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_user_events` (`id` int(11) unsigned NOT NULL auto_increment, `user` varchar(255) default NULL, `time` varchar(255) default NULL, `subject` varchar(255) default NULL, `description` text default NULL, `date` varchar(50) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_usergroups` (`id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, PRIMARY KEY (`id`));
CREATE TABLE `perlDesk_users` (`id` int(11) unsigned NOT NULL auto_increment, `username` varchar(30) default NULL, `password` varchar(255) default NULL, `name` varchar(50) default NULL, `email` text default NULL, `url` varchar(50) default NULL, `company` varchar(50) default NULL, `rkey` varchar(5) default NULL, `pending` char(1) default NULL, `active` varchar(50) default NULL, `acode` varchar(50) default NULL, `lcall` varchar(255) NOT NULL, `group_id` int(11) default NULL, `secondary_email` text default NULL, PRIMARY KEY (`id`));
UPDATE perlDesk_calls, perlDesk_departments SET perlDesk_calls.category=perlDesk_departments.id  WHERE (perlDesk_departments.level = perlDesk_calls.category) AND (!(ISNULL(category)));
UPDATE perlDesk_staff SET access = REPLACE(access, ":: ", "::") WHERE (perlDesk_staff.id != 1);
UPDATE perlDesk_staff SET access = REPLACE(access, "::", "','") WHERE (perlDesk_staff.id != 1);
UPDATE perlDesk_staff SET access = TRIM(BOTH "','" FROM access) WHERE (perlDesk_staff.id != 1);
UPDATE perlDesk_staff SET access = CONCAT("'", access, "'") WHERE (perlDesk_staff.id != 1);
INSERT INTO perlDesk_staffaccess SELECT DISTINCT perlDesk_staff.id as staff_id, perlDesk_departments.id as dep_id FROM perlDesk_staff, perlDesk_departments WHERE (perlDesk_staff.id != 1)AND(FIND_IN_SET(CONCAT("'",perlDesk_departments.level,"'"), perlDesk_staff.access)>0);
INSERT INTO perlDesk_staffaccess SELECT DISTINCT perlDesk_staff.id as staff_id, '-1' as dep_id FROM perlDesk_staff, perlDesk_departments WHERE (perlDesk_staff.id != 1)AND(FIND_IN_SET(CONCAT("'",'GLOB',"'"), perlDesk_staff.access)>0);
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('v3', 'staff_default_template');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('v3', 'admin_default_template');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('10', 'tickets_count');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('New', 'default_tab');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('1', 'downloads_feature');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('1', 'articles_feature');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('1', 'livechat_feature');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('1', 'skype_feature');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('', 'skype_name');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('0', 'forum_auth');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('1', 'forum_showthreads');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('vb3', 'forum_type');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('', 'forum_url');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('', 'forum_host');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('', 'forum_port');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('', 'forum_dbname');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('', 'forum_tableprefix');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('', 'forum_username');
REPLACE INTO perlDesk_settings_extra (value, setting) VALUES ('', 'forum_password');
REPLACE INTO perlDesk_cronrules (period, action, lastrun, lastcheck) VALUES ('1800', 'check_calls', 0, 0);
UPDATE perlDesk_staff SET report_access = 1 WHERE 1;
REPLACE perlDesk_settings (value, setting) VALUES ('3.3', 'db_version');