File: 0.01.1a/sql/upgrade.sql (View as HTML)

  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: