89 -- Daten für Tabelle `t_group` |
35 -- Daten für Tabelle `t_group` |
90 -- |
36 -- |
91 |
37 |
92 INSERT INTO `t_group` (`id`, `name`) VALUES |
38 INSERT INTO `t_group` (`id`, `name`) VALUES |
93 (1, 'System'), |
39 (1, 'System'), |
94 (2, 'Admin'), |
40 (2, 'Admin'); |
95 (3, 'Manager'), |
|
96 (4, 'Supporter'), |
|
97 (5, 'Gast'); |
|
98 |
|
99 -- -------------------------------------------------------- |
|
100 |
|
101 -- |
|
102 -- Tabellenstruktur für Tabelle `t_history` |
|
103 -- |
|
104 |
|
105 CREATE TABLE IF NOT EXISTS `t_history` ( |
|
106 `id` bigint(20) unsigned NOT NULL, |
|
107 `ticket_id` bigint(11) unsigned NOT NULL DEFAULT '1', |
|
108 `action_id` bigint(20) unsigned NOT NULL DEFAULT '1', |
|
109 `user_id` bigint(20) unsigned NOT NULL DEFAULT '1', |
|
110 `status_id` bigint(20) unsigned NOT NULL DEFAULT '1', |
|
111 `subject` varchar(512) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', |
|
112 `message` varchar(512) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', |
|
113 `locked` tinyint(4) NOT NULL DEFAULT '0', |
|
114 `last_access` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP |
|
115 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
|
116 |
|
117 -- |
|
118 -- Daten für Tabelle `t_history` |
|
119 -- |
|
120 |
|
121 INSERT INTO `t_history` (`id`, `ticket_id`, `action_id`, `user_id`, `status_id`, `subject`, `message`, `locked`, `last_access`) VALUES |
|
122 (1, 1, 1, 1, 1, 'Systemeintrag', 'Systemeintrag', 0, '2015-11-09 09:37:15'), |
|
123 (2, 2, 2, 1, 2, 'Ticket-1', 'Beschreibung-1', 0, '2015-11-09 13:15:27'), |
|
124 (3, 3, 2, 1, 2, 'Ticket-2', 'Beschreibung-2', 0, '2015-11-09 13:15:27'), |
|
125 (4, 4, 2, 1, 2, 'Ticket-3', 'Beschreibung-3', 0, '2015-11-09 13:15:27'), |
|
126 (5, 5, 2, 1, 2, 'Ticket-4', 'Beschreibung-4', 0, '2015-11-09 13:15:27'), |
|
127 (6, 6, 2, 1, 2, 'Ticket-5', 'Beschreibung-5', 0, '2015-11-09 13:15:27'), |
|
128 (7, 7, 2, 1, 2, 'Ticket-6', 'Beschreibung-6', 0, '2015-11-09 13:15:27'), |
|
129 (8, 8, 2, 1, 2, 'Ticket-7', 'Beschreibung-7', 0, '2015-11-09 13:15:27'), |
|
130 (9, 9, 2, 1, 2, 'Ticket-8', 'Beschreibung-8', 0, '2015-11-09 13:15:27'), |
|
131 (10, 10, 2, 1, 2, 'Ticket-9', 'Beschreibung-9', 0, '2015-11-09 13:15:27'), |
|
132 (11, 11, 2, 1, 2, 'Ticket-10', 'Beschreibung-10', 0, '2015-11-09 13:15:27'); |
|
133 |
|
134 -- -------------------------------------------------------- |
|
135 |
|
136 -- |
|
137 -- Tabellenstruktur für Tabelle `t_status` |
|
138 -- |
|
139 |
|
140 CREATE TABLE IF NOT EXISTS `t_status` ( |
|
141 `id` bigint(20) unsigned NOT NULL, |
|
142 `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' |
|
143 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
|
144 |
|
145 -- |
|
146 -- Daten für Tabelle `t_status` |
|
147 -- |
|
148 |
|
149 INSERT INTO `t_status` (`id`, `name`) VALUES |
|
150 (1, 'System'), |
|
151 (2, 'offen'), |
|
152 (3, 'geschlossen'), |
|
153 (4, 'abgelehnt'), |
|
154 (5, 'korrigiert'), |
|
155 (6, 'nicht korrigierbar'); |
|
156 |
|
157 -- -------------------------------------------------------- |
|
158 |
|
159 -- |
|
160 -- Tabellenstruktur für Tabelle `t_text` |
|
161 -- |
|
162 |
|
163 CREATE TABLE IF NOT EXISTS `t_text` ( |
|
164 `id` bigint(20) unsigned NOT NULL, |
|
165 `user_id` bigint(20) unsigned NOT NULL DEFAULT '1', |
|
166 `headline` text COLLATE utf8_unicode_ci NOT NULL, |
|
167 `text` text COLLATE utf8_unicode_ci NOT NULL |
|
168 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
|
169 |
|
170 -- |
|
171 -- Daten für Tabelle `t_text` |
|
172 -- |
|
173 |
|
174 INSERT INTO `t_text` (`id`, `user_id`, `headline`, `text`) VALUES |
|
175 (1, 1, '', 'Systemdatensatz'), |
|
176 (2, 2, 'Testbox-Ticket System', 'Das ist ein simples Ticketsystem für dieses Unternehmen. Das Formatieren von HTML Text ist erst einmal deaktiviert und wird wohl auch niemals aktiviert.\r\n'); |
|
177 |
|
178 -- -------------------------------------------------------- |
|
179 |
|
180 -- |
|
181 -- Tabellenstruktur für Tabelle `t_ticket` |
|
182 -- |
|
183 |
|
184 CREATE TABLE IF NOT EXISTS `t_ticket` ( |
|
185 `id` bigint(20) unsigned NOT NULL, |
|
186 `user_id` bigint(20) unsigned NOT NULL DEFAULT '1', |
|
187 `status_id` bigint(20) unsigned NOT NULL DEFAULT '1', |
|
188 `subject` varchar(512) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', |
|
189 `message` varchar(512) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', |
|
190 `locked` tinyint(1) NOT NULL DEFAULT '0', |
|
191 `last_access` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP |
|
192 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
|
193 |
|
194 -- |
|
195 -- Daten für Tabelle `t_ticket` |
|
196 -- |
|
197 |
|
198 INSERT INTO `t_ticket` (`id`, `user_id`, `status_id`, `subject`, `message`, `locked`, `last_access`) VALUES |
|
199 (1, 1, 1, 'System', 'System', 0, '2015-11-02 15:37:14'), |
|
200 (2, 1, 2, 'Ticket-1', 'Beschreibung-1', 0, '2015-11-09 13:15:27'), |
|
201 (3, 1, 2, 'Ticket-2', 'Beschreibung-2', 0, '2015-11-09 13:15:27'), |
|
202 (4, 1, 2, 'Ticket-3', 'Beschreibung-3', 0, '2015-11-09 13:15:27'), |
|
203 (5, 1, 2, 'Ticket-4', 'Beschreibung-4', 0, '2015-11-09 13:15:27'), |
|
204 (6, 1, 2, 'Ticket-5', 'Beschreibung-5', 0, '2015-11-09 13:15:27'), |
|
205 (7, 1, 2, 'Ticket-6', 'Beschreibung-6', 0, '2015-11-09 13:15:27'), |
|
206 (8, 1, 2, 'Ticket-7', 'Beschreibung-7', 0, '2015-11-09 13:15:27'), |
|
207 (9, 1, 2, 'Ticket-8', 'Beschreibung-8', 0, '2015-11-09 13:15:27'), |
|
208 (10, 1, 2, 'Ticket-9', 'Beschreibung-9', 0, '2015-11-09 13:15:27'), |
|
209 (11, 1, 2, 'Ticket-10', 'Beschreibung-10', 0, '2015-11-09 13:15:27'); |
|
210 |
|
211 -- |
|
212 -- Trigger `t_ticket` |
|
213 -- |
|
214 DELIMITER $$ |
|
215 CREATE TRIGGER `t_ticket_after_delete` AFTER DELETE ON `t_ticket` |
|
216 FOR EACH ROW BEGIN |
|
217 call update_history(OLD.id, 4, OLD.user_id, OLD.status_id, OLD.subject, OLD.message, OLD.locked, OLD.last_access); |
|
218 END |
|
219 $$ |
|
220 DELIMITER ; |
|
221 DELIMITER $$ |
|
222 CREATE TRIGGER `t_ticket_after_insert` AFTER INSERT ON `t_ticket` |
|
223 FOR EACH ROW BEGIN |
|
224 call update_history(NEW.id, 2, NEW.user_id, NEW.status_id, NEW.subject, NEW.message, NEW.locked, NEW.last_access); |
|
225 END |
|
226 $$ |
|
227 DELIMITER ; |
|
228 DELIMITER $$ |
|
229 CREATE TRIGGER `t_ticket_after_update` AFTER UPDATE ON `t_ticket` |
|
230 FOR EACH ROW BEGIN |
|
231 call update_history(NEW.id, 3, NEW.user_id, NEW.status_id, NEW.subject, NEW.message, NEW.locked, NEW.last_access); |
|
232 END |
|
233 $$ |
|
234 DELIMITER ; |
|
235 |
41 |
236 -- -------------------------------------------------------- |
42 -- -------------------------------------------------------- |
237 |
43 |
238 -- |
44 -- |
239 -- Tabellenstruktur für Tabelle `t_user` |
45 -- Tabellenstruktur für Tabelle `t_user` |
251 -- |
57 -- |
252 -- Daten für Tabelle `t_user` |
58 -- Daten für Tabelle `t_user` |
253 -- |
59 -- |
254 |
60 |
255 INSERT INTO `t_user` (`id`, `group_id`, `username`, `password`, `firstname`, `lastname`) VALUES |
61 INSERT INTO `t_user` (`id`, `group_id`, `username`, `password`, `firstname`, `lastname`) VALUES |
256 (1, 1, 'system@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'System', 'Datensatz'), |
62 (1, 1, 'system@testbox.de', '115f89503138416a242f40fb7d7f338e', 'System', 'Datensatz'), |
257 (2, 2, 'broeker@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'Markus', 'Bröker'), |
63 (2, 2, 'broeker@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'Markus', 'Bröker'); |
258 (3, 3, 'manager1@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'Manager1', 'Testbox'), |
|
259 (4, 4, 'supporter1@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter1', 'Testbox'), |
|
260 (5, 4, 'supporter2@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter2', 'Testbox'), |
|
261 (6, 4, 'supporter3@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter3', 'Testbox'), |
|
262 (7, 4, 'supporter4@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter4', 'Testbox'), |
|
263 (8, 4, 'supporter5@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter5', 'Testbox'), |
|
264 (9, 4, 'supporter6@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter6', 'Testbox'), |
|
265 (10, 4, 'supporter7@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter7', 'Testbox'), |
|
266 (11, 4, 'supporter8@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter8', 'Testbox'), |
|
267 (12, 4, 'supporter9@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter9', 'Testbox'), |
|
268 (13, 4, 'supporter10@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter10', 'Testbox'); |
|
269 |
64 |
270 -- |
65 -- |
271 -- Indizes der exportierten Tabellen |
66 -- Indizes der exportierten Tabellen |
272 -- |
67 -- |
273 |
68 |
274 -- |
69 -- |
275 -- Indizes für die Tabelle `t_action` |
|
276 -- |
|
277 ALTER TABLE `t_action` |
|
278 ADD UNIQUE KEY `id` (`id`); |
|
279 |
|
280 -- |
|
281 -- Indizes für die Tabelle `t_group` |
70 -- Indizes für die Tabelle `t_group` |
282 -- |
71 -- |
283 ALTER TABLE `t_group` |
72 ALTER TABLE `t_group` |
284 ADD UNIQUE KEY `id` (`id`); |
73 ADD PRIMARY KEY (`id`); |
285 |
|
286 -- |
|
287 -- Indizes für die Tabelle `t_history` |
|
288 -- |
|
289 ALTER TABLE `t_history` |
|
290 ADD UNIQUE KEY `id` (`id`), |
|
291 ADD KEY `fk_history_ticked_id` (`ticket_id`), |
|
292 ADD KEY `fk_history_action_id` (`action_id`), |
|
293 ADD KEY `fk_history_user_id` (`user_id`), |
|
294 ADD KEY `fk_history_status_id` (`status_id`); |
|
295 |
|
296 -- |
|
297 -- Indizes für die Tabelle `t_status` |
|
298 -- |
|
299 ALTER TABLE `t_status` |
|
300 ADD UNIQUE KEY `id` (`id`); |
|
301 |
|
302 -- |
|
303 -- Indizes für die Tabelle `t_text` |
|
304 -- |
|
305 ALTER TABLE `t_text` |
|
306 ADD UNIQUE KEY `id` (`id`), |
|
307 ADD KEY `fk_t_user_text` (`user_id`); |
|
308 |
|
309 -- |
|
310 -- Indizes für die Tabelle `t_ticket` |
|
311 -- |
|
312 ALTER TABLE `t_ticket` |
|
313 ADD UNIQUE KEY `id` (`id`), |
|
314 ADD KEY `fk_t_status` (`status_id`), |
|
315 ADD KEY `fk_t_user` (`user_id`); |
|
316 |
74 |
317 -- |
75 -- |
318 -- Indizes für die Tabelle `t_user` |
76 -- Indizes für die Tabelle `t_user` |
319 -- |
77 -- |
320 ALTER TABLE `t_user` |
78 ALTER TABLE `t_user` |
321 ADD UNIQUE KEY `id` (`id`), |
79 ADD PRIMARY KEY (`id`), |
322 ADD UNIQUE KEY `co_username` (`username`), |
80 ADD UNIQUE KEY `co_username` (`username`), |
323 ADD KEY `fk_t_group` (`group_id`); |
81 ADD KEY `fk_t_group` (`group_id`); |
324 |
82 |
325 -- |
83 -- |
326 -- AUTO_INCREMENT für exportierte Tabellen |
84 -- AUTO_INCREMENT für exportierte Tabellen |
327 -- |
85 -- |
328 |
86 |
329 -- |
87 -- |
330 -- AUTO_INCREMENT für Tabelle `t_action` |
|
331 -- |
|
332 ALTER TABLE `t_action` |
|
333 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5; |
|
334 -- |
|
335 -- AUTO_INCREMENT für Tabelle `t_group` |
88 -- AUTO_INCREMENT für Tabelle `t_group` |
336 -- |
89 -- |
337 ALTER TABLE `t_group` |
90 ALTER TABLE `t_group` |
338 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6; |
91 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2; |
339 -- |
|
340 -- AUTO_INCREMENT für Tabelle `t_history` |
|
341 -- |
|
342 ALTER TABLE `t_history` |
|
343 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=12; |
|
344 -- |
|
345 -- AUTO_INCREMENT für Tabelle `t_status` |
|
346 -- |
|
347 ALTER TABLE `t_status` |
|
348 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=7; |
|
349 -- |
|
350 -- AUTO_INCREMENT für Tabelle `t_text` |
|
351 -- |
|
352 ALTER TABLE `t_text` |
|
353 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3; |
|
354 -- |
|
355 -- AUTO_INCREMENT für Tabelle `t_ticket` |
|
356 -- |
|
357 ALTER TABLE `t_ticket` |
|
358 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=12; |
|
359 -- |
92 -- |
360 -- AUTO_INCREMENT für Tabelle `t_user` |
93 -- AUTO_INCREMENT für Tabelle `t_user` |
361 -- |
94 -- |
362 ALTER TABLE `t_user` |
95 ALTER TABLE `t_user` |
363 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=14; |
96 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2; |
364 -- |
97 -- |
365 -- Constraints der exportierten Tabellen |
98 -- Constraints der exportierten Tabellen |
366 -- |
99 -- |
367 |
100 |
368 -- |
101 -- |
369 -- Constraints der Tabelle `t_history` |
|
370 -- |
|
371 ALTER TABLE `t_history` |
|
372 ADD CONSTRAINT `fk_history_action_id` FOREIGN KEY (`action_id`) REFERENCES `t_action` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, |
|
373 ADD CONSTRAINT `fk_history_status_id` FOREIGN KEY (`status_id`) REFERENCES `t_status` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, |
|
374 ADD CONSTRAINT `fk_history_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; |
|
375 |
|
376 -- |
|
377 -- Constraints der Tabelle `t_text` |
|
378 -- |
|
379 ALTER TABLE `t_text` |
|
380 ADD CONSTRAINT `fk_t_user_text` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; |
|
381 |
|
382 -- |
|
383 -- Constraints der Tabelle `t_ticket` |
|
384 -- |
|
385 ALTER TABLE `t_ticket` |
|
386 ADD CONSTRAINT `fk_t_status` FOREIGN KEY (`status_id`) REFERENCES `t_status` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, |
|
387 ADD CONSTRAINT `fk_t_user` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; |
|
388 |
|
389 -- |
|
390 -- Constraints der Tabelle `t_user` |
102 -- Constraints der Tabelle `t_user` |
391 -- |
103 -- |
392 ALTER TABLE `t_user` |
104 ALTER TABLE `t_user` |
393 ADD CONSTRAINT `fk_t_group` FOREIGN KEY (`group_id`) REFERENCES `t_group` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; |
105 ADD CONSTRAINT `fk_t_group` FOREIGN KEY (`group_id`) REFERENCES `t_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; |
394 |
106 |
395 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
107 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
396 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
108 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
397 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
109 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |