From 031fc3057c46245cd62fc39f2d11ee3f0a393e06 Mon Sep 17 00:00:00 2001 From: msquare Date: Tue, 27 Sep 2016 17:49:13 +0200 Subject: rename settings to event config and implement mvc pattern on it --- db/install.sql | 495 ++++++++++++++++++++++++++++++++++++++++++--------------- db/update.sql | 32 ---- 2 files changed, 366 insertions(+), 161 deletions(-) (limited to 'db') diff --git a/db/install.sql b/db/install.sql index f1c0d098..0222dc08 100644 --- a/db/install.sql +++ b/db/install.sql @@ -1,16 +1,19 @@ -- phpMyAdmin SQL Dump --- version 4.0.4.1 +-- version 4.5.2 -- http://www.phpmyadmin.net -- -- Host: localhost --- Erstellungszeit: 25. Aug 2015 um 15:29 --- Server Version: 5.6.12 --- PHP-Version: 5.5.3 +-- Erstellungszeit: 27. Sep 2016 um 17:48 +-- Server-Version: 10.1.10-MariaDB +-- PHP-Version: 7.0.4 -SET FOREIGN_KEY_CHECKS=0; SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; +-- +-- Datenbank: `engelsystem` +-- + -- -------------------------------------------------------- -- @@ -18,14 +21,29 @@ SET time_zone = "+00:00"; -- DROP TABLE IF EXISTS `AngelTypes`; -CREATE TABLE IF NOT EXISTS `AngelTypes` ( - `id` int(11) NOT NULL AUTO_INCREMENT, +CREATE TABLE `AngelTypes` ( + `id` int(11) NOT NULL, `name` varchar(50) NOT NULL DEFAULT '', `restricted` int(1) NOT NULL, `description` text NOT NULL, - PRIMARY KEY (`id`), - UNIQUE KEY `Name` (`name`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; + `requires_driver_license` tinyint(1) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -------------------------------------------------------- + +-- +-- Tabellenstruktur für Tabelle `EventConfig` +-- + +DROP TABLE IF EXISTS `EventConfig`; +CREATE TABLE `EventConfig` ( + `event_name` varchar(255) DEFAULT NULL, + `buildup_start_date` int(11) DEFAULT NULL, + `event_start_date` int(11) DEFAULT NULL, + `event_end_date` int(11) DEFAULT NULL, + `teardown_end_date` int(11) DEFAULT NULL, + `event_welcome_msg` varchar(255) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -34,14 +52,11 @@ CREATE TABLE IF NOT EXISTS `AngelTypes` ( -- DROP TABLE IF EXISTS `GroupPrivileges`; -CREATE TABLE IF NOT EXISTS `GroupPrivileges` ( - `id` int(11) NOT NULL AUTO_INCREMENT, +CREATE TABLE `GroupPrivileges` ( + `id` int(11) NOT NULL, `group_id` int(11) NOT NULL, - `privilege_id` int(11) NOT NULL, - PRIMARY KEY (`id`), - KEY `group_id` (`group_id`,`privilege_id`), - KEY `privilege_id` (`privilege_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=259 ; + `privilege_id` int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Daten für Tabelle `GroupPrivileges` @@ -74,6 +89,7 @@ INSERT INTO `GroupPrivileges` (`id`, `group_id`, `privilege_id`) VALUES (235, -4, 27), (239, -4, 28), (236, -4, 32), +(218, -4, 39), (258, -3, 31), (247, -2, 3), (246, -2, 4), @@ -100,10 +116,9 @@ INSERT INTO `GroupPrivileges` (`id`, `group_id`, `privilege_id`) VALUES -- DROP TABLE IF EXISTS `Groups`; -CREATE TABLE IF NOT EXISTS `Groups` ( +CREATE TABLE `Groups` ( `Name` varchar(35) NOT NULL, - `UID` int(11) NOT NULL, - PRIMARY KEY (`UID`) + `UID` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -126,14 +141,12 @@ INSERT INTO `Groups` (`Name`, `UID`) VALUES -- DROP TABLE IF EXISTS `LogEntries`; -CREATE TABLE IF NOT EXISTS `LogEntries` ( - `id` int(11) NOT NULL AUTO_INCREMENT, +CREATE TABLE `LogEntries` ( + `id` int(11) NOT NULL, `timestamp` int(11) NOT NULL, `nick` text NOT NULL, - `message` text NOT NULL, - PRIMARY KEY (`id`), - KEY `timestamp` (`timestamp`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; + `message` text NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -142,18 +155,14 @@ CREATE TABLE IF NOT EXISTS `LogEntries` ( -- DROP TABLE IF EXISTS `Messages`; -CREATE TABLE IF NOT EXISTS `Messages` ( - `id` int(11) NOT NULL AUTO_INCREMENT, +CREATE TABLE `Messages` ( + `id` int(11) NOT NULL, `Datum` int(11) NOT NULL, `SUID` int(11) NOT NULL DEFAULT '0', `RUID` int(11) NOT NULL DEFAULT '0', `isRead` char(1) NOT NULL DEFAULT 'N', - `Text` text NOT NULL, - PRIMARY KEY (`id`), - KEY `Datum` (`Datum`), - KEY `SUID` (`SUID`), - KEY `RUID` (`RUID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Fuers interen Communikationssystem' AUTO_INCREMENT=1 ; + `Text` text NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Fuers interen Communikationssystem'; -- -------------------------------------------------------- @@ -162,17 +171,13 @@ CREATE TABLE IF NOT EXISTS `Messages` ( -- DROP TABLE IF EXISTS `NeededAngelTypes`; -CREATE TABLE IF NOT EXISTS `NeededAngelTypes` ( - `id` int(11) NOT NULL AUTO_INCREMENT, +CREATE TABLE `NeededAngelTypes` ( + `id` int(11) NOT NULL, `room_id` int(11) DEFAULT NULL, `shift_id` int(11) DEFAULT NULL, `angel_type_id` int(11) NOT NULL, - `count` int(11) NOT NULL, - PRIMARY KEY (`id`), - KEY `room_id` (`room_id`,`angel_type_id`), - KEY `shift_id` (`shift_id`), - KEY `angel_type_id` (`angel_type_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; + `count` int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -181,16 +186,14 @@ CREATE TABLE IF NOT EXISTS `NeededAngelTypes` ( -- DROP TABLE IF EXISTS `News`; -CREATE TABLE IF NOT EXISTS `News` ( - `ID` int(11) NOT NULL AUTO_INCREMENT, +CREATE TABLE `News` ( + `ID` int(11) NOT NULL, `Datum` int(11) NOT NULL, `Betreff` varchar(150) NOT NULL DEFAULT '', `Text` text NOT NULL, `UID` int(11) NOT NULL DEFAULT '0', - `Treffen` tinyint(4) NOT NULL DEFAULT '0', - PRIMARY KEY (`ID`), - KEY `UID` (`UID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; + `Treffen` tinyint(4) NOT NULL DEFAULT '0' +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -199,16 +202,13 @@ CREATE TABLE IF NOT EXISTS `News` ( -- DROP TABLE IF EXISTS `NewsComments`; -CREATE TABLE IF NOT EXISTS `NewsComments` ( - `ID` bigint(11) NOT NULL AUTO_INCREMENT, +CREATE TABLE `NewsComments` ( + `ID` bigint(11) NOT NULL, `Refid` int(11) NOT NULL DEFAULT '0', `Datum` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `Text` text NOT NULL, - `UID` int(11) NOT NULL DEFAULT '0', - PRIMARY KEY (`ID`), - KEY `Refid` (`Refid`), - KEY `UID` (`UID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; + `UID` int(11) NOT NULL DEFAULT '0' +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -217,13 +217,11 @@ CREATE TABLE IF NOT EXISTS `NewsComments` ( -- DROP TABLE IF EXISTS `Privileges`; -CREATE TABLE IF NOT EXISTS `Privileges` ( - `id` int(11) NOT NULL AUTO_INCREMENT, +CREATE TABLE `Privileges` ( + `id` int(11) NOT NULL, `name` varchar(128) NOT NULL, - `desc` varchar(1024) NOT NULL, - PRIMARY KEY (`id`), - UNIQUE KEY `name` (`name`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=39 ; + `desc` varchar(1024) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Daten für Tabelle `Privileges` @@ -266,7 +264,8 @@ INSERT INTO `Privileges` (`id`, `name`, `desc`) VALUES (35, 'shifts_json_export', 'Export shifts in JSON format'), (36, 'angeltypes', 'View angeltypes'), (37, 'user_angeltypes', 'Join angeltypes.'), -(38, 'shifttypes', 'Administrate shift types'); +(38, 'shifttypes', 'Administrate shift types'), +(39, 'admin_event_config', 'Allow editing event config'); -- -------------------------------------------------------- @@ -275,16 +274,13 @@ INSERT INTO `Privileges` (`id`, `name`, `desc`) VALUES -- DROP TABLE IF EXISTS `Questions`; -CREATE TABLE IF NOT EXISTS `Questions` ( - `QID` bigint(20) NOT NULL AUTO_INCREMENT, +CREATE TABLE `Questions` ( + `QID` bigint(20) NOT NULL, `UID` int(11) NOT NULL DEFAULT '0', `Question` text NOT NULL, `AID` int(11) DEFAULT NULL, - `Answer` text, - PRIMARY KEY (`QID`), - KEY `UID` (`UID`), - KEY `AID` (`AID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Fragen und Antworten' AUTO_INCREMENT=1 ; + `Answer` text +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Fragen und Antworten'; -- -------------------------------------------------------- @@ -293,16 +289,21 @@ CREATE TABLE IF NOT EXISTS `Questions` ( -- DROP TABLE IF EXISTS `Room`; -CREATE TABLE IF NOT EXISTS `Room` ( - `RID` int(11) NOT NULL AUTO_INCREMENT, +CREATE TABLE `Room` ( + `RID` int(11) NOT NULL, `Name` varchar(35) NOT NULL DEFAULT '', `Man` text, `FromPentabarf` char(1) NOT NULL DEFAULT 'N', `show` char(1) NOT NULL DEFAULT 'Y', - `Number` int(11) DEFAULT NULL, - PRIMARY KEY (`RID`), - UNIQUE KEY `Name` (`Name`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ; + `Number` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Daten für Tabelle `Room` +-- + +INSERT INTO `Room` (`RID`, `Name`, `Man`, `FromPentabarf`, `show`, `Number`) VALUES +(1, 'Testraum', NULL, '', 'Y', 0); -- -------------------------------------------------------- @@ -311,20 +312,15 @@ CREATE TABLE IF NOT EXISTS `Room` ( -- DROP TABLE IF EXISTS `ShiftEntry`; -CREATE TABLE IF NOT EXISTS `ShiftEntry` ( - `id` int(11) NOT NULL AUTO_INCREMENT, +CREATE TABLE `ShiftEntry` ( + `id` int(11) NOT NULL, `SID` int(11) NOT NULL DEFAULT '0', `TID` int(11) NOT NULL DEFAULT '0', `UID` int(11) NOT NULL DEFAULT '0', `Comment` text, `freeload_comment` text, - `freeloaded` tinyint(1) NOT NULL, - PRIMARY KEY (`id`), - KEY `TID` (`TID`), - KEY `UID` (`UID`), - KEY `SID` (`SID`,`TID`), - KEY `freeloaded` (`freeloaded`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; + `freeloaded` tinyint(1) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -333,8 +329,8 @@ CREATE TABLE IF NOT EXISTS `ShiftEntry` ( -- DROP TABLE IF EXISTS `Shifts`; -CREATE TABLE IF NOT EXISTS `Shifts` ( - `SID` int(11) NOT NULL AUTO_INCREMENT, +CREATE TABLE `Shifts` ( + `SID` int(11) NOT NULL, `title` text, `shifttype_id` int(11) NOT NULL, `start` int(11) NOT NULL, @@ -345,14 +341,8 @@ CREATE TABLE IF NOT EXISTS `Shifts` ( `created_by_user_id` int(11) DEFAULT NULL, `created_at_timestamp` int(11) NOT NULL, `edited_by_user_id` int(11) DEFAULT NULL, - `edited_at_timestamp` int(11) NOT NULL, - PRIMARY KEY (`SID`), - UNIQUE KEY `PSID` (`PSID`), - KEY `RID` (`RID`), - KEY `shifttype_id` (`shifttype_id`), - KEY `created_by_user_id` (`created_by_user_id`), - KEY `edited_by_user_id` (`edited_by_user_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=133 ; + `edited_at_timestamp` int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -361,14 +351,12 @@ CREATE TABLE IF NOT EXISTS `Shifts` ( -- DROP TABLE IF EXISTS `ShiftTypes`; -CREATE TABLE IF NOT EXISTS `ShiftTypes` ( - `id` int(11) NOT NULL AUTO_INCREMENT, +CREATE TABLE `ShiftTypes` ( + `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `angeltype_id` int(11) DEFAULT NULL, - `description` text NOT NULL, - PRIMARY KEY (`id`), - KEY `angeltype_id` (`angeltype_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ; + `description` text NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Daten für Tabelle `ShiftTypes` @@ -384,8 +372,8 @@ INSERT INTO `ShiftTypes` (`id`, `name`, `angeltype_id`, `description`) VALUES -- DROP TABLE IF EXISTS `User`; -CREATE TABLE IF NOT EXISTS `User` ( - `UID` int(11) NOT NULL AUTO_INCREMENT, +CREATE TABLE `User` ( + `UID` int(11) NOT NULL, `Nick` varchar(23) NOT NULL DEFAULT '', `Name` varchar(23) DEFAULT NULL, `Vorname` varchar(23) DEFAULT NULL, @@ -415,22 +403,15 @@ CREATE TABLE IF NOT EXISTS `User` ( `got_voucher` int(11) NOT NULL, `arrival_date` int(11) DEFAULT NULL, `planned_arrival_date` int(11) NOT NULL, - `planned_departure_date` int(11) DEFAULT NULL, - PRIMARY KEY (`UID`), - UNIQUE KEY `Nick` (`Nick`), - KEY `api_key` (`api_key`), - KEY `password_recovery_token` (`password_recovery_token`), - KEY `force_active` (`force_active`), - KEY `arrival_date` (`arrival_date`,`planned_arrival_date`), - KEY `planned_departure_date` (`planned_departure_date`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ; + `planned_departure_date` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Daten für Tabelle `User` -- INSERT INTO `User` (`UID`, `Nick`, `Name`, `Vorname`, `Alter`, `Telefon`, `DECT`, `Handy`, `email`, `email_shiftinfo`, `jabber`, `Size`, `Passwort`, `password_recovery_token`, `Gekommen`, `Aktiv`, `force_active`, `Tshirt`, `color`, `Sprache`, `Menu`, `lastLogIn`, `CreateDate`, `Art`, `kommentar`, `Hometown`, `api_key`, `got_voucher`, `arrival_date`, `planned_arrival_date`, `planned_departure_date`) VALUES -(1, 'admin', 'Gates', 'Bill', 42, '', '-', '', 'admin@example.com', 1, '', 'XL', '$6$rounds=5000$hjXbIhoRTH3vKiRa$Wl2P2iI5T9iRR.HHu/YFHswBW0WVn0yxCfCiX0Keco9OdIoDK6bIAADswP6KvMCJSwTGdV8PgA8g8Xfw5l8BD1', NULL, 1, 1, 0, 1, 2, 'de_DE.UTF-8', 'L', 1439759300, '0000-00-00 00:00:00', '', '', '', '038850abdd1feb264406be3ffa746235', 3, 1439490478, 1436964455, 1440161255); +(1, 'admin', 'Gates', 'Bill', 42, '', '-', '', 'admin@example.com', 1, '', 'XL', '$6$rounds=5000$hjXbIhoRTH3vKiRa$Wl2P2iI5T9iRR.HHu/YFHswBW0WVn0yxCfCiX0Keco9OdIoDK6bIAADswP6KvMCJSwTGdV8PgA8g8Xfw5l8BD1', NULL, 1, 1, 0, 1, 0, 'de_DE.UTF-8', 'L', 1474990948, '0000-00-00 00:00:00', '', '', '', '038850abdd1feb264406be3ffa746235', 0, 1439490478, 1436964455, 1440161255); -- -------------------------------------------------------- @@ -439,18 +420,37 @@ INSERT INTO `User` (`UID`, `Nick`, `Name`, `Vorname`, `Alter`, `Telefon`, `DECT` -- DROP TABLE IF EXISTS `UserAngelTypes`; -CREATE TABLE IF NOT EXISTS `UserAngelTypes` ( - `id` int(11) NOT NULL AUTO_INCREMENT, +CREATE TABLE `UserAngelTypes` ( + `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `angeltype_id` int(11) NOT NULL, `confirm_user_id` int(11) DEFAULT NULL, - `coordinator` tinyint(1) NOT NULL, - PRIMARY KEY (`id`), - KEY `user_id` (`user_id`,`angeltype_id`,`confirm_user_id`), - KEY `angeltype_id` (`angeltype_id`), - KEY `confirm_user_id` (`confirm_user_id`), - KEY `coordinator` (`coordinator`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ; + `coordinator` tinyint(1) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -------------------------------------------------------- + +-- +-- Tabellenstruktur für Tabelle `UserDriverLicenses` +-- + +DROP TABLE IF EXISTS `UserDriverLicenses`; +CREATE TABLE `UserDriverLicenses` ( + `user_id` int(11) NOT NULL, + `has_car` tinyint(1) NOT NULL, + `has_license_car` tinyint(1) NOT NULL, + `has_license_3_5t_transporter` tinyint(1) NOT NULL, + `has_license_7_5t_truck` tinyint(1) NOT NULL, + `has_license_12_5t_truck` tinyint(1) NOT NULL, + `has_license_forklift` tinyint(1) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Daten für Tabelle `UserDriverLicenses` +-- + +INSERT INTO `UserDriverLicenses` (`user_id`, `has_car`, `has_license_car`, `has_license_3_5t_transporter`, `has_license_7_5t_truck`, `has_license_12_5t_truck`, `has_license_forklift`) VALUES +(1, 1, 1, 1, 1, 1, 1); -- -------------------------------------------------------- @@ -459,14 +459,11 @@ CREATE TABLE IF NOT EXISTS `UserAngelTypes` ( -- DROP TABLE IF EXISTS `UserGroups`; -CREATE TABLE IF NOT EXISTS `UserGroups` ( - `id` int(11) NOT NULL AUTO_INCREMENT, +CREATE TABLE `UserGroups` ( + `id` int(11) NOT NULL, `uid` int(11) NOT NULL, - `group_id` int(11) NOT NULL, - PRIMARY KEY (`id`), - KEY `uid` (`uid`,`group_id`), - KEY `group_id` (`group_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ; + `group_id` int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Daten für Tabelle `UserGroups` @@ -479,6 +476,241 @@ INSERT INTO `UserGroups` (`id`, `uid`, `group_id`) VALUES (2, 1, -4), (1, 1, -2); +-- +-- Indizes der exportierten Tabellen +-- + +-- +-- Indizes für die Tabelle `AngelTypes` +-- +ALTER TABLE `AngelTypes` + ADD PRIMARY KEY (`id`), + ADD UNIQUE KEY `Name` (`name`); + +-- +-- Indizes für die Tabelle `GroupPrivileges` +-- +ALTER TABLE `GroupPrivileges` + ADD PRIMARY KEY (`id`), + ADD KEY `group_id` (`group_id`,`privilege_id`), + ADD KEY `privilege_id` (`privilege_id`); + +-- +-- Indizes für die Tabelle `Groups` +-- +ALTER TABLE `Groups` + ADD PRIMARY KEY (`UID`); + +-- +-- Indizes für die Tabelle `LogEntries` +-- +ALTER TABLE `LogEntries` + ADD PRIMARY KEY (`id`), + ADD KEY `timestamp` (`timestamp`); + +-- +-- Indizes für die Tabelle `Messages` +-- +ALTER TABLE `Messages` + ADD PRIMARY KEY (`id`), + ADD KEY `Datum` (`Datum`), + ADD KEY `SUID` (`SUID`), + ADD KEY `RUID` (`RUID`); + +-- +-- Indizes für die Tabelle `NeededAngelTypes` +-- +ALTER TABLE `NeededAngelTypes` + ADD PRIMARY KEY (`id`), + ADD KEY `room_id` (`room_id`,`angel_type_id`), + ADD KEY `shift_id` (`shift_id`), + ADD KEY `angel_type_id` (`angel_type_id`); + +-- +-- Indizes für die Tabelle `News` +-- +ALTER TABLE `News` + ADD PRIMARY KEY (`ID`), + ADD KEY `UID` (`UID`); + +-- +-- Indizes für die Tabelle `NewsComments` +-- +ALTER TABLE `NewsComments` + ADD PRIMARY KEY (`ID`), + ADD KEY `Refid` (`Refid`), + ADD KEY `UID` (`UID`); + +-- +-- Indizes für die Tabelle `Privileges` +-- +ALTER TABLE `Privileges` + ADD PRIMARY KEY (`id`), + ADD UNIQUE KEY `name` (`name`); + +-- +-- Indizes für die Tabelle `Questions` +-- +ALTER TABLE `Questions` + ADD PRIMARY KEY (`QID`), + ADD KEY `UID` (`UID`), + ADD KEY `AID` (`AID`); + +-- +-- Indizes für die Tabelle `Room` +-- +ALTER TABLE `Room` + ADD PRIMARY KEY (`RID`), + ADD UNIQUE KEY `Name` (`Name`); + +-- +-- Indizes für die Tabelle `ShiftEntry` +-- +ALTER TABLE `ShiftEntry` + ADD PRIMARY KEY (`id`), + ADD KEY `TID` (`TID`), + ADD KEY `UID` (`UID`), + ADD KEY `SID` (`SID`,`TID`), + ADD KEY `freeloaded` (`freeloaded`); + +-- +-- Indizes für die Tabelle `Shifts` +-- +ALTER TABLE `Shifts` + ADD PRIMARY KEY (`SID`), + ADD UNIQUE KEY `PSID` (`PSID`), + ADD KEY `RID` (`RID`), + ADD KEY `shifttype_id` (`shifttype_id`), + ADD KEY `created_by_user_id` (`created_by_user_id`), + ADD KEY `edited_by_user_id` (`edited_by_user_id`); + +-- +-- Indizes für die Tabelle `ShiftTypes` +-- +ALTER TABLE `ShiftTypes` + ADD PRIMARY KEY (`id`), + ADD KEY `angeltype_id` (`angeltype_id`); + +-- +-- Indizes für die Tabelle `User` +-- +ALTER TABLE `User` + ADD PRIMARY KEY (`UID`), + ADD UNIQUE KEY `Nick` (`Nick`), + ADD KEY `api_key` (`api_key`), + ADD KEY `password_recovery_token` (`password_recovery_token`), + ADD KEY `force_active` (`force_active`), + ADD KEY `arrival_date` (`arrival_date`,`planned_arrival_date`), + ADD KEY `planned_departure_date` (`planned_departure_date`); + +-- +-- Indizes für die Tabelle `UserAngelTypes` +-- +ALTER TABLE `UserAngelTypes` + ADD PRIMARY KEY (`id`), + ADD KEY `user_id` (`user_id`,`angeltype_id`,`confirm_user_id`), + ADD KEY `angeltype_id` (`angeltype_id`), + ADD KEY `confirm_user_id` (`confirm_user_id`), + ADD KEY `coordinator` (`coordinator`); + +-- +-- Indizes für die Tabelle `UserDriverLicenses` +-- +ALTER TABLE `UserDriverLicenses` + ADD PRIMARY KEY (`user_id`); + +-- +-- Indizes für die Tabelle `UserGroups` +-- +ALTER TABLE `UserGroups` + ADD PRIMARY KEY (`id`), + ADD KEY `uid` (`uid`,`group_id`), + ADD KEY `group_id` (`group_id`); + +-- +-- AUTO_INCREMENT für exportierte Tabellen +-- + +-- +-- AUTO_INCREMENT für Tabelle `AngelTypes` +-- +ALTER TABLE `AngelTypes` + MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; +-- +-- AUTO_INCREMENT für Tabelle `GroupPrivileges` +-- +ALTER TABLE `GroupPrivileges` + MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=259; +-- +-- AUTO_INCREMENT für Tabelle `LogEntries` +-- +ALTER TABLE `LogEntries` + MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; +-- +-- AUTO_INCREMENT für Tabelle `Messages` +-- +ALTER TABLE `Messages` + MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; +-- +-- AUTO_INCREMENT für Tabelle `NeededAngelTypes` +-- +ALTER TABLE `NeededAngelTypes` + MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; +-- +-- AUTO_INCREMENT für Tabelle `News` +-- +ALTER TABLE `News` + MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT; +-- +-- AUTO_INCREMENT für Tabelle `NewsComments` +-- +ALTER TABLE `NewsComments` + MODIFY `ID` bigint(11) NOT NULL AUTO_INCREMENT; +-- +-- AUTO_INCREMENT für Tabelle `Privileges` +-- +ALTER TABLE `Privileges` + MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=40; +-- +-- AUTO_INCREMENT für Tabelle `Questions` +-- +ALTER TABLE `Questions` + MODIFY `QID` bigint(20) NOT NULL AUTO_INCREMENT; +-- +-- AUTO_INCREMENT für Tabelle `Room` +-- +ALTER TABLE `Room` + MODIFY `RID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; +-- +-- AUTO_INCREMENT für Tabelle `ShiftEntry` +-- +ALTER TABLE `ShiftEntry` + MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; +-- +-- AUTO_INCREMENT für Tabelle `Shifts` +-- +ALTER TABLE `Shifts` + MODIFY `SID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13; +-- +-- AUTO_INCREMENT für Tabelle `ShiftTypes` +-- +ALTER TABLE `ShiftTypes` + MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5; +-- +-- AUTO_INCREMENT für Tabelle `User` +-- +ALTER TABLE `User` + MODIFY `UID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; +-- +-- AUTO_INCREMENT für Tabelle `UserAngelTypes` +-- +ALTER TABLE `UserAngelTypes` + MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; +-- +-- AUTO_INCREMENT für Tabelle `UserGroups` +-- +ALTER TABLE `UserGroups` + MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13; -- -- Constraints der exportierten Tabellen -- @@ -556,10 +788,15 @@ ALTER TABLE `UserAngelTypes` ADD CONSTRAINT `userangeltypes_ibfk_2` FOREIGN KEY (`angeltype_id`) REFERENCES `AngelTypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `userangeltypes_ibfk_3` FOREIGN KEY (`confirm_user_id`) REFERENCES `User` (`UID`) ON DELETE SET NULL ON UPDATE CASCADE; +-- +-- Constraints der Tabelle `UserDriverLicenses` +-- +ALTER TABLE `UserDriverLicenses` + ADD CONSTRAINT `userdriverlicenses_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `User` (`UID`) ON DELETE CASCADE ON UPDATE CASCADE; + -- -- Constraints der Tabelle `UserGroups` -- ALTER TABLE `UserGroups` ADD CONSTRAINT `usergroups_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `Groups` (`UID`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `usergroups_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `User` (`UID`) ON DELETE CASCADE ON UPDATE CASCADE; -SET FOREIGN_KEY_CHECKS=1; diff --git a/db/update.sql b/db/update.sql index ba0227d4..e69de29b 100644 --- a/db/update.sql +++ b/db/update.sql @@ -1,32 +0,0 @@ --- drivers license information -CREATE TABLE IF NOT EXISTS `UserDriverLicenses` ( - `user_id` int(11) NOT NULL, - `has_car` tinyint(1) NOT NULL, - `has_license_car` tinyint(1) NOT NULL, - `has_license_3_5t_transporter` tinyint(1) NOT NULL, - `has_license_7_5t_truck` tinyint(1) NOT NULL, - `has_license_12_5t_truck` tinyint(1) NOT NULL, - `has_license_forklift` tinyint(1) NOT NULL, - PRIMARY KEY (`user_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; -ALTER TABLE `UserDriverLicenses` - ADD CONSTRAINT `userdriverlicenses_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `User` (`UID`) ON DELETE CASCADE ON UPDATE CASCADE; - -ALTER TABLE `AngelTypes` ADD `requires_driver_license` BOOLEAN NOT NULL; - --- --------------------------------------------------------------------------------- --- Settings table -DROP TABLE IF EXISTS `Settings`; -CREATE TABLE IF NOT EXISTS `Settings` ( - `event_name` varchar(255) DEFAULT NULL, - `buildup_start_date` int(11) DEFAULT NULL, - `event_start_date` int(11) DEFAULT NULL, - `event_end_date` int(11) DEFAULT NULL, - `teardown_end_date` int(11) DEFAULT NULL, - `event_welcome_msg` varchar(255) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; - --- Added privilege for Admin Settings -INSERT INTO `Privileges` (`id`, `name`, `desc`) VALUES (39, 'admin_settings', 'Settings Page for Admin'); - -INSERT INTO `GroupPrivileges` (`id`, `group_id`, `privilege_id`) VALUES (218, -4, 39); -- cgit v1.2.3-54-g00ecf