sql/init.sql
changeset 19 95ee596c03c5
parent 6 6c23ee543e0e
--- 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 */;