diff --git a/sql/init.sql b/sql/init.sql --- a/sql/init.sql +++ b/sql/init.sql @@ -1,11 +1,11 @@ -- phpMyAdmin SQL Dump --- version 4.4.14 +-- version 4.4.6 -- http://www.phpmyadmin.net -- -- Host: 127.0.0.1 --- Erstellungszeit: 09. Nov 2015 um 14:16 --- Server-Version: 5.6.26 --- PHP-Version: 5.6.12 +-- Erstellungszeit: 13. Nov 2015 um 18:50 +-- Server-Version: 5.6.24 +-- PHP-Version: 5.6.8 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; @@ -14,65 +14,11 @@ /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8mb4 */; +/*!40101 SET NAMES utf8 */; -- --- Datenbank: `ticketsystem` --- - -DELIMITER $$ --- --- Prozeduren +-- Datenbank: `bfw` -- -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) - COMMENT 'Simpler Update Trigger für die History-Log-Tabelle' -BEGIN -INSERT INTO - t_history( - id, - ticket_id, - action_id, - user_id, - status_id, - subject, - message, - locked, - last_access - ) VALUES( - NULL, - tid, - aid, - uid, - sid, - s_text, - m_text, - si_locked, - ts_last_access - ); -END$$ - -DELIMITER ; - --- -------------------------------------------------------- - --- --- Tabellenstruktur für Tabelle `t_action` --- - -CREATE TABLE IF NOT EXISTS `t_action` ( - `id` bigint(20) unsigned NOT NULL, - `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' -) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; - --- --- Daten für Tabelle `t_action` --- - -INSERT INTO `t_action` (`id`, `name`) VALUES -(1, 'System'), -(2, 'ANGELEGT'), -(3, 'GEÄNDERT'), -(4, 'GELÖSCHT'); -- -------------------------------------------------------- @@ -91,147 +37,7 @@ INSERT INTO `t_group` (`id`, `name`) VALUES (1, 'System'), -(2, 'Admin'), -(3, 'Manager'), -(4, 'Supporter'), -(5, 'Gast'); - --- -------------------------------------------------------- - --- --- Tabellenstruktur für Tabelle `t_history` --- - -CREATE TABLE IF NOT EXISTS `t_history` ( - `id` bigint(20) unsigned NOT NULL, - `ticket_id` bigint(11) unsigned NOT NULL DEFAULT '1', - `action_id` bigint(20) unsigned NOT NULL DEFAULT '1', - `user_id` bigint(20) unsigned NOT NULL DEFAULT '1', - `status_id` bigint(20) unsigned NOT NULL DEFAULT '1', - `subject` varchar(512) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', - `message` varchar(512) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', - `locked` tinyint(4) NOT NULL DEFAULT '0', - `last_access` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; - --- --- Daten für Tabelle `t_history` --- - -INSERT INTO `t_history` (`id`, `ticket_id`, `action_id`, `user_id`, `status_id`, `subject`, `message`, `locked`, `last_access`) VALUES -(1, 1, 1, 1, 1, 'Systemeintrag', 'Systemeintrag', 0, '2015-11-09 09:37:15'), -(2, 2, 2, 1, 2, 'Ticket-1', 'Beschreibung-1', 0, '2015-11-09 13:15:27'), -(3, 3, 2, 1, 2, 'Ticket-2', 'Beschreibung-2', 0, '2015-11-09 13:15:27'), -(4, 4, 2, 1, 2, 'Ticket-3', 'Beschreibung-3', 0, '2015-11-09 13:15:27'), -(5, 5, 2, 1, 2, 'Ticket-4', 'Beschreibung-4', 0, '2015-11-09 13:15:27'), -(6, 6, 2, 1, 2, 'Ticket-5', 'Beschreibung-5', 0, '2015-11-09 13:15:27'), -(7, 7, 2, 1, 2, 'Ticket-6', 'Beschreibung-6', 0, '2015-11-09 13:15:27'), -(8, 8, 2, 1, 2, 'Ticket-7', 'Beschreibung-7', 0, '2015-11-09 13:15:27'), -(9, 9, 2, 1, 2, 'Ticket-8', 'Beschreibung-8', 0, '2015-11-09 13:15:27'), -(10, 10, 2, 1, 2, 'Ticket-9', 'Beschreibung-9', 0, '2015-11-09 13:15:27'), -(11, 11, 2, 1, 2, 'Ticket-10', 'Beschreibung-10', 0, '2015-11-09 13:15:27'); - --- -------------------------------------------------------- - --- --- Tabellenstruktur für Tabelle `t_status` --- - -CREATE TABLE IF NOT EXISTS `t_status` ( - `id` bigint(20) unsigned NOT NULL, - `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' -) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; - --- --- Daten für Tabelle `t_status` --- - -INSERT INTO `t_status` (`id`, `name`) VALUES -(1, 'System'), -(2, 'offen'), -(3, 'geschlossen'), -(4, 'abgelehnt'), -(5, 'korrigiert'), -(6, 'nicht korrigierbar'); - --- -------------------------------------------------------- - --- --- Tabellenstruktur für Tabelle `t_text` --- - -CREATE TABLE IF NOT EXISTS `t_text` ( - `id` bigint(20) unsigned NOT NULL, - `user_id` bigint(20) unsigned NOT NULL DEFAULT '1', - `headline` text COLLATE utf8_unicode_ci NOT NULL, - `text` text COLLATE utf8_unicode_ci NOT NULL -) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; - --- --- Daten für Tabelle `t_text` --- - -INSERT INTO `t_text` (`id`, `user_id`, `headline`, `text`) VALUES -(1, 1, '', 'Systemdatensatz'), -(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'); - --- -------------------------------------------------------- - --- --- Tabellenstruktur für Tabelle `t_ticket` --- - -CREATE TABLE IF NOT EXISTS `t_ticket` ( - `id` bigint(20) unsigned NOT NULL, - `user_id` bigint(20) unsigned NOT NULL DEFAULT '1', - `status_id` bigint(20) unsigned NOT NULL DEFAULT '1', - `subject` varchar(512) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', - `message` varchar(512) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', - `locked` tinyint(1) NOT NULL DEFAULT '0', - `last_access` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; - --- --- Daten für Tabelle `t_ticket` --- - -INSERT INTO `t_ticket` (`id`, `user_id`, `status_id`, `subject`, `message`, `locked`, `last_access`) VALUES -(1, 1, 1, 'System', 'System', 0, '2015-11-02 15:37:14'), -(2, 1, 2, 'Ticket-1', 'Beschreibung-1', 0, '2015-11-09 13:15:27'), -(3, 1, 2, 'Ticket-2', 'Beschreibung-2', 0, '2015-11-09 13:15:27'), -(4, 1, 2, 'Ticket-3', 'Beschreibung-3', 0, '2015-11-09 13:15:27'), -(5, 1, 2, 'Ticket-4', 'Beschreibung-4', 0, '2015-11-09 13:15:27'), -(6, 1, 2, 'Ticket-5', 'Beschreibung-5', 0, '2015-11-09 13:15:27'), -(7, 1, 2, 'Ticket-6', 'Beschreibung-6', 0, '2015-11-09 13:15:27'), -(8, 1, 2, 'Ticket-7', 'Beschreibung-7', 0, '2015-11-09 13:15:27'), -(9, 1, 2, 'Ticket-8', 'Beschreibung-8', 0, '2015-11-09 13:15:27'), -(10, 1, 2, 'Ticket-9', 'Beschreibung-9', 0, '2015-11-09 13:15:27'), -(11, 1, 2, 'Ticket-10', 'Beschreibung-10', 0, '2015-11-09 13:15:27'); - --- --- Trigger `t_ticket` --- -DELIMITER $$ -CREATE TRIGGER `t_ticket_after_delete` AFTER DELETE ON `t_ticket` - FOR EACH ROW BEGIN - call update_history(OLD.id, 4, OLD.user_id, OLD.status_id, OLD.subject, OLD.message, OLD.locked, OLD.last_access); -END -$$ -DELIMITER ; -DELIMITER $$ -CREATE TRIGGER `t_ticket_after_insert` AFTER INSERT ON `t_ticket` - FOR EACH ROW BEGIN - call update_history(NEW.id, 2, NEW.user_id, NEW.status_id, NEW.subject, NEW.message, NEW.locked, NEW.last_access); -END -$$ -DELIMITER ; -DELIMITER $$ -CREATE TRIGGER `t_ticket_after_update` AFTER UPDATE ON `t_ticket` - FOR EACH ROW BEGIN - call update_history(NEW.id, 3, NEW.user_id, NEW.status_id, NEW.subject, NEW.message, NEW.locked, NEW.last_access); -END -$$ -DELIMITER ; +(2, 'Admin'); -- -------------------------------------------------------- @@ -253,72 +59,24 @@ -- INSERT INTO `t_user` (`id`, `group_id`, `username`, `password`, `firstname`, `lastname`) VALUES -(1, 1, 'system@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'System', 'Datensatz'), -(2, 2, 'broeker@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'Markus', 'Bröker'), -(3, 3, 'manager1@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'Manager1', 'Testbox'), -(4, 4, 'supporter1@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter1', 'Testbox'), -(5, 4, 'supporter2@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter2', 'Testbox'), -(6, 4, 'supporter3@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter3', 'Testbox'), -(7, 4, 'supporter4@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter4', 'Testbox'), -(8, 4, 'supporter5@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter5', 'Testbox'), -(9, 4, 'supporter6@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter6', 'Testbox'), -(10, 4, 'supporter7@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter7', 'Testbox'), -(11, 4, 'supporter8@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter8', 'Testbox'), -(12, 4, 'supporter9@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter9', 'Testbox'), -(13, 4, 'supporter10@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'supporter10', 'Testbox'); +(1, 1, 'system@testbox.de', '115f89503138416a242f40fb7d7f338e', 'System', 'Datensatz'), +(2, 2, 'broeker@testbox.de', '098f6bcd4621d373cade4e832627b4f6', 'Markus', 'Bröker'); -- -- Indizes der exportierten Tabellen -- -- --- Indizes für die Tabelle `t_action` --- -ALTER TABLE `t_action` - ADD UNIQUE KEY `id` (`id`); - --- -- Indizes für die Tabelle `t_group` -- ALTER TABLE `t_group` - ADD UNIQUE KEY `id` (`id`); - --- --- Indizes für die Tabelle `t_history` --- -ALTER TABLE `t_history` - ADD UNIQUE KEY `id` (`id`), - ADD KEY `fk_history_ticked_id` (`ticket_id`), - ADD KEY `fk_history_action_id` (`action_id`), - ADD KEY `fk_history_user_id` (`user_id`), - ADD KEY `fk_history_status_id` (`status_id`); - --- --- Indizes für die Tabelle `t_status` --- -ALTER TABLE `t_status` - ADD UNIQUE KEY `id` (`id`); - --- --- Indizes für die Tabelle `t_text` --- -ALTER TABLE `t_text` - ADD UNIQUE KEY `id` (`id`), - ADD KEY `fk_t_user_text` (`user_id`); - --- --- Indizes für die Tabelle `t_ticket` --- -ALTER TABLE `t_ticket` - ADD UNIQUE KEY `id` (`id`), - ADD KEY `fk_t_status` (`status_id`), - ADD KEY `fk_t_user` (`user_id`); + ADD PRIMARY KEY (`id`); -- -- Indizes für die Tabelle `t_user` -- ALTER TABLE `t_user` - ADD UNIQUE KEY `id` (`id`), + ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `co_username` (`username`), ADD KEY `fk_t_group` (`group_id`); @@ -327,70 +85,24 @@ -- -- --- AUTO_INCREMENT für Tabelle `t_action` --- -ALTER TABLE `t_action` - MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5; --- -- AUTO_INCREMENT für Tabelle `t_group` -- ALTER TABLE `t_group` - MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6; --- --- AUTO_INCREMENT für Tabelle `t_history` --- -ALTER TABLE `t_history` - MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=12; --- --- AUTO_INCREMENT für Tabelle `t_status` --- -ALTER TABLE `t_status` - MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=7; --- --- AUTO_INCREMENT für Tabelle `t_text` --- -ALTER TABLE `t_text` - MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3; --- --- AUTO_INCREMENT für Tabelle `t_ticket` --- -ALTER TABLE `t_ticket` - MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=12; + MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2; -- -- AUTO_INCREMENT für Tabelle `t_user` -- ALTER TABLE `t_user` - MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=14; + MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2; -- -- Constraints der exportierten Tabellen -- -- --- Constraints der Tabelle `t_history` --- -ALTER TABLE `t_history` - ADD CONSTRAINT `fk_history_action_id` FOREIGN KEY (`action_id`) REFERENCES `t_action` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, - ADD CONSTRAINT `fk_history_status_id` FOREIGN KEY (`status_id`) REFERENCES `t_status` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, - ADD CONSTRAINT `fk_history_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; - --- --- Constraints der Tabelle `t_text` --- -ALTER TABLE `t_text` - ADD CONSTRAINT `fk_t_user_text` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; - --- --- Constraints der Tabelle `t_ticket` --- -ALTER TABLE `t_ticket` - ADD CONSTRAINT `fk_t_status` FOREIGN KEY (`status_id`) REFERENCES `t_status` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, - ADD CONSTRAINT `fk_t_user` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; - --- -- Constraints der Tabelle `t_user` -- ALTER TABLE `t_user` - ADD CONSTRAINT `fk_t_group` FOREIGN KEY (`group_id`) REFERENCES `t_group` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; + ADD CONSTRAINT `fk_t_group` FOREIGN KEY (`group_id`) REFERENCES `t_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;