File:
0.01.1a/sql/upgrade.sql (
View as Code)
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: