|
1 -- phpMyAdmin SQL Dump |
|
2 -- version 4.4.14 |
|
3 -- http://www.phpmyadmin.net |
|
4 -- |
|
5 -- Host: 127.0.0.1 |
|
6 -- Erstellungszeit: 09. Nov 2015 um 14:16 |
|
7 -- Server-Version: 5.6.26 |
|
8 -- PHP-Version: 5.6.12 |
|
9 |
|
10 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; |
|
11 SET time_zone = "+00:00"; |
|
12 |
|
13 |
|
14 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
|
15 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
|
16 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
|
17 /*!40101 SET NAMES utf8mb4 */; |
|
18 |
|
19 -- |
|
20 -- Datenbank: `ticketsystem` |
|
21 -- |
|
22 |
|
23 DELIMITER $$ |
|
24 -- |
|
25 -- Prozeduren |
|
26 -- |
|
27 CREATE DEFINER=`root`@`localhost` PROCEDURE `update_history`(IN `tid` BIGINT, IN `aid` BIGINT, IN `uid` BIGINT, IN `sid` BIGINT, IN `s_text` VARCHAR(512), IN `m_text` VARCHAR(512), IN `si_locked` SMALLINT, IN `ts_last_access` TIMESTAMP) |
|
28 COMMENT 'Simpler Update Trigger für die History-Log-Tabelle' |
|
29 BEGIN |
|
30 INSERT INTO |
|
31 t_history( |
|
32 id, |
|
33 ticket_id, |
|
34 action_id, |
|
35 user_id, |
|
36 status_id, |
|
37 subject, |
|
38 message, |
|
39 locked, |
|
40 last_access |
|
41 ) VALUES( |
|
42 NULL, |
|
43 tid, |
|
44 aid, |
|
45 uid, |
|
46 sid, |
|
47 s_text, |
|
48 m_text, |
|
49 si_locked, |
|
50 ts_last_access |
|
51 ); |
|
52 END$$ |
|
53 |
|
54 DELIMITER ; |
|
55 |
|
56 -- -------------------------------------------------------- |
|
57 |
|
58 -- |
|
59 -- Tabellenstruktur für Tabelle `t_action` |
|
60 -- |
|
61 |
|
62 CREATE TABLE IF NOT EXISTS `t_action` ( |
|
63 `id` bigint(20) unsigned NOT NULL, |
|
64 `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' |
|
65 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
|
66 |
|
67 -- |
|
68 -- Daten für Tabelle `t_action` |
|
69 -- |
|
70 |
|
71 INSERT INTO `t_action` (`id`, `name`) VALUES |
|
72 (1, 'System'), |
|
73 (2, 'ANGELEGT'), |
|
74 (3, 'GEÄNDERT'), |
|
75 (4, 'GELÖSCHT'); |
|
76 |
|
77 -- -------------------------------------------------------- |
|
78 |
|
79 -- |
|
80 -- Tabellenstruktur für Tabelle `t_group` |
|
81 -- |
|
82 |
|
83 CREATE TABLE IF NOT EXISTS `t_group` ( |
|
84 `id` bigint(20) unsigned NOT NULL, |
|
85 `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' |
|
86 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
|
87 |
|
88 -- |
|
89 -- Daten für Tabelle `t_group` |
|
90 -- |
|
91 |
|
92 INSERT INTO `t_group` (`id`, `name`) VALUES |
|
93 (1, 'System'), |
|
94 (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, 'Onscreen-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 |
|
236 -- -------------------------------------------------------- |
|
237 |
|
238 -- |
|
239 -- Tabellenstruktur für Tabelle `t_user` |
|
240 -- |
|
241 |
|
242 CREATE TABLE IF NOT EXISTS `t_user` ( |
|
243 `id` bigint(20) unsigned NOT NULL, |
|
244 `group_id` bigint(20) unsigned NOT NULL DEFAULT '1', |
|
245 `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', |
|
246 `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', |
|
247 `firstname` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', |
|
248 `lastname` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' |
|
249 ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
|
250 |
|
251 -- |
|
252 -- Daten für Tabelle `t_user` |
|
253 -- |
|
254 |
|
255 INSERT INTO `t_user` (`id`, `group_id`, `username`, `password`, `firstname`, `lastname`) VALUES |
|
256 (1, 1, 'system@onscreen.net', '098f6bcd4621d373cade4e832627b4f6', 'System', 'Datensatz'), |
|
257 (2, 2, 'broeker@onscreen.net', '098f6bcd4621d373cade4e832627b4f6', 'Markus', 'Bröker'), |
|
258 (3, 3, 'manager1@onscreen.net', '098f6bcd4621d373cade4e832627b4f6', 'Manager1', 'Onscreen'), |
|
259 (4, 4, 'supporter1@onscreen.net', '098f6bcd4621d373cade4e832627b4f6', 'supporter1', 'Onscreen'), |
|
260 (5, 4, 'supporter2@onscreen.net', '098f6bcd4621d373cade4e832627b4f6', 'supporter2', 'Onscreen'), |
|
261 (6, 4, 'supporter3@onscreen.net', '098f6bcd4621d373cade4e832627b4f6', 'supporter3', 'Onscreen'), |
|
262 (7, 4, 'supporter4@onscreen.net', '098f6bcd4621d373cade4e832627b4f6', 'supporter4', 'Onscreen'), |
|
263 (8, 4, 'supporter5@onscreen.net', '098f6bcd4621d373cade4e832627b4f6', 'supporter5', 'Onscreen'), |
|
264 (9, 4, 'supporter6@onscreen.net', '098f6bcd4621d373cade4e832627b4f6', 'supporter6', 'Onscreen'), |
|
265 (10, 4, 'supporter7@onscreen.net', '098f6bcd4621d373cade4e832627b4f6', 'supporter7', 'Onscreen'), |
|
266 (11, 4, 'supporter8@onscreen.net', '098f6bcd4621d373cade4e832627b4f6', 'supporter8', 'Onscreen'), |
|
267 (12, 4, 'supporter9@onscreen.net', '098f6bcd4621d373cade4e832627b4f6', 'supporter9', 'Onscreen'), |
|
268 (13, 4, 'supporter10@onscreen.net', '098f6bcd4621d373cade4e832627b4f6', 'supporter10', 'Onscreen'); |
|
269 |
|
270 -- |
|
271 -- Indizes der exportierten Tabellen |
|
272 -- |
|
273 |
|
274 -- |
|
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` |
|
282 -- |
|
283 ALTER TABLE `t_group` |
|
284 ADD UNIQUE KEY `id` (`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 |
|
317 -- |
|
318 -- Indizes für die Tabelle `t_user` |
|
319 -- |
|
320 ALTER TABLE `t_user` |
|
321 ADD UNIQUE KEY `id` (`id`), |
|
322 ADD UNIQUE KEY `co_username` (`username`), |
|
323 ADD KEY `fk_t_group` (`group_id`); |
|
324 |
|
325 -- |
|
326 -- AUTO_INCREMENT für exportierte Tabellen |
|
327 -- |
|
328 |
|
329 -- |
|
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` |
|
336 -- |
|
337 ALTER TABLE `t_group` |
|
338 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6; |
|
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 -- |
|
360 -- AUTO_INCREMENT für Tabelle `t_user` |
|
361 -- |
|
362 ALTER TABLE `t_user` |
|
363 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=14; |
|
364 -- |
|
365 -- Constraints der exportierten Tabellen |
|
366 -- |
|
367 |
|
368 -- |
|
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` |
|
391 -- |
|
392 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; |
|
394 |
|
395 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
|
396 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
|
397 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |