diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/install.sql | 57 | ||||
| -rw-r--r-- | db/update.sql | 21 |
2 files changed, 70 insertions, 8 deletions
diff --git a/db/install.sql b/db/install.sql index 1176d9fc..5c612d54 100644 --- a/db/install.sql +++ b/db/install.sql @@ -3,7 +3,7 @@ -- http://www.phpmyadmin.net -- -- Host: localhost --- Erstellungszeit: 07. Dez 2014 um 20:31 +-- Erstellungszeit: 25. Dez 2014 um 22:28 -- Server Version: 5.6.12 -- PHP-Version: 5.5.3 @@ -11,6 +11,12 @@ SET FOREIGN_KEY_CHECKS=0; SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; +-- +-- Datenbank: `engelsystem` +-- +CREATE DATABASE IF NOT EXISTS `engelsystem` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; +USE `engelsystem`; + -- -------------------------------------------------------- -- @@ -47,6 +53,14 @@ CREATE TABLE IF NOT EXISTS `Counter` ( PRIMARY KEY (`URL`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Counter der Seiten'; +-- +-- Daten für Tabelle `Counter` +-- + +INSERT INTO `Counter` (`URL`, `Anz`) VALUES +('login', 2), +('news', 1); + -- -------------------------------------------------------- -- @@ -61,7 +75,7 @@ CREATE TABLE IF NOT EXISTS `GroupPrivileges` ( PRIMARY KEY (`id`), KEY `group_id` (`group_id`,`privilege_id`), KEY `privilege_id` (`privilege_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=257 ; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=258 ; -- -- Daten für Tabelle `GroupPrivileges` @@ -82,6 +96,7 @@ INSERT INTO `GroupPrivileges` (`id`, `group_id`, `privilege_id`) VALUES (213, -5, 28), (206, -5, 31), (215, -5, 33), +(257, -5, 38), (219, -4, 14), (221, -4, 25), (220, -4, 33), @@ -240,7 +255,7 @@ CREATE TABLE IF NOT EXISTS `Privileges` ( `desc` varchar(1024) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=38 ; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=39 ; -- -- Daten für Tabelle `Privileges` @@ -282,7 +297,8 @@ INSERT INTO `Privileges` (`id`, `name`, `desc`) VALUES (34, 'atom', ' Atom news export'), (35, 'shifts_json_export', 'Export shifts in JSON format'), (36, 'angeltypes', 'View angeltypes'), -(37, 'user_angeltypes', 'Join angeltypes.'); +(37, 'user_angeltypes', 'Join angeltypes.'), +(38, 'shifttypes', 'Administrate shift types'); -- -------------------------------------------------------- @@ -351,20 +367,38 @@ CREATE TABLE IF NOT EXISTS `ShiftEntry` ( DROP TABLE IF EXISTS `Shifts`; CREATE TABLE IF NOT EXISTS `Shifts` ( `SID` int(11) NOT NULL AUTO_INCREMENT, + `title` text, + `shifttype_id` int(11) NOT NULL, `start` int(11) NOT NULL, `end` int(11) NOT NULL, `RID` int(11) NOT NULL DEFAULT '0', - `name` varchar(1024) DEFAULT NULL, `URL` text, `PSID` int(11) DEFAULT NULL, PRIMARY KEY (`SID`), UNIQUE KEY `PSID` (`PSID`), - KEY `RID` (`RID`) + KEY `RID` (`RID`), + KEY `shifttype_id` (`shifttype_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=191 ; -- -------------------------------------------------------- -- +-- Tabellenstruktur für Tabelle `ShiftTypes` +-- + +DROP TABLE IF EXISTS `ShiftTypes`; +CREATE TABLE IF NOT EXISTS `ShiftTypes` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `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=1 ; + +-- -------------------------------------------------------- + +-- -- Tabellenstruktur für Tabelle `User` -- @@ -410,7 +444,7 @@ CREATE TABLE IF NOT EXISTS `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`, `Avatar`, `Menu`, `lastLogIn`, `CreateDate`, `Art`, `kommentar`, `Hometown`, `api_key`) VALUES -(1, 'admin', 'Gates', 'Bill', 42, '', '-', '', 'admin@example.com', 0, '', 'XL', '$6$rounds=5000$hjXbIhoRTH3vKiRa$Wl2P2iI5T9iRR.HHu/YFHswBW0WVn0yxCfCiX0Keco9OdIoDK6bIAADswP6KvMCJSwTGdV8PgA8g8Xfw5l8BD1', NULL, 1, 0, 1, 0, 2, 'de_DE.UTF-8', 115, 'L', 1417980341, '0000-00-00 00:00:00', '', '', '', '038850abdd1feb264406be3ffa746235'); +(1, 'admin', 'Gates', 'Bill', 42, '', '-', '', 'admin@example.com', 0, '', 'XL', '$6$rounds=5000$hjXbIhoRTH3vKiRa$Wl2P2iI5T9iRR.HHu/YFHswBW0WVn0yxCfCiX0Keco9OdIoDK6bIAADswP6KvMCJSwTGdV8PgA8g8Xfw5l8BD1', NULL, 1, 0, 1, 0, 2, 'de_DE.UTF-8', 115, 'L', 1419542882, '0000-00-00 00:00:00', '', '', '', '038850abdd1feb264406be3ffa746235'); -- -------------------------------------------------------- @@ -524,7 +558,14 @@ ALTER TABLE `ShiftEntry` -- Constraints der Tabelle `Shifts` -- ALTER TABLE `Shifts` - ADD CONSTRAINT `shifts_ibfk_1` FOREIGN KEY (`RID`) REFERENCES `Room` (`RID`) ON DELETE CASCADE ON UPDATE CASCADE; + ADD CONSTRAINT `shifts_ibfk_1` FOREIGN KEY (`RID`) REFERENCES `Room` (`RID`) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT `shifts_ibfk_2` FOREIGN KEY (`shifttype_id`) REFERENCES `ShiftTypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Constraints der Tabelle `ShiftTypes` +-- +ALTER TABLE `ShiftTypes` + ADD CONSTRAINT `shifttypes_ibfk_1` FOREIGN KEY (`angeltype_id`) REFERENCES `AngelTypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints der Tabelle `UserAngelTypes` diff --git a/db/update.sql b/db/update.sql index 7caf7223..681d2a7c 100644 --- a/db/update.sql +++ b/db/update.sql @@ -1,3 +1,24 @@ +/* introduce shift types */ +CREATE TABLE IF NOT EXISTS `ShiftTypes` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(255) NOT NULL, + `angeltype_id` int(11) DEFAULT NULL, + `description` text NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; +ALTER TABLE `ShiftTypes` ADD INDEX ( `angeltype_id` ); +ALTER TABLE `ShiftTypes` ADD FOREIGN KEY ( `angeltype_id` ) REFERENCES `engelsystem`.`AngelTypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; +INSERT INTO `engelsystem`.`Privileges` (`id`, `name`, `desc`) VALUES (NULL , 'shifttypes', 'Administrate shift types'); +INSERT INTO `GroupPrivileges` SET `group_id`=-5, `privilege_id`=(SELECT `id` FROM `Privileges` WHERE `name`='shifttypes'); + +ALTER TABLE `Shifts` ADD `shifttype_id` INT NOT NULL AFTER `SID`, ADD INDEX ( `shifttype_id` ); +UPDATE `Shifts` SET `name`='' WHERE `name` IS NULL; +INSERT INTO `ShiftTypes` SELECT DISTINCT NULL , `name` , NULL , '' FROM `Shifts`; +UPDATE `Shifts` SET `shifttype_id`=(SELECT `id` FROM `ShiftTypes` WHERE `ShiftTypes`.`name`=`Shifts`.`name`); +ALTER TABLE `Shifts` ADD `title` TEXT NULL AFTER `SID`; +ALTER TABLE `Shifts` ADD FOREIGN KEY ( `shifttype_id` ) REFERENCES `engelsystem`.`ShiftTypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE `Shifts` DROP `name`; + /* cleanup */ ALTER TABLE `User` DROP `ICQ` ; |