diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/install.sql | 71 | ||||
| -rw-r--r-- | db/update.d/16_admin_user_angeltypes.php | 2 | ||||
| -rw-r--r-- | db/update.sql | 39 |
3 files changed, 64 insertions, 48 deletions
diff --git a/db/install.sql b/db/install.sql index 5c612d54..3b45fa27 100644 --- a/db/install.sql +++ b/db/install.sql @@ -3,7 +3,7 @@ -- http://www.phpmyadmin.net -- -- Host: localhost --- Erstellungszeit: 25. Dez 2014 um 22:28 +-- Erstellungszeit: 12. Jul 2015 um 14:45 -- Server Version: 5.6.12 -- PHP-Version: 5.5.3 @@ -11,12 +11,6 @@ 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`; - -- -------------------------------------------------------- -- @@ -31,7 +25,7 @@ CREATE TABLE IF NOT EXISTS `AngelTypes` ( `description` text NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `Name` (`name`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ; -- -- Daten für Tabelle `AngelTypes` @@ -43,27 +37,6 @@ INSERT INTO `AngelTypes` (`id`, `name`, `restricted`, `description`) VALUES -- -------------------------------------------------------- -- --- Tabellenstruktur für Tabelle `Counter` --- - -DROP TABLE IF EXISTS `Counter`; -CREATE TABLE IF NOT EXISTS `Counter` ( - `URL` varchar(255) NOT NULL DEFAULT '', - `Anz` bigint(20) NOT NULL DEFAULT '0', - 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); - --- -------------------------------------------------------- - --- -- Tabellenstruktur für Tabelle `GroupPrivileges` -- @@ -161,7 +134,7 @@ DROP TABLE IF EXISTS `LogEntries`; CREATE TABLE IF NOT EXISTS `LogEntries` ( `id` int(11) NOT NULL AUTO_INCREMENT, `timestamp` int(11) NOT NULL, - `nick` varchar(23) NOT NULL, + `nick` text NOT NULL, `message` text NOT NULL, PRIMARY KEY (`id`), KEY `timestamp` (`timestamp`) @@ -204,7 +177,7 @@ CREATE TABLE IF NOT EXISTS `NeededAngelTypes` ( 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=107 ; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -------------------------------------------------------- @@ -222,7 +195,7 @@ CREATE TABLE IF NOT EXISTS `News` ( `Treffen` tinyint(4) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `UID` (`UID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -------------------------------------------------------- @@ -316,7 +289,7 @@ CREATE TABLE IF NOT EXISTS `Questions` ( PRIMARY KEY (`QID`), KEY `UID` (`UID`), KEY `AID` (`AID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Fragen und Antworten' AUTO_INCREMENT=2 ; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Fragen und Antworten' AUTO_INCREMENT=1 ; -- -------------------------------------------------------- @@ -334,7 +307,7 @@ CREATE TABLE IF NOT EXISTS `Room` ( `Number` int(11) DEFAULT NULL, PRIMARY KEY (`RID`), UNIQUE KEY `Name` (`Name`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -------------------------------------------------------- @@ -356,7 +329,7 @@ CREATE TABLE IF NOT EXISTS `ShiftEntry` ( KEY `UID` (`UID`), KEY `SID` (`SID`,`TID`), KEY `freeloaded` (`freeloaded`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -------------------------------------------------------- @@ -378,7 +351,7 @@ CREATE TABLE IF NOT EXISTS `Shifts` ( UNIQUE KEY `PSID` (`PSID`), KEY `RID` (`RID`), KEY `shifttype_id` (`shifttype_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=191 ; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -------------------------------------------------------- @@ -394,7 +367,14 @@ CREATE TABLE IF NOT EXISTS `ShiftTypes` ( `description` text NOT NULL, PRIMARY KEY (`id`), KEY `angeltype_id` (`angeltype_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ; + +-- +-- Daten für Tabelle `ShiftTypes` +-- + +INSERT INTO `ShiftTypes` (`id`, `name`, `angeltype_id`, `description`) VALUES +(4, 'asdf', NULL, ''); -- -------------------------------------------------------- @@ -424,7 +404,6 @@ CREATE TABLE IF NOT EXISTS `User` ( `Tshirt` tinyint(4) DEFAULT '0', `color` tinyint(4) DEFAULT '10', `Sprache` char(64) NOT NULL, - `Avatar` int(11) DEFAULT '0', `Menu` char(1) NOT NULL DEFAULT 'L', `lastLogIn` int(11) NOT NULL, `CreateDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', @@ -432,19 +411,23 @@ CREATE TABLE IF NOT EXISTS `User` ( `kommentar` text, `Hometown` varchar(255) NOT NULL DEFAULT '', `api_key` varchar(32) NOT NULL, + `got_voucher` tinyint(1) NOT NULL, + `arrival_date` int(11) DEFAULT NULL, + `planned_arrival_date` int(11) NOT 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`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ; + KEY `force_active` (`force_active`), + KEY `arrival_date` (`arrival_date`,`planned_arrival_date`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ; -- -- 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`, `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', 1419542882, '0000-00-00 00:00:00', '', '', '', '038850abdd1feb264406be3ffa746235'); +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`) VALUES +(1, 'admin', 'Gates', 'Bill', 42, '', '-', '', 'admin@example.com', 0, '', 'XL', '$6$rounds=5000$hjXbIhoRTH3vKiRa$Wl2P2iI5T9iRR.HHu/YFHswBW0WVn0yxCfCiX0Keco9OdIoDK6bIAADswP6KvMCJSwTGdV8PgA8g8Xfw5l8BD1', NULL, 1, 0, 0, 0, 2, 'de_DE.UTF-8', 'L', 1436705059, '0000-00-00 00:00:00', '', '', '', '038850abdd1feb264406be3ffa746235', 0, NULL, 0); -- -------------------------------------------------------- @@ -464,7 +447,7 @@ CREATE TABLE IF NOT EXISTS `UserAngelTypes` ( KEY `angeltype_id` (`angeltype_id`), KEY `confirm_user_id` (`confirm_user_id`), KEY `coordinator` (`coordinator`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ; -- -- Daten für Tabelle `UserAngelTypes` @@ -487,7 +470,7 @@ CREATE TABLE IF NOT EXISTS `UserGroups` ( PRIMARY KEY (`id`), KEY `uid` (`uid`,`group_id`), KEY `group_id` (`group_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ; -- -- Daten für Tabelle `UserGroups` diff --git a/db/update.d/16_admin_user_angeltypes.php b/db/update.d/16_admin_user_angeltypes.php index de1803ea..29fc9f54 100644 --- a/db/update.d/16_admin_user_angeltypes.php +++ b/db/update.d/16_admin_user_angeltypes.php @@ -5,7 +5,7 @@ if (sql_num_query("SELECT * FROM `Privileges` WHERE `name`='admin_user_angeltypes'") == 0) { sql_query("INSERT INTO `Privileges` (`id`, `name`, `desc`) VALUES ( NULL , 'admin_user_angeltypes', 'Confirm restricted angel types' );"); $id = sql_id(); - sql_query("INSERT INTO `GroupPrivileges` SET `group_id`=-5, `privilege_id`=" . sql_escape($id)); + sql_query("INSERT INTO `GroupPrivileges` SET `group_id`=-5, `privilege_id`='" . sql_escape($id) . "'"); sql_query("INSERT INTO `Sprache` ( `TextID` , `Sprache` , diff --git a/db/update.sql b/db/update.sql index 681d2a7c..3dbd613a 100644 --- a/db/update.sql +++ b/db/update.sql @@ -1,3 +1,36 @@ +/* more shift infos */ +ALTER TABLE `Shifts` ADD `created_by_user_id` INT NOT NULL , +ADD `created_at_timestamp` INT NOT NULL , +ADD `edited_by_user_id` INT NOT NULL , +ADD `edited_at_timestamp` INT NOT NULL; +ALTER TABLE `Shifts` ADD INDEX ( `created_by_user_id` ); +ALTER TABLE `Shifts` ADD INDEX ( `edited_by_user_id` ); +ALTER TABLE `Shifts` CHANGE `created_by_user_id` `created_by_user_id` INT( 11 ) NULL ; +ALTER TABLE `Shifts` CHANGE `edited_by_user_id` `edited_by_user_id` INT( 11 ) NULL ; +update Shifts set created_by_user_id=null, edited_by_user_id=null; +ALTER TABLE `Shifts` ADD FOREIGN KEY ( `created_by_user_id` ) REFERENCES `engelsystem`.`User` (`UID`) ON DELETE SET NULL ON UPDATE CASCADE ; +ALTER TABLE `Shifts` ADD FOREIGN KEY ( `edited_by_user_id` ) REFERENCES `engelsystem`.`User` (`UID`) ON DELETE SET NULL ON UPDATE CASCADE ; + +/* Introduce planned departure date */ +ALTER TABLE `User` ADD `planned_departure_date` INT NULL, ADD INDEX ( `planned_departure_date` ); + +/* Allow longer angeltype names */ +ALTER TABLE `AngelTypes` CHANGE `name` `name` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ''; + +/* Enable multiple vouchers */ +ALTER TABLE `User` CHANGE `got_voucher` `got_voucher` INT NOT NULL; + +/* introduce user arrival date */ +ALTER TABLE `User` ADD `arrival_date` INT NULL , +ADD `planned_arrival_date` INT NOT NULL , +ADD INDEX ( `arrival_date` , `planned_arrival_date` ) ; + +/* fix log */ +ALTER TABLE `LogEntries` CHANGE `nick` `nick` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ; + +/* introduce got-voucher flag */ +ALTER TABLE `User` ADD `got_voucher` BOOLEAN NOT NULL; + /* introduce shift types */ CREATE TABLE IF NOT EXISTS `ShiftTypes` ( `id` int(11) NOT NULL AUTO_INCREMENT, @@ -7,8 +40,8 @@ CREATE TABLE IF NOT EXISTS `ShiftTypes` ( 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'); +ALTER TABLE `ShiftTypes` ADD FOREIGN KEY ( `angeltype_id` ) REFERENCES `AngelTypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; +INSERT INTO `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` ); @@ -16,7 +49,7 @@ 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` ADD FOREIGN KEY ( `shifttype_id` ) REFERENCES `ShiftTypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `Shifts` DROP `name`; /* cleanup */ |