-- phpMyAdmin SQL Dump

-- version 4.4.14

-- 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

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

/*!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 */;

--

-- Datenbank: `ticketsystem`

--

DELIMITER $$

--

-- Prozeduren

--

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');

-- --------------------------------------------------------

--

-- Tabellenstruktur für Tabelle `t_group`

--

CREATE TABLE IF NOT EXISTS `t_group` (

  `id` bigint(20) unsigned NOT NULL,

  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ''

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

-- Daten für Tabelle `t_group`

--

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'),

-- --------------------------------------------------------

--

-- 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 ;

-- --------------------------------------------------------

--

-- Tabellenstruktur für Tabelle `t_user`

--

CREATE TABLE IF NOT EXISTS `t_user` (

  `id` bigint(20) unsigned NOT NULL,

  `group_id` bigint(20) unsigned NOT NULL DEFAULT '1',

  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

  `firstname` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

  `lastname` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ''

) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

-- Daten für Tabelle `t_user`

--

INSERT INTO `t_user` (`id`, `group_id`, `username`, `password`, `firstname`, `lastname`) VALUES

--

-- 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`);

--

-- Indizes für die Tabelle `t_user`

--

ALTER TABLE `t_user`

  ADD UNIQUE KEY `id` (`id`),

  ADD UNIQUE KEY `co_username` (`username`),

  ADD KEY `fk_t_group` (`group_id`);

--

-- AUTO_INCREMENT für exportierte Tabellen

--

--

-- 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;

--

-- AUTO_INCREMENT für Tabelle `t_user`

--

ALTER TABLE `t_user`

  MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=14;

--

-- 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;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;