|
Viewing File sql/upgrade.sql of 0.01.1a
|
1: -- FreeDESK SQL Upgrade Script -- PurplePixie Systems/David Cutting 2: -- 3: -- SHOW TABLES 4: -- Table: customer 5: -- DESCRIBE `customer` 6: ALTER TABLE `customer` CHANGE `customerid` `customerid` bigint(20) unsigned NOT NULL auto_increment; 7: ALTER TABLE `customer` ADD `customerid` bigint(20) unsigned NOT NULL auto_increment; 8: ALTER TABLE `customer` ADD PRIMARY KEY( `customerid` ); 9: ALTER TABLE `customer` CHANGE `firstname` `firstname` varchar(254) NOT NULL; 10: ALTER TABLE `customer` ADD `firstname` varchar(254) NOT NULL; 11: ALTER TABLE `customer` CHANGE `lastname` `lastname` varchar(254) NOT NULL; 12: ALTER TABLE `customer` ADD `lastname` varchar(254) NOT NULL; 13: ALTER TABLE `customer` CHANGE `email` `email` varchar(254) NOT NULL; 14: ALTER TABLE `customer` ADD `email` varchar(254) NOT NULL; 15: ALTER TABLE `customer` CHANGE `username` `username` varchar(254) NOT NULL; 16: ALTER TABLE `customer` ADD `username` varchar(254) NOT NULL; 17: ALTER TABLE `customer` CHANGE `password` `password` varchar(254) NOT NULL; 18: ALTER TABLE `customer` ADD `password` varchar(254) NOT NULL; 19: -- 20: -- Table: email 21: -- DESCRIBE `email` 22: ALTER TABLE `email` CHANGE `accountid` `accountid` mediumint(9) NOT NULL auto_increment; 23: ALTER TABLE `email` ADD `accountid` mediumint(9) NOT NULL auto_increment; 24: ALTER TABLE `email` ADD PRIMARY KEY( `accountid` ); 25: ALTER TABLE `email` CHANGE `name` `name` varchar(254) NOT NULL; 26: ALTER TABLE `email` ADD `name` varchar(254) NOT NULL; 27: ALTER TABLE `email` CHANGE `host` `host` varchar(254) NOT NULL; 28: ALTER TABLE `email` ADD `host` varchar(254) NOT NULL; 29: ALTER TABLE `email` CHANGE `from` `from` varchar(254) NOT NULL; 30: ALTER TABLE `email` ADD `from` varchar(254) NOT NULL; 31: ALTER TABLE `email` CHANGE `fromname` `fromname` varchar(254) NOT NULL; 32: ALTER TABLE `email` ADD `fromname` varchar(254) NOT NULL; 33: ALTER TABLE `email` CHANGE `wordwrap` `wordwrap` int(11) NOT NULL; 34: ALTER TABLE `email` ADD `wordwrap` int(11) NOT NULL; 35: ALTER TABLE `email` CHANGE `auth` `auth` int(11) NOT NULL; 36: ALTER TABLE `email` ADD `auth` int(11) NOT NULL; 37: ALTER TABLE `email` CHANGE `username` `username` varchar(254) NOT NULL; 38: ALTER TABLE `email` ADD `username` varchar(254) NOT NULL; 39: ALTER TABLE `email` CHANGE `password` `password` varchar(254) NOT NULL; 40: ALTER TABLE `email` ADD `password` varchar(254) NOT NULL; 41: ALTER TABLE `email` CHANGE `smtpsec` `smtpsec` varchar(128) NOT NULL; 42: ALTER TABLE `email` ADD `smtpsec` varchar(128) NOT NULL; 43: -- 44: -- Table: permgroup 45: -- DESCRIBE `permgroup` 46: ALTER TABLE `permgroup` CHANGE `permgroupid` `permgroupid` bigint(20) unsigned NOT NULL auto_increment; 47: ALTER TABLE `permgroup` ADD `permgroupid` bigint(20) unsigned NOT NULL auto_increment; 48: ALTER TABLE `permgroup` ADD PRIMARY KEY( `permgroupid` ); 49: ALTER TABLE `permgroup` CHANGE `groupname` `groupname` varchar(254) NOT NULL; 50: ALTER TABLE `permgroup` ADD `groupname` varchar(254) NOT NULL; 51: -- 52: -- Table: permissions 53: -- DESCRIBE `permissions` 54: ALTER TABLE `permissions` CHANGE `permissionid` `permissionid` bigint(20) NOT NULL auto_increment; 55: ALTER TABLE `permissions` ADD `permissionid` bigint(20) NOT NULL auto_increment; 56: ALTER TABLE `permissions` ADD PRIMARY KEY( `permissionid` ); 57: ALTER TABLE `permissions` CHANGE `permissiontype` `permissiontype` varchar(16) NOT NULL; 58: ALTER TABLE `permissions` ADD `permissiontype` varchar(16) NOT NULL; 59: CREATE INDEX `permissiontype` ON `permissions` ( `permissiontype` ); 60: ALTER TABLE `permissions` CHANGE `permission` `permission` varchar(254) NOT NULL; 61: ALTER TABLE `permissions` ADD `permission` varchar(254) NOT NULL; 62: ALTER TABLE `permissions` CHANGE `usergroupid` `usergroupid` varchar(254) NOT NULL; 63: ALTER TABLE `permissions` ADD `usergroupid` varchar(254) NOT NULL; 64: ALTER TABLE `permissions` CHANGE `allowed` `allowed` tinyint(4) NOT NULL; 65: ALTER TABLE `permissions` ADD `allowed` tinyint(4) NOT NULL; 66: -- 67: -- Table: plugins 68: -- DESCRIBE `plugins` 69: ALTER TABLE `plugins` CHANGE `pluginid` `pluginid` bigint(20) NOT NULL auto_increment; 70: ALTER TABLE `plugins` ADD `pluginid` bigint(20) NOT NULL auto_increment; 71: ALTER TABLE `plugins` ADD PRIMARY KEY( `pluginid` ); 72: ALTER TABLE `plugins` CHANGE `plugin` `plugin` varchar(254) NOT NULL; 73: ALTER TABLE `plugins` ADD `plugin` varchar(254) NOT NULL; 74: ALTER TABLE `plugins` CHANGE `active` `active` int(11) NOT NULL DEFAULT '0'; 75: ALTER TABLE `plugins` ADD `active` int(11) NOT NULL DEFAULT '0'; 76: CREATE INDEX `active` ON `plugins` ( `active` ); 77: -- 78: -- Table: priority 79: -- DESCRIBE `priority` 80: ALTER TABLE `priority` CHANGE `priorityid` `priorityid` bigint(20) unsigned NOT NULL auto_increment; 81: ALTER TABLE `priority` ADD `priorityid` bigint(20) unsigned NOT NULL auto_increment; 82: ALTER TABLE `priority` ADD PRIMARY KEY( `priorityid` ); 83: ALTER TABLE `priority` CHANGE `priorityname` `priorityname` varchar(128) NOT NULL; 84: ALTER TABLE `priority` ADD `priorityname` varchar(128) NOT NULL; 85: ALTER TABLE `priority` CHANGE `resolutionsla` `resolutionsla` bigint(20) NOT NULL; 86: ALTER TABLE `priority` ADD `resolutionsla` bigint(20) NOT NULL; 87: ALTER TABLE `priority` CHANGE `schedule` `schedule` int(11) NOT NULL; 88: ALTER TABLE `priority` ADD `schedule` int(11) NOT NULL; 89: -- 90: -- Table: request 91: -- DESCRIBE `request` 92: ALTER TABLE `request` CHANGE `requestid` `requestid` bigint(20) NOT NULL auto_increment; 93: ALTER TABLE `request` ADD `requestid` bigint(20) NOT NULL auto_increment; 94: ALTER TABLE `request` ADD PRIMARY KEY( `requestid` ); 95: ALTER TABLE `request` CHANGE `customer` `customer` bigint(20) NOT NULL; 96: ALTER TABLE `request` ADD `customer` bigint(20) NOT NULL; 97: CREATE INDEX `customer` ON `request` ( `customer` ); 98: ALTER TABLE `request` CHANGE `assignteam` `assignteam` bigint(20) NOT NULL; 99: ALTER TABLE `request` ADD `assignteam` bigint(20) NOT NULL; 100: ALTER TABLE `request` CHANGE `assignuser` `assignuser` varchar(254) NOT NULL; 101: ALTER TABLE `request` ADD `assignuser` varchar(254) NOT NULL; 102: ALTER TABLE `request` CHANGE `class` `class` int(11) NOT NULL; 103: ALTER TABLE `request` ADD `class` int(11) NOT NULL; 104: ALTER TABLE `request` CHANGE `openeddt` `openeddt` datetime NOT NULL; 105: ALTER TABLE `request` ADD `openeddt` datetime NOT NULL; 106: ALTER TABLE `request` CHANGE `status` `status` int(11) NOT NULL; 107: ALTER TABLE `request` ADD `status` int(11) NOT NULL; 108: ALTER TABLE `request` CHANGE `priority` `priority` int(11) NOT NULL; 109: ALTER TABLE `request` ADD `priority` int(11) NOT NULL; 110: ALTER TABLE `request` CHANGE `closeddt` `closeddt` datetime NOT NULL; 111: ALTER TABLE `request` ADD `closeddt` datetime NOT NULL; 112: -- 113: -- Table: requestclass 114: -- DESCRIBE `requestclass` 115: ALTER TABLE `requestclass` CHANGE `classid` `classid` int(10) unsigned NOT NULL auto_increment; 116: ALTER TABLE `requestclass` ADD `classid` int(10) unsigned NOT NULL auto_increment; 117: ALTER TABLE `requestclass` ADD PRIMARY KEY( `classid` ); 118: ALTER TABLE `requestclass` CHANGE `classname` `classname` varchar(254) NOT NULL; 119: ALTER TABLE `requestclass` ADD `classname` varchar(254) NOT NULL; 120: ALTER TABLE `requestclass` CHANGE `classclass` `classclass` varchar(254) NOT NULL; 121: ALTER TABLE `requestclass` ADD `classclass` varchar(254) NOT NULL; 122: -- 123: -- Table: session 124: -- DESCRIBE `session` 125: ALTER TABLE `session` CHANGE `session_id` `session_id` varchar(254) NOT NULL; 126: ALTER TABLE `session` ADD `session_id` varchar(254) NOT NULL; 127: ALTER TABLE `session` ADD PRIMARY KEY( `session_id` ); 128: ALTER TABLE `session` CHANGE `username` `username` varchar(254) NOT NULL; 129: ALTER TABLE `session` ADD `username` varchar(254) NOT NULL; 130: ALTER TABLE `session` CHANGE `sessiontype` `sessiontype` int(11) NOT NULL DEFAULT '-1'; 131: ALTER TABLE `session` ADD `sessiontype` int(11) NOT NULL DEFAULT '-1'; 132: ALTER TABLE `session` CHANGE `created_dt` `created_dt` datetime NOT NULL; 133: ALTER TABLE `session` ADD `created_dt` datetime NOT NULL; 134: ALTER TABLE `session` CHANGE `updated_dt` `updated_dt` datetime NOT NULL; 135: ALTER TABLE `session` ADD `updated_dt` datetime NOT NULL; 136: ALTER TABLE `session` CHANGE `expires_dt` `expires_dt` datetime NOT NULL; 137: ALTER TABLE `session` ADD `expires_dt` datetime NOT NULL; 138: ALTER TABLE `session` CHANGE `realname` `realname` varchar(254) NOT NULL; 139: ALTER TABLE `session` ADD `realname` varchar(254) NOT NULL; 140: -- 141: -- Table: status 142: -- DESCRIBE `status` 143: ALTER TABLE `status` CHANGE `status` `status` int(11) NOT NULL; 144: ALTER TABLE `status` ADD `status` int(11) NOT NULL; 145: ALTER TABLE `status` ADD PRIMARY KEY( `status` ); 146: ALTER TABLE `status` CHANGE `description` `description` varchar(254) NOT NULL; 147: ALTER TABLE `status` ADD `description` varchar(254) NOT NULL; 148: -- 149: -- Table: sysconfig 150: -- DESCRIBE `sysconfig` 151: ALTER TABLE `sysconfig` CHANGE `sc_option` `sc_option` varchar(254) NOT NULL; 152: ALTER TABLE `sysconfig` ADD `sc_option` varchar(254) NOT NULL; 153: ALTER TABLE `sysconfig` ADD PRIMARY KEY( `sc_option` ); 154: ALTER TABLE `sysconfig` CHANGE `sc_value` `sc_value` varchar(254) NOT NULL; 155: ALTER TABLE `sysconfig` ADD `sc_value` varchar(254) NOT NULL; 156: -- 157: -- Table: syslog 158: -- DESCRIBE `syslog` 159: ALTER TABLE `syslog` CHANGE `event_id` `event_id` bigint(20) unsigned NOT NULL auto_increment; 160: ALTER TABLE `syslog` ADD `event_id` bigint(20) unsigned NOT NULL auto_increment; 161: ALTER TABLE `syslog` ADD PRIMARY KEY( `event_id` ); 162: ALTER TABLE `syslog` CHANGE `event_dt` `event_dt` datetime NOT NULL; 163: ALTER TABLE `syslog` ADD `event_dt` datetime NOT NULL; 164: ALTER TABLE `syslog` CHANGE `event` `event` varchar(254) NOT NULL; 165: ALTER TABLE `syslog` ADD `event` varchar(254) NOT NULL; 166: ALTER TABLE `syslog` CHANGE `event_class` `event_class` varchar(128) NOT NULL; 167: ALTER TABLE `syslog` ADD `event_class` varchar(128) NOT NULL; 168: ALTER TABLE `syslog` CHANGE `event_type` `event_type` varchar(128) NOT NULL; 169: ALTER TABLE `syslog` ADD `event_type` varchar(128) NOT NULL; 170: ALTER TABLE `syslog` CHANGE `event_level` `event_level` int(11) NOT NULL; 171: ALTER TABLE `syslog` ADD `event_level` int(11) NOT NULL; 172: -- 173: -- Table: team 174: -- DESCRIBE `team` 175: ALTER TABLE `team` CHANGE `teamid` `teamid` bigint(20) unsigned NOT NULL auto_increment; 176: ALTER TABLE `team` ADD `teamid` bigint(20) unsigned NOT NULL auto_increment; 177: ALTER TABLE `team` ADD PRIMARY KEY( `teamid` ); 178: ALTER TABLE `team` CHANGE `teamname` `teamname` varchar(254) NOT NULL; 179: ALTER TABLE `team` ADD `teamname` varchar(254) NOT NULL; 180: -- 181: -- Table: teamuserlink 182: -- DESCRIBE `teamuserlink` 183: ALTER TABLE `teamuserlink` CHANGE `linkid` `linkid` bigint(20) unsigned NOT NULL auto_increment; 184: ALTER TABLE `teamuserlink` ADD `linkid` bigint(20) unsigned NOT NULL auto_increment; 185: ALTER TABLE `teamuserlink` ADD PRIMARY KEY( `linkid` ); 186: ALTER TABLE `teamuserlink` CHANGE `teamid` `teamid` bigint(20) unsigned NOT NULL; 187: ALTER TABLE `teamuserlink` ADD `teamid` bigint(20) unsigned NOT NULL; 188: CREATE INDEX `teamid` ON `teamuserlink` ( `teamid` ); 189: ALTER TABLE `teamuserlink` CHANGE `username` `username` varchar(254) NOT NULL; 190: ALTER TABLE `teamuserlink` ADD `username` varchar(254) NOT NULL; 191: -- 192: -- Table: templates 193: -- DESCRIBE `templates` 194: ALTER TABLE `templates` CHANGE `templateid` `templateid` varchar(128) NOT NULL; 195: ALTER TABLE `templates` ADD `templateid` varchar(128) NOT NULL; 196: ALTER TABLE `templates` ADD PRIMARY KEY( `templateid` ); 197: ALTER TABLE `templates` CHANGE `subject` `subject` text NOT NULL; 198: ALTER TABLE `templates` ADD `subject` text NOT NULL; 199: ALTER TABLE `templates` CHANGE `body` `body` text NOT NULL; 200: ALTER TABLE `templates` ADD `body` text NOT NULL; 201: -- 202: -- Table: update 203: -- DESCRIBE `update` 204: ALTER TABLE `update` CHANGE `updateid` `updateid` bigint(20) unsigned NOT NULL auto_increment; 205: ALTER TABLE `update` ADD `updateid` bigint(20) unsigned NOT NULL auto_increment; 206: ALTER TABLE `update` ADD PRIMARY KEY( `updateid` ); 207: ALTER TABLE `update` CHANGE `requestid` `requestid` bigint(20) unsigned NOT NULL; 208: ALTER TABLE `update` ADD `requestid` bigint(20) unsigned NOT NULL; 209: CREATE INDEX `requestid` ON `update` ( `requestid` ); 210: ALTER TABLE `update` CHANGE `update` `update` text NOT NULL; 211: ALTER TABLE `update` ADD `update` text NOT NULL; 212: ALTER TABLE `update` CHANGE `public` `public` tinyint(4) NOT NULL; 213: ALTER TABLE `update` ADD `public` tinyint(4) NOT NULL; 214: ALTER TABLE `update` CHANGE `updateby` `updateby` varchar(254) NOT NULL; 215: ALTER TABLE `update` ADD `updateby` varchar(254) NOT NULL; 216: ALTER TABLE `update` CHANGE `updatedt` `updatedt` datetime NOT NULL; 217: ALTER TABLE `update` ADD `updatedt` datetime NOT NULL; 218: -- 219: -- Table: user 220: -- DESCRIBE `user` 221: ALTER TABLE `user` CHANGE `username` `username` varchar(254) NOT NULL; 222: ALTER TABLE `user` ADD `username` varchar(254) NOT NULL; 223: ALTER TABLE `user` ADD PRIMARY KEY( `username` ); 224: ALTER TABLE `user` CHANGE `password` `password` varchar(254) NOT NULL; 225: ALTER TABLE `user` ADD `password` varchar(254) NOT NULL; 226: ALTER TABLE `user` CHANGE `realname` `realname` varchar(254) NOT NULL; 227: ALTER TABLE `user` ADD `realname` varchar(254) NOT NULL; 228: ALTER TABLE `user` CHANGE `email` `email` varchar(254) NOT NULL; 229: ALTER TABLE `user` ADD `email` varchar(254) NOT NULL; 230: ALTER TABLE `user` CHANGE `permgroup` `permgroup` bigint(20) unsigned NOT NULL; 231: ALTER TABLE `user` ADD `permgroup` bigint(20) unsigned NOT NULL; 232: ALTER TABLE `user` CHANGE `authtype` `authtype` varchar(254) NOT NULL; 233: ALTER TABLE `user` ADD `authtype` varchar(254) NOT NULL; 234: ALTER TABLE `user` CHANGE `sparefield0` `sparefield0` varchar(254) NOT NULL; 235: ALTER TABLE `user` ADD `sparefield0` varchar(254) NOT NULL; 236: ALTER TABLE `user` CHANGE `sparefield1` `sparefield1` varchar(254) NOT NULL; 237: ALTER TABLE `user` ADD `sparefield1` varchar(254) NOT NULL; 238: ALTER TABLE `user` CHANGE `sparefield2` `sparefield2` varchar(254) NOT NULL; 239: ALTER TABLE `user` ADD `sparefield2` varchar(254) NOT NULL; 240: ALTER TABLE `user` CHANGE `sparefield3` `sparefield3` varchar(254) NOT NULL; 241: ALTER TABLE `user` ADD `sparefield3` varchar(254) NOT NULL; 242: ALTER TABLE `user` CHANGE `sparefield4` `sparefield4` varchar(254) NOT NULL; 243: ALTER TABLE `user` ADD `sparefield4` varchar(254) NOT NULL; 244: ALTER TABLE `user` CHANGE `sparefield5` `sparefield5` varchar(254) NOT NULL; 245: ALTER TABLE `user` ADD `sparefield5` varchar(254) NOT NULL; 246: ALTER TABLE `user` CHANGE `sparefield6` `sparefield6` varchar(254) NOT NULL; 247: ALTER TABLE `user` ADD `sparefield6` varchar(254) NOT NULL; 248: ALTER TABLE `user` CHANGE `sparefield7` `sparefield7` varchar(254) NOT NULL; 249: ALTER TABLE `user` ADD `sparefield7` varchar(254) NOT NULL; 250: ALTER TABLE `user` CHANGE `sparefield8` `sparefield8` varchar(254) NOT NULL; 251: ALTER TABLE `user` ADD `sparefield8` varchar(254) NOT NULL; 252: ALTER TABLE `user` CHANGE `sparefield9` `sparefield9` varchar(254) NOT NULL; 253: ALTER TABLE `user` ADD `sparefield9` varchar(254) NOT NULL; 254: -- 255: -- Table: vis_country 256: -- DESCRIBE `vis_country` 257: ALTER TABLE `vis_country` CHANGE `country` `country` char(2) NOT NULL; 258: ALTER TABLE `vis_country` ADD `country` char(2) NOT NULL; 259: CREATE INDEX `country` ON `vis_country` ( `country` ); 260: ALTER TABLE `vis_country` CHANGE `country_desc` `country_desc` varchar(120) NOT NULL; 261: ALTER TABLE `vis_country` ADD `country_desc` varchar(120) NOT NULL; 262: CREATE INDEX `country_desc` ON `vis_country` ( `country_desc` ); 263: ALTER TABLE `vis_country` CHANGE `lat` `lat` float; 264: ALTER TABLE `vis_country` ADD `lat` float; 265: ALTER TABLE `vis_country` CHANGE `long` `long` float; 266: ALTER TABLE `vis_country` ADD `long` float; 267: -- 268: